Tutorial by Examples

Create a snapshot of a whole database: mysqldump [options] db_name > filename.sql Create a snapshot of multiple databases: mysqldump [options] --databases db_name1 db_name2 ... > filename.sql mysqldump [options] --all-databases > filename.sql Create a snapshot of one or more tables...

> mysqldump -u username -p [other options] Enter password: If you need to specify the password on the command line (e.g. in a script), you can add it after the -p option without a space: > mysqldump -u username -ppassword [other options] If you password contains spaces or special chara...

mysql [options] db_name < filename.sql Note that: db_name needs to be an existing database; your authenticated user has sufficient privileges to execute all the commands inside your filename.sql; The file extension .sql is fully a matter of style. Any extension would work. You cannot spe...

In order to use compression over the wire for a faster transfer, pass the --compress option to mysqldump. Example: mysqldump -h db.example.com -u username -p --compress dbname > dbname.sql Important: If you don't want to lock up the source db, you should also include --lock-tables=false. But ...

gunzip -c dbname.sql.gz | mysql dbname -u username -p Note: -c means write output to stdout.

If you wish to make a complete backup of a large MySql installation and do not have sufficient local storage, you can dump and compress it directly to an Amazon S3 bucket. It's also a good practice to do this without having the DB password as part of the command: mysqldump -u root -p --host=localho...

If you need to copy a database from one server to another, you have two options: Option 1: Store the dump file in the source server Copy the dump file to your destination server Load the dump file into your destination server On the source server: mysqldump [options] > dump.sql On th...

By default stored procedures and functions or not generated by mysqldump, you will need to add the parameter --routines (or -R): mysqldump -u username -p -R db_name > dump.sql When using --routines the creation and change time stamps are not maintained, instead you should dump and reload the ...