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 email@example.com 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.