当前位置：首页 > 文章列表 > 数据库 > MySQL > ubuntu mysql 5.7 replica同步

ubuntu mysql 5.7 replica同步

来源：SegmentFault 2023-01-25 19:39:06 0浏览收藏

积累知识，胜过积蓄金银！毕竟在##column_title##开发的过程中，会遇到各种各样的问题，往往都是一些细节知识点还没有掌握好而导致的，因此基础知识点的积累是很重要的。下面本文《ubuntu mysql 5.7 replica同步》，就带大家讲解一下MySQL知识点，若是你对本文感兴趣，或者是想搞懂其中某个知识点，就请你继续往下看吧~

主备目的

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

基本流程（图）

主库把数据更改记录到二进制文件（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

(模拟环境用的命令行工具

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)