资讯专栏INFORMATION COLUMN

ubuntu mysql 5.7 replica同步

William_Sang / 3009人阅读

摘要:且根据不同的引擎类型与数据库集群配置方式,都有很多具体的复制方式和参数配置就我个人看,前期根据尽量考虑周全,以避免不必要的后期更新与容错。更多建议参考官方文档,或者高性能中复制章节。官方文档参考非官方文档参考

主备目的

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

基本流程(图)

主库把数据更改记录到二进制文件(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 STATUSG
*************************** 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 STATUSG
*************************** 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中复制章节。

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

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/47475.html

相关文章

  • Orchestrator管理mysql复制

    摘要:会保证安全并且禁止非法复制拓扑。事实上,拒绝任何非法操作,以免破坏系统。可以人为进行恢复。一旦集群经历了恢复,将在接下来的秒小时内阻止自动恢复。优势完成故障转移。 .................................................................................系统:centos7Mysql:5.7.22IP:192.16...

    3fuyu 评论0 收藏0
  • 使用 Windows Linux Subsystem 配置开发环境

    摘要:微软前些日子公布了下简称,当时还只有支持安装,作为普通用户的我就没有尝试。解决方案有待进一步发掘。按照刚刚发布的报告,微软已经是开源贡献第一大公司,拥抱之后我们也可以在下进行开发了。 微软前些日子公布了 Windows Subsystem Linux(下简称WSL),当时还只有 Insider Preview 支持安装,作为普通用户的我就没有尝试。这两天不知道伴随哪次更新,标准的专业版...

    kk_miles 评论0 收藏0
  • 使用 Windows Linux Subsystem 配置开发环境

    摘要:微软前些日子公布了下简称,当时还只有支持安装,作为普通用户的我就没有尝试。解决方案有待进一步发掘。按照刚刚发布的报告,微软已经是开源贡献第一大公司,拥抱之后我们也可以在下进行开发了。 微软前些日子公布了 Windows Subsystem Linux(下简称WSL),当时还只有 Insider Preview 支持安装,作为普通用户的我就没有尝试。这两天不知道伴随哪次更新,标准的专业版...

    Jenny_Tong 评论0 收藏0
  • 使用 Windows Linux Subsystem 配置开发环境

    摘要:微软前些日子公布了下简称,当时还只有支持安装,作为普通用户的我就没有尝试。解决方案有待进一步发掘。按照刚刚发布的报告,微软已经是开源贡献第一大公司,拥抱之后我们也可以在下进行开发了。 微软前些日子公布了 Windows Subsystem Linux(下简称WSL),当时还只有 Insider Preview 支持安装,作为普通用户的我就没有尝试。这两天不知道伴随哪次更新,标准的专业版...

    TigerChain 评论0 收藏0
  • Mysql主从同步实战

    摘要:之前写过一篇文章主从同步的原理。相信看过这篇文章的童鞋,都摩拳擦掌,跃跃一试了吧今天我们就来一次主从同步实战环境说明下面的实战演练,都是基于上面的环境。创建主从同步的。 1、Introduction 之前写过一篇文章:Mysql主从同步的原理。相信看过这篇文章的童鞋,都摩拳擦掌,跃跃一试了吧?今天我们就来一次mysql主从同步实战! 2、环境说明 os:ubuntu16.04 mysq...

    RdouTyping 评论0 收藏0

发表评论

0条评论

William_Sang

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<