1、主从参数配置
server_id： 设置MySQL实例的server_id，每个server_id不能一样
gtid_mode=ON： MySQL实例开启GTID模式，不能用0和1
enforce_gtid_consitency=ON：使用GTID模式复制时，需要开启参数，用来保证数据的一致性。
log-bin: MySQL必须要开启binlog
log-slave-updates=1：决定SLAVE从Master接收到更新且执行是否记录到SLAVE的binlog中
binlog_format=ROW: binlog格式为row
skip-slave-start=1(可选): 当SLAVE数据库启动的时候，SLAVE不会启动复制
2、用户权限创建
mysql> create user ‘rep’@’%’ identified by ‘123’;
Query OK, 0 rows affected (0.00 sec)
mysql> grant REPLICATION CLIENT,REPLICATION SLAVE on . to ‘rep’@’%’;
Query OK, 0 rows affected (0.01 sec)
mysql>
如果仅出于复制目的创建帐户，则该帐户仅需要 REPLICATION SLAVE特权
3、从库数据还原
主库-xtrabackup备份：
innobackupex --defaults-file=/etc/my.cnf --user=root --password=HDsqm@2020 /data/sqmbak01/
从库-xtrabackup恢复：
xtrabackup --prepare --target-dir=/tmp/dbbak/2020-07-11_02-10-44
因为备份的时间可以进行业务操作的 业务操作的内容都记录在一个文件里了 prepare就把备份过程中的事务都应用回去了，这样保证了数据的一致性
xtrabackup --copy-back --target-dir=/tmp/dbbak/2020-07-11_02-10-44 --datadir=/home/mysql3308/mysql3308
还原后记得给还原文件授权:
chown -R mysql:mysql /home/mysql3308/mysql3308
备份最好把目标datadir改名。
–move-back：将备份移动到目的路径，这样节省空间
–copy-back：将备份拷贝到目的路径，这样备份多一份，占用空间（一般建议用这个，如果空间够的话）
–apply-log：直接在备份路径应用日志文件，启库。做这一步最好将备份移动到目的路径。
–target-dir：备份文件路径位置
–datadir：要还原的对象位置
[root@localhost 2020-07-11_02-10-44]# pwd
/tmp/dbbak/2020-07-11_02-10-44
[root@localhost 2020-07-11_02-10-44]# cat xtrabackup_info
uuid = b4f58f64-c2d8-11ea-81f2-5254002a9b72
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my3307.cnf -S /tmp/mysql3307.sock -uroot -poracle /tmp/dbbak/
tool_version = 2.4.9
ibbackup_version = 2.4.9
server_version = 5.7.29-log
start_time = 2020-07-11 02:10:44
end_time = 2020-07-11 02:10:59
lock_time = 0
binlog_pos = filename ‘mysql-bin.000001’, position ‘168415000’, GTID of the last change ‘1101d656-9a62-11ea-a214-5254002a9b72:1-177’
innodb_from_lsn = 0
innodb_to_lsn = 2128762277
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
4、主从复制同步
#配置主从关系， 登录从数据库
stop slave;
reset master;
SET @@GLOBAL.GTID_PURGED= ‘1101d656-9a62-11ea-a214-5254002a9b72:1-177’
change master to master_host = ‘106.52.138.102’,master_port = 3307,master_user = ‘rep’,master_password = ‘123’,master_auto_position=1;
mysql>start slave;
mysql>show slave status;
命令查看下列参数值为
Slave_IO_Running:YES
Slave_SQL_Running:YES
5、主从跳过事务
这个是为了防止意外。在平安搭建主从的时候发现io线程和sql线程都是yes，但是数据就是不同步。
1）stop slave --io和sql线程都停止
2）reset master
3）start slave --报错Got fatal error 1236 from master when reading data from binary log: ‘The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.’
4）主库查看gtid_purged
show global variables like ‘%gtid%’;
找到gtid_purged的值
5）从库执行：
mysql> stop slave;
Query OK, 0 rows affected (0.18 sec)
mysql> reset slave;
Query OK, 0 rows affected (1.29 sec)
mysql> reset master;
Query OK, 0 rows affected (1.18 sec)
mysql> set @@global.gtid_purged=‘你的GITD值’;
Query OK, 0 rows affected (2.12 sec)
mysql> change master to master_host=‘MASTER_IP’,master_port=PORT,master_user=‘USERNAME’,master_password=‘PASSWORD’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (5.55 sec)
mysql> start slave;
Query OK, 0 rows affected (0.40 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: xxx
Master_User: xxx
Master_Port: xxx
Connect_Retry: 60
Master_Log_File: mysql-bin.002188
Read_Master_Log_Pos: 4925243
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 117539
Relay_Master_Log_File: mysql-bin.002188
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: xxx
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:xxx
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 117489
Relay_Log_Space: 4925493
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 73626
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: xxx
Master_UUID: xxx
Master_Info_File: /data02/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: xxx
Executed_Gtid_Set: GTID值
Auto_Position: 1
1 row in set (0.45 sec)
