Database Backups
The final concept discussed in this tutorial is indeed an important one: data backups. In this section, two methods for making backups of MySQL data and database structures are discussed, namely mysqldump and mysqlhotcopy .
mysqldump
The utility mysqldump provides a rather convenient way to dump existing data and table structures. Note that while mysqldump is not the most efficient method for creating backups (mysqlhotcopy is, described next), it does offer a convenient method for copying data and table structures which could then be used to repopulate another SQL server, that server not even necessarily being MySQL.
The function mysqldump can be used to backup all databases, several databases, one database, or just certain tables within a given database. In this section, the syntax involved with each scenario is provided, followed with a few examples.
Using mysqldump to backup just one database:
%>mysqldump [options] db_name
Using mysqldump to backup several tables within a database:
%>mysqldump [options] db_name table1 table2 . . . tableN
Using mysqldump to backup several databases:
%>mysqldump [options] --databases [options] db_name1 db_name2 . . . db_nameN
Using mysqldump to backup all databases:
%>mysqldump [options] --all-databases [options]
The options can be viewed by executing the following command:
%>mysqldump --help
Examples :
Backing up both the structure and data found within the widgets database would be accomplished as follows:
%>mysqldump -u root -p --opt widgets
Alternatively, perhaps just a backup of the data is required. This is accomplished by including the option --no-create-info , which means no table creation data:
%>mysqldump -u root -p --no-create-info widgets
Another variation is just to backup the table structure. This is accompished by including the option --no-data , which means no table data:
%>mysqldump -u root -p --no-data widgets
If you are planning on using mysqldump for reason of backing up data so it can be moved to another MySQL server, it is recommended that you use the option '--opt'. This will give you an optimized dump which will result in a faster read time when you later load it to another MySQL server.
While mysqldump provides a convenient method for backing up data, there is a second method which is both faster and more efficient. It is described in the next section.
mysqlhotcopy
The mysqlhotcopy utility is a perl script that uses several basic system and SQL commands to backup a database. More specifically, it will lock the tables, flush the tables, make a copy, and unlock the tables. Although it is the fastest method available for backing up a MySQL database, it is limited to backing up only those databases residing on the same machine as where it is executed.
The function mysqlhotcopy can be executed to backup one database, a number of databases, or only those databases matching a name specified by a regular expression. In this section, the syntax involved with each scenario is provided, followed with a few examples.
Using mysqlhotcopy to backup just one database:
%>mysqlhotcopy [options] db_name /path/to/new_directory
Using mysqlhotcopy to backup just several databases:
%>mysqlhotcopy [options] db_name_1 ... db_name_n /path/to/new_directory
Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:
%>mysqlhotcopy [options] db_name./regex/
The options can be viewed by executing the following command:
%>mysqlhotcopy --help
Examples :
Experiment with mysqlhotcopy by backing up the widgets database to the directory path "/usr/mysql/backups/". Execute the following command:
%>mysqlhotcopy -u root -p widgets /usr/mysql/backups
As a second example, assume that the widgets database contains the tables: "products2000", "products2001", "clientele2000", and "clientele2001", with the four digits at the end of each name representing the year for which that data represents. The administrator wants to backup only those tables relative to the year "2000":
%>mysqlhotcopy -u root -p widgets./^.+('2000')$/ /usr/mysql/backups In the above example, the regular expression /^.+('2000')$/ tells mysqlhotcopy to only backup those tables ending with the string "2000".
Further Reading
Conclusion
The goal of this tutorial was to introduce the essential topics which surround the basic functionality of the MySQL server. It is suggested that the novice reader devote some time to experimenting with all of the examples, as "learning by doing" is certainly the fastest way to become comfortable with MySQL.
Next time, we'll take a look at some general configuration issues, focusing on the my.cnf files first introduced at the beginning of this tutorial.
W.J. Gilmore is the author of A Programmer's Introduction to PHP 4.0 (January, 2001 Apress). He is the Assistant Editorial Director of Web and Open Source Technologies at Apress , and is a regular contributor to several of the Web's most prominent developer publications.
|
