MySQL 5.5主从复制实践

uykh

一、环境说明

两台安装好的MySQL 5.5的数据库。以Master与Slave区分
Master：
IP地址（172.16.1.210）：

1 2 3

[iyunv@Qinglin-A /]# ifconfig eth0 eth1      Link encap:Ethernet  HWaddr 00:0C:29:B4:0A:5D             inet addr:192.168.1.210  Bcast:172.16.1.255  Mask:255.255.255.0

MySQL版本：

1 2	[iyunv@Qinglin-A /]# mysql -V mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

Slave：
IP地址（172.16.1.220）：

1 2 3

[iyunv@Qinglin-B /]# ifconfig eth0 eth1      Link encap:Ethernet  HWaddr 00:0C:29:0E:20:1D             inet addr:192.168.1.220  Bcast:172.16.1.255  Mask:255.255.255.0

MySQL版本：

1 2	[iyunv@Qinglin-B /]# mysql -V mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

二、实战部署

MySQL 主从同步都是异步的复制方式，即不是严格实时同步的数据同步。
实战前必须知道的知识
a. 主从复制是异步的，逻辑的SQL语句级的复制。
b. 同步时，主库有一个IO线程，从库有两个线程，IO和SQL线程。
c. 实现主从复制的必要条件，主库要开启binlog功能。
1、Master开启binlog，并设置server-id
注：server id 是避免不同机器或实列ID重复，选择0<server-id

1 2 3 4 5 6

[iyunv@Qinglin-A /]# vim /etc/my.cnf server-id       = 1 log-bin=mysql-bin [iyunv@Qinglin-A /]# egrep "log-bin|server-id" /etc/my.cnf server-id       = 1 log-bin=mysql-bin

2、Slave设置server-id
注：Slave的binlog是不开启的，如果开启那就是双主同步，性能很一般。

1 2 3 4 5

[iyunv@Qinglin-B /]# vim /etc/my.cnf server-id       = 3 [iyunv@Qinglin-B /]#  egrep "log-bin|server-id" /etc/my.cnf server-id       = 3 #log-bin=mysql-bin

3、Master和Slave同步重启mysql

1 2 3 4 5 6

[iyunv@Qinglin-A /]# /etc/init.d/mysql restart Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS! [iyunv@Qinglin-B /]# /etc/init.d/mysql restart Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

4、Master效验bin-log与server-id
注：slave只需要查看server-id就可以

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

mysql> show variables like "log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin       | ON    | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like "server_id"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id     | 1     | +---------------+-------+ 1 row in set (0.00 sec)

5、master创建并授权网段访问的从库个帐户，使slave可以访问master
下列为创建Slave用户，刷新权限，并查看用户权限。

1 2 3 4 5 6 7 8 9 10 11

mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by'123456'; Query OK, 0 rows affected (0.00 sec) mysql>  flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>  show grants for rep@'192.168.1.%';   +--------------------------------------------------------------------------------------------------------------------------+ | Grants for rep@192.168.1.%                                                                                               | +--------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

6、记录Master的binlog位置与点，请切记下列标黄处，后期会用到。

1 2 3 4 5 6 7

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

7、锁表备份Master的所有数据库
-A 备份全部数据库
-B 导出几个数据库
-F –flush-logs 开始导出之前刷新日志 注意：假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志
-x 锁表，保证数据的一致性
–events 导出事件
–master-data=2 如果=2就生成就是注释，不执行。

1 2 3

[iyunv@Qinglin-A /]#  mysqldump -uroot -p'123456' --events -A -B -F --master-data=2 -x |gzip >/home/bak_`date +%F`.sql.gz [iyunv@Qinglin-A /]# ll /home/bak_2016-08-02.sql.gz -rw-r--r-- 1 root root 144365 Aug  2 15:16 /home/bak_2016-08-02.sql.gz

8、slave恢复数据库，因为我加大B参数，所以不需要指定库恢复
注：从master的备份文件到slave可以考虑用scp传到slave中，这里不做演示。

1 2 3 4

[iyunv@Qinglin-B guanqinglin]# gzip -d bak_2016-08-02.sql.gz       [iyunv@Qinglin-B guanqinglin]# ls bak_2016-08-02.sql [iyunv@Qinglin-B guanqinglin]# mysql -uroot -p123456

9、导入slave的master信息（slave操作）
操作前，请使用如下方法测试一下，用户和远程登陆是否OK，执行如下命令，登录到那mysq>界面为成功。

1 2 3 4 5 6 7 8 9 10

[iyunv@Qinglin-B /]# mysql -h 192.168.1.210 -urep -p123456 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

退出上面执行的mysql 界面，重新使用root登录Slaver的mysql界面执行导入Master信息操作。

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

[iyunv@Qinglin-B /]# mysql -uroot -p123456                Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.32 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CHANGE MASTER TO     -> MASTER_HOST='192.168.1.210',     -> MASTER_PORT=3306,     -> MASTER_USER='rep',     -> MASTER_PASSWORD='123456',     -> MASTER_LOG_FILE='mysql-bin.000001',     -> MASTER_LOG_POS=332; Query OK, 0 rows affected (0.12 sec)

10、slave查看master info信息

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

[iyunv@Qinglin-B /]# cat /application/mysql/data/master.info 18 mysql-bin.000001 332 192.168.1.210 rep 123456 3306 60 0 0 1800.000 0

11、slave开启slave开启

1 2 3 4 5 6 7 8 9 10 11

[iyunv@Qinglin-B /]# mysql -uroot -p123456                           Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.32 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> start slave; Query OK, 0 rows affected (0.01 sec)

12、检查，有两个标黄的OK，则是正常启动（Seconds_Behind_Master 0 是落后主库的秒数，0是正确）

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

mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.210                   Master_User: rep                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002           Read_Master_Log_Pos: 107                Relay_Log_File: Qinglin-B-relay-bin.000004                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000002              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: 107               Relay_Log_Space: 456               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)

三、测试环节

1、Master创建一个数据库

1 2	mysql> create database qinglin; Query OK, 1 row affected (0.09 sec)

2、slave 查看从库relay文件中是否存在主库创建的create数据库qinglin
注：下列是截取的内容

1 2 3 4 5

[iyunv@Qinglin-B data]# mysqlbinlog Qinglin-B-relay-bin.000004 SET @@session.collation_database=DEFAULT/*!*/; create database qinglin /*!*/; DELIMITER ;

3、检查salve是否已经创建了一个数据库

1 2 3 4 5 6 7 8 9 10

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | qinglin            | +--------------------+ 4 rows in set (0.00 sec)