www.cioby.ro

Introduction

Making backups for your MySQL databases is really important, in cases of disasters, data loss, database migration or replication. There are situations when a user with full privileges to a specific database can issue by mistake a DROP DATABASE statement and delete its contents. As a database administrator you should ensure you have regular backups for all databases which can be used for later restoration. There are several tools out there to make database backups and restores but here we'll talk only about the command line tools provided by MySQL.

Types of backups in Mysql

MySQL backups can be divided in two main categories:

• Physical backups - are created by directly copying the files containing table contents which are stored on disk (tipically in /var/lib/mysql). This type of backup can be made by using programs provided with the operating system, like cp, tar, rsync or similar tools. The data is stored in binary format and these kind of backups are also called raw backups.
• Logical backups - are created by saving the information that represents the logical database structures using SQL statements like CREATE DATABASE, CREATE TABLE, and INSERT into an external dump file. Logical backups are more compatible between MySQL versions but are slower on the backup and restore process. Usually the dump files have an extension which ends in .sql if the backup is not compressed.

Making logical backups in MySQL

MySQL provides the mysqldump utility which is used to create logical backups. If the MySQL server was not configured to allow remote connections you need SSH access to the database server in order to use this utility. The syntax for this command is pretty straightforward.

mysqldump [options] db_name [tables]

When performing a backup using mysqldump command you need to specify an output file otherwise the output will be dumped to the screen. The output file is in ASCII format and is easily readable. This utility has many options but only few of them are used frequently. Among these frequently used options are:

• -u - specifies the username to use when connecting to the server. The specified username must have appropriate privileges to the database in order to perform the dump otherwise the backup will fail.
• -p - specifies the password to use when connecting to the server. If you specify the password in clear text you cannot have a space between the option and the password e.g.(-ppassword).
• -h - specifies the host to which mysqldump should connect to perform the dump. The default host is localhost.

Assuming we have a database named mydb let's use a practical example to better demonstrate how to use the mysqldump utility.

# mysqldump -u dba -p mydb > mydb.sql

Here I have specified 'dba' as the username. If you do not specify an username the currently logged in user will be used to connect. As you can see from the above example after the '-p' option there is a space which means you will be prompted to enter a password interactively. The next argument is the database name to be dumped 'mydb' and the last parameter is the filename 'mydb.sql' which will be placed in the current directory.

The above example applies only to a single database but if you want to backup all the databases available on the server in one step you could use the '--all-databases' parameter to the mysqldump command.

# mysqldump -u dba -p --all-databases > alldbs.sql

This will create a single dump file of all the databases in the server into the file "alldbs.sql" in the current directory. If you want to backup multiple specific databases you can use the '--databases' parameter followed by the databases names.

# mysqldump -u dba -p --databases mydb1 mydb2 mydb3 > 3dbs.sql

Some databases can contain millions of rows thus resulting a huge dump file which occupies a lot of space. Mysqldump with the help of the standard archiving tools available in Linux such gzip or bzip2 can compress them of the fly, and save disk space.

# mysqldump -u dba -p mydb1 | gzip > mydb1.sql
# mysqldump -u dba -p mydb1 | bzip2 > mydb1.sql

Mysqldump also offers the possibility to dump individual tables from specific databases. The tables names are specified imediately after the database name like in the example below:

# mysqldump -u dba -p mydb1 tbl1 tbl2 > mytables.sql

This example with dump only the content of the tbl1 and the tbl2 tables from the mydb1 database. Going even further you can export only a subset of data which meets a certain condition by using the '--where' option followed by a WHERE clause.

# mysqldump -u dba -p mydb1 tbl1 --where "name='John' ORDER BY id" > dump.sql

If you’re dumping InnoDB tables, you can use the '--single-transaction' option to dump the tables within a transaction and get a consistent backup. Mysqldump supports many other options for locking tables, flushing logs and privileges, working with fields etc. For a complete list type mysqldump --help at your command prompt or consult the man page.

Making raw backups in MySQL

As I have said already raw backups implies copying database tables manually to a new location. MySQL also offers the mysqlhotcopy utility which is a Perl DBI script used to make raw database backups. This utility is available only on Unix/Linux systems and can backup only MyISAM tables and not InnoDB. To perform backups using mysqlhotcopy the MySQL server needs to be up and running. When run, mysqlhotcopy uses the LOCK TABLES command to create read locks on the tables being backed up. The syntax for using mysqlhotcopy is:

mysqlhotcopy db_name [/path/to/new_directory]

The connection parameters for username, password and hostname are the same as specified earlier for mysqldump utility. The exception is that mysqlhotcopy does not provide interactive password input and it must be specified in clear text. For example to backup the content of the mydb1 mydb2 mydb3 databases into the bkp directory use the following:

# mysqlhotcopy mydb1 mydb2 mydb3 /bkp

The above command will create 3 subdirectories (mydb1 mydb2 mydb3) in the bkp directory each one named after the database name. By default mysqlhotcopy uses cp for transferring backups into a different directory. If you need to store the backups on a different server you can instruct mysqlhotcopy to use the scp method.

# mysqlhotcopy --method=scp mydb1 mydb2 mydb3 \ This e-mail address is being protected from spambots. You need JavaScript enabled to view it :/bkp

By using the scp method you need to specify the remote username (cioby) and IP (192.168.2.20) and also the remote folder (bkp) where to store the backups. Mysqlhotcopy also lets you test the steps it will perform before actually running them by using the -n or --dry-run option.

# mysqlhotcopy -n mydb1 mydb2 mydb3 /bkp

This way mysqlhotcopy will run in "no execution" mode and it report the actions to the screen without performing them. Mysqlhotcopy supports regular expressions by using the --regexp option. This is useful to filter the database names that match a specific expression. For example to backup only the tables from a database mydb1 that ends with the string "test" use the following:

# mysqlhotcopy mydb1./^.+('test')$/ /bkp

Mysqlhotcopy supports many other option not used very often. For a full list please consult its man page or type perldoc mysqlhotcopy to view its documentation.

Restoring a MySQL database

The most common reason to perform a database restore is to undo some unwanted changes or to recover from database corruption. The restore process involves using mysql client utility. If your dump files were compressed during the backup process you must extract them before the restore process can take place. To do this use the standard archiving utilities available in Linux (unzip, gunzip, bunzip). The mysql client uses the same connection parameters as the mysqldump utility:

• -u - specifies the username to use when connecting to the server. The specified username must have appropriate privileges to the database in order to perform the restore otherwise it will fail.
• -p - specifies the password to use when connecting to the server. If you specify the password in clear text you cannot have a space between the option and the password e.g.(-ppassword).
• -h - specifies the host to which mysql client should connect to perform the restore process. The default host is localhost.

The syntax for using the mysql client utility is very simple.

mysql [options] db_name < dumpfile

In my following example I will use the dump file for the mydb database created earlier to perform the restore process.

# mysql -u dba -p mydb < mydb.sql

If somehow the database on the server was deleted you need to create it first otherwise the restore will fail. The last parameter is the dump file which is used to recreate all the tables and data within the database. If you backed up multiple databases in one step then the dump file contains the CREATE DATABASE statements to create them, so there is no need to create the databases first. Also there is no need to specify a database name when restoring multiple databases.

# mysql -u dba -p < alldbs.sql

You can combine the backup and restore process in one step by piping the output of the mysqldump command to the input of the mysql utility. For example if you want to duplicate a database on a remote server use the following command:

# mysqldump -u dba -ppass123 mydb1 | mysql --host=192.168.2.20 -C mydb1

This command will duplicate the content of the mydb1 database on the remote server 192.168.2.20. The '-C' option is used here to compress all information sent between the client and the server if both support compression and it applies only to network traffic.

Conclusion

The tools presented here when combined with the power of shell scripting and cron jobs can provide to a database administrator a very powerful way to automate the backup and restore process. Also as a database administrator you must ensure that backups are working by testing the restore process before running on a production server.

Share this post