Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

270 Part II . SQL Essentials the –all-databasesmacros is that you can use the –databases option to specify only the databases you d like to use; the –all-databasesoption includes all databases. Figure 10-38: Taking the mysqldump command and redirecting the output to a file The –databases function expects at least one database name to follow as an argument. If you want to specify more than one database, separate the database names single spaces. In Figure 10-39 the –databasesfunction serves to produce a dump of the MySQL grants database and the ecommerce database example. Normally I would redirect the output to a file (as shown in Figure 10-38); the command that produces these results is as follows: mysqldump -p –databases mysql ecommerce Figure 10-39: Using the –databases function to produce a dump of two databases
Check Tomcat Web Hosting services for best quality webspace to host your web application.

Chapter 10 . Databases And Data 269 Figure 10-37: Using the -A or –all-databases macro to dump the contents of all databases and data on the server. As you can see by Figure 10-37, all output was sent to the screen. Oops! If there were any large tables on the server, the output could have taken quite some time. Using mysqldumpavoids tying up the screen by redirecting the output to a file. As stated previously, in Linux you use the greater-than sign (>) to accomplish this redirection. For example, to rerun the –all-databases macro from the example in Figure 10-37, the command would be mysqldump -A -p > outfile.txt Performing that command produces a file that could be imported onto another server or servers to create a snapshot of the database server at that point in time. The file could also be used as a backup as well. Figure 10-38 shows another run of the command, followed by a look at the resulting data. The databases function The second macro-level function with mysqldumpis the –databases or -Bfunction. This option takes databases as arguments, producing the same type of output as the –all-databases macro. Specifically, the –databases function includes all DDL to create the database(s) and table(s) as well as the USE statements to connect to those new databases. The difference between the –databases and
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

268 Part II . SQL Essentials Figure 10-35: The mysqldump -p ecommerce –lines-terminated- by= HELLO –tab=outputdir command is run to terminate lines by the word HELLO. Figure 10-36: The mysqldump -p ecommerce –fields-enclosed-by= \ –tab=outputdir command to produce output with a backslash around the fields. Note the use of two slashes in the actual command. The all-databases function There are two macro-level functions to dump all databases with mysqldump. With such functions it is possible to make a backup copy of an entire database server though MySQL AB recommends that you use the mysqlhotcopy utility for this purpose. Cross-The mysqlhotcopy utility for making backups is covered in Chapter 14. Reference The first option, –all-databases or -A, dumps all databases, including database structure and data. The –all-databasesoption also creates the actual databases as well. In Figure 10-37 the –all-databases macro serves to dump all databases contained on my example server. The command to run is as follows: mysqldump -A -p
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

Chapter 10 . Databases And Data 267 The –line-and –fields-options require the use of the –tab=switch. Each option is sufficiently self-explanatory. If you want to terminate or enclose a field of the output with a string you can do so with these functions. Also, things like nfor newline and t for tabare supported, so you can separate fields or lines by those characters as well. The keyword OPTIONALLYused with ENCLOSEDcauses only CHARand VARCHARcolumn types to be enclosed in the specified manner. Examples of various mysqldumpcommands that use these switches are shown in Figures 10-33 through 10-36. Figure 10-33: Performing the command: mysqldump -p ecommerce –fields-terminated-by=+ –tab=outputdir to produce output with fields terminated by a plus sign (+) Figure 10-34: Running the command mysqldump -p ecommerce -fields- terminated-by= n –tab=outputdir to separate all fields by a newline (n)
Check Tomcat Web Hosting services for best quality webspace to host your web application.

266 Part II . SQL Essentials –fields-terminated-by= –fields-enclosed-by= –fields-optionally-enclosed-by= –fields-escaped-by= –lines-terminated-by= The first switch, –quote-names, places backquotes (`) around the names of tables and databases. The second switch, –tab=, can also be abbreviated as -T. The –tab=switch causes mysqldumpto create two files, placing output from each table in the argument list. One file
.sqlis the normal output of the DDL that you would expect from mysqldump. The second file,
.txt, is a tab-delimited file containing only the data from the table. Tip The –tab= switch can be useful for importing data into other applications such as Microsoft Access. The –tab= or -T switch expects a directory or path as an argument, in effect, using –tab=outputdirwill cause the results to be placed in a directory called outputdir. It is important that MySQL have permission to write into that directory. For this reason, the –tab= switch only works when run from the server that mysqld runs on. A few examples of the –tab=switch are shown in Figure 10-32. The first attempt is unsuccessful because the specified directory does not exist. In the second attempt, the MySQL server does not have permission to write into the directory specified. The final attempt is successful. Figure 10-32: Three examples of use of the –tab= switch, two unsuccessful and one successful
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

Chapter 10 . Databases And Data 265 Figure 10-31: The -d or –no-data switch causes mysqldump to exclude the DML or INSERT statements from the output. Caution Using the –add-drop-table switch will cause all data to be lost in any tables that are removed. mysqldump also includes a switch that can specify a number of the previously discussed options as well as others. The switch is the –opt switch and includes the switches –add, –add-drop-tables, –add-locks, –extended-insert, -lock- tables, and –quick. Tip If you are going to use the –add, –add-drop-tables, –add-locks, -extended- insert, –lock-tables, or –quick switch, specifying the –opt switch is a timesaver. Special formatting functions with mysqldump With mysqldumpyou can specify a number of options that determine how the output will look. These include special quotation of fields in the output, escaping or enclosing the fields in a special manner, and terminating lines and fields with the desired character. The switches or options to specify on the mysqldumpcommand line are as follows: –quote-names –tab=
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

264 Part II . SQL Essentials Figure 10-30: Examples of the types of INSERT statements that can be produced by mysqldump Using the -d switch, a mysqldump will simply output the table (and possibly database) creation statements. This feature can be useful for making a skeleton copy of the database and table structure on another server. Figure 10-31 is an example of the -d switch in action, producing the DDL for the ecommercedatabase. The command for this example is as follows: mysqldump -p -d ecommerce Adding drops and locks mysqldump can be configured to add statements to lock and unlock tables around INSERT statements. Doing so ensures that the INSERT will complete without an unexpected change in the data or contention for the table. The switch to enable the locking function is –add-locks. You can also lock the tables around the reads that produce the output of mysqldump. The switch -lor –lock-tablesturns this function on. The –add-drop-tableswitch tells mysqldumpto add a DROP TABLEstatement just before creating the table. Adding this switch can be helpful if you already have tables of the same name that do not contain useful data.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

Chapter 10 . Databases And Data 263 Figure 10-29: Using the –where= switch to specify only certain records to be dumped Suppressing DDL In the examples in Figure 10-30, you may have noticed an extra option: -t (also known as –no-create-info), which suppresses the DDL or the statements that would otherwise create the table. The -t option can be useful for making a quick backup of the data in a table. For example, if you have another server that contains the same table structure and only want to migrate your data to the new server, you could suppress the DDL with the -toption. The result is the DML or statements to insert the data into the table or tables. Suppressing DML and data Just as you might want to suppress the printing of table creation statements in the output of a mysqldump, you might also want to suppress the actual DML or insert statements from the output as well. The switch for this option is -d or –no-data.
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

262 Part II . SQL Essentials Figure 10-28 shows examples of both redirect methods. Figure 10-28: Examples of redirecting output via the greater-than sign and the -r switch Specifying records to be dumped You can even get more specific than a particular table with mysqldump. The use of the –where= or -wswitch enables you to add a SELECT statement. For example, to dump only the contents of the user table of the MySQL grants database where the user is suehring , the command in Figure 10-29 could be issued: mysqldump -p –where=user= suehring mysql user The where clause can be longer than one argument. For example, the following command is valid: mysqldump -p –where=user= suehring and host = localhost mysql user Controlling the INSERT statement Two options control how any INSERT statements are done with mysqldump. These options are -c(–complete-insert), or -e (–extended-insert). The default is extended-insert. Examples with both types of insert options are shown in Figure 10-30. The commands to reproduce these examples are as follows: mysqldump -t -p -e –where=user= suehring and host = localhost mysql user mysqldump -t -p -c –where=user= suehring and host = localhost mysql user
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

Chapter 10 . Databases And Data 261 database the IF NOT EXISTS keywords were introduced in version 3.23.12 of MySQL. The keywords IF NOT EXISTS will be enclosed in an escape sequence along with the version number. This allows the functionality of IF NOT EXISTS to be used by those versions that can handle it and safely ignored without error on those versions that do not include the function. Normal usage of mysqldumpcalls for the command along with options such as user- name and password followed by the name of the database to dump and (optionally) specific tables and columns to dump. In Figure 10-27 the mysqldumpcommand is issued to dump just the dbtable of the MySQL grants database. The command is as follows: mysqldump -p mysql db Figure 10-27: Using mysqldump to obtain the DDL and DML for one table of one database For most of its uses, mysqldumpredirects the output to a file instead of sending the output to stdoutor to the screen (which is the default). The characters used to redirect output can vary by OS: . In Linux, you perform redirect operations with the greater-than (>) sign. . In Windows, use the -ror –result-file= switch with mysqldump, which assists the newline and carriage return.
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.