MySQL : Dumping Data in SQL Format with mysqldump

MySQL : Dumping Data in SQL Format with mysqldump

 

By default, mysqldump writes information as SQL statements to the standard output. You can save the output in a file:

shell> mysqldump [arguments] > file_name
  • To dump all databases, invoke mysqldump with the --all-databases option:

shell> mysqldump –all-databases > dump.sql

  • To dump only specific databases, name them on the command line and use the --databases option:

shell> mysqldump –databases db1 db2 db3 > dump.sql

 

The –databases option causes all names on the command line to be treated as database names.

Without this option, mysqldump treats the first name as a database name and those following as table names.

With –all-databases or –databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database. This ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came.

If you want to cause the dump file to force a drop of each database before recreating it, use the –add-drop-database option as well. In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

  • To dump a single database, name it on the command line:

shell> mysqldump –databases test > dump.sql

  • In the single-database case, it is permissible to omit the –databases option:

shell> mysqldump test > dump.sql

  • To dump only specific tables from a database, name them on the command line following the database name:

shell> mysqldump test t1 t3 t7 > dump.sql

 

Schedule database backup at midnight

0 0 * * * mysqldump –user=root –password=password –host=localhost “database_name” > home/backup/database_name.sql

 

mysqldump – MySQL Database Backup and restore program

Backup and Restore Mysql database table

quickly copy MySQL InnoDB database without using MySQL dump

 

Leave a Reply