1 环境介绍
系统:CentOS6.5
mysql:MySQL5.6.27
ip
主:192.168.1.110 master
从:192.168.1.11 slave
mysql5.6.27的安装见:
http://blog.csdn.net/erujo/article/details/48949103
2 把MySQL主服务器上的wlzh库备份到MySQL从服务器上
master
# mysqldump -u root -p wlzh > ~/wlzh.sql
# scp wlzh.sql root@192.168.1.11:/root/
slave
# mysql -u root -p discuz < wlzh.sql
3 配置主服务器上的my.cnf
# vim /etc/my.cnf
server_id=1 #必须
binlog-do-db=wlzh #允许主从复制的库
#binlog-ignore-db=test #不记录binlog
replicate-ignore-db=test #不复制test库的binlog
log-bin=wlzh-bin #必须
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3
# service mysqld restart
# mysql -uroot -p
mysql>grant replication slave on *.* to 'slave'@'192.168.1.11' identified by '123456';
mysql>flush tables with read lock;
mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| wlzh-bin.000001 | 1281 | wlzh | | |
+------------------+----------+--------------+------------------+-------------------+
4 修改slave配置文件并重启服务
首先测试从服务器能否访问主上的MySQL
# mysql -h192.168.1.110 -uslave -p123456
如果无法访问,请测试两主机是否联通,如果能ping通,请检查密码和自己的输入,再无法解决请关闭防火墙后尝试,仍旧无法请问度娘
# iptables -F //关闭防火墙
# vim /etc/my.cnf
server_id=2 #只要不与主的相同,且大于主即可
binlog-do-db = wlzh # 允许的库
binlog-ignore-db=test #不记录binlog
replicate-ignore-db=test #不复制test库的binlog
log-bin=wlzh-bin
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3
# service mysqld restart
# mysql -uroot -p
mysql>stop slave; //5.6之前为slave stop/start;
mysql>change master to master_host='192.168.1.110', master_port=3306, master_user='slave', master_password='123456', master_log_file='wlzh_log.000001', master_log_pos=1281;
#master_user是我们在master上创建的赋予replication slave权限的账户
#master_port为默认端口3306时可不用指定
#master_log_file和master_log_pos就是在master上show出来的master的状态信息
mysql>start slave;
5 查看是否成功
在从上执行
mysql> show slave status \G;
看能否看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6 常见错误处理
6.1 master and slave have equal MySQL server UUIDs
虚拟机是在mysql安装好以后克隆的,所以在mysql的数据目录下的auto.cnf文件中的uuid一样,所以导致错误
解决方法:删除slave数据目录的auto.cnf,重启mysql服务会自动生成新的auto.cnf,uuid也会变化。
6.2 ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
解决办法:打开my.cnf里的log-bin,并设置
6.3 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
解决办法:修改从上的server_id,使其大于主上的server_id,并重启MySQL
6.4 Slave_IO_Running: No
将其理解为逻辑错误,应该是由于master意外掉电,重新启动时自动flush了binlog,而slave并未获取到这个信息导致,因此解决该问题也比较简单,直接重置同步的master位置应该就可以
解决办法:
master:mysql> show master status;
slave:mysql>stop slave;
slave:mysql>change master to master_host='192.168.1.110', master_port=3306, master_user='slave', master_password='123456', master_log_file='wlzh_log.000001', master_log_pos=1281;
slave:mysql>start slave;