In this post we will see how to make a MySQL backup and how to restore it.
If you are a web developer and even more if you programm with PHP surely you know that the most used database engine is MySQL. Of course, as a good developer you should know that is a golden rule to make and keep backups of your databases. Here we’ll see how to do it easily.
- # Back up from command line (using mysqldump).
- # Back up whith compression.
- # Restoring the databases.
Back up from command line (using mysqldump)
We can backup our database using the mysqldump command, which connects with the database and generates a SQL dump file. This file has all the needed SQL queries to restore the database:
1 | user@unix:~$ mysqldump --opt -u[uname] -p[pass] [dbname] > [backupfile.sql] |
Where [uname] is the MySQL username, [pass] the user’s password, [dbname] is the database name, [backupfile.sql] is the output file name, and [–opt] are mysqldump options.
For instance, if we want to backup a database named ‘myBlog’ with a ‘root’ as username and the ‘mypass’ passowrd, and store the backup in a file called ‘myBlog_backup.sql’, we should run:
1 | user@unix:~$ mysqldump -uroot -pmypass myBlog > myBlog_backup.sql |
With mysqldump we can specify which tables we want to backup, for instance if we want to backup the ‘users’ and ‘posts’ tables, we should run:
1 | user@unix:~$ mysqldump -uroot -pmypass myBlog users posts > myBlog2_backup.sql |
Furthermore if we need to backup more than one database we can use the –databases option:
1 | user@unix:~$ mysqldump -uroot -pmypass --databases myBlog car_shop clients > manydb_backup.sql |
And also if we want to backup all the databases in MySQL we can use the –all-databases option:
1 | user@unix:~$ mysqldump -uroot -pmypass --all-databases > alldb_backup.sql |
Other utils options for mysqldump are:
–add-drop-table: Adds a DROP TABLE sentence before of each CREATE TABLE sentence in the dump.
–no-data: Backs up only the database structure with no data.
–add-locks: Adds the LOCK TABLES and UNLOCK TABLES sentences.
Back up whith compression
If our database is huge enough, we could want to compress the output with the following command:
1 | user@unix:~$ mysqldump -u[uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz] |
And to uncompress:
1 | user@unix:~$ gunzip [backupfile.sql.gz] |
Restoring the databases
Above we backed up a database called myBlog in a myBlog_backup.sql file. To restore it we should run the below commands:
1 | user@unix:~$ mysql -u[uname] -p[pass] [db_to_restore] < [myBlog_backup.sql] |
If we have compressed the dump:
1 | user@unix:~$ gunzip < [backupfile.sql.gz] | mysql -u[uname] -p[pass] [dbname] |
If we need to restore an existing database, we should run the mysqlimport command:
1 | user@unix:~$ mysqlimport -u[uname] -p[pass] [dbname] [backupfile.sql] |
That is. I hope it’s helful.