mysql主从复制
1. 准备master,slave2台服务器
2.修改主服务器my.cnf
log-bin=/opt/mysql/logs/mysql-bin
server-id = 146 //唯一 ,是ip地址后几位
3.修改从服务器my.cnf
log-bin=/opt/mysql/logs/mysql-bin
server-id = 117
replicate-do-db= test //只复制test数据库
4.重启mysql
/opt/mysql/bin/mysql restart
5.在主服务器上建立帐户并授权slave:
grant replication slave on *.* 'test'@'%' identified'123456'
//%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.145.226,加强安全
6.登录主服务器 ,查询master状态
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000020 | 400 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
7.配置从服务器Slave
change master to master_host='123.123.123.123',master_user='test',master_password='123456',master_log_file='mysql-bin.000020',master_log_pos=400;
mysql> start slave 重启服务器复制功能
8.查看从服务器的slave状态
show slave status;
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。