mysql主从复制

一、什么是Binlog?

Mysql的二进制日志可以是Mysql最重要的日志， 记录了所有的DDL和DML语句（除了数据查询语句之外的语句）语句，以事件形式记录，还包含语句所执行的消耗时间，Mysql的二进制日志是事务安全型的。

二进制日志包含两类文件：

1、二进制日志索引文件（文件后缀为".index"）用于记录有所的二进制文件；

2、二进制日志文件（文件后缀为“.00000*”）记录了数据库所有的DDL和DML（除了数据查询语句之外的语句）

二、Binlog类型

Mysql Binlog种类有三种：Statement、Mixed、Row。

1、Statement：语句级，binlog会记录每次执行写操作的语句。

优点：节省空间。

缺点：有可能造成数据不一致。

2、row：行级，binlog会记录每次操作后每行记录的结果。

优点：保持数据的绝对一致性。

缺点：占用较大空间。

3、mixed：statement的升级版本，一定程度上解决了因为一些情况而造成的statement模式不一致问题，默认还是statement，在某些情况下，譬如：当函数中包含UUID()时，包含AUTO_INCREMENT字段的表被更新时；执行INSERT DELAYED语句时；用UDF时；会按照ROW的方式进行处理

优点：节省空间，同时兼顾了一定的一致性。

缺点：还有些极个别情况依旧会造成不一致，另外statement和mixed对于需要对binlog的监控的情况都不方便。

三、主从同步机制

1、从数据库执行start slave，开启主从复制开关，slave服务器的IO线程请求从master服务器读取binlog（如果该线程追赶上了主库，会自动进入休眠状态）。

2、主数据库的更新SQL（update、insert、delete）被写到binlog，主库的binlog dump thread会把binlog的内容发送到从库。

3、从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写到relaylog（会记录位置信息，一遍下次继续读取）。

4、从服务器的sql线程会实时检测relaylog中新增的日志内容，把relaylog解析成sql语句并执行。

四、主从复制配置

1、master库配置

#配置文件配置以下
vim /etc/my.conf

log-bin=mysql-bin
server-id=1
binlog_format=statement         #复制模式
max_binlog_size=100M         #超过max_binlog_size或超过6小时会切换到下一序号文件
binlog_cache_size=16M
expire_logs_days= 7                           #日志过期时间，设置为0则永不过期
relay_log_recovery  = 1            #当slave从库宕机后，假如relay-log损坏了
innodb_flush_log_at_trx_commit = 1     #每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误
log_bin_trust_function_creators = TRUE #若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步

添加账户

#添加同步账户
grant replication slave on *.* to replication@'172.19.25.97' identified by 'Test@123456';

#刷新权限
FLUSH PRIVILEGES;

#查看master状态
show master status;        #记录查询结果中的File、Position,配置从库时会用到

 2、slave从库配置

#配置文件配置以下
vim /etc/my.conf

log-bin=mysql-bin
server-id=2            #与主库不能一致
binlog_format=statement         #复制模式
max_binlog_size=100M         #超过max_binlog_size或超过6小时会切换到下一序号文件
binlog_cache_size=16M
expire_logs_days= 7                           #日志过期时间，设置为0则永不过期
relay_log_recovery  = 1            #当slave从库宕机后，假如relay-log损坏了
innodb_flush_log_at_trx_commit = 1     #每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。

relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

#需要同步的库
replicate-do-db=demo

 连接信息配置

CHANGE MASTER TO
MASTER_HOST = '172.18.10.119',  
MASTER_USER = 'replication',
MASTER_PASSWORD = 'Test@123456',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000005', #使用从主库查询的结果
MASTER_LOG_POS=154,              #使用从主库查询的结果
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

启动

#开启同步
start slave;

#查看同步状态
show slave status\G;