Securing MySQL

By | March 16, 2016

A default install of MySQL is somewhat lacking in security. To fix that we are going to do
the following:

  1. Remove all default users which are installed in MySQL
  2. Create a new admin user (sqladmin) instead of using the default name of
    ‘root@localhost’
  3. Disable network access to the MySQL port (3306)

Log into mysql

# cd /usr/mysql/bin
# ./mysql -u root

Delete all users (this is to get rid of any default created accounts) specifically root@localhost and root@hostname.

mysql> connect mysql;
Connection id: 2
Current database: mysql
mysql> delete from user;
At this point in time you have no users in MySQL. That means if you disconnect from
MySQL you will not be able to log back in and will have to reinstall MySQL.

In the instructions for MySQL you should be able to insert a plain text password when
creating a new user however I was unable to. Instead I generated a 16 bit hexadecimal
password to the screen:
mysql> select password(‘test123’);
+———————+
| password(‘test123’) |
+———————+
| 39817a786ddf7333 |
+———————+
1 row in set (0.00 sec)

Create an admin user (sqladmin) with full privileges and an encrypted password.
Remember to use a good password that is unique.

mysql> grant all privileges on *.* to sqladmin@localhost identified by password
‘39817a786ddf7333’ with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from user;
+———–+———-+——————+
| host | user | password |
+———–+———-+——————+
| localhost | sqladmin | 39817a786ddf7333 |
+———–+———-+——————+
1 row in set (0.00 sec)

To summarize, I deleted the default MySQL users amd created a new admin account.

Restart mysql.

# /etc/init.d/mysqld restart

 

Leave a Reply

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