一.master配置
1.修改mysql配置文件
sudo vi /etc/mysql/my.cnf
在[mysqld]模块中添加以下配置(默认是被注释掉的)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = yy_test
binlog_ignore_db = mysql
2.重启mysql
3.登录Mysql,执行 如下sql
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 445 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
记录 File 和 Position的值;
二.配置slave
1.修改mysql配置文件
sudo vi /etc/mysql/my.cnf
在[mysqld]模块中添加以下配置(默认是被注释掉的)
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
replicate_do_db = yy_test
replicate_ignore_db = mysql
2.重启mysql
3.slave进入mysql,执行以下sql
stop salve;
4.设置从master复制日志配置:
change master to master_host='192.168.1.108',master_user='root',master_password='123',master_log_file='mysql-bin.000002' ,master_log_pos=445; (mast er_log_file和master_log_pos 是以上查询的值)
5.在salve上执行以下sql
start slave;
SHOW SLAVE STATUS;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.108 //主服务器地址
Master_User: root //授权帐户名,尽量避免使用root
Master_Port: 3306 //数据库端口,部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 600 //#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
......
出现以上信息表示配置成功