个人学习使用

cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core)
----------------------------------------------------------------------
getenforce 
Enforcing
----------------------------------------------------------------------
systemctl status firewalld.service 
   Active: active (running)
----------------------------------------------------------------------
systemctl status NetworkManager
   Active: active (running)
----------------------------------------------------------------------
MySQL
Server version: 5.7.26

一）主从复制的搭建

1.1）节点准备

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat -tulnp

mysql -S /tmp/mysql3307.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|        3307 |
+-------------+
-------------------------------------------------------------------------------------
mysql -S /tmp/mysql3308.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|        3308 |
+-------------+
-------------------------------------------------------------------------------------
mysql -S /tmp/mysql3309.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|        3309 |
+-------------+

1.2）检查主库二进制日志

mysql -S /tmp/mysql3307.sock -e "select @@log_bin"
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

1.3）主库建用户

mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123456'"

1.4）全备主库数据、从库恢复数据

mysqldump -S /tmp/mysql3307.sock -A  --master-data=2 --single-transaction --triggers -R -E --max_allowed-packet=64M >/tmp/full.sql
-------------------------------------------------------------------------------------
mysql -S /tmp/mysql3308.sock < /tmp/full.sql 
-------------------------------------------------------------------------------------
mysql -S /tmp/mysql3309.sock < /tmp/full.sql

1.5）通知从库复制信息，并开启复制线程

• 范例

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;

• 3308

mysql -S /tmp/mysql3308.sock
-------------------------------------------------------------------------------------
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;
-------------------------------------------------------------------------------------
start slave;

• 3309

mysql -S /tmp/mysql3309.sock
-------------------------------------------------------------------------------------
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;
-------------------------------------------------------------------------------------
start slave;

1.6）查看复制状态

mysql -S /tmp/mysql3308.sock -e "show slave status \G" |grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
-------------------------------------------------------------------------------------
mysql -S /tmp/mysql3309.sock -e "show slave status \G" |grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

二）主从复制监控

• 主库

show processlist;
---------------------------------
show slave hosts;

• 从库

show slave status \G;

• 主库相关信息监控

Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 444

• 从库中继日志的应用状态

Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 485

• 从库复制线程有关的状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 

• 过滤复制有关的状态

Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 

• 主从延时相关状态(非人为)

Seconds_Behind_Master: 0

• 延时从库有关的状态(人为)

SQL_Delay: 0
SQL_Remaining_Delay: NULL

• GTID 复制有关的状态

Retrieved_Gid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

三）主从故障

3.1）IO线程故障

1.连接主库
连接信息错误
网络不通
防火墙
主库连接数上限
2.请求二进制日志
二进制文件丢失、损坏

• 跳过错误

stop slave
-----------------------------------
set global sql_slave_counter=1;

• 清除slave信息

stop slave;
------------------------------------
reset slave all;

3.2）SQL线程故障

1.从库发生写入：

• 新建的对象已存在
• 操作的对象不存在
• 约束冲突

stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;

2.SQL_MODE不兼容
3.参数不兼容

将从库只读

set global read_only=1;
set global super_read_only=1;
--------------------------------------------
show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+

• 扩展工具
  pt-table-checksum
  pt-table-sync

3.3）主从延时问题

• 主库原因：
  1.主库压力大
  2.主库dump是串行，事务并行
  3.长时间锁定

• 解决方案：
  1.开启GTID，实现并行传输binlog
  2.拆分大事务

• 从库原因：
  1.SQL线程是串行工作
  2.减少大事务

• 解决方案：
  5.7版本后新增Logical_clock(LC)功能，多SQL线程回访机制