mysql主从复制

  • 主从复制原理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1.从库执行change master to 命令(主库的连接信息+复制的起点)
2.从库会将以上信息,记录到master.info文件
3.从库执行 start slave 命令,立即开启IO_T和SQL_T
4. 从库 IO_T,读取master.info文件中的信息,获取到IP,PORT,User,Pass,binlog的位置信息
5. 从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
6. IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog
7. 主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T
8. IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info
9.IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.
10. SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息
11. SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息
12. 从库会自动purge应用过relay进行定期清理
补充说明:
一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.
  • 搭建主从复制

1.编辑主库配置文件,添加如下内容

1
2
3
4
5
6
[root@localhost pub]# vi /etc/my.cnf
[mysqld]
log_bin=master-bin        #开启二进制日志,并给二进制日志文件起名
server_id=1                     #配置服务器节点id,全局唯一
innodb_file_per_table=ON      #开启每表单独存储
skip_name_resolve=on           #关闭域名反解 

2.查看二进制日志开启情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)
 
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     30379 |
| master-bin.000002 |   1038814 |
| master-bin.000003 |       245 |
+-------------------+-----------+
3 rows in set (0.00 sec)

 3.查看服务id

1
2
3
4
5
6
7
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

4.创建一个拥有复制权限的账号,并将主库锁定,不能写入 

1
2
3
MariaDB [mysql]> GRANT REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO copy@'%' IDENTIFIED BY 'copy';
MariaDB [mysql]> FLUSH PRIVILEGES ;
MariaDB [mysql]> FLUSH tables with read lock;

4.1在主库中备份出所有数据,在从库上恢复

1
2
3
4
5
6
7
8
#主库备份
[root@localhost ~]# mysqldump -uroot -p 数据库名称 > /root/备份库位置
Enter password:
 
 
#从库恢复
[root@localhost ~]# mysql -uroot -p 数据库名称 < 数据库备份文件
Enter password:

5.编辑从库配置文件

1
2
3
4
5
[root@back ~]# vi /etc/my.cnf
relay_log=relay-log         #开启中继日志
server_id=2                      #配置服务器节点id,全局唯一
innodb_file_per_table=ON          #开启每表单存储
skip_name_resolve=ON             #关闭域名反解

6.查看中继日志是否开启

1
2
3
4
5
6
7
8
9
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%relay_log';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_recovery_update_relay_log | OFF       |
| relay_log                        | relay-log |
| sync_relay_log                   | 0         |
+----------------------------------+-----------+
3 rows in set (0.00 sec)

7.查看server_id

1
2
3
4
5
6
7
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%server_id%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

8.设置主库位置及其他信息

1
2
3
4
5
6
MariaDB [hellodb]> CHANGE MASTER TO
    -> MASTER_HOST='172.17.148.255' ,     #主库地址
    -> MASTER_USER='copy' ,                      #复制用户
    -> MASTER_PASSWORD='copy' ,           #用户密码
    -> MASTER_LOG_FILE='master-bin.000003' ,     #从哪个二进制文件开始复制
    -> MASTER_LOG_POS=245;                   #复制起始位置

9.开启复制线程

1
MariaDB [mysql]> START SLAVE ;

10.查看SLAVE状态

1
MariaDB [mysql]> SHOW SLAVE STATUS \G

 11.主库解锁

1
MariaDB [mysql]> UNLOCK TABLES;

 

复制架构中应该注意的问题

  • 1.限制从服务器为只读 

  在从服务器上限制read_only=ON,此限制对拥有super权限的用户均无效(mysql可以开启super_read_only限制super用户)

1
2
3
4
5
6
7
8
9
10
MariaDB [hellodb]> SET GLOBAL read_only=ON;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)  

  或是在配置文件中添加此项

1
2
[root@back ~]# vi /etc/my.cnf
read_only=ON
  • 2.如何保证主从复制的事务安全

  在master节点启用参数

1
2
3
4
sync_binlog=ON   #事务提交时,立即将内存中的二进制文件写入磁盘
sync_master_info=ON     #马上同步这个文件内容至磁盘
innodb_flush_logs_at_trx_commit=ON   #提交时,立即将日志写入磁盘
innodb_support_xa=ON     #支持分布式  

  在slave节点启用参数

1
2
3
skip_slave_start=ON       #关闭自动提交
sync_relay_log=ON          #文件立即同步至磁盘
sync_relay_log_info=ON    #立即同步至磁盘
  • 3.延时复制:防止主库删除重要数据后,从库马上同步。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
#原理:IO线程将数据写入relaylog中,SQL线程"慢点"运行;一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
 
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
 
#排查思路
延时从库处理逻辑故障
1.2.1 延时从库的恢复思路
(1) 监控到数据库逻辑故障
(2) 停从库SQL线程,记录已经回放的位置点(截取日志起点)
(3) 截取relaylog,
  起点: show slave status \G Relay_Log_File ,Relay_Log_Pos
  终点: drop之前的位置点:show relaylog events in ''
(4) 模拟SQL线程回放日志
(5) 恢复业务
    情况一: 就一个库的话
    从库替代主库工作
    情况二:
    从库导出故障库,还原到主库中.
 
#故障演练
主库 :
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
 
从库:
 
# 1.停止 从库SQL 线程,获取relay的位置点
mysql> stop slave sql_thread;
mysql> show slave status \G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 626
 
# 2. 找到relay的截取终点
mysql> show relaylog events in 'db01-relay-bin.000002';
| db01-relay-bin.000002 | 1299 | Query          |         7 |        1228 | drop database delay           
 
3. 截取relay
[root@db01 data]# cd /data/3308/data/
[root@db01 data]# mysqlbinlog --start-position=626 --stop-position=1299 db01-relay-bin.000002 >/tmp/relay.sql
 
4. 恢复relay到从库
[root@db01 data]# mysql -uroot -p -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
  • 4.过滤复制
1
2
3
4
5
6
7
8
9
10
11
12
#选择指定的数据库进行复制,减轻主库压力
#replicate_do_db:要复制的库
#replicate_ignore_db:不复制的库,只需要开启一项配置就行
#过滤复制应用:从库配置文件,加上要复制的库名
vim /etc/my.cnf
replicate_do_db=wordpress
[root@db01 data]# systemctl restart mysqld
 
#查看从库复制状态
mysql> show slave status \G
Replicate_Do_DB: wordpress
Replicate_Ignore_DB:

 

*如果从库之前做过复制,需要清空之前的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#先停止复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
#再清空状态
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
 
#重新配置
mysql> CHANGE MASTER TO \
    -> MASTER_HOST='10.69.39.73',\
    -> MASTER_USER='copy',\
    -> MASTER_PASSWORD='copy',\
    -> MASTER_LOG_FILE='mysql-bin.000002',\
    -> MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.01 sec)
 
#开启复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

  

*查看主从状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
#主库的Position不能为0
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 106
    Binlog_Do_DB: gz_namc
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
 
 
#从库的Slave_IO_Running,Slave_SQL_Running要为yes
 
mysql> SHOW SLAVE STATUS\G
#主库有关的信息(master.info):
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
*******************************
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 609
*******************************
 
#从库relay应用信息有关的(relay.info):
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
 
#从库线程运行状态(排错)
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_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

 

*自动删除之前的二进制日志

1
2
3
4
5
6
7
8
9
10
11
#设置最多保留多长时间之前的日志
mysql> set GLOBAL expire_logs_days=30;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+
1 row in set (0.00 sec)

  

slave复制停了很久,重新倒库,重新reset slave出现relay log报错的解决办法

  

主从延时监控及原因 

  

  

  

  

  

  

  

 

posted @   ForLivetoLearn  阅读(223)  评论(0编辑  收藏  举报
编辑推荐:
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
· Linux系统下SQL Server数据库镜像配置全流程详解
阅读排行:
· Sdcb Chats 技术博客:数据库 ID 选型的曲折之路 - 从 Guid 到自增 ID,再到
· 语音处理 开源项目 EchoSharp
· 《HelloGitHub》第 106 期
· Huawei LiteOS基于Cortex-M4 GD32F4平台移植
· mysql8.0无备份通过idb文件恢复数据过程、idb文件修复和tablespace id不一致处
点击右上角即可分享
微信分享提示