Mysql database backup command -- mysqldump

mysqldump explain

Default mysqldump Derived SQL In file , It not only contains the exported data , It also includes the export of all data tables in the database structural information .
in addition , Use mysqldump Derived SQL If the file does not have an absolute path , The default is to save in bin In the catalog .

1、mysql Configuration information

Database address ：127.0.0.1
Database user name ：root
Database password ：pass
Database name ：myweb

2、 Backup

 Back up the database to D Disk and directory 

mysqldump -h127.0.0.1 -uroot -ppass myweb > d:/backupfile.sql

 Back up to the current directory   Backup MySQL The database is in the form of deleted tables , The backup can overwrite the existing database without manually deleting the original database 

mysqldump --add-drop-table -h127.0.0.1 -uroot -ppass myweb > backupfile.sql

 Direct will MySQL Database compression backup    Backup to D Disk and directory 

mysqldump -h127.0.0.1 -uroot -ppass myweb | gzip > d:/backupfile.sql.gz

 Backup MySQL A database ( some ) surface . This example backup table1 Table and table2 surface . Backup to linux The host /home Next 
mysqldump -h127.0.0.1 -uroot -ppass myweb table1 table2 > /home/backupfile.sql

 Backup multiple files at the same time MySQL database 

mysqldump -h127.0.0.1 -uroot -ppass --databases myweb myweb2 > multibackupfile.sql

 Just back up the database structure . At the same time, the backup name is myweb Database and name myweb2 database 

mysqldump --no-data -h127.0.0.1 -uroot -ppass --databases myweb myweb2 > structurebackupfile.sql

 Back up all databases on the server 

mysqldump --all-databases -h127.0.0.1 -uroot -ppass > allbackupfile.sql

3、 Restore

 Restore MySQL Database commands . Restore the current backup named backupfile.sql The database of 

mysql -h127.0.0.1 -uroot -ppass myweb < backupfile.sql

 Log in to mysql database 
 then , Carry out by  source  Command execution recovery 

source /backup/backupfile.sql

 Restore compressed MySQL database 

gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot -ppass myweb

 Move the database to the new server .
 This example is to the local database myweb Copy to the remote database named serweb in ,
 among , The remote database must be named serweb The database of , It should be created in advance 

mysqldump -h127.0.0.1 -uroot -ppass myweb | mysql --host=***.***.***.*** -u Database user name  -p Database password  -C serweb