MySQL Database server configuration

T

his tutorial works for CentOS only. For your specific OS tutorial use the tags or serach function in the sidebar area.

MySQL comes pre-installed on a Hostinger VPS. And The advantage of using yum (as opposed to installing via source code) is that you will get any security updates (if and when distributed) and dependencies are automatically taken care of.

First, confirm that you have MySQL installed:

type: yum list installed mysql*

[root@server][/]
$ yum list installed mysql*
Loaded plugins: fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: mirrors.advancedhosters.com
* extras: mirror.atlanticmetro.net
* rpmforge: mirror.teklinks.com
* updates: centosa5.centos.org
15 packages excluded due to repository priority protections
Installed Packages
mysql.i686 5.1.69-1.el6_4 @updates
mysql-libs.i686 5.1.69-1.el6_4 @updates
mysql-server.i686
 

but if you don't have it for any reason type:

          $ yum install mysql mysql-server
          $ chkconfig mysqld on
          $ service mysqld start

 

Now for the all of you first-config-users.

MySQL needs to be configured, lucky for you the script will do this for us.

+ Resetting MySQL root password - important
+ Removing anonymous users
+ Disable remote root access to MySQL (you may want otherwise)
+ Remove default test database
+ Reload privilege tables

type: /usr/bin/mysql_secure_installation



NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!

Cleaning up...



All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

[root@server][/]
$
 

Remeber the password and type:

service mysqld restart

[root@server][/]
$ service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

[root@server][/]
$
 

MySQL config file - /etc/my.cnf should look something like this at the moment

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
 

After 48h+ mySQL running you may make some tweaks

Tune using MySQL Performance Tuning Primer

first instal 'bc' command line calculator which Tuning Primer script is using

yum install bc

then

[root@server][~]
$ wget http://day32.com/MySQL/tuning-primer.sh

[root@server][~]
$ chmod u x tuning-primer.sh

[root@server][~]
$ ./tuning-primer.sh
 

Tune MySQL using mysqltuner.pl

[root@server][~]
$ cd ~

[root@server][~]
$ wget http://mysqltuner.com/mysqltuner.pl

[root@server][~]
$ chmod x mysqltuner.pl
[root@server][~]
$ ./mysqltuner.pl
 

You may make changes to the my.cnf config file, later is advised to make a backup of that file every time you making changes:
cp /etc/my.cnf /etc/my.cnf.bak
After making changes you need to reload MySQL service

service mysqld reload