mysqldump 逻辑备份搭建备库

MYSQL第一节：Mysql5.7 通用二进制安装方法

第二节：MySQL主从/主主复制环境搭建

在前两节的基础环境上，使用mysqldump命令再搭建一个备库，

环境：

主库：192.168.150.101

备库1:192.168.150.102

备库2（当前服务器）：192.168.150.103

备库2已经安装好MySQL数据库，并修改my.cnf配置文件中server_id=3

1、

到主库使用mysqldump命令备份数据库。

[ ~]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --single-transaction --all-databases --master-data=2 -E -R >all.sql

加入--single-transaction参数，在备份过程中不会锁表，开启通用日志，看下不锁表备份原理.

在正式备份前会有一次flush table with read lock锁库的操作，随便设置隔离级别为可重复读，拿到当前binlog位点后，再解锁，之后开始正式备份数据。

从通用日志中看到，备份会先获取表结构，再依次备份各个表中的数据。

如果在备份过程中主库执行DDL可能会导致备份失败，

场景一：DDL操作发生在表已经备份完成了，没有问题。

场景二：DDL操作发生在表正在备份过程中，会遇到MDL锁。会话状态是【Waiting for table metadata lock】

场景三：DDL操作发生在表还没有备份，当备份到这个表的时候，会报错【ERROR 1412 (HY000): Table definition has changed, please retry transaction】表定义已经被修改，请重新开启事务。

场景三发生时备份会失败。

[ data]# tail -f host101.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.29-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2020-06-07T12:18:48.555923Z        11 Connect   127.0.0.1 on  using SSL/TLS
2020-06-07T12:18:48.556383Z        11 Query     /*!40100 SET @@SQL_MODE=‘‘ */
2020-06-07T12:18:48.556956Z        11 Query     /*!40103 SET TIME_ZONE=‘+00:00‘ */
2020-06-07T12:18:48.557430Z        11 Query     FLUSH /*!40101 LOCAL */ TABLES #关闭所有表
2020-06-07T12:18:48.560743Z        11 Query     FLUSH TABLES WITH READ LOCK    #设置数据库为只读模式
2020-06-07T12:18:48.561254Z        11 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ   #设置当前会话隔离级别为可重复读
2020-06-07T12:18:48.561635Z        11 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */    #开启事务
2020-06-07T12:18:48.562073Z        11 Query     SHOW VARIABLES LIKE ‘gtid\_mode‘　　　　　　　　　　　　　　　　#获取GTID模式
2020-06-07T12:18:48.566816Z        11 Query     SELECT @@GLOBAL.GTID_EXECUTED　　　　　　　　　　　　　　　　　　#获取GTID位点
2020-06-07T12:18:48.567266Z        11 Query     SHOW MASTER STATUS　　　　　　　　　　　　　　　　　　　　　　　　　#获取当前binlog位点
2020-06-07T12:18:48.567644Z        11 Query     UNLOCK TABLES　　　　　　　　　　　　　　　　　　　　　　　　　　　　#解锁
2020-06-07T12:18:48.568043Z        11 Query     SELECT .....2020-06-07T12:18:48.569246Z        11 Query     SELECT .....2020-06-07T12:18:48.570099Z        11 Query     SHOW DATABASES
2020-06-07T12:47:35.126606Z 12 Query SHOW VARIABLES LIKE ‘ndbinfo\_version‘2020-06-07T12:47:35.128796Z        12 Init DB   ceshi2020-06-07T12:47:35.128931Z        12 Query     SHOW CREATE DATABASE IF NOT EXISTS `ceshi`2020-06-07T12:47:35.129049Z        12 Query     SAVEPOINT sp2020-06-07T12:47:35.129201Z        12 Query     show tables2020-06-07T12:47:35.129503Z        12 Query     show table status like ‘t1‘2020-06-07T12:47:35.130266Z        12 Query     SET SQL_QUOTE_SHOW_CREATE=12020-06-07T12:47:35.130386Z        12 Query     SET SESSION character_set_results = ‘binary‘2020-06-07T12:47:35.130492Z        12 Query     show create table `t1`　　　　　　　　　　　　#拿到t1表结构2020-06-07T12:47:35.130677Z        12 Query     SET SESSION character_set_results = ‘utf8‘2020-06-07T12:47:35.130826Z        12 Query     show fields from `t1`2020-06-07T12:47:35.131325Z        12 Query     show fields from `t1`2020-06-07T12:47:35.131692Z        12 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`　　#开始备份t1数据2020-06-07T12:47:35.131957Z        12 Query     SET SESSION character_set_results = ‘binary‘2020-06-07T12:47:35.132061Z        12 Query     use `ceshi`2020-06-07T12:47:35.132166Z        12 Query     select @@collation_database2020-06-07T12:47:35.132283Z        12 Query     SHOW TRIGGERS LIKE ‘t1‘2020-06-07T12:47:35.132608Z        12 Query     SET SESSION character_set_results = ‘utf8‘2020-06-07T12:47:35.132779Z        12 Query     ROLLBACK TO SAVEPOINT sp2020-06-07T12:47:35.132890Z        12 Query     show table status like ‘t2‘2020-06-07T12:47:35.133291Z        12 Query     SET SQL_QUOTE_SHOW_CREATE=12020-06-07T12:47:35.133392Z        12 Query     SET SESSION character_set_results = ‘binary‘2020-06-07T12:47:35.133493Z        12 Query     show create table `t2`　　　　　　　　　　　　#拿到t2表结构2020-06-07T12:47:35.133666Z        12 Query     SET SESSION character_set_results = ‘utf8‘2020-06-07T12:47:35.133865Z        12 Query     show fields from `t2`2020-06-07T12:47:35.134295Z        12 Query     show fields from `t2`2020-06-07T12:47:35.134682Z        12 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`　　#开始备份t2数据2020-06-07T12:47:35.134871Z        12 Query     SET SESSION character_set_results = ‘binary‘2020-06-07T12:47:35.134975Z        12 Query     use `ceshi`2020-06-07T12:47:35.135079Z        12 Query     select @@collation_database2020-06-07T12:47:35.135196Z        12 Query     SHOW TRIGGERS LIKE ‘t2‘

 
2、
查看备份文件all.sql

[ ~]# head -35 all.sql  
-- MySQL dump 10.13  Distrib 5.7.29, for linux-glibc2.12 (x86_64)
--
-- Host: 127.0.0.1    Database: 
-- ------------------------------------------------------
-- Server version       5.7.29-log

/*!40101 SET @@CHARACTER_SET_CLIENT */;
/*!40101 SET @@CHARACTER_SET_RESULTS */;
/*!40101 SET @@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @@TIME_ZONE */;
/*!40103 SET TIME_ZONE=‘+00:00‘ */;
/*!40014 SET @@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */;
/*!40111 SET @@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED=‘9fef2262-97b1-11ea-92b5-000c29cd3ff3:1-6,
adc4403d-97b2-11ea-b803-000c298076e0:1-65‘; 　　　　#由于我之前的测试环境是运行在GTID复制模式下，所以备份会有这项信息，跳过这些GITD位点。

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000023‘, MASTER_LOG_POS=234;   #普通的复制的binlog位点信息。

--
-- Current Database: `ceshi`
--

3、

将all.sql文件发送到备库

[ ~]#  scp all.sql 192.168.150.103:~
192.168.150.103‘s password: 
all.sql                                                                                                                                                                                                                       100%  845KB  19.0MB/s   00:00    
[ ~]#

4、

登录103服务器，导入数据

[ ~]# mysql -uroot -p123456 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[ ~]# 
[ ~]# mysql -uroot -p123456Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases; 　　　　#ceshi chai test这些数据库已经导入进来了。+--------------------+| Database           |+--------------------+| information_schema || ceshi              || chai               || mysql              || performance_schema || sys                || test               |+--------------------+7 rows in set (0.01 sec)

 
 
配置主从同步

mysql>  change master to master_host=‘192.168.150.101‘,master_port=3306,master_user=‘replicator‘,master_password=‘123456‘,master_auto_position=1; #使用gtid复制模式去连接主库。
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status\G　　　　#IO、和SQL线程状态都是YES了
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.101
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000023
          Read_Master_Log_Pos: 1083
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000023
             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: 234
              Relay_Log_Space: 1427
              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: 2021
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: 9fef2262-97b1-11ea-92b5-000c29cd3ff3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 9fef2262-97b1-11ea-92b5-000c29cd3ff3:7-10
            Executed_Gtid_Set: 9fef2262-97b1-11ea-92b5-000c29cd3ff3:1-6,
adc4403d-97b2-11ea-b803-000c298076e0:1-65
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

在主库新建ceshi3

mysql> create database ceshi3;
Query OK, 1 row affected (0.00 sec)

mysql>

在103服务器备库查看，ceshi3 库已经同步过来了。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| ceshi3             |
| chai               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
8 rows in set (0.04 sec)

mysql>

mysqldump 逻辑备份搭建备库

相关推荐