mysql主从工作_mysql主从复制配置 & 工作原理

MySQL主从复制的基本工作原理

MySQL支持两种复制方法：基于语句的复制(from Version3.23)和基于行的复制(from Version5.1)。这两种复制方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。

复制模式

基于语句的复制(又称基于逻辑的复制)：主库记录造成数据更改的查询，从库读取并重放这些事件，实际上是把主库执行过的SQL再执行一遍。

优点：实现简单；二进制日志紧凑，相对而言占用带宽少；

不足：无法处理带有当前时间戳， CURRENT_USER()函数的语句；不是所有存储引擎都支持这种复制模式；

基于行的复制：将实际数据记录在二进制日志中。

复制如何工作

在主库上把数据更改记录到二进制日志(Binary Log)中。

从库将主库上的日志复制到自己的中继日志(Relay Log)中。

从库读取中继日志中的事件，将其重放到从库数据之上。

主从复制配置

主从配置主要分为以下几步：

创建从库访问主库时使用的账号；

配置主库和从库；

启动复制。

创建账号

在MySQL主库创建一个用户，并赋予其REPLICATION SLAVE权限。

mysql>CREATE USER 'mysqlsync'@'%' IDENTIFIED BY 'password';

mysql>GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';

配置主库和从库

在主库的my.cnf配置文件，[mysqld]中增加以下内容：

log_bin=mysql-bin # [必须]启用二进制日志

server-id=222 # [必须]服务器唯一ID，默认是1，一般取IP最后一段

# [可选] [解释部分配置项]

binlog_ignore_db=mysql # 设置忽略备份的数据库

如果之前没有在配置文件中制定log-bin选项,需重启MySQL。使用show master status命令可以检查是否已经创建二进制文件。

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 551 | mstest | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

启动复制

在从库做如下配置：(不建议修改my.cnf配置文件，以下语句可以代替修改my.cnf中的相应设置)

mysql> CHANGE MASTER TO MASTER_HOST='localhost',

-> MASTER_USER='mysql',

-> MASTER_PASSWORD='mysql',

-> MASTER_LOG_FILE='mysql-bin.00001',

-> MASTER_LOG_POS=551;

更多CHANGE MASTER语法请见： MySQL Doc CHANGE MASTER TO Syntax

其中MASTER_LOG_POS参数设置为551，表示从主库master binlog的postition。执行完以后，可以通过以下命令检查复制是否正确执行

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: localhost

Master_User: mysql

Master_Port: 3001

Connect_Retry: 60

Master_Log_File: mysql-bin.00001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.00001

Slave_IO_Running: No

Slave_SQL_Running: No

这两项参数 Slave_IO_Running: No ，Slave_SQL_Running: No显示从库复制尚未开始运行，使用以下命令开始复制：

mysql> START SLAVE;

Query OK, 0 rows affected (0.01 sec)

再次使用SHOW SLAVE STATUS\G命令检查： Slave_IO_Running，Slave_SQL_Running两项参数都显示yes即为启动复制成功。

ps : 当两项参数中有出现no的情况，请注意查看log: /log/mysql.err中的详细信息。

扩展-配置GTID主从复制

配置GTID主从复制 点击连接查看官网 。官网上给出的方式是在启动mysql server时指定gtid-mode=on，同时指定其它相关参数。这些参数也可以在etc/my.cnf中配置。

在主从库的etc/my.cnf中做如下配置(配置前，先设置主从库read-only,并将主从库shutdown)：

[mysqld]

#GTID:

gtid-mode=on

enforce-gtid-consistency=on

server-id=2003306 #每个实例有唯一的server_id

#binlog

log-bin=my-sqlbin # 5.6版本 gtid-mode=on需要该参数项设置

log-slave-updates=1 # 5.6版本 gtid-mode=on需要该参数项设置为1.

# 官网描述：Whether the slave should log the updates performed by its SQL thread to its own binary log.

binlog-format=ROW #强烈建议，其他格式可能造成数据不一致

#relay log

skip_slave_start=1 #从库配置

在从库上设置(此部分默认在基础主从配置上完成，其它设置参数请参见上文)：

mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

Query OK, 0 rows affected (0.01 sec)

设置完成后，start slave, 并通过show slave status查看主从复制，从库I/O, SQL线程状态。

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: mysqlsync

Master_Port: 6001

Connect_Retry: 60

Master_Log_File: mysql-bin.000033

Read_Master_Log_Pos: 151

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000033

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

查看gtid_mode

mysql> show global variables like 'gtid_mode';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| gtid_mode | ON |

+---------------+-------+

1 row in set (0.00 sec)

参考