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. ]
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.
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
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.
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:
Connecting to phpMyAdmin
When 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.