20190225 实验：实现基于SSL加密的主从复制、主主复制、半同步、复制过滤器

原创

清欢难寻 2019-02-26 18:00:19 ©著作权

©著作权归作者所有：来自51CTO博客作者清欢难寻的原创作品，请联系作者获取转载授权，否则将追究法律责任

实验：实现基于SSL加密的主从复制实验步骤：环境：三台主机，一台CA：200，一台master：150 ，一台slave：100 平时都是在CA上帮用户生成私钥，在服务器上做的 1 CA,master,slave 的证书相关文件 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 给master 颁发证书 openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr openssl x509 -req -in master.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt 给slave 颁发证书 openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr openssl x509 -req -in slave.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt

root@CA ssl]#ls /etc/my.cnf.d/ssl/ cacert.pem cakey.pem master.crt master.csr master.key slave.crt slave.csr slave.key

scp -r /etc/my.cnf.d/ssl master:/etc/my.cnf.d/

scp -r /etc/my.cnf.d/ssl slave:/etc/my.cnf.d/

2 master vim /etc/my.cnf [mysqld] log-bin server-id=17 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

mysql> grant replication slave on . to ssluser@'192.168.35.%' identified by 'centos' require ssl ;

3 slave

vim /etc/my.cnf [mysqld] server-id=27 read-only

mysql> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='ssluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=7821, 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'; mysql>start slave;

1、150主机修改配置文件：加入“log_bin server_id=150” 100主机修改配置文件：加入“server_id=100 read_only” 150master:[root@centos7 ~]#vim /etc/my.cnf [root@centos7 ~]#systemctl restart mariadb [root@centos7 ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave on . to repluser@'192.168.141.%' identified by '123gxy'; 创建用户 Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 401 | +--------------------+-----------+ 1 row in set (0.00 sec) 100slave：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.141.150', MASTER_USER='repluser', MASTER_PASSWORD='123gxy', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245; Query OK, 0 rows affected (0.02 sec)

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

MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.141.150 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 687 Relay_Master_Log_File: mariadb-bin.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: 401 Relay_Log_Space: 983 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: 150 1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> select user,host from mysql.user; +----------+---------------+ | user | host | +----------+---------------+ | root | 127.0.0.1 | | repluser | 192.168.141.% | | root | ::1 | | root | localhost | +----------+---------------+ 4 rows in set (0.00 sec) 可见此时已经连接master。 2、[root@CA ~]#mkdir /etc/my.cnf.d/ssl 专门存放证书 [root@CA ssl]#openssl genrsa 2048 > cakey.pem 生成CA的私钥 Generating RSA private key, 2048 bit long modulus .....................................................................................................+++ .................................+++ e is 65537 (0x10001) [root@CA ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.

Country Name (2 letter code) [XX]:cn State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:huayixiongdi Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:ca.magedu.com Email Address []: [root@CA ssl]#ll 这是CA给自己颁发的自签名的证书 total 8 -rw-r--r--. 1 root root 1346 Feb 26 15:59 cacert.pem -rw-r--r--. 1 root root 1679 Feb 26 15:56 cakey.pem [root@CA ssl]#openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr 生成master的私钥文件 Generating a 2048 bit RSA private key .........................+++ ....................................................+++ writing new private key to 'master.key'

You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.

Country Name (2 letter code) [XX]:cn State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:huayixiongdi Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:master.huayixiongdi.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@CA ssl]#ll total 16 -rw-r--r--. 1 root root 1346 Feb 26 15:59 cacert.pem -rw-r--r--. 1 root root 1679 Feb 26 15:56 cakey.pem -rw-r--r--. 1 root root 1029 Feb 26 16:11 master.csr -rw-r--r--. 1 root root 1708 Feb 26 16:11 master.key 3、颁发CA证书： [root@CA ssl]#openssl x509 -req -in master.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt Signature ok subject=/C=cn/ST=beijing/L=beijing/O=huayixiongdi/OU=devops/CN=master.huayixiongdi.com Getting CA Private Key [root@CA ssl]#ll total 20 -rw-r--r--. 1 root root 1346 Feb 26 15:59 cacert.pem -rw-r--r--. 1 root root 1679 Feb 26 15:56 cakey.pem -rw-r--r--. 1 root root 1233 Feb 26 16:15 master.crt -rw-r--r--. 1 root root 1029 Feb 26 16:11 master.csr -rw-r--r--. 1 root root 1708 Feb 26 16:11 master.key 有三个文件 [root@CA ssl]#openssl x509 -req -in slave.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > slave.crt Signature ok subject=/C=cn/ST=beijing/L=beijing/O=huayixiongdi/OU=devops/CN=slave.huayixiongdi.com Getting CA Private Key [root@CA ssl]#ll total 32 -rw-r--r--. 1 root root 1346 Feb 26 15:59 cacert.pem -rw-r--r--. 1 root root 1679 Feb 26 15:56 cakey.pem -rw-r--r--. 1 root root 1233 Feb 26 16:15 master.crt -rw-r--r--. 1 root root 1029 Feb 26 16:11 master.csr -rw-r--r--. 1 root root 1708 Feb 26 16:11 master.key -rw-r--r--. 1 root root 1229 Feb 26 16:51 slave.crt -rw-r--r--. 1 root root 1029 Feb 26 16:18 slave.csr -rw-r--r--. 1 root root 1704 Feb 26 16:18 slave.key 主服务器此时没开启加密： MariaDB [(none)]> show variables like'%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+ 7 rows in set (0.00 sec)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status

mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 7 Current database:
Current user: ssluser@192.168.141.100 SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.60-MariaDB MariaDB Server Protocol version: 10 Connection: 192.168.141.150 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 8 min 25 sec 普通登录： [root@centos7 ssl]#mysql -ussluser -p123gxy -h 192.168.141.150 ERROR 1045 (28000): Access denied for user 'ssluser'@'192.168.141.100' (using password: YES) [root@centos7 ssl]#mysql -urepluser -p123gxy -h 192.168.141.150 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status;

mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 9 Current database:
Current user: repluser@192.168.141.100 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.60-MariaDB MariaDB Server Protocol version: 10 Connection: 192.168.141.150 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 11 min 7 sec 5、master：MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 420 | | mariadb-bin.000002 | 264 | | mariadb-bin.000003 | 264 | | mariadb-bin.000004 | 7897 | +--------------------+-----------+ 4 rows in set (0.00 sec) 已经7897了 MariaDB [(none)]> stop slave； Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> reset slave all; Query OK, 0 rows affected (0.01 sec) 关闭清空slave的复制重新建：在slave： MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.141.150', -> MASTER_USER='ssluser', -> MASTER_PASSWORD='123gxy', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000004', -> MASTER_LOG_POS=7897, -> 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'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.141.150 Master_User: ssluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 7897 Relay_Log_File: mariadb-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mariadb-bin.000004 Slave_IO_Running: No 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_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 7897 Relay_Log_Space: 245 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key Seconds_Behind_Master: NULL 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: 150 1 row in set (0.00 sec) 这时，开启从服务器： DB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.141.150 Master_User: ssluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 7897 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000004 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: 7897 Relay_Log_Space: 827 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /etc/my.cnf.d/ssl/slave.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: 150 在master上创建用户： MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.00 sec) slave上马上回显示： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec) 至此，本实验结束！

实验：主主复制

前提准备：2台主服务器，200,150；1台从服务器，100 200主机： [root@master mysql]#rm -rf /var/lib/mysql/* [root@master mysql]#systemctl restart mariadb [root@master mysql]#vim /etc/my.cnf [mysqld] log_bin server_id=1 auto_increment_offset=1 auto_increment_increment=2 [root@master mysql]#systemctl restart mariadb 150主机： [root@master mysql]#rm -rf /var/lib/mysql/* [root@master mysql]#systemctl restart mariadb [root@master mysql]#vim /etc/my.cnf [mysqld] log_bin server_id=1 auto_increment_offset=2 auto_increment_increment=2 [root@master mysql]#systemctl restart mariadb 200主机： MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> grant replication slave on . to repluser@'192.168.141.%' identified by '123gxy'; Query OK, 0 rows affected (0.00 sec) 150主机： MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> grant replication slave on . to repluser@'192.168.141.%' identified by '123gxy'; Query OK, 0 rows affected (0.00 sec) 此时，200和150的masterlogs都已更新： MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 406 | +--------------------+-----------+ 1 row in set (0.00 sec) 200主机： MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.141.150', 指向150master主机 -> MASTER_USER='repluser', -> MASTER_PASSWORD='123gxy', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=406； Query OK, 0 rows affected (0.02 sec) 150主机： MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.141.200', 指向200master主机 -> MASTER_USER='repluser', -> MASTER_PASSWORD='123gxy', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001‘； -> MASTER_LOG_POS=406； Query OK, 0 rows affected (0.01 sec) 此时，看150、200的slave status： MariaDB [(none)]> start slave; 200的 Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.141.150 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 406 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.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: 406 Relay_Log_Space: 827 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: 2 1 row in set (0.00 sec) MariaDB [(none)]> start slave; 150的 Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.141.200 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 406 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.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: 406 Relay_Log_Space: 827 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 1 row in set (0.00 sec) 200主机： MariaDB [(none)]> use test; Database changed MariaDB [test]> create table t1 (id int auto_increment primary key,name char(30)); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select * from t1; 创建的t1表是空的。 Empty set (0.00 sec) 150主机： MariaDB [test]> select * from t1; 同步也是空的。 Empty set (0.00 sec) 200主机： MariaDB [test]> insert t1 (name)values('a'); Query OK, 1 row affected (0.01 sec) MariaDB [test]> select * from t1; +----+------+ | id | name | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) 150主机： MariaDB [test]> insert t1 (name)values('b'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from t1; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) 回到200： MariaDB [test]> select * from t1; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) 此时，本实验结束。

实验：半同步

实前提准备：先要清空 [root@centos7 ~]#rm -rf /var/lib/mysql/* [root@centos7 ~]#systemctl restart mariadb 先实现主从复制，再实现半同步 1台master 200 1台slave 150 本实验要用到插件：[root@centos7 ~]#rpm -ql mariadb-server /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so 200主机： MariaDB [(none)]> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> grant replication slave on . to repluser@'192.168.141.%' identified by '123gxy'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 401 | +--------------------+-----------+ 1 row in set (0.00 sec) 150主机： MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.141.200', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123gxy', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245； Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.141.200 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 687 Relay_Master_Log_File: mariadb-bin.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: 401 Relay_Log_Space: 983 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 1 row in set (0.00 sec) 200主机：安装插件 MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME-> 'semisync_master.so'; uninstall是卸载 Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show plugins; rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | 此记录会产生。 MariaDB [(none)]> show 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 | +------------------------------------+-------+ 4 rows in set (0.00 sec) MariaDB [(none)]> show 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 | +--------------------------------------------+-------+ 14 rows in set (0.01 sec) [root@centos7 ~]#vim /etc/my.cnf 写入文件永久生效。 [mysqld] log_bin rpl_semi_sync_master_enabled server_id=1 [root@centos7 ~]#systemctl restart mariadb MariaDB [(none)]> show variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | 此时为开启 | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) 此时，200master已实现半同步了。接下来我们配置slave即可：执行统一的操作： MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAM-> 'semisync_slave.so'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.01 sec) 配置文件：[root@centos7 ~]#vim /etc/my.cnf rpl_semi_sync_slave_enabled [root@centos7 ~]#systemctl restart mariadb MariaDB [(none)]> show variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | 从节点的插件已安装完毕。 | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.01 sec) 200主机： MariaDB [hellodb]> insert teachers(name,age)values('mage',20); Query OK, 1 row affected (0.01 sec) MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | mage | 20 | NULL | +-----+---------------+-----+--------+ 5 rows in set (0.00 sec) 150主机： MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | mage | 20 | NULL | +-----+---------------+-----+--------+ 5 rows in set (0.00 sec) 同步的那是相当的快啊！为了看效果，我们把slave stop掉： MariaDB [hellodb]> insert teachers(name,age)values('huge',10); | 此时会卡住，出现闪烁 10s后，会自动成功；Query OK, 1 row affected (10.00 sec) MariaDB [hellodb]> insert teachers(name,age)values('lipi',40); Query OK, 1 row affected (0.00 sec) 这时，start slave，会看到： MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | mage | 20 | NULL | | 6 | huge | 10 | NULL | | 7 | lipi | 40 | NULL | +-----+---------------+-----+--------+ 7 rows in set (0.00 sec) slave服务器会自动同步。至此，本实验完毕！！

基于本实验我们也可定制黑名单白名单，即可被记录二进制的文件和不想被记录的二进制日志的文件在200主机： [root@centos7 ~]#vim /etc/my.cnf binlog_ignore_db=test [root@centos7 ~]#systemctl start mariadb MariaDB [test]> create table t1(i int); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) 在150主机： MariaDB [hellodb]> use test Database changed MariaDB [test]> show tables; Empty set (0.00 sec) 可见master上可显示，slave上不可见，因为我们设置了不记录test的二进制日志，自然也就不复制了。 200主机上的黑名单是“test” MariaDB [(none)]> show master status; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000003 | 245 | | test | +--------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 至此，我们的实验暂告一段落。