Mysql security settings under the CentOS server

Full introduction to mysql security settings and performance optimization configuration. Note that you are advised to back up data before changing the settings to avoid errors. 1. modify the password of the root user. MySQL does not set a password by default. you can set the password header in three ways: * Use the mysqladmin command to change the root user password # mysqladmin-urootpasswordecan5com // Set the MySQL management operation user

Full introduction to mysql security settings and performance optimization configuration. Note that you are advised to back up data before changing the settings to avoid errors.

1. modify the password of the root user

By default, no password is set for MySQL. you can set the password header in three ways:

* Use the mysqladmin command to change the root user password
# Mysqladmin-uroot password ecan5com // Set the root password of the MySQL administrator to 52 netseek.

* Use set password to change the password:
Mysql> set password for root @ localhost = password ('ecan5com ');

* Directly modify the root user password of the user table
Mysql> use mysql;
Mysql> update user set password = password ('ecan5com') where user = 'root ';
Mysql> flush privileges;

2. delete default databases and users

Our database is local, and we only need a local php script to read mysql, so many users do not need it. M
After ysql initialization, empty users and test databases are automatically generated, which poses a threat to the database and we will delete all of them.
The following prompt is displayed after we connect to the local mysql server using the mysql client program:
Mysql> drop database test;
Mysql> use mysql;
Mysql> delete from db;
Mysql> delete from user where not (host = "localhost" and user = "root ");
Mysql> flush privileges;

3. change the default mysql administrator name // only recommended

According to my habits, because the default mysql administrator name is root, if you can modify it,
It can prevent some script kiddies from making effort on the system. We can directly modify the database and change the root user to "admin"
Mysql> use mysql;
Mysql> update user set user = "admin" where user = "root ";
Mysql> flush privileges;

4. improve local security

To improve local security, mysql mainly prevents access to local files. for example, a hacker uses mysql to obtain/etc/passwd, which may pose a threat to the system.
Mysql accesses LOCAL files through SQL statements, mainly through Load DATA LOCAL INFILE. we can disable this function.
Prevents hackers from obtaining system core files through SQL injection. To disable this function, you must add a parameter in [mysqld] of my. cnf:
Set-variable = local-infile = 0

5. disable remote connection to mysql

Because our mysql only needs a local php script for connection, we do not need to enable socket for listening, so we can completely disable the listening function.
There are two methods to achieve this:
* Configure the my. cnf file and add the skip-networking parameter in the [mysqld] section.
* The-skip-networking startup parameter is added to the parameters of the mysqld server so that mysql does not listen to any TCP/IP connections, increasing security. To manage mysql, you can install phpMyadmin locally on the server.

6. Control database access permissions

For php scripts for interaction, it is best to create a user with the update, select, delete, insert, drop table, create table, and other permissions only for a database, in this way, the minimum loss is avoided after the database user name and password are viewed by hackers.
For example, we create a database named db1 and create a user named test1 to access the database.
Mysql> create database db1;
Mysql> grant select, insert, update, delete, create, drop privileges on db1. * to test1 @ localhost identified by 'admindb ';
The preceding SQL statement creates a database db1 and adds a user named test1. the password is admindb, but it can only connect to mysql locally. for database db1, select, insert, update, and delete are available, create and drop operation permissions.

7. restrict normal users to browse other user databases

If you have multiple databases and each database has one user, you must restrict the user to browse other databases, you can add the-skip-show-database startup parameter when starting the MySQL server.

How to forget the mysql password
If you accidentally forget the MySQL root password, you can add the parameter-skip-grant-tables when starting the MySQL server to skip the authentication of the authorization table (. /safe_mysqld-skip-grant-tables &), so that we can directly log on to the MySQL server, then modify the password of the root user, and restart MySQL to log on with the new password.

8. database file security

By default, mysql is installed in the/usr/local/mysql directory, and the corresponding database file is in the/usr/local/mysql/var directory, therefore, we need to ensure that this directory cannot allow unauthorized users to package and copy the database, so we need to restrict access to this directory.
We modify the user and group of the directory to be mysql, and change the access permission at the same time:
# Chown-R mysql. mysql/usr/local/mysql/var
# Chmod-R go-rwx/usr/local/mysql/var

9. delete historical records

The preceding commands are recorded by shell in History Files. for example, bash writes the. bash_history files in the user directory. if these files are accidentally read,
Then the database password will be leaked. The SQL commands executed after you log on to the database are also recorded in the. mysql_history file in the user directory by MySQL.
If the database user uses an SQL statement to modify the database password, the. mysql_history file will also leak. So when we log on to and back up shell data
Do not add a password after-p, but enter the database password after prompt. In addition, we should not allow these two files to record our operations, just in case.
# Rm. bash_history. mysql_history
# Ln-s/dev/null. bash_history
# Ln-s/dev/null. mysql_history

10. others

In addition, you can also use chroot and other methods to control the mysql running directory to better control permissions. for details, refer to relevant articles.
Debugging parameters of my. cnf

11. quick restoration of MySQL databases

Restore database
# Mysqlcheck-A-o-r-p
Fix a specified database
# Mysqlcheck-o-r Database_NAME-p

12. select the MySQL load as the data memory size and copy the corresponding file name to/etc/my. cnf.

If the RPM Package is installed:/usr/share/doc/mysql-XXX/
For source code compilation and installation: support-files/directory.
Cp/usr/local/share/mysql-***/my-large.cnf/etc/my. cnf
My-small.cnf #> my-medium.cnf #32 M-64 M
My-large.cnf # memory = 512 M
My-huge.cnf #1G-2G
My-innodb-heavy-4G.cnf #4 GB

After reading this article, do you have a better understanding of mysql security settings and performance optimization configurations? if you think this article is good! Please share your space and Weibo so that more friends can share and find this article as soon as they need to view it!