Recommended mysql database backup and Restoration Methods

Author/Translator: ye Jinrong (Email:); Source: http://imysql.cn/. the interpreter can be used for commercial purposes.

Date: 2006/10/01

This article discusses the MySQL backup and recovery mechanisms and how to maintain data tables, including the two most important table types:MyISAMAndInnodbThe MySQL version is 5.0.22.

Currently, MySQL supports the following free backup tools:mysqldump、mysqlhotcopyYou can also back up data using SQL Syntax:BACKUP TABLEOrSELECT 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.InnodbAll tables are stored in the same data file.ibdata1Medium (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

mysqldumpSQL-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.mysqldumpSome 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_optionsAnd 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.INSERTMethod, that is, to write all the values in a row. This can improve the insert efficiency, but maymax_allowed_packetThe 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 defaultlatin1If 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

  TellmysqldumpInINSERTThe 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 applicableMyISAMTable.

• -- Extended-insert = true | false

  By default,mysqldumpEnable--complete-insertIf you do not want to use it, use this option to set its valuefalseYou 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-transactionAnd--lock-tables.

• -- Lock-tables

  It and--lock-all-tablesSimilarly, it is used to lock the currently exported data table, rather than locking the tables in all databases at once. This option applies onlyMyISAMTable.InnodbTable can be used--single-transaction.

• -- No-create-info,-t

  Only export data without addingCREATE TABLEStatement.

• -- 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 enablesmysqldumpExport data quickly, and export data can be quickly imported back. This option is enabled by default, but can be used--skip-optDisable. Note: If you runmysqldumpNot Specified--quickOr--optThe 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 forcesmysqldumpRetrieve 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 submitsBEGINSQL statement,BEGINIt does not block any applications and ensures Database Consistency during export. It only applies to transaction tables, suchInnoDBAndBDB.
  This option and--lock-tablesThe options are mutually exclusive, becauseLOCK TABLESIt 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-triggersDisable it.

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

/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 dataInnodbTable:

/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

In addition, if you want to implement online backup, you can also use--master-dataParameters are as follows:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \--default-character-set=utf8 --opt --master-data=1 \--single-transaction --flush-logs db_name > db_name.sql

It only requests the lock table at the beginning, refresh the binlog, and then add it to the exported file.CHANGE MASTERStatement to specify the binlog location of the current backup. If you want to restore the file to the slave, you can use this method.

1.2 restore

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

• Direct usemysqlClient

  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,mysqlFunctions provided by the client, such:

  SOURCE /tmp/db_name.sql;

  The absolute path of the file must be specified and must bemysqldThe running user (such as nobody) has the permission to read files.

2. mysqlhotcopy2.1 backup

mysqlhotcopyIs a PERL program, initially written by Tim Bunce. It usesLOCK TABLES、FLUSH TABLESAndcpOrscpTo 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.mysqlhotcopyBackup onlyMyISAMAnd can only run inUnix-likeAndNetWareSystem.

mysqlhotcopySupports 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/tmproot #/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 viewmysqlhotcopyHelp:

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_nameDirectory permission.

2.2 restore

mysqlhotcopyThe entire database directory is backed up and can be directly copiedmysqldThe 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 TABLESyntax andmysqlhotcopyThe 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:FILEPermission to execute this SQL statement and the Directory/Tmp/db_name/Must bemysqldUsers can write and exported files cannot overwrite existing files to avoid security issues.

SELECT INTO OUTFILEData 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:FILEPermission to execute this SQL statement and file/Tmp/db_name/tbl_name.txtMust bemysqldUsers can write and exported files cannot overwrite existing files to avoid security issues.

3.2 recovery

UseBACKUP TABLEMethod to back up the file, you can runRESTORE TABLEStatement to restore the data table.
Example:

RESTORE TABLE FROM '/tmp/db_name/';

The permission requirements are similar to those described above.

UseSELECT INTO OUTFILEMethod to back up the file, you can runLOAD DATA INFILEStatement 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 addREPLACEKeyword to replace existing records or useIGNOREKeyword to ignore them.

4. Enable binlog)

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

EnablebinlogMust be restartedmysqld. First, disablemysqld, Openmy.cnf, Add the following lines:

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

Then startmysqldYou can. During runningbinlog.000001Andbinlog.indexThe preceding file ismysqldRecord all data update operations, followed by all filesbinlogAnd cannot be deleted easily. AboutbinlogFor more information, see the manual.

To back up data, run the SQL statementmysqldTerminate the currentbinlogYou 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.infoAndRelay-log.infoFile.

Backed upbinlogFiles can be used with tools provided by MySQLmysqlbinlogTo 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

SetmysqlbinlogThe output SQL statement is executed directly as input.

If you have an idle machine, use this method for backup. AsslaveThe 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 thatInnodbFor a type table, you also need to back up its log files, that isib_logfile*File. Because whenInnodbWhen 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. For important and BUSY systems, full backup may be required 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 adopt the master-slave replication mechanism (replication), InslaveBack 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, CHECK or maintain the MyISAM table optimize table, OPTIMIZE the MyISAM table analyze table, and ANALYZE the MyISAM TABLE

Of course, the above commands can start with toolsmyisamchk.

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

ALTER TABLE tbl_name ENGINE = Innodb;

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

Usually usedMyISAMYou can use the restoration method mentioned above to complete the table. If the index is broken, you can usemyisamchkTool to rebuild the index. ForInnodbThe table is not so straightforward, because it stores all the tables in a tablespace. HoweverInnodbThere 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. cnfFile, add the following parametersmysqldAutomatically Check log files at startup:

innodb_force_recovery = 4

For more information about this parameter, see the manual.

8. Summary

A proper backup policy is required to back up data. This is a small part of what DBAs do. Everything is difficult at the beginning. Let's start from now on!