Improving Mysql Stability & Functioning Using Mysqltuner - Business Office 1
MySQL is the database engine behind many spider web applications on the Internet today. While it is relatively straightforward to install, configuring MySQL to best back upwardly your exceptional application requires expertise as well as the correct tools. This shipping service introduces MySQLTuner, a command-line programme which offers suggestions to optimize MySQl functioning as well as stability.
MySQLTuner is a read-only script: it won't genuinely write to the MySQL configuration file. Based on your database's past times usage, it recommends novel values to assign to specific MySQL configuration variables. It is your responsibleness to sympathize each recommended modify as well as its possible ramifications, conduct the changes you lot desire to make, as well as to brand them inwards a controlled manner.
Installing MySQLTuner
Before you lot install MySQLTuner, brand certain that it supports your MySQL version. You tin honor the up-to-date compatibility information on its website.
To position the MySQL version on your database server, run this command:
$ mysqladmin -u source -p version
...
Server version 5.5.43-0+deb7u1
...
MySQLTuner is a PERL script that you lot tin install from the measure Debian as well as Ubuntu repositories. You tin install it using the next command:
$ sudo apt-get install mysqltuner
The prepackaged MySQLTuner may non live on the latest unloosen available. If you lot desire the latest, or you lot run a Linux distro other than Debian/Ubuntu, you lot tin install the up-to-date version past times downloading it directly. Simply download the PERL script to a directory of your selection using the command:
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
Running MySQLTuner
Your database should live on upwardly longer than 1 solar daytime before you lot run MySQLTuner. This is because MySQLTuner bases its recommendations on past times database usage. The to a greater extent than information it has to analyze, the to a greater extent than accurate is its recommendations. If MySQLTuner is run on a database that has been restarted inwards the final day, you lot volition larn a alarm message: 'MySQL started inside final 24 hours - recommendations may live on inaccurate'.
To run the script, motion into the following:
$ perl mysqltuner.pl
Analyzing output
MySQLTuner reports statistics close the database, as well as makes tuning recommendations. The altitude department of the written report gives you lot useful database metrics, many of them actionable. The bottom department provides tuning suggestions for the MySQL configuration file.
You should thoroughly interrogation a suggested configuration modify before deciding to implement it. To modify a configuration variable, edit the file /etc/mysql/my.cnf.
After you lot brand a MySQL configuration change, restart the MySQL service.
$ sudo service mysql restart
Scheduling runs
Database tuning is non a 'once as well as done' type of task. Conditions modify over time. Influenza A virus subtype H5N1 goodness practise is to schedule regular MySQLTuner runs using crontabs.
The next schedules MySQLTuner to run every Th at 4:30 am.
$ (crontab -l; echo "30 04 * * iv perl <path-to-script>/mysqltuner.pl --nocolor 2>&1") | crontab -
Please cite to my earlier post for details close crontabs.
By default, MySQLTuner prompts the user for the database login credentials. For a cronjob to run MySQLTuner, you lot may furnish the database trouble organisation human relationship as well as password inwards the user-specific MySQL configuration file.
$ truthful cat >> /home/peter/.my.cnf <<< '
[client]
user=<db_user>
password=<db_pass>'
Finally, protect the db login credentials past times tightening upwardly access permissions for the .my.cnf file.
$ chmod 600 /home/peter/.my.cnf
What's next?
The ease of this serial volition guide you lot through taking actions based on MySQLTuner recommendations.
0 Response to "Improving Mysql Stability & Functioning Using Mysqltuner - Business Office 1"
Post a Comment