@[TOC](第二十三章 MYSQL数据库 手册6-2 备份与恢复)
2、MySQL复制
扩展方式： Scale Up ，Scale Out
MySQL的扩展
读写分离
复制：每个节点都有相同的数据集
向外扩展
二进制日志
单向
复制的功用：
数据分布
负载均衡读
备份
高可用和故障切换
MySQL升级测试
2.1、主从复制
2.1.1、主从复制线程：
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
主从复制特点：
异步复制
主从数据不一致比较常见
复制架构：
Master/Slave, Master/Master, 环状复制
一主多从
从服务器还可以再有从服务器
一从多主:适用于多个不同数据库
复制需要考虑二进制日志事件记录格式
STATEMENT（5.0之前）
ROW（5.1之后，推荐）
MIXED
2.1.2、主从配置过程：
参看官网
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
主节点配置：
(1) 启用二进制日志
[mysqld]
log_bin
(2) 为当前节点设置一个全局惟一的ID号
[mysqld]
server_id=#
log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
(3) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
从节点配置：
(1) 启动中继日志
[mysqld]
server_id=# 为当前节点设置一个全局惟的ID号
read_only=ON 设置数据库只读
relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index 默认值hostname-relay-bin.index
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST='host',
MASTER_USER='repluser', MASTER_PASSWORD='replpass',
MASTER_LOG_FILE=' mariadb-bin.xxxxxx', MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
通过备份恢复数据至从服务器
复制起始位置为备份时，二进制日志文件及其POS
如果要启用级联复制,需要在从服务器启用以下配置
[mysqld]
log_bin
log_slave_updates
2.1.3、复制架构中应该注意的问题：
1、限制从服务器为只读
在从服务器上设置read_only=ON 注意：此限制对拥有SUPER权限的用户均无效
阻止所有用户, 包括主服务器复制的更新 mysql> FLUSH TABLES WITH READ LOCK;
2、RESET SLAVE
在从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log ,注意：需要先STOP
SLAVE
RESET SLAVE ALL 清除所有从服务器上设置的主服务器同步信息如：PORT, HOST, USER和 PASSWORD 等
3、sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量
4、如何保证主从复制的事务安全
参考https://mariadb.com/kb/en/library/server-system-variables/
在master节点启用参数：
sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘
在slave节点启用服务器选项：
skip_slave_start=ON 不自动启动slave
在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
【例20】主从复制，主192.168.37.7，从192.168.37.8。
干净的主机，主192.168.37.7，从192.168.37.8。
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# systemctl restart mariadb
主节点192.168.37.7，必须启⽤⼆进制⽇志。
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log_bin=/data/bin/mysql-bin
innodb_file_per_table
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
查看⼆进制⽇志的情况
7 ~]# mysql -e 'show master logs' > pos.log
7 ~]# cat pos.log
Log_name File_size
mysql-bin.000001 30373
mysql-bin.000002 1038814
mysql-bin.000003 8142
mysql-bin.000004 30373
mysql-bin.000005 1038814
mysql-bin.000006 264
mysql-bin.000007 245
授权⽤户
7 ~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.37.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
从节点192.168.37.8。
7 ~]# cat /etc/my.cnf
[mysqld]
server_id=8
read_only
#bin_logformat=row
#log_bin=/data/bin/mysql-bin
innodb_file_per_table
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
7 ~]# systemctl restart mariadb
7 ~]# ll /var/lib/mysql/
total 28712
-rw-rw---- 1 mysql mysql 16384 Jun 6 01:36 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Jun 6 01:36 aria_log_control
-rw-r----- 1 mysql mysql 18874368 Jun 6 01:36 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jun 6 01:36 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 6 01:06 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Jun 6 00:59 mysql
srwxrwxrwx 1 mysql mysql 0 Jun 6 01:36 mysql.sock
drwxr-x--- 2 mysql mysql 4096 Jun 6 00:59 performance_schema
drwxr-x--- 2 mysql mysql 20 Jun 6 00:59 test
-rw-r----- 1 mysql mysql 32 Jun 6 00:59 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 519 Jun 6 00:59 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Jun 6 00:59 xtrabackup_master_key_id
7 ~]# mysql
MariaDB [(none)]> help change master to
...
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
...
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.37.7', #主机ip
-> MASTER_USER='repluser', #用户名
-> MASTER_PASSWORD='123456', #用户密码
-> MASTER_PORT=3306, #服务端口号
-> MASTER_LOG_FILE='mysql-bin.000007', #主服务日志
-> MASTER_LOG_POS=245; #日志开始位置
Query OK, 0 rows affected (0.00 sec)
从节点⽣成了主从复制⽂件
#也可以用'll -t /var/lib/mysql'查看、用时间排序
7 ~]# ll /var/lib/mysql/
total 28728
-rw-rw---- 1 mysql mysql 16384 Jun 6 01:36 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Jun 6 01:36 aria_log_control
-rw-r----- 1 mysql mysql 18874368 Jun 6 01:36 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jun 6 01:36 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 6 01:06 ib_logfile1
-rw-rw---- 1 mysql mysql 245 Jun 6 01:53 mariadb-relay-bin.000001
-rw-rw---- 1 mysql mysql 27 Jun 6 01:53 mariadb-relay-bin.index
-rw-rw---- 1 mysql mysql 82 Jun 6 01:53 master.info
drwxr-x--- 2 mysql mysql 4096 Jun 6 00:59 mysql
srwxrwxrwx 1 mysql mysql 0 Jun 6 01:36 mysql.sock
drwxr-x--- 2 mysql mysql 4096 Jun 6 00:59 performance_schema
drwxr-x--- 2 mysql mysql 20 Jun 6 00:59 test
7 ~]# cat /var/lib/mysql/master.info
18
mysql-bin.000007
245
192.168.37.7
repluser
123456
3306
60
0
0
1800.000
0
7 ~]# cat /var/lib/mysql/relay-log.info
./mariadb-relay-bin.000001
4
mysql-bin.000007
245
查看从节点状态
7 ~]# mysql
MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.37.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: No #负责与主机的io通信
Slave_SQL_Running: No #负责自己的slave mysql进程
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: 245
Relay_Log_Space: 245
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
启动线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 400
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 684
Relay_Master_Log_File: mysql-bin.000007
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: 400
Relay_Log_Space: 980
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: 7
1 row in set (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
主节点192.168.37.7
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
导⼊数据库
7 ~]# mysql < hellodb_innodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | <--发现多了一个'hellodb'数据库
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
主节点⽣成1个线程
MariaDB [(none)]> show processlist;
+----+----------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+----------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 4 | repluser | 192.168.37.8:33364 | NULL | Binlog Dump | 375 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+----------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
2 rows in set (0.00 sec)
从节点192.168.37.8，⾃动同步。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | <--从节点以同步
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
从节点⽣成2个线程
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 5 | system user | | NULL | Connect | 545 | Waiting for master to send event | NULL | 0.000 |
| 6 | system user | | NULL | Connect | 299 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)
【例21】主从复制测试，主192.168.37.7，从192.168.37.8。
主192.168.37.7
7 ~]# mysql hellodb < testlog.sql
7 ~]# mysql hellodb
MariaDB [hellodb]> call pro_testlog;
Query OK, 1 row affected (4 min 33.14 sec)
从192.168.37.8
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+----------+
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 5 | system user | | NULL | Connect | 2019 | Waiting for master to send event | NULL | 0.000 |
| 6 | system user | | NULL | Connect | 0 | Reading event from the relay log | NULL | 0.000 |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> select count(*) from hellodb.testlog;
+----------+
| count(*) |
+----------+
| 999999 |
+----------+
1 row in set (0.11 sec)
【例22】基于就数据库做主从复制，增加新的从截点，主192.168.37.7，从192.168.37.8。
干净的主机，主192.168.37.7，从192.168.37.8。
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# systemctl restart mariadb
主节点192.168.37.7，必须启⽤⼆进制⽇志。
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log_bin=/data/bin/mysql-bin
innodb_file_per_table
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
7 ~]# systemctl restart mariadb
授权⽤户
7 ~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.37.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
完全备份，复制到从主机
7 ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/backup/all.sql
7 ~]# scp /data/backup/all.sql 192.168.37.8:
从节点192.168.37.8。
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=8
read_only
#bin_logformat=row
#log_bin=/data/bin/mysql-bin
innodb_file_per_table
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
7 ~]# systemctl restart mariadb
导⼊数据库
7 ~]# mysql < all.sql
备份⽂件中有位置
7 ~]# vim all.sql
...
--
-- Position to start replication or point-in-time recovery from
--
#备份⽂件及位置
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;
...
DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
查看完成情况
7 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
主节点，数据库192.168.37.7的数据发⽣了改变
7 ~]# mysql hellodb
MariaDB [hellodb]> select * from 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 |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert teachers (name,age)value('alice',20);
Query OK, 1 row affected (0.00 sec)
从节点192.168.37.8。改备份⽂件，⼀步到位。清空数据库。
7 ~]# vim all.sql
...
CHANGE MASTER TO
MASTER_HOST='192.168.37.7', #添加主机ip地址
MASTER_USER='repluser', #用户名
MASTER_PASSWORD='123456', #用户密码
MASTER_PORT=3306, #服务端口号
MASTER_LOG_FILE='mysql-bin.000004', #之前备份的时候有、不用改
MASTER_LOG_POS=245; #之前备份的时候有、不用改
...
导⼊备份⽂件，线程还没有启动
7 ~]# mysql < all.sql
7 ~]# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.37.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: No <--线程还没有启动
Slave_SQL_Running: No <--线程还没有启动
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: 245
Relay_Log_Space: 245
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
MariaDB [(none)]> 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 |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
启动线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> 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 | alice | 20 | NULL |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
2.2、主主复制
主主复制：互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程
【例23】主主复制，主192.168.37.7，主192.168.37.8。
⼲净系统、两台需要安装服务
7 ~]# yum -y install mariadb-server
主192.168.37.7，改节点配置
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=7
log_bin
auto_increment_offset=1
auto_increment_increment=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
7 ~]# systemctl restart mariadb
主192.168.37.8，改节点配置
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=8
log_bin
auto_increment_offset=2
auto_increment_increment=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
7 ~]# systemctl restart mariadb
主192.168.37.7
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 | <--看看二进制日志、起始位置245
+------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.37.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
7 ~]# mysqldump -A --single-transaction --master-data=1 > /data/all.sql
7 ~]# scp /data/all.sql 192.168.37.8:/data/
主192.168.37.8
#添加下面信息
7 ~]# vim /data/all.sql
--
CHANGE MASTER TO
MASTER_HOST='192.168.37.7', <---
MASTER_USER='repluser', <---
MASTER_PASSWORD='123456', <---
MASTER_PORT=3306, <---
MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=405;
--
7 ~]# mysql < /data/all.sql #将37.7数据库信息导入
7 ~]# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.37.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 405
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: No <--观察变化
Slave_SQL_Running: No <--观察变化
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: 405
Relay_Log_Space: 245
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 405
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
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: 405
Relay_Log_Space: 827
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: 7
1 row in set (0.00 sec)
授权的⽤户复制过来了
MariaDB [(none)]> select user,host from mysql.user;
+----------+---------------------+
| user | host |
+----------+---------------------+
| root | 127.0.0.1 |
| repluser | 192.168.37.% | <---
| root | ::1 |
| | centos7.localdomain |
| root | centos7.localdomain |
| | localhost |
| root | localhost |
+----------+---------------------+
7 rows in set (0.00 sec)
复制过来的数据不体现在⼆进制⽇志中
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 514994 |
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> flush privileges; #刷新数据库权限
Query OK, 0 rows affected (0.00 sec)
此时已经完成了单向复制
主192.168.37.7、开始双向复制
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.37.8', <---
-> MASTER_USER='repluser', <---
-> MASTER_PASSWORD='123456', <---
-> MASTER_PORT=3306, <---
-> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=514994; <---
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 515074
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 611
Relay_Master_Log_File: mariadb-bin.000001
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: 515074
Relay_Log_Space: 907
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: 8
1 row in set (0.00 sec)
主192.168.37.8创建数据库db1
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 | <---创建数据库db1
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
主192.168.37.7查看能否看见数据库db1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 | <---数据库db1
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
主192.168.37.7和主192.168.37.8同时修改同⼀个数据，造成数据冲突。
利⽤xshell⼯具，同时执⾏命令。.
全部会话
MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]>
MariaDB [db1]> create table test(id int auto_increment primary key,name char(20));
Query OK, 0 rows affected (0.01 sec)
查看主从状态。主192.168.37.7和主192.168.37.8，报错，冲突了。
MariaDB [db1]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 515379
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 783
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1050
Last_Error: Error 'Table 'test' already exists' on query. Default database: 'db1'. Query: 'create table test(id int auto_increment primary key,name char(20))'
Skip_Counter: 0
Exec_Master_Log_Pos: 515246
Relay_Log_Space: 1212
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: 1050
Last_SQL_Error: Error 'Table 'test' already exists' on query. Default database: 'db1'. Query: 'create table test(id int auto_increment primary key,name char(20))'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 8
1 row in set (0.00 sec)
ERROR: No query specified
解决冲突问题：
MariaDB [db1]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 515379
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
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: 515379
Relay_Log_Space: 1498
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: 8
1 row in set (0.00 sec)
id号不同，可以适当地避免冲突。
MariaDB [db1]> insert test (name)values('a');
Query OK, 1 row affected (0.00 sec)
MariaDB [db1]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | a |
+----+------+
2 rows in set (0.00 sec)
主192.168.37.7
#添加两条信息、序号分别是3和5
MariaDB [db1]> insert test (name)values('a');
Query OK, 1 row affected (0.00 sec)
MariaDB [db1]> insert test (name)values('a');
Query OK, 1 row affected (0.00 sec)
MariaDB [db1]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | a |
| 3 | a | <---
| 5 | a | <---
+----+------+
4 rows in set (0.00 sec)
主192.168.37.8
#添加两条信息、序号分别是6和8
MariaDB [db1]> insert test (name)values('a');
Query OK, 1 row affected (0.01 sec)
MariaDB [db1]> insert test (name)values('a');
Query OK, 1 row affected (0.01 sec)
MariaDB [db1]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 5 | a |
| 6 | a | <---
| 8 | a | <---
+----+------+
6 rows in set (0.00 sec)
注意：默认找最⼤的，开始。
2.3、半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失
2.3.1、半同步复制实现：
主服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1;
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;超时长为1s
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql>SHOW GLOBAL STATUS LIKE '%semi%‘;
从服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
复制过滤器： 让从节点仅复制指定的数据库，或指定数据库的指定表
两种实现方式：
(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
注意：此项和binlog_format相关
参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlogignore-db
binlog_do_db = 数据库白名单列表，多个数据库需多行实现
binlog_ignore_db = 数据库黑名单列表
问题：基于二进制还原将无法实现；不建议使用
(2) 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于
本地
问题：会造成网络及磁盘IO浪费
从服务器上的复制过滤器相关变量
replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=
【例24】半同步复制，主192.168.37.7，从192.168.37.8。
从主主到主从，把主192.168.37.8的从配置删除。
MariaDB [db1]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> reset slave all;
Query OK, 0 rows affected (0.01 sec)
删除了⼏个⽂件
7 ~]# ll /var/lib/mysql/
MariaDB [(db1)]> show slave status\G
Empty set (0.00 sec)
数据库加载插件，启⽤插件。有多个从截点，都要安装插件。
主192.168.37.7
MariaDB [db1]> show plugins;
+--------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
...
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------------+----------+--------------------+---------+---------+
42 rows in set (0.00 sec)
MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show plugins;
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
...
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)
MariaDB [db1]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [db1]> 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 | OFF |
| 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.00 sec)
永久启⽤，改配置⽂件
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=7
rpl_semi_sync_master_enabled
7 ~]# systemctl restart mariadb
从192.168.37.8
MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show plugins;
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
...
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)
MariaDB [db1]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [db1]> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
永久启⽤，改配置⽂件
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=8
rpl_semi_sync_slave_enabled
7 ~]# systemctl restart mariadb
主192.168.37.7，超时长为1s，临时设置
7 ~]# mysql
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> 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 | 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.00 sec)
从192.168.37.8
7 ~]# mysql
MariaDB [(none)]> 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)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
从192.168.37.8，同步不了。主192.168.37.7等⼀个超时时长。
从192.168.37.8，停⽌从。
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
主192.168.37.7，创建新数据库。
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (1.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
从192.168.37.8，开启从，⽴即同步。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
主192.168.37.7，状态
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1525 |
| Rpl_semi_sync_master_net_wait_time | 3051 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 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.00 sec)
【例25】复制过滤器：只复制hellodb数据库，指定复制库的⽩名单，主192.168.37.7，从192.168.37.8。
从192.168.37.8，改配置
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=8
replicate_do_db='hellodb'
rpl_semi_sync_slave_enabled
7 ~]# systemctl restart mariadb
7 ~]# mysql
MariaDB [(none)]> SHOW VARIABLES LIKE 'replicate_do_db';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| replicate_do_db | hellodb |
+-----------------+---------+
1 row in set (0.00 sec)
主192.168.37.7,修改数据库
7 ~]# mysql
MariaDB [(none)]> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> drop table hellodb.toc;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
+-------------------+
6 rows in set (0.00 sec)
从192.168.23.148，没有同步
MariaDB [(none)]> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
主192.168.37.7,修改数据库
MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
从192.168.37.8，⽴即同步
MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
【例26】复制过滤器：只复制hellodb数据库，⽤⼆进制⽇志，指定复制库的⽩名单，主192.168.37.7，从192.168.37.8。
主192.168.37.7
7 ~]# vim /etc/my.cnf
[mysqld]
server_id=7
binlog_do_db='hellodb'
7 ~]# systemctl restart mariadb
7 ~]# mysql
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000003 | 245 | hellodb | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
+-------------------+
6 rows in set (0.00 sec)
MariaDB [hellodb]> drop table coc; #删除coc
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| courses |
| scores |
| students |
| teachers |
+-------------------+
5 rows in set (0.00 sec)
从192.168.37.8
7 ~]# mysql hellodb
MariaDB [hellodb]> show tables; #刚刚删除的coc没有了
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
6 rows in set (0.00 sec)
主192.168.37.7
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> drop database db2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
从192.168.37.8
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
复制过滤器，必须切换到当前数据库，不然不起作⽤。
主192.168.37.7
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| courses |
| scores |
| students |
| teachers |
+-------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> drop table courses;
Query OK, 0 rows affected (10.00 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| scores |
| students |
| teachers |
+-------------------+
4 rows in set (0.00 sec)
