Mysql data backup and recovery methods

For mysql database backup or recovery, we often use Direct Copy database backup. For mysqldump database backup and mysqlhotcopy database backup, let's take a look.

Mysqldump command mode

The backup IP address is 192.168.1.100:

The Code is as follows:	Copy code
D:/APM/APMServ5.2.6/MySQL5.1/bin> Mysqldump-h 192.168.1.100-u root-p ucenter> d:/SQL/uc_20130306. SQL Enter password :****

Back up a table in a database:

The Code is as follows:	Copy code
Mysqldump-u root-p password dbname tablename> d:/test. SQL

Backup full database:

The Code is as follows:	Copy code
Mysqldump-u root-p password-all-databases> d:/test. SQL

Back up the dbname database structure:
Mysqldump-u root-p-d-add-drop-table dbname> d:/SQL/a. SQL
Note:-d has no data.-add-drop-table adds a drop table before each create statement.
Import database:

The Code is as follows:	Copy code
D:/APM/APMServ5.2.6/MySQL5.1/bin> Mysql-u root-p [dbname] <backup. SQL

Parameter description:

The Dbname parameter indicates the database name. This parameter is optional. You can specify the database name or not. If the database name is specified, the table under the database is restored.
If the database name is not specified, it indicates that a specific database is restored.

Mysqlhotcopy

It directly Copies files, so it is faster than mysqldump.

Data Locking can be automatically completed without shutting down the server during Backup.

Refresh the log so that the checkpoints of Backup files and log files can be synchronized.

Backup:Mysqlhotcopy supports copying multiple databases at a time and regular expression. The following are examples:

The Code is as follows:	Copy code
/Usr/local/mysql/bin/mysqlhotcopy-h = localhost-u = root-p = root Testdb/tmp (copy the database directory testdb to/tmp) /Usr/local/mysql/bin/mysqlhotcopy-h = localhost-u = root-p = root Testdb_1 testdb_2 testdb_n/tmp /Usr/local/mysql/bin/mysqlhotcopy-h = localhost-u = root-p = root Testdb./regex/tmp

 

Restore: mysqlhotcopy backs up the entire database directory. during use, you can directly copy it to the datadir (/usr/local/mysql/data/) directory specified by mysqld, pay attention to permission issues as follows:

The Code is as follows:	Copy code
Cp-rf testdb/usr/local/mysql/data/ Chown-R nobody: nobody/usr/local/mysql/data/(change the owner of the testdb directory to the mysqld running user)

Direct Replication

In windows:Directly copying is simple. We stopped the mysql database and copied it.

In linux:

① Find the location where the database files are stored. The suffixes of MySql files include *. MYD, *. MYI, and *. frm;

 

The Code is as follows:	Copy code
Find/-name *. MYD

② Enter the database storage file directory;

The Code is as follows:	Copy code
Cd/opt/lan mp/mysql-5.1.63/win/data/

③ Use the copy command to copy all the data;

The Code is as follows:	Copy code
Copy mysql/home

④ At this time, you only need to convert these database files into SQL files, which is also the focus of this Article. If you need to import the SQL files with commands, you can;

⑤ Create a new database named mysqlbak and paste the copied folder into the data folder of the new database;

The Code is as follows:	Copy code
Find/-name mysqlbak Cd/home/mysql Copy */www/wdlinux/mysql-5.1.63/var/mysqlbak/

⑥ Now we can see the data in the new database mysqlbak, which is the same as the data in the original database mysql;

7. Use commands to export database files.

The Code is as follows:	Copy code
Mysqldump-uroot-p mysqlbak>/home/mysql. SQL

The mysql. SQL file in the home folder is a database file that is easy to import.