1.主从配置
1.主库开启binlog
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
//查看
show variables like '%log_bin%';
show master status;
reset master;
2.从库开启relay-log
[mysqld]
server-id=2
relay-log=mysql-relay-bin
#replicate-do-db = test #在master端不指定binlog-do-db,在slave端用replication-do-db来过滤
#replicate-ignore-db = mysql #忽略的库
//查看
show variables like '%relay_log%';
3.主机授权
grant replication slave on *.* to slave@192.168.0.113 identified by '123456';
4.配置从库
change master to
master_host='192.168.0.106',
master_port=3306,
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;
5.开启监听
start slave;
6.查看状态,这2个要都为yes
show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
//停止 slave
stop slave;
replicate-do-db // 指定数据库
replicate-do-table // 指定表
replicate-ignore-db //忽略数据库
replicate-ignore-table // 忽略表
show slave status // 查看从服务器状态
注意:
从服务器与住服务器的连通性。
1.主从服务器同步维护
1.阻塞主服务器更新
flush tables with read lock;
show master status; // 查看当前主服务器位置状态
2.设置从服务器等待更新的点
select master_pos_wait('mysql-bin.000014',154)
3.同步完成后,允许主服务器更新
unlock tables;
2.切换主从服务器
1.先确保所有的从数据库都已经执行了 relay_log 中的全部更新, 用 show processlist 查看 Slave has read all relay log;
2.在从服务器s1上,执行 stop slave 操作, 然后 reset master 重置成主数据库
3.在 s2 上 执行 stop slave 停止服务,然后 change master to MASTER_HOST='s1' 执行 s1. start slave;
4.通知所有客户端指向 s1
5.删除新主服务器上的 master.info 和 relay-log.info 文件
2.双主热备:
双主热备(互为主从,然后再各自挂上一个从):
在原先的主从上做:
1.原先的 slave 机器开启 binlog:
server-id=2
relay-log=mysql-relay-bin
log-bin=mysql-bin //开启binlog
binlog_format=mixed
log_slave_updates=1 //将relay-log写入binlog中,这样即使在主库中写binlog,从库的binlog也一样。将来主挂掉了,从可以作为主
2.重启从库mysql
3.在从库给主机授权
grant replication slave on *.* to master@192.168.0.108 identified by '123456';
4.让主机监听从机
[mysqld]
log-bin=mysql-bin
server-id=11
relay_log=mysql-relay-bin
log_slave_updates=1 // 让更新也更新到第二个主库的bin-log中
//重启mysql master
//重置slave
reset slave;
stop slave;
change master to
master_host='192.168.0.113',
master_port=3306,
master_user='master',
master_password='123456',
master_log_file='mysql-bin.000003',
master_log_pos=765;
//开启slave
start slave;
注意,如果报不同步的错误的话:
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000001' at 254, the last event read from 'mysql-bin.000001' at 4, the last byte read from 'mysql-bin.000001' at 4.'
先stop slave, 然后将 master_log_pos 改为 4(错误信息提示的位置);