I’ll start by inspecting the anonymous accounts listed here:
Remove Anonymous User accounts – there are 2 Anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Set up a Secure Password for user@host ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
- Log in to your mysql DB as root or an account with sufficient user admin privileges.
- Select the MySQL internal DB with: USE mysql;
- Display all accounts with: SELECT Host,User FROM user WHERE User=”;
SELECT Host,User FROM user WHERE User=”;
+———–+——+
| Host | User |
+———–+——+
| localhost | |
| wordpress | |
+———–+——+
2 rows in set (0.01 sec)
Ah, I see now. Let’s drop these since requiring “no user name” is a bad exposure for your database using:
mysql> DELETE FROM user WHERE User=”;
Query OK, 2 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
That’ll at least bump up security, but I also noticed that we aren’t using any caching available so let’s enable those and let MySQL “settle” for a few days before revisiting tuning again.
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
^
|__ Pretty fair to start and enable the caches that MySQL offers.
You can enable the query_cache an the thread_cache by using the following in the configuration file (vi /usr/local/etc/my.cnf):
# My Tuning
thread-cache-size = 4
query-cache-type = 1
query-cache-size = 16M
As per mysqltuner, we’ll start the thread cache at a small 4, the query cache type “1” caches all queries except for queries with “NO_CACHE” prepended and sets the size to 16MB (>= 8MB) as recommended by mysqltuner. I’ve restarted MySQL and so far, the output from mysqltuner is much, much better ;). See you in a few days!
Full MySQL Tuner Stats below:
pkg install mysqltuner
mysqltuner
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: >> MySQLTuner 1.6.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
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46
[OK] Operating on 64-bit architecture——– Storage Engine Statistics ——————————————-
[–] Status: +CSV +InnoDB +MRG_MYISAM
[–] Data in InnoDB tables: 1M (Tables: 12)
[!!] Total fragmented tables: 12——– Security Recommendations ——————————————-
[!!] User ‘@localhost’ is an anonymous account.
[!!] User ‘@wordpress’ is an anonymous account.
[!!] User ‘@localhost’ has no password set.
[!!] User ‘@wordpress’ has no password set.
[!!] User ‘root@127.0.0.1’ has no password set.
[!!] User ‘root@::1’ has no password set.
[!!] User ‘root@wordpress’ has no password set.
[!!] User ‘@localhost’ has user name as password.
[!!] User ‘@wordpress’ has user name as password.
[!!] There is not basic password file list !——– Performance Metrics ————————————————-
[–] Up for: 33d 15h 47m 10s (1M q [0.585 qps], 112K conn, TX: 7B, RX: 155M)
[–] Reads / Writes: 99% / 1%
[–] Binary logging is disabled
[–] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 179.0M (2.36% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (7.68% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Aborted connections: 0.00% (1/112210)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 145K sorts)
[!!] Temporary tables created on disk: 95% (74K on disk / 77K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 87% (49 open / 56 opened)
[OK] Open file limit used: 0% (18/218K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)——– MyISAM Metrics —————————————————–
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/101.0K——– InnoDB Metrics —————————————————–
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/1.7M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 6.45% (528 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 100.00% (27787063 hits/ 27787462 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 9647 writes)——– AriaDB Metrics —————————————————–
[–] AriaDB is disabled.——– Replication Metrics ————————————————-
[–] No replication slave(s) for this server.
[–] This is a standalone server..——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Remove Anonymous User accounts – there are 2 Anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Set up a Secure Password for user@host ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)