This article uses examples to describe how to back up and restore MySQL data. Share with you for your reference, the details are as follows:

In this article:

• Copy File Law
• Using mysqldump
• Using select into outfile
• Others (listed but not introduced)

Release date: 2018-04-19

Sometimes, you need to flush tables before backing up to ensure that all data is written to disk.

Copy file method:

• For the database of the myisam storage engine, its table structure (.frm), data (.myd) and index (.myi) are all separate files. You can directly copy these three files to the backup space to successfully back up.
• As for restoration, you only need to copy the three files back to a database directory.

Using mysqldump:

Applicable situations: It essentially restores the table structure and data into SQL statements, so the table structure can be backed up

Backup:

• Single table backup: mysqldump -u username -p database name table name > backup file path
• Multi-table backup: mysqldump -u username -p database name table name 1 [other table names] > backup file path
• Single database backup: mysqldump -u username -p database name > backup file path
• Backup multiple databases: mysqldump -u username -p --databases database name 1 database name 2 [other databases] > backup file path
• Back up all databases: mysqldump -u username -p --all --databases > backup file path

reduction:

• mysqldump -u username -p password [database name] < backup file path [When a database is specified, it means only restoring the backup of the specified database. Not specifying it means restoring all the contents in the backup file]

Replenish:

• mysqldump can also export only table data [but considering that there is select into outfile, it will not be introduced here]

Using select into outfile:

Applicable situations: Single table data backup, only the table data can be copied, not the table structure.

Backup:

• Syntax: select *\field list from data source into outfile backup file target path [fields field processing] [lines line processing]; [The file in the backup file target path must not exist]
  • Fields processing is optional and is how the data of each field is stored. There are the following options
    • enclosed by: What character is used to enclose the data? The default is an empty character.
    • terminated by: What character is used as the end of the field data? The default is \t, the backspace character.
    • escaped by: How to handle special symbols, the default is "\\"
    • optionally enclosed by: What character is used to enclose data whose data type is a string.
    • grammar:

      select * from class into outfile "d:\c1.txt" fields enclosed by '-';
      select * from class into outfile "d:\c1.txt" fields enclosed by '-' optionally enclosed by "'";

  • lines processing is optional
    • starting by: what each line starts with, the default is an empty string
    • terminated by: What each line of record ends with, the default is "\n"
    • grammar:

      select * from class into outfile "d:\c.txt" lines starting by 'start:';
      select * from class into outfile "d:\c1.txt" lines starting by 'start:' terminated by ' \n';

Note: A new feature, secure_file_priv, was added in 5.7 to restrict data export. The secure-file-priv parameter is used to restrict the directory to which LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() are transferred.

Need to be set in my.ini To export data normally.

reduction:

• Syntax: load date infile backup file path into table table name [fields field processing] [lines line processing]; [Write down the previous field processing and line processing, otherwise the data cannot be distinguished correctly]

other

• Backup:
  • Use mysql command to backup
  • Backup using mysqlhotcopy
  • Using backup table
• reduction:
  • Use mysqlimport to import the data file.
  • Restore using restore
  • Restore using source

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.