Introduction
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.
Requirements
-
Single server instance with external connectivity
-
CentOS 7 or Red Hat Enterprise Linux 7
-
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 YUM repository provided by Oracle which will provide the latest version available.
The YUM repository configuration can be downloaded from the MySQL website.
http://dev.mysql.com/downloads/repo/yum/
Choose the desired distribution (Red Hat Enterprise Linux 7 / Oracle Linux 7 for this tutorial) and click Download.
The download link can be retrieved without registering for an Oracle account. Locate the No thanks, just start my download link and pass the link URL as a parameter to rpm.
sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Install MySQL Packages
MySQL server can now be installed using YUM. The MySQL client package will be included with the server package.
sudo yum -y install mysql-community-server
Any required changes to MySQL configuration file, /etc/my.cnf, should be made after the install has completed.
Start MySQL Server
The MySQL daemon should be enabled to start on boot.
sudo /usr/bin/systemctl enable mysqld
The server can now be started.
sudo /usr/bin/systemctl start mysqld
Secure MySQL
Once the MySQL server has started, the service will then need to be secured. The mysql_secure_installation script will assist with this process by presenting several questions. It is recommended to accept the default action for all questions which is yes.
sudo /usr/bin/mysql_secure_installation
-
Set root password? [Y/n] Y
-
Remove anonymous users? [Y/n] Y
-
Disallow root login remotely? [Y/n] Y
-
Remove test database and access to it? [Y/n] Y
-
Reload privilege tables now? [Y/n] Y
Firewall Rules
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.
MySQL listens on TCP port 3306 by default.
If the CentOS firewall is enabled, then a rule allowing access to the MySQL server on port 3306/tcp from host192.0.2.10 can be added.
firewall-cmd –permanent –zone=trusted –add-source=192.0.2.10/32
firewall-cmd –permanent –zone=trusted –add-port=3306/tcp
firewall-cmd –reload
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 an 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