MySQL主从复制

Mysql主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的，在B机器上读数据，实现数据的读写分离，可以极大的提升性能，降低服务器负载， MySQL主从是基于binlog的，主上须开启binlog才能进行

主从过程大致有3个步骤
1）主将更改操作记录到binlog里
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
3）从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程，用来和从的I/O线程传递binlog
从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

如下图：

配置主机器A（master）

编辑配置文件/etc/my.cnf，在[mysqld]中添加如下内容：

#定义server-id，不能与B机器（slave）的server-id重复，定义binlog名称

重启mysql服务后即可在数据目录下生成binlog文件：

[root@master ~]# service mysql restart
Shutting down MySQL....                                    [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@master ~]# ll /data/mysql/ |grep mslog
-rw-r----- 1 mysql mysql      154 Nov  4 21:34 mslog.000001
-rw-r----- 1 mysql mysql       15 Nov  4 21:34 mslog.index

创建数据同步用户：

grant replication slave on *.* to msuser@'122.51.40.214' identified by 'msuser';

#创建用户msuser，密码msuser，授权ip为B机器（slave）地址，授权权限：replication slave

锁表：

mysql> flush tables with read lock;

#停止数据写入，防止主从不同步，配置完主从后需要解锁：unlock tables;

导出测试的数据库：

[root@master ~]# mysqldump -uroot -p ms_db > /tmp/msdb.sql

记录file、position的值：

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

将msdb库导出的sql脚本发送到B机器：

[root@master ~]# scp /tmp/msdb.sql 122.51.49.214:/tmp

配置从机器B（slave）

编辑配置/etc/my.cnf 定义server-id：

#slave机器不需要配置binlog

重启服务：

[root@slave ~]# service mysql restart

恢复A机器导出的ms_db库：

mysql> create database ms_db;
Query OK, 1 row affected (0.00 sec)

mysql> use ms_db;
Database changed

mysql> source /tmp/msdb.sql

停止slave：

mysql> stop slave;

配置A机器（master）信息：

mysql> change master to master_host='139.159.162.126', master_user='msuser', master_password='msuser', master_log_file='mslog.000001', master_log_pos=451;

定义A机器IP地址，主从同步数据的用户和密码以及之前记录的file、position信息

启动slave：

mysql> start slave

查看主从状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 139.159.162.126
                  Master_User: msuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mslog.000001
          Read_Master_Log_Pos: 451
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 316
        Relay_Master_Log_File: mslog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#Slave_IO_Running、Slave_SQL_Running都显示Yes即表示主从配置成功，还需要注意Seconds_Behind_Master的值，表示主从延迟的时间以及Last_IO_Errno、Last_IO_Error、Last_SQL_Errno、Last_SQL_Error等信息

在A机器（master）解锁表：

mysql> unlock tables;

测试主从：

在A机器操作测试数据库ms_db的数据：

mysql> use ms_db;

Database changed
mysql> select * from test_tb;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   20 |
|  2 | b    |   22 |
|  3 | c    |   18 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> delete from test_tb where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_tb;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   20 |
|  2 | b    |   22 |
+----+------+------+
2 rows in set (0.00 sec)

在B机器查看数据：

mysql> use ms_db;

Database changed
mysql> select * from test_tb;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   20 |
|  2 | b    |   22 |
+----+------+------+
2 rows in set (0.00 sec)

#在A机器删除的数据，B机器也同步删除

补充
配置参数：
主服务器上
binlog-do-db= //仅同步指定的库，可以写多个库名，用逗号分隔
binlog-ignore-db= //忽略指定库
从服务器上
replicate_wild_do_table= //如ms_db.%，支持通配符%，表示同步ms_db中所有表
replicate_wild_ignore_table=

如果通过克隆的方式配置主从，会导致mysql uuid相同，Slave_IO无法启动，报错信息：The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决方法：修改数据存放目录(/data/mysql)下的auto.cnf文件，使uuid不同即可