文章目录
实现级联复制
- 级联复制需要在中间的从服务启用以下配置。
- 此配置实现中间slave节点能够将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制。
[mysqld]
server-id=20
log-bin=mysql-slave01
log-slave-updates
read-only
环境说明
- 所用服务器信息说明。
| 主机名 | IP地址 | 安装组件 |
|---|---|---|
| mysql-master | 192.168.23.10 | mysql-server-8.0.26 |
| mysql-slave01 | 192.168.23.20 | mysql-server-8.0.26 |
| mysql-slave02 | 192.168.23.30 | mysql-server-8.0.26 |
- 需要实现
mysql-master --> mysql-slave01 --> mysql-slave02之间的复制关系。 - 已经做好
mysql-master --> mysql-slave01之间的同步，并且数据同步正常，一下步骤仅仅只配置mysql-slave01 --> mysql-slave02之间的同步。
级联复制相关操作
- 中间从节点需要开启
log_slave_updates，能将master的二进制日志在本机进行数据库更新，并且同时更新本机的二进制。
[mysqld]
log_slave_updates
三台主机实现级联复制
- 在
mysql-slave01修改/etc/my.cnf配置文件
[root@mysql-slave01 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
log-bin=mysql-salve01
read-only=ON
relay-log=slave01_relay_log
relay-log-index=slave01_relay_log.index
log-slave-updates
- 全量备份
mysql-slave01数据库中的数据
[root@mysql-slave01 ~]# mysqldump -u root -p123456 -A -E -R --single-transaction --master-data=1 -F --flush-privileges > slave01-all.sql
[root@mysql-slave01 ~]# ls -l slave01-all.sql
-rw-r--r-- 1 root root 2506088 Oct 21 22:26 slave01-all.sql
- 修改全量备份文件中的
CHANGE MASRER TO的参数
[root@mysql-slave01 ~]# vim slave01-all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.23.20',
MASTER_USER='replica',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-salve01.000002', MASTER_LOG_POS=156;
- 在
mysql-slave02修改/etc/my.cnf配置文件。
[root@mysql-slave02 ~]# cat /etc/my.cnf
[mysqld]
server-id=30
log-bin=mysql-salve01
read-only=ON
relay-log=slave01_relay_log
relay-log-index=slave01_relay_log.index
- 将全量备份文件拷贝至
mysql-slave02主机上进行恢复还原。
[root@mysql-slave02 ~]# mysql -u root -p123456 < /root/slave01-all.sql
[root@mysql-slave02 ~]# mysql -u root -p123456 -e "start slave;"
- 查看
mysql-slave02的复制状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.23.20
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-salve01.000002
Read_Master_Log_Pos: 156
Relay_Log_File: slave01_relay_log.000003
Relay_Log_Pos: 328
Relay_Master_Log_File: mysql-salve01.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 539
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: d1b85e27-5121-11ed-b3f5-000c29574e47
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
- 测试级联复制是否正常
## 在 `mysql-master` 节点上插入数据
### 我这里使用的是存储过程
mysql> call sp_testlog();
Query OK, 1 row affected (1 min 39.62 sec)
## 在`mysql-slave01` 上查看复制情况
mysql> select count(*) from test.testlog;
+----------+
| count(*) |
+----------+
| 147716 |
+----------+
1 row in set (0.00 sec)
## 在`mysql-slave02` 上查看复制情况
mysql> select count(*) from test.testlog;
+----------+
| count(*) |
+----------+
| 147716 |
+----------+
1 row in set (0.00 sec)
半同步复制
- 默认情况下，MySQL的复制功能是异步的（异步就是说，主节点写入完成数据后就直接给客户端返回结果，不管从节点是否将数据同步并写入）。异步复制可以提供最佳性能，主库把 binlog 日志发送给从库后，并不验证从库是否接收完毕，这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的 binlog 日志。
- MySQL 5.5 版本为了保证主从数据的一致性问题，加入了半同步复制组件（插件），可以控制从库IO线程是否将
reloy-log(中继日志)落盘，一但落盘，主库的事务才能提交成功。默认情况下，如果超过10s没有任何从节点返回确认，此次复制行为会切换为异步复制。
半同步复制的区别
rpl_semi_sync_master_wait_point的值为AFTER_SYNC和AFTER_COMMIT的区别。
AFTER_COMMIT 会在数据提交并写入到磁盘后等待从节点 IO线程dump主节点的 binlog 日志文件。会导致出现 幻读 和 数据丢失。
AFTER_SYNC 是增强半同步复制，MySQL 8.0默认此设置。此项会在数据提交时先写入主节点的 binlog 日志文件，然后等待从节点 IO 线程 dump 主节点的 binlog 日志文件后在写入磁盘进行数据存储。可以解决AFTER_COMMIT出现的幻读和数据丢失。
半同步复制实现步骤
- 启动MySQL数据库，配置主从同步，验证主从同步是否正常；
- 连接MySQL数据库并使用
INSTALL指令在master和slave节点上安装半同步模块插件； - 修改MySQL数据库配置文件，在master节点将 指令
rpl_semi_sync_master_enable设置为ON，在slave节点将指令rpl_semi_sync_slave_enable设置为ON。
半同步复制相关操作
半同步指令设置
## 开启半同步
### 全局变量
mysql> set global rpl_semi_sync_master_enabled=1;
### 配置文件
rpl_semi_sync_master_enable=ON
## 设置半同步超时时间，默认10s
### 全局变量
mysql> set global rpl_semi_sync_master_timeout=3000;
### 配置文件
rpl_semi_sync_master_timeout=3000
## 设置半同步模式，MySQL 8.0默认为'AFTER_SYNC'
### 配置文件设置
rpl_semi_sync_master_wait_point='AFTER_SYNC'
### 全局变量设置
mysql> set global rpl_semi_sync_master_wait_point='AFTER_SYNC';
Query OK, 0 rows affected (0.00 sec)
半同步插件安装
- 安装半同步模块
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)
- 查看插件
mysql> show plugins;
- 卸载半同步模块
mysql> uninstall plugin rpl_semi_sync_master;
MySQL 8.0实现半同步复制
- 查看是否有插件模块文件
[root@mysql-master ~]# rpm -ql mysql-server | grep semi
/usr/lib64/mysql/plugin/semisync_master.so ##主半同步复制模块
/usr/lib64/mysql/plugin/semisync_replica.so
/usr/lib64/mysql/plugin/semisync_slave.so ##从半同步复制模块
/usr/lib64/mysql/plugin/semisync_source.so
- 在master服务器上修改
/etc/my.cnf配置文件- 注意先不要重启MySQL服务，需要先启动数据库安装
semisync_master.so模块，不然无法启动数据库。
- 注意先不要重启MySQL服务，需要先启动数据库安装
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
server-id=10
log-bin=mysql-master
rpl_semi_sync_master_enable=ON ##开启半同步，需要先安装'semisync_master.so' 插件后在重启数据库，否则无法启动
rpl_semi_sync_master_timeout=3000 ## 设置3s内无法同步也将返回成功信息给客户端
- 在
master节点上安装semisync_master.so
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)
## 查看安装的插件
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+---------------------------------+----------+--------------------+--------------------+---------+
- 在
master节点上临时修改变量，启动半同步复制。
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)
## 查看半同步全局变量
mysql> show global variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
## 查看半同步状态变量
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
- 在
slave节点上修改/etc/my.cnf配置文件
[mysqld]
server-id=20
log-bin=mysql-salve01
read-only=ON
relay-log=slave01_relay_log
relay-log-index=slave01_relay_log.index
log-slave-updates
rpl_semi_sync_slave_enabled=ON ##加入如下配置。
- 在
slave节点上安装semisync_slave.so模块并开启半同步模式；
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
- 查看半同步相关全局变量信息
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
- 停止并重新启动
slave节点上的主从同步（已经实现主从复制需要此步骤）。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
验证半同步
- 在开启半同步后，若没有从节点返回确认信息，主节点会等待3秒后提交给客户端成功信息，所以，我们可以停止下
slave从节点服务。
## 停止slave mysql服务
[root@mysql-slave02 ~]# systemctl stop mysqld
## 在主节点上查看客户端情况
### 'rpl_semi_sync_master_clients=0' 表示当前没有从节点客户端连接。
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 708 |
| Rpl_semi_sync_master_tx_wait_time | 2125 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
## 在主节点上写入数据
### 明显可以查看到，插入一条数据需要3秒钟。
mysql> insert into hellodb.teachers(name, age, gender) values('test3', 24, 'F');
Query OK, 1 row affected (3.00 sec)
mysql>
- 再次启动从节点然后插入数据观察
## 半同步有一个客户端连接
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 708 |
| Rpl_semi_sync_master_tx_wait_time | 2125 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
## 再次插入数据，不需要等待。
mysql> insert into hellodb.teachers(name, age, gender) values('test4', 24, 'F');
Query OK, 1 row affected (0.01 sec)
复制过滤
- 复制过滤器可以实现让从节点仅复制指定的数据库，或指定数据库的指定表。
- 复制过滤器有两种实现方式：
-
通过在 主（master）服务器 上设置仅向二进制日志中记录特定的数据库相关的事件；
-
通过在 从（slave）服务器 上设置
SQL_THREAD（SQL线程）在relay log中的事件时，仅读取与特定数据库（特定表）相关的事件并应用与本地。
-
设置方法
在Master服务器上设置
- 注意：不适用于基于具有基于语句的日志记录的跨数据库更新。
缺点：基于二进制还原将无法实现，只能修改配置文件，不支持动态修改。
优点：只需要在主节点配置一次即可
binlog-do-db=db1 ## 数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需写多行。
binlog-do-db=db2
binlog-ignore-db=db3 ## 数据库黑名单列表。
在Slave服务器上设置
缺点：会造成网络及磁盘IO浪费，在所有从节点都要配置。
优点：不影响二进制备份还原。
replicate_do_db=db1 ##指定复制库的白名单，选项不支持多值，只能分别写多行实现。
replicate_ignore_db= ##指定复制库黑名单
replicate_do_table= ##指定复制表白名单
replicate_ignore_table= ##指定复制表黑名单
## 建议使用以下通配符指令
replicate_wild_do_table=foo%.bar% ##支持通配符复制表白名单,也可以是'db1.%'
replicate_wild_ignore_table=
环境说明
- 已有主从同步，一主一从。
主服务器的白名单过滤复制
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
server-id=10
log-bin=mysql-master
rpl_semi_sync_master_enable=ON
rpl_semi_sync_master_timeout=3000
binlog-do-db=db1 ##加入如下配置
[root@mysql-master ~]# systemctl restart mysqld
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-master.000004 | 156 | db1 | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 在主服务器
db1和hellodb的表中写入数据
mysql> create table db1.t_test(id int not null auto_increment primary key, name varchar((5) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into db1.t_test(name) values('test');
Query OK, 1 row affected (0.06 sec)
mysql> insert into hellodb.teachers(name,age,gender) values('test5',24,'M');
Query OK, 1 row affected (0.01 sec)
mysql> select * from db1.t_test;
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
1 row in set (0.00 sec)
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhangshan | 5 | M |
| 6 | test | 24 | F |
| 7 | test1 | 24 | F |
| 8 | test2 | 24 | F |
| 9 | test3 | 24 | F |
| 10 | test4 | 24 | F |
| 11 | h_test | 24 | F |
| 12 | test5 | 24 | M |
+-----+---------------+-----+--------+
12 rows in set (0.00 sec)
- 在从节点上查看
db1和hellodb中的信息
mysql> select * from db1.t_test;
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
1 row in set (0.00 sec)
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhangshan | 5 | M |
| 6 | test | 24 | F |
| 7 | test1 | 24 | F |
| 8 | test2 | 24 | F |
| 9 | test3 | 24 | F |
| 10 | test4 | 24 | F |
+-----+---------------+-----+--------+
10 rows in set (0.01 sec)
- 查看主节点的二进制日志记录状态，可以观察到
hellodb数据库的改动是不会影响二进制日志的大小的。
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-master.000004 | 696 | db1 | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into hellodb.teachers(name,age,gender) values('test6',24,'M');
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-master.000004 | 696 | db1 | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into db1.t_test(name) values('test2');
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-master.000004 | 982 | db1 | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从服务器的白名单过滤复制
- 在从库上设置仅复制数据库
db1，db2的数据，不复制其他数据库数据。
[root@mysql-slave01 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
log-bin=mysql-salve01
read-only=ON
relay-log=slave01_relay_log
relay-log-index=slave01_relay_log.index
log-slave-updates
rpl_semi_sync_slave_enabled=ON
replicate_do_db=db1 ##设置一下两条
replicate_do_db=db2
[root@mysql-slave01 ~]# systemctl restart mysqld
- 在master服务器上创建
db1，db2数据库并写入数据
[root@mysql-master ~]# mysqladmin -uroot -p123456 create db1
[root@mysql-master ~]# mysqladmin -uroot -p123456 create db2
[root@mysql-master ~]# mysql -u root -p123456
## 注意：此步骤没有同步至从节点，请查看下面的错误问题处理进行了解。
mysql> create table db1.t_test1(id int not null auto_increment primary key,name varchar(5) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into db1.t_test1(name) values('test');
Query OK, 1 row affected (0.01 sec)
mysql> select * from db1.t_test1;
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
1 row in set (0.00 sec)
- 查看从节点信息
## 创建的db1,db2同步了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| databases |
| db1 |
| db2 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
9 rows in set (0.01 sec)
mysql> select * from db1.t_test1;
+----+-------+
| id | name |
+----+-------+
| 1 | test |
+----+-------+
1 rows in set (0.00 sec)
- 在其他数据库中插入数据，查看从库是否同步
mysql> insert into hellodb.teachers(name,age,gender) values('h_test',24,'F');
Query OK, 1 row affected (0.01 sec)
## 主库查看
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhangshan | 5 | M |
| 6 | test | 24 | F |
| 7 | test1 | 24 | F |
| 8 | test2 | 24 | F |
| 9 | test3 | 24 | F |
| 10 | test4 | 24 | F |
| 11 | h_test | 24 | F |
+-----+---------------+-----+--------+
11 rows in set (0.00 sec)
## 从库进行查看
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhangshan | 5 | M |
| 6 | test | 24 | F |
| 7 | test1 | 24 | F |
| 8 | test2 | 24 | F |
| 9 | test3 | 24 | F |
| 10 | test4 | 24 | F |
+-----+---------------+-----+--------+
10 rows in set (0.01 sec)
错误问题
允许同步指定库，创建表指令不同步
- 通过设置复制过滤器添加
db1为白名单后，在master上在db1创建表时没有同步至slave节点上，导致后续master的db1中创建的表中写入数据后同步出现错误。
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: db1,db2
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error executing row event: 'Table 'db1.t_test1' doesn't exist'
Skip_Counter: 0
Exec_Master_Log_Pos: 2253
Relay_Log_Space: 1447
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: 'Table 'db1.t_test1' doesn't exist'
Replicate_Ignore_Server_Ids:
解决办法一
## 由于开启了复制过滤，创建表的指令没有在从节点上进行回滚，
## 所以需要手动将创建表的指令粘贴至从节点上进行手动创建。
mysql> create table db1.t_test1(id int not null auto_increment primary key,name varchar(5) not null);
Query OK, 0 rows affected (0.01 sec)
## 创建完成后，错误依然存在，因为从节点读取到了与master 节点记录binlog Pos 位置一致的位置上，
## 所以不会回滚插入的数据，固我停止主从同步，重新指定从节点读取master节点的binlog 日志位置，
## 然后停止slave，启动slave操作后同步正常。
### 重新指定的位置我根据'Exec_Master_Log_Pos' 记录的值进行指定。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave until MASTER_LOG_FILE='mysql-master.000002',MASTER_LOG_POS=2253;
Query OK, 0 rows affected, 4 warnings (0.01 sec)
## 查看 slave 同步状态，发现 SQL 线程未启动。
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.23.10
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master.000002
Read_Master_Log_Pos: 2539
Relay_Log_File: slave01_relay_log.000007
Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-master.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: db1,db2
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2253
Relay_Log_Space: 2390
Until_Condition: Master
Until_Log_File: mysql-master.000002
Until_Log_Pos: 2253
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: d1b86d5d-5121-11ed-815a-000c29ce0282
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
mysql> select * from db1.t_test1;
Empty set (0.00 sec)
## 重新停止slave，再次启动，同步正常，插入的数据也回滚了。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select * from db1.t_test1;
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
1 row in set (0.00 sec)
- 再次在主节点上插入数据，看看从节点是否同步
mysql> insert into db1.t_test1(name) values('test2');
Query OK, 1 row affected (0.01 sec)
## 从节点数据
mysql> select * from db1.t_test1;
+----+-------+
| id | name |
+----+-------+
| 1 | test |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
解决办法二
- 创建新表的语句不会在从库上执行，进而导致主从错误
## 需要在从库上进行设置，加入'replicate_wild_do_table'指令
##（根据环境，我是添加白名单，还有一个黑名单'replicate_wild_ignore_table'）
[root@mysql-slave01 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
log-bin=mysql-salve01
read-only=ON
relay-log=slave01_relay_log
relay-log-index=slave01_relay_log.index
log-slave-updates
rpl_semi_sync_slave_enabled=ON
#replicate_do_db=db1 ##注销
#replicate_do_db=db2
replicate_wild_do_table=db1.% ##添加
[root@mysql-slave01 ~]# systemctl restart mysqld
mysql> create table db1.t_test2(id int not null auto_increment primary key,name varchar((5) not null);
Query OK, 0 rows affected (0.01 sec)
## 在从库上查看表是否创建
mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t_test1 |
| t_test2 |
+---------------+
2 rows in set (0.01 sec)
GTID复制
- GTID（全局事务标识符）建议使用
MySQL 5.7以上版本的GTID功能； - 开启 GTID 功能可以支持多
DUMP线程的并发复制，而且在MySQL 5.6实现了基于库级别多SQL线程并发。在MySQL 5.7利用 GTID 的Logic clock 逻辑时钟，保证了同库级别下的事务顺序问题，即可以实现基于事务级别的并发回放，从而大大减少了同步的延迟。 - GTID具有幂等性，即多次执行结果是一样的；
- 利用GTID复制方式只需要知道
Master IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，会自动寻找到相应的位置开始同步。
GTID相关选项
[mysqld]
gtid_mode ##gtid模式
enforce_gtid_consistency ##保证GTID安全的参数
GTID复制实现
Master服务器实现
- 修改主服务器配置文件
/etc/my.cnf，并重启服务。
[root@mysql-master ~]# cat /etc/my.cnf
[mysqld]
server-id=10
log-bin=mysql-master
gtid_mode=ON
enforce_gtid_consistency
[root@mysql-master ~]# systemctl restart mysqld
- 在
master服务器上创建主从同步账号。
mysql> create user 'replica'@'192.168.23.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replica'@'192.168.23.%';
Query OK, 0 rows affected (0.02 sec)
Slave服务器上实现
- 修改配置文件
[root@mysql-slave01 ~]# cat /etc/my.cnf
[mysqld]
server-id=20
log-bin=mysql-salve01
read-only=ON
relay-log=slave01_relay_log
relay-log-index=slave01_relay_log.index
gtid_mode=ON
enforce_gtid_consistency
[root@mysql-slave01 ~]# systemctl restart mysqld
- 登录slave数据库，设置GTID主从同步
注意：若数据不一致，则需要使用 ‘mysqldump -A -E -R --single-transaction -F–flush-privileges --master-data=2 > all.sql’ 在主库上进行数据备份，然后将备份的数据还原至从库。
mysql> change master to master_host='192.168.23.10',
-> master_user='replica',
-> master_password='123456',
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
- 启动主从复制，并查看复制状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.23.10
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master.000003
Read_Master_Log_Pos: 687
Relay_Log_File: slave01_relay_log.000002
Relay_Log_Pos: 908
Relay_Master_Log_File: mysql-master.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 687
Relay_Log_Space: 1119
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 1671118d-51e9-11ed-b0be-000c29ce0282
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1671118d-51e9-11ed-b0be-000c29ce0282:1-2
Executed_Gtid_Set: 1671118d-51e9-11ed-b0be-000c29ce0282:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
264
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
