ubuntu mysql 5.7 replica同步

主备目的

高可用
可扩展
容灾
备份
数据仓库

基本流程（图）

主库把数据更改记录到二进制文件（binary log）
备库将主库的日志复制到自己的中继日志（relay log）
备库读取自己中继日志上的文件，重放到备份数据库上

图片描述

观点区分：

mysql集群和主备（也可以叫做从库）是两个不相同的概念

安装环境介绍

ubuntu 版本：Ubuntu 16.04.2 LTS
mysql 版本：mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
虚拟机：virtual box

主服务器ip：10.70.31.151（主库ip地址在从库连接主库同步的时候会用到）
从服务器ip：10.70.30.191
port ：都为3306

主库操作之修改配置文件

1.在主上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 111                |
| hive               |
| mysql              |
| performance_schema |
| sonar              |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

需要同步的就是111库，注意字母的库名如果需要在命令行新建或者删除需要加上``
如drop database 111``

mysql> use 111;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> show tables;
+---------------+
| Tables_in_111 |
+---------------+
| 360ky         |
| content       |
| product       |
+---------------+
3 rows in set (0.00 sec)

2.在主上修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

#server-id 服务器唯一标识，与其集群内不能重复
server-id = 1

#log_bin 启动MySQL二进制日志，表示自定义binglog文件名称
log_bin = master-bin


#管理所有bin log的索引文件
log_bin_index = master-bin.index

#binlog_do_db 指定记录二进制日志的数据库
binlog_do_db = 111


#binlog_ignore_db 指定不记录二进制日志的数据库

binlog_ignore_db = mysql

设置后如下：

 80 # The following can be used as easy to replay backup logs or for replication.
 81 # note: if you are setting up a replication slave, see README.Debian about
 82 #       other settings you may need to change.
 83 server-id               = 1
 84 log_bin                 = /var/log/mysql/mysql-bin.log
 85
 86 expire_logs_days        = 10
 87 max_binlog_size   = 100M
 88 binlog_do_db            = 111
 89 binlog_ignore_db        = mysql

另外建议将bind_address 设置成非127.0.0.1 ，具体设置根据当前环境设定。

记得重启：

root@ubuntu:/home/tb# service mysql restart

主库操作之验证状态

在主库上验证一下log bin状态：

mysql> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/log/mysql/mysql-bin       |
| log_bin_index                   | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
5 rows in set (0.00 sec)

在主库查看binlog日志文件列表（不同机器有差别）

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       154 |
+------------------+-----------+

查看目前位置偏移，从库同步更新时需要用到以下参数值：File 、Position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1044 | 111          | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看当前正在写入的binlog日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | 111          | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

查看data目录地址（也就是auto.conf的路径）

mysql> show global variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

主库操作之设置权限及导出

在主库设置从库复制权限：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'IDENTIFIED BY '123456';

刷新权限

FLUSH PRIVILEGES;

刷新读写，生产环境可能会造成阻塞

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

在主库用mysqldump导出数据（由于采用了FTWRL锁表，所以数据会相对完整及过程安全）

mysqldump -u root -p --opt 111 > 111.sql

完成后主库解锁

ublock tables

从库操作之导入数据

从库执行更新复制

create database `111`;

mysql -u root -p 111 < /path/to/111.sql

(模拟环境用的命令行工具szrz工具同步两台机器之间的.sql文件)

从库操作之修改配置文件

修改从库的mysql.cnf文件为

 83 server-id               = 2
 84 relay-log              =/var/log/mysql/mysql-relay-bin.log
 85 log-bin               =/var/log/mysql/mysql-bin.log
 86 binlog_do_db =111
 87 #log_bin                        = /var/log/mysql/mysql-bin.log
 88 expire_logs_days        = 10
 89 max_binlog_size   = 100M
 90 #binlog_do_db           = include_database_name
 91 #binlog_ignore_db       = include_database_name
 92#重放的事件也记录到自己的二进制文件中
 93log_salve_updates=1

然后重启从库的msyql

sudo service mysql restart

从库操作之配置启动同步

mysql> CHANGE MASTER TO  MASTER_HOST='10.70.31.151', MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1044;

从库操作之可能出现问题

ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

原因：如果采用虚拟机复制，会发生节点错误，建议在复制的虚机上删除后重新安装

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.7 mysql-client-core-5.7

OR(sudo apt-get remove --purge mysql*)
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

然后需要重新安装一下

sudo apt-get install mysql-server mysql-client

可以用mysqlbinglog命令行（非mysql cli 环境下）查看binlog日志文件内容

root@ubuntu:/home/tb# cd /var/log/mysql/
root@ubuntu:/var/log/mysql# ls
error.log  mysql-bin.000001  mysql-bin.index
root@ubuntu:/var/log/mysql# mysqlbinlog -v mysql-bin.000001  > mysql-bin.1.log
root@ubuntu:/var/log/mysql# cat mysql-bin.1.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180102 14:16:56 server id 1  end_log_pos 123 CRC32 0x8ed37480  Start: binlog v 4, server v 5.7.17-0ubuntu0.16.04.1-log created 180102 14:16:56 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
2CNLWg8BAAAAdwAAAHsAAAABAAQANS43LjE3LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADYI0taEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYB0044=
'/*!*/;
# at 123
#180102 14:16:56 server id 1  end_log_pos 154 CRC32 0x5dd6eb7e  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

开启同步：

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

查看从库状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.70.31.151
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1044
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Connecting
            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: 1044
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'repl@10.70.31.151:3306' - retry-time: 60  retries: 6
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 180102 16:41:36
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

发现密码不对，改一下重新开始

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO  MASTER_HOST='10.70.31.151', MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1044;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

从库运行关键指标：

如果再次查询状态仍然发现Slave_IO_Running 或者Slave_SQL_Running 不同时为YES,尝试执行

mysql> stop slave;
mysql> reset slave;
mysql> START SLAVE;

最后之成功状态：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.70.31.151
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 3023
               Relay_Log_File: mysql-relay-bin.000006
                Relay_Log_Pos: 3236
        Relay_Master_Log_File: mysql-bin.000004
             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: 3023
              Relay_Log_Space: 4546
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 16edfaa2-0970-11e7-994e-080027e11ac8
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

写在最后

上述流程就是自己模拟，线上不同的业务场景与地域具体配置，远远复杂于以上虚拟机下的demo。
且根据不同的引擎类型与数据库集群配置方式，都有很多具体的复制方式和参数配置
就我个人看，前期根据尽量考虑周全，以避免不必要的后期更新与容错。
更多建议参考官方文档，或者高性能mysql中复制章节。

官方文档参考
 非官方文档参考

ubuntu mysql 5.7 replica同步

主备目的

基本流程（图）

观点区分：

安装环境介绍

主库操作之修改配置文件

主库操作之验证状态

主库操作之设置权限及导出

从库操作之导入数据

从库操作之修改配置文件

从库操作之配置启动同步

从库操作之可能出现问题

查看从库状态

写在最后

牙小木木

引用和评论

云原生架构设计理论与实践（14）

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 回收表碎片实践教程

高效工程师的七个习惯

Mysql梳理1

mysql梳理2