Exploring Different Backup options of ‘mysqldump’ Tool

Database 6 responses so far

The ‘mysqldump’ is a tool to back up a single database or a set of databases, and then move the backed up copies to a different server (can be SQL Server or MySQL server). This backup program creates a ‘dump’ of select databases to a preferred location. Typically, the process involves forming a group of SQL Statements to create and populate the tables of databases being backed up. You can use ‘mysqldump’ to create database backups in other formats, including CSV, other delimited text, or XML format.

The user performing the dump must have appropriate privileges for different objects, such as ‘SELECT’ and ‘LOCK TABLES’ (if you are not using the ‘–single-transaction’ option) privileges for tables, ‘SHOW VIEW’ for views. If backups are being performed on the server and you are using MyISAM tables, then you can speed up the process by using ‘mysqlhotcopy’.

In general, you can start ‘mysqldump’ using any of the following commands:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] –databases db_name …
shell> mysqldump [options] –all-databases

For backing up all databases, use ‘–databases’ and ‘–all-databases’ options. By default, ‘mysqldump’ does not work on the ‘INFORMATION_SCHEMA’ database. In case you need to back up this database, you need to specify it on the command line. When dumping ‘INFORMATION_SCHEMA’, you should use the ‘–skip-lock-tables’ option. This can be accomplished if you are running MySQL 5.1.38 or later. With MySQL 5.1.64, ‘mysqldump’ also backs up the ‘general_log’ or ‘slow_query_log’ tables.

Typically, ‘mysqldump’ is used to create a ‘.sql’ file with T-SQL statements, such as DROP table, CREATE table, and INSERT. You can restore your database from dump by executing the ‘.sql’ file. With this tool, you can restore a local database to a remote destination.

Using mysqldump to Backup MySQL Databases

Dumping a Single Database

Execute the following at the command line to back up a single MySQL database:

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Replace ‘[database_name]‘ with the name of the database to be dumped, ‘[root_password]‘ with the root password for MySQL, and ‘dumpfilename.sql’ with the name of the dump file. ‘dumpfilename.sql’ will contain commands for each table in your database.

Dumping Multiple Databases

‘mysqldump’ allows you to back up multiple databases simultaneously. To perform this task, you need to identify which databases you want to back up. In order to do this, you can use the ‘show databases’ command as follows:

# mysql -u root -ptmppassword

mysql> show databases;
For instance, to back up any two databases, you can run ‘mysqldump’ as follows:

# mysqldump -u root -p[root_password] –databases [database_name1] [database_name2] > dumpfilename.sql
You can also check whether the dump contains all the backups. To do so, do the following:

# grep -i “Current database:” [Full path of the dump]
– Current Database: `database_name1`
– Current Database: `database_name2`

The ‘mysqldump’ tool is an easy and reliable backup option for those who need to keep from facing data loss in times of database corruption and similar disasters.

The following two tabs change content below.


Adam Gorge is a researcher working on MySQL Database corruption issues. He has knowledge in MySQL recovery inbuilt utilities & Third Party MySQL database recovery tool.
  • http://www.spinxwebdesign.com/website-development-company-los-angeles/ Alan Smith

    As per my view you should upload video regarding your post so it will be more easier for developers to implement this procedure, but you have discussed on mysql very nicely.

  • Shon MX

    backup single database

    # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

    all database

    # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql

  • http://www.webdeveloperjobs.co.uk/ web developer jobs

    Have to agree with Alan, a video tutorial would be awesome.
    You could record it, then pay a couple of dollars to get it transcribed, which would save you writing out the whole post :)

  • http://www.sitehatchery.com Jason

    Great! I was just thinking about how to backup a website and database automatically to a remote location. Thanks for the information about the –all-databases option. You mentioned that it can be dumped to a remote destination, but didn’t give instructions for this. It’s a good starting point though. Thanks!

  • Helen

    I used a cool program, and no problems observed. I had to backup an entire section сoursework writing http://www.onlineessay.us/coursework/coursework-writing.html

  • Jordan Clockwin

    For fast and quality MySQL dump I use dbForge Studio. In few simple steps this tool will backup and restore MySQL databse. Learn more here: http://www.devart.com/dbforge/mysql/studio/mysql-backup.html