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;
centos 数据库复制
最新推荐文章于 2023-03-29 16:56:03 发布