Mysql Backup, Recovery

Core tools: mysqldump

Local Backup table structure

Mysqldump-uroot-ppassword--no-data--databases database1 database2 database3> filename

Local BACKUP Database:

Mysqldump-uroot-ppassword databasename > FileName

Back up all databases locally

Mysqldump-uroot-ppassword--all-databases > FileName

Cross-server Backup (host2 first create an empty targedb):

Mysqldump--host=host1-uroot-ppassword--opt Sourcedb | MySQL--host=host2-uroot-ppassword-c targetdb

Database recovery:

MySQL DatabaseName < filename

Note Several parameters:

--add-drop-table generated backup file with drop table IF EXIST XXX Table

--skip-add-drop-table generated backup file will not have drop table IF EXIST XXX Table

--opt generated backup file with DROP TABLE ... With lock TABLES UNLOCK

-C Compressed Transport

Specific parameters can be mysqldump--help view

Recommendation: Backup files are a good habit to name a backup point in time

Mysql Backup, Recovery