mysql主从配置

黄振国

#版本为mysql 5.5
#主数据库IP:192.168.0.11        从数据库IP:192.168.0.22

#主服务器的配置
vi /etc/my.cnf，增加以下参数
=======================================
log-bin=mysql-bin   
server-id = 1  
binlog-do-db=test
binlog-do-db=bbs
binlog-do-db=ucenter
binlog-ignore-db=mysql
=======================================

#进入mysql控制台
/usr/local/mysql/bin/mysql -u root -p

#从服务器的用户授权（数据库密码使用随机数）
GRANT REPLICATION SLAVE ON *.* TO m2s@"192.168.1.%" IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

#File列显示日志名，而Position显示偏移量，记录备用。
#File和Position为配置从库提供了MASTER_LOG_FILE和MASTER_LOG_POS的值
SHOW MASTER STATUS;

#从服务器的配置
#修改my.cnf配置，server-id根据实际环境配置
vi /etc/my.cnf
===============================================
server-id = 2
# binary logging is required for replication
log-bin=mysql-bin
replicate-do-db=test
replicate-do-db=bbs
replicate-do-db=ucenter
===============================================

#进入mysql控制台
/usr/local/mysql/bin/mysql -u root -p

#配置访问主数据，用户和密码等参数根据实际环境配置
CHANGE MASTER TO MASTER_HOST='192.168.0.11',MASTER_USER='m2s',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=107;

#看一下从服务器的同步情况
show slave status\G;
===============================================
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已经在同步
===============================================

#同步错误处理，在从服务器执行
stop slave;   
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;   
start slave;  

#清空从库的主从信息
reset slave;