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.
Setting up your first Azure Virtual Machine can be done by following these steps: Create…
Amazon CloudFront is a content delivery network (CDN) that helps you serve static content such…
Step-By-Step Guide To Setting Up An AWS Application Load Balancer Are you looking for a…
MySQL databases often get corrupted due to issues like hardware failure, file system failure etc.…
SQL Server Replication is the process of copying databases from one node to another to…
Here are 101 System Admin tools which make System Admins' life easy.
This website uses cookies.