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

 

解决。。。