Mysql的ssl主从复制+半同步主从复制
准备工作
1、主从服务器时间同步
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 172.16.0.1 &>/dev/null
?
?
MariaDB（10以上版本）的编译安装
部署配置
2、mysql说明
(1) 主服务器
hostname:master ? ?IP：172.16.21.2
?
(1) 从服务器
hostname:master ? ?IP：172.16.21.3
?
(3) 数据目录
?
/mydata/data
?
(4) 二进制日志目录
/mydata/binlogs
?
(5) 中继日志目录
/mydata/relaylogs
?
?
?
主库配置
vi /etc/my.cnf
????server-id = 10 # 在复制架构中，需保持全局唯一
????log-bin = /mydata/binlogs/mysql-bin # 默认在数据目录下
sync_binlog = 1 # 设置mariadb每次在提交事务前会将二进制日志同步到磁盘，保证服务器崩溃时不会丢失事件
?
?
=====
service mysqld start # 启动mariadb10
=====
mysql -hlocalhost -uroot -p # 登录mysql
MariaDB [mysql]> grant replication slave,replication client on *.* to ‘repluser‘@‘172.16.%.%‘ identified by ‘replpass‘; # 创建最小权限的复制账号
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> show master status; # 查看主库的状态信息
?
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 663 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
?
?
?
从库配置
?
vi /etc/my.cnf
????server-id = 20 # 在复制架构中，需保持全局唯一
????log-bin = /mydata/binlogs/mysql-bin # 也可设置为none，即关闭从库的二进制日志
????relay-log=/mydata/relaylogs/relay-bin # 设置中继日志文件
????log-slave-updates = 1 # 允许从库将其重放的事件也记录到自身的二进制日志中
????read_only = 1 # 从库设置为只读
=====
service mysqld start # 启动mariadb10
=====
mysql -hlocalhost -uroot -p # 登录mysql
MariaDB [mysql]> change master to master_host=‘172.16.21.2‘,master_user=‘repluser‘,master_password=‘replpass‘,master_log_file=‘master-bin.000002‘,master_log_pos=663; # 连接主库
MariaDB [mysql]> start slave;
MariaDB [mysql]> show slave status\G 查看从库状态
?
验证
?
# 在主库上新建数据库并创建数据
MariaDB [(none)]> create database test_for_replication;
MariaDB [(none)]> create table test_for_replication.user(id int not null primary key auto_increment,name char(20) not null,year int not null,classid int not null);
MariaDB [(none)]> insert into test_for_replication.user(name,year,classid) values(‘Jason Kk‘,23,2),(‘Hello Kitty‘,18,1);
# 查看从库能否正常同步数据
MariaDB [(none)]> select * from test_for_replication.user; # 见下图1
MariaDB [(none)]> show slave status\G # 见下图2
图 1：
?
?
图 2 :
?
?
可以看出主从同步正常，数据无误！
?
三、SSL主从同步的实现
1、将master（172.16.7.202）做为CA服务器
[root@localhost ~]# cd /etc/pki/CA/
[root@localhost CA]# ls
certs crl newcerts private
[root@localhost CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
.................................................................+++
......................................................................................+++
e is 65537 (0x10001)
?
?
[root@localhost CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:changsheng
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server‘s hostname) []:changsheng
Email Address []:
?
?
[root@localhost CA]# touch index.txt serial crlnumber
[root@localhost CA]# echo 01 > serial
?
?
master（172.16.21.2）签发证书
?
[root@localhost CA]# mkdir /usr/local/mysql/ssl
[root@localhost CA]# cd /usr/local/mysql/ssl
[root@localhost ssl]# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
..................................+++
..........+++
e is 65537 (0x10001)
?
?
?
[root@localhost ssl]# openssl req -new -key master.key -out master.csr -days 36500
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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:changsheng
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server‘s hostname) []:changsheng
Email Address []:
?
Please enter the following ‘extra‘ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
?
?
?
?
?
[root@localhost ssl]# openssl ca -in master.csr -out master.crt -days 36500
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Jan 25 03:42:29 2015 GMT
Not After : Jan 1 03:42:29 2115 GMT
Subject:
countryName = CN
stateOrProvinceName = HA
organizationName = changsheng
organizationalUnitName = tech
commonName = changsheng
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
5D:CB:5F:32:BB:24:6C:6F:4B:23:92:11:7D:FC:C1:9B:2B:57:50:E4
X509v3 Authority Key Identifier:
keyid:22:1F:2F:97:5B:70:84:F9:5C:BE:7E:7E:49:F3:CE:47:00:6D:19:61
?
Certificate is to be certified until Jan 1 03:42:29 2115 GMT (36500 days)
Sign the certificate? [y/n]:y
?
?
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
?
?
?
Slave(172.16.21.3)生成证书申请请求
?
?
[root@localhost ~]# mkdir /usr/local/mysql/ssl
[root@localhost ~]# cd /usr/local/mysql/ssl
[root@localhost ssl]# ls
[root@localhost ssl]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
..........+++
....................................+++
e is 65537 (0x10001)
[root@localhost ssl]# openssl req -new -key slave.key -out slave.csr -days 36500
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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:changsheng
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server‘s hostname) []:changsheng
Email Address []:
?
Please enter the following ‘extra‘ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
?
[root@localhost ssl]# ls
slave.csr slave.key
[root@localhost ssl]# scp slave.csr root@172.16.21.2:/root
?
?
Master (172.16.21.2)向slave（172.16.7.250）签发证书
?
在主节点（172.16.21.2）:
?
---------------------------------------------------------------------------------------------------------------------------------
[root@localhost ~]# openssl ca -in slave.csr -out slave.crt -days 36500
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Jan 25 03:51:18 2015 GMT
Not After : Jan 1 03:51:18 2115 GMT
Subject:
countryName = CN
stateOrProvinceName = HA
organizationName = changsheng
organizationalUnitName = tech
commonName = changsheng
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
BF:B2:EB:07:56:20:17:07:D7:CB:47:44:07:A7:75:48:68:F1:CF:A1
X509v3 Authority Key Identifier:
keyid:22:1F:2F:97:5B:70:84:F9:5C:BE:7E:7E:49:F3:CE:47:00:6D:19:61
?
Certificate is to be certified until Jan 1 03:51:18 2115 GMT (36500 days)
Sign the certificate? [y/n]:y
?
?
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
?
?
[root@localhost ~]# scp slave.crt root@172.16.21.3:/usr/local/mysql/ssl/
The authenticity of host ‘172.16.21.3 (172.16.21.3)‘ can‘t be established.
RSA key fingerprint is 4a:47:8c:1b:c9:52:74:38:80:23:05:e4:27:0a:60:d0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘172.16.21.3‘ (RSA) to the list of known hosts.
root@172.16.21.3‘s password:
slave.crt
?
?
---------------------------------------------------------------------------------------------------------------------------------
?
?
master及slave提供CA的证书
?
还是在master这个节点上(172.16.21.2):
--------------------------------------------------------------------------------------------------------
?
[root@localhost ~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
[root@localhost ~]# cd /usr/local/mysql/ssl/
[root@localhost ssl]# ls
cacert.pem master.crt master.csr master.key
?
?
[root@localhost ~]# scp /etc/pki/CA/cacert.pem root@172.16.21.3:/usr/local/mysql/ssl/
root@172.16.21.3‘s password:
cacert.pem 100% 1306 1.3KB/s 00:00
[root@localhost ~]#
?
?
?
?
查看slave 节点(172.16.21.3)
?
[root@localhost ssl]# pwd
/usr/local/mysql/ssl
[root@localhost ssl]# ls
cacert.pem slave.crt slave.csr slave.key
[root@localhost ssl]#
?
---------------------------------------------------------------------------------------------------------------------------------
?
改master和slave的属主、属组为"mysql"
?
Master(172.16.21.2)
?
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@localhost ~]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1306 Jan 25 11:56 cacert.pem
-rw-r--r-- 1 mysql mysql 4431 Jan 25 11:42 master.crt
-rw-r--r-- 1 mysql mysql 993 Jan 25 11:41 master.csr
-rw------- 1 mysql mysql 1679 Jan 25 11:39 master.key
?
?
Slave(172.16.21.3)
?
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@localhost ~]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1306 Jan 25 11:57 cacert.pem
-rw-r--r-- 1 mysql mysql 4432 Jan 25 11:52 slave.crt
-rw-r--r-- 1 mysql mysql 997 Jan 25 11:46 slave.csr
-rw------- 1 mysql mysql 1675 Jan 25 11:45 slave.key
?
?
?
?
?
?
改mysql配置文件开启SSL加密功能
?
Master(172.16.21.2)
-----------------------------------------------------------------------------------------------
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_key = /usr/local/mysql/ssl/master.key
ssl_cert = /usr/local/mysql/ssl/master.crt
[root@localhost ~]## service mysqld restart
#
#
Slave(172.16.21.3)
--------------------------------------------------------------------------------------
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_key = /usr/local/mysql/ssl/slave.key
ssl_cert = /usr/local/mysql/ssl/slave.crt
[root@localhost ~]# service mysqld restart
?
?
?
?
?
Master(172.16.21.2)上验证SSL加密功能开启并创建基于密钥认证用户
[root@localhost ~]# mysql
MariaDB [(none)]> show variables like ‘%ssl%‘;
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/slave.key |
+---------------+---------------------------------+
9 rows in set (0.00 sec)
?
?
MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘172.16.%.%‘ identified by ‘replpass‘ require ssl;
Query OK, 0 rows affected (0.00 sec)
?
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
?
?
查看master（172.16.21.2）状态信息
?
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 681 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
?
?
?
?
?
?
?
验证slave开启SSL加密功能
?
[root@localhost ~]# mysql
MariaDB [(none)]> show variables like ‘%ssl%‘;
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/slave.key |
+---------------+---------------------------------+
9 rows in set (0.00 sec)
?
?
slave连接master
-------------------------------------------------------------------------------------------------------
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> change master to master_host=‘172.16.21.2‘,master_user=‘repluser‘,master_password=‘replpass‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=681,master_ssl=1,master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘,master_ssl_cert=‘/usr/local/mysql/ssl/slave.crt‘,master_ssl_key=‘/usr/local/mysql/ssl/slave.key‘;
?
Query OK, 0 rows affected (0.07 sec)
?
MariaDB [(none)]> start slave;
?
MariaDB [(none)]> show slave status\G # 查看从库状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.21.2
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 681
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.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: 681
Relay_Log_Space: 826
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/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: 10
Master_SSL_Crl: /usr/local/mysql/ssl/cacert.pem
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
?
?
?
?
?
?
验证：
?
# 主库写入：
MariaDB [test]> create table t1(name char(20) not null ,age int not null);
# 从库读取；
MariaDB [(none)]> show tables in test;
?
?
?
?
?
?
?
?
?
?
上面的操作是实现好了，到这里异步的主从复制到这里配置完成。下面我们来说一下什么是半同步复制（或说是同步也行）。
-------------------------------------------------------------------------------------------------------------
实验第二部分
-------------------------------------------------------------------------------------------------------------------
?
Mysql 主从复制（半同步）
?
1.半同步复制
? ? ? ?在说明半同步复制之前我们先来了解一下，什么是同步复制？同步复制：同步复制可以定义为数据在同一时刻被提交到一台或多台机器，通常这是通过众所周知的"两阶段提交"做到的。虽然这确实给你在多系统中保持一致性，但也由于增加了额外的消息交换而造成性能下降。使用MyISAM或者InnoDB存储引擎的MySQL本身并不支持同步复制，然而有些技术，例如分布式复制块设备（简称DRBD），可以在下层的文件系统提供同步复制，允许第二个MySQL服务器在主服务器丢失的情况下接管（使用第二服务器的复本）。了解了同步复制我们正下面来说一下，什么是半同步复制？
? ? ? ?MYSQL 5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。Semisynchronous Replication(半同步复制)则一定程度上保证提交的事务已经传给了至少一个备库。Semi synchronous中，仅仅保证事务的已经传递到备库上，但是并不确保已经在备库上执行完成了。
? ? ? ?此外，还有一种情况会导致主备数据不一致。在某个session中，主库上提交一个事务后，会等待事务传递给至少一个备库，如果在这个等待过程中主库Crash，那么也可能备库和主库不一致，这是很致命的。如果主备网络故障或者备库挂了，主库在事务提交后等待10秒（rpl_semi_sync_master_timeout的默认值）后，就会继续。这时，主库就会变回原来的异步状态。
MySQL在加载并开启Semi-sync插件后，每一个事务需等待备库接收日志后才返回给客户端。如果做的是小事务，两台主机的延迟又较小，则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。
2.异步与半同步异同
? ? ? ?默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到Slave之上。异步操作虽然效率高，但是在Master/Slave出现问题的时候，存在很高数据不同步的风险，甚至可能丢失数据。
? ? ? ?MySQL5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据是完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台salve追赶上之后，继续切换到半同步模式。
3.具体配置
注，mysql5.5半同步插件是由谷歌提供，具体位置/usr/local/mysql/lib/plugin/下，一个是master用的semisync_master.so，一个是slave用的semisync_slave.so，下面我们就来具体配置一下。
Master(172.16.21.2)
--------------------------------------------------------------------------------------------------
(1).安装插件
?
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;?
Query OK, 0 rows affected (0.39 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
?
?
(2).修改配置文件
?
?
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1 #启用半同步
rpl_semi_sync_master_timeout=1000 #超时时间为1s
?
?
?
?
(3).重新启动服务
?
[root@localhost ~]# service mysqld restart
Shutting down MySQL... SUCCESS!??
Starting MySQL.. SUCCESS!
?
?
?
?
?
?
?
?
Slave(172.16.21.3):
?
(1).安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;?
Query OK, 0 rows affected (0.38 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;?
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
?
?
?
?
?
?
(2).修改配置文件
?
?
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled=1? #启用半同步复制
?
?
?
?
(3).重新启动服务
?
[root@localhost ~]# service mysqld restart
?
?
?
4.查看一下状态
?
?
Master(172.16.21.2):
?
?
mysql> SHOW GLOBAL STATUS LIKE ‘rpl_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 |
?
| 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.00 sec)
?
?
?
Slave(172.16.21.3):
?
mysql> SHOW GLOBAL STATUS LIKE ‘rpl_semi%‘;
+----------------------------+-------+?
| Variable_name????????????? | Value |?
+----------------------------+-------+?
| Rpl_semi_sync_slave_status | ON??? |?
+----------------------------+-------+?
1 row in set (0.01 sec)
?
?
5.测试一下
?
?
Master(172.16.21.2):
?
mysql> create table user (id int(10));
Query OK, 0 rows affected (0.42 sec)
mysql> show tables;
+----------------+?
| Tables_in_mydb |?
+----------------+?
| user?????????? |?
+----------------+?
1 row in set (0.00 sec)
mysql> insert user value (1);
Query OK, 1 row affected (0.34 sec)
?
?
注，大家可以看到创建一个表的插入一个数据的时间都很长，说明半同步配置完成。
?
?
?
?
?
6.模拟一下故障
?
?
?
?
?
?
Slave(172.16.21.3):
?
?
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
master:
mysql> create table user1 (id int(10));
Query OK, 0 rows affected (1.03 sec)
?
?
注，大家可以看到主服务器会卡1s，我们超时时间设置的为1s
?
?
7.查看一下状态
?
?
mysql> SHOW GLOBAL STATUS LIKE ‘rpl_semi%‘;
?
+--------------------------------------------+-------+
?
| Variable_name | Value |
?
+--------------------------------------------+-------+
?
| Rpl_semi_sync_master_clients | 1 |
?
| Rpl_semi_sync_master_net_avg_wait_time | 1560 |
?
| Rpl_semi_sync_master_net_wait_time | 10920 |
?
| Rpl_semi_sync_master_net_waits | 7 |
?
| Rpl_semi_sync_master_no_times | 1 |
?
| Rpl_semi_sync_master_no_tx | 1 |
?
| Rpl_semi_sync_master_status | OFF |
?
| Rpl_semi_sync_master_timefunc_failures | 0 |
?
| Rpl_semi_sync_master_tx_avg_wait_time | 985 |
?
| Rpl_semi_sync_master_tx_wait_time | 985 |
?
| 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 | 6 |
?
+--------------------------------------------+-------+
?
14 rows in set (0.00 sec)
?
mysql> STOP SLAVE IO_THREAD;
?
Query OK, 0 rows affected (0.01 sec)
?
mysql> SHOW GLOBAL STATUS LIKE ‘rpl_semi%‘;
?
+----------------------------+-------+
?
| Variable_name | Value |
?
+----------------------------+-------+
?
| Rpl_semi_sync_slave_status | OFF |
?
+----------------------------+-------+
?
1 row in set (0.00 sec)
?
?
?
好了，到这里我们就配置完成了半同步复制。
?
?
?
?
?
?
?
注，在主-从架构上建议使用的配置
master:
????sync_binlog=1 # 立刻同步binlog
innodb_flush_logs_at_trx_commit=1 #立刻刷新innodb日志
slave:
skip_slave_start=1 #设置开机不同步
read_only=1 #设置为只读
?
Mysql 复制过滤
????master:
????????binlog-do-db=mydb
binlog-ignore-db=mysql
slave:
replicate_do_db
rpplicate_ignore_db
replicate_do_table
replicate_ignore_table
replicate_wild_do_table
replicate_wild_ignore_table
?
测试一下：
在从服务器上只复制testdb一个数据库
?
slave:
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
replicate_do_db=testdb
replicate_do_db=mysql
[root@localhost ~]# service mysqld restart
master:
mysql> create database mydb1;
Query OK, 1 row affected (0.34 sec)
mysql> show databases;
+--------------------+?
| Database?????????? |?
+--------------------+?
| information_schema |?
| mydb?????????????? |?
| mydb1????????????? |?
| mysql????????????? |?
| performance_schema |?
| test?????????????? |?
+--------------------+?
6 rows in set (0.00 sec)
?
slave:
mysql> show databases;
+--------------------+?
| Database?????????? |?
+--------------------+?
| information_schema |?
| mydb?????????????? |?
| mysql????????????? |?
| performance_schema |?
| test?????????????? |?
+--------------------+?
5 rows in set (0.00 sec)
注，大家可以看到没有同步mydb1，再测试一下。
master:
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+?
| Database?????????? |?
+--------------------+?
| information_schema |?
| mydb?????????????? |?
| mydb1????????????? |?
| mysql????????????? |?
| performance_schema |?
| test?????????????? |?
| testdb???????????? |?
+--------------------+?
7 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+?
| Database?????????? |?
+--------------------+?
| information_schema |?
| mydb?????????????? |?
| mysql????????????? |?
| performance_schema |?
| test?????????????? |?
| testdb???????????? |?
+--------------------+?
6 rows in set (0.00 sec)
?
大家可以看到同步了testdb
原文：http://www.cnblogs.com/na2po2lun/p/4271029.html