Talk:Backup The MySQL Database
EXAMPLES
The most normal use of mysqldump is probably for making a backup
of whole databases. See Mysql Manual section 21.2 Database Back-
ups.
mysqldump --opt database > backup-file.sql
You can read this back into MySQL with:
mysql database < backup-file.sql
or
mysql -e 'source /patch-to-backup/backup-file.sql' database
However, it's also very useful to populate another MySQL server
with information from a database:
mysqldump --opt database | mysql --host=remote-host -C database
It is possible to dump several databases with one command:
mysqldump --databases database1 [ database2 database3... ] >
my_databases.sql
If all the databases are wanted, one can use:
mysqldump --all-databases > all_databases.sql
From command line, Back-up to SQL file:
mysqldump -u username -p --lock-tables DB1 > database-backup.sql
for multiple databases
mysqldump -u username -p --lock-tables --databases DB1 DB2 DB3 ... > database-backup.sql
for all databases
mysqldump -u username -p --lock-tables --all-databases > database-backup.sql
use of "--lock-tables" to prevent database access while dumping.
to import a database :
mysql -u username -p DB1 < database-backup.sql
for multiple databases:
mysql -u username -p < database-backup.sql
'table does not exist' error
I had the same issue after copying a database directory directly (e.g. cp -r /path/to/my/database /var/lib/mysql/new_database). If you do this with a database that uses InnoDB tables, you will get this crazy 'table does not exist' error mentioned above. The issue is that you need the ib* files in the root of the mysql datadir (e.g. ibdata1, ib_logfile0 ib_logfile1).
Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file. http://stackoverflow.com/questions/5745221/import-frm-and-opt-files-to-mysql
