MySQL notes: Backup and restore

First, backup

The mysqldump command backs up the data as text and the text content is the statement executed at the time of the restore.

The Mysqlhotcopy command is intended for fast backups that do not stop the server, and it works by adding a read operation lock to the database that needs to be backed up, then writing the data in memory back to the hard disk and finally copying the database files that need to be backed up to the destination directory. (Linux system)

If you choose to replicate the backup database directory directly, you must ensure that the data table engine is the MYISAM,INNODB table space cannot be copied directly.

- - User > Backup  ----databases MySQL world > Backup.sql #备份多个数据库 --  --all-databases > Backup.sql #备份所有数据库

View Code

Second, restore

Backupbackup.sql

View Code

Note: You can implement a database migration through a backup restore.

Third, export the table

SHOW VARIABLES like '%secure%'; #查看secure-file-priv Default ValueSELECT * fromMysql.User intoOUTFILE'C:/backup/outfile.txt'Fields TERMINATED by '\,'#字段使用, separated optionally enclosed by '\"'#字符型数据使用 "including lines starting by '\>'#记录以>opening TERMINATED by '\n\r'; #Windows默认回车换行mysqldump-U root-P-T C:/BackupMySQL "--fields-terminated-by=, "--fields-optionally-enclosed-by= " "MySQL-U root-P-ESELECT *  from User"MySQL>C:/Backup/Outfile.txt

View Code

Note: You can modify the default value of Secure-file-priv by modifying the My.ini file, and you must restart MySQL after you modify it.

Iv. Export Table

LOADDATA INFILE'C:/backup/outfile.txt'  into TABLEMysql.UserFields TERMINATED by '\,'optionally enclosed by '\"'LINES Starting by '\>'TERMINATED by '\n\r'; Mysqlimport-U root-P MySQL C:/Backup/Outfile.txt "--fields-terminated-by=, "--fields-optionally-enclosed-by= " "#非法代码和指令, only for demo use

View Code

MySQL notes: Backup and restore