本文详细介绍了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
397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
