原文链接： mysql 主从、主主复制

slave端配置

1.中继日志是连接mastert和slave的信息，它是复制的核心，I/O线程将来自master的事件存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个事件。

relay_log=relay-log     

2.只读模式,仅对非super权限的用户有效

read_only=on

set global read_only=1;
show global variables like "%read_only%";

3.设置备节点延迟的时间，单位秒。

stop slave; change master to master_delay = 1800;start slave; #延时半小时
stop slave; change master to master_delay = 300;start slave; #延时五分钟

1. mysql主从部分库表同步

replication 中通过以下参数减少binlog数据量
binlog-do-db：指定mysql的binlog日志记录哪个db
replication-do-db 参数是在slave上配置，指定slave要复制哪个库

master端：

设置同步的数据库，多个库写多条语句

--binlog-do-db 二进制日志记录的数据库（多数据库用逗号，隔开）

忽略同步的数据库，多个库写多条语句

--binlog-ignore-db 二进制日志中忽略数据库 （多数据库用逗号，隔开）

binlog-do-db = lybb
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog-ignore-db = test

slave端

--replication-do-db 设定需要复制的数据库（多数据库使用逗号，隔开）
--replication-ignore-db 设定需要忽略的复制数据库 （多数据库使用逗号，隔开）
--replication-do-table 设定需要复制的表
--replication-ignore-table 设定需要忽略的复制表
--replication-wild-do-table 同replication-do-table功能一样，但是可以通配符
--replication-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

2. master.00001 binlog日志自动删除时间

expire_logs_days=5

3. 设置主从复制延时时间

[](http://www.cnblogs.com/zhoujinyi/p/4527964.html)

master端：主动预防

mysql 可以指定三个参数，用于复制线程重连主库：

--master-retry-count = 86400 
--master-connect-retry = 60 
--slave-net-timeout = 3600

具体的重试策略为：备库过了 slave-net-timeout 秒还没有收到主库来的数据，它就会开始第一次重试。然后每过 master-connect-retry 秒，备库会再次尝试重连主库。直到重试了 master-retry-count 次，它才会放弃重试。如果重试的过程中，连上了主库，那么它认为当前主库是好的，又会开始 slave-net-timeout 秒的等待。

slave-net-timeout 的默认值是 3600 秒， master-connect-retry 默认为 60 秒， master-retry-count默认为 86400 次。也就是说，如果主库一个小时都没有任何数据变更发送过来，备库才会尝试重连主库。这就是为什么在我们模拟的场景下，一个小时后，备库才会重连主库，继续同步数据变更的原因。

实战：mysql主从热备

slave通过master的binary log，采用异步的方式将master的数据复制到slave端。

1. master端

1. 创建replication用户
   grant replication slave on *.* to 'repl'@'%' identified by '123456';

   grant lock tables, reload, replication slave,replication client on *.* to 'repl'@'10.114.0.%' identified by '123456'; flush privileges;
   

   1. 修改配置文件
      master和slave的server-id不能一致
      
      

   for master replication

   [server]
   server_id=1
   log_bin=master-binlog
   log-bin-trust-function-creators=1 # view dont reqiure super privileged
   
   #binlog-do-db = lybb
   binlog-ignore-db = mysql,information_schema,performance_schema 
   binlog-ignore-db = test
   
   expire_logs_days = 7	#binlog files older than this will be purged
   max_binlog_size  = 256M #max size for binlog before rolling
   
   [mysqld]
   
   # 多主模式
   #auto-increment-offset = 1
   #auto-increment-increment = 3
   
   #[实时写入 可选] 每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘,最大限度保证可靠性和一致性
   innodb_flush_log_at_trx_comit=1 
   sync_binlog = 1 
   
   

2. 封锁commit操作 简称(ftwrl)，该命令主要用于备份工具获取一致性备份(数据与binlog位点匹配)

   flush tables with read lock; 
   show master status;     
   mysqldump -uroot -p --master-data -b test > backup_test.sql 
   unlock tables;
   

   note：不要退出当前mysql cli，否则lock失效

   1. 查看binary log文件名和坐标
      show master status;
      记录file和position的输出
      
      
      

   slave配置

   修改配置文件
   vim /etc/my.cnf.d/server.cnf

   for slave replication

   [server]
   server-id=2
   read_only=ON    #只读设置，仅对非super权限的用户有效
   
   relay_log=relay-log
   log-slave-updates = ON #[必须]中继日志执行之后将变化写入自己的二进制文件 
   
   # replication-do-db=lybb
   replicate-ignore-db = mysql,information_schema,performance_schema 
   
   slave-skip-errors = all
   
   

3. 停止slave

   stop slave;
   reset slave all;
   

   1. 设置写入缓存大小。提高mysql导入速度。(和磁盘io差不多就行)
      set global bulk_insert_buffer_size=128*1024*1024;
      
      

   mysql -uroot -p < backup_test.sql
   

4. 配置master信息
   CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=560688166;
   master_use_gtid=current_pos; GTID复制功能

   change master to master_host='10.114.0.10', master_user='repl', master_password='123456', master_log_file='master-log.000002', master_log_pos=560688166, master_delay = 0;
   master-binlog.000001
   
   change master to master_host='10.26.28.74', master_user='repl', master_password='123456', master_log_file=' master-binlog.000001', master_log_pos=617;
   

   1. 修改缓存。启动复制
      set global bulk_insert_buffer_size=8*1024*1024;
      
      

   start slave;
   

后文

如果是双主热备，反过来将slave也配置成master即可

auto-increment-increment = 2    //每次增长2
auto-increment-offset = 1       //设置自动增长的字段的偏移 1 3 5 7

进入从服务器去复制主服务器的配置文件和数据

    change master to master_host='10.114.0.10',master_user='repl',master_password='123456',master_log_file='master-log.000003',master_log_pos=713404672;	#定义从主服务器的哪里开始复制文件
    start slave io_thread;            	#启动，开始复制
    show slave status\G			#查看从节点的slave 是否为开启状态
                这么多信息中，我们只需要看2项，只要为yes即可，分别是：
                slave_io_running: yes     # 去主库读二进制日志，然后保存到从库去
                slave_sql_running: yes    # 将中继日志转换成为sql语句执行
    show databases;			#查看数据库是否复制过来
    show databases;			#查看数据库里的表是否复制过来

注意： 如果这里主服务器已经工作很长时间，里面有太多以前存储的数据。需要先把主服务器的数据备份出来导入从服务器，在进行后期的主从同步复制。这样可以避免复制时主服务器的i/o压力过大。这里从备份文件中恢复到从节点后的复制，复制的起始点为备份操作时主节点所处的日志文件及事件记录的最后位置；另外，从服务器的服务器版本号要高于主服务器的版本号；

主主复制服务器配置：

既然两个两服务器为主主复制，那这两个服务器必须互为主从，这样两个服务器的数据库数据才能保持一样。所以这里两个节点各自都要开启binlog和relay log；

但是，这里要注意：如果某个表里定义了一个自动增长的主键或唯一键id，那么这里就会遇到一个问题了。这样的话如果两边主机同时写入一个数据，两边同时增长id号，那这个id 就会冲突，就会出现问题了。所以我们在配置时一定要注意这一点；

vim /etc/my.cnf.d/server.cnf
[server]
	server_id = 1	 # 两个主机之间的id必须不同
	log_bin = master-log
	relay = relay-log

# 这里要注意两个主机之间自动增长id时，主键的不一致。所以这里定义主键时要有所区别
	auto_increment_offset=1			#定义第一个自动增长的id 为1
	auto_increment_increment=2		#id 的步进数为二，使它一直为奇数

vim /etc/my.cnf.d/server.cnf
[server]
	server_id = 2	# 两个主机之间的id必须不同
	log_bin = master-log
	relay = relay-log

    # 这个节点使用偶数id
	auto_increment_offset=2			#定义第一个自动增长id 为2
	auto_increment_increment=2		#id 步进数为二，使id 一直为偶数

启动服务后执行如下两步：

    都授权对方一个有复制权限的用户账号；
    各把对方指定为主节点；

进入服务器(1):

grant replication slave,replication client on *.* to 'repluser'@'172.18.35.%' identified by '123456';	
	flush privileges;

# 同样的操作在 另一个服务器上也进行一遍
	grant replication slave,replication client on *.* to 'repluser'@'172.18.35.%' identified by '123456';		
	flush privileges;
	show master status\g     #查看本机主节点的日志记录状态，使172.18.35.4 可以确定从哪里开始复制

配置

    show master status\g	  #查看本机主节点的日志记录状态，使172.18.35.5 可以确定从哪里开始复制
    change master to master_host='172.18.35.5',master_user='repluser',master_password='123456',master_log_file='master-log.000001',master_log_pos=245;   #从上面172.18.35.5里查看的某个日志的位置开始复制
    start slave
    show slave status\g

进入服务器(2):172.18.35.5

    change master to master_host='172.18.35.4',master_user='repluser',master_password='123456',master_log_file='master-log.000001',master_log_pos=245;   #从上面172.18.35.4里查看的某个日志的位置开始复制
    start slave
    show slave status\g

到这里双主复制模型已经配置完毕，下面就可以进行测试了

下面写了一些复制时需要注意的问题。我们上面只说了双主的复制，没有提到多主多从的复制。你如果有兴趣可以自己试试多主多从的模型；

复制时需要注意的问题：
    1、从服务设定为“只读”；
        在从服务器启动read_only=on，但它仅对非super权限的用户有效；

    2、尽量确保复制时的事务安全
        在master节点启用参数：
            sync_binlog = on  #定义从服务器要同步复制

            如果用到的是innodb存储引擎：
                innodb_flush_logs_at_trx_commit=on  #每当事务发生时就要同步事务的二进制日志
                innodb_support_xa=on                #innodb 支持分布式事务

    3、从服务器意外中止时尽量避免自动启动复制线程
            因为服务意外中断时，可能有些日志没有执行完毕，复制时可能会出现错误。所以要把最后的日志清除，再重新进行复制。

    4、从节点：设置参数
        sync_master_info=on     #实时同步主节点的二进制日志信息
        sync_relay_log_info=on  #实时同步中继日志信息，防止服务器宕机后复制时数据的丢失

---

mysql从库恢复并开启主从复制的过程

本次过程大致如下：

1. 从主库找到一个备份文件，放到从库服务器。
2. 恢复数据到从库
    3. 找到binlog还原点
4. 设置mysql还原点
5. 启动从库开始主从复制

1. 连接到数据库

   mysql -h127.0.0.1 -uroot -p ;

2. 切换数据库

   use yourdatabase;

3. 停止主从复制

stop slave;
reset slave all;

1. 设置写入缓存大小。提高mysql导入速度。(和磁盘io差不多就行)
   
   

set global bulk_insert_buffer_size=128*1024*1024;

1. 恢复数据 （根据自己的备份方式恢复）
   
   

source /bakfile

1. 找到mysql binlog 备份点 （在linux 下面执行，其他操作系统同理）
   
   

less /bakfile

找到
-- change master to master_log_file='mysql-bin-190.000640', master_log_pos=120;
获取时间binlog文件名和pos。

1. 修改从库同步位置
   
   

change master to
master_host='10.251.192.18',
master_user='sync',
master_password='dbect98773!_sync',
master_port=3306,
master_log_file='mysql-bin-190.000640',
master_log_pos=120;

1. 修改缓存。启动复制
   
   

set global bulk_insert_buffer_size=8*1024*1024;
start slave ;

1. 查看主从同步状态
   
   

show slave status \g;

1. 如果有问题。查看mysql 错误日志。
   
2. 同步开始后可能会有少量的冲突出现 ，使用下面语句可以跳过一个事务并查看同步状态
   
   

stop slave;
set global sql_slave_skip_counter = 1 ;
start slave ;
show slave status \g;