Chapter 1: Introduction
Hello, and welcome to Lesson 10. As I mentioned at the close of Lesson 9, we'll take a break from PHP coding today and focus on a crucial administrative element of your Web application—data backups.
The downside of using MySQL as a repository for dynamic Web page content is that all of your information is located in one place that's not particularly easy to get to. With static Web pages, you can easily copy HTML files to wherever you need. But MySQL database files are much more restrictive.
So today we'll go exploring. First we'll locate our data files, and then we'll look at a few options for backing them up to a safe place. After doing our backups, we'll talk about how to use them to restore data if something bad happens to our Web site.
Let the exploration begin!
Chapter 2:
The MySQL Data Layout
The MySQL server consists of many different parts, each performing a specific function. The part that's responsible for storing and accessing data is called the database engine. The database engine is the gatekeeper to your data, and all requests for data go through it. The MySQL database server incorporates two different database engines:
These use different techniques for storing data. MyISAM is the original MySQL database engine. It stores data in a separate file for each table contained within a database. Each database is represented as a separate folder under the main MySQL data directory, with the table files located under the appropriate database folder.
The MyISAM database engine creates each table as two separate files:
While the MyISAM database format is robust and easy to understand, it's somewhat limited in its capabilities. This was one reason why in its early days MySQL wasn't widely accepted as a commercial-quality database server.
The InnoDB database engine is more advanced and was only recently incorporated into MySQL. It provides many commercial-quality database features within MySQL, such as SQL transactions and automatic database crash recovery.
Now let's take a look at how and where InnoDB stores database data on a PC.
The InnoDB Database Format
Unlike MyISAM, InnoDB uses a single file to store all of the information and data for a database. This single file is called a tablespace, and it can even store information and data for multiple databases.
A tablespace is nothing more than a large file the database engine creates and sets aside to hold data as you place it in your database. The database engine uses a unique format for placing data into the tablespace, which makes it fast and easy to retrieve when necessary.
In a normal file, as you add new data to it, it takes up more space on the hard drive. Unfortunately, this forces the operating system to look for available disk space to add to the file—a time-consuming process. The tablespace file makes things faster because it contains all NULL characters. As the database engine adds new data to the tablespace, it just replaces the existing NULL characters with data, which is much faster than having to write to a new space on the hard drive.
Often, the database engine uses more than one tablespace to store databases. MySQL allows you to create multiple tablespace files, then specify which tablespace file you want to use as you create each table in a new database. (Theoretically, you could select different database engines for each table in a single database, but that's not a great idea.)
By default, the WAMP5 server uses a single tablespace file, which it calls ibdata1, and locates it in the folder c:\wamp\bin\mysql\mysql5.0.45\data. WAMP5 sets the default size of the tablespace to 10 MB.
One downside to prebuilding a tablespace file is that at some point you may run out of room in the tablespace. When a tablespace runs out of room, MySQL has two options:
By default, WAMP5 creates the ibdata1 tablespace file using what's called autoextend mode. The MySQL autoextend feature allows the InnoDB database engine to add more space to the tablespace file automatically, as it's needed.
Obviously, when this occurs you'll notice a performance hit as MySQL must negotiate with Windows to find more space on the hard drive. The other downside of this feature is that for large applications you must watch your tablespace file size to ensure it doesn't consume your entire hard drive.
Log Files
As you look in the c:\wamp\bin\mysql\mysql5.0.45\data folder, you'll see one or more other files in addition to the ibdata1 file. The file ib_logfile0 is called a binary log file (sometimes referred to as just a log file).
The binary log file tracks database SQL transactions that modify data in the tablespace file. The database engine stores a copy of each INSERT, UPDATE, and DELETE SQL statement it processes in the log file. This process is what enables the InnoDB database engine to support automatic crash recovery.
The database engine keeps track of which SQL statements it processes in the actual database tablespace file. If the MySQL server crashes before an SQL statement is completely processed, the statement is still available in the log file.
The first thing MySQL does each time you start it is check the log file for unprocessed SQL statements. If any are found, it processes them. This brings the databases up-to-date with where the system last left off before the crash.
MySQL Support Files
You may have already noticed by looking in the c:\wamp\bin\mysql\mysql5.0.45\data folder that under the data folder, MySQL creates a new folder for each database, even if you're using the InnoDB database engine. It uses the database name as the folder name.
You should already see a recipe folder, along with a mysql and phpmyadmin folder. The mysql database contains system information for the MySQL server. The phpmyadmin database contains information for the phpMyAdmin tool. Both of these databases use the MyISAM database engine, so you'll see all of the MYD and MYI files associated with each table in the database.
Also under the database folders, you'll see a file for each table with a .frm extension. This is a format template, and it's where MySQL keeps track of the format of the table data fields. You'll also see a db.opt file, which is where MySQL keeps track of the various options you've set for the database, such as the collation type.
Knowing Which Database Engine You're Using
The WAMP5 software we've been using in this course supports both database engines. It uses MyISAM for tracking internal MySQL database system items (such as user accounts and table names), but, by default, it uses InnoDB for user-created database tables.
If you remember, when we created the recipe database using phpMyAdmin, we didn't specify a database engine type. So the MySQL server in WAMP5 automatically used the InnoDB database engine for every database table we created.
|
When you look at the recipe database in phpMyAdmin, you'll notice the Type field. This indicates what database engine each table uses. In the recipe database, you should see that all of the tables use InnoDB.
)
The phpMyAdmin database information page for the
recipe database
Since all of the tables in the recipe database use the InnoDB database engine, we know that MySQL is storing all of our data in the ibdata1 tablespace file.
]Now that you've seen where all the MySQL
data files are located, you might think it would be a snap to just copy
these files to somewhere else for backups. Well, unfortunately, things
aren't quite that easy. Follow me to Chapter 3 to find out why.
Chapter 3:
Cold Backups
All of the MySQL database files we talked about in Chapter 2 have one thing in common—you shouldn't touch them while the MySQL server is running. When the MySQL server is running, these files are locked and can't be moved. If you try to move them, you'll get an error message from Windows saying that the file is in use.
Depending on what the MySQL server happens to be doing at any moment, you might be able to copy the files while the server is running, but this is not recommended as you can end up with a damaged file. (You'll see in Chapter 4 that there is a way to export the data from a running MySQL server without having to copy the actual database files.)
Also, if you run a commercial tape-backup software package on your Windows server, you'll probably notice that it skips the database files if the MySQL server is running (and gives you a message that they're in use).
Obviously, the easiest way to safely back up MySQL database files is to turn off the MySQL server. Copying data files while the database server is turned off is called a cold backup.
This method ensures that none of the database files will be in use, so you'll be able to copy all of the data and system files you'd need to completely restore the database system. Unfortunately, when you use this method, visitors can't connect to your application database while it's being backed up. You'll have to evaluate how big a problem this is in your specific situation.
Performing a Cold Backup
If you do decide to perform a cold backup of your data, you'll need to know what data to copy, how to copy it, and where to copy it to. In the case of the WAMP5 server, we've already seen where our data resides.
You'll want to copy the entire c:\wamp\mysql\data folder to perform a complete backup. Here are the steps for performing a complete cold backup of the MySQL server:
The alternative location you copy the files to should be on a separate storage device from where they normally are. Just copying them to another folder on the same hard drive doesn't solve anything if the hard drive crashes. In this day of large, cheap USB memory sticks, it's often easy to just copy the files to a removable memory stick, and then put the stick in a safe place.
Restoring From a Cold Backup
If you should suffer a catastrophic loss of the hard drive that WAMP5 is running from, you can easily recover from your last cold backup. Here are the steps to do that:
That's all there is to it! This is not only an easy way to recover a lost database, but it's also an excellent way to migrate between different computers (as long as they are the same platform, such as Windows).
Advanced File-Based Backup Methods
There are a couple of alternative methods for backing up data if you want to still copy the database files but don't want the Web application down for an extended time.
InnoDB Hot Backup
The InnoDB database engine is supported by Innodatabase Oy, a subsidiary of the Oracle company. Innodatabase Oy provides several commercial tools that professional administrators who work with InnoDB databases might be interested in.
The InnoDB Hot Backup package is a tool you can use to copy all of the InnoDB database files to an alternative location without turning off the MySQL server. Unlike the hot backup data export we'll see in Chapter 4, InnoDB Hot Backup actually copies all of the MySQL database files (including system information) to an alternative location.
Unfortunately, this feature comes with a pretty hefty price tag. Currently, a one-year license to run it on just one computer costs $470. Obviously, this is not a tool intended for backyard database administrators (unless you've got lots of cash you want to spend on your hobby).
MySQL Replication
Another option for keeping copies of your MySQL database files is replication. Replication is having a complete duplicate of a database server running in an alternative (usually remote) location, as shown.
Running a master/subordinate MySQL server combination
The alternative location runs another copy of the MySQL server and uses a duplicate set of the database files. The primary MySQL server is considered the master. All database activity transpires on the master server (in other words, this is the server your Web application should point to). The alternative MySQL server is the subordinate.
A subordinate MySQL server connects to the master server and monitors SQL statements placed in the log file. The subordinate server reads the master server's log file and duplicates each statement to the subordinate log file. You can have any number of subordinate MySQL servers duplicating data from a single master server.
Each subordinate server synchronizes the statements in its log file with the master copy. If a subordinate server is offline for a period of time, the next time it contacts the master it resyncs the log file, adding all of the statements necessary to become up-to-date.
This makes replication ideal for storing a remote copy of the MySQL database. The subordinate
copy is often very close to a real-time backup of the master database.
Chapter 4:
Performing Hot Backups
Another technique for backing up MySQL data is to perform hot backups. As you might have guessed, hot backups are backups that you do while the MySQL server is running and accepting SQL statements.
Because the database is still running, a hot backup can't lock the database tables during the backup. Since you aren't locking the tables, the database engine can still process SQL statements, altering data contained in the database.
Because of this, most hot backups can't copy any of the files associated with the database engine (this is what makes the InnoDB Hot Backup product we discussed earlier unique). Instead, all they can do is take snapshots of the data contained within the database files at a specific moment in time.
This type of hot backup is called a data export. A data export uses SQL statements to copy (or export) data from within the database tables to a text file outside of the database. When you perform a cold backup, you copy the entire binary database files to an alternative location. One disadvantage of this method is that you can't see the data inside of the binary database file. So you have no way of knowing if those files are good or not.
In an export hot backup, the backup program exports data in the database tables to text files containing SQL statements. It creates the SQL statements necessary to rebuild the entire table structure and repopulate the data. You can easily view the export data file and see if the file is complete.
WAMP5 provides two tools for easily performing export hot backups of databases:
Let's take a look at how both of these tools work.
The MySQL mysqldump.exe Program
The mysqldump.exe command line program allows you to quickly and easily export table data from the command line. The mysqldump.exe file is located in the c:\wamp\mysql\bin folder. Here's how to use it:
mysqldump recipe -u root -p > recipe.txt
The mysqldump program exports the entire contents of the recipe database tables to the recipe.txt file. Since it's a text file, you can view the contents of the recipe.txt file you just created by double-clicking it in Windows Explorer.
)
Viewing a database export using Notepad
You should recognize the SQL statements in the export file. First, the export file uses the DROP SQL statement to remove each table if it exists, then it uses the CREATE TABLE SQL statement to rebuild the table. Following that, the export file contains INSERT SQL statements for every table record to repopulate the data in the table.
Copy the recipe.txt file to an alternative location to keep it safe in case of a hard drive crash. Later on we'll see how to use it to restore the database.
The phpMyAdmin Export Tool
The phpMyAdmin tool provides an easy way to graphically perform export hot backups on your databases. Let's walk through an example to see how easy this is:
This produces the phpMyAdmin Export Web page, as shown.
)
The phpMyAdmin Export Web page
There are lots of options on the Export Web page that allow you to customize the export data file. The left-side box allows you to select which table or tables you want to export from the recipe database. Under that, you have the option of exporting the data in several formats. The default is to create a text file of SQL commands, exactly as produced by the mysqldump.exe program.
One of my favorite choices is PDF, which allows you to create a PDF file of the data contained in a single database table. This is great if you need to quickly prepare reports on your database data. You can use the CSV format if you want to easily export your table data into an Excel spreadsheet.
The box on the right contains additional options that depend on the specific export format you select. For the SQL format, you can define the structure of the SQL statements (such as not to include the DROP statements to remove existing tables, or include the AUTO_INCREMENT values in the data) and how to create the INSERT SQL statements to import the data. The default values for all of these settings are fine for our application.
The bottom section of the Export Web page allows you to define some options for the resulting export file. The file name template uses the value __DB__, which is a variable that phpMyAdmin sets to the database name in the resulting data file. You can add additional information, such as a date and time, to the filename template. Finally, you can specify if phpMyAdmin should use compression after it creates the export data file.
Once you've set all of the options to your liking, just click the Go button on the bottom of the Web page to start the export. A Save As dialog box appears, asking you for the location to store the exported data file.
Restoring Hot Backups
Okay, so now you've got your export data file that contains SQL statements to completely restore your database data. But what do you do if your database crashes? When it comes to restore methods, hot backups are a little different from cold backups.
Since the hot backup export file only contains information on the tables and the table data in the database, you'll first need to do some manual work to get things ready to import the data. Here's how:
As you can see, there's lots of manual work you need to do before you can import the export data file. Once you have the database ready to import the stored export data file, you can use either the mysql.exe command line program to import the data or the phpMyAdmin Import tool.
Restoring Data Using mysql.exe
Here's how to restore the recipe database tables using the recipe.txt export file we created earlier:
mysql recipe -u root -p <>
You can now run the Recipe Center Web application and view your data.
Restoring Data Using phpMyAdmin Import
The phpMyAdmin interface allows you to directly import stored export data files. To do this:
The phpMyAdmin import tool automatically processes the SQL statements in the export data file and re-creates the tables and table data.
Okay, enough about backing up and restoring. Let's finish up.
Chapter 5:
Summary
Today, we looked at how to protect our Web application from a catastrophic disk failure. Since the application stores all of the dynamic Web page data in a MySQL database, we looked at how MySQL stores that data, how to back it up, and, finally, how to restore it.
MySQL uses two types of database engines. The MyISAM database engine stores database table data in separate files within a database folder. The WAMP5 server uses the InnoDB database engine by default, which stores all database table data in a single tablespace file.
The InnoDB database engine also uses separate binary log files to store SQL statements in case of a database crash. The MySQL server recovers unprocessed SQL statements from the binary log files automatically when it restarts.
We saw that the easiest database backup procedure is the cold backup. A cold backup copies the entire database file structure to an alternative location. The downside of a cold backup is that the MySQL server must be inactive to copy the database files without risking errors. This means that your Web application must be offline during the cold backup, which could take awhile and result in unhappy visitors.
A hot database backup allows you to copy table information and data while the MySQL server is still running. While this is more friendly for our Web page visitors, it requires more work on our part to create text SQL export data files, and even more work to restore the database.
You can restore a hot database backup file using the mysql.exe command line prompt or the phpMyAdmin Import tool. Unfortunately, in both cases you must first re-create the original database and user accounts.
In the next lesson, we'll delve into some more-advanced PHP
programming for our application. For applications that contain lots of
data, it's often not a good idea to list all of the data on a single Web
page. So we'll look at how to present application data using multiple
Web pages that visitors can browse through.
Supplementary Material
|
Backup Tutorial http://www.datamills.com/Tutorials/backup_tutorial_Index.htm |
|
This tutorial provided by the Data Mills Corporation (a backup solution provider) lists lots of things to think about when deciding on a backup strategy for your application. Items such as off-site storage of backup data and rotating backup media are important, but often overlooked when formulating a data backup strategy. |
FAQs
Q: How often should I perform a database backup?
A: This mostly depends on how often the data in your database changes. Performing daily backups doesn't make much sense if your data only changes once a month. On the other hand, Web sites that have important data changing constantly (such as shopping sites) use replication to ensure data is continually backed up. For most content management Web sites, it's a good idea to at least perform nightly backups.
Q: How long should I keep a backup data file on hand?
A: If you're running a Web application that doesn't delete data from the database, you can safely delete old backups as soon as a new backup is taken (assuming you can trust the integrity of the backups). If you're running a Web site that deletes data on a regular basis, it's always a good idea to keep older backups around. Inevitably, you'll be asked to restore an individual data record that someone accidentally deleted a couple of months ago.
Assignment
Today's assignment is to practice doing a database
backup. Of course, it's not a good idea to practice your backups with
live data, so I recommend you start by creating a test database with
test tables and test data.
After creating your test database, try
exporting the data using both the cold and hot backup methods. Once
you've performed successful backups, try deleting your entire test
database environment, and see how to restore things back to normal using
each type of backup. It's extremely important to know how to properly
restore your backup data. You don't want to find out you're missing
anything after you've already lost your live application data!
Note: If you're using a WAMP or LAMP
server other than WAMP5, you'll need to see how your MySQL server is
configured to find out which database engine it uses. For a WAMP server,
you should be able to find the my.ini MySQL configuration file
to see which it supports. LAMP servers call this file my.cnf.