MySQL Backup Recovery Summary

Source: Internet
Author: User
Tags mysql backup

MySQL 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

    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

  • -- Extended-insert = true | false

    By default,mysqldump
    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

  • -- 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 can be used --single-transaction

  • -- No-create-Info,-T

    Only export data without addingCREATE TABLE

  • -- 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
    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
    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.

Common table parameters:

mysqldump -u userName -p --default-character-set=utf8 --opt --extended-insert=false 
--triggers -R --hex-blob -x db_name > db_name.sql


mysqldump -u userName -p --default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction db_name > db_name.sql

Online backup

Parameters are as follows:

mysqldump -u userName -p --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 MASTER
Statement to specify the BINLOG location of the current backup. If you want to restore the file to the slave, you can use this method.




MySQL command:

Mysql-u username-P db_name <db_name. SQL

Source command:
Source db_name. SQL;
Log on to MySQL and select a database before using this command.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.