MySQL is a popular open source relational database. The popularity of MySQL means there is an abundance of information online and well documented client libraries available.
MySQL supports many common database features such as replication, partitioning, triggers, views, and stored procedures. A plugin storage architecture allows support for multiple storage engines.
- Single server instance with external connectivity
- Ubuntu 10.04 LTS
- MySQL 5.6
Add the Repository
Most Linux distributions will already provide the MySQL packages in the default distribution repository. The following steps will describe adding an official APT repository provided by Oracle which will provide the latest version available.
The APT repository configuration can be downloaded from the MySQL website:
Choose the desired distribution (Ubuntu Linux 14.04 for this tutorial) and click Download.
The repository configuration package can be retrieved without registering for an Oracle account. Locate the No thanks, just start my download link and download the package.
curl -OL http://dev.mysql.com/get/mysql-apt-config_0.3.2-1ubuntu12.04_all.deb
The package can then be installed using
sudo dpkg -i mysql-apt-config_0.3.2-1ubuntu12.04_all.deb
An installation interface will offer some configuration options for the mysql-apt-config. The default options are acceptable and the installation can be finished by selecting Apply.
Install MySQL Packages
MySQL server can now be installed using
apt-get. The MySQL client package will be included with the server package.
sudo apt-get -y update sudo apt-get -y install mysql-community-server
The installation interface will prompt for a MySQL root user password and ask if the test database should be removed. There is usually no reason to keep the test database.
The MySQL server will be bound to 127.0.0.1 (localhost) by default. If you plan to connect to MySQL remotely, then the
my.cnfconfiguration file will need to be updated.
sudo vi /etc/mysql/my.cnf
Locate the bind-address parameter and replace 127.0.0.1 with the server IP address. An IP address of 0.0.0.0 can be used to bind to all available IP addresses assigned to the host.
bind-address = 0.0.0.0
Further changes can be made
my.cnf as required.
Start MySQL Server
The MySQL daemon should already be set to start on boot and running. The service will need to be restarted if the
my.cnf file has been modified.
sudo /etc/init.d/mysqld restart
It is highly recommended that the ProfitBricks firewall and/or local Linux firewall be used to restrict access to the MySQL server. Only hosts requiring connectivity to the MySQL server should be granted network access.
The MySQL daemon listens on TCP port 3306 by default.
A new allow rule can be added if the Ubuntu firewall has already been enabled. The following
ufw rule will allow host 192.0.2.10 to connect to the MySQL server listening on port 3306/tcp.
sudo ufw allow from 192.0.2.10 to any port 3306 proto tcp
Create Database User
Never configure a web application to use the root user to access MySQL. An individual application-specific user should be used instead. First log into MySQL with the administrative user.
mysql -u root -p mysql
The following steps will describe creating a new database named appdb and granting the appuser full access to the new database. Adjust the hostname from which the user will be connecting and password as necessary.
mysql> create database appdb; mysql> grant all on appdb.* to 'appuser'@'localhost' identified by 'password'; mysql> quit
You should test access to the database with the new application user.
mysql -u appuser -p -h localhost appdb