MySQL8.4.1

CREATE USER `dev`@`%` IDENTIFIED WITH sha256_password BY 'pZcTri8kabiKLuT';

GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, Index, Insert, Lock Tables, Process, References, Reload, Replication Client, Replication Slave, Select, Show Databases, Show View, Shutdown, Trigger, Update ON *.* TO `dev`@`%`;

安装MySQL 8.4.x LTS

单机安装配置

下载YUM源，官方下载页面

# 基于 EL8 的系统（例如 RHEL8 和 Oracle Linux 8）
wget https://repo.mysql.com//mysql84-community-release-el8-1.noarch.rpm
yum localinstall -y mysql84-community-release-el8-1.noarch.rpm


# 基于 EL8的系统
wget https://repo.mysql.com//mysql84-community-release-el9-1.noarch.rpm

yum localinstall -y mysql84-community-release-el9-1.noarch.rpm

安装

# 如果是EL8系统，先禁用默认启用的 MySQL 模块
yum module disable mysql

# 检查 MySQL Yum 存储库是否已成功添加并启用
yum repolist enabled | grep mysql.*-community

#mysql-8.4-lts-community                 MySQL 8.4 LTS Community Server
#mysql-connectors-community              MySQL Connectors Community
#mysql-tools-8.4-lts-community           MySQL Tools 8.4 LTS Community


# 安装
yum install -y mysql-server

my.cnf配置示例

#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
mysql_native_password=ON

log-bin=mysql-bin
binlog_format=ROW
server-id=101
gtid_mode=ON
enforce-gtid-consistency=ON
log_bin_trust_function_creators=1

event_scheduler=1
federated

# Recommended in standard MySQL setup
#
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO


character-set-server=utf8
skip-external-locking
skip-name-resolve
long_query_time = 1
slow_query_log = 0
lower_case_table_names=1
interactive_timeout = 120

max_binlog_size = 1G
max_binlog_cache_size = 2G
# 使用 binlog_expire_logs_seconds 代替 expire_logs_days
binlog_expire_logs_seconds=259200
sync_binlog = 1
innodb_file_per_table

max_allowed_packet = 1024M
key_buffer_size = 96M
table_open_cache = 2000
table_definition_cache = 1400
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M


thread_cache_size = 300


## Try number of CPU's*2 for thread_concurrency
##thread_concurrency = 32

open_files_limit = 65000
innodb_open_files = 50000
back_log = 300
max_connections = 3000
max_connect_errors = 100000
tmp_table_size = 96M
max_heap_table_size = 96M


##innodb_buffer_pool_size is 46G
#innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_file_per_table = 1

innodb_log_file_size = 256M
innodb_log_files_in_group = 2

[mysqldump]
quick
max_allowed_packet = 1024M

[client]
# 如果[mysqld]中的socket改了位置，[client]也要配置成相同位置，否则本机无法连接
socket=/data/mysql/mysql.sock

服务启动后会生成一个临时密码，登录之后需要修改

grep password /var/log/mysql/mysqld.log 
#2024-07-19T06:42:10.524800Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: <qWv;<ubd68%

mysql -u root -p

修改临时密码，并创建新用户

-- 改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_passwd';

-- 新建用户
CREATE USER `root`@`%` IDENTIFIED WITH sha256_password BY 'passwd';

GTID主从复制配置

确保所有节点的my.cnf中以下配置，如果原本没有，添加后要重启服务

[mysqld]
...
log-bin=mysql-bin  # 启用binlog，如果只是从库，可以不用这行
binlog_format=ROW  # binlog格式，行
server-id=101      # 服务ID,主从服务器不能相同
gtid_mode=ON       # 启用GTID复制
enforce-gtid-consistency=ON # 确保仅记录对基于 GTID 的复制安全的语句
...

在数据库中操作

-- 主库创建同步用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'passwd';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 在从库指定主库信息，启用GTID，并启用复制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.10.200',
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'passwd',
SOURCE_AUTO_POSITION = 1;

-- 在从库启动复制
START REPLICA;

-- 在从库查看复制状态
SHOW REPLICA STATUS\G

-- 在主库查看状态
SHOW BINARY LOG STATUS\G

备份及恢复

基本备份命令

mysqldump --all-databases --source-data --single-transaction > backup_sunday_1_PM.sql

增量备份

如果MySQL服务启用了binlog日志，则可以通过binlog日志实现增量备份

mysqldump --single-transaction --flush-logs --source-data=2 \
         --all-databases > backup_sunday_1_PM.sql

在 mysqldump 命令中，--source-data 选项的作用是将源数据库的二进制日志文件名和位置写入导出的 SQL 文件中。这对于基于二进制日志的复制和恢复操作非常有用，因为它可以记录数据库在导出时的精确状态，从而确保数据一致性。

--source-data 的作用 --source-data 选项有两个参数值：

  • --source-data=1：在导出的 SQL 文件的开头添加 CHANGE MASTER TO 语句，用于设置主服务器的二进制日志文件名和位置。

  • --source-data=2：同样添加 CHANGE MASTER TO 语句，但会作为注释添加到导出的 SQL 文件中。 这些 CHANGE MASTER TO 语句用于配置复制从服务器，指定复制开始的二进制日志位置。

以下是使用了--source-data=2参数的备份文件头部，其中记录了未备份的数据存在于 gbichot2-bin.000007二进制日志文件或更新的文件中。这样当需要使用binlog恢复增量时，直接从备份中记录的这个binlog开始就可以完整的恢复备份时间节点之后的数据。

-- Position to start replication or point-in-time recovery from
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='gbichot2-bin.000007',SOURCE_LOG_POS=4;

恢复

全量备份的恢复

mysql -u root -p < backup_sunday_1_PM.sql

在全量恢复的基础上恢复增量备份

mysqlbinlog gbichot2-bin.000007 | mysql -u root -p

报错处理

同步时提示"Authentication plugin 'caching_sha2_password' reported error"

Error connecting to source '[email protected]:3306'. This was attempt 5/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

是因为MySQL8.4默认用的密码插件是caching_sha2_password，如果没有配置SSL/TLS的话，就会报这个错，将默认的插件改成mysql_native_password即可。

修改my.cnf，并重启服务

[mysqld]
mysql_native_password=ON

修改指定用户身份验证插件

ALTER USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'your_password';

同步某个事务报错

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'fabfdee6-4599-11ef-9296-080027130d93:2' at source log mysql-bin.000003, end_log_pos 783. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

原因：默认同步时因为SOURCE_AUTO_POSITION设置为1，导致会把初始化时创建数据库的用户等信息也同步过来，但因为两边都已经事先执行过相同的操作，所以看mysqld.log，发现是创建root用户这个事务报错。可以通过跳过这个事务来恢复

[ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'fabfdee6-4599-11ef-9296-080027130d93:2' at source log mysql-bin.000003, end_log_pos 783; Error 'Operation CREATE USER failed for 'root'@'%'' on query. Default database: ''. Query: 'CREATE USER 'root'@'%' IDENTIFIED WITH 'sha256_password' AS '$5$
                                                                                                    xV]*aI)\Z~x	RL,|\nl$tcGCpUfeY8Jc2vKCR974v9eulwWKi2gvm0kh3vCW0d.'', Error_code: MY-001396

恢复步骤，在从库上操作

stop REPLICA;

-- GTID的值从show replica status中或者/var/log/mysqld.log中找到。
SET GTID_NEXT='fabfdee6-4599-11ef-9296-080027130d93:2';

BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

start REPLICA;

”mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'.“

删除my.cnf中[mysql] [client]中的default-character-set=utf8配置

上一页MySQL5.6升级5.7下一页MySQLTuner-perl检测工具

最后更新于