一、主从复制简介
MySQL内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
1、mysql支持的复制类型：
- 基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。 一旦发现没法精确复制时， 会自动选着基于行的复制。
- 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
- 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
2、复制解决的问题
MySQL复制技术有以下一些特点：
- 数据分布 (Data distribution )
- 负载平衡(load balancing)
- 备份(Backups)
- 高可用性和容错行 (High availability and failover )
二、主从复制工作过程
1、从库开启线程
从库执行change master to 命令（主库的连接信息+复制的起点），从库会将以上信息记录到master.info文件，从库执行 start slave 命令，立即开启SLAVE_IO_THREAD和SLAVE_SQL_THREAD。
2、从库线程连接主库
从库SLAVE_IO_THREAD读取master.info文件中的信息获取到IP、PORT、Use、Pass、binlog的位置信息，从库SLAVE_IO_THREAD请求连接主库，主库专门提供一个Binlog_Dump Thread，负责和IO_T交互，SLAVE_IO_THREAD根据binlog的位置信息，请求主库新的binlog。
3、主库传输数据到从库
主库通过Binlog_Dump Thread将最新的binlog，通过网络传输给从库的SLAVE_IO_THREAD，SLAVE_IO_THREAD接收到新的binlog日志，存储到TCP/IP缓存，立即返回ACK给主库，并更新master.info。
4、从库根据数据实时同步
SLAVE_IO_THREAD将TCP/IP缓存中数据，转储到磁盘relaylog中，SLAVE_SQL_THREAD读取relay.info中的信息，获取到上次已经应用过的relaylog的位置信息，SLAVE_SQL_THREAD会按照上次的位置点回放最新的relaylog，再次更新relay.info信息，从库会自动purge应用过relay进行定期清理，一旦主从复制构建成功，主库当中发生了新的变化，都会通过Binlog_Dump Thread发送信号给SLAVE_IO_THREAD，增强了主从复制的实时性。
三、主从复制搭建
1、准备多实例
# systemctl start mysqld3307
# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
# systemctl start mysqld3308
# mysql -S /data/3308/mysql.sock
mysql> exit
Bye
# mysql -S /data/3308/mysql.sock -e "select @@port"
+--------+
| @@port |
+--------+
| 3308 |
+--------+
# mysql -S /data/3307/mysql.sock -e "select @@port"
+--------+
| @@port |
+--------+
| 3307 |
+--------+
2、检查配置文件二进制日志是否开启
# vim /data/3307/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
# vim /data/3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
3、主库创建授权用户
# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.1.%' identified by '123'"
4、恢复已有最新备份文件
# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/full.sql
5、从库配置主库信息
1、准备相关信息
# vim /tmp/full.sql
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=447;
# mysql -S /data/3308/mysql.sock
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
2、写入配置信息
# mysql -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=447,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
6、检查主从复制状态
# mysql -S /data/3308/mysql.sock -e "show slave status\G" | grep "Yes"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7、重新配置主从复制
mysql> stop slave;
mysql> reset slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=447,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
四、主从复制监控
1、命令
1、主库查看
mysql> show processlist;
+----+------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| 5 | repl | mysql-1:60848 | NULL | Binlog Dump | 12505 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 447
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
2、从库查看
mysql> show slave status \G
2、内容分析
1、主库相关信息
Master_Host: 192.168.1.10 # master的IP地址或域名
Master_User: repl # master上面的一个用户，用来负责主从复制
Master_Port: 3307 # 服务器的端口
Connect_Retry: 10 # master-connect-retry选项的当前值
Master_Log_File: mysql-bin.000002 # I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos: 447 # 在当前的主服务器二进制日志中，I/O线程已经读取的位置
Relay_Log_File: mysql-1-relay-bin.000002 # slave的SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos: 320 # 在当前的中继日志中，slave的SQL线程已读取和执行的位置
Relay_Master_Log_File: mysql-bin.000002 # SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
2、过滤复制相关信息
Replicate_Do_DB: # 白名单
Replicate_Ignore_DB: # 黑名单
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
3、从库线程运行状态
Slave_IO_Running: Yes # I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running: Yes # SQL线程是否被启动
Last_Errno: 0 # slave的SQL线程读取日志参数的的错误数量
Last_Error: # slave的SQL线程读取日志参数的的错误消息
Last_IO_Errno: 0 # 最后一次I/O线程错误时的时间戳
Last_IO_Error: # 最后一次I/O线程错误时的错误消息
Last_SQL_Errno: 0 # 最后一次SQL线程错误时的时间戳
Last_SQL_Error: # 最后一次SQL线程错误时的错误消息
4、从库延时主库的时间
Seconds_Behind_Master: 0 # 表示主从之间的时间差，是数字的时候表示相差多少秒
5、延时从库
SQL_Delay: 0 # Slave滞后多少秒于master
SQL_Remaining_Delay: NULL # 当Slave_SQL_Running_State等待，直到MASTER_DELAY秒后，Master执行事件
6、GTID复制状态信息
Retrieved_Gtid_Set: # 获取到的GTID<IO线程>
Executed_Gtid_Set: # 执行过的GTID<SQL线程>
Auto_Position: 0
五、延时从库
1、设置延时
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE MASTER TO MASTER_DELAY = 10800;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
SQL_Delay: 10800 # 单位秒，建议3-6小时
SQL_Remaining_Delay: NULL # 距离同步剩余时间
2、延时从库处理逻辑故障
1、停止从库SQL线程，记录已经回放的位置点
mysql> stop slave sql_thread ;
mysql> show slave status \G
Relay_Log_File: mysql-1-relay-bin.000002
Relay_Log_Pos: 320
2、截取relaylog
start-position查看Relay_Log_Pos，stop-position查看需要撤回命令Pos行的序列号
mysql> show relaylog events in 'mysql-1-relay-bin.000002';
+--------------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-1-relay-bin.000002 | 1081 | Anonymous_Gtid | 7 | 1273 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-1-relay-bin.000002 | 1146 | Query | 7 | 1368 | drop database delay |
+--------------------------+------+----------------+-----------+-------------+---------------------------------------+
17 rows in set (0.00 sec)
mysql> exit
Bye
# cd /data/3308/data/
# mysqlbinlog --start-position=320 --stop-position=1146 mysql-1-relay-bin.000002 >/tmp/relay.sql
3、恢复relay到从库
# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/relay.sql
六、过滤复制
# vim /data/3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
replicate_do_db=repl # 白名单，仅同步repl数据库数据
# systemctl restart mysqld3308
# mysql -S /data/3308/mysql.sock
mysql> show slave status \G
Replicate_Do_DB: repl
Replicate_Ignore_DB:
七、GTID复制
1、准备主从环境
1、安装MySQL软件
# mkdir -p /server/soft
# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
# yum -y remove mariadb-libs.x86_64
# yum -y install libaio-devel
# cd /server/soft/
# tar zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
# ln -s /server/soft/mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
# useradd -s /sbin/nologin mysql
# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
# source /etc/profile
# mkdir -p /data/mysql/data
# mkdir -p /data/binlog/
# chown -R mysql.mysql /usr/local/mysql/
# chown -R mysql.mysql /data
2、准备配置文件
1、主库
# cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
#启用gtid类型
gtid-mode=on
#强制GTID的一致性
enforce-gtid-consistency=true
#slave更新是否记入日志
log-slave-updates=1
[mysql]
#mysql登录时显示的名字
prompt=mysql01 [\\d]>
EOF
2、slave1
# cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=mysql02 [\\d]>
EOF
3、slave2
# cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=mysql03 [\\d]>
EOF
3、初始化数据并启动
# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
# cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
# systemctl start mysqld
# netstat -anpt | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 32615/mysqld
2、构建主从
1、创建授权用户
# mysql
db01 [(none)]>grant replication slave on *.* to repl@'192.168.1.%' identified by '123';
2、构建主从关系
# mysql
db02 [(none)]>change master to
master_host='192.168.1.10',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
db02 [(none)]>start slave;
# mysql
db03 [(none)]>change master to
master_host='192.168.1.10',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
db03 [(none)]>start slave;
3、GTID主从复制和普通主从复制的区别
| 区别 | GTID主从复制 | 普通主从复制 |
|---|---|---|
| 首次构建恢复文件 | MASTER_AUTO_POSITION自动识别 | 手动指定 |
| 复制过程 | 直接读取最后一个relaylog的 GTID号 | 依赖master.info文件 |
| 支持引擎 | 不支持非事务引擎 | 支持多引擎 |
| 一致性 | GTID具有唯一性 | 各个集群日志文件里的pos都各不相同 |
八、快速恢复主从测试环境
从库
# mysql -S /data/3308/mysql.sock
mysql> drop database delay ;
mysql> stop slave;
mysql> reset slave all;
主库
# mysql -S /data/3307/mysql.sock
mysql> reset master;
从库
# mysql -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
评论区