Specific implementation of MySQL backup and recovery (I)

Document directory

• Mysqldump Backup Tool

Cold backup:

The cold backup of the InnoDB Storage engine is very simple. You only need to back up frm files, shared tablespace files, independent tablespace files (*. IBD), and redo log files of the MySQL database. -- We recommend that you regularly back up the MySQL database configuration file my. CNF

 

Advantages of cold backup:

A) the backup is simple. You only need to copy the relevant files.

B) backup files can be easily recovered in different operating systems and MySQL versions.

C) recovery is quite simple. You only need to restore the file to the specified location.

D. fast recovery. No SQL statement or index reconstruction is required.

Disadvantages of cold backup:

A) InnoDB Storage Engine Cold backup files are usually much larger than logical files, because the tablespace stores data, such as Undo segments and insert buffer information.

B) Cold Standby cannot always be cross-platform. The operating system, MySQL version, file case sensitivity, and floating point format all become problems.

 

Logical backup:Mysqldump Backup Tool

The syntax of mysqldump is as follows:

Shell>Mysqldump [argument]> file_name

 

Back up all databases. Use the -- all-database option:

Shell>Mysqldump -- all-databases> All. SQL

Back up the specified database, -- database:

Shell>Mysqldump-database db1 DB2 db3> dump. SQL

 

Some important parameter options of mysqldump:

-- Single-transaction: run the starttransaction command before the backup starts to obtain the backup consistency.

-- Lock-tables (-l): In the backup, all tables under each architecture are locked. It is generally used in the MyISAM storage engine. Only the database can be read during backup, but the backup can still ensure consistency. InnoDB does not need to use this parameter. You can use -- Single-transaction. -- lock-tables and -- Single-transaction are mutually exclusive and cannot be used at the same time. If your MySQL database contains both MyISAM storage engine tables and InnoDB Storage engine tables, you can only choose -- lock-tables. The -- lock-tables option locks the tables in each architecture in sequence. Therefore, only the consistency of the backup tables in each architecture can be guaranteed, and the consistency of the tables in all architectures cannot be guaranteed.

-- Lock-all-tables (-x): locks all tables in all schemas during the backup process.

-- Add-drop-Database: Run drop database before createdatabase. This parameter must be used with the -- all-databases or -- database option.

 

Back up the specified table:

#Mysqldump -- Single-transaction -- socket =/var/run/mysqld/mysql5.socket test1 test1> test1. SQL

#Cattest1. SQL

-- MySQL dump 10.13 distrib 5.1.61, for Redhat-Linux-GNU (i386)

--

-- Host: localhost Database: test1

--------------------------------------------------------

-- Server version 5.5.20-ndb-7.2.5-log

 

/*! 40101 set @ old_character_set_client = @ character_set_client */;

/*! 40101 set @ old_character_set_results = @ character_set_results */;

/*! 40101 set @ old_collation_connection = @ collation_connection */;

/*! 40101 set names utf8 */;

/*! 40103 set @ old_time_zone = @ time_zone */;

/*! 40103 set time_zone = '+ '*/;

/*! 40014 set @ old_unique_checks =@ unique_checks, unique_checks = 0 */;

/*! 40014 set @ old_foreign_key_checks = @ foreign_key_checks, foreign_key_checks = 0 */;

/*! 40101 set @ old_ SQL _mode = @ SQL _mode, SQL _mode = 'no _ auto_value_on_zero '*/;

/*! 40111 set @ old_ SQL _notes = SQL _notes, SQL _notes = 0 */;

 

--

-- Table structure for table 'test1'

--

 

Drop table if exists 'test1 ';

/*! 40101 set @ saved_cs_client = @ character_set_client */;

/*! 40101 set character_set_client = utf8 */;

Create Table 'test1 '(

'Id' int (11) not null,

'Name' varchar (10) default null

) Engine = InnoDB default charset = Latin1;

/*! 40101 set character_set_client = @ saved_cs_client */;

 

--

-- Dumping data for table 'test1'

--

 

Lock tables 'test1' write;

/*! 40000 alter table 'test1' disable keys */;

Insert into 'test1' values (1, 'wu'), (2, 'terry '), (3, 'tang'), (4, 'jack '), (4, 'cat'), (3, null), (3, 'Dog ');

/*! 40000 alter table 'test1' enable keys */;

Unlock tables;

/*! 40103 set time_zone = @ old_time_zone */;

 

/*! 40101 set SQL _mode = @ old_ SQL _mode */;

/*! 40014 setforeign_key_checks = @ old_foreign_key_checks */;

/*! 40014 set unique_checks = @ old_unique_checks */;

/*! 40101 set character_set_client = @ old_character_set_client */;

/*! 40101 setcharacter_set_results = @ old_character_set_results */;

/*! 40101 setcollation_connection = @ old_collation_connection */;

/*! 40111 set SQL _notes = @ old_ SQL _notes */;

 

-- Dump completed on 2012-11-10 1:39:53

 

Select... Into OUTFILE

The Select... into statement is also a logical backup method, or, more accurately, exporting data in a table.

Select [Column 1], [column2]… Into OUTFILE 'file _ name' from table where... ...

Mysql> select * into OUTFILE '/root/test1.txt' from test1; // it must be a MySQL user that can write error 1 (hy000 ): can't create/write to file '/root/test1.txt' (errcode: 13) mysql> select * into OUTFILE '/home/MySQL/test1.txt' from test1; query OK, 7 rows affected (0.00 Sec) mysql> quitbye

#CAT/home/MySQL/test1.txT

1 Wu

2 Terry

3 Tang

4 Jack

4 cat

3 \ n

3 dog

 

#Mysql-e "select * into OUTFILE '/home/MySQL/test_bak' from test1.test1 ";

#CAT/home/MySQL/test_bak

1 Wu

2 Terry

3 Tang

4 Jack

4 cat

3 \ n

3 dog

 

Logical Backup recovery:

Mysqldump is easy to restore because the backup file is the exported SQL statement.

Import in shell command line:

#Mysqldump-uroot-p -- socket =/var/run/mysqld/mysql5.socket> test1. SQL test1

 

Use source to restore:

Mysql>Usetest1;

 

Mysql>Source/root/test1. SQL;

Query OK, 0 rows affected (0.00 Sec)

Query OK, 0 rows affected (0.00 Sec)

... ...

 

Note: Mysqldump can export stored procedures, triggers, events, and data, but cannot export views.

 

Load data infile

If the data exported through mysqldump -- tab or select into OUTFILE needs to be restored, the load data infile command must be used for import.

Load data [low_priority | concurrent] [local] infile 'file _ name'

[Replace | ignore]

Into Table tbl_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]

[(Col_name_or_user_var,...)]

[Set col_name = expr,...]

mysql> select * fromtest1;Empty set (0.00 sec)mysql> load datainfile '/home/mysql/test1.txt' into table test1;Query OK, 7 rows affected (0.05 sec)Records: 7  Deleted:0  Skipped: 0  Warnings: 0mysql> select * fromtest1;+----+-------+| id | name  |+----+-------+|  1 | wu    ||  2 | terry ||  3 | tang  |……7 rows in set (0.00 sec)mysql>

For faster import, you can ignore the foreign key check during the import process. You can use:

Mysqlimport

Mysqlimport is a command line program provided by the MySQL database. It is essentially a command interface for load data infile, and most of the options are the same as the load data infile syntax.

Shell>Mysqlimport [Options] db_name testfile1 [textfile2…]

 

Different from load data infile, The mysqlimport command can import multiple tables and-- User-ThreadParameters to concurrently import different files. Concurrency here refers to the concurrent import of multiple files, not to mention that mysqlimport can concurrently import a file, which is different, and concurrently import the same table, the effect is generally not better than the serial method.

Use mysqlimport to concurrently import two tables:

#/Usr/local/MySQL/bin/mysqlimport -- use-threads = 2-S/var/run/mysqld/mysql5.socket test1/home/MySQL/test1.txt/home/MySQL/test3.txt

Test1.test1: records: 7 deleted: 0 skipped: 0 Warnings: 0

Test1.test3: records: 7 deleted: 0 skipped: 0 Warnings: 0

Binary log backup and recovery

Binary logs are critical. We can use them to restore point-in-time data.

Enable binary log:

[Mysqld]

Bin-Log

Sync_binlog = 1

 

Sync_binlog

If it is positive, after each sync_binlog 'th writes the binary log, the MySQL server synchronizes its binary log to the hard disk (fdatasync ()). Note that in autocommit mode, every time a statement is executed, it is written to the binary log. Otherwise, each transaction is written once. The default value is 0, which is not synchronized with the hard disk. A value of 1 is the safest option, because when a crash occurs, you can discard at most one statement/transaction in the binary log. However, this is the slowest option (unless the hard disk has a battery backup cache, to make the synchronization work faster ).

 

Binary logs are stored in the databasedir directory by default, as shown below:Mysql5-bin.000001

Use mysqlbinlog to view the binary log Content:

#Mysqlbinlogmysql5-bin.000001

/*! 40019 set @ session. max_insert_delayed_threads = 0 */;

/*! 50003 set @ old_completion_type = @ completion_type, completion_type = 0 */;

Delimiter /*! */;

# At 4

#121110 15:40:06 server Id 1 end_log_pos 112 start: BINLOG V4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup

# Warning: This BINLOG is either in use or was not closed properly.

Rollback /*! */;

Binlog'

Bytes

Bytes

'/*! */;

... ...

 

MysqlbinlogThere are some options that can be used, a simple description of common options:

Ø-D, -- database = Name: Specifies the database name. Only operations of the specified database are listed.

Ø-D, -- disable-log-bin: Binary logs are forbidden when recovery is performed. This prevents endless loops when the same MySQL instance is added with-t.

Ø-O, -- offset = N: Ignore the first n lines of logs

Ø-R, -- result-file = Name: Output logs to a specified file

Ø-R, -- read-from-remote-Server: Read Binary data from a MySQL Server

Ø-S, -- short-form: Simple display format. Some information is omitted.

Ø-S, -- socket = Name: Socket file connection path.

Ø-T, -- to-last-Log: Used with-R. It does not stop when the binary log ends, but ends with the BINLOG generated at the end of the MySQL server, if both the output and input are on a MySQL instance, an endless loop may occur.

Ø-- Set-charset = char-name: In the output text format, add set names char-name to the first line.

Ø-- Start-datetime = # -- stop-datetime = #
: Specify the log output start date.

Ø-- Start-position = # -- stop-position = #: Specifies the location of the Start log.

 

# Mysqlbinlog-D test2mysql5-bin.000001

/*! 40019 set @ session. max_insert_delayed_threads = 0 */;

/*! 50003 set @ old_completion_type = @ completion_type, completion_type = 0 */;

Delimiter /*! */;

# At 4

#121110 15:40:06 server Id 1 end_log_pos 112 start: binlog v 4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup

# Warning: This BINLOG is either in use or was not closed properly.

Rollback /*! */;

Binlog'

Bytes

Bytes

'/*! */;

# At 112

#121110 16:07:51 server Id 1 end_log_pos 197 query thread_id = 3 exec_time = 0 error_code = 0

Set timestamp = 1352534871 /*! */;

Set @ session. pseudo do_thread_id = 3 /*! */;

Set @ session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 0, @ session. unique_checks = 1, @ session. autocommit = 1 /*! */;

Set @ session. SQL _mode = 0 /*! */;

Set @ session. auto_increment_increment = 1, @ session. auto_increment_offset = 1 /*! */;

/*! \ C Latin1 *//*! */;

Set @ session. character_set_client = 8, @ session. collation_connection = 8, @ session. collation_server = 8 /*! */;

Set @ session. lc_time_names = 0 /*! */;

Set @ session. collation_database = default /*! */;

Createdatabase Test2

/*! */;

... ...

 

#Mysqlbinlog-D test1 -- stop-position = 870 mysql5-bin.000001 | mysql-uroot-P

 

-- Start-datetime = # -- stop-datetime = #: Specify the log of the output start date.

-- Start-position = # -- stop-position = #: Specify the position of the Start log.

The preceding two options are similar: a date and a log location.

This article is for the author to take notes on the MySQL technology insider InnoDB engine.