Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

142 Part II . SQL Essentials Figure 8-11: The –print-defaults switch shows you which environment variables MySQL uses for your session. Figure 8-12: Creating a .my.cnf file in my home directory so I can set defaults for the MySQL CLI In Figure 8-13, the output of the –print-defaults switch, note the new socket name actually two socket defaults. Because local options are read last, the last socket option to be read is applied.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision web design programs services

Chapter 8 . Command Line Interface (CLI) 141 Figure 8-10: Using the CLI and specifying a database from within the CLI MySQL CLI Environment Variables Regardless of whether you use the CLI in interactive mode, variables are available to control how the CLI operates. When read from configuration files, those variables can be set globally with the use of configuration files in particular, my.cnf or my.ini(usually located in the /etcor /usr/local Linux directory or the WINNT directory in Windows 2000). If you want to set the variables on a per-user basis, a .my.cnf file in each user s home directory can provide customized individual use of the MySQL CLI. Make sure that a dot (.) immediately precedes each user s .my.cnf filename for example, .my.cnf. Since the configuration file can reside in each user s home directory, it is only applied when logged in as that user. Determining and changing default variables To determine the variables the MySQL CLI uses for a particular session, use the –print-defaults switch, as shown in Figure 8-11. Changing a default variable is as simple as editing the configuration file. For example, if I want to change the socket that MySQL connects through from the default /tmp/mysql.sock, I could make a .my.cnf file (as shown in Figure 8-12) and include a section for the MySQL CLI in brackets with the socket option underneath. Note
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision make web site services

140 Part II . SQL Essentials Figure 8-9: Use the -h switch to connect to another MySQL server or leave it blank to connect to the local machine. If you want to set a default host other than localhost without having to specify the -h switch every time you can set the host inside the configuration file, see the discussion of environment variables (later in this chapter) for details on how to set and change default variables for the MySQL CLI. Any host specified in a configuration file is overridden if you specify another -h on the command line. Specifying the database The MySQL CLI enables you to specify the database to use without the addition of any switches to the command line. The command expects the database to be specified as the last part of the command line. For example, to use a database called inventory, simply add it to the command at the end, like this: mysql -p -u suehring inventory If no database is specified on the command line (assuming you have access privileges), you return automatically to the MySQL CLI in interactive mode but not connected to any database. To connect and use a database, issue the connect statement. In Figure 8-10, I connect to the MySQL server without specifying a database. I can then connect to a database by using the connect statement. If you want to specify the database at a location other than the end of the command line, you can use the -Dor –database=switches. To set a default database for MySQL to use upon startup, add a database= to the MySQL configuration file, see the section on Environment Variables in this chapter for details on how to set and change default variables for the MySQL CLI.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision christian web host services

Chapter 8 . Command Line Interface (CLI) 139 Figure 8-7: Specifying the password immediately following the -p switch prevents the CLI from prompting for a password. Figure 8-8: The syntax for the –password switch is slightly different from the -p switch. The password=
switch can be added into the MySQL configuration file and thus saved between sessions. This is also a security risk. Alternatively, you could simply place the word password under the [mysql] section of the configuration file and you are prompted for the password just as if you used the -p switch. (See this chapter s MySQL CLI Environment Variables section for more about working with CLI variables.) Specifying the host In much the same way that MySQL uses the current user if the -uswitch is not used, not using the -h switch makes MySQL assume you mean to connect to the server on the localhost (the current machine). If you want to connect to a MySQL server on another machine, you can specify the IP address or hostname of the other MySQL server via the -hswitch, as shown in Figure 8-9.
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision personal web hosting services

138 Part II . SQL Essentials Figure 8-6: MySQL uses the username of the current user if the -u switch is not used. Accordingly, if you are using the CLI as the rootuser, MySQL attempts to connect as the rootuser if the -u switch is not used. If you want to override the default user and have the MySQL CLI authenticate as a different user every time, you can add the username=clause to the MySQL configuration file. Cross-For details on how to set and change default variables for the MySQL CLI, see the Reference MySQL CLI Environment Variables section later in this chapter. Specifying the password One source of confusion for some is the use of the -pswitch to specify the password. Often people believe that what follows the -p is actually the password but this isn t always the case. The -pswitch simply tells the mysql program to prompt for the password if none is given. In the example in Figure 8-5, a space appears after the -pswitch it s prompting you for the password. If you want to specify the password on the command line, you can do so by placing the password immediately after the -p switch, as shown in Figure 8-7. Caution Specifying the password on the command line should be considered a security risk because the password is right in plain sight for a would-be attacker to see. Additionally, the –passwordswitch can be substituted for the -p switch and is used much the same way. If you provide just the –passwordswitch you are prompted for the password. However, unlike the -pswitch, the –password syntax requires you to specify the password on the command line by using an equals sign (=), as shown in Figure 8-8.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision web and email hosting services

Chapter 8 . Command Line Interface (CLI) 137 How you start the MySQL CLI depends largely on what you want to do. To work with a database interactively, you have to supply credentials to establish a connection. Normally those credentials come in the form of a username and a password though they can include an optional name of the database you re working with, as well as the hostname (or IP address) of the server. In Figure 8-5, for example, I m connecting to the local server (localhost) as username suehringand telling the program to perform two tasks: Ask me for a password and connect me to the ecommerce server. Figure 8-5: Connecting as username suehring to the ecommerce database and specifying a password Cross-The examples throughout this chapter assume that the user account you re using Reference has sufficient access privileges to perform the operations. If this is not the case, consult Chapter 12 for a description of how to add a user account with specified privileges. Specifying the username When using mysqlin Linux, normally you need not add the -u(username) switch. If the -u username switch is not specified, the mysqlcommand uses the username from your environment variables. For that authentication to work, the MySQL server must have a user entry with correct privileges in its user table. In Figure 8-6, the -uswitch is not used, so MySQL grabs the username suehringfrom the environment variables. Because a user suehring exists in the MySQL database and has the proper privileges, I can connect to the database server after supplying the correct password.
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision ftp web hosting services

136 Part II . SQL Essentials Figure 8-4: Using the -e switch to execute a query in non-interactive mode Although it is possible to execute other commands with the -eswitch, I don t normally recommend executing DDL (Data Definition Language) commands such as ALTER TABLE with the -e switch. MySQL still prints error messages back to the command line, but I ve found that DDL commands usually require more than one command to be run (for example, a command to describe a table) before alteration can begin. Command recall reusing the command or statement you last typed by pressing the up-arrow on the keyboard is one of the best features of the MySQL CLI. This feature saves countless keystrokes when you make a typographical or spelling error in the middle of a long query and have to run it again. Additionally, command recall makes large and complex queries easier to work with; if you find that the output is not exactly as you want it, simply use the up-arrow and tweak the query when it appears on-screen. Starting the CLI the Basics By now you ve probably already been introduced to the CLI through some earlier chapters or the introduction in this chapter. Don t worry if you haven t read those chapters; the CLI itself makes only a brief appearance there. The command to start the MySQL CLI is contained normally in the bin directory of the MySQL installation. However, it may be located in another directory depending on how you installed MySQL or the operating system you are using. In Linux, I ve seen the command located in /usr/bin, /usr/local/bin, /usr/local/mysql/bin and somewhere in the /varmount.
Note: If you are looking for cheap and reliable webhost to host and run your web application check Vision coldfusion web hosting services

Chapter 8 . Command Line Interface (CLI) 135 . Troubleshooting queries . Adding users to MySQL . Analyzing tables . Analyzing queries . Altering tables . Checking replication status . Looking at server health The mysql command can also be used in non-interactive mode, directly from the command line or command prompt. The CLI is also useful in non-interactive mode, as shown in Figure 8-3. Figure 8-3: The CLI in non-interactive mode in Linux In non-interactive mode, the CLI provides many of the same functions as in interactive mode. However, in non-interactive mode you don t receive the same feedback from the server (along with the results). For example, if you perform an INSERT operation using the -eswitch in non- interactive mode, you won t receive a message that tells you the number of rows inserted. When you insert rows in interactive mode, the server gives you feedback that includes the number of rows from the statement. Some CLI switches are useful only in non-interactive mode, most notably is the -e or –executeswitch. An example of the -e switch is shown in Figure 8-4. In the example in Figure 8-4, the -pswitch specifies that MySQL should prompt for a password and the word mysqlat the end of the command indicates the database I d like to connect to and within which I plan to execute the query that appears in quotes after the -e switch. (I provide more information on these switches, and on the syntax of the CLI, later in this chapter.)
Note: If you are looking for reliable webhost to maintain and run your java application check Vision java hosting services

134 Part II . SQL Essentials Figure 8-1: Here I use the help switch to remind myself of the correct syntax for the mysql command. The CLI can be used in interactive mode where you are talking directly with the server and it is talking back to you, in a sense. In interactive mode the commands you type are sent directly to the server and the results are given back to you within the CLI. An example of the CLI in interactive mode is in Figure 8-2. You can obtain help from within in CLI by typing help; or h. Figure 8-2: The CLI in interactive mode Interactive-mode CLI is a great tool for troubleshooting database problems. For example, you can use the CLI in interactive mode to make sure queries return the expected results or see why they aren t running correctly. You can use commands to analyze tables and queries with the CLI and you can alter tables to add indexes, change structure, and more. In addition, when using the CLI in interactive mode a history file is recorded. Not only is this a great way to save keystrokes for frequently (or infrequently) used commands, but it can also provide a means (though insecure) of tracking what a user did to break something. Using the CLI interactively makes a number of database tasks easier, including these:
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision web design programs services

Command Line Interface (CLI) The MySQL Command-Line Interface abbreviated CLI and also known as the command-line tool is the program through which the users handle most of their daily interactions with the database. You access the CLI by issuing the mysql command. This chapter looks at some normal CLI tasks, as well as some that may not be obvious but are still useful. An introduction to the CLI lays the groundwork; before long, you can get your hands dirty with some real functions and try out some uses of the CLI. Introducing the CLI Before the advent of GUIs and third-party tools, the MySQL CLI provided the only way to interact with the database. However the CLI offers advantages over some of its newer counterparts insofar as it is tested and stable, it is lightweight, and it comes standard with most installations of MySQL. The basic syntax for to use the MySQL CLI is as follows: mysql (options) [database] The options for this command help make the MySQL CLI configurable as well as powerful. Should you forget the options and not have this book available, you can get help from the mysql command itself by adding the –helpor -? switches after you type it, as shown in Figure 8-1. 8 88 CHAPTER …. In This Chapter Introducing the CLI Starting the CLI basics Using environment variables in MySQL CLI Choosing when to use the CLI Interactive mode Configuring a useful MySQL CLI environment Avoiding common CLI user errors Listing alternatives to the CLI ….
Note: In case you are looking for affordable webhost to host and run your web application check Vision cheap hosting services