mysql数据同步-基于二进制日志文件和position复制点的方式-白红宇

mysql数据同步-基于二进制日志文件和position复制点的方式

阅读量：7080 次

发布时间：2019-06-28

本文共 3999 字，大约阅读时间需要 13 分钟。

mysql的binlog日志有三种格式，分别为statement，mixed，以及row。

1.Statement：每一条会修改数据的sql都会记录在binlog中

优缺点：1）不用记录每一行的变化，日志量小（执行update table set column=xx，只需记录一条sql即可）；

2）有些sql语句可能会跟数据库环境有关，比如登录用户等等数据，导致复制的时候数据出问题。

2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改（默认的数据复制方式，通过show variables like 'binlog_format'命令查看）

优缺点：1）binlog日志会记录每一行数据修改的细节，不会使用和当前数据库环境相关的数据；

2）日志量较大（如执行update table set column=xx，会记录和表数据数量一样的日志记录）

3.Mixedlevel: 是以上两种level的混合使用

mysql数据复制架构

一、主从架构（Master-Slave）

优缺点：

1）一台master对应多台slave，数据修改可在master操作，数据查询分别在2台slave中操作，主要用于读操作大于写操作的请情况，可提高数据库的查询性能；

2）如果slave比较多，会严重增加master数据库的压力，因为一台slave连上master时，会开启3个线程（Connection Thread 连接master的线程，IO Thread slave接受master二进制数据的线程， SQL Thread slave处理日志重放的线程）；

3）可能会造成单点故障，因为master只有一台，如果master宕机之后，需要重新指定master；

4）slave库的数据会出现延时（延时时间为slave端运行show slave status时，Seconds_Behind_Master选项的值）。

实现步骤：（默认都是新装的机器，数据库中都没有数据）

1、安装3台mysql服务器，一台master，二台slave，master的端口是3307，二台slave的端口分别是3308、3309；

2、安装成功之后，修改master配置my.ini文件（假设master的服务名为mysqlmaster），我的配置如下：

[mysqld]

innodb_buffer_pool_size = 128M

#启用二进制日志

log_bin=mysql-bin

innodb_flush_log_at_trx_commit=1

#事物一提交，就必须同步二进制日志，这样会降低性能，但是数据比较重要

sync_binlog=1

#服务器ID不能重复

server_id=10

#需要做主从备份的数据库名字

#binlog-do-db=test_custer

#只保留7天的二进制日志，以防磁盘被日志占满

expire-logs-days=7

#mysql的安装目录

basedir=D:\mysqlcuster\mysqlmaster

#mysql数据存放目录

datadir=D:\mysqlcuster\mysqlmaster\data

port=3307

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#服务端的编码方式

character-set-server=utf8

[client]

port=3307

default-character-set=utf8

3、使用net start mysqlmaster启动master服务器，启动成功之后，登录mysql，命令为mysql -uroot -p -P3307；

4、在master中创建用于复制的账号，账号名、密码、ip可修改：grant replication slave on *.* to repl_user@127.0.0.1 identified by 'repl_password'；

5、修改slave的my.ini文件配置，以其中一台slave为例，配置如下：

[mysqld]

innodb_buffer_pool_size = 128M

#需要做复制的数据库名

#replicate-do-db=test_custer

#服务器id

server_id=2

#自动跳过的表，session表没必要做复制

#replicate-ignore-table=dzx2.pre_common_session

#自动跳过的错误代码，以防复制出错被中断

#slave-skip-errors=1032,1062,126,1114,1146,1048,1396

#避免从库意外崩溃，导致数据重复复制

sync_master_info=1

basedir=D:\mysqlcuster\mysqlslave

datadir=D:\mysqlcuster\mysqlslave\data

port=3308

#设置从节点只读，防止从节点写入数据，导致主从数据库数据不一致

read_only=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#服务端的编码方式

character-set-server=utf8

[client]

port=3308

default-character-set=utf8

6、使用net start mysqlsalve启动salve服务器，启动成功之后，登录mysql，命令为mysql -uroot -p -P3308和mysql -uroot -p -P3309；

7、在master端输入show master status，得到查询结果file和position；

8、在slave设置master的信息

change master to master_host='master的ip',

master_user='master创建的复制账号',

master_password='master创建的复制账号用户名',

master_port=master的数据库端口,

master_log_file='master运行show master status的结果中的file字段值',

master_log_pos=master运行show master status的结果中的position字段值';

9、在slave端运行start slave，之后再运行show slave status\G;如果查询结果中的Slave_IO_Running、Slave_SQL_Running的值都为yes，表示环境设置成功，如图所示

10、测试。在master中创建test_custer数据库和test表，查看slave数据库也会存在test_custer数据库和test表。

二、主主架构（Master-Master）

master-master模式和master-slave模式差不多，需要在2台master中打开二进制文件，只是master-master模式中，存在2台主机，2台主机均可执行读写操作，这种模式有个缺点就是怎么保证数据完整性，因为2台主机均可执行写操作，可能会存在一部分数据存放在master1，另一部分数据存放在master2中，如果master1宕机了，整个数据库环境的数据也就不完整了。

三、主从从架构（Master-Slave-Slave）

优点：1）减轻master的复制压力；

2）master宕机后，slave可充当master角色，可避免单点故障

步骤：（master-slave-slave2，即slave充当slave2实例的master角色）

1）修改master配置文件my.ini，同上；

2）修改slave、slave2配置文件，同上。不过需要在slave服务器配置文件中新加2项配置

#二进制日志文件

log_bin=myslave-bin

#从服务器从主服务器接收到的更新记入到slave的二进制日志

log_slave_updates=1

3）分别启动master、slave、slave2服务器

4）登录master服务器，输入show master status;命令，查看file和position字段的值