mysql5.6 基于日志主从复制

from 原文

主库配置:

[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
#tmpdir=/var/lib/mysql/temp/
log-bin=/var/lib/mysql/mysqlmaster-bin

server-id=1
sync_binlog=1
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1

从库配置:

[mysqld]
server-id=2 #id不同
sync_binlog=1
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1

pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
#tmpdir=/var/lib/mysql/temp/
log-bin=mysqlslave-bin.log

主库操作:

1. 分配可访问主库的用户

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库ip或%' IDENTIFIED BY 'repl';

2. 锁库,暂时停止主库的写入

FLUSH TABLES WITH READ LOCK;

3. 导出主库的数据sql

mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases  --triggers --routines --events >all.sql

4. 查看主库当前二进制日志文件信息

SHOW MASTER STATUS;

从库操作:

1. 导入主库sql
2. 设置主库的信息(具体值按SHOW MASTER STATUS;取)

CHANGE MASTER TO MASTER_HOST='主库ip',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=332;

3. 开启从库

START slave;

4. 检查连接情况:

SHOW slave STATUS \G

如果有:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

则配置成功

主库开锁:

UNLOCK TABLES;