mysql 设置主从同步

环境配置

系统：CentOS release 6.8 (Final)

mysql版本：5.7

Master数据库设置

创建复制用户

此用户在Master和Slave上最好都有且一直，若是全备份Master中包含此用户， 并将备份导入slave库中时，可不用创建。（主备库中均创建主要是为了主从切换）

CREATE USER 'repl2'@'172.16.5.%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl2'@'172.16.5.%';

 

master需要开启的配置

# 服务器id，不可重复，从服务器读取master日志时，若server_id与自己的相同，会忽略此日志。
server-id=100
# binlog日志
log-bin=mysql-bin
# binlog 同步配置
innodb_flush_log_at_trx_commit=1
sync_binlog=1

配置后，重启启动服务器命令

shell> service mysqld restart

生成master备份快照文件

查看master 信息

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

 

使用mysqldump 的方式，在RR级别下，生成快照文件，保存数据一致。同时应该使用 --master-data，生成 change master to 语句，包含当前备份时的二进制文件和postion 信息

mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

1. --all-databases 备份所有数据
2. --master-data 生成 change master to 语句，包含当前备份生成时的二进制文件和postion 信息。--master-data=2 ，该语句被注释，不会有影响。
3. --single-transaction 保持数据一致。

查看备份文件的话，可以看到其中关于master 的信息

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=1046;

从服务器的配置

配置文件设置 设置从服务器的server ID，然后重启， 从库应该设为只读模式，同时二进制日志也不必开启

[mysqld]
server-id=2
# 有备份数据的化，此选项可以在启动时跳过复制，之后手动开启
skip-slave-start 
# 忽略的schema，未验证
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
# 从库设置为只读 0-读写，1-只读
read-only=1

导入master 的生成的备份

shell> mysql -uroot -p < backup_sunday_1_PM.sql

登陆从服务器，执行sync 命令

 若快照文件中已经包含了 change  master,且未被注释，此步骤不需要了，若被注释，可以从中获取 binlog 信息。

logfile 和logfilepos 从主服务器上的 show master status 获取

mysql> CHANGE MASTER TO MASTER_HOST='172.16.5.33', master_port= 3306, MASTER_USER='repl', MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=613;

查看备份状态

此时备份未启动。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.16.5.33
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: staticuat1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001  ## 从服务器也开启了binlog
             Slave_IO_Running: No  ## 从服务器io未开启
            Slave_SQL_Running: No  ## 从服务器sql线程未启动
            ··· ···
          Exec_Master_Log_Pos: 613 
              Relay_Log_Space: 154

   ··· 省略

开启从服务器同步，启动复制线程

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看从服务器同步状态

若出错，通过show slave status\G;可以看到出错信息。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.5.33
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: staticuat1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 613
              Relay_Log_Space: 532

 

可以通过在主库执行更新操作，来查看从库的备份状态来验证。