NOTICE: Whilst this document provides valid information, more secure mechanisms for accessing databases using PHP are now available. mysqli should be used instead of mysql_query.

Buy this as as a 155-page colour PDF for only $3

Chapter 6 - Install MySQL

MySQL is a popular Open Source database server. It has some limitations when compared to more Enterprise-class databases, such as the old but solid Oracle database product, but it is far better than writing your own code to store data in plain text files and then trying to find ways of efficiently finding and sorting that data. Because it is so widely used, there is a lot of information about it freely available on the internet, particularly at the dev.mysql.com website.

To start with, install MySQL using yum. As normal, accept the confirmation prompt to allow yum to complete the installation. This does look like a lot of output from the install, but it is really just 11 packages - MySQL client and server, and some Perl modules which MySQL requires.

Get the right one!

The "mysql" package only contains the MySQL client; to get the server too, install the "mysql-server" package.

You may also notice here that as yum fetches the server from the repositories, it also fetches a matching update to the already-installed mysql-libs package. This is another of the useful features of yum; by tracking dependencies stated in the individual RPMs, it ensures that all of your packages are in sync.

[root@lamp ~]# yum install mysql-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.melbourne.co.uk
* extras: mirror.bytemark.co.uk
* updates: mirror.sov.uk.goscomb.net
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-server.x86_64 0:5.1.67-1.el6_3 will be installed
--> Processing Dependency: mysql = 5.1.67-1.el6_3 for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl-DBI for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(vars) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(strict) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(Sys::Hostname) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(POSIX) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(File::Temp) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(File::Path) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(File::Copy) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(File::Basename) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: perl(DBI) for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Processing Dependency: /usr/bin/perl for package: mysql-server-5.1.67-1.el6_3.x86_64
--> Running transaction check
---> Package mysql.x86_64 0:5.1.67-1.el6_3 will be installed
--> Processing Dependency: mysql-libs = 5.1.67-1.el6_3 for package: mysql-5.1.67-1.el6_3.x86_64
---> Package perl.x86_64 4:5.10.1-130.el6_4 will be installed
--> Processing Dependency: perl-libs = 4:5.10.1-130.el6_4 for package: 4:perl-5.10.1-130.el6_4.x86_64
--> Processing Dependency: perl-libs for package: 4:perl-5.10.1-130.el6_4.x86_64
--> Processing Dependency: perl(version) for package: 4:perl-5.10.1-130.el6_4.x86_64
--> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-130.el6_4.x86_64
--> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-130.el6_4.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.10.1-130.el6_4.x86_64
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.66-2.el6_3 will be updated
---> Package mysql-libs.x86_64 0:5.1.67-1.el6_3 will be an update
---> Package perl-Module-Pluggable.x86_64 1:3.90-130.el6_4 will be installed
---> Package perl-Pod-Simple.x86_64 1:3.13-130.el6_4 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-130.el6_4.x86_64
---> Package perl-libs.x86_64 4:5.10.1-130.el6_4 will be installed
---> Package perl-version.x86_64 3:0.77-130.el6_4 will be installed
--> Running transaction check
---> Package perl-Pod-Escapes.x86_64 1:1.04-130.el6_4 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mysql-server x86_64 5.1.67-1.el6_3 updates 8.6 M
Installing for dependencies:
mysql x86_64 5.1.67-1.el6_3 updates 886 k
perl x86_64 4:5.10.1-130.el6_4 updates 10 M
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
perl-DBI x86_64 1.609-4.el6 base 705 k
perl-Module-Pluggable x86_64 1:3.90-130.el6_4 updates 38 k
perl-Pod-Escapes x86_64 1:1.04-130.el6_4 updates 31 k
perl-Pod-Simple x86_64 1:3.13-130.el6_4 updates 210 k
perl-libs x86_64 4:5.10.1-130.el6_4 updates 576 k
perl-version x86_64 3:0.77-130.el6_4 updates 50 k
Updating for dependencies:
mysql-libs x86_64 5.1.67-1.el6_3 updates 1.2 M

Transaction Summary
================================================================================
Install 10 Package(s)
Upgrade 1 Package(s)

Total download size: 23 M
Is this ok [y/N]: y
Downloading Packages:
(1/11): mysql-5.1.67-1.el6_3.x86_64.rpm | 886 kB 00:07
(2/11): mysql-libs-5.1.67-1.el6_3.x86_64.rpm | 1.2 MB 00:13
(3/11): mysql-server-5.1.67-1.el6_3.x86_64.rpm | 8.6 MB 00:46
(4/11): perl-5.10.1-130.el6_4.x86_64.rpm | 10 MB 01:18
(5/11): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:04
(6/11): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:10
(7/11): perl-Module-Pluggable-3.90-130.el6_4.x86_64.rpm | 38 kB 00:01
(8/11): perl-Pod-Escapes-1.04-130.el6_4.x86_64.rpm | 31 kB 00:00
(9/11): perl-Pod-Simple-3.13-130.el6_4.x86_64.rpm | 210 kB 00:04
(10/11): perl-libs-5.10.1-130.el6_4.x86_64.rpm | 576 kB 00:11
(11/11): perl-version-0.77-130.el6_4.x86_64.rpm | 50 kB 00:00
--------------------------------------------------------------------------------
Total 122 kB/s | 23 MB 03:08
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : mysql-libs-5.1.67-1.el6_3.x86_64 1/12
Installing : 1:perl-Pod-Escapes-1.04-130.el6_4.x86_64 2/12
Installing : 1:perl-Module-Pluggable-3.90-130.el6_4.x86_64 3/12
Installing : 3:perl-version-0.77-130.el6_4.x86_64 4/12
Installing : 4:perl-libs-5.10.1-130.el6_4.x86_64 5/12
Installing : 1:perl-Pod-Simple-3.13-130.el6_4.x86_64 6/12
Installing : 4:perl-5.10.1-130.el6_4.x86_64 7/12
Installing : perl-DBI-1.609-4.el6.x86_64 8/12
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 9/12
Installing : mysql-5.1.67-1.el6_3.x86_64 10/12
Installing : mysql-server-5.1.67-1.el6_3.x86_64 11/12
Cleanup : mysql-libs-5.1.66-2.el6_3.x86_64 12/12
Verifying : mysql-server-5.1.67-1.el6_3.x86_64 1/12
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 2/12
Verifying : 1:perl-Pod-Simple-3.13-130.el6_4.x86_64 3/12
Verifying : mysql-libs-5.1.67-1.el6_3.x86_64 4/12
Verifying : perl-DBI-1.609-4.el6.x86_64 5/12
Verifying : 1:perl-Module-Pluggable-3.90-130.el6_4.x86_64 6/12
Verifying : 3:perl-version-0.77-130.el6_4.x86_64 7/12
Verifying : 4:perl-5.10.1-130.el6_4.x86_64 8/12
Verifying : 1:perl-Pod-Escapes-1.04-130.el6_4.x86_64 9/12
Verifying : 4:perl-libs-5.10.1-130.el6_4.x86_64 10/12
Verifying : mysql-5.1.67-1.el6_3.x86_64 11/12
Verifying : mysql-libs-5.1.66-2.el6_3.x86_64 12/12

Installed:
mysql-server.x86_64 0:5.1.67-1.el6_3

Dependency Installed:
mysql.x86_64 0:5.1.67-1.el6_3
perl.x86_64 4:5.10.1-130.el6_4
perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-DBI.x86_64 0:1.609-4.el6
perl-Module-Pluggable.x86_64 1:3.90-130.el6_4
perl-Pod-Escapes.x86_64 1:1.04-130.el6_4
perl-Pod-Simple.x86_64 1:3.13-130.el6_4
perl-libs.x86_64 4:5.10.1-130.el6_4
perl-version.x86_64 3:0.77-130.el6_4

Dependency Updated:
mysql-libs.x86_64 0:5.1.67-1.el6_3

Complete!
[root@lamp ~]#

Secure MySQL

Although the package we installed was called mysql-server, the service is called mysqld, so that is how we start the database. The first time you start it, it will initialize itself, and displays vital information on how to secure the database.

The text that it displays here is the generic information from the MySQL project, which is OS-agnostic; you can safely ignore the messages about starting mysqld at boot time on Red Hat, (or CentOS) because the OS takes care of that for you with the chkconfig and service commands.

[root@lamp ~]# service mysqld start
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h lamp.example.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[ OK ]
Starting mysqld: [ OK ]
[root@lamp ~]#

We will set the password as "dba"; in real life, this is a very insecure password, but it is sufficient for our demonstration purposes.

[root@lamp ~]# mysqladmin -u root password dba
[root@lamp ~]#

The root account in MySQL has nothing to do with the root account on the operating system, other than the fact that they are both the "superuser" accounts of their respective areas.

Now we can log in to the database using our password. MySQL uses a "user@client" syntax for users, so whilst we have set the "root@localhost" password, somebody can still connect as "root@lamp.example.com" with the default blank password. There is also a generic (anonymous) entry for root@{127.0.0.1, localhost, lamp} to be cleared.

You can enter the password on the commandline, but this is not very secure at all, since anybody who can run "ps" on the server can see the password. It is better to use the interactive method to log in. This asks for the password at the terminal, and does not echo it back.

[root@lamp ~]# mysql -u root -p
Enter password: dba
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 11
Server version: 5.1.67 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

The "mysql>" prompt is similar to the shell prompt; you can use arrows to navigate the command line and go back and forwards within history. SQL statements end with a semicolon, and it will go onto another line until you feed it a semicolon. The "continue" prompt is a "->", like this:

mysql> select user, host
-> from mysql.user where user='bob';
Empty set (0.00 sec)

mysql>

Here we will show the accounts, set passwords for the first two, then delete the anonymous accounts. This is an essential first step.

mysql> select user,host,password from mysql.user;
+------+------------------+-------------------------------------------+
| user | host | password |
+------+------------------+-------------------------------------------+
| root | localhost | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
| root | lamp.example.com | |
| root | 127.0.0.1 | |
| | localhost | |
| | lamp.example.com | |
+------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

This shows 3 accounts, the first with a password (shown in encrypted form), the others without. First, we will set a password for "root@lamp.example.com":

mysql> set password for 'root'@'lamp.example.com' = PASSWORD('dba');
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+------------------+-------------------------------------------+
| user | host | password |
+------+------------------+-------------------------------------------+
| root | localhost | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
| root | lamp.example.com | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
| root | 127.0.0.1 | |
| | localhost | |
| | lamp.example.com | |
+------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

Finally, delete the remaining accounts with blank passwords.

mysql> delete from mysql.user where password='';
Query OK, 3 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+------------------+-------------------------------------------+
| user | host | password |
+------+------------------+-------------------------------------------+
| root | localhost | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
| root | lamp.example.com | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
+------+------------------+-------------------------------------------+
2 rows in set (0.00 sec)

The flush privileges command makes MySQL reread the configuration.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@lamp ~]#

Enable Start at Boot

[root@lamp ~]# chkconfig mysqld on
[root@lamp ~]# chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@lamp ~]#

Troubleshoot MySQL

If you get this message, it normally means that MySQL is not running:

[root@lamp ~]# mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@lamp ~]#

Check that the processes is running, that the socket file exists (if connecting via sockets), and that there are no firewall or other networking issues (if connecting over the network).

How to Build a LAMP Server (Linux, Apache, MySQL, PHP)