Mysql主从同步配置方法

一、主从全新配置

1.主库配置

(1) 重置主库
mysql> reset master;

(2) 配置my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin

(3) 创建用户
mysql> create user repl@'192.168.1.%' identified by 123;
mysql> grant replication slave on *.* to rep1@'192.168.1.%';

(4) 重启数据库
[root@server01 ~]# mysqladmin -uroot -p shutdown
[root@server01 ~]# mysqld_safe --user=mysql &

(5) 获取主库二进制位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.从库配置

(1) 重置从库
mysql> reset master;

(2) 配置my.cnf
[mysqld]
server_id=2

(3) 重启数据库
[root@server02 ~]# mysqladmin -uroot -p shutdown
[root@server02 ~]# mysqld_safe --user=mysql &

(4) 配置主库信息
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.1.191',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;

(5) 开启从库复制进程
mysql> start slave;

二、主库已有数据配置

1.主库配置

(1) 配置my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin

(2) 创建用户
mysql> create user repl@'192.168.1.%' identified by 123;
mysql> grant replication slave on *.* to rep1@'192.168.1.%';

(3) 重启数据库
[root@server01 ~]# mysqladmin -uroot -p shutdown
[root@server01 ~]# mysqld_safe --user=mysql &

(4) 导出数据
[root@server01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql

(5) 获取主库二进制位置
[root@server01 ~]# more /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=194;

2.从库配置

(1) 配置my.cnf
[mysqld]
server_id=2

(3) 重启数据库
[root@server02 ~]# mysqladmin -uroot -p shutdown
[root@server02 ~]# mysqld_safe --user=mysql &

(3) 导入数据
[root@server02 ~]# scp 192.168.1.191:/tmp/full.sql /tmp
mysql> source /tmp/full.sql

(4) 配置主库信息
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.1.191',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=194,
  MASTER_CONNECT_RETRY=10;

(5) 开启从库复制进程
mysql> start slave;

三、重新配置从库步骤

在遇到故障后，需要重新配置从库才能恢复，则使用如下步骤

1.从库操作

(1) 停止slave进程
mysql> stop slave
或者使用--skip-slave-start参数重启数据库:
[root@server02 ~]# mysqladmin -uroot -p shutdown
[root@server02 ~]# mysqld_safe --user=mysql --skip-slave-start &

(2) 重置从库
如果主库执行了reset master，从库如果开启了二进制，则从库需要执行reset master:
mysql> reset master

2.主库操作

(1) 主库备份
[root@server01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql

(2) 获取主库二进制位置
[root@server01 ~]# more /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=194;

3.从库操作

(1) 导入备份
[root@server02 ~]# scp 192.168.1.191:/tmp/full.sql /tmp
mysql> source /tmp/full.sql

(2) 配置主库信息
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.1.191',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=194,
  MASTER_CONNECT_RETRY=10;

mysql> start slave;

(3) 开启从库复制进程
mysql> start slave;