mysql主从同步配置

主：192.168.199.200

从：192.168.199.201

1.主mysql开启binlog功能

/etc/my.cnf #配置文件

[mysqld]
log_bin = /data/mysql/mysql_m-bin #开启binlog
server_id=1 #设置id，避免丢失
binlog_format="mixed" #设置同步模式

2.重启mysql,后生成日志文件

cat /data/mysql/mysql_m-bin.000001 #把里面记录的日志文件名记录下来等一下从库要用到

3.创建用户并授权

mysql -uroot -p
grant replication slave on *.* to "cs-1"@"192.168.199.%"identified by "wangwei"; 
flush privileges;

4.查看主库id和状态

SHOW VARIABLES LIKE 'server_id'; #查看id
SET GLOBAL server_id=1;  #修改id，保证主从库id不同

show master status; #记录下245等下配置从库要用
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mysql_m-bin.000002 | 245 | | |
+--------------------+----------+--------------+------------------+

 6.配置从库（在/etc/my.cnf配置也可以）

mysql -uroot -p
CHANGE MASTER TO  
MASTER_HOST='192.168.199.200',  #主库IP
MASTER_USER='cs-1',   #账号
MASTER_PASSWORD='wangwei', #密码  
MASTER_LOG_FILE='masters-bin.000001', #主库日志文件名  
MASTER_LOG_POS=245; 主库的245

7.启用从库配置，然后启动失败，查看日志解决问题

start slave; #启动
stop slave; #关闭
show slave status\G #查看状态

 日常配置

CHANGE MASTER TO MASTER_DELAY = 1800 #修改同步时间
change master to master_heartbeat_period = 10; #修改从库心跳为10秒一次
set global slave_net_timeout = 25; #修改从库超时时间
show slave hosts; #主库查看从库状态

 

MYSQL中BINLOG_FORMAT的三种模式

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

 

[mysqld]
server-id = 1        # 节点ID，确保唯一

# log config
log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行

auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误