Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 13 . Debugging and Repairing Databases 349 Cross-For further details of the mysqldump program, see Chapter 10. Reference For example, in Figure 13-18 the mysqldumpcommand runs to create a backup of all databases on the server and place the contents in a file called serverbackup.sql. The contents of the file include the DDL to create the databases and internal structure for the databases. The file also includes the DML and data contained in the databases. Figure 13-18: Using mysqldump to export the entire contents of a database server including DDL and DML The file created by mysqldump could then be saved or archived to a remote location or tape drive. Because mysqldump can be run from a remote machine, the file would automatically be a redundant backup in case of hardware failure on the MySQL server. If the hard drive fails on the MySQL server, the file produced by mysqldump on the remote machine would still be intact. The file produced by mysqldumpcan then be imported into a new MySQL server by simply reading it in through the CLI. Figure 13-19 shows an example of reading the output of a mysqldumpinto the CLI. No output or success message prints to the screen upon completion. Figure 13-19: Importing the output of a mysqldump file is as easy as reading it into the CLI.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

348 Part III . Administration Figure 13-17: Using the –dryrun or -n switch you can see what would happen with mysqlhotcopy without actually performing the backup. Other options for mysqlhotcopy include the following: Option Description –flushlog Performs the same functions as the flush-logs function with mysqladmin to reset any logging. Related to replication, –resetmaster Restarts the replication process. –resetslave Restarts the replication process. –noindices Excludes any index files from the backup. –tmpdir Changes the location of the temporary directory used by mysqlhotcopy (which is /tmp by default). Later this chapter provides an example of a script that automates the process of making backups with mysqlhotcopy. On the CD-ROM The script for automating backups with mysqlhotcopy is also located on the CD ROM included with this book. mysqldump With mysqldump, you can obtain a copy of the database structure, as well as the actual data for all databases on the server. The mysqldump command is flexible enough that you can specify structural units as small as tables and export them. Taking the output from mysqldumpand importing it back into MySQL, you can quickly reproduce an entire database server, along with all its data.
We recommend high quality webhost to host and run your jsp application: christian web host services.

Chapter 13 . Debugging and Repairing Databases 347 To copy more than one database at a time, you can use the –regexp switch to create a regular expression of matching databases. This switch uses a syntax similar to the one that tells mysqlhotcopy which tables to copy. Figure 13-15 shows an example of the –regexp switch in action. Caution The –regexp option does not run well currently; it may be fixed in a future version of mysqlhotcopy. Figure 13-15: The –regexp switch serves to copy more than one database at a time but is currently quite buggy. You can also specify a suffix for copied databases with the –suffixswitch. Doing so can help to keep a standard suffix for all backed-up databases. In Figure 13-16, for example, I use the –suffix switch to create a backup with an extension of _bak. Figure 13-16: Using the –suffix switch to create a backup with a special extension To see an example of what mysqlhotcopywould do given a set of arguments, use the -nor –dryrunswitch. The -nor –dryrunswitch provides a listing of what would happen without actually performing the operation. An example of the –dryrun switch is shown in Figure 13-17.
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

346 Part III . Administration To allow mysqlhotcopyto overwrite the destination database or directory, you use the –allowoldswitch. The –allowoldswitch changes the existing destination and appends _old to the name of the existing destination. When the copy is successfully completed, the old directory and files therein are deleted. Figure 13-13 shows an example of this behavior. Figure 13-13: The –allowold switch moves the existing destination and appends the extension _old to the name. After the copy is complete, the old directory is deleted. Although the –allowoldswitch enables you to perform a backup even if the stated destination already exists, mysqlhotcopydeletes the old copy as its final step in the backup process. If you want to keep the old copy after the command is complete, use the –keepoldswitch. Figure 13-14 shows mysqlhotcopy with the –keepold switch added as well as a listing of the MySQL data directory after the mysqlhotcopycommand runs to keep the old copy of the data using the –keepoldswitch. Figure 13-14: Adding the –keepold switch tells mysqlhotcopy to keep the old destination after performing a copy where the destination already exists.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

Chapter 13 . Debugging and Repairing Databases 345 At first, you might not think this method convenient or powerful enough for backing up tables (more than one table cannot be specified). However, by using the regular expression character for logical OR , you can back up more than one table name with mysqlhotcopy. In Figure 13-11, I re-run the command from Figure 13-10 and add the logical OR (or the pipe character) to the command because I want to back up the tables that contain the word card. Notice that the pipe character must be allowed to escape by using a backslash. Figure 13-11: Using the regular expression syntax for a logical OR you can back up more than one table with mysqlhotcopy. Up to this point I ve only been looking at the basic syntax of mysqlhotcopy. mysqlhotcopy includes a number of switches that directly affect the operation of the program. These switches can change which databases are copied, how they are copied, and any extra functions that mysqlhotcopyshould perform. When mysqlhotcopy makes a backup copy of a database the process fails if there is already a database of the same name. This has the obvious advantage of preventing an accidental overwrite of a vital database. Figure 13-12 shows an example of the error message when the destination database already exists. Note mysqlhotcopy indicates an error if the destination exists regardless of whether the destination is a database or a directory. Figure 13-12: If the destination exists, whether it is a database or directory, the mysqlhotcopy script fails and generates the error message shown.
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

344 Part III . Administration Figure 13-9: Using mysqlhotcopy to make a backup of a MySQL database to a directory mysqlhotcopy isn t limited to simply making backups of the entire database or even to one database. I ll look at how to back up more than one database within this section as well. For now, I m going to examine how to make a backup of only certain tables within a database. Again, recalling the syntax of the mysqlhotcopy program, you can specify tables within the format of a regular expression. In effect, you specify the tables that match a certain pattern that you want to back up. For example, to back up all tables in the ecommerce database that contain the word product, the command in Figure 13-10 is issued. Figure 13-10: Backing up only certain tables with the mysqlhotcopy command
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

Chapter 13 . Debugging and Repairing Databases 343 Figure 13-7: The MySQL server does not have sufficient privileges to read the database of a recently backed up database with mysqlhotcopy. The error in Figure 13-7 occurs because the mysqlhotcopyscript was run as the root or superuser. Therefore the new database directory was created by and is owned by root. Because the database server files are owned by the username mysql and the server runs as that user it cannot read the contents of the new database directory. You can fix this with a command such as chown. For example, Figure 13-8 shows the directory ownership and permissions before and after the chown command in Linux. Figure 13-8 also shows a successful connection to the backup copy created by mysqlhotcopy. Figure 13-8: Changing ownership of the new database created by mysqlhotcopy The other method for backing up a database is to another directory. For added precaution, the directory could be on a separate physical disk. Instead of providing a database name for mysqlhotcopy to back up to, you specify a directory name. You must make sure that the directory exists. Figure 13-9 shows an example of using mysqlhotcopy to make a backup of a database to a directory. In the first attempt, the directory does not exist. The second attempt is successful. A directory listing is provided for convenience.
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

342 Part III . Administration mysqlhotcopy also copies any other files within the directory housing the database as well. For example, if you have files such as those produced with mysqldump, performing a mysqlhotcopyof the database copies those to the new location. Figure 13-6 shows an example of this behavior. A listing of the ecommerce database directory is done, then a mysqlhotcopy to make a backup of the database into ecommercebak, followed by a listing of the new ecommercebak directory. Figure 13-6: The mysqlhotcopy script copies all files located in the database s directory on the server. Recall the basic syntax for the mysqlhotcopy program: mysqlhotcopy [./
/] [ | ] You have two possible destinations for the backup: . A new database . A directory in a different location The examples in Figures 13-5 and 13-6 each made new databases. However, attempting to use either database can result in an error message, as shown in Figure 13-7.
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

Chapter 13 . Debugging and Repairing Databases 341 At its most basic level, the mysqlhotcopy script performs the following three steps: 1. Locks the table or tables specified. 2. Flushes the tables to close any open tables. 3. Performs the actual copying of the data files. . Because mysqlhotcopyperforms a lock tables as the first step, no other clients can use the tables while mysqlhotcopy runs. . The locking can take quite some time on large tables. . The flushing of the tables will cause all open tables to close. . The actual copy can be time-consuming if the tables are large as well. The mysqlhotcopyscript makes an actual electronic copy of the datafiles for the database. Therefore any user account that runs mysqlhotcopymust have sufficient privileges to read the directory in which the datafiles reside. For example, Figure 13-4 shows an attempt to run mysqlhotcopyas a normal user in Linux. Normal users don t have the requisite privileges on the database directory; the command fails. Figure 13-4: mysqlhotcopy requires that the user running the script have sufficient privileges to read the database directory. A simple summary (a common usage of the mysqlhotcopyprogram) is shown in Figure 13-5. In the figure, the MySQL grantsdatabase (mysql)is copied or backed up to a new database called mysqlbak. Figure 13-5: A common usage of the mysqlhotcopy program to make a copy of the MySQL grants database
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

340 Part III . Administration One notable exception in the behavior of the switches is the -por –password switch. Normally, programs such as the MySQL CLI and mysqladminprompt for a password when you use the -pswitch (as shown in Figure 13-2). You could also specify the password on the actual command line, also illustrated in Figure 13-2. Figure 13-2: The variations of the types of password entry methods for most MySQL commands Unlike most programs that come with MySQL, mysqlhotcopydoesn t prompt for a password. Rather, the password must be specified on the command line. In addition, if you use the -p switch mysqlhotcopyexpects a space after the -p. Figure 13-3 shows an example of this behavior. If the space isn t given after the -pswitch, an Invalid Optionerror message results. Figure 13-3: Attempting to give the password without a space after the -p switch results in an error.
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.