Shulou(Shulou.com)06/01 Report--

This article mainly explains mysql how to achieve data backup and recovery, the content is clear, interested friends can learn, I believe that after reading it will be helpful.

The content of this article is:

Copy file method using mysqldump using select into outfile other (enumerated but not introduced)

Launch date: 2018-04-19

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

Copy file method: for the database of myisam storage engine, its table structure (.frm), data (.myd) and index (.myi) are all separate files. You can copy these three files directly to the backup space to back up successfully. As for restore, you only need to copy three files back to a database directory. Take advantage of mysqldump:

Application: essentially restore the table structure and data to sql statements, so you can back up the table structure

Backup: single table backup: mysqldump-u user name-p database name table name > backup file path multi-table backup: mysqldump-u user name-p database name table name 1 [other table name] > backup file path single database backup: mysqldump-u user name-p database name > backup file path multiple database backup: mysqldump-u user name-p-- databases database name 1 Back up all databases according to library name 2 [other databases] > backup file path: mysqldump-u user name-p-all-- databases > backup file path restore: mysqldump-u user name-p password [database name] < backup file path [when you specify a database, it means to restore only the backup of the specified database. Do not specify to restore everything in the backup file] add: mysqldump can also export only table data (but considering that there is select into outfile, it will not be introduced here] use select into outfile:

Application: single table data backup, can only copy table data, can not copy table structure.

Backup: syntax: select *\ Field list from data source into outfile backup file destination path [fields field processing] [lines line processing] [the file must not exist in the destination path of the backup file] the fields field processing is optional, which is how the data of each field is stored. There are the following small options: enclosed by: what character to wrap the data with. The default is empty character terminated by: what character should be used as the end of the field data. The default is\ t. Backspace character escaped by: how to handle special symbols. The default is "\" optionally enclosed by: what character is used to wrap data with a string data type. Syntax: 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 line processing is optional starting by: what each line begins with, the default is the empty string terminated by: what each line ends with, the default is "\ n" syntax: 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, is added in 5.7. it restricts the export of data, and the secure-file-priv parameter is used to restrict LOAD DATA, SELECT. OUTFILE, and LOAD_FILE () to which specified directory.

It needs to be set up in my.ini to export data properly.

Restore: 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 you can't correctly distinguish the data] other backups: use mysql command backup to use mysqlhotcopy backup to use backup table restore: use mysqlimport to import data files. Use restore restore to use source restore to read the above content, do you have a further understanding of how mysql can achieve data backup and recovery? if you want to learn more, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.