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 configuration
For 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.
