MySQL主从复制

kyjh

Mysql复制
Mysql内建的复制功能是构建大型、高性能应用程序的基础。Mysql支持单向、异步复制，复制过程中一台服务器充当主服务器，而另外一个或多个服务器充当从服务器。复制解决的基本问题是让一台服务器的数据和另外的服务器保持同步。主服务器和从服务器可以位于不同的网络拓扑中，还能对整台服务器、特定的数据库、甚至特定的表进行复制。
总体来说，复制有3个步骤：
1、主服务器把数据更改记录到二进制日志中。（这叫做二进制日志事件）
2、从服务器把主服务器的二进制日志拷贝到自己的中继日志中。
3、从服务器重放中继日志中的事件，把更改应用到自己的数据上。

第一步是在主服务器上记录二进制日志。在每个更新数据的事务完成之前，主服务器都会将数据更改记录到二进制日志中。即使事务在执行期间是交错的，mysql也会串行地将事务写入到二进制日志中。在把事件写入二进制日志之后，主服务器告诉存储引擎提交事务。
第二步是从服务器把主服务器的二进制日志拷贝到自己的硬盘上，进入所谓的“中继日志”中。首先，它启动一个工作线程，叫I/O线程，这个I/O线程开启一个普通的客户端连接，然后启动一个特殊的二进制日志转储进程（它没有相应的SQL命令）。这个转储进程从主服务器的二进制日志中读取数据。它不会对事件进行轮询。如果3跟上了主服务器，就会进入休眠状态并等待有新的事件发生时主服务器发出的信号。I/O线程把数据写入从服务器的中继日志中。
第三步SQL线程读取中继日志，并且重放其中的事件，然后更新从服务器的数据。由于这个线程能跟上I/O线程，中继日志通常在操作系统的缓存中，所以中继日志的开销很低。SQL线程执行事件也可以被写入从服务器自己的二进制日志中，它对于有些场景很实用。
本篇主要讲解Mysql的主从复制、半同步复制、基于SSL复制。
环境介绍：

1 2 3 4 5

系统：CentoS6.4-x86 主服务器：node1：192.168.184.143 从服务器：node2： 192.168.184.144 Mysql版本：mysql-5.5.14

主从复制：
思路解析：
主服务器：创建具有复制权限的用户账号；设置server-id；启动二进制日志。
从服务器：启动中继日志；设置server-id；启动复制线程。
1、创建主从服务器并实现双机互信
主服务器：

1 2	[iyunv@centos64_1 ~]# ssh-keygen -t rsa -P '' [iyunv@centos64_1 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.184.144

从服务器：

1 2	[iyunv@centos64_2 ~]# ssh-keygen -t rsa -P '' [iyunv@centos64_2 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.184.143

2、编辑主从服务器的hosts文件

1 2 3

[iyunv@centos64_1 ~]# vi /etc/hosts 192.168.184.143 centos64_1 192.168.184.144 centos64_2

同样在node2上。
3、查看时间并同步时间

1 2	[iyunv@centos64_1 ~]# date;ssh centos64_2 'date' [iyunv@centos64_2 ~]# date;ssh centos64_1 'date'

注意：必须保证时间一致，这样主从复制才不会实现时间间隔。
4、安装Mysql：（主从服务器）
略
5、配置主服务器
（1）编辑配置文件(主要信息)

1 2 3 4

[iyunv@centos64_1 ~]# vi /etc/my.cnf server-id  = 1        #标识自己的ID号 log-bin=mysql-bin     #二进制日志，默认是启动的 binlog_format=mixed   #使用混合模式

（2）建立复制账号权限

1 2 3 4

mysql> GRANT REPLICATION SLAVE ON *.* TO rpuser@'192.168.%.%' IDENTIFIED BY 'rpuser'; mysql> flush privileges; [iyunv@centos64_1 mysql]# service mysqld restart

6、配置从服务器
（1）编辑配置文件(主要信息)

1 2 3 4 5 6 7 8 9 10 11

[iyunv@centos64_2 mysql]# vi /etc/my.cnf server-id=20                            #标识自己的ID号，和主服务器不能相同 #log-bin=mysql-bin                      #关闭二进制日志 #binlog_format=mixed                    #关闭使用混合模式 #####设置从服务器为只读模式###### read-only=1                             #注意从服务器不可有写模式 #####添加中继日志################ relay-log=/opt/data/mysql/relay-mysql   #中继日志 relay-log-index=relay-mysql.index       #中继日志索引文件 [iyunv@centos64_2 mysql]# service mysqld restart

【relay-log日志记录的是在复制过程中，从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器。】

（2）登录mysql查看线程

1 2 3 4 5 6 7

mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host      | db   | Command | Time | State | Info             | +----+------+-----------+------+---------+------+-------+------------------+ |  2 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec)

根据结果显示：此时还未启动任何线程。

（3）配置连接服务器
命令参数解析：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql>help CHANGE MASTER TO;     MASTER_BIND = 'interface_name'       #将控制绑定在那个接口上   | MASTER_HOST = 'host_name'            #主服务器地址   | 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)  #将某些ID号忽略掉

配置从服务器连接主服务器：

1	mysql> CHANGE MASTER TO MASTER_HOST='192.168.184.143',MASTER_USER='rpuser',MASTER_PASSWORD='rpuser';

（4）启动从服务器线程

1	mysql> start slave;

（5）查看从服务器工作状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event    #I/O状态；接受主服务器发送状态                   Master_Host: 192.168.184.143                     #主服务器                   Master_User: rpuser                              #用户                   Master_Port: 3306                                #默认端口                 Connect_Retry: 60                                  #重试时间间隔               Master_Log_File: mysql-bin.000012                    #读取的二进制日志文件           Read_Master_Log_Pos: 107                                 #事件位置                Relay_Log_File: relay-mysql.000021                  #当前读取的中继日志文件                 Relay_Log_Pos: 253                                          Relay_Master_Log_File: mysql-bin.000012              Slave_IO_Running: Yes                                 #I/O线程已启动             Slave_SQL_Running: Yes                                 #SQL线程已启动               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: 107               Relay_Log_Space: 551               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) mysql>

7、测试
主服务器：

1 2 3

mysql> system hostname; centos64_1 mysql> create database dbtest;;

从服务器：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> system hostname; centos64_2 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | dbtest             | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec) mysql>

主从复制配置成功。

半同步复制：
半同步复制：就是让从服务器从指定位置进行同步数据，而不是从头开始同步数据。
为了实现效果，这里将从服务器mysql的原有数据清除，其他配置不变：

1 2 3 4 5

[iyunv@centos64_2 mysql]# service mysqld stop [iyunv@centos64_2 mysql]# rm -rf /opt/data/mysql/ [iyunv@centos64_2 mysql]# cd /usr/local/mysql/ [iyunv@centos64_2 mysql]#./scripts/mysql_install_db --user=mysql --datadir=/opt/data/mysql [iyunv@centos64_2 mysql]# service mysqld start

清理完成启动成功进行半同步复制：
思路：首先在主服务器上完全备份数据库，然后将数据导出在从服务器上应用即可。
1、主服务器数据库完全备份

1	[iyunv@centos64_1 tmp]# mysqldump -uroot --lock-all-tables --master-data=2 --events --all-databases > all.sql

2、备份的数据复制至从服务器上

1	[iyunv@centos64_1 tmp]# scp all.sql 192.168.184.144:/tmp/

3、从服务器将数据导入mysql

1	mysql> source /tmp/all.sql;

4、查看主服务器上二进制文件及事件位置

1 2 3 4 5 6 7 8

mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000012 |      194 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>

5、配置连接服务器
从服务器连接主服务器：

1	mysql> CHANGE MASTER TO MASTER_HOST='192.168.184.143',MASTER_USER='rpuser',MASTER_PASSWORD='rpuser',MASTER_LOG_FILE='mysql-bin.000012',MASTER_LOG_POS=194;

6、启动从服务器

1	mysql> start slave;

7、查看从服务器状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.184.143                   Master_User: rpuser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000012     #指定的二进制文件           Read_Master_Log_Pos: 194                  #指定的事件位置                Relay_Log_File: relay-mysql.000002                    Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000012              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: 194               Relay_Log_Space: 405               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) mysql>

8、测试
主服务器：

1 2 3

mysql> system hostname; centos64_1 mysql> mysql> create database dbtest2;

从服务器：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> system hostname; centos64_2 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | dbtest             | | dbtest2            | | mysql              | | performance_schema | | test               | +--------------------+ 6 rows in set (0.00 sec) mysql>

半同步主从复制就完成了！！

基于SSL的mysql主从复制
环境介绍：
Mysql版本：mysql-5.5.14
主服务器：node1：192.168.184.143 数据未改变
从服务器：node2：192.168.184.144 数据未改变
演示过程：
在主服务器上配置CA，然后分别为主从服务器颁发证书；
1、将主服务器做成CA服务器
[iyunv@centos64_1 ~]# cd /etc/pki/CA/