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

Revision as of 21:19, 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