一,主服务mater配置
1.主服务需要为从服务器创建一个账号
1)进入mysql
执行 create user 'slave'@'ip' identified by '123456';
ps:create user 'slave'@'122.11.112.3' identified by '123456';
用于创建用户slave,密码是123456
2)给slave账号权限
执行 grant replication slave on *.* to '用户名'@'主机' identified by '密码';
ps: grant replication slave on *.* to 'slave'@'122.11.112.3' identified by '123456';
flush privileges;//生效
2.配置/etc/my.cnf
退出mysql,执行 vi etc/my.cnf 加入下面配置
server-id=2 //server-id 不可重复
log-bin=mysql-bin //日志
binlog-do-db=vrs //指定mysql的binlog日志记录哪个数据库实例(VRS)
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
重启mysql,service mysqld restart
3)验证
进入mysql执行 show master status;
ps:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | slave | | |
+------------------+----------+--------------+------------------+-------------------+
slave 为要备份的库;
二、从服务器Slave配置
- /etc/my.cnf 配置 执行 vi /etc/my.cnf 加入以下配置
server-id=3 //server-id 不可重复
log-bin=mysql-bin //日志
binlog-do-db=slave //指定mysql的binlog日志记录哪个db
replicate-do-db=slave //参数是在slave上配置,指定slave要复制哪个库
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-ignore-db=mysql
配置完成重启mysql
2.从库开启配置
进入从mysql
mysql -uroot -p123456
stop slave;//停止
CHANGE MASTER TO
MASTER_HOST='122.11.112.3',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001', // 同master status的文件
MASTER_LOG_POS=120; // 同master status的文件位置pos
start slave;//启动
3.验证
在mysql中执行 show slave status\G;
你会得到一个配置列表,
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果这两个选项不全是Yes,那就说明你前面某个步骤配置错了。
如果正确你就可以在主服务中创建表或增加字段,数据,来验证了。