Backups are very important to prevent data loss. Imagine, you have stored lots of important data in a database and the database server is running in a secure environment. You might think your data is safe because of the high security measures you have followed. What happens if the database gets corrupted and you have never felt the necessity to take regular backups. The result is all your important data is lost. It is thus important to keep regular backups of the data. Luckily, MySQL server has a great and a very simple tool called mysqldump to backup your databases.
The mysqldump tool has to be run from the command line. For example, if you want to backup an entire MySQL Database backup.
mysqldump -u [user] -p[password] [database] > [backupfile.sql]
[user] The MySQL database user
[password] The MySQL database password
[database] The MySQL database which you will backup
[backupfile.sql] The name of the file that will contain the database backup
Please note that the square brackets are for example purpose. They are not to be used in the actual command
You can also backup only selected tables from a MySQL database. Use the following command to backup the tables. Each table name has to be separated by space.
mysqldump -u [user] -p[password] [database] table1 table2 > [backupfile.sql]
Similarly, you can exclude certain tables while performing a database backup
mysqldump -u [user] -p[password] [database] –ignore-table=table1 –ignore-table=table2 > [backupfile.sql]
Sometimes, you may want to backup multiple databases. Instead of executing the mysqldump command for each database separately, you can backup multiple databases in a single command
mysqldump -u root -p[password] –databases database1 database2 database3 > backupfile.sql
If you want to back up all the databases in the server at one time you should use the –all-databases option.
mysqldump -u root -p[password] –all-databases > alldb_backup.sql
Please note that you have to use the root user if you want to backup multiple databases or all databases.
If you have large databases, then you can back up the databases in compressed format to save storage.
mysqldump -u [user] -p[password] [database] | gzip -9 > [backupfile.sql.gz]
To extract the file you can use the gunzip command.
How to Restore MySQL Database?
Before restoring database from the backup file, create the database if not already created.
Now execute the following command to restore the MySQL database from the backup file.
mysql -u [user] -p[password] [database] < [backupfile.sql]
To restore database from compressed backup files you can do the following
gunzip < [backupfile.sql.gz] | mysql -u [user] -p[password] [database]
Also visit my next post to know how to back up MySQL databases and upload it to a remote server via FTP.