Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

260 Part II . SQL Essentials Note Cross- Reference Replication takes updates to data from one server and automatically copies them in real-time to another MySQL server. Chapter 18 covers replication. If you are not using replication, these functions are of no use. Exporting Data Two main methods are available for exporting data from a MySQL database: . mysqldump . the SELECT INTO OUTFILEstatement. Whether you re looking to make a simple backup of the server or export certain columns from a table into another program such as Microsoft Access, the exporting utilities and statements included with MySQL will help. You can also use a program called mysqlhotcopy to make a copy of a database. In addition, through the use of the MySQL CLI in non-interactive mode or with the Tee option, results can be saved or redirected into a file. mysqldump The mysqldump utility is a powerful program for extracting database and table structure along with the actual data. The mysqldumputility is sufficiently granular so that you could get the table structure and data from just one table or from all tables of all databases. If you would just like the table structure with no data you can do that as well. If you want just the data with no table structure, mysqldump also provides that functionality. The basic syntax for mysqldump is as follows: mysqldump [OPTIONS] database [tables] Cross- Reference Note When given no arguments, mysqldump simply prints a syntax reference. There are quite a few options with mysqldump. Some of the options and switches such as those for specifying the username and password have been discussed in other chapters. If you are unsure how to specify a username or password with MySQL utilities, please refer to Chapter 8 for more details. There are two variables that can affect your ability to dump tables with large columns: max_allowed_packet and net_buffer_length. Both can be set using the -Oor –set-variable switch on the mysqldump command line or in the MySQL configuration file. The mysqldump utility uses comments (escaped with /* */) to select options that may only be found in newer versions of MySQL. For example, when creating a
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Chapter 10 . Databases And Data 259 Figure 10-25: Examples of changing passwords with the mysqladmin password command Stopping the MySQL server with mysqladmin The shutdown function stops the MySQL server through the mysqladmincommand. As with any shutdown, the command has to wait until threads are through with tables; this requirement protects the integrity of the data. The shutdown function provides no feedback to the user, see Figure 10-26. Figure 10-26: The mysqladmin shutdown command provides no feedback to the user upon success. Replication functions with mysqladmin There are two main functions of mysqladmin for use with replication. These are the start-slave and stop-slavefunctions. These functions start and stop a slave replication process.
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

258 Part II . SQL Essentials . The ping function simply tells the administrator whether the server, mysqld, is alive. If the server is not alive, the command returns an error. In Figure 10-24 I run the command on the server while it is running, then secretly stop the server in another window (see Figure 10-26) and re-run the ping function again. Figure 10-24: The mysqladmin ping command in action. The first attempt is successful; the second fails because I shut the MySQL server down in another window. Changing passwords through mysqladmin The mysqladmin command can also be used to set or change passwords for users in the MySQL database. In fact, this is how you changed the initial root password for the server back when it was installed. The syntax for the password function can sometimes cause confusion. The syntax is as follows: mysqladmin (-u username) (-p) password newpassword The -u username switch is necessary if you do not want to change the password for the current user. If you need to supply an existing password for the user account specified with the -u username switch (or for yourself if you don t use the -u usernameswitch), then you must use the -p switch to have mysqladmin prompt for a password. Next the function name, password, is required. Finally, the new password is given as the last argument. The new password must be quoted. I ve prepared a couple of examples, shown in Figure 10-25. The first example changes the password for the current user, which happens to be suehring. The second example changes the password for the root user. Both are localhost changes.
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

Chapter 10 . Databases And Data 257 3. Finally, in Figure 10-22 I attempt to run the same SELECT statement as in Figure 10-20. Notice the Error message produced by the CLI. Figure 10-22: Issuing the same SELECT statement as in Figure 10-20 results in an error but the statement runs. Information functions . The version function informs the user what version of the server is running along with some other pertinent information about the server, including status and uptime information. An example is shown in Figure 10-23. Figure 10-23: Output from the mysqladmin version command shows the server version along with other useful information on server performance.
You want to have a cheap webhost for your apache application, then check apache web hosting services.

256 Part II . SQL Essentials Here s a simple example of the kill function: 1. Using the MySQL CLI, I connect to the MySQL CLI and issue a simple SELECT statement, as shown in Figure 10-20. Figure 10-20: Connecting to the MySQL CLI and performing a basic SELECT statement 2. In Figure 10-21 I run the mysqladmin processlist command to determine the ID of the other client connection. As you can see it is 139 as shown by the id column of the output. Also in Figure 10-21 I issue the mysqladmin kill command to terminate the process. Figure 10-21: Using the mysqladmin processlist and mysqladmin kill commands to terminate a client process
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

Chapter 10 . Databases And Data 255 Figure 10-19: Another snapshot of server parameters immediately after the flush-status function . refresh The refresh function is a combination of the flush-tables and flush- logs functions. . flush-privileges A frequently used function, though maybe not via the mysqladmin command, is the flush-privilegesfunction. The flush-privileges function causes the GRANTStables to be reloaded, making MySQL aware of any changes to those tables. If statements working with those tables are done with a GRANTor REVOKEstatement, using the flush-privilegesfunction in unnecessary. However, if using any other SQL statement on any of the grants tables, you must flush-privilegesor optionally reload the server. . reload The reload function is a synonym for the flush-privileges function. Killing MySQL processes The word kill sounds somewhat harsh, but this is the name of the function to terminate a client process or thread in the server. Using the mysqladmin processlist command, you can determine the id of a client or process that needs to be terminated. Issuing the mysqladminkill function terminates that process. This does not stop the client from reconnecting; in fact, the CLI reconnects automatically.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

254 Part II . SQL Essentials . flush-logs MySQL also keeps a number of logfiles. These can include update logs and binary update logs. The flush-logsfunction purges logfiles and restarts the logging process. For update logs done with the standard numbering extension, the new logfile has the last logfile s extension incremented by one. The flush-logsfunction does not touch the error log, .err, typically located in your data directory. . flush-status This function takes status typevariables and resets them to zero useful for debugging server performance and parameters. Figure 10-18, for example, shows partial output from the extended-statusfunction after I run the flush-statusfunction. Figure 10-19 shows the results of another run of the extended-statusfunction immediately after I run the flushstatusfunction. The command to flush the status variables is mysqladmin flush-status. Figure 10-18: A snapshot of server parameters prior to running the mysqladmin flush-status command . flush-tables The flush-tables function should be used with care. This function closes any and all open tables. Even if a table is in use, the flush-tables functions closes those as well. This can result in errors to the client. I ve found that this function sometimes must be used on extremely busy servers that seem unresponsive to a shutdown command.
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

Chapter 10 . Databases And Data 253 Server version Sizes of caches and buffers An example of the output from the mysqladminvariables command is shown in Figure 10-17. The command to reproduce this output is mysqladmin variables. Figure 10-17: The output from the mysqladmin variables command shows the options that the MySQL server uses. Functions for clearing server parameters The mysqladmin command provides numerous functions for flushing or clearing server parameters. The functions are as follows: . flush-hosts This function clears the MySQL DNS host cache. By default MySQL stores the connecting hosts to IP resolution. If you change the host s DNS entry or a number of errors occur during a connection, the host may be blocked by MySQL. Performing a mysqladminflush-hostsoperation clears the DNS host cache and solves the problem.
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

252 Part II . SQL Essentials A dbcolumn indicates whether the command or statement involved a database. The Command column indicates the type of command that was run. The Time column indicates the time of the connection. The State column indicates what the process is doing, from waiting, to opening connection, establishing communication, and so forth. The Info column shows what statement or command the process is running. An example of the processlist function is shown in Figure 10-16. Figure 10-16: The MySQL processlist function for use with the mysqladmin command Tip Using the processlist function I was able to troubleshoot a problem where a developer was opening multiple connections to the database and closing none of them. The clients would issue a command and then sit idle until they timed out. The server would reach its client connection limit quickly and start refusing legitimate connections. The processlist function showed that there were a number of connections from the application user and the connections remained open. By changing the connection_timeoutvalue and restarting the server, I was able to solve the problem. Without the processlist command troubleshooting this issue would have been difficult. . Variables. The variables function outputs the various options and server parameters that the MySQL server operates with. These include parameters like Connection timeouts Types of tables included with the server
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 251 . Extended-status. As you might expect, the extended-statusfunction returns much more information about server parameters and performance than does the statusfunction. When you tune server variables to get better performance from the MySQL server, the extended-status function is frequently useful. Cross-Chapter 11 and Chapter 13 cover tuning your server parameters. Reference Figure 10-15 shows an example of part of the output from the extended -status function. Figure 10-15: Partial output from the mysqladmin extended-status command . Processlist. The processlist function lists the currently active or open connections to a MySQL server. These can be from applications, the CLI, other MySQL servers, or through any other communication with the MySQL server. The eight columns in the processlistfunction output provide a snapshot of activity on the database server at the instant the command was run. The Idcolumn is a counter of the connections to the database server and is incremented with each new connection. The User is the user performing the action. The Host is where the user s connection originated.
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.