- 1基于c语言的在嵌入式系统中以太网通信,基于μCOS-Ⅱ的嵌入式系统以太网通信功能的实现...
- 2网页版滑动验证码+短信验证码（史上最全版）_哎呀,出错了,点击刷新再来一次
- 3unity2D学习（2）Tilemap绘制地图_unity2d绘制不了地图
- 4银河麒麟V10桌面操作系统下安装Qt（5.12.10）_麒麟v10系统离线安装qt编译器
- 5Ubuntu安装qt 5.12_ubuntu安装qt5.12
- 6探索 Apple 公司股价数据-Python实现_请编写python代码,对appl公司股价数据进行下列操作: (1)读取数据文件appl.csv,储
- 7爬取链家二手房房价数据存入mongodb并进行分析
- 8Vue基础-6(v-for:根据数据生成列表结构、v-model:获取和设置表单元素的值)_v-for="(item,index) in model.palletlist
- 9Flutter更新到1.9.x版本iOS运行报错问题，Permission denied_ios集成flutter app.framework签名错误
- 10linux学习笔记-切换目录
MySQL主从同步
赞
踩
| master | 192.168.110.31 | 数据库主服务器 | |
|---|---|---|---|
| slave1 | 192.168.110.32 | 数据库从服务器 | |
| slave2 | 192.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 <<EOF
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
