centos 数据库复制

centos 数据库复制
1，主从复制  2，级联复制  3，主主复制  4，半同步  5，复制过滤器  6，主从复制证书加密  7，GTID架构
一，数据库主从复制
数据库不能跨版本
1.    主节点配置：
a.    启用二进制日志
b.    设置server-id
[mysqld] 
server_id=# 
log-bin   开启二进制日志
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名 
c.    重启服务
d.    创建有复制权限的账号
grant replication slave on *.* to 'xiapi'@'192.168.19.%' identified by 'xiapi';
e.    查看二进制编号（一会填写在从节点上，从节点将会在这里往后同步）
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      383 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> 
2.    从节点配置：
a.    配置服务器配置文件
[mysqld]
server-id=2
read-only=on   设置数据库为制度，针对supper user 无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin 
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
b.    启动服务
c.    配置主服务器相关信息
MariaDB [(none)]> help change master to 查看帮助
MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.19.12',
  MASTER_USER='xiapi',
  MASTER_PASSWORD='xiapi',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
d.    启动备份程序
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
e.    查看备份状态
MariaDB [(none)]> show slave status\G;
3.    扩展：
a.    查看服务器server-id
MariaDB [(none)]> show variables like '%server_id%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> 
b.    停止从节点，并删除从节点信息
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.01 sec)
c.    复制错误解决方法
可以在从服务器忽略几个主服务器的复制事件
[mysqld] 
slave_skip_errors=1007|ALL 
二，级联复制
级联复制是 让其他从服务器，在另外一台从服务器进行复制，节省主服务器资源
需要在中间服务器，启用二进制日志，并且同步的内容写进二进制日志
[mysqld] 
log_bin 
log_slave_updates
案例：
#在192.168.100.8充当master #
在192.168.100.18充当级联slave 
#在192.168.100.28充当slave 
考虑到已经有了数据，所以先做了完全备份让从节点同步
 
1.    在master实现 
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld] 
server-id=8
log-bin
[root@centos8 ~]#systemctl restart mariadb 
[root@centos8 ~]#mysql 
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' identified by 'xiapi'; 
[root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql 
[root@centos8 ~]#scp /data/all.sql 192.168.100.18:/data 
[root@centos8 ~]#scp /data/all.sql 192.168.100.28:/data 
 
2.    在中间级联slave实现 
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld] 
server-id=18 
log-bin 
log_slave_updates 
#级联复制中间节点的必选项 
[root@centos8 ~]#systemctl restart mariadb 
#还原数据库 
[root@centos8 ~]#mysql 
MariaDB [(none)]> set sql_log_bin=0; 
MariaDB [(none)]> source /data/all.sql 
MariaDB [(none)]> show master logs; 
#记录二进制位置，给第三个节点使用 
MariaDB [(none)]> set sql_log_bin=1;
 MariaDB [(none)]> start slave; 
3.    在第三个节点slave上实现 
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld] server-id=28 
[root@centos8 ~]#systemctl restart mariadb 
[root@centos8 ~]#vim /data/all.sql 
CHANGE MASTER TO 
MASTER_HOST='192.168.100.18', 
MASTER_USER='repluser', 
MASTER_PASSWORD='xiapi', 
MASTER_PORT=3306, 
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=344; 
[root@centos8 ~]#mysql < /data/all.sql 
[root@centos8 ~]#mysql -e 'start slave;'
三，主主复制
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长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) 均把对方指定为主节点，并启动复制线程
 
范例：实现两个节点的主主复制模型
1.    在第一个master节点上实现
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=8
log-bin
auto_increment_offset=1 
 #开始点
#增长幅度
auto_increment_increment=2 
[root@master1 ~]#systemctl start mariadb
[root@master1 ~]#mysql 
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name 
 | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28303 |
| mariadb-bin.000002 | 
 386 |
+--------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' 
identified by 'xiapi';
2.    在第二个master节点上实现
[rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld] 
server-id=18
log-bin
auto_increment_offset=2 
 #开始点
#增长幅度
auto_increment_increment=2 
[root@master2 ~]#systemctl start mariadb
[root@master2 ~]#mysql 
MariaDB [(none)]> CHANGE MASTER TO
  ->   MASTER_HOST='192.168.100.8',
  ->   MASTER_USER='repluser',
  ->   MASTER_PASSWORD='xiapi',
  ->   MASTER_PORT=3306,
  ->   MASTER_LOG_FILE='mariadb-bin.000002',
  ->   MASTER_LOG_POS=386;
Query OK, 0 rows affected (0.019 sec)
MariaDB [(none)]> start slave;
 
 
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> show master logs;  #查看二进制位置
+--------------------+-----------+
| Log_name 
 | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28303 |
| mariadb-bin.000002 | 
 344 |
+--------------------+-----------+
2 rows in set (0.001 sec)
3.    在第一个master节点上实现
MariaDB [(none)]> CHANGE MASTER TO
  ->   MASTER_HOST='192.168.100.18',
  ->   MASTER_USER='repluser',
  ->   MASTER_PASSWORD='xiapi',
  ->   MASTER_PORT=3306,
  ->   MASTER_LOG_FILE='mariadb-bin.000002',
  ->   MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10));
4.    两个节点分别插入数据
#在第一个节点上执行
MariaDB [db1]> create database db1;
MariaDB [db1]> insert t1 (name) values('user1');
#在第二个节点上执行
MariaDB [db1]> insert t1 (name) values('user2');
#两个节点同时插入数据
MariaDB [db1]> insert t1 (name) values('userX');
MariaDB [db1]> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|   1 | user1 |
|   2 | user2 |
|   3 | userX |
|   4 | userX |
+----+-------+
4 rows in set (0.001 sec)
5.    两个节点同时创建数据库，发生复制冲突
MariaDB [db1]> create database db2;
MariaDB [db1]> show slave status\G
*************************** 1. row ***************************
 Master_User: repluser
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mariadb-bin.000002
 Read_Master_Log_Pos: 1029
  Relay_Log_File: mariadb-relay-bin.000002
  Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.100.18
 
 
 Relay_Log_Pos: 1110
 Relay_Master_Log_File: mariadb-bin.000002
  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_Error: Error 'Can't create database 'db2'; database 
exists' on query. Default database: 'db2'. Query: 'create database db2'
 Exec_Master_Log_Pos: 897
 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_Error: 
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'db2'; database 
exists' on query. Default database: 'db2'. Query: 'create database db2'
 Replicate_Ignore_Server_Ids: 
  Master_Server_Id: 18
  Master_SSL_Crl: 
  Master_SSL_Crlpath: 
  Using_Gtid: No
 Gtid_IO_Pos: 
 Replicate_Do_Domain_Ids: 
 Replicate_Ignore_Domain_Ids: 
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: 
  Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
  Slave_Transactional_Groups: 2
1 row in set (0.003 sec)
 Parallel_Mode: conservative
 SQL_Delay: 0
 Last_IO_Errno: 0
 Relay_Log_Space: 1553
Skip_Counter: 0
  Last_Errno: 1007
四，半同步
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从
库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务
器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在
恢复时造成数据的丢失
1.    查看是否存在这个模块
[root@localhost ~]#find / -name semisync_master.so
/usr/lib64/mysql/plugin/semisync_master.so
[root@localhost ~]#
2.    主服务器安装模块
MariaDB [(none)]> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> 
3.    主服务器修改配置文件
[mysqld]
rpl_semi_sync_master_enabled=1                                                                 
rpl_semi_sync_master_timeout=3000
4.    从服务器安装模块
MariaDB [(none)]> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so'
    -> ;
Query OK, 0 rows affected (0.00 sec)
5.    从服务器修改配置文件
[mysqld]
server-id=3
datadir=/var/lib/mysql
rpl_semi_sync_slave_enabled=1 
 
6.    查看全局变量（也可以设置全局变量生效）
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  #超时长1s,默认值为10s
 
MariaDB [(none)]> 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_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)
 
MariaDB [(none)]> 
7.    停掉从服务器查看效果
[root@localhost ~]#systemctl stop mariadb.service 
 
MariaDB [(none)]> create database test2;
Query OK, 1 row affected (3.00 sec)
 
MariaDB [(none)]> 
8.    扩展mariadb-10.3以后版本启用半同步
#mariadb-10.3版以后，没有了.so文件，使用加载的方式来启用
#主服务器配置:
[mysqld]
plugin_load_add = semisync_master
#从服务器配置:
[mysqld]
plugin_load_add = semisync_slave
 
主服务器范例：
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
plugin_load_add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端
从服务器范例
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=18
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON
五，复制过滤器
让从节点仅复制指定的数据库，或指定数据库的指定表
复制过滤器两种实现方式：
1.    服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
此方法存在的问题：基于二进制还原将无法实现；不建议使用
参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
vim /etc/my.cnf
binlog-do-db    =       #数据库白名单列表，多个数据库需多行实现
binlog-ignore-db    =   #数据库黑名单列表
 
2.    从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
此方法存在的问题：会造成网络及磁盘IO浪费
从服务器上的复制过滤器相关变量
replicate_do_db=                #指定复制库的白名单
replicate_ignore_db=            #指定复制库黑名单
replicate_do_table=             #指定复制表的白名单
replicate_ignore_table=         #指定复制表的黑名单
replicate_wild_do_table= foo%.bar%    #支持通配符
replicate_wild_ignore_table=
注意：
跨库的更新将无法同步
MariaDB [db1]> create table db2.t1(id int);
Query OK, 0 rows affected (0.010 sec)
六，主从复制，证书加密
1.    生成证书
# 生成ca的key
[root@localhost ~]#openssl genrsa 2048 > cakey.pem
# 生成ca的证书
[root@localhost ~]#openssl req -new -x509 -key cakey.pem --out cacert.pem -days 3650
# 生成请求文件以及私钥
[root@localhost ~]#openssl req -newkey rsa:2048 -nodes -keyout master.key >  master.csr
# 颁发证书
[root@localhost ~]#openssl x509 -req -in master.csr  -CA cacert.pem -CAkey  cakey.pem --set_serial 01 > master.crt
# 生成第二个请求文件及私钥
[root@localhost ~]#openssl req -newkey rsa:2048 -nodes -keyout slave.key > slave.csr
# 颁发第二个证书
[root@localhost ~]#openssl x509 -req -in slave.csr  -CA cacert.pem -CAkey  cakey.pem --set_serial 02 > slave.crt
2.    主服务器开启ssl
[mysqld]
log-bin
server_id=1
ssl
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
[root@centos8 ~]#chown -R mysql.mysql  /etc/my.cnf.d/ssl/
3.    创建一个要求必须使用SSL连接的复制账号
GRANT REPLICATION SLAVE ON *.* TO   'repluser'@'192.168.100.%' IDENTIFIED BY 
'xiapi' REQUIRE SSL;
4.    从服务器slave上使用CHANGER MASTER TO 命令时指明ssl相关选项
# 这个账户如果不加证书是无法连接的
[root@centos8 ~]#mysql -urepluser -pxiapi -h192.168.100.8
ERROR 1045 (28000): Access denied for user 'repluser'@'192.168.100.18' (using 
password: YES)
[root@centos8 ~]#mysql -urepluser -pxiapi -h192.168.100.8 --ssl-
ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-
key=/etc/my.cnf.d/ssl/slave.key
MariaDB [(none)]> 
# 配置从服务器
[root@centos8 ~]#chown -R mysql.mysql  /etc/my.cnf.d/ssl/
mysql>
CHANGE MASTER TO 
MASTER_HOST='MASTERIP', 
MASTER_USER='repluser', 
MASTER_PASSWORD='xiapi',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1, 
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', 
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', 
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
七，GTID架构
GTID复制：（global transaction id 全局事务标识符） MySQL 5.6版本开始支持，GTID复制不像传统
的复制方式（异步复制、半同步复制）需要找到binlog和POS点，只需知道master的IP、端口、账号、
密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找同步
# GTID = server_uuid:transaction_id，在一组复制中，全局唯一
# server_uuid 来源于 auto.cnf
GTID配置范例
1.    主服务器
vim /etc/my.cnf
server-id=1 
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
mysql> grant replication slave on . to 'repluser'@'192.168.100.%' identified by 
'xiapi';
2.    从服务器
 
vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency
mysql>CHANGE MASTER TO  MASTER_HOST='192.168.100.100',
  MASTER_USER='repluser',
  MASTER_PASSWORD='xiapi',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;
mysql>start slave;