Installation
To prepare for installation, make sure you have plenty of room on your root partition. When you install MySQL server rpm, MySQL client rpm, MySQL shared rpm, and the MySQL development rpm, it requires over 26MB of space.
In addition to the space MySQL needs, the database files themselves require room. The default database storage directory is /var/lib/mysql. In that directory, you will find files and directories. The database files are stored in directories.
If you have a partition on which you want to store the database files, you will need to do some work after install. I will cover moving the entire MySQL database files to a new location after installation.
Installing the RPM
Copy all the MySQL rpm files into one directory. Run the rpm install command with all the MySQL rpms listed, putting a space between each one. You will get a printout similar to Listing 3.1.
Listing 3.1 Initial MySQL Installation
[root@winbook imp]# rpm -i MySQL-3.22.32-1.i386.rpm MySQL-client-3.22.32-1.i386. rpm MySQL-shared-3.22.32-1.i386.rpm MySQL-devel-3.22.32-1.i386.rpm Creating db table Creating host table Creating user table Creating func table Creating tables_priv table Creating columns_priv table PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/bin/mysqladmin -u root password 'new-password' See the manual for more instructions. Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy. Starting mysqld daemon with databases from /var/lib/mysql [root@winbook imp]#
Now select a password for the root user. The root user has full access to MySQL by default. The initial password for the root user is blank. Choose something you will not forget and that is difficult for other people to guess. For this book, I'll be using the password mypass. It is a terrible password and should not be used under any circumstance.
Run the command as shown. Note that MySQL does not prompt you to re-enter the password. You must get it right the first time!
[root@winbook /root]# mysqladmin -u root password 'mypass'
A Quick Test of the Install
Three things must be done to verify that MySQL installed correctly. First, run the rpm query (rpm —qa) command and make sure all the packages installed. Because I want to see all the packages installed, I have rpm dump the entire list and then grep for the packages I want. I use the search option -i, which is not case sensitive.
[root@winbook /root]# rpm -qa | grep -i mysql
Ouput
MySQL-3.22.32-1 MySQL-client-3.22.32-1 MySQL-shared-3.22.32-1 MySQL-devel-3.22.32-1
After this checks out, I look to see that the mysql daemons are running. I run the ps commmand with the ax command-line switch, and grep for mysql. You should get a printout similar to the following:
[root@winbook /root]# ps ax | grep -i mysql
Output
529 ? S 0:00 sh /usr/bin/safe_mysqld --user=mysql \pid-file=/var/ 563 ? SN 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql 621 ? SN 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql 622 ? SN 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql
Finally, I run the mysql command-line program and take a look at the mysql database. The mysql database contains information about permissions and other databases in the system.
[root@winbook /root]# mysql mysql
Output
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) [root@winbook /root]# mysql -pmypass mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 3.22.32 Type 'help' for help. mysql>
The first time I attempted to use the mysql program, I got an error message because I did not enter the password. If you don't get an error message, it is because you did not set a password! The second time, I entered the password and it let me in. Note the password is entered immediately after the -p option with no spaces in between.
Now, show the table information. You must put a semicolon (;) at the end of every command. This signals to MySQL that you are finished entering the command, and to begin acting upon that command. If you press Enter before putting in a semicolon, simply enter the semicolon on the next line and then press Enter:
mysql> show tables;
Output
+-----------------+ | Tables in mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.00 sec) mysql>
Note
The mysql program allows commands to span multiple lines. You usually let the mysql program know that you are ready for it to interpret the command line(s) just entered by entering a semicolon as the last character in the line before pressing Enter. You can also use \G or \g as the signal to the mysql program to interpret the command line(s). These endings format the output a different way when using the mysql program.
The mysql utility also uses the readline library. This library enables the up arrow and down arrow keys to be used to recall previously entered command lines. These lines can be edited using the right and left arrow keys to move the cursor. After you press Enter, the new line is entered into the readline buffer and handed to the mysql program for interpretation.
When you use SQL statements in PHP, do not use the semicolon to signal the end of the statement. If you do, you will get an error. The semicolon is only used by the mysql utility to tell it to start processing your command.
You can show information from all the tables in the mysql database. The most interesting table in a new mysql database is the user table (see Listing 3.2). Let's look at the columns in the user table:
Listing 3.2 MySQL System User Table
mysql> show columns from user;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Password | char(16) | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+---------------+------+-----+---------+-------+
17 rows in set (0.00 sec)
mysql>
The user table has many columns. The column names are fairly obvious. This table controls most of the ability to access this database system. For now, let's look at the first four columns for all the rows in the database:
mysql> select host,user,password,select_priv from user; Output +-----------+------+------------------+-------------+ | host | user | password | select_priv | +-----------+------+------------------+-------------+ | localhost | root | 6f8c114b58f2ce9e | Y | | winbook | root | | Y | | localhost | | | N | | winbook | | | N | +-----------+------+------------------+-------------+ 4 rows in set (0.00 sec) mysql>
Briefly, the root user can access the database from the localhost (on loopback address 127.0.0.1) using a password. If the root user accesses the database from the winbook host, no password is needed.
Note
Even though winbook and localhost are the same machine in this case, any command-line invocations of programs use the loopback adapter by default. This means that you will always require a password to access MySQL. However, if someone is able to spoof and pretend they are coming from the winbook host, MySQL does not require a password.
To fix this problem, you need to run mysql as root and enter an update command where you update the password for all occurrences of the root user:
mysql> select Host,User,Password from user;
Output
+-----------+--------+------------------+
| Host | User | Password |
+-----------+--------+------------------+
| localhost | root | 6f8c114b58f2ce9e |
| winbook | root | |
| localhost | | |
| winbook | | |
| localhost | impmgr | 5567401602cd5ddd |
+-----------+--------+------------------+
5 rows in set (0.00 sec)
mysql> UPDATE user SET Password=PASSWORD('mypass') where User='root';
Query OK, 1 row affected (0.12 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select Host,User,Password from user where User='root';
+-----------+------+------------------+
| Host | User | Password |
+-----------+------+------------------+
| localhost | root | 6f8c114b58f2ce9e |
| winbook | root | 6f8c114b58f2ce9e |
+-----------+------+------------------+ 2 rows in set (0.01 sec)The root user also has select privilege to databases. Any other user has no select privilege, whether coming from the localhost or from the winbook host. If you examine the rest of the columns in the user table, you will see that root has full privileges, and other users have no privileges. This is the default security setup for MySQL.
To exit the mysql command-line utility, enter quit and press Enter. For some reason, no semicolon is needed at the end of this command.
Troubleshooting the Install
I have never had the MySQL install fail unless I was installing it on an early version of an operating system. If you attempt to install this on a stock Red Hat 5.x system then MySQL might very well not work. If you must do this, be sure to upgrade your glibc package. If this library is too far out of date, MySQL will not function.
The other possibility for an install failure is lack of disk space. You must have plenty of room in the partition that holds the /usr and /var directories. If you don't then MySQL might install, but fail to work properly. I strongly recommend that you have 100MB of free disk space in the partition that holds the /var/lib/mysql directory after you have installed MySQL. If you don't you might have a surprise failure after a short time, unless you monitor your disk usage carefully.