• MySQL主从同步

    master192.168.110.31数据库主服务器
    slave1192.168.110.32数据库从服务器
    slave2192.168.110.33数据库从服务器

    1.1 基于binlog的主从同步

    1.1.1 master配置

    1、配置server_id

    [root@master ~]# echo 'server_id=1' >> /etc/my.cnf.d/mysql-server.cnf #添加server_id
    [root@master ~]# systemctl restart mysqld.service

    2、完全备份发送给slave保证数据一致性

    [root@master ~]# mysql -e 'show databases;'

    +--------------------+	
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    [root@master ~]# mysql -e 'flush tables with read lock;' #锁表只读
    [root@master ~]# mysqldump -B school > /tmp/school.sql
    [root@master ~]# scp /tmp/school.sql 192.168.110.32:/tmp
    [root@master ~]# scp /tmp/school.sql 192.168.110.33:/tmp
    [root@master ~]# mysql -e 'unlock tables;' #解锁

    3、授权用户

    mysql> create user 'rep'@'%' identified with mysql_native_password by 'MySQL@1234';
    Query OK, 0 rows affected (0.03 sec)

    mysql> grant all on *.* to 'rep'@'%';
    Query OK, 0 rows affected (0.00 sec)

    4、查看当前binlog状态

    mysql> show master status;

    +---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000008 |     2245 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    1.1.2 slave1配置

    1、设置server_id

    [root@slave1 ~]# echo 'server_id=2' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave1 ~]# systemctl restart mysqld.service

    2、同步master数据

    [root@slave1 ~]# mysql < /tmp/school.sql
    [root@slave1 ~]# mysql -e 'show databases;'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    3、配置change msater

    mysql> change master to
    -> master_host='192.168.110.31',
    -> master_user='rep',
    -> master_password='MySQL@1234',
    -> master_log_file='binlog.000008',
    -> master_log_pos=2245,
    -> get_master_public_key=1;
    Query OK, 0 rows affected, 9 warnings (0.01 sec)

    mysql> show slave status\G ##IO和SQL线程YES就OK
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    1.1.3 slave2配置

    1、设置server_id

    [root@slave2 ~]# echo 'server_id=3' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave2 ~]# systemctl restart mysqld.service

    2、同步master数据

    [root@slave2 ~]# mysql < /tmp/school.sql
    [root@slave2 ~]# mysql -e 'show databases;'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    3、配置change msater

    mysql> change master to
    -> master_host='192.168.110.31',
    -> master_user='rep',
    -> master_password='MySQL@1234',
    -> master_log_file='binlog.000008',
    -> master_log_pos=2245
    -> get_master_public_key=1;
    Query OK, 0 rows affected, 9 warnings (0.01 sec)

    mysql> show slave status\G #IO和SQL线程YES就OK
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    1.1.4 测试

    [root@master ~]# mysql -e 'show databases' #主库创建一个数据库

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    [root@slave1 ~]# mysql -e 'show databases;' #两个从库也就有了

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    [root@slave2 ~]# mysql -e 'show databases;'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    1.2 基于GTID的主从同步

    1.2.1 master配置

    1、完全备份发送给slave保证数据一致性

    [root@master ~]# mysql -e 'show databases;'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    [root@master ~]# mysql -e 'flush tables with read lock;' #锁表只读
    [root@master ~]# mysqldump -B school > /tmp/school.sql
    [root@master ~]# scp /tmp/school.sql 192.168.110.32:/tmp
    [root@master ~]# scp /tmp/school.sql 192.168.110.33:/tmp
    [root@master ~]# mysql -e 'unlock tables;' #解锁

    2、授权用户

    mysql> create user 'rep'@'%' identified with mysql_native_password by 'MySQL@1234';
    Query OK, 0 rows affected (0.03 sec)

    mysql> grant all on *.* to 'rep'@'%';
    Query OK, 0 rows affected (0.00 sec)

    3、配置server_id,开启GTID特性

    [root@master ~]# echo 'server_id=1' >> /etc/my.cnf.d/mysql-server.cnf #添加server_id
    [root@master ~]# echo 'gtid_mode=ON' >> /etc/my.cnf.d/mysql-server.cnf
    [root@master ~]# echo 'enforce-gtid-consistency=true' >> /etc/my.cnf.d/mysql-server.cnf
    [root@master ~]# `systemctl restart mysqld.service

    1.2.2 slave1配置

    1、同步master数据，保证数据一致性

    [root@slave1 ~]# mysql < /tmp/school.sql
    [root@slave1 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2、配置server_id并开启GTID特性

    [root@slave1 ~]# echo 'server_id=2' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave1 ~]# echo 'gtid_mode=ON' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave1 ~]# echo 'enforce-gtid-consistency=true' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave1 ~]# systemctl restart mysqld.service

    3、配置change msater

    mysql> change master to
    -> master_host='192.168.110.31',
    -> master_user='rep',
    -> master_password='MySQL@1234',
    -> master_auto_position = 1;
    Query OK, 0 rows affected, 7 warnings (0.05 sec)

    [root@slave1 ~]# mysql -e 'start slave'
    [root@slave1 ~]# mysql -e 'show slave status\G'
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    1.2.3 slave2配置

    1、同步master数据，保证数据一致性

    [root@slave2 ~]# mysql < /tmp/school.sql
    [root@slave2 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2、配置server_id并开启GTID特性

    [root@slave2 ~]# echo 'server_id=3' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave2 ~]# echo 'gtid_mode=ON' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave2 ~]# echo 'enforce-gtid-consistency=true' >> /etc/my.cnf.d/mysql-server.cnf
    [root@slave2 ~]# systemctl restart mysqld.service

    3、配置change msater

    mysql> change master to
    -> master_host='192.168.110.31',
    -> master_user='rep',
    -> master_password='MySQL@1234',
    -> master_auto_position = 1;
    Query OK, 0 rows affected, 7 warnings (0.05 sec)

    [root@slave2 ~]# mysql -e 'start slave'
    [root@slave2 ~]# mysql -e 'show slave status\G'
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    1.2.4 测试

    [root@master ~]# mysql -e 'create database db1'
    [root@master ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    [root@slave1 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    [root@slave2 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    1.3 延时同步

    SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
    一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
    在主从同步的基础上配置，这里就拿一个slave做测试了，另一个配置一样

    1.3.1 slave1配置

    mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)

    mysql> change master to MASTER_DELAY = 300; #将从服务器的复制延迟设置为300秒（5分钟）。
    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: 300

    1.3.2 测试

    master
    [root@master ~]# mysql -e 'create database db2' #主库创建一个库
    [root@master ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    slave1
    [root@slave1 ~]# mysql -e 'show databases' #从库不会立刻同步

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    [root@slave1 ~]# sleep 300 #等待三百秒
    [root@slave1 ~]# mysql -e 'show databases' #同步

    +--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    1.3.3 故障恢复

    master
    [root@master ~]# mysql -e 'drop database db2' #主库模拟删除文件
    [root@master ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    slave1
    [root@slave1 ~]# mysql -e 'stop slave sql_thread;' #停止sql线程，不能再开启，开启后还是会删
    [root@slave1 ~]# mysql -e 'show master status\G' #查看当前使用的binlog

    *************************** 1. row ***************************
             File: binlog.000009
         Position: 909
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:1-4

[root@slave1 ~]# mysql -e "show binlog events in 'binlog.000009'\G"  #查询中继日志
*************************** 1. row ***************************
   Log_name: binlog.000009
        Pos: 4
 Event_type: Format_desc
  Server_id: 2
End_log_pos: 126
       Info: Server ver: 8.0.35, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000009
        Pos: 126
 Event_type: Previous_gtids
  Server_id: 2
End_log_pos: 157
       Info: 
*************************** 3. row ***************************
   Log_name: binlog.000009
        Pos: 157
 Event_type: Gtid
  Server_id: 1
End_log_pos: 241
       Info: SET @@SESSION.GTID_NEXT= '3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:1'
*************************** 4. row ***************************
   Log_name: binlog.000009
        Pos: 241
 Event_type: Query
  Server_id: 1
End_log_pos: 346
       Info: create database db1 /* xid=14 */
*************************** 5. row ***************************
   Log_name: binlog.000009
        Pos: 346
 Event_type: Gtid
  Server_id: 1
End_log_pos: 430
       Info: SET @@SESSION.GTID_NEXT= '3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:2'
*************************** 6. row ***************************
   Log_name: binlog.000009
        Pos: 430
 Event_type: Query
  Server_id: 1
End_log_pos: 535
       Info: create database db2 /* xid=30 */
*************************** 7. row ***************************
   Log_name: binlog.000009
        Pos: 535
 Event_type: Gtid
  Server_id: 1
End_log_pos: 619
       Info: SET @@SESSION.GTID_NEXT= '3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:3'
*************************** 8. row ***************************
   Log_name: binlog.000009
        Pos: 619
 Event_type: Query
  Server_id: 1
End_log_pos: 720
       Info: drop database db2 /* xid=31 */
*************************** 9. row ***************************
   Log_name: binlog.000009
        Pos: 720
 Event_type: Gtid
  Server_id: 1
End_log_pos: 804
       Info: SET @@SESSION.GTID_NEXT= '3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:4'
*************************** 10. row ***************************
   Log_name: binlog.000009
        Pos: 804
 Event_type: Query
  Server_id: 1
End_log_pos: 909
       Info: create database db2 /* xid=32 */

    • 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
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78

    #找到drop命令的GTID，恢复时不要它这里为 ‘3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:4’

    [root@slave1 ~]# mysqlbinlog --skip-gtids --include-gtids='3f8e4de8-d2c1-11ee-abc3-000c29ae0c7f:1-3' /var/lib/mysql/binlog.000009 > /tmp/gtid1.sql #导处binlog文件

    [root@slave1 ~]# mysql < /tmp/gtid1.sql #恢复，从库当主库，slave1为master

    下来就是停止原来的主库

    1.4 过滤同步

    1.4.1 master配置

    [root@master ~]# cat >> /etc/my.cnf.d/mysql-server.cnf <
    binlog_do_db= db1
    binlog_do_db= db2
    EOF
    #只同步db1和db2
    [root@master ~]# systemctl restart mysqld.service

    1.4.2 测试

    1、保证数据一致

    [root@master ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    [root@slave1 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    [root@slave1 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    [root@slave2 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    2、创建一堆数据库包括db1和db2

    [root@master ~]# mysql -e 'create database db1'
    [root@master ~]# mysql -e 'create database db2'
    [root@master ~]# mysql -e 'create database ppt'
    [root@master ~]# mysql -e 'create database word'
    [root@master ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| ppt                |
| school             |
| sys                |
| word               |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    3、从库查看

    [root@slave1 ~]# mysql -e 'show databases' #只有db1和db2被同步

    +--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    [root@slave2 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
  • 相关阅读:
    矢量叉乘能否反求矢量
    （一）TinyWebServer的环境配置与运行
    大数据Flink（五十四）：Flink用武之地
    React基础学习-Day04
    如何在VBA中实现工作表函数SIGN
    企业架构LNMP学习笔记47
    应力奇异，你是一个神奇的应力！
    R语言ggplot2可视化：gganimate包基于transition_time函数创建动态散点图动画(gif)
    消息队列 - Kafka
    Python实现猎人猎物优化算法(HPO)优化BP神经网络分类模型(BP神经网络分类算法)项目实战
  • 原文地址：https://blog.csdn.net/weixin_72583321/article/details/136381277