Mysql Database Installation and Tunning on Ubuntu 14.04

Installation:

To install MySQL, run the following command from a terminal prompt:

# sudo apt-get install mysql-server

During the installation process you will be prompted to enter a password for the MySQL root user.

Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:

# sudo netstat -tap | grep mysql

When you run this command, you should see the following line or something similar:

tcp        0      0 localhost:mysql         *:*                LISTEN      2556/mysqld

If the server is not running correctly, you can type the following command to start it:

# sudo service mysql restart

Configuration

You can edit the /etc/mysql/my.cnf file to configure the basic settings -- log file, port number, etc. For example, to configure MySQL to listen for connections from network hosts, change the bind-address directive to the server's IP address:

bind-address            = 192.168.1.125

Replace 192.168.1.125 with the appropriate address.

After making a change to /etc/mysql/my.cnf the MySQL daemon will need to be restarted:

# sudo service mysql restart

If you would like to change the MySQL root password, in a terminal enter:

# sudo dpkg-reconfigure mysql-server-5.5

The MySQL daemon will be stopped, and you will be prompted to enter a new password.

Database Engines

Whilst the default configuration of MySQL provided by the Ubuntu packages is perfectly functional and performs well there are things you may wish to consider before you proceed.

MySQL is designed to allow data to be stored in different ways. These methods are referred to as either database or storage engines. There are two main engines that you'll be interested in: InnoDB and MyISAM. Storage engines are transparent to the end user. MySQL will handle things differently under the surface, but regardless of which storage engine is in use, you will interact with the database in the same way.

As of MySQL 5.5 InnoDB is the default engine, and is highly recommended over MyISAM unless you have specific need for features unique to the engine.

Advanced configuration:

Creating a tuned my.cnf file

There are a number of parameters that can be adjusted within MySQL's configuration file that will allow you to improve the performance of the server over time. For initial set-up you may find Percona's my.cnf generating tool useful. This tool will help generate a my.cnf file that will be much more optimised for your specific server capabilities and your requirements.

Do not replace your existing my.cnf file with Percona's one if you have already loaded data into the database. Some of the changes that will be in the file will be incompatible as they alter how data is stored on the hard disk and you'll be unable to start MySQL. If you do wish to use it and you have existing data, you will need to carry out a mysqldump and reload:

# mysqldump --all-databases --routines -u root -p > ~/fulldump.sql

This will then prompt you for the root password before creating a copy of the data. It is advisable to make sure there are no other users or processes using the database whilst this takes place. Depending on how much data you've got in your database, this may take a while. You won't see anything on the screen during this process.

Once the dump has been completed, shut down MySQL:

# sudo service mysql stop

Now backup the original my.cnf file and replace with the new one:

# sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

# sudo cp /path/to/new/my.cnf /etc/mysql/my.cnf

Then delete and re-initialise the database space and make sure ownership is correct before restarting MySQL:

# sudo rm -rf /var/lib/mysql/*

# sudo mysql_install_db

# sudo chown -R mysql: /var/lib/mysql

# sudo service mysql start

Finally all that's left is to re-import your data. To give us an idea of how far the import process has got you may find the 'Pipe Viewer' utility, pv, useful. The following shows how to install and use pv for this case, but if you'd rather not use it just replace pv with cat in the following command. Ignore any ETA times produced by pv, they're based on the average time taken to handle each row of the file, but the speed of inserting can vary wildly from row to row with mysqldumps:

# sudo apt-get install pv

# pv ~/fulldump.sql | mysql

Once that is complete all is good to go!

This is not necessary for all my.cnf changes. Most of the variables you may wish to change to improve performance are adjustable even whilst the server is running. As with anything, make sure to have a good backup copy of config files and data before making changes.

MySQL Tuner:

MySQL Tuner is a useful tool that will connect to a running MySQL instance and offer suggestions for how it can be best configured for your workload. The longer the server has been running for, the better the advice mysqltuner can provide. In a production environment, consider waiting for at least 24 hours before running the tool. You can get install mysqltuner from the Ubuntu repositories:

# sudo apt-get install mysqltuner

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following NEW packages will be installed:

  mysqltuner

0 upgraded, 1 newly installed, 0 to remove and 32 not upgraded.

Need to get 14.5 kB of archives.

After this operation, 48.1 kB of additional disk space will be used.

Get:1 http://http.debian.net/debian/ jessie/main mysqltuner all 1.3.0+git2014081                                                 2-1 [14.5 kB]

Fetched 14.5 kB in 0s (17.8 kB/s)

[master e704f44] saving uncommitted changes in /etc prior to apt run

 3 files changed, 19 insertions(+), 4 deletions(-)

debconf: delaying package configuration, since apt-utils is not installed

Selecting previously unselected package mysqltuner.

(Reading database ... 49772 files and directories currently installed.)

Preparing to unpack .../mysqltuner_1.3.0+git20140812-1_all.deb ...

Unpacking mysqltuner (1.3.0+git20140812-1) ...

Processing triggers for man-db (2.7.0.2-5) ...

Setting up mysqltuner (1.3.0+git20140812-1) ...

Counting objects: 1517, done.

Compressing objects: 100% (961/961), done.

Writing objects: 100% (1517/1517), done.

Total 1517 (delta 74), reused 1506 (delta 69) 

Then once its been installed, run it:

# mysqltuner

and wait for its final report. The top section provides general information about the database server, and the bottom section provides tuning suggestions to alter in your my.cnf. Most of these can be altered live on the server without restarting, look through the official MySQL documentation (link in Resources section) for the relevant variables to change in production. The following is part of an example report from a production database which shows there may be some benefit from increasing the amount of

# mysqltuner

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.46-0+deb8u1

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in InnoDB tables: 768K (Tables: 45)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 45

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 14d 2h 11m 6s (41K q [0.034 qps], 1K conn, TX: 14M, RX: 4M)

[--] Reads / Writes: 75% / 25%

[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)

[!!] Maximum possible memory usage: 597.8M (120% of installed RAM)

[OK] Slow queries: 0% (0/41K)

[OK] Highest usage of available connections: 3% (6/151)

[OK] Key buffer size / total MyISAM indexes: 16.0M/103.0K

[OK] Query cache efficiency: 90.5% (33K cached / 36K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 16 sorts)

[OK] Temporary tables created on disk: 19% (54 on disk / 279 total)

[OK] Thread cache hit rate: 99% (6 created / 1K connections)

[OK] Table cache hit rate: 92% (88 open / 95 opened)

[OK] Open file limit used: 4% (48/1K)

[OK] Table locks acquired immediately: 100% (4K immediate / 4K locks)

[OK] InnoDB data size / buffer pool: 768.0K/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Reduce your overall MySQL memory footprint for system stability

    Enable the slow query log to troubleshoot bad queries

A final comment on tuning databases:

Whilst we can broadly say that certain settings are the best, performance can vary from application to application. For example, what works best for Wordpress might not be the best for Drupal, Joomla or proprietary applications.

Performance is dependent on the types of queries, use of indexes, how efficient the database design is and so on. You may find it useful to spend some time searching for database tuning tips based on what applications you're using it for.

Once you get past a certain point any adjustments you make will only result in minor improvements, and you'll be better off either improving the application, or looking at scaling up your database environment through either using more powerful hardware or by adding slave servers.

Keywords: MySQL, MYSQL on AWS, Tunning, ubuntu mysql, Tunning mysql