How to Backup and Restore MySQL Database in Linux
MySQL database is the widely used server for storing data permanently for many of the purposes such as for storing data of a website. We can configure MySQL database on both types of Operating Systems Linux as well as on Windows. But the Configuration process and commands are slightly different for both the Operating Systems. So today here we only focus on Backup and Restoring Process of MySQL database on Linux Operating system.
MySQL has an inbuilt utility named as mysqldump for taking backups. If you are using a MySQL database server for any of the productive use, then it is necessary to take backups of your database. So we can easily recover from any type of crash and data loss. mysqldump is a database backup program that performs logical backups, produces a set of SQL statements that can be executed to reproduce the original database object definitions and table data. For more about mysqldump click on the above link.
Also Read: – Basic Facts about Linux Operating System
In this article, we will learn simple use of mysqldump utility such as taking database backups in the .sql format for archive format. Also, we will learn about various options for it. The mysqldump command can also generate output in CSV, XML, and other delimited text format.
Options for taking MySQL database Backup in Linux
We have many options for taking database backups using mysqldump utility. Here we use few options from them for creating backup files in the .sql format for more options you can read MySQL reference manual. Here we use database name “manisha” for creating backups as an example. So, let’s start here how this is done in Linux.
Note: – All the Commands are executed on the root user. If you use local user for this then simply add sudo in all the commands.
1. For Full MySQL Database backup in Plain .sql File
[[email protected] ~]# mysqldump -u root -p manisha > manisha.sql
2. For Full MySQL Database Backup in Compressed Archive .gz.sql
[[email protected] ~]# mysqldump -u root -p manisha | gzip > manisha.sql.gz
3. For taking Backup of Single Table Only
[[email protected] ~]# mysqldump -u root -p manisha tbl_student > tbl_student.sql
4. For taking Backup of MySQL Database Structure only (no data backup required)
[[email protected] ~]# mysqldump -u root -p --no-data manisha > manisha.sql
5. For taking Backup of Multiple MySQL Databases at once in a single file
[[email protected] ~]# mysqldump -u root -p --databases manisha annu jaya > manisha-annu-jaya.sql
6. For taking Backup of All MySQL Databases presents on a Server at once in a single file
[[email protected] ~]# mysqldump -u root -p --all-databases > all-databases-backup.sql
7. For taking Backup of MySQL Database Data only (no table structure backup required)
[[email protected] ~]# mysqldump -u root -p --no-create-info manisha > manisha.sql
8. For taking Backup of MySQL Database in XML format
[[email protected] ~]# mysqldump -u root -p --xml manisha > manisha.xml
9. For taking Backup of InnoDB tables
[[email protected] ~]# mysqldump --all-databases --master-data --single-transaction > all_databases.sql
10. For taking Backup of MySQL Database in Delimited-Text Format
[[email protected] ~]# mysqldump --tab=/var manisha
These are the few options for taking backups of MySQL databases. Now to restore a database backup in MySQL use given below process.
How to Restore MySQL Backup in Linux
Restoring of MySQL databases is quite easier and simpler than taking backups in Linux. Because for restoring MySQL database we use mysql command in it. For example, given below command restore all of the data from manisha.sql to manisha database. Here mansha.sql is the file that is created by us in the backup process.
[[email protected] ~]# mysql -u root -p manisha < manisha.sql
This is the process of taking backups and perform restores on a MySQL database server. If you have any queries regarding this then simply ask in the comment section. Also, provide feedback to us because your feedback is valuable for us and follow our blog for further updates. Also, share it with your friends.