主从复制
架构
依赖于二进制日志，“实时”备份的一个多节点架构。
主从复制的前提（搭建主从复制），至少 2 个实例
11.不同的server_id（区别主从）
22.主库要开启二进制日志功能，从库不开（有些特殊情况下也可以开）
33.主库需要开启二进制日志
44.主库需要授权一个专用复制用户
55.主库数据备份
66.开启专用复制线程
准备架构
| 端口 | 作用 |
|---|---|
| 3307 | 主 |
| 3308 | 从 |
| 3309 | 从 |
15.5 MySQL 多实例
2
35.5.1 创建目录
4mkdir -p /data/330{7,8,9}/data
5
65.5.2 准备配置文件
7
8cat > /data/3307/my.cnf <<EOF
9[mysqld]
10basedir=/application/mysql
11datadir=/data/3307/data
12socket=/data/3307/mysql.sock
13log_error=/data/3307/mysql.log
14port=3307
15server_id=7
16log_bin=/data/3307/mysql-bin
17EOF
18
19cat > /data/3308/my.cnf <<EOF
20[mysqld]
21basedir=/application/mysql
22datadir=/data/3308/data
23socket=/data/3308/mysql.sock
24log_error=/data/3308/mysql.log
25port=3308
26server_id=8
27log_bin=/data/3308/mysql-bin
28EOF
29
30cat > /data/3309/my.cnf <<EOF
31[mysqld]
32basedir=/application/mysql
33datadir=/data/3309/data
34socket=/data/3309/mysql.sock
35log_error=/data/3309/mysql.log
36port=3309
37server_id=9
38log_bin=/data/3309/mysql-bin
39EOF
40
415.5.3 初始化三套数据
42mv /etc/my.cnf /etc/my.cnf.bak
43mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
44mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
45mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
46
475.5.4 systemd管理多实例
48cd /etc/systemd/system
49cp mysqld.service mysqld3307.service
50cp mysqld.service mysqld3308.service
51cp mysqld.service mysqld3309.service
52
53
54vim mysqld3307.service
55ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
56
57vim mysqld3308.service
58ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
59
60vim mysqld3309.service
61ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
62
63
645.5.5 授权
65chown -R mysql.mysql /data/*
66
675.5.6 启动
68systemctl start mysqld3307.service
69systemctl start mysqld3308.service
70systemctl start mysqld3309.service
71
725.5.7 验证多实例
73netstat -lnp|grep 330
74mysql -S /data/3307/mysql.sock -e "select @@server_id"
75mysql -S /data/3308/mysql.sock -e "select @@server_id"
76mysql -S /data/3309/mysql.sock -e "select @@server_id"
主从复制搭建
1# 检查3307（主库）的二进制日志情况
2[root@localhost system]# mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'";
3+---------------------------------+----------------------------+
4| Variable_name | Value |
5+---------------------------------+----------------------------+
6| log_bin | ON |
7
8# 主库授权一个专门用于连接的用户
9[root@localhost system]# mysql -S /data/3307/mysql.sock
10mysql> grant replication slave on *.* to repl@'192.168.31.%' identified by '123';
11Query OK, 0 rows affected, 1 warning (0.00 sec)
12
13# 主库进行数据备份
14[root@localhost ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
15
16# 恢复数据到从库3308
17[root@localhost system]# mysql -S /data/3308/mysql.sock
18mysql> set sql_log_bin=0;
19mysql> source /tmp/full.sql;
20
21
22# 查看备份使用的日志文件和起点
23[root@localhost ~]# vim /tmp/full.sql
24-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=448;
25
26# 告诉从库要从哪里复制信息
27mysql> CHANGE MASTER TO
28 MASTER_HOST='192.168.31.210',
29 MASTER_USER='repl',
30 MASTER_PASSWORD='123',
31 MASTER_PORT=3307,
32 MASTER_LOG_FILE='mysql-bin.000001',
33 MASTER_LOG_POS=448,
34 MASTER_CONNECT_RETRY=10;
35
36# 启动复制线程
37mysql> start slave;
38Query OK, 0 rows affected (0.00 sec)
39
40# 在从库上查看状态
41mysql> show slave status\G
42*************************** 1. row ***************************
43 Slave_IO_Running: Yes
44 Slave_SQL_Running: Yes
45
主从复制的工作流程
名词认识
1# 文件
2 主库: binlog(二进制日志文件)
3 从库: relay-log(中继日志)、master.info(主库信息文件)、relay-log.info(中继日志应用信息)
4
5# 线程
6 主库: binlog_dump-thread(二进制日志投递线程)
7 [root@localhost system]# mysql -S /data/3307/mysql.sock -e "show processlist";
8 从库: IO_Thread(从库IO线程：请求和接收主库发过来的日志)
9 SQL_Thread(从库的SQL线程：回放从主库拿过来的日志)
主从监控
1# 主库
2[root@localhost system]# mysql -S /data/3307/mysql.sock -e "show processlist";
3+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------ +
4| Id | User | Host | db | Command | Time | State | Info |
5+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------ +
6| 7 | repl | 192.168.31.210:43850 | NULL | Binlog Dump | 4278 | Master has sent all binlog to slave; waiting for more updates | NULL |
7| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
8+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------
9
10# 从库
11mysql> show slave status\G
12*************************** 1. row ***************************
13 Slave_IO_State: Waiting for master to send event
14 Master_Host: 192.168.31.210
15 Master_User: repl
16 Master_Port: 3307
17 Connect_Retry: 10
18 Master_Log_File: mysql-bin.000001
19 Read_Master_Log_Pos: 448
20 Relay_Log_File: localhost-relay-bin.000002
21 Relay_Log_Pos: 320
22 Relay_Master_Log_File: mysql-bin.000001
23 Slave_IO_Running: Yes
24 Slave_SQL_Running: Yes
25 Replicate_Do_DB:
26 Replicate_Ignore_DB:
27 Replicate_Do_Table:
28 Replicate_Ignore_Table:
29 Replicate_Wild_Do_Table:
30 Replicate_Wild_Ignore_Table:
31 Last_Errno: 0
32 Last_Error:
33 Skip_Counter: 0
34 Exec_Master_Log_Pos: 448
35 Relay_Log_Space: 531
36 Until_Condition: None
37 Until_Log_File:
38 Until_Log_Pos: 0
39 Master_SSL_Allowed: No
40 Master_SSL_CA_File:
41 Master_SSL_CA_Path:
42 Master_SSL_Cert:
43 Master_SSL_Cipher:
44 Master_SSL_Key:
45 Seconds_Behind_Master: 0
46Master_SSL_Verify_Server_Cert: No
47 Last_IO_Errno: 0
48 Last_IO_Error:
49 Last_SQL_Errno: 0
50 Last_SQL_Error:
51 Replicate_Ignore_Server_Ids:
52 Master_Server_Id: 7
53 Master_UUID: 0e06a3df-b12b-11eb-a316-080027fb878b
54 Master_Info_File: /data/3308/data/master.info
55 SQL_Delay: 0 # 延时从库设定的时间
56 SQL_Remaining_Delay: NULL # 延时从库的剩余时间
57 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
58 Master_Retry_Count: 86400
59 Master_Bind:
60 Last_IO_Error_Timestamp:
61 Last_SQL_Error_Timestamp:
62 Master_SSL_Crl:
63 Master_SSL_Crlpath:
64 Retrieved_Gtid_Set:
65 Executed_Gtid_Set:
66 Auto_Position: 0
67 Replicate_Rewrite_DB:
68 Channel_Name:
69 Master_TLS_Version:
主从延时原因分析
1# 主库
21.日志写入不及时 :(sync_binlog=1)
32.主库并发业务较多:升级硬件、做业务拆分(分布式架构)、
43.从库太多
54.级联主从
6
7# 从库方便
8SQL线程只能有一个：5.7加入了MTS，真正实现了事务级别的并发SQL
延时从库
1# 3~6小时
2stop slave;
3CHANGE MASTER TO MASTER_DELAY=300;
4start slave
5show slave status\G;