MySQL Database is one of the most widely used database management systems in the world. Based on SQL (Structured Query Language), the RDBMS is owned by Oracle and is used by millions of people the world over. Though, just like any other database, it is of utmost importance to backup MySQL database as well. In this informative tutorial, we will teach you how to backup and restore MySQL database in a stepwise manner.
MySQL is an open source DBMS, which was originally developed by My SQL AB. Presently owned by Oracle, it is a Relational Database Management System (RDBMS) that is based on SQL. One of the best things about MySQL is that it can run on almost every platform. Due to its seamless compatibility, it is widely used for online publishing by being a part of LAMP (Linux Apache MySQL PHP) or WAMP (when used with Windows).
It is a server database system, as it uses SQL to query database and yield desirable results. Since MySQL is an open source database, anyone can use it under the General Public License. Though, for its commercial use, enterprises need to obtain a license from Oracle.
In the current tech scenario, it is of utmost importance to backup MySQL database in a timely manner. If you are managing a web portal, then you can’t function without a database. Even though MySQL database has an advanced layer of security, it can be injected with malware.
With the advancement in SQL injection techniques and the rise of cyber attacks on database systems, taking a backup of your database has become a necessity. This will prevent you from an unexpected scenario and you won’t lose your crucial data. Additionally, while moving from one server to another, it is important to take a backup of your database. The frequency depends on the size of data, availability of free space, and personal preferences.
Now when you know how significant it is to backup MySQL database, let’s proceed and learn how to do it in two different ways. All you need is an access (shell or telnet) to your web server to backup MySQL database by taking the assistance of command line interface.
You might already be aware of the “mysqldump” command, which creates an SQL dump file after connecting to the MySQL server. Since the dump file has necessary statements to restore your database, it can be used as a backup tool. The correct syntax for the command is as follows:
$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
Here, <uname> stands for the username of your database, <pass> is the password, <dbname> is the name of your database, <backupfile.sql> is the file name given to the backup and <--opt> is the option for mysqldump.
For instance, if you need to backup a database "abc" with the username "one" to a file "abc_backup.sql" (with no password), you will give the following command:
$ mysqldump -u one -p abc < abc_backup.sql
After giving this command, a SQL file "abc_backup.sql" will be created, having all the essential commands to recreate the original database.
Perform a selective backup
The command can also be used to backup certain tables instead of the whole database. Let's consider this with an example. Suppose, we need to backup only "table_one" and "table_two" from the "abc" database to "abc_backup.sql". It can be done by passing the following command.
$ mysqldump -u root -p abc table_one table_two > abc_backup.sql
Backup multiple databases
Additionally, the command can also be used to take a backup of more than one database at a time. In this, you need to provide --database in the option with the list of databases you need to backup. For instance, the following command can be used to backup "abc" and "xyz" databases.
$ mysqldump -u root -p --databases abc xyz > abc_backup.sql
Also, you can give the --all-databases option to take a backup of all the databases present in the server at once.
$ mysqldump -u root -p --all-databases > alldata_backup.sql
Furthermore, there are a few other commands as well that can help you take a selective backup. The "--add-drop-table" command can be used to drop a table while taking a backup. The "--no-data" command dumps only the structure of the database without any content. Also, to set locks on a table, you can use the "--add-locks" command.
Since it is quite easy to use the mysqldump command, it provides a fast and reliable solution to backup MySQL database. By using the above-stated commands, you can easily take a backup of your database in no time.
If you don’t have enough space, then you can also choose to compress your database while taking its backup. This can be done by dumping the output to a gzip file with the "mysqldump" command. Its syntax is as follows:
$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupdbfile.sql.gz]
Later, you can extract the compressed file by providing the "gunzip" command like this:
$ gunzip [backupdbfile.sql.gz]
After taking the backup of your MySQL database, you can restore it as per your needs. Firstly, you need to create a properly named database on the target computer. Once it is done, you can restore your database by giving the following command:
$ mysql -u [uname] -p[pass] [name_to_restore] < [backupdbfile.sql]
If you want to restore a compressed file, then provide the following command:
gunzip < [backupdbfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
For instance, here is how we can restore the "abc_backup" to our new database "abc_new".
$ mysql -u root -p abc_new < abc_backup.sql
Lastly, the "mysqlimport" command can be used to restore a database that already exists in your system. It has the following syntax:
mysqlimport -u [uname] -p[pass] [dbname] [backupdbfile.sql]
By following these instructions, you can easily backup MySQL database and restore it afterward. Even though we have discussed various cases and options for the mysqldumb command, you can do your bit of research and be familiar with other alternatives as well. Go ahead and follow these simple instructions to take a comprehensive or selective backup of your MySQL database in no time.