配置环境:ubuntu12 + mysql5.5
主库IP:172.28.14.109
从库IP:172.28.14.85
**********************************************配置开始***********************************************
1,主库配置:
a,授权从库同步数据用户
mysql> GRANT REPLICATION SLAVE ON *.* to 'slavedb'@'172.28.14.85' identified by '123456';
mysql> FLUSH PRIVILEGES;
b,修改mysql配置文件:vi /etc/http://www.doczj.com/doc/cade4138bb68a98271fefa50.htmlf (修改之前先备份一份),需要修改以下几项
server-id = 1 #设置server-id为1,1表示为主服务器
log_bin = /var/log/mysql/mysql-bin.log (去掉默认配置文件的注释即可,该项为必须配置)
binlog-do-db= #需要进行同步的数据库,全部库都同步可不填
binlog-ignore-db= #不需要同步的数据库,全部库都同步可不填
(注意:bind-address 默认是127.0.0.1 需要注释掉该项,否则Slave将无法链接到 Master(否则不能远程访问))
c,查看主服务器状态,记录红色字体标示,配置从服务器备用(重启mysql服务后查看)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 107 | | mysql |
+------------------+----------+--------------+------------------+
如果显示下面内容表示,配置不正确
mysql> SHOW MASTER STATUS;
Empty set (0.02 sec)
*****************************************主库配置完毕*****************************************
2,从库配置:
a,修改mysql配置文件:vi /etc/http://www.doczj.com/doc/cade4138bb68a98271fefa50.htmlf (修改之前先备份一份),需要修改以下几项
server-di = 2 #设置server-id为2(与主库ID不同即可)
b,重启数据库后,登陆到mysql,停止slave同步
mysql> salve stop;
c,配置数据库同步命令, master_log_file, master_log_pos选项需要根据主数据填写
Mysql>Change master to
master_host='172.28.14.109',
master_user='slavedb',
master_password='123456',
master_log_file='mysql-bin.000006',
master_log_pos=107;
d,启动slave同步
mysql> salve start;
e,查看同步状态,Slave_IO_Running,Slave_SQL_Running同时为yes表示已开启同步
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.28.14.109
Master_User: slavedb
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 107