mysql主从复制实战

准备工作

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

SELECT VERSION();

2、主库：为从库创建复制专用账户

该账户必须授予 REPLICATION SLAVE 权限

CREATE USER 'dba_repl'@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT SELECT,RELOAD,FILE,SUPER,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;

3、主库：启用binlog并配置serverid

[mysqld]
server_id=1001
log_bin=mysql-bin

注意：主库的server_id应介于1到2^32-1。如果没有这些选项，启用binlog后重启mysql服务。

4、从库：启用binlog及relaylog并配置serverid

[mysqld]
server_id=1002
log_bin=mysql-bin
relay_log=mysql-relay-bin

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

5、主库：备份数据库

• 备份所有库：添加 --set-gtid-purged=off 可取消gtid属性（若配置GTID复制则不可取消gtid属性）

mysqldump -h 192.168.0.11 --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc --set-gtid-purged=off -uroot -p --all-databases > /data/alldb.20190822.sql

• 备份指定库：若只对指定库配置主从同步，则不需要备份所有库；

mysqldump -h 192.168.0.11 --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc --set-gtid-purged=off -uroot -p --databases db1 db2 > /data/2db.20190822.sql

6、主库：拷贝dump文件到从库服务器

• 实测发现，scp比rsync快，且不丢失大小。

scp -r -P1618 -l 16000 /data/alldb.20190822.sql 192.168.0.12:/data/

主从配置过程

点位复制

1、从库：导入主库的dump文件

从库> mysql -h 192.168.0.12 -uroot -p < /data/alldb.20190822.sql

• 遇到的问题：若主库启用了gtid，同时dump的时候没有取消gtid属性，则从库导入备份之前应该先执行 reset master; 否则会报如下错误：

报错：ERROR 1840 (HY000) at line 33: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
处理：reset master;

• [x] 加快导入速度的几个参数

set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=500000;
show variables like 'sync_binlog';

2、从库：从备份文件中获取点位信息

cat /data/alldb.20190822.sql | grep "CHANGE MASTER"

3、从库：根据实际情况修改如下参数，并执行 start slave; 启动同步

CHANGE MASTER TO MASTER_HOST='x.x.x.x',MASTER_PORT=3406,MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;
START SLAVE;
SHOW SLAVE STATUS \G;

4、从库：确认 Slave_IO_Running 及 Slave_SQL_Running 均为 yes，则表示相关进程运行无报错，待 Seconds_Behind_Master 变为 0 之后，则表示主从已完全同步。

从库> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes    #.看这里
            Slave_SQL_Running: Yes    #.看这里
        Seconds_Behind_Master: 2403   #.看这里

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

[root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | grep "skip_slave_start"
skip_slave_start=0

快速搭建 GTID 复制

• 以下是两台 MySQL 5.7.44 实例（已启用 GTID），搭建一主一从的全脚本过程，本示例为 mysql 5.7.44

注1：gtid_purged 的顺序调整，5.7.36 之后放到了文件的结尾 tail -n 50 mysqldump.sql | grep PURGED，而 8.0 仍在文件的开头 head -n 50 mysqldump.sql | grep PURGED

注2：第4步导入完成后，从库自动拥有 GTID_PURGED，不用通过 tail -n 50 mysqldump.sql | grep GTID_PURGED 手动 SET

#.注意：因为所有脚本全部通过-h ip执行，所以整个配置过程可在任一节点上操作

#.1.定义全局变量
work_dir=`pwd`
port="3306"
mysql_m="192.168.31.101"
mysql_s="192.168.31.103"

#.2.在主库上创建复制专用账户
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"CREATE USER IF NOT EXISTS 'dba_repl'@'%' IDENTIFIED with mysql_native_password BY 'MA6RuouuZZn4x_Hd';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"GRANT SELECT,RELOAD,FILE,SUPER,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'dba_repl'@'%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"FLUSH PRIVILEGES;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"select user,host,plugin,account_locked from mysql.user;"
#mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"select @@GLOBAL.gtid_purged;"

#.3.备份mysql主库的所有数据库
mysqldump --single-transaction --master-data=2 --flush-logs --set-gtid-purged=on --triggers --events --routines --skip-tz-utc -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port --all-databases > ${work_dir}/mysqldump.sql

#.4.由于从库启用gtid,在导入备份之前先reset
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e "reset master;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port < ${work_dir}/mysqldump.sql
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e "show databases;"

#.5.在从库上启动主从
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"STOP SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"RESET SLAVE ALL;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"CHANGE MASTER TO MASTER_HOST='${mysql_m}',MASTER_PORT=${port},MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"START SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"SHOW SLAVE STATUS \G;" | egrep "(Running|Last_IO_Err)"

#.6.开启从库的超级只读模式(根据需要)
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"set global read_only = on;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"set global super_read_only = on;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"select @@read_only, @@super_read_only;"