Backup/Restore MySQL database

December 2016

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.

Backup


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.

Restore your database


To restore a dump just launch the command:

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

Note that


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.

Related :

This document entitled « Backup/Restore MySQL database » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.