How to Optimize security and performance of mysql database

1. Secure MySQL Installation

This is the first recommended step after installing MySQL server, towards securing the database server. This script facilitates in improving the security of your MySQL server by asking you to:

Set a password for the root account, if you didn’t set it during installation.

Disable remote root user login by removing root accounts that are accessible from outside the local host.

Remove anonymous-user accounts and test database which by default can be accessed by all users, even anonymous users.
# mysql_secure_installation

After running it, set the root password and answer the series of questions by entering [Yes/Y] and press [Enter].
Secure MySQL Installation

2. Bind Database Server To Loopback Address

This configuration will restrict access from remote machines, it tells the MySQL server to only accept connections from within the localhost. You can set it in main configuration file.

# vi /etc/mysql/my.conf

Add the following line below under [mysqld] section.

bind-address = 127.0.0.1

3. Disable LOCAL INFILE in MySQL

As part of security hardening, you need to disable local_infile to prevent access to the underlying filesystem from within MySQL using the following directive under [mysqld] section.

local-infile=0

4. Change MYSQL Default Port

The Port variable sets the MySQL port number that will be used to listen on TCP/ IP connections. The default port number is 3306 but you can change it under the [mysqld] section as shown.

Port=5000

5. Enable MySQL Logging

Logs are one of the best ways to understand what happens on a server, in case of any attacks, you can easily see any intrusion-related activities from log files. You can enable MySQL logging by adding the following variable under the [mysqld] section.

log=/var/log/mysql.log

6. Set Appropriate Permission on MySQL Files

Ensure that you have appropriate permissions set for all mysql server files and data directories. The /etc/my.conf file should only be writeable to root. This blocks other users from changing database server configurations.

# chmod 644 /etc/my.cnf

7. Delete MySQL Shell History

All commands you execute on MySQL shell are stored by the mysql client in a history file: ~/.mysql_history. This can be dangerous, because for any user accounts that you will create, all usernames and passwords typed on the shell will recorded in the history file.

# cat /dev/null > ~/.mysql_history

8.Follow the Principle of Least Privilege

The principle of least privilege is a security principle that can be summed up like this:

Only give an account the access it needs to do the job and nothing more.

This principle can be applied to MySQL in a number of ways. First, when using the GRANT command to add database permissions to a particular user, be sure to restrict access to just the database the user needs access to:

> grant all privileges on mydb.* to someuser@”localhost” identified by ‘astrongpassword’;

> flush privileges;

If that user only needs access to a particular table (say, the users table), replace “mydb.*“ (which grants access to all tables) with “mydb.users“ or whatever the name of your table happens to be.

Many people will grant a user full access to a database; however, if your database user only needs read data but not change data, go the extra step of granting read-only access to the database:

> grant select on *.* to 'username'@'localhost' identified by 'password';

> flush privileges;

Finally, many database users will not access the database from localhost, and often administrators will create them, like this:

> grant all privileges on mydb.* to someuser@”%” identified by ‘astrongpassword’;

> flush privileges;

This will allow “someuser” to access the database from any network. However, if you have a well-defined set of internal IPs or — even better — have set up VLANs so that all of your application servers are on a different subnet from other hosts, then take advantage to restrict “someuser,” so that account can access the database only from a particular network(s):

> grant all privileges on mydb.* to someuser@10.0.1.0/255.255.255.0 identified by ‘astrongpassword’;

> flush privileges;

9.Change MySQL Passwords Regularly

This is a common piece of information/application/system security advice. How often you do this will entirely depend on your internal security policy. However, it can prevent “snoopers” who might have been tracking your activity over an long period of time, from gaining access to your mysql server.

> USE mysql;
> UPDATE user SET password=PASSWORD('YourPasswordHere') WHERE User='root' AND Host = 'localhost';
> FLUSH PRIVILEGES;