mysql 5.6主从复制_Mysql5.6主从复制

搭建(192.168.1.10 -> 192.168.1.20)

1 master 上执行

阻塞 DML

flush tables with read lock;

记录 File 和 Position

show master status;

mysqldump -u root -p --all-databases > /data/dbbak/db.dmp

如果此 master 同时亦为 slave

mysqldump -u root -p --all-databases --dump-slave > /data/dbbak/db.dmp

unlock tables;

scp /data/dbbak/db.dmp [email protected]:/data/dbbak

2 slave 上执行

如果备份产生于 slave

mysqladmin -u root -p shutdown

/usr/local/mysql/bin/mysqld_safe --user=mysql --skip-slave-start &

mysql -u root -p < /data/dbbak/db.dmp

3 master 上执行

[mysqld]

server-id = 110

log-bin = /data/mysql/mysql-bin

binlog_format = MIXED

skip-slave-start = 1

#auto_increment_offset = 1

#auto_increment_increment = 1

create user [email protected] identified by ‘xxxxxxxx‘;

grant replication slave on *.* to [email protected];

4 slave 上执行

[mysqld]

server-id = 120

log-bin = /data/mysql/mysql-bin

binlog_format = MIXED

skip-slave-start = 1

relay-log = rep_relay_log

relay-log-index = rep_relay_log

read_only = 1

#sync_master_info = 0

#sync_relay_log = 0

#sync_relay_log_info = 0

#log-slave-updates = 1

#replicate-rewrite-db = cisdb -> newdb

#replicate-do-db = cisdb

#replicate-ignore-db = cisdb

#replicate-do-table = cisdb.t1

#replicate-ignore-table = cisdb.t1

#replicate-wild-do-table = cisdb.%

#replicate-wild-ignore-table = cisdb.%

#slave-skip-errors = xxxx,xxxx

#relay_log_purge

#relay_log_space_limit

#slave_exec_mode

#slave_compressed_protocol

#slave_parallel_workers

change master to

MASTER_HOST=‘192.168.60.202‘,

MASTER_USER=‘repl‘,

MASTER_PASSWORD=‘1‘,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=120;

start slave;

5 salve 上执行

检查 Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes

检查 Seconds_Behind_Master

show slave status\G;

检查 Slave_SQL_Running 线程的 Time 值(延时，单位为秒)

show processlist\G;

为方便主从切换：

create user [email protected] identified by ‘xxxxxxxx‘;

grant replication slave on *.* to [email protected];

原文：http://www.cnblogs.com/zping/p/4810580.html