Here I am going to explain a way to optimize MySQL server. Except MySQLTuner there are number of ways through which we can optimize MySQL server and their impact is also quite different. So if you want to know other ways to optimize please refer to my previous blog.

What is MySQLTuner:
MySQLTuner is a small perl script which can check the MySQL server configuration and create a report to tune the server. MySQLTuner is a quite handy tool as it does not require any specific environment, you just need perl on your machine in order to run this script.

How to setup MySQLTuner:
To set up MySQLTuner we just need to download a single perl script which can be done through following script:

wget http://mysqltuner.com/mysqltuner.pl

After executing this command mysqltuner.pl script file will be downloaded on the current directory location. Once the file is downloaded we need to provide the execute access to the file and this can we done using following chmod command:

chmod +x mysqltuner.pl 

After this chmod command we are all set to execute the command.

Execute MySQLTuner
We need the root access of MySQL server in order to execute the script. To execute the script we need to run following command:

./mysqltuner.pl

This command will ask for root login and password to execute. Once we input the credentials it will execute and will display following output on the screen:

./mysqltuner.pl 
 >>  MySQLTuner 1.6.9 - 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: 
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.22-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 62M (Tables: 29)
[--] Data in InnoDB tables: 498M (Tables: 169)
[!!] Total fragmented tables: 8

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'root@localhost' has user name as password.
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations  ---------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 59m 23s (24K q [3.379 qps], 24 conn, TX: 16M, RX: 8M)
[--] Reads / Writes: 57% / 43%
[--] Binary logging is disabled
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 201.6M (2.62% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (4.57% of installed RAM)
[OK] Slow queries: 0% (0/24K)
[OK] Highest usage of available connections: 5% (9/151)
[!!] Aborted connections: 8.33%  (2/24)
[OK] Query cache is disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 1036
[OK] Temporary tables created on disk: 0% (0 on disk / 81 total)
[OK] Thread cache hit rate: 62% (9 created / 24 connections)
[OK] Table cache hit rate: 58% (172 open / 294 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (100 immediate / 100 locks)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema --------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 72B

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[!!] Key buffer size / total MyISAM indexes: 16.0M/39.5M
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/498.9M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 15.62% (1280 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.48% (199946 hits/ 200998 total)
[OK] InnoDB Write log efficiency: 98.14% (4842 hits/ 4934 total)
[OK] InnoDB log waits: 0.00% (0 waits / 92 writes)

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    key_buffer_size (> 39.5M)
    innodb_buffer_pool_size (>= 498M) if possible.

In the above output we can see that MySQLTuner is showing different metrics and after that it shows the recommendations. Under recommendation we have two options:

General Recommendations: It shows us different options to optimize the MySQL server like to set up a secure password, run optimize table or adjust your join queries. By following these recommendations we can optimize and can secure our MySQL server.

Variables to adjust: Here MySQLTuner suggests us to tune some variables like join_buffer_size, key_buffer_size, innodb_buffer_pool etc.

If we want to run it on remote server we can execute following command:

perl mysqltuner.pl --host IP --user user --pass password

In this way we can run this MySQLTuner on any MySQL server setup to get a quick understanding of what is going on and can get some vital recommendations to improve the server. These recommendations are quite handy and we can easily fix the configuration related changes.