MySQL backup methods in Linux

Note that Ubuntu 11.04 MySQL 5.1 is used. Windows estimates are different .. Study for yourself !!

1. It is not recommended to use the BACKUP and RESTORE commands after logging on to MySQL. Later versions may be removed .. So stop using it!

2. Use the mysqldump command to back up data. Suppose there is a test table in firstdb. Then run

Mysqldump-uUERNAME-pPASSWORD-hHOSTNAME firstdb test> test. SQL

After logging on to mysql, run: drop table test;

Restore now: mysql-uUSRNAME-pPASSWORD-hHOSTNAME firstdb <test. SQL

In this way, the original test is restored ..

Of course, mysqldump has many parameters .. Man on your own!

3. Use select into for backup

Log on to mysql and run: select * from test into outfile "test. dat ";

Clear table: truncate test;

Recovery: load data infile "test. dat" into table test;

Of course, you can also use the mysqlimport command to replace load data .. Run in bash:

Mysqlimport-uUSERNAME-pPASSWROD-hHOSTNAME firstdb test. dat;

In this way, it can be restored ..

But pay attention to the permission issues in linux .. This is not explained .. The permission management user of mysql is a system user-mysql. For details, refer to related linux permission books!

4. Use the mysqlhotcopy command

The usage is similar to that of mysqlimport .. This is a perl script .. View help ..

5. Use a binary Update log file to restore data to the nearest position.

In this case, open the log-bin option and set it under/etc/mysql/my. cnf .. Using Regular Expressions, you can easily find the log-bin option and annotate it. Of course, you can add it yourself.

Then the mysqlbinlog XXXX-bin.XXX can be restored .. It should be stored in/var/log/mysql by default .. Use the find command ..

6. The last method is not mentioned .. Directly copy the entire database... Copy the command cp to another directory or file system .. Recovery is also easy...