MySQL主从复制的实现

主服务器master配置

启用二进制日志
为当前节点设置一个全局唯一ID号

创建有复制权限的用户账号

[mysqld]
log_bin=/data/mysql-bin
server_id=1

# 创建复制用户
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.80.%' identified by 'teamw0rk';
# 确定复制的位置为mysql-bin.000003的404
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30334 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |       404 |
+------------------+-----------+

从服务器slave配置

启用中继日志

使用有复制权限的用户账号连接至主服务器，并启动复制线程

[mysqld]
server-id=2

MariaDB [(none)]> show slave status;
Empty set (0.00 sec)

CHANGE MASTER TO
  MASTER_HOST='192.168.80.11',
  MASTER_USER='repluser',
  MASTER_PASSWORD='teamw0rk',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=404,
  MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> show slave status\G
MariaDB [(none)]> start slave;

# 查看运行状态
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 |   42 | Waiting for master to send event                                            | NULL             |    0.000 |
|  6 | system user |           | NULL | Connect |   55 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
MariaDB [(none)]> show slave status\G

如果master已经运行了一段时间，且有大量数据

通过备份恢复数据至从服务器

master服务器

# 创建一个用于复制的账号
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.80.%' identified by 'teamw0rk';
# 备份数据库
mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql
# 将备份复制到到slave
scp /data/all.sql 192.168.80.12:/tmp

slave服务器

# 编辑all.sql文件，编辑MASTER_LOG_FILE一行
CHANGE MASTER TO 
MASTER_HOST='192.168.80.11',
MASTER_USER='repluser',
MASTER_PASSWORD='teamw0rk',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;
# 编辑/etc/my.cnf
[mysqld]
server-id=12
# 启动mysql
systemctl start mariadb
# 恢复数据库
mysql < /tmp/all.sql
MariaDB [(none)]> show slave status\G
# 开始同步
MariaDB [(none)]> start slave;

加入新的slave

备份现有节点的数据库，然后在新的slave上恢复
修改配置文件加入serveri-id
修改备份文件加入change-master信息
启动服务和slave

主服务器损坏，提升slave为master

比较二进制日志位置，选择新的为master

show slave status
# Master_Log_File:
# Read_Master_Log_Pos:

提升slave为master

# 清理slave信息
stop slave;
reset slave all;
         
#配置slave为master

其它slave

# 清空slave信息
# 重新设置master
change master to 
...

级联复制

级联节点配置

[mysqld]
log_bin
log_slave_updates

级联节点的备份从master获取，slave节点备份要从slave上获取。

清除复制错误码

忽略错误

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global sql_slave_skip_counter=1; # N：错误个数
# 配置文件中加入 slave-skip-errors=error1#, error2#,.. 指定错误编号
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

主主复制

master1

[mysqld]
server_id=1
log_bin
auto_increment_offset=1
auto_increment_increment=2

master2

server_id=2
log_bin
auto_increment_offset=2
auto_increment_increment=2

把对方指定为主节点

半同步复制

master配置

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%';

slave配置

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

复制过滤器

master 上通过binlog实现

binlog-ignore-db = 数据库黑名单列表

slave的relay log仅读取特定数据库语句

replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=

复制加密

生成证书

mkdir /etc/my.cnf.d/ssl
cd /etc/my.cnf.d/ssl
openssl genrsa 2048 > cakey.pem
openssl req -new x509 -key cakey.pem -out cacert.pem -days 3650
openssl req -new rsa:1024 -days 365 -nodes -keyout master.key > masker.csr
# master 证书
openssl x509 -req -in master.csr -CA cacert.pem -CAkey caker.pem -set_serial 01 > master.crt

# slave 证书
openssl -req -newkey rsa:1024 -nodes -keyout slave.key > slave.csr
openssl x509 -req -in slave.csr -CA cacert.pem -CAkey caker.pem -set_serial 02 > slave.crt

cacert.pem
cakey.pem
master.crt
master.csr
master.key
slave.crt
slave.csr
slave.key

master 配置

[mysqld]
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

# 创建一个使用ssl连接的复制账号
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.8.%' IDENTIFIED BY ‘magedu' REQUIRE SSL;