Backing Up and Restoring Your Database


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."

Backing Up and Restoring with mysqldump

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 optionswe'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:

  1. Creating an appropriately named database on the target machine.

  2. 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.

Backing Up and Restoring with mysqlhotcopy

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.

Backing Up and Restoring Manually

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.

Backing Up and Restoring with BACKUP TABLE and RESTORE TABLE

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.

Restoring from the Binary Log

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 !



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net