PHP MySql Web Hosting

One way to tell your shell where to find mysql is to type its full pathname each time you run it. The command might look like this under Unix: % /usr/local/mysql/bin/mysql Or like this under Windows: C:> C:mysqlbinmysql Typing long pathnames gets tiresome pretty quickly, though. You can avoid doing so by changing into the directory where mysql is installed before you run it. However, I recommend that you not do that. If you do, the inevitable result is that you’ll end up putting all your datafiles and query batch files in the same directory as mysql, thus unnecessarily cluttering up what should be a location intended only for programs. A better solution is to make sure that the directory where mysql is installed is included in the PATH environment variable that lists pathnames of directories where the shell looks for commands. (See Recipe 1.9.) Then you can invoke mysql from any directory by entering just its name, and your shell will be able to find it. This eliminates a lot of unnecessary pathname typing. An additional benefit is that because you can easily run mysql from anywhere, you will have no need to put your datafiles in the same directory where mysql is located. When you’re not operating under the burden of running mysql from a particular location, you’ll be free to organize your files in a way that makes sense to you, not in a way imposed by some artificial necessity. For example, you can create a directory under your home directory for each database you have and put the files associated with each database in the appropriate directory. I’ve pointed out the importance of the search path here because I receive many questions from people who aren’t aware of the existence of such a thing, and who consequently try to do all their MySQL-related work in the bin directory where mysql is installed. This seems particularly common among Windows users. Perhaps the reason is that, except for Windows NT and its derivatives, the Windows Help application seems to be silent on the subject of the command interpreter search path or how to set it. (Apparently, Windows Help considers it dangerous for people to know how to do something useful for themselves.) Another way for Windows users to avoid typing the pathname or changing into the mysql directory is to create a shortcut and place it in a more convenient location. That has the advantage of making it easy to start up mysql just by opening the shortcut. To specify command-line options or the startup directory, edit the shortcut’s properties. If you don’t always invoke mysql with the same options, it might be useful to create a shortcut corresponding to each set of options you need for example, one shortcut to connect as an ordinary user for general work and another to connect as the MySQL root user for administrative purposes.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

mysql first reads your option file to see what connection parameters are listed there, then checks the command line for additional parameters. This means you can specify some options one way, and some the other way. Command-line parameters take precedence over parameters found in your option file, so if for some reason you need to override an option file parameter, just specify it on the command line. For example, you might list your regular MySQL username and password in the option file for general purpose use. If you need to connect on occasion as the MySQL root user, specify the user and password options on the command line to override the option file values: % mysql -p -u root To explicitly specify “no password” when there is a non-empty password in the option file, use -p on the command line, and then just press Return when mysql prompts you for the password: % mysql -p Enter password: press Return here 1.8 What to Do if mysql Cannot Be Found 1.8.1 Problem When you invoke mysql from the command line, your command interpreter can’t find it. 1.8.2 Solution Add the directory where mysql is installed to your PATH setting. Then you’ll be able to run mysql from any directory easily. 1.8.3 Discussion If your shell or command interpreter can’t find mysql when you invoke it, you’ll see some sort of error message. It may look like this under Unix: % mysql mysql: Command not found. Or like this under Windows: C:> mysql Bad command or invalid filename

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

If you want to find out which options will be taken from option files by mysql, use this command: % mysql –print-defaults You can also use the my_print_defaults utility, which takes as arguments the names of the option file groups that it should read. For example, mysql looks in both the [client] and [mysql] groups for options, so you can check which values it will take from option files like this: % my_print_defaults client mysql 1.6 Protecting Option Files 1.6.1 Problem Your MySQL username and password are stored in your option file, and you don’t want other users reading it. 1.6.2 Solution Change the file’s mode to make it accessible only by you. 1.6.3 Discussion If you use a multiple-user operating system such as Unix, you should protect your option file to prevent other users from finding out how to connect to MySQL using your account. Use chmod to make the file private by setting its mode to allow access only by yourself: % chmod 600 .my.cnf 1.7 Mixing Command-Line and Option File Parameters 1.7.1 Problem You’d rather not store your MySQL password in an option file, but you don’t want to enter your username and server host manually. 1.7.2 Solution Put the username and host in the option file, and specify the password interactively when you invoke mysql; it looks both in the option file and on the command line for connection parameters. If an option is specified in both places, the one on the command line takes precedence. 1.7.3 Discussion

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

doesn’t take any value (such as for the no-auto-rehash option), the name is listed by itself with no trailing =value part. If you don’t need some particular parameter, just leave out the corresponding line. For example, if you normally connect to the default host (localhost), you don’t need any host line. If your MySQL username is the same as your operating system login name, you can omit the user line. In option files, only the long form of an option is allowed. This is in contrast to command lines, where options often can be specified using a short form or a long form. For example, the hostname can be given using either -h hostname or -host= hostname on the command line; in an option file, only host=hostname is allowed. Options often are used for connection parameters (such as host, user, and password). However, the file can specify options that have other purposes. The pager option shown for the [mysql] group specifies the paging program that mysql should use for displaying output in interactive mode. It has nothing to do with how the program connects to the server. The usual group for specifying client connection parameters is [client]. This group actually is used by all the standard MySQL clients, so by creating an option file to use with mysql, you make it easier to invoke other programs such as mysqldump and mysqladmin as well. You can define multiple groups in an option file. A common convention is for a program to look for parameters in the [client] group and in the group named after the program itself. This provides a convenient way to list general client parameters that you want all client programs to use, but still be able to specify options that apply only to a particular program. The preceding sample option file illustrates this convention for the mysql program, which gets general connection parameters from the [client] group and also picks up the no-auto-rehash and pager options from the [mysql] group. (If you put the mysql-specific options in the [client] group, that will result in “unknown option” errors for all other programs that use the [client] group and they won’t run properly.) If a parameter is specified multiple times in an option file, the last value found takes precedence. This means that normally you should list any program-specific groups after the [client] group so that if there is any overlap in the options set by the two groups, the more general options will be overridden by the program-specific values. Lines beginning with # or ; characters are ignored as comments. Blank lines are ignored, too. Option files must be plain text files. If you create an option file with a word processor that uses some non-text format by default, be sure to save the file explicitly as text. Windows users especially should take note of this. Options that specify file or directory pathnames should be written using / as the pathname separator character, even under Windows.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost MySQL Web Hosting services

1.5 Specifying Connection Parameters by Using Option Files 1.5.1 Problem You don’t want to type connection parameters on the command line every time you invoke mysql. 1.5.2 Solution Put the parameters in an option file. 1.5.3 Discussion To avoid entering connection parameters manually, put them in an option file for mysql to read automatically. Under Unix, your personal option file is named .my.cnf in your home directory. There are also site-wide option files that administrators can use to specify parameters that apply globally to all users. You can use /etc/my.cnf or the my.cnf file in the MySQL server’s data directory. Under Windows, the option files you can use are C:my.cnf, the my.ini file in your Windows system directory, or my.cnf in the server’s data directory. Windows may hide filename extensions when displaying files, so a file named my.cnf may appear to be named just my. Your version of Windows may allow you to disable extension-hiding. Alternatively, issue a DIR command in a DOS window to see full names. The following example illustrates the format used to write MySQL option files: # general client program connection options [client] host=localhost user=cbuser password=cbpass # options specific to the mysql program [mysql] no-auto-rehash # specify pager for interactive mode pager=/usr/bin/less This format has the following general characteristics: Lines are written in groups. The first line of the group specifies the group name inside of square brackets, and the remaining lines specify options associated with the group. The example file just shown has a [client] group and a [mysql] group. Within a group, option lines are written in name=value format, where name corresponds to an option name (without leading dashes) and value is the option’s value. If an option

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

If you don’t have a MySQL username and password, you need to obtain permission to use the MySQL server, as described earlier in Recipe 1.2. The syntax and default values for the connection parameter options are shown in the following table. These options have both a single-dash short form and a double-dash long form. Parameter type Option syntax forms Default value Hostname -h hostname–host=hostname localhost Username -u username–user=username Your login name Password -p–password None As the table indicates, there is no default password. To supply one, use –password or -p, then enter your password when mysql prompts you for it: % mysql -p Enter password: enter your password here If you like, you can specify the password directly on the command line by using either ppassword (note that there is no space after the -p) or –password=password. I don’t recommend doing this on a multiple-user machine, because the password may be visible momentarily to other users who are running tools such as ps that report process information. If you get an error message that mysql cannot be found or is an invalid command when you try to invoke it, that means your command interpreter doesn’t know where mysql is installed. See Recipe 1.8. To terminate a mysql session, issue a QUIT statement: mysql> QUIT You can also terminate the session by issuing an EXIT statement or (under Unix) by typing Ctrl-D. The way you specify connection parameters for mysql also applies to other MySQL programs such as mysqldump and mysqladmin. For example, some of the actions that mysqladmin can perform are available only to the MySQL root account, so you need to specify name and password options for that user: % mysqladmin -p -u root shutdown Enter password:

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost MySQL Web Hosting services

| centipede | 100 | 0 | | table | 4 | 0 | | armchair | 4 | 2 | | phonograph | 0 | 1 | | tripod | 3 | 0 | | Peg Leg Pete | 1 | 2 | | space alien | NULL | NULL | +————–+——+——+ 12 rows in set (0.00 sec) At this point, you’re all set up with a database and a table that can be used to run some example queries. 1.4 Starting and Terminating mysql 1.4.1 Problem You want to start and stop the mysql program. 1.4.2 Solution Invoke mysql from your command prompt to start it, specifying any connection parameters that may be necessary. To leave mysql, use a QUIT statement. 1.4.3 Discussion To start the mysql program, try just typing its name at your command-line prompt. If mysql starts up correctly, you’ll see a short message, followed by a mysql> prompt that indicates the program is ready to accept queries. To illustrate, here’s what the welcome message looks like (to save space, I won’t show it in any further examples): % mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 18427 to server version: 3.23.51-log Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer. mysql> If mysql tries to start but exits immediately with an “access denied” message, you’ll need to specify connection parameters. The most commonly needed parameters are the host to connect to (the host where the MySQL server runs), your MySQL username, and a password. For example: % mysql -h localhost -p -u cbuser Enter password: cbpass In general, I’ll show mysql commands in examples with no connection parameter options. I assume that you’ll supply any parameters that you need, either on the command line, or in an option file (Recipe 1.5) so that you don’t have to type them each time you invoke mysql.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services

it. This section shows how to do that, and also how to create a table and load it with some sample data that can be used for examples in the following sections. After the cbuser account has been set up, verify that you can use it to connect to the MySQL server. Once you’ve connected successfully, create the database. From the host that was named in the GRANT statement, run the following commands to do this (the host named after -h should be the host where the MySQL server is running): % mysql -h localhost -p -u cbuser Enter password: cbpass mysql> CREATE DATABASE cookbook; Query OK, 1 row affected (0.08 sec) Now you have a database, so you can create tables in it. Issue the following statements to select cookbook as the default database, create a simple table, and populate it with a few records:[1] [1] If you don’t want to enter the complete text of the INSERT statements (and I don’t blame you), skip ahead to Recipe 1.13 for a shortcut. And if you don’t want to type in any of the statements, skip ahead to Recipe 1.16. mysql> USE cookbook; mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’human’,2,2); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’insect’,6,0); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’squid’,0,10); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’octopus’,0,8); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’fish’,0,0); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’centipede’,100,0); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’table’,4,0); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’armchair’,4,2); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’phonograph’,0,1); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’tripod’,3,0); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’Peg Leg Pete’,1,2); mysql> INSERT INTO limbs (thing,legs,arms) VALUES(’space alien’,NULL,NULL); The table is named limbs and contains three columns to records the number of legs and arms possessed by various life forms and objects. (The physiology of the alien in the last row is such that the proper values for the arms and legs column cannot be determined; NULL indicates “unknown value.”) Verify that the table contains what you expect by issuing a SELECT statement: mysql> SELECT * FROM limbs; +————–+——+——+ | thing | legs | arms | +————–+——+——+ | human | 2 | 2 | | insect | 6 | 0 | | squid | 0 | 10 | | octopus | 0 | 8 | | fish | 0 | 0 |

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

The hostname part of ‘cbuser’@'localhost’ indicates the host from which you’ll be connecting to the MySQL server to access the cookbook database. To set up an account that will connect to a server running on the local host, use localhost, as shown. If you plan to make connections to the server from another host, substitute that host in the GRANT statement. For example, if you’ll be connecting to the server as cbuser from a host named xyz.com, the GRANT statement should look like this: mysql> GRANT ALL ON cookbook.* TO ‘cbuser’@'xyz.com’ IDENTIFIED BY ‘cbpass’; It may have occurred to you that there’s a bit of a paradox involved in the procedure just described. That is, to set up a user account that can make connections to the MySQL server, you must connect to the server first so that you can issue the GRANT statement. I’m assuming that you can already connect as the MySQL root user, because GRANT can be used only by a user such as root that has the administrative privileges needed to set up other user accounts. If you can’t connect to the server as root, ask your MySQL administrator to issue the GRANT statement for you. Once that has been done, you should be able to use the new MySQL account to connect to the server, create your own database, and proceed from there on your own. MySQL Accounts and Login Accounts MySQL accounts and login accounts for your operating system are different. For example, the MySQL root user and the Unix root user are separate and have nothing to do with each other, even though the username is the same in each case. This means they are very likely to have different passwords. It also means you cannot create new MySQL accounts by creating login accounts for your operating system; use the GRANT statement instead. 1.3 Creating a Database and a Sample Table 1.3.1 Problem You want to create a database and to set up tables within it. 1.3.2 Solution Use a CREATE DATABASE statement to create a database, a CREATE TABLE statement for each table you want to use, and INSERT to add records to the tables. 1.3.3 Discussion The GRANT statement used in the previous section defines privileges for the cookbook database, but does not create it. You need to create the database explicitly before you can use

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost MySQL Web Hosting services

1.2 Setting Up a MySQL User Account 1.2.1 Problem You need to create an account to use for connecting to the MySQL server running on a given host. 1.2.2 Solution Use the GRANT statement to set up the MySQL user account. Then use that account’s name and password to make connections to the server. 1.2.3 Discussion Connecting to a MySQL server requires a username and password. You can also specify the name of the host where the server is running. If you don’t specify connection parameters explicitly, mysql assumes default values. For example, if you specify no hostname, mysql typically assumes the server is running on the local host. The following example shows how to use the mysql program to connect to the server and issue a GRANT statement that sets up a user account with privileges for accessing a database named cookbook. The arguments to mysql include -h localhost to connect to the MySQL server running on the local host, -p to tell mysql to prompt for a password, and -u root to connect as the MySQL root user. Text that you type is shown in bold; non-bold text is program output: % mysql -h localhost -p -u root Enter password: ****** mysql> GRANT ALL ON cookbook.* TO ‘cbuser’@'localhost’ IDENTIFIED BY ‘cbpass’; Query OK, 0 rows affected (0.09 sec) mysql> QUIT Bye After you enter the mysql command shown on the first line, if you get a message indicating that the program cannot be found or that it is a bad command, see Recipe 1.8. Otherwise, when mysql prints the password prompt, enter the MySQL root password where you see the ******. (If the MySQL root user has no password, just press Return at the password prompt.) Then issue a GRANT statement like the one shown. To use a database name other than cookbook, substitute its name where you see cookbook in the GRANT statement. Note that you need to grant privileges for the database even if the user account already exists. However, in that case, you’ll likely want to omit the IDENTIFIED BY ‘cbpass’ part of the statement, because otherwise you’ll change that account’s current password.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services