MySQL实现主从复制
主要内容：
简介
主从复制
双主复制
半同步复制
基于SSL认证的主从服务
读写分离
实验环境
CentOS 7.3，使用MariaDB-5.5.52-1
CentOS 6.9，使用MySQL-5.1.73-8
简介
MySQL数据库支持同步复制、单向、异步复制集群，在集群一个服务器作为主服务，而一个或多个服务器充当从服务器。用户向主服务器写入数据的操作，主服务执行操作写入数据并将操作指令记录于二进制日志文件中。从服务器向主服务器发出请求，获取主服务器的二进制日志的内容，然后把获取的内容纪录到自己的中继日志中并按照中继进行重放操作。
主从复制的优点：
加快用户访问数据的数据，在使用复制可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。
注意：使用主从复制时，所有对表的写入必须在主服务器上进行。在从服务器上面的写入操作是无法同步到其他服务器上面的，为了避免用户将数据写入从服务器，需要在前端进行调度实现读写分离。
mysql支持的复制类型：
基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。
基于行的复制 ：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
主/从架构：
异步复制：
半同步复制：
一主多从；
一从一主；
级联复制；
循环复制；
双主复制；
一从多主：
配置主从复制：
注意事项
各个服务器之间的时间同步；
复制的开始位置：
从0开始；
从备份中恢复到从节点后启动的复制，复制的起始点备份操作时主节点所处的日志文件及其事件位置；
主从服务器mysqld程序版本不一致？
从的版本号高于主的版本号；
主服务器：
[root@CentOS7.3 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1 #设置当前服务器的ID号
log_bin=log-bin #启动二进制日志并指定文件名
skip_name_resolve=on #跳过主机名解析。在CentOS 6自带的mysql后面的=on不用写
innodb_file_per_table=on #innodb的每个表是用单独的文件
启动服务
[root@CentOS7.3 ~]#systemctl start mariadb #启动数据库
[root@CentOS7.3 ~]#mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.166.%' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec) #创建一个用于从服务器连接复制主服务器的用户
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
从服务器：
[root@centos7.3-1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=2
relay_log=relay-log #启用中继日志。在数据目录下有一个relay-kog.info里面保存了当前的中继日志和位置会主节点二进制文件的名字和位置。
read_only=on #禁止用户写入数据，这一项的管理员和复制重放无效。
在从服务器启动read_only，但仅对非SUPER权限的用户有效；
阻止所有用户：
mysql> FLUSH TABLES WITH READ LOCK; 添加全局读锁
启动服务：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.130',MASTER_USER='repluser',MASTER_PASSWORD='test123',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=498;
Query OK, 0 rows affected (0.03 sec)
设置主服务器的地址和用于连接主服务器的用户名和密码并指定从服务器的那个二进制文件的那个位置开始复制。获取主服务器当前所在二进制文件和位置执行下面这条指令。
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 498 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
这设置会保存在数据目录下的一个加master.info的文件内
MariaDB [(none)]> START SLAVE; #启动SLAVE线程
#从服务器有两个线程，IO_THREAD是复制线程，SQL_THREAD是重放线程，不指定全部开启。
MariaDB [(none)]> SHOW SLAVE STATUS\G; #查看从服务器的状态
Slave_IO_Running是复制线程，Slave_SQL_TRunning是重放线程。
测试
MariaDB [mydb]> create database mydb; #在主服务器上面添加数据
MariaDB [mydb]> use mydb;
MariaDB [mydb]> create table test1 (id int,neme char(100));
MariaDB [mydb]> insert into test1 values (1,'Xiao Ming');
MariaDB [mydb]> select * from test1;
+------+-----------+
| id | neme |
+------+-----------+
| 1 | Xiao Ming |
+------+-----------+
1 row in set (0.00 sec)
查看从服务器
image.png
在上图中可以看到数据已经同步到里从服务器
双主复制：互为主从：两个节点各自都要开启binlog和relay log；
配置：
1、server_id必须要使用不同值；
2、均启用binlog和relay log；
3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；
服务启动后执行如下两步：
4、都授权有复制权限的用户账号；
5、各把对方指定为主节点；
复制时应该注意的问题：
自动增长id；
定义一个节点使用奇数id
auto_increment_offset=1 #自动增长ID初始数
auto_increment_increment=2 #每次增长几位数
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
先在第二台服务器上面也创建一个用于连接复制的用户
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.166.%' IDENTIFIED BY 'test123';
修改第一台服务器配置文件
[mysqld]
server_id=1
log_bin=master-log
skip_name_resolve=on
innodb_file_per_table=on
relay_log=relay-log
auto_increment_offset=1
auto_increment_increment=2
重启服务并为服务器指定主服务器
[root@CentOS7.3 ~]#systemctl restart mariadb
[root@CentOS7.3 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.132',MASTER_USER='repluser',MASTER_PASSWORD='test123',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=428;
Query OK, 0 rows affected (0.03 sec) #上面演示过了，这里就不演示如何获取主服务器的二进制日志信息了
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
image.png
修改第二台服务器配置文件
[mysqld]
server_id=2
skip_name_resolve=on
innodb_file_per_table=on
relay_log=relay-log
log_bin=master-log
auto_increment_offset=2
auto_increment_increment=2
重启服务并为服务器指定主服务器
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.130',MASTER_USER='repluser',MASTER_PASSWORD='test123',MASTER_LOG_FILE='master-log.000006',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
测试
MariaDB [mydb]> CREATE TABLE test2 (id int auto_increment primary key,name char(100) not null,age int);
#新建一张表，设置ID字段的值可以自动增长用于测试。
MariaDB [mydb]> desc test2;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
MariaDB [mydb]> insert into test2 (name,age) values ('test1',10),('test2',11);
Query OK, 2 rows affected (0.00 sec) #向表内插入数据
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from test2; #查询test2的所有数据，id的增长和我们设置的一样，从1开始每次增长两位数。
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | test1 | 10 |
| 3 | test2 | 11 |
+----+-------+------+
2 rows in set (0.00 sec)
查看第二台服务器
image.png
半同步复制，（同步加异步，一部分从服务器进行同步复制，一部分从服务器进行异步复制。）
这个功能需要安装插件方可使用，mysql安装包内自带了这个插件，mysql的插件文件的默认存放路径：/usr/lib64/mysql/plugins/
安装插件的语法
mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
mysql> show pluggins; 查看mysql启用的插件
半同步复制所需的插件：
semisync_master.so
semisync_slave.so
主节点安装：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'; #查看以rpl_semi开头的全局设置
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | #查看已经安装，但是功能并未启动
| rpl_semi_sync_master_timeout | 10000 | #把数据写入从服务器的超时时间，超过这个时间从服务器未响应数据写入，从服务器降级为异步。单位毫秒
| rpl_semi_sync_master_trace_level | 32 | #s设置跟踪级别
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON; #启动插件
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_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 |
+------------------------------------+-------+
从节点：
MariaDB [mydb]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF | #客户端插件未启动
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
MariaDB [mydb]> SET @@global.rpl_semi_sync_slave_enabled=on; #启动插件
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
MariaDB [mydb]> STOP SLAVE IO_THREAD; #开启同步功能后，从服务器线程不会自动切换到同步，需要手动关闭再开启线程。
MariaDB [mydb]> START SLAVE IO_THREAD;
主节点：
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 | #网络IO平均等待时间
| Rpl_semi_sync_master_net_wait_time | 0 | #等待时间
| Rpl_semi_sync_master_net_waits | 0 | #网络IO网络等待发生了几次
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| 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 | #事务网络IO网络等待发生了几次
| 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 |
+--------------------------------------------+-------------+
测试
主服务器
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb
Database changed
MariaDB [mydb]> create table test1;
MariaDB [mydb]> create table test1 (id int auto_increment primary key,name char(100),age int);
Query OK, 0 rows affected (0.25 sec)
MariaDB [mydb]> insert into test1 (name,age) values ('Xiao Ming',10);
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 674 | #平均时间
| Rpl_semi_sync_master_net_wait_time | 2022 | #等待时间
| Rpl_semi_sync_master_net_waits | 3 | #刚才执行3次写入
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 772 |
| Rpl_semi_sync_master_tx_wait_time | 2318 |
| Rpl_semi_sync_master_tx_waits | 3 |
| 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 |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
注：因为inndb引擎是事务性引擎，而且默认开启每执行一条语句自动提交事务，所以上面显示的总计数器的等待次数和事务计数器的值相同。
从服务器
MariaDB [mydb]> select * from test1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | Xiao Ming | 10 |
+----+-----------+------+
1 row in set (0.00 sec)
MariaDB [mydb]> stop slave io_thread; #关闭从服务器的IO线程
主服务器
MariaDB [mydb]> insert into test1 (name,age) values ('Xiao Hong',9); #在主服务器内插入一行数据。
Query OK, 1 row affected (10.00 sec) #语句执行时间
因为刚才关闭了从服务器的IO线程，从服务器无法执行这条语句，就无法显示语句执行完成。主服务器只有等过了超时时间，判断从服务器故障，把从服务器降级为异步，才能反馈显示语句执行成功。
基于SSL复制的复制
- 创建证书
[root@CentOS7.3 ~]#(umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem ) #密钥默认是1024位加密，也可以在命令尾部指定
自签名证书
[root@CentOS7.3 ~]#touch /etc/pki/CA/index.txt #创建证书索引文件
[root@CentOS7.3 ~]#echo 01 > /etc/pki/CA/serial #指定证书颁发的序号，序号必须是两位16进制数
[root@CentOS7.3 ~]#echo 01 > /etc/pki/CA/crlnumber #指定证书吊销的序号
[root@CentOS7.3 ~]#touch /etc/pki/CA/private/.rand #创建伪随机数文件
[root@CentOS7.3 ~]#openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -days 7300 -out /etc/pki/CA/cacert.pem
#创建自签证书 -new：生成新证书签署请求；-x509：生成自签格式证书，专用于创建私有CA时；-key：生成请求时用到的私有文件路径
# -out：生成的请求文件路径；如果自签操作将直接生成签署过的证书；-days：证书的有效时长，单位是day；
Country Name (2 letter code) [XX]:CN #填写CA所在的国家，格式为国家代码
State or Province Name (full name) []:xxxxxx #填写所在的州或省
Locality Name (eg, city) [Default City]:xxxxxx #填写所在的城市
Organization Name (eg, company) [Default Company Ltd]:CentOS #机构名称
Organizational Unit Name (eg, section) []:CA #部门名称
Common Name (eg, your name or your server's hostname) []: #通用名称，服务器的主机名
Email Address []: #邮箱地址
给主服务器MariaDB颁发证书
密钥和证书申请文件
[root@CentOS7.3 ~]#mkdir /var/lib/mysql/ssl
[root@CentOS7.3 ~]#(umask 077;openssl genrsa -out /var/lib/mysql/ssl/master.key) #在master生成私钥
[root@CentOS7.3 ~]#openssl req -new -key /var/lib/mysql/ssl/master.key -out /var/lib/mysql/ssl/master.pem
#生成证书申请文件
Country Name (2 letter code) [XX]:CN #国家，必须和CA相同
State or Province Name (full name) []:HeNan #州或者省份，必须和CA相同
Locality Name (eg, city) [Default City]:zz #城市，可以自定义
Organization Name (eg, company) [Default Company Ltd]:CAServer #机构名必须和CA相同
Organizational Unit Name (eg, section) []:test #部门名称，可以自定义
Common Name (eg, your name or your servers hostname) []:test.com #服务器名称
Email Address []: #邮箱地址可以忽略
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:admin #设置密码加密
An optional company name []:admin
CA颁发证书
[root@CentOS7.3 ~]#openssl ca -in /var/lib/mysql/ssl/master.pem -out /var/lib/mysql/ssl/master.crt -days 365 #CA签发证书
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Jul 17 16:14:46 2017 GMT
Not After : Jul 17 16:14:46 2018 GMT
Subject:
countryName = CN
stateOrProvinceName = HeNan
organizationName = xxxxxx
organizationalUnitName = xxxxxx
commonName = xxxxxx
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
3B:F1:1B:6B:88:C2:17:35:19:2E:35:90:C6:22:6E:69:10:FF:B4:02
X509v3 Authority Key Identifier:
keyid:60:3C:95:CB:A1:63:DC:0C:FC:1B:85:22:B3:4D:FD:FB:3B:5E:A4:B8
Certificate is to be certified until Jul 17 16:14:46 2018 GMT (365 days)
Sign the certificate? [y/n]:y #证书的有效期到xxx是否签发
1 out of 1 certificate requests certified, commit? [y/n]y #是否提交认证
Write out database with 1 new entries
Data Base Updated
修改证书和密钥的属主、属组
[root@CentOS7.3 ~]#chown mysql.mysql /var/lib/mysql/ssl/ -R
给从服务器颁发证书
创建密钥和证书申请文件
[root@centos7.3-1 ~]#mkdir /var/lib/mysql/ssl/ #在Mariadb工作目录创建一个ssl目录用于存放证书和私钥
[root@centos7.3-1 ~]#(umask 077;openssl genrsa -out /var/lib/mysql/ssl/slave.key) #在从服务器端生成私钥
Generating RSA private key, 1024 bit long modulus
..............................++++++
.......++++++
e is 65537 (0x10001)
[root@centos7.3-1 ~]#openssl req -new -key /var/lib/mysql/ssl/slave.key -out /var/lib/mysql/ssl/slave.pem #生成证书申请文件
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 #国家，必须和CA相同
State or Province Name (full name) []:HeNan #州或者省份，必须和CA相同
Locality Name (eg, city) [Default City]:zz #城市，可以自定义
Organization Name (eg, company) [Default Company Ltd]:CAServer #机构名必须和CA相同
Organizational Unit Name (eg, section) []:test #部门名称，可以自定义
Common Name (eg, your name or your servers hostname) []:test.com #服务器名称
Email Address []: #邮箱地址可以忽略
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:admin #设置密码加密
An optional company name []:admin
将证书申请文件发送到CA服务器
[root@centos7.3-1 ~]#scp /var/lib/mysql/ssl/slave.pem 192.168.166.130:/etc/pki/CA/
CA颁发证书
[root@CentOS7.3 ~]#openssl ca -in /etc/pki/CA/slave.pem -out /etc/pki/CA/certs/slave.crt -days 365 #CA颁发证书
Check that the request matches the signature
ignature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Sep 17 04:17:06 2017 GMT
Not After : Sep 17 04:17:06 2018 GMT
Subject:
countryName = CN
stateOrProvinceName = HeNan
organizationName = xxxxx
organizationalUnitName = slave.xxxxxx.com
commonName = slave
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
8E:F3:29:4B:56:72:CE:0F:F0:88:31:21:49:59:67:06:1D:52:63:15
X509v3 Authority Key Identifier:
keyid:90:85:07:D5:53:B4:25:64:0C:D2:10:DA:6D:23:70:3A:49:78:AF:F4
Certificate is to be certified until Jul 17 16:14:46 2018 GMT (365 days)
Sign the certificate? [y/n]:y #证书的有效期到xxx是否签发
1 out of 1 certificate requests certified, commit? [y/n]y #是否提交认证
Write out database with 1 new entries
Data Base Updated
把从服务器和CA证书发送给从服务器
[root@CentOS7.3 ~]#scp /etc/pki/CA/certs/slave.crt 192.168.166.132:/var/lib/mysql/ssl/
在从服务器上面修改属主、属组和权限
[root@centos7.3-1 mysql]#chown mysql.mysql ssl/ -R
[root@centos7.3-1 mysql]#cd ssl/
[root@centos7.3-1 ssl]#chmod 600 *
修改master的配置文件
[root@CentOS7.3 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld] #找到这一项在其下面添加如下选项
log-bin=master-bin #开启二进制日志并指定名字
sync_binlog = 1 #二进制日志文件同步写入磁盘
server-id = 1 #服务器的ID
innodb_flush_log_at_trx_commit=1
skip_name_resolve=on
ssl
ssl_ca =/etc/pki/CA/cacert.pem #CA的证书
ssl_cert= /var/lib/mysql/ssl/master.crt #master的证书
ssl_key = /var/lib/mysql/ssl/master.key #master的密钥
启动主服务器
[root@CentOS7.3 ssl]#systemctl start mariadb
MariaDB [(none)]> show variables like '%ssl%'; #查看ssl信息
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | /etc/pki/CA/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/master.pem |
+---------------+-------------------------------+
MariaDB [(none)]> grant replication slave,replication client on *.* to 'ssluser'@'192.168.166.%' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
修改从服务器的配置文件
[root@CentOS7.3-1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld] #找到这一项在其下面添加如下选项
relay-log=relay-log #开启中继日志并指定名字
server-id = 2 #服务器的ID
innodb_flush_log_at_trx_commit=1
skip_name_resolve=on
ssl
ssl_ca =/var/lib/mysql/ssl/cacert.pem #CA的证书
ssl_cert= /var/lib/mysql/ssl/slave.crt #master的证书
ssl_key = /var/lib/mysql/ssl/slave.key #master的密钥
启动msyql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.130',MASTER_USER='ssluser',MASTER_LOG_FILE='master-log.000004',MASTER_LOG_POS=485,MASTER_SsL=1,MASTER_SSL_CA='/app/ssl/cacert.pem',MASTER_SSL_CERT='/app/ssl/slave.crt',master_ssl_key='/aap/ssh/slave.key';
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
读写分离
ProxySQL
ProxySQL是一个高性能，高可用性的协议感知代理，可以用于MySQL和Percona Server和MariaDB。
双主或多主模型是无须实现读写分离，仅需要负载均衡：haproxy, nginx, lvs, ...
实验环境
ProxySQL：CentOS 7.3-1
内网ip：192.168.166.132
服务IP：172.16.251.77
主服务器：CentOS 7.3-2
IP地址：192.168.166.133
从服务器：
CentOS 6.9 ：IP地址：192.168.166.129
CentOS 6.9-1：IP地址：192.168.166.131
ProxySQL安装
官方网站下载安装包，官网提供rpm包
[root@centos7.3-1 ~]#yum -y install ./proxysql-1.4.2-1-centos7.x86_64.rpm
主要文件
/etc/init.d/proxysql #启动脚本
/etc/proxysql.cnf #配置文件
/usr/bin/proxysql #主程序文件
/usr/share/proxysql/tools/proxysql_galera_checker.sh #和PXC有关脚本
/usr/share/proxysql/tools/proxysql_galera_writer.pl
配置文件示例：
datadir="/var/lib/proxysql" #文件保存位置
admin_variables= #proxy管理配置端
{
admin_credentials="admin:admin" #管理proxysql的账户和密码
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" #管理监听地址
}
mysql_variables= #msyql代理服务配置项
{
threads=4 #线程数
max_connections=2048 #最大连接数
default_query_delay=0 #默认查询延迟
default_query_timeout=36000000 #默认查询超时时间
have_compress=true #是否启用压缩
poll_timeout=2000 #轮询超时时长
interfaces="0.0.0.0:3306;/tmp/mysql.sock" #代理服务监听地址
default_schema="" #用户连接数据库默认进入数据库
stacksize=1048576 #栈大小
server_version="5.5.30" #模拟的服务器版本
connect_timeout_server=3000 #连接的超时时间
monitor_history=600000 #监控历史的保留时长
monitor_connect_interval=60000 #监控后端主机健康状态
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers = #mysql后端主机配置端
(
{
address = "172.18.0.67" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required #后端主机所属的组
status = "ONLINE" # default: ONLINE #指定后端服务器的状态，ONLINE表示在线
weight = 1 # default: 1 #权重
compression = 0 # default: 0 #是否启用压缩，1为启用。 max_replication_lag =0 #默认为0，如果设置了值，从服务器落后主服务器的时间达到这个值，这个从服务器将被下线。
},
{
address = "172.18.0.68"
port = 3306
hostgroup = 1
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = "172.18.0.69"
port = 3306
hostgroup = 1
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
}
#{ address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
#还可以是使用这样的格式
)
mysql_users: #proyx连接后端主机进行管理的用户
(
{
username = "root" #用户
password = "admi123" #密码
default_hostgroup = 0 #所属组
max_connections=1000 #最大连接数
default_schema="mydb" #用户连接上去的默认库
active = 1 #是否是活动状态
}
)
mysql_query_rules: #代理请求
(
)
scheduler= #调度器
(
)
mysql_replication_hostgroups= #复制的主机组，主要用于高可用
(
{
writer_hostgroup=0 #可写的组
reader_hostgroup=1 #只读的组
comment="test repl 1" #注释信息
}
)
注意：在每个配置端的最后一个配置的不要加上“，”号
后端服务器
主节点
MariaDB [(none)]> GRANT ALL ON *.* TO 'proxysql'@'192.168.166.%' IDENTIFIED BY 'admin';
测试
启动
service proxysql start #开启proxysql
客户端连接测试
[root@CentOS7.3 ~]#mysql -h 192.168.166.132 -u proxysql -padmin
[root@CentOS7.3 ~]#mysql -h 192.168.166.132 -u proxysql -padmin
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL)
MySQL [mydb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MySQL [mydb]> create table test1 (id int,name char(100));
Query OK, 0 rows affected (0.16 sec)
MySQL [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
从服务器
mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
复制过滤器：
仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；
有两种实现思路：
(1) 主服务器
主服务器仅向二进制日志中记录有关特定数据库相关的写操作；
问题：其它库的time-point recovery将无从实现；时间点恢复无法实现。
binlog_do_db= 白名单
binlog_ignore_db= 黑名单，二者不要同时使用
(2) 从服务器
从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；
问题：网络IO和磁盘IO；后缀为DB的为库列表，table为表列表
Replicate_Do_DB= 库白名单
Replicate_Ignore_DB=
Replicate_Do_Table= 表级白名单
Replicate_Ignore_Table= 黑名单
Replicate_Wild_Do_Table= 可以使用通配符进行通配
Replicate_Wild_Ignore_Table= 可以使用通配符匹配
复制的监控和维护：
(1) 清理日志：PURGE
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };
PURGE BINARY LOGS TO 'mysql-bin.001' 清理这个文件之前的二进制日志文件
PURGE BINARY LOGS BEFORE '2017-01-01' 清理这个时间点之前的二进制日志文件
(2) 复制监控
MASTER:
SHOW MASTER STATUS;
SHOW BINLOG EVENTS;
SHOW BINARY LOGS;
SLAVE:
SHOW SLAVE STATUS;
判断从服务器是否落后于主服务器：
Seconds_Behind_Master: 0
(3) 如何确定主从节点数据是否一致？
通过表的CHECKSUM检查；
使用percona-tools中pt-table-checksum；
(4) 主从数据不一致时的修复方法？
重新复制；