Mysql主从同步

第二十七课 Mysql主从同步

一、 MySQL主从介绍
二、准备工作
三、配置主
四、配置从
五、测试主从同步
六、扩展

一、 MySQL主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台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语句落地

主从复制原理图：

二、准备工作

实现MySQL主从复制需要进行的配置：

主服务器：
- 开启二进制日志
- 配置唯一的server-id
- 获得master二进制日志文件名及位置
- 创建一个用于slave和master通信的用户账号
从服务器：
- 配置唯一的server-id
- 使用master分配的用户账号读取master二进制日志
- 启用slave服务

主从数据库版本最好一致

主从数据库内数据保持一致

环境准备：

主数据库：mysql-master 10.0.1.26 /Centos7.4 3.10.0-693.el7.x86_64 mysql-5.6.36

从数据库：mysql-slave 10.0.1.27 /Centos7.4 3.10.0-693.el7.x86_64 mysql-5.6.36

三、配置主

1.编辑/etc/my.cnf配置文件

[root@mysql-master ~]# vim /etc/my.cnf
//修改如下字段
server_id = 210
log_bin=master_bin_log
//重启服务
[root@mysql-master ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@mysql-master ~]# ls /data/mysql/
auto.cnf  ib_logfile0  localhost.localdomain.err  master_bin_log.index  mysql-master.err  performance_schema
ibdata1   ib_logfile1  master_bin_log.000001      mysql                 mysql-master.pid  test

2.创建测试库及数据

[root@mysql-master ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> create database ms_test; 
Query OK, 1 row affected (0.00 sec)

mysql> use ms_test;
Database changed
mysql> create table tb1(`id` int(4),`name` char(40));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into tb1 values(1,'long');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb1 values(2,'oooo');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb1 values(3,'ccc');
Query OK, 1 row affected (0.00 sec)

//备份测试数据库
[root@mysql-master ~]# mysqldump -uroot ms_test > ms_test.sql

3.创建同步数据帐户

mysql> grant replication slave on *.* to 'ms_user'@'10.0.1.27' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'ms_user'@'10.0.1.27';  
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for ms_user@10.0.1.27                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'ms_user'@'10.0.1.27' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//锁定表状态
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

//显示主状态
mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| master_bin_log.000001 |     1240 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

四、配置从

1.修改配置文件

[root@mysql-slave ~]# vim /etc/my.cnf
//修改如下内容
server_id = 220
[root@mysql-slave ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@mysql-slave ~]#

2.将主上备份的测试数据库复制到从库并恢复

//在主上执行
[root@mysql-master ~]# scp ms_test.sql root@10.0.1.27:/root
The authenticity of host '10.0.1.27 (10.0.1.27)' can't be established.
ECDSA key fingerprint is SHA256:zx1ETx0EY8r1LzVAx806KJRBjPOnR/b3vss7sp1y6Hs.
ECDSA key fingerprint is MD5:1f:39:43:b7:16:b4:37:b2:15:4d:92:d4:a5:b6:a4:c7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.1.27' (ECDSA) to the list of known hosts.
root@10.0.1.27's password: 
ms_test.sql                                                                                  100% 1842   740.6KB/s   00:00  
//在从上执行
[root@mysql-slave ~]# mv ms_test.sql /data/mysql/
[root@mysql-slave ~]# ls /data/mysql/
auto.cnf  ib_logfile0  localhost.localdomain.err  mysql            mysql-slave.pid     test
ibdata1   ib_logfile1  ms_test.sql                mysql-slave.err  performance_schema

//创建ms_test
[root@mysql-slave ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> create database ms_test;
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
//恢复ms_test
[root@mysql-slave ~]# mysql -uroot ms_test </data/mysql/ms_test.sql

3.设定同步

//从上执行
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_host='10.0.1.26', master_user='ms_user', master_password='123456', master_log_file='master_bin_log.000001', master_log_pos=1240;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从状态

mysql> show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.26
                  Master_User: ms_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master_bin_log.000001
          Read_Master_Log_Pos: 1240
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 288
        Relay_Master_Log_File: master_bin_log.000001
             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: 1240
              Relay_Log_Space: 467
              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: 210
                  Master_UUID: 060b64b3-899b-11e8-9ed3-000c29609adb
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

//主上执行
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

五、测试主从同步

1.编译主配置文件

[root@mysql-master ~]# vim /etc/my.cnf
//设定仅同步ms_test数据库，添加下行
binlog-do-db=ms_test
[root@mysql-master ~]# vim /etc/my.cnf
[root@mysql-master ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS!

2.在主上插入新的记录

[root@mysql-master ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> use ms_test;
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_ms_test |
+-------------------+
| tb1               |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | long |
|    2 | oooo |
|    3 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> insert into tb1 values(4,'dddd');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | long |
|    2 | oooo |
|    3 | ccc  |
|    4 | dddd |
+------+------+
4 rows in set (0.00 sec)

3.在从上查看是否有同步

mysql> use ms_test;
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_ms_test |
+-------------------+
| tb1               |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | long |
|    2 | oooo |
|    3 | ccc  |
|    4 | dddd |
+------+------+
4 rows in set (0.00 sec)