mysql 主从
建同步用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
*************主
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=facebk #数据库
systemctl restart mysql
show master status;
*************从
[mysqld]
server-id=2
replicate-do-db = facebk
replicate-ignore-db = mysql #忽略的库
systemctl restart mysql
mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.233',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl123',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=313;
mysql> start slave;
show slave status \G;
而最新版5.7版本中,如下三步就可以不用重启服务器就生效了:
STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(demo);
START SLAVE SQL_THREAD;
路径
show variables like 'log_%';
mysql从库Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'报错处理
- 从:
show slave status
Slave_IO_Running: 状态变成NO了
stop slave;
- 主:
flush logs;
刷新后的日志会+1
例如上面的 File: mysql-bin.000113 会变成 File: mysql-bin.000114
show master status
- 从:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000114',MASTER_LOG_POS=120;
start slave
show slave status
解决。。。