mysql主从同步配置

机器

192.168.1.1         # 主 master
192.168.1.2         # 从 slave

master 配置

sudo vim /etc/mysql/my.cnf

修改:

bind-address = 192.169.1.1         # your master ip
server-id = 1                 # 在master-slave架构中，每台机器节点都需要有唯一的server-id
log_bin = /var/log/mysql/mysql-bin.log     # 开启binlog

重启

sudo service mysql restart
# or
sudo systemctl restart mysql

创建主从同步的账号 slave_user

# 创建slave_user用户，密码123456，并指定该用户只能在主机%(可改成ip192.168.1.2)上登录。
mysql> CREATE USER 'slave_user'@'%' IDENTIFIED BY '123456';

为slave1赋予REPLICATION SLAVE权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';

为MYSQL加读锁 (新库可忽略)

# 为了主库与从库的数据保持一致，我们先为mysql加入读锁，使其变为只读。
mysql> flush tables with read lock;

查看状态

mysql> show master status;

会出现 File / Position，配置从机器会用到

导出 master 机器数据

mysqldump -u root -p  --all-databases --master-data > dbdump.sql

解除master db锁

mysql> unlock tables;

将dbdump.sql copy 到 slave机器

scp dbdunp.sql ubuntu@192.168.1.2:/home/ubuntu/

slave 配置

sudo vim /etc/mysql/my.cnf

修改:

bind-address = 192.168.33.33         #your slave ip
server-id = 2         #master-slave结构中，唯一的server-id
log_bin = /var/log/mysql/mysql-bin.log         #开启binlog

重启

sudo service mysql restart
# or
sudo systemctl restart mysql

导入master db

mysql -u root -p  < /home/ubuntu/dbdump.sql

建立连接

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.1.1',
 -> MASTER_USER='slave_user',
 -> MASTER_PASSWORD='123456',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS=613;

#启动同步
mysql> START SLAVE;

MASTER_LOG_FILE | MASTER_LOG_POS 在master机器 show master status 可得到

查看状态

show slave status;