Work essay-mysql

1: How to retrieve MySQL data

Using repair table can retrieve most of the data, but there are still some error records (These records are in the copy data file when the record is read and write) in the repair is deleted directly. So, I realize that the way we do this backup is a hidden problem. Consulted the MySQL documentation and saw how two secure backups were recorded in the document: 1. Mysqlhotcopy This command locks the tables in the database and then copies the files to ensure the integrity of the data. Commands are used: Mysqlhotcopy-u root-p<rootpass> db1 DB2 ... dbn <output_dir> if you need to back up all the databases, you can add the--regexp= ". *" parameter. This command locks the table before copying the file and synchronizes the data to the data file to avoid copying to incomplete data files, which is the safest and quickest way to backup. Advantages: Fast speed disadvantage: can only be used in MYISAM format database; must have database access 2. Mysqldump This command is used to pour the database into SQL files, is very old command. But because the SQL file generated by this command has always been thought to support BLOB data is not very good, and our database mostly have blobs, so not like it. In fact, only now know, just add a simple parameter to solve the blob problem: Mysqldump-u root-p<rootpass>--hex-blob db1 > Db1.sql with the--hex-blob parameter, the BLOB data is converted to a text-like method in a SQL file that is similar to BASE64, without causing the SQL file format problem. After my testing, this method of backing up the BLOB data and restoring it back again can be completely correct. If you want to back up all the databases, you can add the--all-databases parameter. Advantages: can be used in any format database disadvantage: Slow, the output file is large; You must have access to the database for databases that do not use InnoDB, Mysqlhotcopy is the best approach; otherwise, mysqldump must be used. However, the backup method for copying files directly should be deprecated.

Work essay-mysql