MySQL備份和恢復方法彙總
一：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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【MySQL】MySQL備份和恢復MySql
- Mysql備份和恢復MySql
- MySQL 備份和恢復 一MySql
- MySQL備份和恢復資料表的方法MySql
- Mysql備份恢復MySql
- mysql 備份恢復MySql
- 【MySql】innobackupex增量備份和恢復MySql
- 【MySql】innobackupex 增量備份和恢復MySql
- MySQL日誌管理，備份和恢復MySql
- MySQL備份和恢復工具圖譜MySql
- 【Mysql】xtrabackup 備份和恢復測試MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- MySQL備份與恢復MySql
- MySQL 備份與恢復MySql
- 解密MySQL備份恢復的4種方法解密MySql
- 備份和恢復
- Mysql備份與恢復(1)---物理備份MySql
- mysql備份和恢復測試(一）--xtrabackupMySql
- mysql innobackupex增量備份恢復MySql
- MySql邏輯備份恢復MySql
- Mysql的備份與恢復MySql
- mysql備份恢復測試MySql
- Mysql 備份與恢復 二MySql
- windwos server 路由備份和恢復 路由表備份和恢復Server路由
- MySQL 非常規恢復與物理備份恢復MySql
- redis 備份和恢復Redis
- 備份和恢復redisRedis
- Oracle 備份和恢復Oracle
- Mysql備份與恢復(2)---邏輯備份MySql
- mysqldump常用備份恢復方法MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- Backup And Recovery User's Guide-備份和恢復概覽-備份和恢復介紹-備份和恢復的目的GUIIDE
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- 使用mysqldump對mysql進行備份和恢復MySql
- 使用xtrabackup對mysql進行備份和恢復MySql
- MySQL備份和恢復具體實施(上)MySql
- java中實現MYSQL的備份和恢復JavaMySql
- oracle RMAN 備份恢復總結Oracle