mysql基于bin-log配置主从复制

一、mysql主库配置

1、修改/etc/my.cnf文件

[mysqld]
server-id=1
log_bin=master-log-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
innodb_flush_log_at_trx_commit=1
binlog_format=mixed

注意：

• 此配置一定要在[mysqld]之下；
• server-id与从库的server-id要不一致；
• log_bin配置二进制日志文件；
• binlog-ignore-db配置忽略系统数据库、测试库记录二进制日志；
• innodb_flush_log_at_trx_commit=1,该模式是最安全的，但也是最慢的一种方式。在mysql服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
• binlog_format配置二进制日志格式；

binlog_format三种格式

• STATEMENT模式（SBR）
  每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如uuid()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)；
• ROW模式（RBR）
  不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
• MIXED模式（MBR）
  以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。(推荐使用)

修改完成后，重启数据库。

2、主库创建备份的账号并授权

1. 创建备份账号

mysql> create user 'repl'@'%' identified by '123!@#';

2. 授权

mysql> grant replication slave on *.* to 'repl'@'%';
mysql> flush privileges;

二、主从数据初始化同步

1、对主进行锁表

mysql> flush tables with read lock;

注意：
对主进行锁表的目的，是在初始化同步过程中防止主库数据变动。但是会导致，整个数据库表的阻塞导致数据库服务不可用。

2、记录log-bin的位置

mysql> show master status;

3、主数据库备份

mysqldump --all-databases --master-data > dbdump.db

将主数据库备份文件scp到从服务器

4、主数据库进行解锁

mysql> unlock tables;

4、主备份文件导入从数据库

mysql < dbdump.db -uroot -p

三、从数据库配置

1、修改/etc/my.cnf文件

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
relay_log_recovery=1

修改完成后，重启数据库。

2、在从上设置主从配置

mysql> stop slave;
mysql> change master to 
       master_host='master IP',
       master_user='repl',
       master_password='123！@#',
       master_log_file='mariadb-logbin.000001',#主库show master status 显示的file文件
       master_log_pos=769;#主库show master status 显示的postion数字
 mysql> start slave;

至此，已完成mysql基于数据库二进制bin-log完成主从同步。