首页 > 代码库 > 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主从配置