November 17, 2012, 7:15 AM — Long a staple of open source computing, MySQL serves as the database back end to a massive array of applications, from network monitoring frameworks to Facebook. To those uninitiated in how databases work, setting up MySQL for the first time can be somewhat daunting. Nevertheless, with a few pointers and concepts, you can quickly get a new MySQL instance up and running, ready to deploy your application.
For the purposes of this guide, we'll assume that the reader has little or no experience with MySQL on Linux, and we'll concentrate on getting MySQL installed and configured to the point where an application can be connected to the database and begin operation. Advanced elements of MySQL, such as database programming and the SQL language itself, are beyond the scope of this effort.
[ Also on InfoWorld: How to install Apache on Linux | Prove your expertise with the free OS in InfoWorld's Linux admin IQ test round 1 and round 2. | Track the latest trends in open source with InfoWorld's Open Sources blog and Technology: Open Source newsletter. ]
Installing MySQL First things first, we need to get MySQL installed on our system. Assuming that we have a clean installation of Ubuntu Server, Fedora, or CentOS, we simply need to use the package installation tools to pull down the required packages and install them.
Note that we may need some extra packages aside from the main MySQL code in order to make our application function. For instance, if we're going to use a PHP-based application with MySQL, we'll need to install the PHP MySQL packages that allow PHP to talk to MySQL servers.
To begin, however, we should check to see if MySQL was installed during the OS installation. On Fedora and CentOS we would run this as root:
# rpm -qa | grep mysql
On Ubuntu, we would run:
$ sudo dpkg -l | grep mysql
If the mysql-server package is listed, it's already installed. You should also ensure that the MySQL extensions are installed for your application, such as php-mysql.
If we don't see MySQL in the list, we need to install it. On Fedora and CentOS, we'll use YUM to do this as the root user:
# yum install mysql-server php-mysql
This will install the MySQL server, client, and required packages and libraries, as well as the PHP MySQL extensions.
On Ubuntu, we'd run:
$ sudo apt-get install mysql-server php5-mysql
This will install the MySQL server and the PHP MySQL extensions. In addition, the installer will prompt for the MySQL root user password and start the server before completing the install.
For CentOS and Fedora, we now need to start the server and set the MySQL root user password:
$ su -
# service mysqld start
# mysqladmin password <newpassword>
Where <newpassword> should be replaced with the actual password.
At this point, we should have a functional MySQL server instance. We can test by logging into the server with the mysql client:
$ mysql -u root -p
This will prompt for the MySQL root user password you previously configured and present you with a mysql> prompt. At this point, we're logged into our new instance and ready to configure it. You can log out of the MySQL server by typing the quit command at the mysql> prompt.
Before continuing, we need to ensure that MySQL is listening for connections on the proper interfaces. By default, Ubuntu sets MySQL to listen only on the localhost address. If the application you're installing will run on the same server, this may not be an issue, as they can communicate over the localhost interface. However, if you must connect to this MySQL server from other servers, you'll be required to remove this restriction. Note that this issue is specific to Ubuntu, as the Fedora and CentOS install is not restricted to the localhost address.
In order for Ubuntu to allow connections to MySQL from other systems, we need to edit the MySQL configuration file. This file is located at /etc/mysql/my.cnf. We can use the nano editor to make the changes.
$ sudo nano /etc/mysql/my.cnf
Once the file is open, scroll down to the bind-address element, and insert the # comment character at the beginning of this line. This will cause MySQL to ignore the configuration line when it starts, which will then allow the server to listen for connections on all IP addresses and interfaces on the server. In turn, other hosts wlll be allowed to connect.
Save the file with Ctrl-O and exit with Ctrl-X. Now, we need to restart the MySQL server:
$ sudo service mysql restart
At this point, we have a MySQL server ready for configuration.