利用gtid及主主复制实现mysql多活

• 机器规划清单

节点	ip地址	备注	版本
节点1	10.30.3.231	主主复制的主1	MySQL 8.0.32
节点2	10.30.3.232	主主复制的主2	MySQL 8.0.32

• 1.主+从：保证主从MySQL版本一致

[root@localhost ~]# mysql -uroot -p -S /tmp/mysql_3306.sock -e "SELECT VERSION();"
+-----------+
| VERSION() |
+-----------+
| 8.0.32-24 |
+-----------+

• 2.主+从：修改mysql配置文件，在 [mysqld] 中添加binlog及gtid参数，需重启mysql以启用binlog及gtid，若binlog及gtid已启用请忽略此步

[root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | egrep "(gtid|server-id|log-bin|relay-log)"
server-id = 1001
log-bin = /data/mysql_3306/binlog/mysql-bin
relay-log = /data/mysql_3306/binlog/repl-bin
gtid-mode = on
enforce-gtid-consistency = on
binlog_gtid_simple_recovery= 1

[root@localhost ~]# mysqladmin -uroot -p -S /tmp/mysql_3306.sock shutdown

[root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &

[root@localhost ~]# mysql -uroot -p -S /tmp/mysql_3306.sock -e "show global variables like 'gtid_mode';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+

注：mysql的server_id值应介于1到2^32-1，且从库的 server-id 不能与主库相同；

• 3.主库（示例10.30.3.231）：修改mysql配置文件，在 [mysqld] 中确认如下参数

[root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | egrep "(server-id|log-bin|relay-log|log-slave-updates|auto_increment|skip-slave-start)"

server-id = 10001
log-bin = /data/mysql_3306/binlog/mysql-bin
skip-slave-start  = 1
log-slave-updates = true
relay-log = /data/mysql_3306/binlog/repl-bin
auto_increment_increment = 2
auto_increment_offset = 1

注：2台主库上分别配置 auto_increment_offset=1 和 auto_increment_offset=2 可解决多主自增长ID重复问题

• 4.从库（示例10.30.3.232）：修改mysql配置文件，在 [mysqld] 中确认如下参数

[root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | egrep "(server-id|log-bin|relay-log|log-slave-updates|auto_increment|skip-slave-start)"

server-id = 10002
log-bin = /data/mysql_3306/binlog/mysql-bin
skip-slave-start  = 1
log-slave-updates = true
relay-log = /data/mysql_3306/binlog/repl-bin
auto_increment_increment = 2
auto_increment_offset = 2

• 5.主+从：重启服务生效

[root@localhost ~]# mysqladmin -uroot -p -S /tmp/mysql_3306.sock shutdown
[root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &

• 6.主+从：登录mysql，创建复制专用账户，该账户必须授予 REPLICATION SLAVE 权限；

CREATE USER IF NOT EXISTS 'dba_repl'@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE,RELOAD,PROCESS,SUPER ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;

• 7.主+从：MySQL 8.0 则需要调整相关账号的默认加密规则

alter user 'dba_admin'@'%' identified with mysql_native_password by '1]yyJnwRD48CbSql';
alter user 'dba_repl'@'%' identified with mysql_native_password by 'MA6RuouuZZn4x_Hd';
flush privileges;

• 8.主+从：确认相关账号的加密规则为 mysql_native_password

[root@localhost ~]# mysql -udba_admin -p1]yyJnwRD48CbSql -S /tmp/mysql_3306.sock -e"select host,user,plugin,account_locked from mysql.user;"
+-----------+------------------+-----------------------+----------------+
| host      | user             | plugin                | account_locked |
+-----------+------------------+-----------------------+----------------+
| %         | dba_admin        | mysql_native_password | N              |
| %         | dba_repl         | mysql_native_password | N              |
| localhost | root             | caching_sha2_password | N              |
+-----------+------------------+-----------------------+----------------+

• 9.主库（示例10.30.3.231）：执行备份，并将备份文件拷贝到从库

[root@localhost ~]# mysqldump -h 10.30.3.231 --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc --set-gtid-purged=on -udba_admin -p --all-databases > /data/alldb.2023.sql
[root@localhost ~]# scp /data/alldb.2023.sql 10.30.3.232:/data/

• 10.从库（示例10.30.3.232）：由于启用gtid，所以导入备份之前先reset

[root@localhost ~]# mysql -uroot -p -S /tmp/mysql_3306.sock -e "reset master;"
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql_3306.sock < /data/alldb.2023.sql

• 11.从库（示例10.30.3.232）：从备份文件中获取gtid号

[root@localhost ~]# head -n 50 /data/alldb.2023.sql | grep GTID_PURGED
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'caa5303f-3a6f-11ee-9de6-7e23ca599077:1-15';

• 12.从库（示例10.30.3.232）：启动主从，将 SET @@GLOBAL.GTID_PURGED= 改为上一步获取的gtid号

STOP SLAVE;
RESET SLAVE ALL;
RESET MASTER;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'caa5303f-3a6f-11ee-9de6-7e23ca599077:1-15';
CHANGE MASTER TO MASTER_HOST='10.30.3.231',MASTER_PORT=3306,MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;
START SLAVE;
SHOW SLAVE STATUS \G;

• 13.主库（示例10.30.3.231）：将从库作为master，配置成主主模式

CHANGE MASTER TO MASTER_HOST='10.30.3.232',MASTER_PORT=3306,MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;
START SLAVE;
SHOW SLAVE STATUS \G;

• 14.分别向2个主库各创建一个测试db，然后确认已同步

[root@localhost ~]# mysql -h 10.30.3.231 -udba_admin -p1]yyJnwRD48CbSql -e "create database db1;"

[root@localhost ~]# mysql -h 10.30.3.232 -udba_admin -p1]yyJnwRD48CbSql -e "create database db2;"

[root@localhost ~]# mysql -h 10.30.3.232 -udba_admin -p1]yyJnwRD48CbSql -e "show databases like 'db%';"
+----------------+
| Database (db%) |
+----------------+
| db1            |
| db2            |
+----------------+

• x.优化：禁用skip-slave-start（需重启mysql服务以生效），slave进程会随着mysql启动而启动

[root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | grep "skip-slave-start"
skip-slave-start=0