MySQL備份和恢復方法彙總

chenoracle發表於2021-09-21

一:CTAS
二:create table table_name_bak like table_name;
三:SELECT * FROM t1 INTO OUTFILE/load data infile/mysqlimport
四: mysqldump
五: mysqlbinlog恢復
六:mysqldumper/myloader
七:xtrabackup/innobackupex
八: MEB(MySQL Enterprise Backup)
九:冷備
十:閃回
十一: 快照備份
十二:mysqlhotcopy

Mysql備份型別

按照備份時對資料庫的影響分為
Hot backup(熱備):也叫線上備份。指在資料庫執行中直接備份,對正在執行的資料庫沒有任何影響。
Cold backup(冷備):也叫離線備份。指在資料庫停止的情況下備份。
Warm backup(溫備):在資料庫執行時備份,會加一個全域性鎖以保證資料的一致性,會對當前資料庫的操作有影響

建立測試資料:

MySQL [cjcdb]> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [cjcdb]> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

MySQL [cjcdb]> create index i_t1_id on t1(id);
MySQL [cjcdb]> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | i_t1_id  |            1 | id          | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

方式一:CTAS

表級備份,備份到資料庫。

MySQL [cjcdb]> create table t1_bak as select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
MySQL [cjcdb]> create table t1_bak as select * from t1 where 1=2;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

由於啟動了GTID,不允許CTAS操作。

因為create table ...select語句會生成兩個sql,一個是DDL建立表SQL,一個是insert into插入資料的sql。

由於DDL會導致自動提交,所以這個sql至少需要兩個GTID,但是GTID模式下,只能給這個sql生成一個GTID,如果強制執行會導致和上面更新非事務引擎一樣的結果。


但是不能因為執行不了CTAS而關閉GTID,可以將CTAS操作拆成如下兩部分。


###craete table xx as ...用來建立相同表結構並複製源表資料,不包含索引等。

###create table xx like ...用來建立完整表結構和全部索引。


方式二:create table table_name_bak like table_name;

MySQL [cjcdb]> create table t1_bak like t1;
Query OK, 0 rows affected (0.04 sec)
MySQL [cjcdb]> select * from t1_bak;
Empty set (0.00 sec)
MySQL [cjcdb]> show create table t1_bak;
+--------+--------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                       |
+--------+--------------------------------------------------------------------------------------------------------------------+
| t1_bak | CREATE TABLE `t1_bak` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [cjcdb]> show index from t1_bak;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1_bak |          1 | i_t1_id  |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
MySQL [cjcdb]> insert into t1_bak select * from t1;
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0
MySQL [cjcdb]> select * from t1_bak;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

還原備份的表

MySQL [cjcdb]> rename table t1 to t1_old;
Query OK, 0 rows affected (0.04 sec)
MySQL [cjcdb]> rename table t1_bak to t1;
Query OK, 0 rows affected (0.05 sec)
MySQL [cjcdb]> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [cjcdb]> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | i_t1_id  |            1 | id          | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
MySQL [cjcdb]> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

方式三:select into outfile

Mysql select into outfile命令

在Mysql中,與load data infile命令作用相反的一個命令是select into outfile命令

MySQL [cjcdb]> SELECT * FROM t1 INTO OUTFILE "/home/mysql/t1_001.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
MySQL [cjcdb]> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

檢視 secure_file_priv 的值,預設為NULL,表示限制不能匯入匯出。


檢視官方文件,secure_file_priv引數用於限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()傳到哪個指定目錄。

secure_file_priv 為 NULL 時,表示限制mysqld不允許匯入或匯出。

secure_file_priv 為 /tmp 時,表示限制mysqld只能在/tmp目錄中執行匯入匯出,其他目錄不能執行。

secure_file_priv 沒有值時,表示不限制mysqld在任意目錄的匯入匯出。


又因為secure_file_priv 引數是隻讀引數,不能使用set global命令修改。

MySQL [cjcdb]> set global secure_file_priv='/home/mysql';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
[root@mysql01 ~]# vim /etc/my.cnf
secure_file_priv='/home/mysql'
[mysql@mysql01 ~]$ service mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS!
MySQL [(none)]> show variables like '%secure%';
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| require_secure_transport | OFF          |
| secure_auth              | ON           |
| secure_file_priv         | /home/mysql/ |
+--------------------------+--------------+
3 rows in set (0.00 sec)
MySQL [(none)]> use cjcdb;
MySQL [cjcdb]> SELECT * FROM t1 INTO OUTFILE "/home/mysql/t1_001.txt";
Query OK, 9 rows affected (0.00 sec)

也可以指定分隔符和包圍符。

MySQL [cjcdb]> SELECT * FROM t1 INTO OUTFILE "/home/mysql/t1_002.txt" FIELDS TERMINATED BY "," ENCLOSED BY '"';
Query OK, 9 rows affected (0.00 sec)
MySQL [cjcdb]> system cat /home/mysql/t1_001.txt
1
2
3
4
5
6
7
8
9
MySQL [cjcdb]> system cat /home/mysql/t1_002.txt
"1"
"2"
"3"
"4"
"5"
"6"
"7"
"8"
"9"

恢復:

MySQL [cjcdb]> help load data;
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]
......
MySQL [cjcdb]> delete from t1;
Query OK, 9 rows affected (0.03 sec)
MySQL [cjcdb]> select * from t1;
Empty set (0.00 sec)
MySQL [cjcdb]> load data infile "/home/mysql/t1_001.txt" into table t1;
Query OK, 9 rows affected (0.03 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
MySQL [cjcdb]> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

mysqlimport客戶端提供了 LOAD DATA INFILEQL 語句的一個命令列介面。

mysqlimport 的大多數選項直接對應 LOAD DATA INFILE 子句。

[mysql@mysql01 ~]$ mysql -uroot -p cjcdb -e "select count(*) from t1"
Enter password: 
+----------+
| count(*) |
+----------+
|        9 |
+----------+
mysql -uroot -p cjcdb -e "delete from t1"
[mysql@mysql01 ~]$ mysqlimport -u root -p --local cjcdb "/home/mysql/t1_001.txt"
Enter password: 
mysqlimport: Error: 1146, Table 'cjcdb.t1_001' doesn't exist, when using table: t1_001
[mysql@mysql01 ~]$ mv t1_001.txt t1.txt
[mysql@mysql01 ~]$ mysqlimport -u root -p --local cjcdb "/home/mysql/t1.txt"
Enter password: 
cjcdb.t1: Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
[mysql@mysql01 ~]$ mysql -uroot -p cjcdb -e "select count(*) from t1"
Enter password: 
+----------+
| count(*) |
+----------+
|        9 |
+----------+

mysqlimport 命令可以指定選項來設定指定格式,命令語句格式如下:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

mysqlimport 語句中使用 --columns 選項來設定列的順序:

$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****
mysqlimport -uroot -p cjcdb /home/mysql/t1_001.txt 
[mysql@mysql01 ~]$ man mysqlimport
shell> mysqlimport --local test imptest.txt
           test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
           shell> mysql -e ´SELECT * FROM imptest´ test
           +------+---------------+
           | id   | n             |
           +------+---------------+
           |  100 | Max Sydow     |
           |  101 | Count Dracula |
           +------+---------------+

方式四:Mysqldump

mysqldump是邏輯備份工具,支援MyISAM和InnoDB引擎。資料庫執行時,MyISAM引擎只支援溫備,InnoDB支援熱備和溫備。


7.4.1 Dumping Data in SQL Format with mysqldump

https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html


預設情況下,mysqldump將資訊作為SQL語句寫入標準輸出。可以將輸出儲存在檔案中:


備份所有資料庫

要轉儲所有資料庫,請使用--all databases選項呼叫mysqldump。

[mysql@mysql01 backup]$ mysqldump -uroot -p --all-databases > dump.sql
[mysql@mysql01 backup]$ ll -rth
total 1.5M
-rw-rw-r-- 1 mysql mysql 1.5M Sep 19 13:27 dump.sql
[mysql@mysql01 backup]$ vim dump.sql
--
-- Current Database: `cjcdb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjcdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `cjcdb`;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `t1_old`
--
......
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `columns_priv`
--
......

備份指定資料庫:

要僅轉儲特定資料庫,請在命令列中命名它們並使用--databases選項:

mysqldump -uroot -p --databases cjc cjcdb > dump1.sql


--databases選項使命令列上的所有名稱都被視為資料庫名稱。

如果沒有此選項,mysqldump將第一個名稱視為資料庫名稱,後面的名稱視為表名稱。

例如:

匯出cjc庫test1表。

mysqldump -uroot -p cjc test1 > dump2.sql


對於--all databases或--databases,mysqldump在每個資料庫的轉儲輸出之前編寫CREATE DATABASE和USE語句。

這樣可以確保在重新載入轉儲檔案時,如果每個資料庫不存在,它將建立每個資料庫,並使其成為預設資料庫,以便將資料庫內容載入到它們來自的同一個資料庫中。

如果要使轉儲檔案在重新建立資料庫之前強制刪除每個資料庫,請同時使用--add drop database選項。

在本例中,mysqldump在每條CREATE DATABASE語句之前寫入一條DROP DATABASE語句。

例如:

備份cjc資料庫,同時在轉儲檔案中記錄刪除cjc資料庫的語句。

mysqldump -uroot -p --databases --add-drop-database cjc  > dump3.sql


如果不加--databases選項,轉儲檔案中不會記錄包含CREATE DATABASE或USE語句。

例如:

mysqldump -uroot -p cjc  > dump4.sql


僅匯出表結構,不匯出資料。

mysqldump -uroot -p --no-data cjcdb t1 > dump6.sql


對於僅定義轉儲,新增--routines和--events選項,以還包括儲存過程和事件定義:

mysqldump -uroot -p --no-data --routines --events test > dump-defs.sql


僅匯出表資料,不匯出表結構。

mysqldump -uroot -p --no-create-info cjcdb t1 > dump7.sql


MySQLdump 備份時如何保持資料的一致性( –single-transaction)

MySQLdump備份不得不提的兩個關鍵引數

--single-transcation
--master-data=2
--master-data=2,--master-data=1 預設的話會--lock-all-tables,會鎖住整個mysql資料庫中的所有表。但是如果加上--single-transaction會加上事務,不會鎖表。
--master-data=2表示在dump過程中記錄主庫的binlog和pos點,並在dump檔案中註釋掉這一行;
--master-data=1表示在dump過程中記錄主庫的binlog和pos點,並在dump檔案中不註釋掉這一行,即恢復時會執行;
 
--dump-slave=2表示在dump過程中,在從庫dump,mysqldump程式也要在從庫執行,記錄當時主庫的binlog和pos點,並在dump檔案中註釋掉這一行;
--dump-slave=1表示在dump過程中,在從庫dump,mysqldump程式也要在從庫執行,記錄當時主庫的binlog和pos點,並在dump檔案中不註釋掉這一行;

關於mysqldump工作原理

1.呼叫FWRL(flush tables with read lock),全域性禁止讀寫

2.開啟快照讀,獲取此期間的快照(僅僅對innodb起作用)

3.備份非innodb表資料(*.frm,*.myi,*.myd等)

4.非innodb表備份完畢之後,釋放FTWRL

5.逐一備份innodb表資料

6.備份完成


mysqldump -uroot -p --databases cjcdb --single-transaction --master-data=2 --default-character-set=utf8mb4 --flush-logs --quick > dump8.sql

檢視:

-- CHANGE MASTER TO MASTER_LOG_FILE='on.000010', MASTER_LOG_POS=194;
--
-- Current Database: `cjcdb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjcdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `cjcdb`;
......
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

–single-transaction 實際上透過做了下面兩個操作 :

(1)在開始的時候把該 session 的事務隔離級別設定成 repeatable read ;
(2)然後啟動一個事務(執行 begin ),備份結束的時候結束該事務(執行 commit )
有了這兩個操作,在備份過程中,該 session 讀到的資料都是啟動備份時的資料(同一個點)。
可以理解為對於 innodb 引擎來說加了該引數,備份開始時就已經把要備份的資料定下來了,備份過程中的提交的事務時是看不到的,也不會備份進去。
對於不支援事務的引擎如 MyISAM ,只能透過鎖表來保證資料一致性,這裡分三種情況:
(1)匯出全庫 :加 –lock-all-tables 引數 , 這會在備份開始的時候啟動一個全域性讀鎖 ( 執行 flush tables with read lock ), 其他 session 可以讀取但不能更新資料 , 備份過程中資料沒有變化 , 所以最終得到的資料肯定是完全一致的 ;
(3)匯出單個庫:加 –lock-tables 引數,這會在備份開始的時候鎖該庫的所有表,其他 session 可以讀但不能更新該庫的所有表,該庫的資料一致;

恢復資料庫:

恢復所有資料庫:

mysql -uroot -p  <  dumpxxx.sql

mysql> show databases;

mysql> source dump.sql


方式五:mysqlbinlog恢復資料


mysqlbinlog常見的選項:

a、--start-datetime

b、--stop-datetime

c、--start-position

d、--stop-position


flush logs

flush 重新整理log日誌,自此刻開始產生一個新編號的binlog日誌檔案;


注意:

每當mysqld服務重啟時,會自動執行此命令,重新整理binlog日誌;

在mysqlddump備份資料時加-F選項也會重新整理binlog日誌;


reset master

重置(清空)所有binlog日誌


a、查詢最近的binlog日誌:
show binlog events\G;
b、指定查詢mysql-bin.000002這個檔案
show binlog events in 'mysql-bin.000002'\G;
c、指定查詢mysql-bin.000002這個檔案,從pos點:624開始查起:
show binlog events in 'mysql-bin.000002' from 624\G;
d、指定查詢mysql-bin.000002這個檔案,從pos點:624開始查起,查詢10條(即10條語句)
show binlog events in 'mysql-bin.000002' from 624 limit 10\G;
e、指定查詢 mysql-bin.000002這個檔案,從pos點:624開始查起,偏移2行(即中間跳過2個)查詢10條(即10條語句)。
show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

恢復場景如下:

備份cjcdb01庫資料:

insert into t2 values(1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');

MySQL [cjcdb01]> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | a    |
|    5 | a    |
+------+------+
5 rows in set (0.00 sec)

備份:

mysqldump -uroot -p --databases cjcdb01 --single-transaction --master-data=2 > /home/mysql/backup/dump03.sql
insert into t2 values(6,'a'),(7,'a');
update t2 set name='b' where id=5;
insert into t2 values(8,'a');
update t2 set name='c' where id=2;
update t2 set name='d' where id=3;
insert into t2 values(9,'a');
delete from t2 where id=9;
insert into t2 values(9,'f');

模擬誤刪除:

delete from t2;


MySQL [cjcdb01]> select * from t2;

Empty set (0.00 sec)


恢復:

檢視備份結束位置

[mysql@cjcos02 backup]$ cat dump03.sql |grep MASTER
-- CHANGE MASTER TO MASTER_LOG_FILE='on.000005', MASTER_LOG_POS=7741;
MySQL [cjcdb01]> show binary logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |       177 |
| on.000002 |       177 |
| on.000003 |       177 |
| on.000004 |       573 |
| on.000005 |     10204 |
+-----------+-----------+
5 rows in set (0.00 sec)
MySQL [cjcdb01]> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
MySQL [cjcdb01]> SHOW BINLOG EVENTS IN 'on.000005' FROM 7741;
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos   | Event_type  | Server_id | End_log_pos | Info                                                               |
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
| on.000005 |  7741 | Gtid        |         1 |        7806 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:33' |
| on.000005 |  7806 | Query       |         1 |        7881 | BEGIN                                                              |
| on.000005 |  7881 | Table_map   |         1 |        7932 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  7932 | Write_rows  |         1 |        7981 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  7981 | Xid         |         1 |        8012 | COMMIT /* xid=236 */                                               |
| on.000005 |  8012 | Gtid        |         1 |        8077 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:34' |
| on.000005 |  8077 | Query       |         1 |        8152 | BEGIN                                                              |
| on.000005 |  8152 | Table_map   |         1 |        8203 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  8203 | Update_rows |         1 |        8253 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  8253 | Xid         |         1 |        8284 | COMMIT /* xid=237 */                                               |
| on.000005 |  8284 | Gtid        |         1 |        8349 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:35' |
| on.000005 |  8349 | Query       |         1 |        8424 | BEGIN                                                              |
| on.000005 |  8424 | Table_map   |         1 |        8475 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  8475 | Write_rows  |         1 |        8517 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  8517 | Xid         |         1 |        8548 | COMMIT /* xid=238 */                                               |
| on.000005 |  8548 | Gtid        |         1 |        8613 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:36' |
| on.000005 |  8613 | Query       |         1 |        8688 | BEGIN                                                              |
| on.000005 |  8688 | Table_map   |         1 |        8739 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  8739 | Update_rows |         1 |        8789 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  8789 | Xid         |         1 |        8820 | COMMIT /* xid=239 */                                               |
| on.000005 |  8820 | Gtid        |         1 |        8885 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:37' |
| on.000005 |  8885 | Query       |         1 |        8960 | BEGIN                                                              |
| on.000005 |  8960 | Table_map   |         1 |        9011 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9011 | Update_rows |         1 |        9061 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9061 | Xid         |         1 |        9092 | COMMIT /* xid=240 */                                               |
| on.000005 |  9092 | Gtid        |         1 |        9157 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:38' |
| on.000005 |  9157 | Query       |         1 |        9232 | BEGIN                                                              |
| on.000005 |  9232 | Table_map   |         1 |        9283 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9283 | Write_rows  |         1 |        9325 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9325 | Xid         |         1 |        9356 | COMMIT /* xid=241 */                                               |
| on.000005 |  9356 | Gtid        |         1 |        9421 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:39' |
| on.000005 |  9421 | Query       |         1 |        9496 | BEGIN                                                              |
| on.000005 |  9496 | Table_map   |         1 |        9547 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9547 | Delete_rows |         1 |        9589 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9589 | Xid         |         1 |        9620 | COMMIT /* xid=242 */                                               |
| on.000005 |  9620 | Gtid        |         1 |        9685 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:40' |
| on.000005 |  9685 | Query       |         1 |        9760 | BEGIN                                                              |
| on.000005 |  9760 | Table_map   |         1 |        9811 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9811 | Write_rows  |         1 |        9853 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9853 | Xid         |         1 |        9884 | COMMIT /* xid=244 */                                               |
| on.000005 |  9884 | Gtid        |         1 |        9949 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:41' |
| on.000005 |  9949 | Query       |         1 |       10024 | BEGIN                                                              |
| on.000005 | 10024 | Table_map   |         1 |       10075 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 | 10075 | Delete_rows |         1 |       10173 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 | 10173 | Xid         |         1 |       10204 | COMMIT /* xid=245 */                                               |
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
45 rows in set (0.00 sec)

第一次delete位置在9547,屬於正常操作,第二次delete位置在10075屬性誤操作。

[mysql@cjcos02 backup]$ mysql -uroot -p -e"show binlog events in 'on.000005'"|grep Delete_rows
Enter password: 
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
on.0000055965Delete_rows16077table_id: 112 flags: STMT_END_F
on.0000057355Delete_rows17418table_id: 112 flags: STMT_END_F
on.0000059547Delete_rows19589table_id: 114 flags: STMT_END_F

獲取表結構:

MySQL [cjcdb01]> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

透過備份檔案獲取t2表結構:

[mysql@cjcos02 ~]$ sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t2`/!d;q' dump01.sql
DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

透過備份檔案獲取t2表資料:

[mysql@cjcos02 backup]$ grep 'INSERT INTO `t2`' dump03.sql
INSERT INTO `t2` VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');

透過備份恢復表資料

rename table t2 to t2_bak;
create table t2 like t2_bak;
INSERT INTO `t2` VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');
/usr/local/mysql/bin/mysqlbinlog --start-position=7741 --stop-position=10075 --database=cjcdb01 /usr/local/mysql/data/on.000005 | /usr/local/mysql/bin/mysql -u root -p

透過binlog獲取資料

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2021-09-20 12:15:34" /usr/local/mysql/data/on.000005
[mysql@cjcos02 data]$ /usr/local/mysql/bin/mysqlbinlog --start-datetime="2021-09-20 12:15:34" /usr/local/mysql/data/on.000005|grep -i del
DELIMITER /*!*/;
#210920 12:34:53 server id 1  end_log_pos 6077 CRC32 0x01c2c864 Delete_rows: table id 112 flags: STMT_END_F
DELIMITER ;
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/on.000005 --start-datetime="2021-09-20 12:15:34" --stop-position 6077 >2.sql
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/on.000005 --start-datetime="2021-09-20 12:15:34" --stop-position 6077|mysql -uroot -p
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/on.000005 --start-datetime="2021-09-20 12:15:34" --skip-gtids --stop-position=6077 >01.sql

具體恢復方法,參考:MySQL備份和恢復

http://blog.itpub.net/29785807/viewspace-2156430/


六:mysqldumper/myloader

參考連結:


mysqldump無法並行,這點與Oracle的expdp相比,存在一定的劣勢,但是開源的mysqldumper是一個很好的補充

mydumper()是一個在GNU GPLv3許可下發布的高效能MySQL備份和恢復工具集。

mydumper是多執行緒的,他建立一個mysql備份就比隨mysql釋出的mysqldump工具要快得多。

mydumper也有從源端伺服器恢復二進位制日誌的能力。


mysqldumper優點

1.多執行緒,可以是轉存資料快很多

2.mydumper的輸出已於管理和分析,因為他的表和後設資料是分開的單獨檔案。

3.所有執行緒都維護有一直的快照,這邊提供了精準的主從位置。

4.Mydumper支援Perl正規表示式,這樣就既可以包括是資料庫名和報名的模式匹配,也可以配置這種匹配。

5.透過名為myloader的多執行緒工具,mydumper工具集也可以從mydumper備份中恢復資料。


主要備份步驟概括

1.主執行緒 FLUSH TABLES WITH READ LOCK, 施加全域性只讀鎖,以阻止DML語句寫入,保證資料的一致性

2.讀取當前時間點的二進位制日誌檔名和日誌寫入的位置並記錄在metadata檔案中,以供即使點恢復使用

3.N個(執行緒數可以指定,預設是4)dump執行緒 START TRANSACTION WITH CONSISTENT SNAPSHOT; 開啟讀一致的事物

4.dump non-InnoDB tables, 首先匯出非事物引擎的表

5.主執行緒 UNLOCK TABLES 非事物引擎備份完後,釋放全域性只讀鎖

6.dump InnoDB tables, 基於事物匯出InnoDB表

7.事務結束


mysqldumper安裝

資料庫版本 : MySQL 5.7

作業系統版本: CentOS 7.8


軟體地址:



yum -y install cmake glib2 pcre pcre-devel mysql-devel
###yum -y install mydumper-0.9.5-2.el7.x86_64.rpm
[root@cjcos02 mysql]# yum install mydumper-0.9.1-4.1.x86_64.rpm 
......
Running transaction
  Installing : mydumper-0.9.1-4.1.x86_64                                                                                                                                                   1/1 
  Verifying  : mydumper-0.9.1-4.1.x86_64                                                                                                                                                   1/1 
Installed:
  mydumper.x86_64 0:0.9.1-4.1  
  
[root@cjcos02 mysql]# which mydumper
/usr/bin/mydumper
[root@cjcos02 mysql]# which myloader
/usr/bin/myloader
[root@cjcos02 mysql]# mydumper --help
Usage:
  mydumper [OPTION?] multi-threaded MySQL dumping
Help Options:
  -?, --help                  Show help options
Application Options:
  -B, --database              Database to dump
  -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
  -o, --outputdir             Directory to output files to
  -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000
  -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
  -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB
  -c, --compress              Compress output files
  -e, --build-empty-files     Build dump files even if no data available from table
  -x, --regex                 Regular expression for 'db.table' matching
  -i, --ignore-engines        Comma delimited list of storage engines to ignore
  -m, --no-schemas            Do not dump table schemas with the data
  -d, --no-data               Do not dump table data
  -G, --triggers              Dump triggers
  -E, --events                Dump events
  -R, --routines              Dump stored procedures and functions
  -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
  --less-locking              Minimize locking time on InnoDB tables.
  -l, --long-query-guard      Set long query timer in seconds, default 60
  -K, --kill-long-queries     Kill long running queries (instead of aborting)
  -D, --daemon                Enable daemon mode
  -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60
  -L, --logfile               Log file name to use, by default stdout is used
  --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
  --skip-tz-utc               
  --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
  --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
  --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
  -U, --updated-since         Use Update_time to dump only tables updated in the last U days
  --trx-consistency-only      Transactional consistency only
  -h, --host                  The host to connect to
  -u, --user                  Username with privileges to run the dump
  -p, --password              User password
  -P, --port                  TCP/IP port to connect to
  -S, --socket                UNIX domain socket file to use for connection
  -t, --threads               Number of threads to use, default 4
  -C, --compress-protocol     Use compression on the MySQL connection
  -V, --version               Show the program version and exit
  -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  
-B 指定資料庫
-T 指定表
-t 指定執行緒數,預設4
-v 輸出顯示info
-L 指定日誌路徑和名稱
-o 指定備份檔案路徑和名稱

備份:

mydumper -u root -p 1 -B cjcdb01 -T t1,t2  -t 4 -r 100000 -c --less-locking  -v 3 -D -L /home/mysql/backup/mydumper01.log   -o /home/mysql/backup/

[mysql@cjcos02 backup]$ ls
0  003.sql  01.sql  1  2.sql  33.sql  dump03.sql  mydumper01.log
[mysql@cjcos02 backup]$ cat mydumper01.log 
2021-09-20 19:35:00 [INFO] - Connected to a MySQL server
2021-09-20 19:35:00 [INFO] - Started dump at: 2021-09-20 19:35:00
2021-09-20 19:35:00 [INFO] - Written master status
2021-09-20 19:35:00 [INFO] - Thread 5 connected using MySQL connection ID 24
2021-09-20 19:35:00 [INFO] - Thread 6 connected using MySQL connection ID 25
2021-09-20 19:35:00 [INFO] - Thread 7 connected using MySQL connection ID 26
2021-09-20 19:35:00 [INFO] - Thread 8 connected using MySQL connection ID 27
2021-09-20 19:35:00 [INFO] - Thread 1 connected using MySQL connection ID 28
2021-09-20 19:35:00 [INFO] - Thread 2 connected using MySQL connection ID 29
2021-09-20 19:35:00 [INFO] - Thread 3 connected using MySQL connection ID 30
2021-09-20 19:35:00 [INFO] - Thread 4 connected using MySQL connection ID 31
2021-09-20 19:35:00 [INFO] - Thread 6 shutting down
2021-09-20 19:35:00 [INFO] - Thread 7 shutting down
2021-09-20 19:35:00 [INFO] - Thread 8 shutting down
2021-09-20 19:35:00 [INFO] - Thread 5 dumping data for `cjcdb01`.`t1`
2021-09-20 19:35:00 [INFO] - Non-InnoDB dump complete, unlocking tables
2021-09-20 19:35:00 [INFO] - Thread 2 dumping data for `cjcdb01`.`t2`
2021-09-20 19:35:00 [INFO] - Thread 3 dumping schema for `cjcdb01`.`t1`
2021-09-20 19:35:00 [INFO] - Thread 4 dumping schema for `cjcdb01`.`t2`
2021-09-20 19:35:00 [INFO] - Thread 5 shutting down
2021-09-20 19:35:00 [INFO] - Thread 3 shutting down
2021-09-20 19:35:00 [INFO] - Thread 2 shutting down
2021-09-20 19:35:00 [INFO] - Thread 4 shutting down
2021-09-20 19:35:00 [INFO] - Thread 1 shutting down
2021-09-20 19:35:00 [INFO] - Finished dump at: 2021-09-20 19:35:00

檢視備份檔案:

[mysql@cjcos02 backup]$ ll -rth 0/
total 24K
-rw-rw-rw- 1 mysql mysql  88 Sep 20 19:35 cjcdb01-schema-create.sql.gz
-rw-rw-rw- 1 mysql mysql 404 Sep 20 19:35 cjcdb01.t1-schema.sql.gz
-rw-rw-rw- 1 mysql mysql 151 Sep 20 19:35 cjcdb01.t2.sql.gz
-rw-rw-rw- 1 mysql mysql 170 Sep 20 19:35 cjcdb01.t2-schema.sql.gz
-rw-rw-rw- 1 mysql mysql 200 Sep 20 19:35 cjcdb01.t1.sql.gz
-rw-rw-rw- 1 mysql mysql 172 Sep 20 19:35 metadata

恢復:

[mysql@cjcos02 backup]$ myloader --help
Usage:
  myloader [OPTION?] multi-threaded MySQL loader
Help Options:
  -?, --help                        Show help options
Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -h, --host                        The host to connect to
  -u, --user                        Username with privileges to run the dump
  -p, --password                    User password
  -P, --port                        TCP/IP port to connect to
  -S, --socket                      UNIX domain socket file to use for connection
  -t, --threads                     Number of threads to use, default 4
  -C, --compress-protocol           Use compression on the MySQL connection
  -V, --version                     Show the program version and exit
  -v, --verbose                     Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  
MySQL [cjcdb01]> select * from t1;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)
MySQL [cjcdb01]> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | a    |
|    5 | a    |
+------+------+
5 rows in set (0.00 sec)

模擬誤刪除:

MySQL [cjcdb01]> delete from t1;
Query OK, 2 rows affected (0.06 sec)
MySQL [cjcdb01]> delete from t2;
Query OK, 5 rows affected (0.09 sec)
MySQL [cjcdb01]> delete from t1;
Query OK, 0 rows affected (0.00 sec)
MySQL [cjcdb01]> delete from t2;
Query OK, 0 rows affected (0.00 sec)

恢復:

myloader -u root -p 1  -B cjcdb01 -e -t 8  -d /home/mysql/backup/ --overwrite-tables -v 3
[mysql@cjcos02 backup]$ myloader -u root -p 1  -B cjcdb01 -e -t 8  -d /home/mysql/backup/0/ --overwrite-tables -v 3
** Message: 8 threads created
** Message: Dropping table or view (if exists) `cjcdb01`.`t1`
** Message: Creating table `cjcdb01`.`t1`
** Message: Dropping table or view (if exists) `cjcdb01`.`t2`
** Message: Creating table `cjcdb01`.`t2`
** Message: Thread 1 restoring `cjcdb01`.`t1` part 0
** Message: Thread 2 restoring `cjcdb01`.`t2` part 0
** Message: Thread 3 shutting down
** Message: Thread 4 shutting down
** Message: Thread 5 shutting down
** Message: Thread 6 shutting down
** Message: Thread 7 shutting down
** Message: Thread 8 shutting down
** Message: Thread 1 shutting down
** Message: Thread 2 shutting down
MySQL [cjcdb01]> select * from t1;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)
MySQL [cjcdb01]> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | a    |
|    5 | a    |
+------+------+
5 rows in set (0.00 sec)

方式七:xtrbackup

獲取xtrabackup備份工具

網站域名:

[root@mysql01 xtrabakcup]# rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm 
warning: percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-80-8.0.26-18.1.el7.x86_64
[root@mysql01 xtrabakcup]# rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm --nodeps
warning: percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-xtrabackup-80-8.0.26-18.1################################# [100%]

檢視測試資料

MySQL [cjcdb]> select * from cjcdb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

授權:

MySQL [cjcdb]> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.08 sec)
MySQL [cjcdb]> flush privileges;
Query OK, 0 rows affected (0.10 sec)

檢視xtrabackup命令

[mysql@mysql01 ~]$ man xtrabackup
$ xtrabackup --backup --target-dir=/home/mysql/xtrabakcup --datadir=/usr/local/mysql/data/
$ xtrabackup --backup --target-dir=/data/backups/inc1 \
          --incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/
xtrabackup --defaults-file=/etc/my.cnf -uadmin  -p123 --target-dir=/home/mysql/xtrabakcup
[mysql@mysql02 bin]$ ./mysqld --verbose --help |grep -A 1 "Default options"
2021-09-16T14:11:47.119008Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-09-16T14:11:47.119163Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

備份:

$ innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 /home/mysql/backup/
 
 [mysql@mysql02 ~]$ innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 /home/mysql/backup/xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --tmpdir=/tmp --server-id=2 --log_bin=on 
xtrabackup: recognized client arguments: 
210916 22:44:18 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
210916 22:44:20  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
210916 22:44:20  version_check Connected to MySQL server
210916 22:44:20  version_check Executing a version check against the server...
210916 22:44:30  version_check Done.
210916 22:44:30 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Using server version 5.7.35-log
innobackupex version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210916 22:44:30 >> log scanned up to (2780419)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
210916 22:44:31 [01] Copying ./ibdata1 to /home/mysql/backup/2021-09-16_22-44-18/ibdata1
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/plugin.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/plugin.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/servers.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/servers.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_topic.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/help_topic.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_category.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/help_category.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_relation.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/help_relation.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_keyword.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/help_keyword.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/time_zone_name.ibd to /home/mysql/backup/2021-09-16_22-44-18/mysql/time_zone_name.ibd
......
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /home/mysql/backup/2021-09-16_22-44-18/sys/x@0024waits_global_by_latency.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./sys/session_ssl_status.frm to /home/mysql/backup/2021-09-16_22-44-18/sys/session_ssl_status.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./cjcdb/db.opt to /home/mysql/backup/2021-09-16_22-44-18/cjcdb/db.opt
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./cjcdb/t1.frm to /home/mysql/backup/2021-09-16_22-44-18/cjcdb/t1.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 Finished backing up non-InnoDB tables and files
210916 22:44:34 [00] Writing /home/mysql/backup/2021-09-16_22-44-18/xtrabackup_binlog_info
210916 22:44:34 [00]        ...done
210916 22:44:34 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2780410'
xtrabackup: Stopping log copying thread.
.210916 22:44:34 >> log scanned up to (2780419)
210916 22:44:34 Executing UNLOCK TABLES
210916 22:44:34 All tables unlocked
210916 22:44:34 [00] Copying ib_buffer_pool to /home/mysql/backup/2021-09-16_22-44-18/ib_buffer_pool
210916 22:44:34 [00]        ...done
210916 22:44:34 Backup created in directory '/home/mysql/backup/2021-09-16_22-44-18/'
MySQL binlog position: filename 'on.000005', position '719', GTID of the last change 'a2c030a0-1560-11ec-8c9d-08002762e5f2:1-8,
bd1b20b5-1563-11ec-9520-080027daf155:1'
210916 22:44:34 [00] Writing /home/mysql/backup/2021-09-16_22-44-18/backup-my.cnf
210916 22:44:34 [00]        ...done
210916 22:44:34 [00] Writing /home/mysql/backup/2021-09-16_22-44-18/xtrabackup_info
210916 22:44:34 [00]        ...done
xtrabackup: Transaction log of lsn (2780410) to (2780419) was copied.
210916 22:44:34 completed OK!
innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 --incremental /homne/mysql/backup/1/
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 --backup --target-dir=/home/mysql/backup/

恢復:

innobackupex --apply-log /backups/2018-07-30_11-04-55/
innobackupex --copy-back --defaults-file=/etc/my.cnf  /backups/2018-07-30_11-04-55/

方式八:MEB

MySQL MEB備份和恢復

基於MySQL MEB的備份恢復

MEB(MySQL Enterprise Backup)是MySQL商業版中提供的備份工具,屬於物理備份。


mysql enterprise版比community在備份工具上有了提升,多了一個mysqlbackup的工具,即mysql企業備份工具。

當然遵循GPL的社群版也可以用:


該工具單獨下載:

到官方的下載中心:


點Sign up登入

然後搜尋並選擇平臺,搜尋mysql,找到企業版


同XtraBackup一樣,mysqlbackup的使用過程同樣包含如下三個步驟:

備份(--backup)=> 應用日誌(--apply-log)=> 恢復(--copy-back)


MySQL Enterprise Backup for Server 8.0 8.0.26 for Linux x86-64
V1010830-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux / RHEL 6 x86 (64bit), 36.0 MB
V1010832-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux / RHEL 7 x86 (64bit), 24.3 MB
V1010834-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux / RHEL 8 x86 (64bit), 21.8 MB
V1010835-01.zipMySQL Enterprise Backup 8.0.26 RPM for SuSE Enterprise Linux 15 / openSUSE 15 x86 (64bit), 29.0 MB
V1010836-01.zipMySQL Enterprise Backup 8.0.26 RPM for SuSE Enterprise Linux 12 x86 (64bit), 23.3 MB
V1010837-01.zipMySQL Enterprise Backup 8.0.26 TAR for Oracle Linux / RHEL 7 x86 (64bit), 36.7 MB
V1010839-01.zipMySQL Enterprise Backup 8.0.26 TAR for Generic Linux x86 (64bit), 51.7 MB
V1010849-01.zipMySQL Enterprise Backup 8.0.26 DEB for Debian 10 Linux x86 (64bit), 42.8 MB
V1010850-01.zipMySQL Enterprise Backup 8.0.26 DEB for Ubuntu 18.04 Linux x86 (64bit), 42.8 MB
V1010852-01.zipMySQL Enterprise Backup 8.0.26 DEB for Ubuntu 20.04 Linux x86 (64bit), 44.1 MB
V1010969-01.zipMySQL Enterprise Backup 8.0.26 Minimal TAR for Generic Linux (glibc2.17) x86 (64bit), 4.3 MB
[mysql@cjcos02 MEB]$ unzip V1010837-01.zip 
Archive:  V1010837-01.zip
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz  
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz.asc  
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz.md5  
 extracting: README.txt  
 
[mysql@cjcos02 MEB]$ tar -zxvf mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libcrypto.so
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libcrypto.so.1.1
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libssl.so
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libssl.so.1.1
mysql-commercial-backup-8.0.26-el7-x86_64/docs/INFO_BIN
mysql-commercial-backup-8.0.26-el7-x86_64/docs/INFO_SRC
mysql-commercial-backup-8.0.26-el7-x86_64/bin/mysqlbackup
mysql-commercial-backup-8.0.26-el7-x86_64/LICENSE.meb
mysql-commercial-backup-8.0.26-el7-x86_64/README.meb
[mysql@cjcos02 MEB]$ mv mysql-commercial-backup-8.0.26-el7-x86_64 /usr/local/mysql/meb/
[mysql@cjcos02 MEB]$ 
http://blog.itpub.net/29773961/viewspace-1992615/
[mysql@cjcos02 bin]$ mkdir /home/mysql/backup/meb

全備:

[mysql@cjcos02 bin]$ ./mysqlbackup -u root -p --backup-dir=/home/mysql/backup/meb backup-and-apply-log
MySQL Enterprise Backup  Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
./mysqlbackup
-u
root
-p
--backup-dir=/home/mysql/backup/meb
backup-and-apply-log
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".
Enter password: 
210920 21:23:31 MAIN     INFO: Establishing connection to server.
210920 21:23:31 MAIN     INFO: No SSL options specified.
210920 21:23:31 MAIN     INFO: MySQL server version is '5.7.35-log'
210920 21:23:31 MAIN     INFO: MySQL server compile os version is 'el7'
210920 21:23:32 MAIN    ERROR: MySQL query 'SELECT member_role, member_host, member_port, member_state FROM performance_schema.replication_group_members': 1054, Unknown column 'member_role' in 'field list'
210920 21:23:32 MAIN    ERROR: Connection initialization error.
mysqlbackup exit code: 19, Server returned error while executing sql
mysqlbackup failed with errors!

5.7版本,performance_schema.replication_group_members表沒有member_role欄位,在使用MEB8時,會報錯。


5.7
MySQL [mysql]> select version();
+------------+
| version()  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.00 sec)
MySQL [mysql]> desc performance_schema.replication_group_members;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| CHANNEL_NAME | char(64) | NO   |     | NULL    |       |
| MEMBER_ID    | char(36) | NO   |     | NULL    |       |
| MEMBER_HOST  | char(60) | NO   |     | NULL    |       |
| MEMBER_PORT  | int(11)  | YES  |     | NULL    |       |
| MEMBER_STATE | char(64) | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
5 rows in set (0.08 sec)
8.0
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)
mysql> desc performance_schema.replication_group_members;
+----------------+-----------+------+-----+---------+-------+
| Field          | Type      | Null | Key | Default | Extra |
+----------------+-----------+------+-----+---------+-------+
| CHANNEL_NAME   | char(64)  | NO   |     | NULL    |       |
| MEMBER_ID      | char(36)  | NO   |     | NULL    |       |
| MEMBER_HOST    | char(255) | NO   |     | NULL    |       |
| MEMBER_PORT    | int       | YES  |     | NULL    |       |
| MEMBER_STATE   | char(64)  | NO   |     | NULL    |       |
| MEMBER_ROLE    | char(64)  | NO   |     | NULL    |       |
| MEMBER_VERSION | char(64)  | NO   |     | NULL    |       |
+----------------+-----------+------+-----+---------+-------+
7 rows in set (0.03 sec)

使用Mysql8.0.26版本進行測試:

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>mysqlbackup.exe  --backup-dir=D:\cjc\mysql\MEB\backup backup-and-apply-log -uroot -p
MySQL Enterprise Backup  Ver 8.0.26-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup.exe
--backup-dir=D:\cjc\mysql\MEB\backup
backup-and-apply-log
-uroot
-p
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".
Enter password: *
210921 11:42:32 MAIN     INFO: Establishing connection to server.
210921 11:42:32 MAIN     INFO: No SSL options specified.
210921 11:42:32 MAIN     INFO: MySQL server version is '8.0.26'
210921 11:42:32 MAIN     INFO: MySQL server compile os version is 'Win64'
210921 11:42:32 MAIN     INFO: SSL/TLS version used for connection is TLSv1.3
210921 11:42:32 MAIN     INFO: Got some server configuration information from running server.
210921 11:42:32 MAIN     INFO: Establishing connection to server for locking.
210921 11:42:32 MAIN     INFO: No SSL options specified.
210921 11:42:37 MAIN     INFO: Backup directory exists: 'D:\cjc\mysql\MEB\backup'
210921 11:42:37 MAIN     INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210921 11:42:37 MAIN     INFO: Mysqlbackup component not installed.
210921 11:42:37 MAIN     INFO: MEB logfile created at D:\cjc\mysql\MEB\backup\meta\MEB_2021-09-21.11-42-37_backup-and-apply-log.log
210921 11:42:37 MAIN     INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_data_home_dir           =
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32
--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = D:\cjc\mysql\MEB\backup\datadir
  innodb_data_home_dir           = D:\cjc\mysql\MEB\backup\datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = D:\cjc\mysql\MEB\backup\datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = D:\cjc\mysql\MEB\backup\datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32
210921 11:42:37 MAIN     INFO: Unique generated backup id for this is 16321957525444995
210921 11:42:37 MAIN     INFO: Copying the server config file 'D:/cjc/mysql/mysql-8.0.26-winx64/data/auto.cnf'
210921 11:42:38 MAIN     INFO: Creating 14 buffers each of size 16777216.
210921 11:42:38 MAIN     INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210921 11:42:38 MAIN     INFO: Found checkpoint at lsn 151862887.
210921 11:42:38 MAIN     INFO: Starting log scan from lsn = 151862784 at offset = 867840 and checkpoint = 151862887 in file D:/cjc/mysql/mysql-8.0.26-winx64/data/ib_logfile1.
210921 11:42:38 MAIN     INFO: Full Backup with Apply-log operation starts with following threads
                               1 read-threads    6 process-threads    1 write-threads
210921 11:42:38 MAIN     INFO: Starting to copy all innodb files...
210921 11:42:38 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/ibdata1.
210921 11:42:38 RLP1     INFO: Starting to parse redo log at lsn = 151862811, whereas checkpoint_lsn = 151862887 and start_lsn = 151862784.
210921 11:42:38 RDR1     INFO: Starting to copy all undo files...
210921 11:42:39 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/undo_002.
210921 11:42:39 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/undo_001.
210921 11:42:40 RDR1     INFO: Starting to lock instance for backup...
210921 11:42:40 RDR1     INFO: The server instance is locked for backup.
210921 11:42:41 RDR1     INFO: The server instance is unlocked after 0.826 seconds.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/sys/sys_config.ibd.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/cjcdb/t1.ibd.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/cjcdb/t2.ibd.
......
210921 11:43:11 PCR1     INFO: Setting 'ib_logfile0' file size to 50331648
210921 11:43:16 PCR1     INFO: Setting 'ib_logfile1' file size to 50331648
210921 11:43:16 PCR1     INFO: Log file header:
         format = 4
         pad1 = 0
         start lsn = 151937024
         checkpoint lsn = 151937202
         checksum = 4172735474
         creator = MEB 8.0.26
210921 11:43:17 PCR1     INFO: We were able to parse ibbackup_logfile up to lsn 151937202.
210921 11:43:17 PCR1     INFO: Last MySQL binlog file position 0 156, file name binlog.000002
210921 11:43:17 PCR1     INFO: The first data file is 'D:/cjc/mysql/MEB/backup/datadir/ibdata1'
                               and the new created log files are at 'D:\cjc\mysql\MEB\backup\datadir'
210921 11:43:17 MAIN     INFO: Apply-log operation completed successfully.
210921 11:43:17 MAIN     INFO: Full backup prepared for recovery successfully.
mysqlbackup completed OK!

檢視備份檔案

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>cd D:\cjc\mysql\MEB\backup
D:\cjc\mysql\MEB\backup>dir
 驅動器 D 中的卷是 LENOVO
 卷的序列號是 568B-1BF9
 D:\cjc\mysql\MEB\backup 的目錄
2021/09/21  11:43    <DIR>          .
2021/09/21  11:43    <DIR>          ..
2021/09/21  11:42               325 backup-my.cnf
2021/09/21  11:43    <DIR>          datadir
2021/09/21  11:43    <DIR>          meta
2021/09/21  11:43            20,032 server-all.cnf
2021/09/21  11:43               452 server-my.cnf
               3 個檔案         20,809 位元組
               4 個目錄 82,218,188,800 可用位元組

恢復:

D:\cjc\mysql\MEB\backup
mysql> use cjcdb;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cjcdb |
+-----------------+
| t1              |
| t2              |
| t3              |
| t5              |
| t6              |
| t7              |
+-----------------+
6 rows in set (0.01 sec)
mysql> drop database cjcdb;
Query OK, 6 rows affected (5.20 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

應用日誌:

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>mysqlbackup --backup-dir=D:\cjc\mysql\MEB\backup apply-log
MySQL Enterprise Backup  Ver 8.0.26-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup
--backup-dir=D:\cjc\mysql\MEB\backup
apply-log
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".
210921 15:24:48 MAIN     INFO: read_backup_variables_txt_file: 'D:\cjc\mysql\MEB\backup\meta\backup_variables.txt'
210921 15:24:48 MAIN     INFO: backup variable mysql_version=8.0.26
210921 15:24:48 MAIN     INFO: MySQL server version is '8.0.26'
210921 15:24:48 MAIN     INFO: Restoring ...8.0.26 version
210921 15:24:48 MAIN     INFO: backup variable meb_version=8.0.26
210921 15:24:48 MAIN     INFO: backup variable start_lsn=151862784
......
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32
210921 15:24:49 MAIN     INFO: Apply-log operation has already been done on that backup.
mysqlbackup completed OK!

恢復

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>
mysqlbackup --defaults-file=D:\cjc\mysql\mysql-8.0.26-winx64\my.ini --datadir=D:\cjc\mysql\mysql-8.0.26-winx64\data1 --backup-dir=D:\cjc\mysql\MEB\backup  copy-back --force
 D:\cjc\mysql\mysql-8.0.26-winx64\data1 的目錄
2021/09/21  17:01    <DIR>          .
2021/09/21  17:01    <DIR>          ..
2021/09/21  17:01                56 backup-auto.cnf
2021/09/21  17:01               828 backup_variables.txt
2021/09/21  17:01        37,934,399 binlog.000001
2021/09/21  17:01               156 binlog.000002
2021/09/21  17:01                32 binlog.index
2021/09/21  17:01    <DIR>          cjcdb
2021/09/21  17:01        12,582,912 ibdata1
2021/09/21  17:01             8,724 ib_buffer_pool
2021/09/21  17:01        50,331,648 ib_logfile0
2021/09/21  17:01        50,331,648 ib_logfile1
2021/09/21  17:01    <DIR>          mysql
2021/09/21  17:01        25,165,824 mysql.ibd
2021/09/21  17:01    <DIR>          performance_schema
2021/09/21  17:01            20,032 server-all.cnf
2021/09/21  17:01               452 server-my.cnf
2021/09/21  17:01    <DIR>          sys
2021/09/21  17:01        16,777,216 undo_001
2021/09/21  17:01        16,777,216 undo_002
              14 個檔案    209,931,143 位元組
               6 個目錄 82,003,558,400 可用位元組


方式九:冷備

檢視當前資料:

MySQL [cjcdb01]> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
|    2 | a    |
+------+------+
3 rows in set (0.00 sec)

檢視資料檔案目錄:

MySQL [cjcdb01]> show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

停庫:

[mysql@cjcos02 ~]$ service mysqld status
 SUCCESS! MySQL running (3941)
 
[mysql@cjcos02 ~]$ service mysqld stop
Shutting down MySQL............ SUCCESS!

備份資料檔案:

[mysql@cjcos02 ~]$ cd /usr/local/mysql/
[mysql@cjcos02 mysql]$ mkdir data_bak
[mysql@cjcos02 mysql]$ cp -r data/ data_bak/
[mysql@cjcos02 mysql]$ du -sh data*
122Mdata
122Mdata_bak

啟庫:

[mysql@cjcos02 mysql]$ service mysqld start
Starting MySQL.. SUCCESS!

模擬誤操作:

MySQL [(none)]> drop database cjcdb01;
Query OK, 4 rows affected (0.04 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cjcdb02            |
| cjcdb03            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
MySQL [(none)]> select * from cjcdb01.test1;
ERROR 1146 (42S02): Table 'cjcdb01.test1' doesn't exist

恢復:

[mysql@cjcos02 mysql]$ service mysqld stop
Shutting down MySQL.. SUCCESS! 
[mysql@cjcos02 mysql]$ mv data data_01
[mysql@cjcos02 mysql]$ mv data_bak/data data
[mysql@cjcos02 mysql]$ service mysqld start

驗證:

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cjcdb01            |
| cjcdb02            |
| cjcdb03            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
MySQL [(none)]> select * from cjcdb01.test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
|    2 | a    |
+------+------+
3 rows in set (0.01 sec)

方式十:閃回

開源工具binlog2sql來進行實戰演練。binlog2sql由美團點評DBA團隊(上海)出品,多次線上上環境做快速回滾。


安裝binlog2sql:

[mysql@cjcos02 ~]$ git clone 
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 153.26 KiB | 0 bytes/s, done.
Resolving deltas: 100% (170/170), done.
[mysql@cjcos02 ~]$ cd binlog2sql/
[mysql@cjcos02 binlog2sql]$ ls
binlog2sql  example  LICENSE  README.md  requirements.txt  tests
[mysql@cjcos02 binlog2sql]$ cat requirements.txt 
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13
[mysql@cjcos02 binlog2sql]$ pip install -r requirements.txt
bash: pip: command not found...
[mysql@cjcos02 binlog2sql]$ curl -O 
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1863k  100 1863k    0     0  1751k      0  0:00:01  0:00:01 --:--:-- 1751k
[mysql@cjcos02 binlog2sql]$ python get-pip.py
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Defaulting to user installation because normal site-packages is not writeable
Collecting pip<21.0
  Downloading pip-20.3.4-py2.py3-none-any.whl (1.5 MB)
     |████████████████████████████████| 1.5 MB 822 kB/s 
Collecting wheel
  Downloading wheel-0.37.0-py2.py3-none-any.whl (35 kB)
Installing collected packages: pip, wheel
Successfully installed pip-20.3.4 wheel-0.37.0
[mysql@cjcos02 binlog2sql]$ pip install -r requirements.txt
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Defaulting to user installation because normal site-packages is not writeable
Collecting PyMySQL==0.7.11
  Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78 kB)
     |████████████████████████████████| 78 kB 592 kB/s 
Collecting wheel==0.29.0
  Downloading wheel-0.29.0-py2.py3-none-any.whl (66 kB)
     |████████████████████████████████| 66 kB 671 kB/s 
Collecting mysql-replication==0.13
  Downloading mysql-replication-0.13.tar.gz (33 kB)
Building wheels for collected packages: mysql-replication
  Building wheel for mysql-replication (setup.py) ... done
  Created wheel for mysql-replication: filename=mysql_replication-0.13-py2-none-any.whl size=41107 sha256=164b5775a156b1de450699de8e05849d338623df15a15a465c2c1f3b75179c6b
  Stored in directory: /home/mysql/.cache/pip/wheels/7f/98/ad/902f1ff5bfac7e8ea16480601e85db4b87b6c608d26e294b81
Successfully built mysql-replication
Installing collected packages: PyMySQL, wheel, mysql-replication
  Attempting uninstall: wheel
    Found existing installation: wheel 0.37.0
    Uninstalling wheel-0.37.0:
      Successfully uninstalled wheel-0.37.0
Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0

建立測試資料

MySQL [cjcdb01]> create table test1(id int,name char(50));
Query OK, 0 rows affected (0.11 sec)
MySQL [cjcdb01]> insert into test1 values(1,'a'),(2,'a'),(3,'a');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
MySQL [cjcdb01]> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
+------+------+
3 rows in set (0.00 sec)
MySQL [cjcdb01]> delete from test1 where id=2;
Query OK, 1 row affected (0.05 sec)
MySQL [cjcdb01]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-09-21 16:49:07 |
+---------------------+
1 row in set (0.06 sec)
MySQL [cjcdb01]> show master logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |       177 |
| on.000002 |       177 |
| on.000003 |       177 |
| on.000004 |       573 |
| on.000005 |     15257 |
| on.000006 |       217 |
| on.000007 |       927 |
+-----------+-----------+
7 rows in set (0.00 sec)
[mysql@cjcos02 binlog2sql]$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'1' -dcjcdb01 --start-file='on.000007' --start-datetime='2021-09-21 16:30:01' --stop-datetime='2021-09-21 18:55:15' > /home/mysql/0001.sql
[mysql@cjcos02 binlog2sql]$ cat /home/mysql/0001.sql 
USE cjcdb01;
create table test1(id int,name char(50));
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (1, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (2, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (3, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
DELETE FROM `cjcdb01`.`test1` WHERE `id`=2 AND `name`='a' LIMIT 1; #start 725 end 896 time 2021-09-21 16:48:54
MySQL [cjcdb01]> show binlog events in 'on.000007';
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| on.000007 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.35-log, Binlog ver: 4                              |
| on.000007 | 123 | Previous_gtids |         1 |         194 | c3c28ba2-19c3-11ec-971b-08002765a9b1:1-54                          |
| on.000007 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:55' |
| on.000007 | 259 | Query          |         1 |         379 | use `cjcdb01`; create table test1(id int,name char(50))            |
| on.000007 | 379 | Gtid           |         1 |         444 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:56' |
| on.000007 | 444 | Query          |         1 |         519 | BEGIN                                                              |
| on.000007 | 519 | Table_map      |         1 |         573 | table_id: 121 (cjcdb01.test1)                                      |
| on.000007 | 573 | Write_rows     |         1 |         629 | table_id: 121 flags: STMT_END_F                                    |
| on.000007 | 629 | Xid            |         1 |         660 | COMMIT /* xid=69 */                                                |
| on.000007 | 660 | Gtid           |         1 |         725 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:57' |
| on.000007 | 725 | Query          |         1 |         800 | BEGIN                                                              |
| on.000007 | 800 | Table_map      |         1 |         854 | table_id: 121 (cjcdb01.test1)                                      |
| on.000007 | 854 | Delete_rows    |         1 |         896 | table_id: 121 flags: STMT_END_F                                    |
| on.000007 | 896 | Xid            |         1 |         927 | COMMIT /* xid=71 */                                                |
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
14 rows in set (0.00 sec)
[mysql@cjcos02 binlog2sql]$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'1' -dcjcdb01 --start-file='on.000007' --start-position=725 --stop-position=896 -B > /home/mysql/0002.sql
[mysql@cjcos02 binlog2sql]$ cat /home/mysql/0002.sql 
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (2, 'a'); #start 725 end 896 time 2021-09-21 16:48:54
[mysql@cjcos02 binlog2sql]$ mysql -h127.0.0.1 -P3306 -uroot -p'1'  < /home/mysql/0002.sql
MySQL [cjcdb01]> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
|    2 | a    |
+------+------+
3 rows in set (0.00 sec)

關於DDL的flashback

本文所述的flashback僅針對DML語句的快速回滾。

但如果誤操作是DDL的話,是無法利用binlog做快速回滾的,因為即使在row模式下,binlog對於DDL操作也不會記錄每行資料的變化。

要實現DDL快速回滾,必須修改MySQL原始碼,使得在執行DDL前先備份老資料。

目前有多個mysql定製版本實現了DDL閃回特性,阿里林曉斌團隊提交了patch給MySQL官方,MariaDB預計在不久後加入包含DDL的flashback特性。

DDL閃回的副作用是會增加額外儲存。考慮到其應用頻次實在過低,本文不做詳述,有興趣的同學可以自己去了解,重要的幾篇文章我在參考資料中做了引用。


十一: 快照備份

使用的比較少,需要儲存或系統工程師配合完成。

LVM使用寫時複製(copy-on-write)技術來建立快照

鎖定所有表,或停庫

FLUSH TABLES WITH READ LOCK;

建立快照

解鎖


十二:mysqlhotcopy

mysql5.7已經沒有這個命令了,多用於mysql5.5之前。

mysqlhotcopy使用lock tables、flush tables和cp或scp來快速備份資料庫或單個表,屬於裸檔案備份(物理備份),只能用於MyISAM引擎的資料庫。

本質是使用鎖表語句,然後cp或scp。

常用命令如下:

備份一個資料庫到目標目錄

mysqlhotcopy -u [使用者名稱] -p [密碼] [資料庫名] [目標目錄]


備份多個資料庫到目標目錄

mysqlhotcopy -u [使用者名稱] -p [密碼] [資料庫名1 資料庫名2...] [目標目錄]


備份資料庫中的某個表到指定目錄

mysqlhotcopy -u [使用者名稱] -p [密碼] [資料庫名]./[表名]/ [目標目錄]


還原

在Linux中Mysql資料庫都是以物理檔案存在的,資料庫對應的是目錄。如果想要還原只要將原來的檔案複製回原目錄即可。

單個資料庫備份還原方式:如果檔案所有這和所屬組不是mysql,請使用chown修改。

cp -ra [目標目錄] /var/lib/mysql/[資料庫名]/

#####chenjuchao 20210921 22:25#####

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2792933/,如需轉載,請註明出處,否則將追究法律責任。

相關文章