Summary of MySql logical Backup Recovery Methods

Summary of MySql logical Backup Recovery Method 1. Mysql logical Backup 1. use mysqldump command to generate INSERT statement backup this method is similar to the oracle expdp \ exp tool Syntax: mysqldump [arguments]> file_name. SQL help: [root @ gc ~] # MysqldumpUsage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..] OR mysqldump [OPTIONS] -- all-databases [OPTIONS] For more options, use mysqldump -- help to back up an instance: back up all databases # mysqldump-uroot-proot -- all-database>/tmp/dumpback/alldb. SQL backup of some databases # mysqldump-uroot-proot -- database sqoop hive>/tmp/dumpback/sqoop_hive. SQL backup of tables in a database # mysqldump-uroot-proo T sqoop tb1>/tmp/dumpback/sqoop_tb1. SQL: [root @ gc dumpback] # more sqoop_tb1. SQL -- MySQL dump 10.13 Distrib 5.5.24, for Linux (x86_64) ---- Host: localhost Database: sqoop -- -------------------------------------------------------- Server version 5.5.24 /*! 40101 SET @ OLD_CHARACTER_SET_CLIENT = @ CHARACTER_SET_CLIENT */; ...... ---- Table structure for table 'tw1' -- drop table if exists 'tw1 ';/*! 40101 SET @ saved_cs_client = @ character_set_client */;/*! 40101 SET character_set_client = utf8 */; create table 'tb1 '('table _ scheme' varchar (64) character set utf8 not null default '', 'table _ name' varchar (64) character set utf8 not null default '', 'table _ type' varchar (64) character set utf8 not null default '') ENGINE = InnoDB default charset = latin1 ;/*! 40101 SET character_set_client = @ saved_cs_client */; ---- Dumping data for table 'tw1' -- lock tables 'tw1' WRITE ;/*! 40000 alter table 'tb1 'Disable KEYS */; insert into 'tb1 'values ('information _ scheme', 'character _ sets ', 'System view ')...... /*! 40000 alter table 'tb1 'enable keys */; unlock tables ;/*! 40103 SET TIME_ZONE = @ OLD_TIME_ZONE */; ...... -- Dump completed on 18:26:53 Note: how to ensure data backup consistency? To ensure data consistency, you can use the following two methods: first, retrieve all data at the same time for the storage engine supported by transactions, such as Innodb or BDB, you can use the "-- single-transaction" option to control the entire backup process in the same transaction. For example: # mysqldump -- single-transaction test> test_backup. SQL 2. The data in the database is in a static state. Use the lock table parameter -- lock-tables to lock the table of a database each time, this parameter is set to true by default (see the backup content instance above). -- lock-all-tables locks all tables at a time, which is applicable to the situations where dump tables are in different databases. generate a plain text file backup in a specific format. 1) Use SELECT... to outfile from... the command outputs specific data to a text file in a specified way through Query. Similar to the spool function parameter description in oracle, fields escaped by ['name'] refers to the characters to be ESCAPED in the SQL statement; fields terminated by sets the delimiter between each two FIELDS. FIELDS [OPTIONALLY] enclosed by 'name 'Wrapping', optionally numeric type is not packaged, otherwise it is fully packaged; lines terminated by 'name' line separator, that is, the characters added at the end of each record; backup instance: mysql> select * into outfile '/tmp/tb1.txt'-> fields terminated ', '-> optionally enclosed by' "'-> lines terminated by' \ n' -- default-> from tb1 limit 50; Query OK, 50 rows affected (0.00 sec) [root @ gc tmp] # more tb1.txt "information_schema", "CHARACTER_SETS", "system view" "information_schema", "COLLATIONS", "SYSTEM VIEW "...... 2) use the mysqldump tool command to export text. This method can generate a text data and a corresponding database structure creation script. The main parameters are-T, -- tab = name Create tab-separated textfile for each table to given path. (Create. SQL and. txt files .) NOTE: This only works if mysqldump is run on the same machine as the mysqld server. backup instance: export the tb1 table of the sqoop database # mysqldump-uroot-proot-T/tmp sqoop tb1 -- fields-enclosed-by = \ "-- fields-terminated-by =, [root @ gc tmp] # lstb1. SQL tb1.txt II, Logical Backup Recovery 1. INSERT statement file recovery 1) use the mysql command to directly restore the tb1 table of sqoop database to the test database # mysql-uroot-proot-D test </tmp/dumpback/sqoop_tb1. SQL 2) in the above example, you can use the following method to restore MySql from the command line: [root @ gc ~] # Mysql-uroot-proot-D testmysql> select database (); + ------------ + | database () | + ------------ + | test | + ------------ + 1 row in set (0.00 sec) mysql> source/tmp/dumpback/sqoop_tb1.sqlQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec )...... or mysql> \. /tmp/dumpback/sqoop_tb1. SQL 2. restore a plain text file 1) Use the load data infile command. This command is SELECT... to outfile from anti-operation, similar TO the sqlldr Syntax of oracle: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt '[REPLACE | IGNORE] into table tbl_name [FIELDS [terminated by 'string'] [[OPTIONALLY] enclosed by 'Char'] [ESCAPED BY 'Char '] [LINES [starting by 'string'] [terminated by 'string'] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...] recover an instance: mysql> use sqoop; Database changed mysql> load data infile '/tmp/tb1.txt 'Into table tb1-> fields terminated by ','-> optionally enclosed by '"'-> lines terminated by '\ n'; Query OK, 50 rows affected (0.01 sec) records: 50 Deleted: 0 Skipped: 0 Warnings: 0 2) use mysqlimport to restore the txt and SQL files generated by mysqldump, therefore, make sure that the tables in the database corresponding to the txt file exist. Restore an instance: -- first restore the table structure [root @ gc ~] # Mysql-uroot-proot-D test </tmp/tb1. SQL -- restore data [root @ gc ~] # Mysqlimport-uroot-proot test -- fields-enclosed-by = \ "-- fields-terminated-by =,/tmp/tb1.txttest. tb1: Records: 93 Deleted: 0 Skipped: 0 Warnings: 0