Backup and Restore MySQL Database

April 30, 2016 by No Comments

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake. Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replication slave servers.

Backup Mysql database using ‘mysqldump’

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. For more complex data services you may need It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Following is the syntax for mysql database backup using mysqldump command.

mysqldump database_name > database_name.sql

The above command works, if mysql is set to access without password

Otherwise we need to add username and password with the above command. In future commands, I’m not using the user and password option for readability, by assuming mysql is set to access without password.

mysqldump -u'user_name' -p'password' database_name > database_name.sql

Backup all the databases in the server, you can use the following syntax

mysqldump --all-databases > all_db.sql

Backup database table structure only, without any data.

There are some tables whose size is very big so I wonder if I could only back up the tables’ structure (only their elements) but not their data. And later take San Diego used cubicles and the backup of that big table only. You can use the following command to take the structure only for the database.

mysqldump -d database_name > database_name_structure.sql

Backup specific table from the database, use the following one.

mysqldump da_name table_name > db_table_name.sql

Restore MySQL database from SQL backup

We can restore the mysqldump backup using the mysql command.

mysql db_name < db_name.sql
mysql -u'user' -p'password' db_name < db_name.sql