MySQL主从可以实现MySQL的读写分离，简言之，master上进行写操作，同步数据库，slave上进行读操作，可以极大的提升性能，降低服务器负载。术语：主从复制——master slave replication（M-S模式）。

用途

• 备份
• 读写分离
• 高可用和故障切换（需要做HA）
• MySQL升级测试

原理

实际上，它的原理思路很简单。MySQL 中有一种日志叫做 bin 日志（二进制日志），这个日志会记录下所有修改了数据库的SQL语句。主从复制的原理其实就是"从"服务器向"主"服务器请求这个日志文件，"主"服务器会把这个 bin 日志复制到"从"服务器上执行一遍，这样"从"服务器上的数据就和"主"服务器上的数据相同了。

流程图：

图解：

1. 主服务器必须启用二进制日志（log-bin），记录任何修改了数据库数据的事件；
2. 从服务器开启一个线程（I/O Thread)把自己扮演成 MySQL 的客户端，通过 MySQL 协议，请求主服务器的二进制日志文件中的事件；
3. 主服务器启动一个线程（Dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主服务器就会从第一个日志文件中的第一个事件一个一个发送给从服务器；
4. 从服务器接收到主服务器发送过来的数据把它放置到中继日志（relay log）文件中。并记录该次请求到主服务器的具体哪一个二进制日志文件内部的哪一个位置（主服务器中的二进制文件会有多个，其名结尾以6位数递增）；
5. 从服务器启动另外一个线程（SQL Thread ），把 relay log 中的事件读取出来，并在本地再执行一次。

配置过程

主服务器（Master）

• 启用二进制日志 log-bin；
• 设置一个全局唯一的 server_id；
• 提前准备好一个有复制权限（replication slave，replication client）的用户。

从服务器（Slave）

• 启动中继日志 relay-log；
• 设置一个全局唯一的 server_id；
• 使用主服务器提供的有复制权限的用户连接至 Master；
• 启动复制线程。

配置演示

测试环境

主服务器：192.168.1.101
从服务器：192.168.1.102
mysql版本：mysql-5.6
系统：主 Centos7，从 Windows10

主服务器配置

 

1.配置 /etc/my.cnf

[mysqld]

server-id = 1  #必须唯一
log-bin = mysql-bin                          # 开启mysql binlog功能
expire_logs_days = 10                    #日志的缓存时间
max_binlog_size = 200M                 #日志的最大大小
binlog-do-db=qp_game_database   # 指定需要复制的数据库名为
binlog-ignore-db=mysql                   #忽略同步的数据库

2.重启数据库

3.创建用于同步的用户账号

#创建用户

mysql> CREATE USER 'rootCopy'@'192.168.1.102' IDENTIFIED BY 'rootCopy';
Query OK, 0 rows affected (0.00 sec)

#分配权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rootCopy'@'192.168.1.102';
Query OK, 0 rows affected (0.00 sec)

#刷新权限

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

4.查看master状态

mysql> show master status;
+------------------+----------+------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------+------------------+-------------------+
| mysql-bin.000002 |      120 | qp_game_database | mysql            |                   |
+------------------+----------+------------------+------------------+-------------------+
1 row in set (0.00 sec)

 

从服务器配置

1.配置  C:\Program Files\MySQL\MySQL Server 5.7/my-default.ini

[mysqld]

server-id=2 #必须唯一
# 开启mysql binlog功能
log-bin=mysql-bin

expire_logs_days        = 10                              #日志的缓存时间
max_binlog_size         = 200M                         #日志的最大大小
replicate_do_db         = qp_game_database   #同步的数据库名称
replicate_ignore_db     = mysql                        #忽略同步的数据库

2.重启数据库

3.用主服务器提供的复制用户连接主服务器

change master to master_host='192.168.1.101',master_user='rootCopy',master_password='rootCopy', master_log_file='mysql-bin.000002',master_log_pos=120;

4.启动复制线程(关闭复制线程stop slave)

start slave;

5.查看slave状态：

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: rootCopy

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 2866

Relay_Log_File: mysqld-relay-bin.000022

Relay_Log_Pos: 616

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: Replicate_Ignore_DB:   

 

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，