MySQL database backup and restoration practices

Opening the cmd command line is definitely not a mysql command line. The first time I got it wrong, I was depressed for a long time and backed up the MySQL database command mysqldump-hhostname-uuser

Opening the cmd command line is definitely not a mysql command line. The first time I got it wrong, I was depressed for a long time and backed up the MySQL database command mysqldump-hhostname-uuser

Open the cmd command line. It must not be the mysql command line. The first time I was wrong in this place, I was depressed for a long time.

Command for backing up MySQL database

Mysqldump-hhostname-uusername-ppassword databasename> backupfile. SQL

For example:

1. cd D: \ ProgramFiles \ MySQL Server 5.5 \ bin

2. mysqldump-uroot-p123456-h 127.0.0.1 test> D:/test. SQL

3. note: there must be no space between the-p password and the 123456 password. After testing, it seems that you need to enter the password again. After the password is entered, the XX table cannot be found. To avoid errors, don't worry about it. It's okay to add spaces or the like. Don't just use spaces.

Back up the MySQL database in the format of a table with deletion
Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.

Mysqldump -- add-drop-table-uusername-ppassword databasename> backupfile. SQL

Directly compress and back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename | gzip> backupfile. SQL .gz [it is estimated that it should be in linux. I guess I did not try it]

Back up a MySQL database table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2> backupfile. SQL

Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword-databases databasename1 databasename2 databasename3> multibackupfile. SQL

Back up database structures only

Mysqldump-no-data-databases databasename1 databasename2 databasename3> structurebackupfile. SQL

Back up all databases on the server

Mysqldump-all-databases> all_backupfile. SQL

Command for restoring MySQL database

Mysql-hhostname-uusername-ppassword databasename <backupfile. SQL

For example:

Method 1:

1. Open window cmd

2. Switch to the bin in the mysql installation directory.

3. mysql-uroot-p123456-h 127.0.0.1 test

4. Note: If-h 127.0.0.1 is not written, the local machine is used by default. This database must exist during Backup; otherwise, data may not be imported.

Method 2:

1. Open mysql cmd

2. source d:/test. SQL

Restore a compressed MySQL database

Gunzip

Transfer database to new server

Mysqldump-uusername-ppassword databasename | mysql-host = *. *-C databasename