How-to: Get started with MySQL

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.

Baseline MySQL configurationFor most small applications, MySQL won't require many configuration changes. Parameters in the my.cnf file can be adjusted as needed, but these will be dictated by the requirements of the application that will use MySQL. If alterations need to be made, they should be listed in the application's installation manual. However, many applications require a database to be created manually, as well as a username and password assigned to the database, to allow the application to be installed. We do that from the Ubuntu, Fedora, or CentOS command line as follows:

$ mysql -u root -p

We then enter the root password and log in to the instance. We then need to create the database:

mysql> CREATE DATABASE 'mydatabase';

Note that those are backticks around mydatabase, not quote marks. This command will create the database called mydatabase. Next, we need to assign privileges. We can assign a restricted set of privileges to a user, but most applications will need full access, so we can start by adding a username with full privileges.

mysql> GRANT ALL PRIVILEGES on mydatabase.* TO myuser@localhost IDENTIFIED BY 'mypassword';

Note that mypassword is surrounded by single-quote marks.

This will grant all privileges to the user named myuser, with the password mypassword, but will only allow connections with that username/password if they originate on the local system. If we have another server that will need to communicate with this MySQL instance, we need to specify the IP address or hostname.

mysql> GRANT ALL PRIVILEGES on mydatabase.* TO myuser@192.168.100.5 IDENTIFIED BY 'mypassword';

This will allow a connection from a system with the IP address 192.168.100.5 to connect with the username myuser and password mypassword.

We can test this new user and connection with the MySQL client. If we're testing from the server that MySQL is running on, we'd use this command line:

$ mysql -u myuser -p

If we're testing from another system, we need to specify the server to connect to.

$ mysql -u myuser -h <server IP address or name> -p

Insert the actual IP address or hostname of the MySQL server after -h in that command line.

If all is well, we should log right in to the instance and we're ready to install our application.

Many LAMP applications have specific MySQL installers and instructions. Some of them have a small script that needs to be run from the command line to create the database schema prior to launching the application, while others do this through a Web-based installer. Read the installation instructions for your application to determine how the application will do the initial MySQL setup prior to use.

Managing MySQL with phpMyAdmin Once the application is installed and tested, you will want to keep tabs on the database and perhaps move into more advanced database management. For many implementations, the use of Web-based tools can make managing the database much simpler and easier for those who are not well versed in MySQL administration.

One of these tools is phpMyAdmin, which is a PHP-based MySQL administration tool. It provides a simple and easy Web GUI for performing nearly all MySQL administrative tasks. Installing it will make everyone's life easier.

To do this on Fedora and CentOS, we run this command as root:

# yum install phpmyadmin

On Ubuntu, we run:

$ sudo apt-get install phpmyadmin

Ubuntu will prompt for the Web server in use so that it can do automatic configuration. In most cases you'll be using Apache, so select that unless you're working with lighttpd or another Web server. In addition, Ubuntu will prompt you to choose manual or automatic configuration of phpMyAdmin. Unless you're comfortable doing this manually, you can safely choose the automatic configuration, which will ask for the MySQL root password. Following this, Ubuntu will configure everything and restart the servers. However, depending on how the server was initially built, you may also need to install the Apache PHP module like this:

$ sudo apt-get install libapache2-mod-php5

On Fedora and CentOS we need to make a few minor changes in order to access phpMyAdmin from remote systems. We'll use nano to edit the phpMyAdmin configuration file:

# nano /etc/httpd/conf.d/phpmyadmin.conf

When the file opens, add a line below "Allow from 127.0.0.1" and input the IP addresses or ranges that you want to allow access to. This may be "Allow from 192.168.1," which will allow everything in the 192.168.1.0/24 subnet to connect or a specific IP address. You can add multiple lines to allow multiple IPs or subnets.

Once you've added those lines, save the file with Ctrl-O and exit with Ctrl-X. Now restart Apache:

# service httpd restart

On Fedora and CentOS, we also need to insert a Blowfish secret into the phpMyAdmin configuration. We can do that with nano too.

# nano /usr/share/phpMyAdmin/config.inc.php

(Note: You may find only a config.sample.inc.php file in the phpMyAdmin directory. If so, make a copy named config.inc.php.)

Locate the line reading:

$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

Enter a secret between the quotes, like this:

$cfg['blowfish_secret'] = 'SDJkjshdkfjhsdf9**&^%^&%'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

The secret can be anything, preferably very random. You will not need to use this secret anywhere else; it's an internal requirement. Save the file with Ctrl-O and exit with Ctrl-X.

Now we can log in to phpMyAdmin by pointing a Web browser at the server with this URL.

http://<server IP address>/phpmyadmin

Connecting to phpMyAdminWhen you connect to phpMyAdmin, you'll be presented with a log-in dialog. By default, phpMyAdmin will be connecting to the localhost MySQL instance, not a remote instance, so this username and password needs to be either the root user or a user with sufficient privileges as noted above.

Once you've logged in, you should see a list of databases on the left, as well as configuration and management options on the right. Clicking on a database on the left will allow you to browse through the database contents, add and delete tables and data, import and export data and database schemas, and perform various other administrative tasks. Be careful that you don't perform actions that you're unsure about, such as dropping (deleting) a database.

There are a few tips you should know about phpMyAdmin to get started. First is a method of backing up or exporting a database to a file on your local system. To do this, click the database name on the left, then click the Export tab at the top. In most cases you won't need to change any of the options, but simply click the box next to the Save as file option near the bottom, select a compression type if desired, and click Go. This will cause the entire database to be exported, possibly compressed, and downloaded to your computer through the browser.

Another handy tip is the converse operation, which will import data from a backup into a new database. First, create the new database by clicking on the Home icon at the top left, then click Databases. Enter a name for the new database in the text field, select a collation if you know it, and click Create. The new database will appear in the list on the left.

Now, click the name of the new database, and click Import at the top. Click to browse for the backup file and select it, then click Go. This will automatically import the database backup into the new database.

You can also create and manage database users with phpMyAdmin. Click the Home icon in the upper left and select Privileges. You can add a user here by filling out the form and specifying the username and password. If you want the user to have global privileges, you can select them here, but if this is a user for one specific database, leave those checkboxes blank. Once the user has been created, click Privileges again, and click the Edit icon to the right of that username. Next, select a database under Database-specific privileges and grant that user whatever rights are necessary. You can use the Check All link to allow that user all privileges on the database or go more granular if needed.

Also, phpMyAdmin offers views on the server status. Click the Home icon in the upper left, click Processes to see active MySQL connections and processes, and click Status to see an exhaustive list of server traffic, query, and variable statistics. phpMyAdmin will flag certain statistics in red if they're potentially problematic -- for example, if the number of open tables is too large. Next to each variable is a brief description of the variable and sometimes a suggestion of what might be causing a problem, such as the possibility that the table cache value is too small. This is a great way to learn about the MySQL server settings and what they mean. All adjustments to these variables should be done in the my.cnf file, though some variables can be set while the server is running.

There are many more elements to proper MySQL operation, tuning, management, and administration, but this guide should get you up and running from a stock OS installation to a fully functional MySQL server with an administrative Web-based GUI. You may want to peruse the MySQL reference manuals for your server version for more information on the care and feeding of your MySQL database server.

This article, "How-to: Get started with MySQL," was originally published at InfoWorld.com. Follow the latest developments in applications, data management, and open source software at InfoWorld.com. Get a digest of the key stories each day in the InfoWorld Daily newsletter. For the latest business technology news, follow InfoWorld on Twitter.

Read more about applications in InfoWorld's Applications Channel.