Categories
Technology

Install, Configure & Optimise MySQL on Debian

Step 1 : Adding the Dotdeb repository

You can skip this step if you have already added the Dotdeb repository while installing NGINX and PHP-FPM on your Debian Squeeze Server. We need to grab the latest version of MySQL to benefit from all new updates and bug fixes. Read this article for information on adding the Dotdeb repository to your Rackspace Cloud Server.

Step 2 : Install the MySQL server and client

Installation is simple. Just use the following command.

apt-get install mysql-server mysql-client

You will be prompted to key in the MySQL ‘root’ password during the installation. Make sure the password is hard to crack. Once the install is over, you can run

mysql_secure_installation

to remove the ‘test’ database and test users. MySQL should be up and running by now!

Step 3 : Configuring and optimising the MySQL Server

MySQL can get resource hungry. There are many articles around the web that talk about MySQL optimisation. The few settings that I have shown here will help free up some memory (RAM). Edit the following MySQL configuration file.

/etc/mysql/my.cnf

The settings that are already in the MySQL configuration file are pretty decent. You can add just the following to free up some additional memory.

skip-locking
skip-innodb
key_buffer = 8M

You can use the Perl script found at https://github.com/rackerhacker/MySQLTuner-perl to further optimise the database server.

Step 4 : Creating a new database user and database

We will need to create a new database and user for the WordPress installation. The following command should do that.

mysql -h localhost -u root -pPassword
GRANT USAGE ON *.* TO 'newuser'@'localhost' IDENTIFIED BY 'password'
GRANT ALL PRIVILEGES ON `newdbname`.* TO 'newuser'@'localhost'

This command will create a newuser with a password and assign the user to the new database with the name newdbname.

Step 5 : Restart the MySQL server

service mysql restart

That’s it! You now have a working MySQL database server.

By Kathir Vel

An agile ebusiness & marketing consultant based in Edinburgh, Scotland, UK. ICT specialist with a MBA in marketing and strategy. Seasoned project manager with strong roots in advertising campaigns and film making. Loves photography and travelling.

2 replies on “Install, Configure & Optimise MySQL on Debian”

Nice simple tutorial. In recent mysql versions (5.1+) there’s some changes in configuration:

– skip-innodb should be two rules:
ignore_builtin_innodb
default_storage_engine=MyISAM

– skip-locking is enabled by default. It’s now called skip-external locking

Cheers!

You should have explained that what you are doing is disabling InnoDB support which is not by any means and optimization if you require innodb. I think the suggestion about changing buffer size once more is debatable, when I read “optimize” I understand “perform better”, not necessarily save some memory.

I think this article lacks context and although can be an interesting tip can cause more trouble than help.

Leave a Reply

Your email address will not be published. Required fields are marked *