MySQL is a relational database management system (RDBMS) written in C/C++ and a key component for the widely used open source web application stack called SAMP (Solaris, Apache, MySQL, PHP), as well as a popular choice for free software projects. It works on many system platforms and its community version is freely downloadable from MySQL site.
In this post, I’ll explain how to make a secure installation of MySQL DB as a service along with a basic initial configuration. At the end of this procedure the most important application paths will be as follows:
- /application/mysql-5.6.19 (BASEDIR, i.e. application directory)
- /data/mysql-5.6.19 (DATADIR, i.e. database directory)
- /etc/my.cnf (configuration file)
- /etc/init.d/mysql.server (startup script, managed by SMF)
Of course, you can plan to change them according to your file system structure and database design needs.
For further information about how MySQL works and how it can be customized please refer to the on line official documentation.
Installation
The installation process should be carried out with the “root” user:
# su - root
Copy in the /tmp directory the MySQL Community Server package, then unzip (take care to use the GNU version of the tar command!) and move it into the application folder:
# cd /tmp # /usr/local/bin/tar xzvf mysql-5.6.19-solaris10-platform.tar.gz # mv mysql-5.6.19-solaris10-platform /application/mysql-5.6.19
where platform can be “sparc” or “x86”.
Create a user and a group called “mysql”, assigning /application/mysql-5.6.19 as home directory and enforcing a robust user password:
# groupadd -g groupid mysql # useradd -c "MySQL System User" -d /application/mysql-5.6.19 -g mysql -s /bin/bash -u userid mysql # passwd mysql
Then, give file ownership of the above home directory to the user and group just defined:
# chown -R mysql:mysql /application/mysql-5.6.19
Also, create a data folder that will store databases and log files with same permissions:
# mkdir -p /data/mysql-5.6.19 # chown -R mysql:mysql /data/mysql-5.6.19 # chmod 750 /data/mysql-5.6.19
Finally, you should modify the bash shell configuration file named “.bashrc” under mysql user’s home directory in order to customize your db’s command line environment in terms of prompt and history, as well as pointing to the bin folder in which all mysql commands are stored (using PATH variable):
# su - mysql # export EDITOR=vi # vi .bashrc ... # SET PATH PATH=/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin:/application/mysql-5.6.19/bin export PATH # MYSQL HISTORY MYSQL_HISTFILE=/dev/null export MYSQL_HISTFILE # MYSQL PROMPT MYSQL_PS1="[\R:\m:\s][\u@\h:\d]> " export MYSQL_PS1 ...
Configuration
The configuration process should be carried out with the “mysql” user.
First of all, your MySQL DB must be initialized using “mysql_install_db” script, specifying:
- which system user will be the owner of all files and folders created by mysqld executable
- the installation directory (named BASEDIR)
- the database directory (named DATADIR)
# su - mysql # scripts/mysql_install_db --user=mysql --basedir=/application/mysql-5.6.19 --datadir=/data/mysql-5.6.19
The above procedure creates a default configuration file named “my.cnf” in the BASEDIR, that is preferable to move in a specific etc directory:
# mkdir etc # mv my.cnf etc/
Remember that, starting from version 5.6:
- InnoDB has become the default storage engine
- option “innodb_file_per_table” is enabled by default, implying that each new table is stored in a separate file inside a directory named equal to database name
Hence, it’s no longer necessary to impose the choice of InnoDB on the configuration file, rather, it is appropriate to set only these parameters:
# vi etc/my.cnf ... # These are commonly set, remove the # and set as required. port = 3306 socket = /tmp/mysql.sock # This is the memory area where InnoDB caches table and index data. innodb_buffer_pool_size = 256M # The number of days for automatic binary log file removal. expire_logs_days = 30 # Connection timeout (12 hours). interactive_timeout=43200 wait_timeout=43200 ...
Finally, as “root” user, put a symbolic link to the “my.cnf” file under /etc directory:
# su - root # cd /etc # ln -s /application/mysql-5.6.19/etc/my.cnf my.cnf
Start MySQL as a service (using SMF)
Login as “mysql” user and copy “support-files/mysql.server” script as “bin/mysql.server”, editing its content so as to specify variables BASEDIR and DATADIR consistently with MySQL installation paths:
# su - mysql # cp support-files/mysql.server bin/mysql.server # vi bin/mysql.server ... basedir=/application/mysql-5.6.19 datadir=/data/mysql-5.6.19 ...
Then, login as “root” user and put a symbolic link to the “bin/mysql.server” file under /etc/init.d directory:
# su - root # cd /etc/init.d # ln -s /application/mysql-5.6.19/bin/mysql.server mysql.server
Copy customized manifest file mysql.xml (rename its extension into xml) to /tmp directory, then enable the MySQL service through Solaris SMF with the following instructions:
# mv /tmp/mysql.xml /var/svc/manifest/application/database # chown root:sys /var/svc/manifest/application/database/mysql.xml # chmod 444 /var/svc/manifest/application/database/mysql.xml # svccfg validate /var/svc/manifest/application/database/mysql.xml # svccfg -v import /var/svc/manifest/application/database/mysql.xml svccfg: Taking "initial" snapshot for svc:/application/database/mysql:version_5_6_19. svccfg: Taking "last-import" snapshot for svc:/application/database/mysql:version_5_6_19. svccfg: Refreshed svc:/application/database/mysql:version_5_6_19. svccfg: Successful import.
Note:
if something goes wrong during manifest import, the procedure can be repeated after deleting the old file as follows:# svccfg delete svc:/application/database/mysql:version_5_6_19
# svcs -xv "*mysql*" svc:/application/database/mysql:version_5_6_19 (MySQL Database Server) State: disabled since June 1, 2014 08:00:00 AM CEST Reason: Disabled by an administrator. See: http://sun.com/msg/SMF-8000-05 See: man -M /application/mysql/man -s 1 mysql See: man -M /application/mysql/man -s 1 mysqld See: man -M /application/mysql/man -s 1 mysqladmin Impact: This service is not running. # svcadm enable svc:/application/database/mysql:version_5_6_19
Then, check corresponding log file so as to verify a proper start:
# more /var/svc/log/application-database-mysql:version_5_6_19.log
[ Jun 1 08:00:00 Enabled. ]
[ Jun 1 08:00:00 Executing start method ("/etc/init.d/mysql.server start") ]
Starting MySQL
. SUCCESS!
[ Jun 1 08:00:01 Method "start" exited with status 0 ]
Besides, you may also refer to the database log in order to find status informations:
# more /data/mysql-5.6.19/hostname.err 08:00:00 mysqld_safe Starting mysqld daemon with databases from /data/mysql-5.6.19 ... 08:00:03 592 [Note] /application/mysql-5.6.19/bin/mysqld: ready for connections. Version: '5.6.19' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) ...
Pay attention to the fact that MySQL daemon has been started by mysql user:
# ps -ef | grep mysqld mysql 12738 1 0 08:00:00 ? 0:00 /bin/sh /application/mysql-5.6.19/bin/mysqld_safe --datadir=/data/mysql-5.6.19 mysql 12864 12738 0 08:00:01 ? 0:01 /application/mysql-5.6.19/bin/mysqld --basedir=/application/mysql-5.6.19 --data
In this way, starting, stopping and restarting your MySQL DB can be accomplished exclusively using svcadm command:
# svcadm enable mysql # svcadm disable mysql # svcadm restart mysql
Hardening
In order to strengthen you MySQL DB installation, it’s strongly recommended for production servers to execute the “mysql_secure_installation” script that helps you to perform the following tasks in one shot:
- set a password for root user
- allow root user to connect only from localhost
- remove anonymous user
- remove test databases
- reload privilege tables to make all changes made so far effective
Login as “mysql” user and make sure that MySQL DB service has been started, afterwards execute the aforementioned script:
# su - mysql # bin/mysql_secure_installation
Verify
Login as “mysql” user and make use of the following commands to obtain status informations and enabled options about MySQL DB:
# su - mysql # bin/mysqladmin -u root -p version Enter password: bin/mysqladmin Ver 8.42 Distrib 5.6.19, for solaris10 on x86_64 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. ... Server version 5.6.19 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 min 00 sec Threads: 1 Questions: 37 Slow queries: 0 Opens: 68 Flush tables: 1 Open tables: 61 Queries per second avg: 0.062 # bin/mysqladmin -u root -p variables ... all variables ...
Then, try to login on your new MySQL database instance:
# mysql -u root -p Enter password: ... Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.19 MySQL Community Server (GPL) ...
Well done! You’re ready now to use your amazing new MySQL DB, enjoy it! 😀
Luca Merello's blog 