Backup/Restore MySQL database

Latest update on March 14, 2012 at 04:40 PM by Virginia Parsons .

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.