Mysql database backup methods (1/3)

Backup mysql tutorial database tutorial command

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

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

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> allbackupfile. SQL

Command for restoring mysql database

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

Restore a compressed mysql database

Gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename

Transfer database to new server

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

Several common use cases:

1. Export the entire database

Mysqldump-u username-p Database Name> exported file name
Mysqldump-u root-p dataname> dataname. SQL

At this time, you will be prompted to enter the password of the root user name. after entering the password, the dataname database will be successfully backed up in the mysql/bin/directory.

2. Export a table

Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u root-p dataname users> dataname_users. SQL

3. Export a database structure

Mysqldump-u wcnc-p-d-add-drop-table smgp_apps tutorial _ wcnc> d: wcnc_db. SQL
-D no data-add-drop-table add a drop table before each create statement

1 2 3