Mysql架构之主从复制
author：JevonWei
版权声明：原创作品
主从复制架构
架构角色
mysql-master：192.168.198.139
mysql-slave：192.168.198.128
主数据库和从数据库需提前安装好mariadb数据库
[root@mysql-master ~]# yum -y install mariadb-server
[root@mysql-master ~]# iptables -F
mysql-master
[root@mysql-master ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1 \\定义server_id,每个数据库服务端都必须有唯一的识别ID
log_bin = master-log \\开启二进制日志记录
skip_name_resolve = ON \\禁止主机名解析
[root@mysql-master ~]# systemctl start mariadb
[root@mysql-master ~]# mysql
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 245 | | |
+-------------------+----------+--------------+------------------+
授权一个有权限做主从复制的用户
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.198.128' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
授权用户之后，二进制日志文件节点发生变化，并记录二进制文件的位置信息，用于从节点的复制起点
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 501 | | |
+-------------------+----------+--------------+------------------+
显示二进制文件的事件记录
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'master-log.000001';
+-------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------+
| master-log.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
| master-log.000001 | 245 | Query | 1 | 426 | GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.198.128' IDENTIFIED BY 'replpass' |
| master-log.000001 | 426 | Query | 1 | 501 | FLUSH PRIVILEGES |
+-------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------+
mysql-slave
[root@mysql-slave ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
relay-log = relay-log \\开启中继日志，并保存在/var/
server_id=5
skip_name_resolve = ON
read-only = ON \\设定slave数据库为只读状态，但仅对非supper用户有效
[root@mysql-slave ~]# systemctl start mariadb
[root@mysql-slave ~]# mysql
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'read_only'; \\查看数据库为只读
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
MariaDB [(none)]> HELP CHANGE MASTER TO 查看change master to 命令的帮助
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name' \\MASTER节点使用哪个连接接口
| MASTER_HOST = 'host_name' \\MASTER的主机名或地址
| MASTER_USER = 'user_name' \\连接主节点时使用的有复制权限的用户权限
| MASTER_PASSWORD = 'password' \\连接主节点的用户密码
| MASTER_PORT = port_num \\主节点的端口号
| MASTER_CONNECT_RETRY = interval \\主节点连接失败的重试间隔
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name' \\连接主节点的哪个二进制文件
| MASTER_LOG_POS = master_log_pos \\连接主节点二进制文件中的哪个起始位置
| RELAY_LOG_FILE = 'relay_log_name' \\复制完成后保存在本地哪个中继日志文件中
| RELAY_LOG_POS = relay_log_pos \\保存在中继日志的哪个位置
| MASTER_SSL = {0|1} \\配置使用SSL加密
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list) \\忽略的server_id节点
连接主节点数据库
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=501;
启动从节点复制进程
MariaDB [(none)]> START SLAVE;
查看SLAVE节点的状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.198.139
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001 主节点的二进制文件
Read_Master_Log_Pos: 501 主节点的二进制文件的位置
Relay_Log_File: relay-log.000002 中继日志文件的初始化
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.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: 501
Relay_Log_Space: 818
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: 1
测试主从复制的效果
mysql-master
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [(none)]> use mydb;
MariaDB [mydb]> CREATE TABLE tb1 (id INT,name CHAR(50));
MariaDB [mydb]> INSERT INTO tb1 VALUES (1,'dan');
mysql-slave
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.198.139
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 957
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 986
Relay_Master_Log_File: master-log.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: 957
Relay_Log_Space: 1274
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 \\表示从节点复制时与主节点数据同步的延时时间，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: 1
查看slave节点的数据库，是否实现数据复制
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
+--------------------+
MariaDB [(none)]> use mydb;
MariaDB [mydb]> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | dan |
+------+------+
测试非SUPPER用户在SLAVE节点上只读
mysql-master
授予test@192.168.198.%用户在testdb库中的权限
MariaDB [(none)]> GRANT SELECT,UPDATE,CREATE,DROP ON testdb.* TO 'test'@'192.168.198.%' IDENTIFIED BY 'testpass';
MariaDB [(none)]> CREATE database testdb;
MariaDB [(none)]> USE testdb;
MariaDB [testdb]> CREATE TABLE mytb1 (name CHAR(50));
使用普通用户test@192.168.198.%用户连接SLAVE数据库
[root@test ~]# mysql -utest -h192.168.198.128 -p
MariaDB [(none)]> use testdb;
MariaDB [testdb]> desc mytb1;;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
普通用户在SLAVE节点上只有只读权限
MariaDB [testdb]> INSERT INTO mytb1 VALUES ('danran');
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
注意：配置完成后先启动主节点
SLAVE节点上相应文件的信息
[root@mysql-slave mysql]# cat /var/lib/mysql/master.info \\记录了主节点上的连接的账号密码等信息
18
master-log.000001 \\复制主节点的二进制文件
501 \\复制时二进制文件的位置
192.168.198.139 \\主数据库的IP
repluser \\连接主节点的账号
replpass \\连接主节点的密码
3306 \\连接主节点的端口号
60
0
0
1800.000
0
[root@mysql-slave mysql]# cat /var/lib/mysql/relay-log.info \\记录了复制的二进制文件对应于本机的哪个中继日志文件
./relay-log.000001 \\保存到本地的中继日志文件
4 \\中继文件的位置
master-log.000001 \\复制的主节点二进制文件
501 \\二进制文件的位置
Mysql的双主复制架构
互为主从，即mysql-A为mysql-B的从节点，同时mysql-B也为mysql-A的从节点
角色
mysql-A：192.168.198.139
mysql-B：192.168.198.128
主数据库和从数据库需提前安装好mariadb数据库
[root@mysql-master ~]# yum -y install mariadb-server
[root@mysql-master ~]# iptables -F
mysql-A节点
[root@mysql-A ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1
log_bin = master-log
relay-log = relay-log
skip_name_resolve = ON
auto_increment_offset=1 \\定义自动增长ID的起始值为1
auto_increment_increment=2 \\定义自动增长ID的增长量为2
[root@mysql-A ~]# systemctl start mariadb
mysql-B节点
[root@mysql-B ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
[mysqld]
server_id=5
relay_log=relay-log
log-bin=master-log
skip_name_resolve = ON
auto_increment_offset=2 \\定义自动增长ID的起始值为2
auto_increment_increment=2 \\定义自动增长ID的增长量为2
[root@mysql-B ~]# systemctl start mariadb
mysql-A
mysql-A连接数据库并授权复制的用户权限
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.198.128' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
查看二进制日志文件位置，用于从节点复制时的起点位置
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 431 | | |
+-------------------+----------+--------------+------------------+
连接mysql-B主节点,即mysql-A作为mysql-B的从节点
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.128',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=431;
启动复制线程
MariaDB [(none)]> START SLAVE;
查看作为从节点的状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.198.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 617
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 716
Relay_Master_Log_File: master-log.000003
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: 617
Relay_Log_Space: 1004
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: 5
mysql-B
mysql-B连接数据库并授权复制的用户权限
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.198.139' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
查看二进制日志文件位置，用于从节点复制时的起点位置
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 431 | | |
+-------------------+----------+--------------+------------------+
连接mysql-A主节点,即mysql-B作为mysql-A的从节点
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=431;
启动复制线程
MariaDB [(none)]> START SLAVE;
查看作为从节点的状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.198.139
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 431
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
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: 431
Relay_Log_Space: 818
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: 1
测试双主复制
mysql-A
MariaDB [(none)]> CREATE DATABASE mydb;
mysql-B
MariaDB [(none)]> use mydb;
MariaDB [mydb]> CREATE TABLE tb1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name CHAR(50));
mysql-A
MariaDB [(none)]> use mydb;
MariaDB [mydb]> desc tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES ('wei'),('jevon');
mysql-B
MariaDB [mydb]> SELECT * FROM tb1;
+----+-------+
| id | name |
+----+-------+
| 1 | wei |
| 3 | jevon |
+----+-------+
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES ('dan'),('ran');
MariaDB [mydb]> SELECT * FROM tb1;
+----+-------+
| id | name |
+----+-------+
| 1 | wei |
| 3 | jevon |
| 4 | dan |
| 6 | ran |
+----+-------+
mysql-A
MariaDB [mydb]> SELECT * FROM tb1;
+----+-------+
| id | name |
+----+-------+
| 1 | wei |
| 3 | jevon |
| 4 | dan |
| 6 | ran |
+----+-------+
从节点的复制线程的启动会保存在mariadb.log错误日志中
[root@mysql-A ~]# tail /var/log/mariadb/mariadb.log
170914 10:11:20 [Note] Server socket created on IP: '0.0.0.0'.
170914 10:11:20 [Warning] 'user' entry 'root@danran.com' ignored in --skip-name-resolve mode.
170914 10:11:20 [Warning] 'user' entry '@danran.com' ignored in --skip-name-resolve mode.
170914 10:11:20 [Warning] 'proxies_priv' entry '@ root@danran.com' ignored in --skip-name-resolve mode.
170914 10:11:20 [Note] Event Scheduler: Loaded 0 events
170914 10:11:20 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.52-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
170914 10:35:57 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.198.128', master_port='3306', master_log_file='master-log.000003', master_log_pos='431'.
170914 10:37:59 [Note] Slave SQL thread initialized, starting replication in log 'master-log.000003' at position 431, relay log './relay-log.000001' position: 4
170914 10:38:00 [Note] Slave I/O thread: connected to master 'repluser@192.168.198.128:3306',replication started in log 'master-log.000003' at position 431
半同步复制构架
半同步复制的架构中，slave重启mysql服务时，global.rpL_semi_sync_slave_enabled变量不会自动启动，需要set @@global.rpL_semi_sync_slave_enabled=1;手动启动
半同步复制的插件程序包
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
半同步复制需要安装半同步复制的插件
mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
半同步复制模块：
semisync_master.so
semisync_slave.so
主数据库和从数据库需提前安装好mariadb数据库
[root@mysql-master ~]# yum -y install mariadb-server
[root@mysql-master ~]# iptables -F
mysql-master
[root@mysql-master ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1 \\定义server_id,每个数据库服务端都必须有唯一的识别ID
log_bin = master-log \\开启二进制日志记录
skip_name_resolve = ON \\禁止主机名解析
[root@mysql-master ~]# systemctl start mariadb
[root@mysql-master ~]# mysql
授权一个有权限做主从复制的用户
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.198.128' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
记录二进制文件的位置信息，用于从节点的复制起点
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 501 | | |
+-------------------+----------+--------------+------------------+
安装半同步复制master插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
显示所有插件
MariaDB [(none)]> SHOW PLUGINS; 显示rpl_semi_sync_master插件已安装
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
显示rpl_semi_sync_master模块相关的信息
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| 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 |
+------------------------------------+-------+
启用rpl_semi_sync_master模块
MariaDB [(none)]> SET @@GLOBAL.rpl_semi_sync_master_enabled=ON;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON | \\模块已启用
| rpl_semi_sync_master_timeout | 10000 | \\同步等待时间，默认10s
| rpl_semi_sync_master_trace_level | 32 | \\设置跟踪级别
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
显示rpl_semi_sync_master模块的状态变量
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'rpl%';
+--------------------------------------------+-------------+
| 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 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
mysql-slave
[root@mysql-slave ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
relay-log = relay-log \\开启中继日志，并保存在/var/
server_id=5
skip_name_resolve = ON
read-only = ON \\设定slave数据库为只读状态，但仅对非supper用户有效
[root@mysql-slave ~]# systemctl start mariadb
[root@mysql-slave ~]# mysql
连接主节点数据库
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=501;
启动从节点复制进程
[root@mysql-slave ~]# mysql
安装半同步复制slave插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave';
显示所有插件
MariaDB [(none)]> SHOW PLUGINS; 显示rpl_semi_sync_slave插件已安装
+--------------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+-------------------+---------+
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
显示rpl_semi_sync_slave模块相关的信息
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | OFF | \\未启用
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
启用rpl_semi_sync_slave模块
MariaDB [(none)]> SET @@GLOBAL.rpl_semi_sync_slave_enabled=ON;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
初次启动SLAVE
MariaDB [(none)]> START SLAVE
或重新启动IO_THREAD
MariaDB [(none)]> STOP SLAVE IO_THREAD; 停止I/O线程
MariaDB [(none)]> START SLAVE IO_THREAD; 再重新开启I/O线程
查看SLAVE节点的状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
测试半同步复制
mysql-master
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES ('dan'),('ran');
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES ('daa'),('qq');
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES ('sqw'),('wei');
MariaDB [mydb]> SHOW GLOBAL STATUS LIKE 'rpl%'; 状态变量记录发生变化
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 2230 | \\平均等待时长
| Rpl_semi_sync_master_net_wait_time | 6690 | \\等待时长
| Rpl_semi_sync_master_net_waits | 3 | \\网络等待的次数
| 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 | 669 | \\事务的等待时长
| Rpl_semi_sync_master_tx_wait_time | 669 | \\事务的等待次数
| Rpl_semi_sync_master_tx_waits | 1 | \\事务的平均等待时长
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
复制过滤器架构
mysql-master配置
[root@mysql-master ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1 \\定义server_id,每个数据库服务端都必须有唯一的识别ID
log_bin = master-log \\开启二进制日志记录
skip_name_resolve = ON \\禁止主机名解析
[root@mysql-master ~]# systemctl restart mariadb
[root@mysql-master ~]# mysql
授权一个有权限做主从复制的用户
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.198.128' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
查看二进制日志文件位置，用于从节点复制时的起点位置
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000004 | 2223 | | |
+-------------------+----------+--------------+------------------+
mysql-slave
[root@mysql-slave ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=5
relay_log=relay-log
skip_name_resolve = ON
read-only = ON
replicate_do_db = hidb
[root@mysql-slave ~]# systemctl restart mariadb \\重启mariadb服务，则复制线程自动重启
[root@mysql-slave ~]# mysql
显示SLAVE的状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.198.139
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000004
Read_Master_Log_Pos: 2223
Relay_Log_File: relay-log.000005
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000004
Slave_IO_Running: Yes \\重启mariadb服务，则复制线程自动重启
Slave_SQL_Running: Yes \\重启mariadb服务，则复制线程自动重启
Replicate_Do_DB: hidb \\过滤hidb数据库记录于二进制日志文件
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: 2223
Relay_Log_Space: 818
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: 1
若复制线程Slave_IO/Slave_SQL没有启动，则手动启动
连接主节点数据库
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=501;
启动从节点复制进程
MariaDB [(none)]> START SLAVE;
测试复制过滤器
mysql-master
MariaDB [(none)]> CREATE DATABASE hidb;
MariaDB [mydb]> CREATE TABLE tb2 (name VARCHAR(20));
mysql-slave
MariaDB [mydb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hidb |
| mydb |
| mysql |
| performance_schema |
+--------------------+
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1 |
+----------------+
最终实现的是，SLAVE数据库服务端Replicate_Do_Table指定的hidb表可以复制到二进制日志文件中实现数据复制，不在此表中的数据并不会从中继日志文件传送到到二进制文件中，从而不能实现数据复制
主从复制架构之SSL加密
- ssl的复制是什么原理呢？
主服务器向CA申请一个自己的证书，从服务器也向相同的CA申请一个证书。同时主服务器和从服务器都拿了CA的公钥，这样就可以实现数据加密解密了。
实现流程如下
主服务器的证书情况如下：
[root@centos7 ssl]#chown -R mysql.mysql /var/lib/mysql/ssl/*
[root@centos7 ssl]#ll
total 20
-rw-r--r-- 1 mysql mysql 1257 Sep 14 16:10 cacert.pem//CA 的公钥文件
-rw-r--r-- 1 mysql mysql 4353 Sep 14 16:09 master.crt
-rw-r--r-- 1 mysql mysql 972 Sep 14 16:06 master.csr
-rw------- 1 mysql mysql 1675 Sep 14 16:05 master.key
从服务器的证书情况
[root@cnetos7 ssl]#chown -R mysql.mysql /var/lib/mysql/ssl/*
[root@cnetos7 ssl]#ll
总用量 20
-rw-r--r--. 1 mysql mysql 1257 9月 14 16:27 cacert.pem
-rw-r--r--. 1 mysql mysql 4394 9月 14 16:26 slave.crt
-rw-r--r--. 1 mysql mysql 997 9月 14 16:24 slave.csr
-rw-------. 1 mysql mysql 1675 9月 14 16:15 slave.key
主服务器mysql配置
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log_bin = master-log
innodb_file_per_table = ON
skip_name_resolve = ON
ssl
ssl-ca = /var/lib/mysql/ssl/cacert.pem
ssl-cert = /var/lib/mysql/ssl/master.crt
ssl-key = /var/lib/mysql/ssl/master.key
[root@centos7 ~]#systemctl start mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%'; //验证主节点配置文件是否成功
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/master.key |
+---------------+-------------------------------+
出现以上信息表示成功
从服务器上的配置
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 2
relay_log = relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
ssl
ssl-ca = /var/lib/mysql/ssl/cacert.pem
ssl-cert = /var/lib/mysql/ssl/slave.crt
ssl-key = /var/lib/mysql/ssl/slave.key
[root@centos7 ~]#systemctl start mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%'; //验证从节点配置文件是否成功
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/slave.key |
+---------------+-------------------------------+
出现以上信息表示成功，
主服务器上创建复制用户
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.18.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> SHOW MASTER STATUS;
从节点连接主节点
MariaDB [(none)]> help change master to查看连接配置帮助
（1）CHANGE MASTER TO MASTER_HOST='192.168.18.131', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=499,MASTER_SSL=1,MASTER_SSL_CA='/var/lib/mysql/ssl/cacert.pem',MASTER_SSL_CERT='/var/lib/mysql/ssl/slave.crt',MASTER_SSL_KEY='/var/lib/mysql/ssl/slave.key';
（2）start slave;
（3）show slave status\G查看是否连接成功
查看是否进行了ssl加密
MariaDB [(none)]> status;
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 //可以看到是于SSL加密成功了。
danran
