backup/restore MySQL db using MYSQLDUMP (quick reference)
Whether you are using MySQL with MyISAM or InnoDB or other database engine it is possible to backup/restore database using MYSQLDUMP utility. Though if you have all tables in MyISAM format MYSQLHOTCOPY is faster option but in any case MYSQLDUMP is a handly tool if you have got a mix set of tables.
Commands are very quick simple and easy to execute and can be made automated by a PHP or other script/tool.
How to backup using mysqldump?:
Before you use any of commands you have to move to mysql/bin directory of your MySQL installation and MySQL server should be up and running.
To backup all MySQL database on a server:
mysqldump --user=[USER] --password=[PASSWORD] -A > C:/BACKUP_PATH/BACKUP_TIMESTAMP.SQL
To backup specific MySQL database on a server:
mysqldump --user=[USER] --password=[PASSWORD] [DATABASE] > C:/BACKUP_PATH/BACKUP_TIMESTAMP.SQL
To backup certain tables in a MySQL database:
mysqldump --user=[USER] --password=[PASSWORD] [DATABASE] --tables [TABLE_1] [TABLE_2] > C:/BACKUP_PATH/BACKUP_TIMESTAMP.SQL
How to restore database/tables which are backed-up using mysqldump?
Reach to mysql/bin directory on prompt and type in following command with correct backup file created with mysqldump utility and specify database to which you want dump-file SQL statements to be executed.
mysql --user=[USER] --password=[PASSWORD] [DATABASE] < C:/BACKUP_PATH/BACKUP_TIMESTAMP.SQL
So easy and simple just specify database/tables along with connection credentials and use ">" and "<" (in and out) signs in a correct way and you are done.
In my projects we have to deal with exporting system/meta data sync with production server on a regular basis and this seems to be easier than using utilities provided by GUI tools, be it HeidiSQL, PHPMySQL, SQLYog or anything else. Though it depends on project to project and person to person but many times when we have to restore big backup created with HeidiSQL or SQLYog I have seen programs/database exhausted such that increasing packet size won't help either. But mysqldump/mysql restore always been a piece of cake to walk with.
For complete list of parameters of mysqldump please refer: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
For mysqlhotcopy reference please refer: http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html