通过binlog实现主从数据同步

通过binlog实现主从数据同步

一.主库配置

1. 配置文件my.cnf

[mysqld]
log-bin = mysql-bin
binlog-format = ROW
server_id =1

2. 查看主库的日志为了从库的配置
   1. 记录File和Position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File                 | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     4801 |                 |                     |                        |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二.从库配置

1. 配置文件my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin
exipre_logs_days=100

2. 接入master

mysql> CHANGE MASTER TO 
	->master_host='192.168.0.10',              ##master的IP
	->master_port=3306,
	->master_user='repl',
	->master_password='repl',
	->master_log_file='mysql-bin.000001',   ## 刚刚记录的 主库上 show master status;
	->master_log_pos=154;                      ## 刚刚记录的
Query OK, 0 rows affected, 2 warnings (0.00 sec)

3. 启动同步：a.启动slave：START SLAVE; ; b.查看状态：SHOW SLAVE STATUS\G;

三.常用命令

1. 查看binlog状态：

   1. 主库： SHOW MASTER STATUS\G;
   2. 从库： SHOW SLAVE STATUS\G;

2. 手动清理binlog文件

   1. 按照时间节点：mysql> purge master logs before'2016-09-01 17:20:00';
   2. 按照binlog文件：mysql> purge master logs to 'mysql-bin.000001';

注意：

1. 时间和文件名一定不可以写错，尤其是时间中的年和文件名中的序号，以防不小心将正在使用的binlog删除！！！
2. 切勿删除正在使用的binlog！！！使用该语法，会将对应的文件和mysql-bin.index中的对应路径删除。

3. 自动清理：设置过期时间

注意：

1. 过期时间设置的要适当，对于主从复制，要看从库的延迟决定过期时间，避免主库binlog还未传到从库便因过期而删除，导致主从不一致！！！

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set global expire_logs_days = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+
1 row in set (0.00 sec)

四.binlog的三种工作模式

1. Row level
   日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
   　　优点：能清楚的记录每一行数据修改的细节
   　　缺点：数据量太大

2. Statement level（默认）
   每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
   　　优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高性能
   　　缺点：容易出现主从复制不一致

3. Mixed（混合模式）
   结合了Row level和Statement level的优点

根据优缺点，在不同的业务场景使用不同的模式。

1. 主从强一致性：ROW
2. 效率要求较高：Statement Level
3. 兼得：Mixed

五.注意

旧数据要手动同步

mysqldump --single-transaction --master-data=2 -uroot -ppassword -hipdbname > dump.sql