Difference between revisions of "Talk:Backup The MySQL Database"

Revision as of 21:20, 21 January 2014

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