网络知识 娱乐 linux系统MySQL主从同步--使用binlog日志方式

linux系统MySQL主从同步--使用binlog日志方式

声明: 如果您有更好的技术与作者分享,或者商业合作; 请访问作者个人网站 http://www.esqabc.com/view/message.html 留言给作者。 如果该案例触犯您的专利,请在这里:http://www.esqabc.com/view/message.html 留言给作者说明原由, 作者一经查实,马上删除。

手机留言请访问:http://www.esqabc.com

复制方式

MySQL5.6开始主从复制有两种方式:基于日志(binlog)、基于GTID(全局事务标示符)。 本文只涉及基于日志binlog的主从配置 1、基于语句的复制SBR(基于sql语句复制)-statement 在Master上执行的SQL语句,在Slave上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制

2、基于行的复制RBR(基于行的复制)-row 把改变的内容复制到Slave,而不是把命令在Slave上执行一遍。从MySQL5.0开始支持

3、混合类型的复制MBR(混合模式复制)-mixed 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制

关于复制过滤

复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:

1、在Master上过滤二进制日志中的事件 2、在Slave上过滤中继日志中的事件。

1,准备工具:

(1)linux 系统的Mysql数据库(一个作为主数据库),如果不用知道自己的数据库版本,请执行:mysql -V

(2)linux 系统的Mysql数据库(一个作为从数据库,注意:从数据库可以多个)

注意:同步的两个数据库版本一致,windows系统安装的MySql数据库命令有所不同。

2,配置Master(主数据库)

1,如果数据库正在运行,请先停止服务, 执行命令:service mysql stop

2,修改配置文件(my.cnf),安装路径不同,注意配置文件所在的位置,windows系统的文件为(my.ini), 执行命令:vim /usr/local/mysql/my.cnf 保存退出命令:按键盘Esc 然后输入 (:wq )

[mysqld]
## 设置server_id,一般设置为IP,注意要唯一,确保主从不相同
server_id=1
## 复制过滤:也就是指定哪个数据库不用同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
##设置同步数据库(如:esqsui)
binlog-do-db=esqsui 	
## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了)
log-bin=mysql-bin-1
##MySQL 磁盘写入策略以及数据安全性
##每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去
innodb_flush_log_at_trx_commit=1
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
## SBR(基于sql语句复制),RBR(基于行的复制),MBR(混合模式复制)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。单位:天
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## 设置binlog每个日志文件大小
max_binlog_size=20M
##当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去,
##sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
sync_binlog=1

3,重新启动mysql 服务,执行命令:service mysql start

4,使用命令登录数据库: (1)执行命令: mysql -uroot -p (2)创建同步账号命令:

 grant replication slave on *.* to 'bak'@'%' identified by 'bak';

(3)执行命令:show master status; 在主(master)上查看binlog日志文件,以及坐标,如下图:

(5),使用SQL语句创建同步账号: 创建账号:

CREATE USER 'bak'@'%' IDENTIFIED BY 'bak';

在主(master)上查看binlog日志文件,以及坐标: show master status; 如下图

关于复制过滤 复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:

1、在Master上过滤二进制日志中的事件 2、在Slave上过滤中继日志中的事件。

3,配置从Slave(从数据库)

1,如果数据库正在运行,请先停止服务, 执行命令:service mysql stop

2,修改配置文件(my.cnf),安装路径不同,注意配置文件所在的位置,windows系统的文件为(my.ini), 执行命令:vim /usr/local/mysql/my.cnf 保存退出命令:按键盘Esc 然后输入 (:wq )

[mysqld]
## 设置server_id,一般设置为IP,注意要唯一,确保主从不相同
server_id=2
## 复制过滤:也就是指定哪个数据库不用同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
##设置同步数据库(如:esqsui)
binlog-do-db=esqsui 	
## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了)
log-bin=mysql-bin-1
##MySQL 磁盘写入策略以及数据安全性
##每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去
innodb_flush_log_at_trx_commit=1
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
## SBR(基于sql语句复制),RBR(基于行的复制),MBR(混合模式复制)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。单位:天
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## 设置binlog每个日志文件大小
max_binlog_size=20M
##当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去,
##sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
sync_binlog=1
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1

3,重新启动mysql 服务,执行命令:service mysql start

4,完成Master(主)和Slave(从)链接

1,在从数据库中,执行命令登录:mysql -uroot -p

2,执行命令:

change master to 
master_host='192.168.1.100', 
master_user='bak', 
master_password='bak', 
master_port=3306, 
master_log_file='mysql-bin-1.000001', 
master_connect_retry=30,
master_log_pos=317;


上面执行的命令的解释:

master_host=’192.168.1.100’ ## Master的IP地址

master_user=’bak’ ## 用于同步数据的用户(在Master中授权的用户)

master_password=’bak’ ## 同步数据用户的密码

master_port=3306 ## Master数据库服务的端口

master_log_file=’mysql-bin-1.000001’ ##指定Slave从哪个日志文件开始读复制数据(Master上执行命令的结果的File字段)

master_log_pos=317## 从哪个POSITION号开始读(Master上执行命令的结果的Position字段)

master_connect_retry=30 ##当重新建立主从连接时,如果连接建立失败,间隔多久后重试。单位为秒,默认设置为60秒,同步延迟调优参数。

注意:如果需要修改配置信息,请执行命令:stop slave; 停止进程

3,启动进程命令:start slave 结果如下图:

4,查看结果命令:show slave statusG 结果如下:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-1.000001
...........
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...........
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O
 ...........
1 row in set (0.00 sec)

会发现Slave_IO_Running和Slave_SQL_Running是Yes了,表明开启成功

5,在主数据库,新增一条数据,然后去从数据库查看,如果看到从数据库可以查到,就说明配置成功了