MySQL is one of the most widely-used open source database management systems for the development of interactive websites. If you are using this tool to store your site’s data, it is highly recommended to back the information up so that it can be accessed and restored in case some kind of failure happens. As with most similar systems, MySQL offers a few different methods of data backup. In this article we are going to briefly discuss available backup types and explain how to backup your database automatically using cron and the MySQLDump utility that comes built in with a service.
Backup Types
There is a whole range of various backups types. Let’s take a look at how they differ from one another.
- Logical – saves information delivered in the form of logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements).
- Physical – contains the raw copies of all directories and files that are stored in the database.
- Local – executed on the same host where the MySQL server runs.
- Remote – performed from a different host.
- Full backups include all data present on the MySQL server at a particular point in time.
- Incremental backups comprise only the changes made to the data during a given time period. You can perform incremental backups only when your server’s binary log is enabled.
- “Snapshots” produce logical copies of the file system at a particular moment in time, with no need for a physical copy of the entire file system. MySQL doesn’t have the described function built in, however it can be added through third-party solutions.
- “Hot” backups (online) occur while the MySQL server is running and the database information is retrieved from the server.
- “Cold” backups (offline) take place when the server is stopped.
- “Warm” backups happen when the server is running but locked against modifying data.
Backing Up the Database Using MySQLDump
The mysqldump is basically a script that allows you to backup the database to any external source, be it a different file or even another server. What mysqldump actually does is it creates a set of commands such as “CREATE TABLE” and “INSERT INTO” that can be used to restore the database.
Before we turn to the particular examples of how to use this tool, let’s look at the most common arguments that can be passed to the mysqldump utility.
–user [user name] – MySQL user name needed to connect to the server. Specifies the username of the account that should be used to perform the database dump. MySQL user accounts can be found in the “user” table of the “mysql” database.
–password=[password] – password to the MYSQL server for a chosen user .
–host=[host name] – followed by the name or the IP address of the desired host allows to point out the source and the target servers for the backup.
[database name] – The name of the database that we want to backup. We can also use the arguments —databases when we want to backup more than one database or —all-databases to backup all databases on our MySQL server.
> [dump file] – the “>” symbol directs to the filename which our database should be backed up to. If no particular path is provided, then it will be automatically set to the current directory.
–opt – an argument that optimizes the dump and includes such features as –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset.
—no-data – tells MySQL to only dump the structure of a database and not any actual data.
The full list of arguments with their detail explanations can be found in the MySQL Documentation.
Now that we know the necessary arguments let’s look at a few methods of how to apply the mysqldump.
The most basic way to use this utility is as follows
mysqldump--user [user name] --password=[password] [database name] > /path/to/file/[dump file]
If you want to create a backup for a particular table or several tables – just add the names of the tables as arguments
mysqldump--user [user name] --password=[password] [database name] [table1 name] [table2 name] ... > /path/to/file/dump_table.sql
Where instead of the text in brackets you would have to provide the relevant user and database names, password and the name of the dump file.
Backing up a database to a different server
The mysqldump provides an option to back up a database from one MySQL server to another using the following command
mysqldump--host=myhost --opt database1 | mysql --host=newhost -C database2
In this example we show how to backup the “database1” from the server named “myhost” to the server named “newhost”. The -C argument tells the mysqldump to enforce data compression between the two servers. The last argument specifies that all data from “database1″ on “myhost” should be generated in “database2” on the “newhost” server.
Keep in mind that in order for this feature to work both the source and the target servers have to permit MySQL connections.
Backing up a database to gzipped file (for Linux/Unix servers only)
Here is how you can backup the “database1” to a gzipped file. Note, in this example we use the user account “admin” and password “password”.
mysqldump--user admin --password=password database1 | gzip > /usr/local/mydatabase.gz
Automating Your Database Backup Using Cron
Now that we know how to use the mysqldump utility, let’s look at how we can automate the task with the help of Linux/Unix Cron.
Cron is essentially a job scheduler that allows users create events and set them to be performed at a specified time or interval, without having to manually execute code at that moment.
Here is what we need to do to automatically perform regular backups using the mysqldump utility
30 2 * * * rootmysqldump --user admin --password=password database1 | gzip > /usr/local /mydatabase.gz
The first part with the number defines the time we want our database to be backed up. The fields mean “minute, hour, day of month, month, day of week” respectively. In our case the task will be performed every day at 2.30.
The “root” argument states that you want to run the following command as a root command.
The final part is the command itself. Here we have provided the example of backing up a database to the gzipped file.
Conclusion
The mysqldump utility is one of numerous approaches to backup the MySQL database. The major advantage of this method is its simplicity, yet an extensive variety of features available. It can be adapted to different needs and can perform even the more advanced backups to remote servers or flat files. Combining this tool with a cron job will allow you to automate the task and rest assured that your data is safe.
P.S. Feeling like it’s time to switch to a different CMS platform? Don’t wait any longer, use aisite, automated migration service and have your data migrated as seamlessly as possible.
