首页 > 代码库 > Mysql主从配置

Mysql主从配置

 参考:http://www.cnblogs.com/kylinlin/p/5258719.html

1. 环境

OS: Ubuntu 15.10

IP: 192.168.68.23(从)/192.168.68.24(主)

Mysql: 5.6.31-0ubuntu0.15.10.1-log

2. 主服务器配置

  • 修改mysql配置

`sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf`

# enable remote connection
bind-address = 0.0.0.0

...

# 2017-5-19 16:35
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
  • 重启mysql

`sudo service mysql restart`

  • 授权主从复制的用户

`grant replication slave on *.* to ‘replication‘@‘192.168.68.%‘ identified by ‘replication‘;`

技术分享

  • `flush tables with read lock;`

技术分享

  • `show master status;`

技术分享

  • 备份整库并传到从服务器

`mysqldump -uroot -p --all-databases > /tmp/mysql_bak.sql`

`scp /tmp/mysql_bak.sql test@192.168.68.23:/tmp/mysql_bak.sql`

  • `unlock tables;`

技术分享

3. 配置从服务器

  • 修改mysql配置

`sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf`

# enable remote connection
bind-address = 0.0.0.0

...

# 2017-5-19 16:35 该值需要与主服务器不一样
server-id = 2
  • 重启mysql

`sudo service mysql restart`

  • 初始化整库

`mysql -uroot -p < /tmp/mysql_bak.sql`

  • 添加认从配置

mysql> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.68.24‘,
-> MASTER_PORT=3306,
-> MASTER_USER=‘replication‘,
-> MASTER_PASSWORD=‘replication‘,
-> MASTER_LOG_FILE=‘mysql-bin.000001‘,
-> MASTER_LOG_POS=120;

技术分享

  • 启动主从

`start slave;`

技术分享

  • 查看主从状态

` show slave status \G`

Slave_IO_Running/Slave_SQL_Running为Yes则表示成功

技术分享

4. 遇到问题

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work?

技术分享

解决方案:http://www.chriscalender.com/resolving-error-master-and-slave-have-equal-mysql-server-uuids/

a) `mv /var/lib/mysql/auto.cnf /tmp/auto.cnf`

b) `sudo service mysql restart`

Mysql主从配置