mysql主从配置 简书_Mysql 主从配置

主从原理

从《高性能mysql》书中讲解主从原理大致有三个步骤：在主库上把数据更改记录到二进制日志中(Binary Log)中，这些记录称为二进制日志事件。

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

从库通过SQL线程读取中继日志中的事件，将其重放到自己数据上。

Mysql主从配置原理图

注意要点：

保证主从mysql的版本号一致，网上说保持大版本号一致即可，比如5.6.53和5.6.80也是可以的，但是未进行测试，本人采用的2个一模一样版本号的mysql。

本文档是在windows系统上进行配置(配置文件my.ini)，linux系统的文件名(my.cnf)略有区别。

如果要为已经有数据的主服务器添加从服务器，要先把主库已经存在的数据先手动同步迁移到从库上面去。搭建过程中，禁止在主库从库上进行任何对数据库的ddl、dml等数据操作。

主从示例

说明

IP

主数据库

192.168.1.100

从数据库

192.168.1.101

官方文档知识点

官方文档推荐的是，在master端不指定binlog-do-db，在slave端用replication-do-db来过滤。

为了在使用InnoDB事务的复制设置中实现最大的持久性和一致性，您应该在主库配置文件中使用 innodb_flush_log_at_trx_commit=1和sync_binlog=1

确保skip-networking 未在复制主服务器上启用该选项。如果已禁用网络，则从属设备将无法与主设备通信，并且复制将失败。

skip-networking说明

#在mysql中执行查询，查看skip-networking是否启用(主从配置设置值需为OFF)

show variables like '%skip_networking%';

属性

值

命令行格式

--skip-networking

系统变量

skip_networking

范围

全局

动态

没有

不监听TCP / IP连接。所有与mysqld的交互必须使用命名管道或共享内存(在Windows上)或Unix套接字文件(在Unix上)。对于仅允许本地客户端的系统，强烈建议使用此选项。请参见第8.12.5.2节“DNS查找优化和主机缓存”。

Mysql主从配置详细步骤

1.主数据库安装目录下配置文件my.ini增加以下配置

# 服务的唯一编号，保证通信之间的唯一即可，1至(2的32次方)-1 之间的正整数；

#如果省略[`server-id`]或将其明确设置为默认值0，则主服务器拒绝来自所有从服务器的连接。

server-id = 1

# 开启mysql binlog功能

log_bin = mysql-bin

# binlog记录内容的方式，记录被操作的每一行

binlog_format = ROW

# 指定需要复制的数据库名为jgyw

binlog-do-db = jgyw

#可选配置，过滤要指定的数据库

#binlog-ignore-db=mysql

2.从数据库安装目录下配置文件my.ini增加以下配置

# 服务的唯一编号，保证通信之间的唯一即可，也可以自己取一个数字ID，1至几万都可以

server-id = 2

# 开启mysql binlog功能

log-bin = mysql-bin

# binlog记录内容的方式，记录被操作的每一行

binlog_format = ROW

# 指定需要复制的数据库名为jgyw

replicate-do-db = jgyw

#如果需要同步多个，则再来一行replicate-do-db = jgyw2

3.重启2个mysql

修改完配置以后，确保还是能正常跑起来。

4.在主库中增加权限账号

#SQL语句，建立一个帐户，并且只能允许从`ip地址`这个地址上来登陆，添加完成后可在从服务器上用mysql -hIP地址 -u账号 -p密码; 来测试是否有权限访问主数据库。

#在master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。

grant replication slave on *.* to '账号'@'ip地址' identified by '密码';

#刷新权限

flush privileges;

#示例，可选权限：SLAVE,RELOAD,SUPER

mysql > grant replication slave on *.* to 'jgyw'@'192.168.1.101' identified by 'jgyw@123';

flush privileges;

5.手动同步数据【可选，如果是新数据库可以略过此步骤】

如果要为已经有数据的主服务器添加从服务器，要先把主库已经存在的数据先手动同步迁移到从库上面去。搭建过程中，禁止在主库从库上进行任何对数据库的ddl、dml等数据操作。

可以用可视化管理工具(如navicat)把数据传输到从库上面。

6.设置复制起始点

#查看主库的起始点

mysql> SHOW MASTER STATUS\G

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

File: mysql-bin.000001

Position: 120

Binlog_Do_DB: jgyw

Binlog_Ignore_DB:

1 row in set (0.00 sec)

以上可以看到File: mysql-bin.000001，Position: 120，记录下来，下面有用，Position这个值会随着操作不断的累加

在从库中添加连接主库的权限账号

mysql> CHANGE MASTER TO

Master_Host='192.168.1.100',

Master_Port=3306,

Master_User='jgyw',

Master_Password='jgyw@123',

Master_Log_File='mysql-bin.000001',

Master_Log_Pos=120,

Master_Connect_Retry=60;

在从库中对slave进行重启

mysql> slave stop;

#如果敲错地方了，在主库中敲会提示ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

mysql> slave start;

在从库中敲如下命令，重点查看2个Slave_IO_Running和Slave_SQL_Running都是YES，则表示正常

mysql> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: jgyw

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 28944

Relay_Log_File: DESKTOP-EJG0Q0H-relay-bin.000009

Relay_Log_Pos: 12215

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: jgyw

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: 28944

Relay_Log_Space: 12527

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: 1

1 row in set (0.00 sec)

完。

下面就可以测试在主库中进行操作，看看从库是否能同步更新啦~~