MySQL database backup and recovery

Document directory

• 1. mysqldump
• 2. mysqlhotcopy
• 3. SQL syntax backup
• 4. Enable BINLOG)
• 5. Back up data files directly
• 6. Backup Policy
• 7. Data Maintenance and disaster recovery

MySQL, mysqldump, export, export table, compress and export MySQL, mysqlhotcopy, and MySQL restore table

This article discusses the MySQL backup and recovery mechanisms and how to maintain data tables, including the two most important table types:MyISAM

AndInnodb

The MySQL version is 5.0.22.

Currently, MySQL supports the following free backup tools:mysqldump、mysqlhotcopy

You can also back up data using SQL Syntax:BACKUP TABLE

OrSELECT INTO OUTFILE

, Or backupBinary log)

Can also beDirectly copy data files and related configuration files

. MyISAM tables are saved as files, so they are relatively easy to back up. The methods mentioned above can be used.Innodb

All tables are stored in the same data file.ibdata1

Medium (or multiple files, or independent tablespace files), it is relatively difficult to back up data. The free solution can beCopy data files

,Back up BINLOG

, Or usemysqldump

.

1. mysqldump1.1 backup

mysqldump

SQL-level backup mechanism is used to export data tables into SQL script files, which is suitable for upgrading between different MySQL versions. This is also the most common backup method. Let's talk about it now.mysqldump

Some of the main parameters:

• -- Compatible = Name

  It tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The value can beansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options

  And so on. Use commas to separate them. Of course, it does not guarantee full compatibility, but is as compatible as possible.

• -- Complete-insert,-C

  The exported data is complete with the field name.INSERT

  Method, that is, to write all the values in a row. This can improve the insert efficiency, but maymax_allowed_packet

  The insertion fails due to the influence of parameters. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.

• -- Default-character-set = charset

  Specifies the character set used for data export. If the data table does not use the defaultlatin1

  If the character set is used, this option must be specified during the export. Otherwise, garbled characters will occur after the data is imported again.

• -- Disable-keys

  Tellmysqldump

  InINSERT

  The beginning and end of the statement are added./*!40000 ALTER TABLE table DISABLE KEYS */;

  And/*!40000 ALTER TABLE table ENABLE KEYS */;

  Statement, which can greatly improve the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicableMyISAM

  Table.

• -- Extended-insert = true | false

  By default,mysqldump

  Enable--complete-insert

  If you do not want to use it, use this option to set its valuefalse

  You can.

• -- Hex-blob

  Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include:BINARY、VARBINARY、BLOB

  .

• -- Lock-all-tables,-x

  Before starting the export, submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and is automatically disabled.--single-transaction

  And--lock-tables

  .

• -- Lock-tables

  It and--lock-all-tables

  Similarly, it is used to lock the currently exported data table, rather than locking the tables in all databases at once. This option applies onlyMyISAM

  Table.Innodb

  Table can be used--single-transaction

  .

• -- No-create-Info,-T

  Only export data without addingCREATE TABLE

  Statement.

• -- No-data,-d

  Only the database table structure is exported without exporting any data.

• -- OPT

  This is just a quick option, equivalent to adding--add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset

  . This option enablesmysqldump

  Export data quickly, and export data can be quickly imported back. This option is enabled by default, but can be used--skip-opt

  Disable. Note: If you runmysqldump

  Not Specified--quick

  Or--opt

  The entire result set is stored in the memory. If you export a large database, problems may occur.

• -- Quick,-Q

  This option is useful when exporting large tables. It forcesmysqldump

  Retrieve records from the Server query directly, instead of getting all records, and then cache them to the memory.

• -- Routines,-R

  Export stored procedures and user-defined functions.

• -- Single-transaction

  This option submitsBEGIN

  SQL statement,BEGIN

  It does not block any applications and ensures Database Consistency during export. It only applies to transaction tables, suchInnoDB

  AndBDB

  . This option and--lock-tables

  The options are mutually exclusive, becauseLOCK TABLES

  It causes any pending transactions to be committed implicitly. To export a large table, use--quick

  .

• -- Triggers

  Export the trigger at the same time. This option is enabled by default.--skip-triggers

  Disable it.

For more information about other parameters, see the manual. I usually use the following SQL to back up data.MyISAM

Table:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr --default-character-set=utf8 --opt --extended-insert=false /
--triggers -R --hex-blob -x db_name > db_name.sql

Use the following SQL to back up dataInnodb

Table:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr --default-character-set=utf8 --opt --extended-insert=false /
--triggers -R --hex-blob --single-transaction db_name > db_name.sql

 

1.2 restore

Usemysqldump

The backed up file is an SQL script that can be directly imported. There are two ways to import data.

• Direct usemysql

  Client

  For example:

  /usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql

   

• Use source syntax

  In fact, this is not a standard SQL syntax,mysql

  Functions provided by the client, such:

  SOURCE /tmp/db_name.sql;

  The absolute path of the file must be specified and must bemysqld

  The running user (such as nobody) has the permission to read files.

   

2. mysqlhotcopy2.1 backup

mysqlhotcopy

Is a Perl program, initially written by Tim Bunce. It usesLOCK TABLES、FLUSH TABLES

Andcp

Orscp

To quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database file (including the data table definition file, data file, and index file) is located.mysqlhotcopy

Backup onlyMyISAM

And can only run inUnix-like

AndNetWare

System.

mysqlhotcopy

Supports one-time copying of multiple databases and regular expression. The following are examples:

Root #/usr/local/MySQL/bin/mysqlhotcopy-H = localhost-u = yejr-P = yejr db_name/tmp (set the database directoryDb_nameCopy/TmpUnder)
Root #/usr/local/MySQL/bin/mysqlhotcopy-H = localhost-u = yejr-P = yejr db_name_1... db_name_n/tmp
Root #/usr/local/MySQL/bin/mysqlhotcopy-H = localhost-u = yejr-P = yejr db_name./RegEx/tmp

For more detailed usage, refer to the manual or call the following command to viewmysqlhotcopy

Help:

perldoc /usr/local/mysql/bin/mysqlhotcopy

Note that you want to usemysqlhotcopy

, Must haveSelect and reload (to execute flush tables)

And must be able to readDatadir/db_name

Directory permission.

 

2.2 restore

mysqlhotcopy

The entire database directory is backed up and can be directly copiedmysqld

The specifiedDatadir

(Here is/Usr/local/MySQL/data/

) Directory, and pay attention to permission issues, as shown in the following example:

Root # cp-RF db_name/usr/local/MySQL/data/
Root # chown-r nobody: Nobody/usr/local/MySQL/data/(change the owner of the db_name directorymysqldRunning user)

 

3. SQL syntax backup 3.1 backup

BACKUP TABLE

Syntax andmysqlhotcopy

The working principle is almost the same. They all lock tables and then copy data files. It can achieve online backup, but the effect is not ideal, so it is not recommended. It only copies table structure files and data files, and does not copy index files at the same time, So recovery is slow. Example:

BACK TABLE tbl_name TO '/tmp/db_name/';

Note:FILE

Permission to execute this SQL statement and the Directory/Tmp/db_name/

Must bemysqld

Users can write and exported files cannot overwrite existing files to avoid security issues.

 

SELECT INTO OUTFILE

Data is exported to common text files. You can customize the field interval to process the data conveniently. Example:

SELECT INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;

Note:FILE

Permission to execute this SQL statement and file/Tmp/db_name/tbl_name.txt

Must bemysqld

Users can write and exported files cannot overwrite existing files to avoid security issues.

 

3.2 recovery

UseBACKUP TABLE

Method to back up the file, you can runRESTORE TABLE

Statement to restore the data table. Example:

RESTORE TABLE FROM '/tmp/db_name/';

The permission requirements are similar to those described above.

 

UseSELECT INTO OUTFILE

Method to back up the file, you can runLOAD DATA INFILE

Statement to restore the data table. Example:

LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;

The permission requirements are similar to those described above. Before you import data, the data table already exists. If you are worried that the data will be duplicated, you can addREPLACE

Keyword to replace existing records or useIGNORE

Keyword to ignore them.

 

Supplement:

shell> mysqldump --quick db_name | gzip > db_name.contents.gz

(
The file created in this example is in the compressed format ).

 

The command for restoring/transferring to another instance is as follows:

shell> gunzip < db_name.contents.gz | mysql db_name

The preceding command is applicable to * NIX operating system machines.

 

4. Enable BINLOG)

Usebinlog

This method is more flexible and cost-effective, and supports Incremental backup.

Enablebinlog

Must be restartedmysqld

. First, disablemysqld

, Openmy.cnf

, Add the following lines:

server-id = 1
log-bin  = binlog
log-bin-index = binlog.index

Then startmysqld

You can. During runningbinlog.000001

Andbinlog.index

The preceding file ismysqld

Record all data update operations, followed by all filesbinlog

And cannot be deleted easily. Aboutbinlog

For more information, see the manual.

 

To back up data, run the SQL statementmysqld

Terminate the currentbinlog

You can back up the file directly, so that you can achieve the purpose of Incremental Backup:

FLUSH LOGS;

If it is a slave server in the backup and replication system, you should also back upMaster.info

AndRelay-log.info

File.

 

Backed upbinlog

Files can be used with tools provided by MySQLmysqlbinlog

To view, such:

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001

This tool allows you to display all the SQL statements in the specified database, and you can also limit the time range, which is quite convenient. For details, please refer to the manual.

 

You can use a statement similar to the following to restore data:

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name

Setmysqlbinlog

The output SQL statement is executed directly as input.

 

If you have an idle machine, use this method for backup. Asslave

The performance requirements of machines are not that high, so the cost is low. at a low cost, Incremental Backup can be achieved and the Data Query pressure can be shared. Why not?

5. Back up data files directly

Compared with the previous methods, backing up data files is the most direct, fast, and convenient. The disadvantage is that Incremental backup is basically not supported. To ensure data consistency, run the following SQL statement before the back-to-back file:

FLUSH TABLES WITH READ LOCK;

That is, the data in the memory is refreshed to the disk, and the data table is locked to ensure that no new data is written during the copy process. In this way, the backup data recovery is also very simple, just copy it back to the original database directory.

 

Note thatInnodb

For a type table, you also need to back up its log files, that isib_logfile*

File. Because whenInnodb

When the table is damaged, you can use these log files to restore the table.

6. Backup Policy

For systems with medium business volume, the backup policy can be set as follows: the first full backup, one Incremental Backup every day, and one full backup every week. And for important and busy
For the system, you may need to perform full backup once a day, Incremental backup once an hour, or even more frequently. To achieve online backup and Incremental Backup without affecting online services, the best way is to use Master/Slave
Replication mechanism (replication

), Inslave

Back up data on the machine.

7. Data Maintenance and disaster recovery

As a DBA (I am not yet, haha), one of the most important tasks is to ensure the security, stability, and high-speed use of data tables. Therefore, you must maintain your data tables on a regular basis. The following SQL statements are useful:

Check table or repair table to check or maintain the MyISAM table
Optimize the MyISAM table
Analyze the MyISAM table

Of course, the above commands can start with toolsmyisamchk

.

 

Innodb

You can execute the following statements to sort the table shards to improve the index speed:

ALTER TABLE tbl_name ENGINE = Innodb;

This is actuallyNULL

Operation. On the surface, nothing is done. Actually, the fragments are reorganized.

 

Usually usedMyISAM

You can use the restoration method mentioned above to complete the table. If the index is broken, you can usemyisamchk

Tool to rebuild the index. ForInnodb

The table is not so straightforward, because it stores all the tables in a tablespace. HoweverInnodb

There is a checking mechanism calledFuzzy checkpoint

, As long as the log file is saved, the error can be fixed based on the log file. You canMy. CNF

File, add the following parametersmysqld

Automatically Check log files at startup:

innodb_force_recovery = 4

For more information about this parameter, see the manual.