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. 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 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