MySQL主从复制（读写分离）

2022-02-08 本文已影响0人江月照我眠

主从复制功能通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间，SELECT查询可以发送到从服务器，以降低主服务器的查询处理速度，修改数据的语句发送到主服务器，使主从服务器保持同步。

一、工作原理

主从复制通过3个过程实现，其中一个发生在主服务器，其他两个发生在从服务器。

1. 主服务器将用户对数据库的更新操作以二进制格式保存到Binary Log日志文件中，然后通过Binlog Dump线程将Binary Log日志文件传输给从服务器。
2. 从服务器通过一个I/O线程将主服务器的Binary Log日志文件中的更新操作复制到一个叫Relay Log的中继日志文件中。
3. 从服务器通过另一个SQL线程将Relay Log中继日志文件中的操作依次在本地执行，从而实现主从同步复制。

二、如何配置

1. 确定主从服务器的MySQL版本

不同版本的binlog格式可能不一样，最好用同版本，最起码确保主服务器版本不高于从服务器版本。

2. 在主服务器上创建从服务器连接账户，授予REPLICATION SLAVE权限

每个从服务器使用标准的MySQL用户名和密码连接主服务器。假定域为mydomain.com，创建一个用户名为"repl"的账户，从服务器使用该账户从域内任何主机使用密码"pass4slave"访问主服务器。使用GRANT创建账户：

mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com'
     -> IDENTIFIED BY 'pass4slave'

3. 配置主服务器

打开二进制日志，指定唯一Server ID。例如在my.conf配置文件加入如下值：

[mysqld]
log-bin = mysql-bin
server-id = 1

4. 重启主服务器

运行show master status语句查看是否成功。

5. 配置从服务器

与主服务器配置类似，配置唯一的Server ID：

[mysqld]
log-bin = mysql-bin
server-id = 2

6. 重启从服务器

让从服务器连接到主服务器，并开始重做主服务器binlog文件中的事件。

7. 指定主服务器信息

使用CHANGE MASTER TO语句指定主服务器信息，不要在配置文件中设置。下述语句可以替代在配置文件中提供主服务器的信息，此外，不需要停止服务器，就直接为从服务器指定不同的主服务器，语句如下：

mysql > CHANGE MASTER TO MASTER_HOST = '192.168.1.100',
     -> MASTER_USER = 'repl',
     -> MASTER_PASSWORD = 'pass4slave',
     -> MASTER_LOG_FILE = 'mysql-bin.000001',
     -> MASTER_LOG_POS = 0;

指定MASTER_LOG_POS为0是希望从日志的开始位置开始读。

8. 查看从服务器的配置是否正确

mysql > START SLAVE\G

三、连接主从服务器

1. 执行START SLAVE语句开始复制：

mysql > START SLAVE;

2. 查看同步结果

主要查看Slave_IO_State、Slave_IO_Running和Slave_SQL_Running的值

mysql > START SLAVE\G

3. 查看主服务器的I/O线程创建的连接

第2行的2.row就是处理从服务器的I/O线程的连接。

mysql > SHOW PROCESSLIST\G

4. 查看从服务器的I/O线程创建的连接

第1行为I/O线程状态，第二行为SQL线程状态。

mysql > SHOW PROCESSLIST\G

注意：从服务器是通过读取主服务器的二进制日志文件实现自我更新，对数据库进行的修改操作都应该放在主服务器上执行，从服务器只用来查询，这也是所谓的读写分离。