In this chapter we will look at how to back up or make copies of your databases, how to move them to another machine, and how to recover when things go wrong.
We will cover the following:
• Backing up your databases
• Restoring from backups
• Checking and repairing tables
Obviously, as with any electronic file, you should back up your database files. You may also want to make a copy of your database for replication purposes or to move to a new machine.
There are four ways you can make a backup in MySQL:
• Use the mysqldump script to create a dump file, that is, a file containing the SQL statements necessary to re-create the database.
• Use the mysqlhotcopy script to create a data file. This copies the data files for a particular database directly.
• Directly back up the data files yourself. This is really doing what mysqlhotcopy does, but manually. If you choose to use this option, you will need to either shut down the database or flush and lock all tables before copying to make sure that they are internally consistent. Both mysqldump and mysqlhotcopy will flush and lock for you, so they are easier, safer options.
• Use the BACKUP TABLE and RESTORE TABLE commands to back up or restore a specified table or set of tables.
We'll look at each of these options in turn.
Bear in mind that although backups are vitally important, all backups involve restricting access to the user while backups are being made. Why? To take a consistent snapshot of a database, tables need to be flushed and held constant while the backup is performed. This can be done by locking tables (in most cases) or by taking down the server (not recommended), but either way you are going to reduce database responsiveness while you are backing up.
One solution to this issue is replication. You can take down one slave and back it up while users continue blissfully about their business. We will discuss replication in detail in Chapter 16, “Replicating Your Database.”
The most common way to run a backup is using the mysqldump script from the command prompt on your system. This script connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database.
For example, typical usage of this script would be
mysqldump --opt –u username –p password employee > backup.sql
In this case we are using the –-opt option, which encapsulates a few other options—we'll look at these in a minute. We have listed the database name and are redirecting the output to the backup file we want to use.
Using this script on the simple employee database will give you an output file similar to the one shown in Listing 14.1.
Listing 14.1. Sample Output from mysqldump
-- MySQL dump 10.2
--
-- Host: localhost Database: employee
---------------------------------------------------------
-- Server version 4.1.0-alpha-max-debug
--
-- Table structure for table 'assignment'
--
DROP TABLE IF EXISTS assignment;
CREATE TABLE assignment (
clientID int(11) NOT NULL default '0',
employeeID int(11) NOT NULL default '0',
workdate date NOT NULL default '0000-00-00',
hours float default NULL,
PRIMARY KEY (clientID, employeeID, workdate)
) TYPE=InnoDB CHARSET=latin1;
--
-- Dumping data for table 'assignment'
--
/*!40000 ALTER TABLE assignment DISABLE KEYS */;
LOCK TABLES assignment WRITE;
INSERT INTO assignment VALUES (1,7513,'0000-00-00',5),(1,7513,'2003-01-20',8.5);
UNLOCK TABLES;
/*!40000 ALTER TABLE assignment ENABLE KEYS */;
--
-- Table structure for table 'client'
--
DROP TABLE IF EXISTS client;
CREATE TABLE client (
clientID int(11) NOT NULL auto_increment,
name varchar(40) default NULL,
address varchar(100) default NULL,
contactPerson varchar(80) default NULL,
contactNumber varchar(12) default NULL,
PRIMARY KEY (clientID)
) TYPE=InnoDB CHARSET=latin1;
--
-- Dumping data for table 'client'
--
/*!40000 ALTER TABLE client DISABLE KEYS */;
LOCK TABLES client WRITE;
INSERT INTO client
VALUES
(1,'Telco Inc','1 Collins St Melbourne','Fred Smith','95551234'),
(2,'The Bank','100 Bourke St Melbourne','Jan Tristan','95559876'),
UNLOCK TABLES;
/*!40000 ALTER TABLE client ENABLE KEYS */;
--
-- Table structure for table 'department'
--
DROP TABLE IF EXISTS department;
CREATE TABLE department (
departmentID int(11) NOT NULL auto_increment,
name varchar(30) default NULL,
PRIMARY KEY (departmentID)
) TYPE=InnoDB CHARSET=latin1;
--
-- Dumping data for table 'department'
--
/*!40000 ALTER TABLE department DISABLE KEYS */;
LOCK TABLES department WRITE;
INSERT INTO department
VALUES
(42,'Finance'),
(128,'Research and Development'),
(129,'Human Resources'),
(130,'Marketing'),
(131,'Property Services'),
UNLOCK TABLES;
/*!40000 ALTER TABLE department ENABLE KEYS */;
--
-- Table structure for table 'employee'
--
DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
employeeID int(11) NOT NULL auto_increment,
name varchar(80) default NULL,
job varchar(30) default NULL,
departmentID int(11) NOT NULL default '0',
PRIMARY KEY (employeeID)
) TYPE=InnoDB CHARSET=latin1;
--
-- Dumping data for table 'employee'
--
/*!40000 ALTER TABLE employee DISABLE KEYS */;
LOCK TABLES employee WRITE;
INSERT INTO employee
VALUES
(6651,'Ajay Patel','Programmer',128),
(7513,'Nora Edwards','Programmer',128),
(9006,'Candy Burnett','Systems Administrator',128),
(9842,'Ben Smith','DBA',42),
(9843,'Fred Smith','DBA',131);
UNLOCK TABLES;
/*!40000 ALTER TABLE employee ENABLE KEYS */;
--
-- Table structure for table 'employeeSkills'
--
DROP TABLE IF EXISTS employeeSkills;
CREATE TABLE employeeSkills (
employeeID int(11) NOT NULL default '0',
skill varchar(15) NOT NULL default '',
PRIMARY KEY (employeeID, skill)
) TYPE=InnoDB CHARSET=latin1;
--
-- Dumping data for table 'employeeSkills'
--
/*!40000 ALTER TABLE employeeSkills DISABLE KEYS */;
LOCK TABLES employeeSkills WRITE;
INSERT INTO employeeSkills
VALUES
(6651,'Java'),
(6651,'VB'),
(7513,'C'),
(7513,'Java'),
(7513,'Perl'),
(9006,'Linux'),
(9006,'NT'),
(9842,'DB2'),
UNLOCK TABLES;
/*!40000 ALTER TABLE employeeSkills ENABLE KEYS */;
We could reload or re-create the employee database elsewhere by doing the following:
Creating an appropriately named database on the target machine.
Loading this file using
mysql –u username –p < backup.sql
The mysqldump script has many options for use. In this case we have used –-opt, which encompasses the following options:
• –-quick: This tells MySQL to dump the data directly to the file, rather than buffering it in memory first (the default). This will speed things up.
• --add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump. (You can see these in Listing 14.1.)
• --add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.
• --extended-insert: Tells MySQL to use the multiline insert syntax to insert multiple rows with a single INSERT. For example, in the listing, these look like the following:
INSERT INTO employeeSkills
VALUES
(6651,'Java'),
(6651,'VB'),
(7513,'C'),
If we have to use our backup to re-create the database, this will be faster to execute than a series of single INSERT statements.
• --lock-tables: Tells MySQL to lock all the tables before starting to dump.
Note that the --opt (meaning optimized) will optimize the length of time it takes to reload the dump file, rather than the length of time it takes to create the dump file. Creating the dump file can be slow.
Here are a couple of other useful options:
• --databases: Allows you to list more than one database for dumping.
• --all-databases: Tells MySQL to dump all the databases it has in storage.
• --allow-keywords: If you ever use field names that are MySQL keywords (or might become keywords in the future), this option tells MySQL to fully qualify every column name with its table name.
• -d or --no-data: Dumps only the database structure, not the contents. This is very useful if you are testing and deploying databases on different machines.
The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you.
There are a couple of disadvantages. The first is that this script locks tables: Running this script on your server will lock out users for seconds or minutes, depending on the size of the tables. If you are planning on doing a dump on a single, nonreplicated server, you should try to do this in a nonpeak period or your users will tend to get a little irritated. If you have a lot of data and users at all times of the day, you should choose another backup option.
The other disadvantage is that, because mysqldump works through the MySQL server, it will be slower to run than mysqlhotcopy. The script mysqlhotcopy does not make much use of the MySQL server. It does most of its work directly through the file system.
The mysqlhotcopy script differs from mysqldump in that it copies the actual database data files, rather than retrieving data through a connection to the server. It does make a connection in order to flush and lock the database tables, but because it is mostly engaged in file-system operations rather than database queries, it should run a little faster than mysqldump.
You can use it as shown here:
mysqlhotcopy -u username -p database_name backup_location
This script is a Perl script. If you are using a Unix or Unix-like machine, you will almost certainly have a perl executable somewhere. If you are using Windows, you will need to install Perl to use it. You can download Perl for Windows from ActiveState if you don't have it already:
www.activestate.com/Products/ActivePerl
The files produced by mysqlhotcopy are replicas of the database data files. To use these backups, you should stop the MySQL server and replace the data files in the MySQL data directory with the backed-up files.
Instead of using mysqlhotcopy, you can replicate what it does manually. This means flushing and locking the tables and copying the data files to a backup location while the tables are still locked.
This means you will need an open MySQL session. You can begin by issuing a LOCK TABLES command to lock all the tables you plan to back up:
lock tables
employee read,
department read,
client read,
assignment read,
employeeSkills read;
The LOCK TABLES statement takes as parameters a list of table names and the type of lock we would like to acquire, READ or WRITE. For a backup, a read lock is generally sufficient. This means that other threads (connections) can continue to read from the tables but will be unable to write to them while we are performing a backup.
Locking is important in situations like this because backups can take a significant amount of time. Using our example, it would be unfortunate if after the employee table had been backed up, but before the department table had been backed up, somebody deleted all employees in one department and then deleted the department. We would be left with an inconsistent backup, showing employees working for a nonexistent department.
Next, you should issue a FLUSH TABLES command:
flush tables;
If you are backing up all of your databases, you can do these two steps in one with the following command:
flush tables with read lock;
Now you can copy the data files. It is very important that you leave your session (where you locked and flushed the tables) open while you do this. This makes sure that the locks are maintained. When you close that session, the tables will be unlocked.
After copying your files, you should of course unlock the tables:
unlock tables;
This procedure is the same as that in the mysqlhotcopy script, and you can restore in the same way.
As an alternative to the approaches we have just discussed, there are two SQL statements we can use to achieve the same effects. These are BACKUP TABLE and RESTORE TABLE. These commands work only on the MyISAM table type.
You can back up a MyISAM table like this:
backup table t1 to 'path/to/backup';
Note that if you are using Windows, you will need to specify the drive letter, for example,
backup table t1 to 'c:/path/to/backup';
This copies the files that represent the specified MyISAM table to the specified path. The table will be read locked before it is backed up.
You can also specify a comma-separated list of tables; however, each one will be locked and backed up in turn. If you want a consistent set of tables, you should issue a LOCK TABLES statement first (see the preceding section, “Backing Up and Restoring Manually,” for details on how to do this).
To restore from the backup, use this:
restore table t1 from 'c:/tmp';
This will work only if the tables you restore do not exist in the current database. If you have a table with this name, you will need to issue a DROP TABLE before you use RESTORE.
Again, RESTORE works only on MyISAM tables.
In most cases when you restore from a backup, some inserts and updates will have been made since the backup was taken. The database can be regenerated by restoring from the backup as described in each of the previous sections and then re-executing any changes made since the backup was taken.
These changes are stored in your binary log or your update log. This is why the binary log is so important. You can extract a list of operations from the binary log using
mysqlbinlog logfile > updates.sql
It's a good idea to look at this file before re-executing the queries, in case you want to not re-execute any of them. It is possible that a poorly-thought-through SQL query caused you to need to resort to a backup.
For example, we once had a programmer type something along the lines of
update user set password='password';
Obviously, when restoring the table, we did not want to re-execute this particular query and again set the password for every user in our system to be password!
Whichever backup method you choose, it is very important to test your backup or, more precisely, your recovery. It is not unusual to find administrators who dutifully create a backup on a regular basis but have never checked whether they can recover from a backup if needed.
A backup procedure should be something you give serious consideration when analyzing your risks and deciding how to do it. Where can you put the backup so that it is on a different physical disk? How can you ensure that a copy of the backup is stored securely offsite? Having made these decisions and scheduled the backup for regular processing, you should not be worrying about whether it works. If you have tried to recover as a test, you can find any problems before they are crucial.
One important part of your MySQL installation and recovery process to check is the binary log. It is not enabled by default, but it is needed to bring a restored database back up to date.
Checking tables for corruption is part of routine table maintenance and is also part of your disaster recovery routine, for example, in the event of a power failure.
MySQL allows us to check tables in three ways: using CHECK TABLE, using myisamchk (or isamchk), and using mysqlcheck. We can then repair any problem tables using REPAIR TABLE or again with myisamchk (or isamchk) or mysqlcheck.
There are a few factors you should take into account when determining which of these options to use. The CHECK and REPAIR commands can be used from inside MySQL, whereas the other techniques are used from the command line. CHECK and REPAIR can be used on both MyISAM and InnoDB tables. The isamchk script can be used on ISAM tables, whereas myisamchk and mysqlcheck can be used on MyISAM tables.
You should not use myisamchk or isamchk on tables that are currently in use. It is better if you take the server down before using these scripts, but you can resort to locking if needed. If you use these scripts on tables while they are being used by other MySQL threads, your data may become corrupted. CHECK, REPAIR, and mysqlcheck are all safe to use when the server is up and tables are in use.
We will look at the use of each of these tools.
You can check a table with CHECK TABLE as in this example:
check table department;
CHECK TABLE works on MyISAM and InnoDB tables.
This should (all being well) give you a response like this:
1 row in set (0.00 sec)
You may also get Table is already up to date, which also means everything is fine.
If you get any other message, you have a problem and should try to repair the table. You can do this with REPAIR TABLE (as long as it's a MyISAM table) as shown here:
repair table t1;
If the repair works (or if no repair was actually needed), you should get a result similar to this:
1 row in set (0.03 sec)
If you get any message other than OK, the REPAIR hasn't worked and you will need to resort to the more powerful myisamchk.
In this chapter we consider only myisamchk and ignore isamchk. If you have any ISAM tables, we suggest that you convert them to MyISAM (see Chapter 9, “Understanding MySQL's Table Types”).
The myisamchk program is incredibly useful and will get you out of some unpleasant spots you might find yourself in. Again, remember that you should not use myisamchk when the server is in use. It is safest to stop the server.
The simplest way to invoke myisamchk is by typing
myisamchk table
at the command prompt.
The table should be the path to a .MYI file that represents a MyISAM table.
This will report virtually all errors. If it doesn't seem to be finding your problem, you can try running it with the -m switch. The default behavior looks for corruption in the indexes; with this switch, the rows are scanned as well.
You can also repair errors with myisamchk. The vast majority of table errors you will encounter with MyISAM tables can be fixed in this way. You can invoke myisamchk with the -q -r options for quick recovery, as shown here:
myisamchk -q -r table
If this doesn't work, you can back up the data file and then try a full recovery:
myisamchk -r table
If that doesn't work, you can try the --safe-recover option, which will fix some errors not fixed by the -r option:
myisamchk --safe-recover table
The myisamchk program has a large number of options, which you can review by typing myisamchk at the command prompt with no parameters.
The mysqlcheck program can be used to check MyISAM and InnoDB tables and to repair MyISAM tables safely while the server is up and running.
To check database tables with mysqlcheck, you can invoke it as in the following example:
mysqlcheck -u username -p employee
You can follow this with a list of tables you would like checked, but by default it will check all the tables in the specified database (a nice feature). If all is well, you should see output similar to the following:
employee.assignment OK
employee.client OK
employee.department OK
employee.employee OK
employee.employeeSkills OK
You can also use the --databases switch to specify a list of databases to check or the --all-databases option to check all the databases on the server.
You can use mysqlcheck with the -r option to repair any corrupted MyISAM tables that it encounters.
• mysqldump creates a dump file of SQL statements.
• mysqlhotcopy copies the data files to a backup location.
• BACKUP TABLE copies the data file for a table to a backup location.
• You can manually back up by locking and flushing the tables and then copying the files.
• Reload dump files from mysqldump.
• Copy back data files from mysqlhotcopy or a manual backup.
• Restore from BACKUP TABLE with RESTORE TABLE.
• Re-execute operations since the backup from the binary log.
In the next chapter, “Securing Your MySQL Installation,” we'll see how you can avoid the most common security pitfalls.