mysql数据库设置主从同步

mysql数据库设置主从同步

环境

mysql主库版本MySQL-5.6.40-2.sles12.x86_64
mysql从库版本mysql-5.7.21-linux-glibc2.12-x86_64

一、主库配置

修改主库my.cnf配置

[mysqld]
#server_id = 1                #唯一标识，主库从库不能重复
#log_bin = mysql-bin          #开启日志
#binlog_format=MIXED      　　 #日志记录的格式
#max_binlog_size = 512M  　　　#单个日志文件最大
#expire_logs_day = 3          #日志有效期（天）
#binlog_do_db = test1,test2    #日志记录那些数据库
#binlog_ignore_db = mysql,performance_schema,information_schema #日志记录忽略那些数据库的

#id要唯一 
server-id = 1
#开启binlog日志 
log-bin = mysql-bin
#在Ubuntu系统中MySQL5.5以后已经默认是1 
auto-increment-increment = 1
auto-increment-offset = 1
#跳过主从复制出现的错误
slave-skip-errors = all
#日志记录的格式
binlog_format = MIXED
#单个日志文件最大
max_binlog_size = 512M
#日志有效期（天）
expire_logs_day = 7

主库创建同步用户

grant all on *.* to 'sync'@'192.168.175.%' identified by 'sync';

重启服务

service mysql restart

二、从库配置

修改从库my.cnf配置

[mysqld]
#这个设置2 
server-id = 2
#开启binlog日志 
log-bin = mysql-bin
#这两个参数在Ubuntu系统中MySQL5.5以后都已经默认是1 
auto-increment-increment = 1
auto-increment-offset = 1
#跳过主从复制出现的错误
slave-skip-errors = all

重启服务

service mysql restart

三、存量数据迁移

备份主库

#导出明细
mysqldump -uroot -p111 --routines --single_transaction --master-data=2 --databases MyTest > MyTest.sql

#传文件
scp mysql.sql root@192.168.175.133:/home/mysql.sql

参数说明：
–routines：导出存储过程和函数
–single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。
–master-data：默认等于1，将dump起始(change master to)binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。

将备份的数据拷贝到从库之后导入数据

mysql -uroot -p123 -e 'create database mysql;' 
mysql -uroot -p123 mysql < mysql.sql

在备份sql中看binlog和pos值

head -25 mysql.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;   #大概22行

从库设置从这个日志点同步，并启动

mysql> change master to master_host='192.168.175.132', 
  -> master_user='sync', 
  -> master_password='sync', 
  -> master_log_file='mysql-bin.000002', 
  -> master_log_pos=120; 
mysql> start slave; 
mysql> show slave status\G;
ERROR 2006 (HY000): MySQL server has gone away 
No connection. Trying to reconnect... 
Connection id:  90 
Current database: *** NONE *** 
*************************** 1. row *************************** 
        Slave_IO_State: Waiting for master to send event 
         Master_Host: 192.168.175.132
         Master_User: sync 
         Master_Port: 3306 
        Connect_Retry: 60 
       Master_Log_File: mysql-bin.000002 
     Read_Master_Log_Pos: 358 
        Relay_Log_File: mysqld-relay-bin.000003 
        Relay_Log_Pos: 504 
    Relay_Master_Log_File: mysql-bin.000001 
       Slave_IO_Running: Yes 
      Slave_SQL_Running: Yes 

看到IO和SQL线程均为YES，说明主从配置成功。

如有my.cnf权限问题可进行授权进行修复

chmod 644 /etc/my.cnf