MySQL主从复制实战

一、主数据库设置

---

• 主mysql增加如下配置到[msqld]

server-id=1
log-bin=/var/log/mysql/mysql-bin

gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1

# 优化参数：非必要
#innodb_flush_log_at_trx_commit = 1 # InnoDB 在事务提交后从buffer写入二进制日志并flush到磁盘的频率， InnoDB 引擎特有。默认值为1，可不用显示配置。
#sync_binlog = 0 # 二进制日志同步到磁盘的频率,默认为0，可不用显示配置。

# 优化参数：非必要
#binlog-do-db=  # 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可,如果不添加此项则默认复制所有数据库。
#binlog-ignore-db=  # 不需要复制的数据库苦命，如果复制多个数据库，重复设置这个选项即可

• 优化参数解释：

sync_binlog默认值是 0，不主动同步，而依赖操作系统本身不定期把文件内容 flush 到磁盘。设为1最安全，在每个语句或事务后同步一次binary log
推荐设置为0或100。对于支付服务推荐 sync_binlog = 1

innodb_flush_log_at_trx_commit 取值为 0 的时候，log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘
当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
当取值为 2 时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。

• 创建二进制日志存放目录

mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql

• 创建具有复制权限的mysql账号

CREATE USER 'copy'@'%' 
GRANT REPLICATION SLAVE ON *.*  TO  'copy'@'%'  identified by 'copy';

• 备份主服务器数据(如果有的话)

mysqldump  --master-data=2 --flush-logs --all-databases > /root/`date +%F-%H-%M-%S`.sql

myisam引擎加上：--lock-all-tables
innodb引擎加上：--single-transaction

二、从数据库设置

---

• 从服务器配置里，[mysqld]添加如下内容

server_id=22
read-only=on  # 开启只读,对拥有super权限的用户无效   

gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1

#replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
#replicate-ignore-db=需要忽略复制的数据库名，如果复制多个数据库，重复设置这个选项即可

• 导入备份

mysql -uroot -proot < /root/2016.sql

• 连接主服务器

CHANGE MASTER TO MASTER_HOST = '10.*.*.36', MASTER_USER = 'repl', MASTER_PASSWORD = 'replpassword', MASTER_PORT = 3306, MASTER_AUTO_POSITION = 1;

• 启停、重置复制功能

start slave;
stop slave;
reset slave;