mysql主从同步

在主服务器上

1.配置my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1
#日志超过3天自动过期
expire_logs_days = 30
#同步student库
binlog-do-db=student
binlog-do-db=student1
#不同步系统库
binlog-ignore-db=mysql
#bin-log日志文件格式，设置为MIXED可以防止主键重复
binlog_format=mixed   
#同步磁盘，这个开启性能比不开启低5倍
sync_binlog=1

2. 创建复制用户并授权给从服务器：

CREATE USER 'replica'@'%' IDENTIFIED BY 'replica_password';
set global validate_password_policy=0; 
set global validate_password_length=1;
GRANT ALL PRIVILEGES ON *.* TO 'replica'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH  PRIVILEGES;

3.查看二进制日志状态，记录下File和Position的值：

SHOW MASTER STATUS;

在从服务器上

1.配置my.cnf

[mysqld]
server-id=2

log-bin=mysql-bin#启动MySQ二进制日志系统
replicate-do-db=student  
replicate-do-db=student1 
#需要同步的数据库名。
replicate-ignore-db=mysql
#不同步mysql数据库  #此项主从库只需要配置一个
slave-skip-errors = all 
#跳过所有的错误，继续执行复制操作

2.登录mysql执行命令

mysql>stop slave
mysql>CHANGE MASTER TO MASTER_HOST='主服务器IP',MASTER_USER='replica',MASTER_PASSWORD='replica_password',MASTER_LOG_FILE='记录的File',MASTER_LOG_POS=记录的Position; 
mysql>START SLAVE
mysql>SHOW SLAVE STATUS

mysql>stop slave
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.99', MASTER_PORT=3306,MASTER_USER='replica',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=3522; 
mysql>START SLAVE
mysql>SHOW SLAVE STATUS