Backup/Restore MySQL database

MySQL, a relational database management system (RDBMS), runs as a server providing access to databases for multiple users. It uses specialized programming languages like C and C++. SQL is the programming language used to communicate with MySQL. MySQL databases are used to save extensive amount of significant data and it becomes imperative to backup your data. Database backups are also used to transfer your database from one server to another in case of a change in web hosts. MySQLDump is a utility used to backup or restore the MySQL database to the text files. With the MySQLDump, using a single command you can backup a local database and restore it at the same time on a remote database.

A simple approach on how to backup and restore MySQL databases.
To save an existing database it is recommended that you create a dump.

  • To dump all databases you must run the command:
mysqldump --user=****** --password=****** -A > /path/to/file_dump.SQL   
  • To dump several specific databases you must run the command:
mysqldump --user=****** --password=******  db_1 db_2 db_n> /path/to/file_dump.SQL
  • To dump all tables from a database you must run the command:
mysqldump --user=****** --password=****** db > /path/to/file_dump.SQL
  • To dump specific tables from a database you must run the command:
mysqldump --user=****** --password=****** db --tables tab1 tab2 > /path/to/file_dump.SQL

For each of the following commands you must specify a user (user) and password (password) with administrator rights on the database.
To restore a dump just launch the command:

mysql --user=****** --password=****** db_name < /path/to/file_dump.SQL

A database dump is a record of the table structure and the data from a database, usually in the form of a list made of SQL statements.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Backup/Restore MySQL database », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (