MySQL主从复制架构及实现
杂项
1、设置从节点为只读模式
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
从节点:
查看复制的主节点信息文件。
[root@master2 mysql]# cat master.info
18
master-bin.000003
1087
10.201.106.131
repluser
replpass
3306
60
0
0
1800.000
0
从节点自己的本地中继日志文件位置,以及从主节点哪个日志文件哪个位置复制记录:
[root@master2 mysql]# cat relay-log.info
./relay-log.000003
613
master-bin.000003
1087
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%relay_log%';
+----------------------------------+----------------+
| Variable_name | Value |
+----------------------------------+----------------+
| innodb_recovery_update_relay_log | OFF |
| max_relay_log_size | 0 |
| relay_log | relay-log |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+----------------------------------+----------------+
2、主节点查看
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%master%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| master_verify_checksum | OFF |
| sync_master_info | 0 | 每一次给从节点发送一些event之后,本地对应的master_info会不会立即同步到磁盘上,能够让本地记录下来。保证从节点及时得到更新
+------------------------+-------+
主主架构实现
1、恢复默认配置
[root@master1 mysql]# systemctl stop mariadb.service
[root@master2 mysql]# systemctl stop mariadb.service
[root@master1 mysql]# rm -rf /data/mysql/*
[root@master2 mysql]# rm -rf /data/mysql/*
2、编辑配置文件
[root@master1 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
log_bin=master-bin
relay_log=relay-log
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
auto_increment_offset=1
auto_increment_increment=2
[root@master2 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
relay-log-index=relay-log.index
datadir=/data/mysql
log_bin=master-bin
relay_log=relay-log
server_id=5
innodb_file_per_table=ON
skip_name_resolve=ON
auto_increment_offset=2
auto_increment_increment=2
启动数据库
[root@master1 mysql]# systemctl start mariadb.service
[root@master2 mysql]# systemctl start mariadb.service
查询配置是否生效:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%%';
3、创建拥有复制权限的账号
生产环境应该单独IP授权,一个一个给
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.201.106.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.201.106.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
4、指定双方为从节点
[root@master1 mysql]#
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 428 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.201.106.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=508;
[root@master2 mysql]#
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 508 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.201.106.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=428;
查看状态:
MariaDB [(none)]> SHOW SLAVE STATUS\G
5、启动线程
[root@master2 mysql]#
MariaDB [(none)]> START SLAVE;
[root@master1 mysql]#
MariaDB [(none)]> START SLAVE;
6、测试
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 516 | | |
+-------------------+----------+--------------+------------------+
备边查看:
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.201.106.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 516
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 618
————————
右边节点测试:
MariaDB [mydb]> CREATE TABLE tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [mydb]> DESC tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)
MariaDB [mydb]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 659 | | |
左节点插入数据:
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES ('Yang Kang'),('Yang Guo'),('Yang Yanzhao');
MariaDB [mydb]> SELECT * FROM tb1;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
上
右节点插入数据:
MariaDB [mydb]> SELECT * FROM tb1;
+----+---------------+
| id | name |
+----+---------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
| 6 | Zhu Yuanzhang |
| 8 | Zhu di |
| 10 | Zhu Yue |
+----+---------------+
半同步复制
1、查看是否有相关插件
[root@master1 ~]# rpm -ql mariadb-server | grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
2、清除配置
[root@master1 ~]# systemctl stop mariadb
[root@master1 ~]# rm -rf /data/mysql/*
[root@master1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
log_bin=master-bin
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
[root@master1 ~]# systemctl start mariadb.service
[root@master2 ~]# systemctl stop mariadb
[root@master2 ~]# rm -rf /data/mysql/*
[root@master2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
relay-log-index=relay-log.index
relay_log=relay-log
server_id=5
innodb_file_per_table=ON
skip_name_resolve=ON
[root@master2 ~]# systemctl start mariadb.service
3、主节点创建有复制权限的账户
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.201.106.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 498 | | |
+-------------------+----------+--------------+------------------+
4、从服务器配置从哪里读取日志
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.201.106.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=498;
5、主服务器安装插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]> SHOW PLUGINS;
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
MariaDB [(none)]> 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 |
+------------------------------------+-------+
MariaDB [(none)]> 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 |
+--------------------------------------------+-------+
6、从节点安装从节点插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SHOW PLUGINS;
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
7、开启主从节点插件功能
7.1 开启主节点
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
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 |
7.2 开启从节点
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%SEMI%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
8、从节点开启复制线程
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
9、测试
9.1 主节点创建数据库和表
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [mydb]> CREATE TABLE tb1 (id int,name char(30));
MariaDB [mydb]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 10910 |
| Rpl_semi_sync_master_net_wait_time | 21821 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 11830 |
| Rpl_semi_sync_master_tx_wait_time | 23660 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
复制过滤器
1、设置从数据库仅复制mydb数据库
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'replicate%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| replicate_annotate_row_events | OFF |
| replicate_do_db | |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+-----------+
1.1 暂时关闭复制线程
MariaDB [(none)]> STOP SLAVE;
1.2 设置参数
MariaDB [(none)]> SET GLOBAL replicate_do_db='mydb';
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'replicate%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| replicate_annotate_row_events | OFF |
| replicate_do_db | mydb |
重新开启复制进程:
MariaDB [(none)]> START SLAVE;
1.3 测试复制数据
主:
MariaDB [mydb]> CREATE DATABASE testdb;
从:
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
主:
MariaDB [mydb]> INSERT INTO tb1 VALUES (1,"a");
从:
MariaDB [mydb]> SELECT * FROM tb1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
2、SSL
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+
2.1 创建一个要求必须使用SSL连接的复制账号
MariaDB [mydb]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.201.106.%' IDENTIFIED BY 'replpass' REQUIRE SSL;
杂项
1、清理日志
MariaDB [mydb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 30349 |
| master-bin.000002 | 1038814 |
| master-bin.000003 | 958 |
+-------------------+-----------+
MariaDB [mydb]> PURGE BINARY LOGS TO 'master-bin.000002';
Query OK, 0 rows affected (0.03 sec)
MariaDB [mydb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000002 | 1038814 |
| master-bin.000003 | 958 |
+-------------------+-----------+
2、查看复制线程
MariaDB [mydb]> SHOW PROCESSLIST;
+----+----------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+----------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 7 | repluser | 10.201.106.132:55276 | NULL | Binlog Dump | 8090 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 8 | root | localhost | mydb | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 |
+----+----------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
2 rows in set (0.00 sec)