Reinforce your MySQL

Summary
This article introduces how to enhance the security of the MySQL server from several aspects. (20:29:37)

By shining

Reinforce your MySQL database

Preface

MySQL has become one of the most widely used databases on the network, especially for Web applications. It occupies the absolute advantage of Small and Medium applications. All of this comes from its small and easy to use, its security and effectiveness, its open license, its multi-platform, and more importantly, its perfect combination with the three major Web languages-PHP.

However, unfortunately, a default and secure MySQL server will be vulnerable to overflow due to an empty root password and a program vulnerability, making the MySQL server frequently attacked. More seriously, the database is often damaged after being attacked, causing catastrophic consequences. The following describes how to protect data.

Environment requirements

1. System Environment

A Red Hat 9.0 custom Installation server is installed with GCC and other required software packages, such as Apache and PHP. The first thing after installing the system is to upgrade the system software package. As a Web server, the system accepts requests from PHP scripts, and PHP uses the MySQL database to be installed below as the contact for Dynamic Release.

The requirements for partitioning are similar to those for general systems. The only difference is that the/chroot and/tmp created later must be in the same partition.

2. Security Requirements

(1) MySQL runs in an independent (Chroot) Environment; (2) The mysqld process runs under an independent user/user group, This user and user group have no root directory, no shell, and cannot be used in other programs; (3) modify the root account of MySQL and use a complex password; (4) only allow local connection to MySQL. The network connection is disabled when MySQL is started; (5) Ensure that the login to the MySQL nobody account is disabled; (6) Delete the test database.

Install MySQL

1. Installation preparation

Before installing MySQL, create a user and group to start MySQL according to the preceding security requirements.

#groupadd mysql #useradd mysql -c "start mysqld's account" -d /dev/null -g mysql -s /sbin/nologin

2. Compile and install

Download the MySQL source code package:

#wget http://mysql.he.net/Downloads/MySQL-4.0/mysql-4.0.16.tar.gz

Decompress:

#tar -zxvf mysql-4.0.16.tar.gz

Generally, MySQL is installed in/usr/local/MySQL. you can adjust it if you have special requirements. However, this is of little significance, because chrooting will be used later, and then only the customer tools here will be used, such as MySQL, mysqladmin, and mysqldump. Compile and install the SDK.

#./configure --prefix=/usr/local/mysql / --with-mysqld-user=mysql / --with-unix-socket-path=/tmp/mysql.sock / --with-mysqld-ldflags=-all-static #make && make install #strip /usr/local/mysql/libexec/mysqld #scripts/mysql_install_db #chown -R root /usr/local/mysql #chown -R mysql /usr/local/mysql/var #chgrp -R mysql /usr/local/mysql

The specific functions of the above steps have been described in the MySQL manual. The only difference between the steps and general steps is -- With-mysqld-ldflags =-all-static. Because the chroot environment is required, Mysql itself does not need to create any database environments after it is connected to a static environment.

3. Configure and start

In MySQL, you must manually select and copy one of several template files to/etc. These template files are located in the support-Files directory of the source file. There are four in total: small, medium, large, and huge.

#cp support-files/my-medium.cnf /etc/my.cnf #chown root:sys /etc/my.cnf #chmod 644 /etc/my.cnf

Start mysql. Note that the user is MYSQL:

#/usr/local/mysq/bin/mysqld_safe --user=mysql &

4. Test

To test whether the installed program is correct and whether MySQL has been started normally, the best way is to use the mysql client to connect to the database.

#/usr/local/mysql/bin/mysql [root@ftp bin]# mysql Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 687 to server version: 3.23.58 Type 'help;' or '/h' for help. Type '/c' to clear the buffer. mysql> mysql> show databases; +--------------+ | Database | +--------------+ | mysql | | test | +--------------+ 2 rows in set (0.00 sec) mysql>quit

If the connection is successful, you can close the database:

#/usr/local/mysql/bin/mysqladmin -uroot shutdown

If the connection fails, you need to carefully analyze the cause of the error:

#more /usr/local/mysql/var/`hostname`.err

Chrooting

1. chrooting Environment

Chroot is a UNIX/Unix-like method. Its establishment will completely isolate it from the main system. That is to say, once a problem occurs, it will not endanger the running main system. This is a very effective method, especially when configuring network service programs.

2. Accurate chroot operation

First, we should establish a 1-display directory structure:

Figure 1 directory structure

#mkdir -p /chroot/mysql/dev #mkdir -p /chroot/mysql/etc #mkdir -p /chroot/mysql/tmp #mkdir -p /chroot/mysql/var/tmp #mkdir -p /chroot/mysql/usr/local/mysql/libexec #mkdir -p /chroot/mysql/usr/local/mysql/share/mysql/english

Then set the directory permissions:

#chown -R root:sys /chroot/mysql #chmod -R 755 /chroot/mysql #chmod 1777 /chroot/mysql/tmp

3. Copy MySQL programs and files to the chroot directory.

#cp -p /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/ #cp -p /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/ #cp -p /etc/hosts /chroot/mysql/etc/ #cp -p /etc/host.conf /chroot/mysql/etc/ #cp -p /etc/resolv.conf /chroot/mysql/etc/ #cp -p /etc/group /chroot/mysql/etc/ #cp -p /etc/passwd /chroot/mysql/etc/passwd #cp -p /etc/my.cnf /chroot/mysql/etc/

4. Edit the passwd and group files under chroot.

#vi /chroot/etc/passwd

Delete all rows except MySQL, root, and sys

#vi /chroot/etc/group

Delete all rows except MySQL and Root

5. Create a special device file/dev/null

Just follow the system instructions:

#ls -al /dev/null crw-rw-rw- 1 root root 1, 3 Jan 30 2003 /dev/null #mknod /chroot/mysql/dev/null c 1 3 #chown root:root /chroot/mysql/dev/null #chmod 666 /chroot/mysql/dev/null

6. Copy the mysql database file to the chroot directory.

#cp -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var #chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var

7. Install the chrootuid Program

Download the chrootuid and install it with RPM.

http://rpm.pbone.net/index.php3/stat/4/idpl/355932/com/ chrootuid-1.3-alt2.i586.rpm.html

8. Test MySQL configuration in the Chroot environment

#chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

If the chroot directory fails, pay attention to the permission issues.

9. Test the connection to MySQL under chroot.

#/usr/local/mysql/bin/mysql --socket=/chroot/mysql/tmp/mysql.sock .............. mysql>show databases; mysql>create database wgh; mysql>quit; #ls -al /chroot/mysql/var/ ...............

Configuration server

To use MySQL more securely, You need to perform security configuration on the MySQL database. The configuration files may vary due to Chroot.

1. Disable remote connection

First, disable port 3306, which is the default listening port of MySQL. MySQL only serves local scripts, so remote connection is not required. Although the built-in security mechanism of MySQL is very strict, listening to a TCP port is still dangerous, because if the MySQL program itself has problems, unauthorized access can bypass the built-in security mechanism of MySQL. The method to disable network listening is very simple. In the [mysqld] section of the/chroot/mysql/etc/my. cnf file, remove "#" before # skip-networking.

When the network is closed, how does a local program connect to the MySQL database? Local programs can be connected through mysql. sock, which is faster than network connections. I will discuss the specific situation of mysql. sock later.

MySQL backup is usually performed using SSH!

2. Prohibit MySQL from importing local files

Next, we will disable MySQL from using the "load data local infile" command. This command uses MySQL to read local files to the database, and then the user can obtain sensitive information illegally. Some attack methods circulating on the Internet are useful. It is also a method used by many new SQL Injection attacks!

To disable the preceding command, add the following in the [mysqld] section of the/chroot/mysql/etc/my. cnf file:

set-variable=local-infile=0

For ease of management, MySQL management commands such as mysql, mysqladmin, and mysqldump in the system use the system's/etc/my. cnf file. If you want to connect, it will look for/tmp/mysql. the sock file is used to connect to the MySQL server, but the connection is the MySQL server under chroot. There are two solutions: one is to add -- socket =/chroot/mysql/tmp/mysql after the management command. sock. For example:

#/usr/local/mysql/bin/mysql -root -p --socket=/chroot/mysql/tmp/mysql.sock

The second step is to add socket =/chroot/mysql/tmp/mysql. sock to the [client] section of/etc/my. cnf. Obviously, the second method is much more convenient.

3. Modify the root user ID and password of MySQL

#chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld & #/usr/local/mysql/bin/mysql -uroot ............... mysql>SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Try to develop the habit of entering the password in mysql, because it may be seen by others when entering the password in Shell.

mysql>use mysql; mysql>update user set user="wghgreat" where user="root"; mysql>select Host,User,Password,Select_priv,Grant_priv from user; mysql>delete from user where user=''; mysql>delete from user where password=''; mysql>delete from user where host='%'; mysql>drop database test; mysql>flush privileges; mysql>quit;

Change to an ID that is not easy to guess

4. Delete historical Command records

These historical files include ~ /. Bash_history ,~ /. Mysql_history. If you open them, you will be surprised. How come there are some Plaintext Passwords here ?!

#cat /dev/null > ~/.bash_history #cat /dev/null > ~/.mysql_history

Communication between PHP and MySQL

By default, PHP uses/tmp/mysql. sock communicates with MySQL, but a major problem here is that MySQL does not generate it, but/chroot/mysql/tmp/mysql. sock. The solution is to establish a connection:

#ln /chroot/mysql/tmp/mysql.sock /tmp/mysql.sock

Note: Since hard links cannot be used between partitions in the file system, the connection must be in the same partition.

Auto-start Configuration

Note the following before you start the configuration: a new account is required for the PHP database, and the database permission settings are available for the database, for example, FILE, GRANT, ACTER, show database, RELOAD, SHUTDOWN, PROCESS, and SUPER.

Example of self-starting script:

#!/bin/sh CHROOT_MYSQL=/chroot/mysql SOCKET=/tmp/mysql.sock MYSQLD=/usr/local/mysql/libexec/mysqld PIDFILE=/usr/local/mysql/var/`hostname`.pid CHROOTUID=/usr/bin/chrootuid echo -n " mysql" case "$1" in start) rm -rf ${SOCKET} nohup ${CHROOTUID} ${CHROOT_MYSQL} mysql ${MYSQLD} >/dev/null 2>&1 & sleep 5 && ln ${CHROOT_MYSQL}/${SOCKET} ${SOCKET} ;; stop) kill `cat ${CHROOT_MYSQL}/${PIDFILE}` rm -rf ${CHROOT_MYSQL}/${SOCKET} ;; *) echo "" echo "Usage: `basename $0` {start|stop}" >&2 exit 64 ;; esac exit 0

The file is located under/etc/rc. d/init. d and is named mysqld. Be sure to run it.

#chmod +x /etc/rc.d/init.d/mysqld #ln -s /etc/rc.d/init.d/mysql /etc/rc3.d/S90mysql #ln -s /etc/rc.d/init.d/mysql /etc/rc0.d/K20mysql

Conclusion: although we cannot achieve 100% security, these measures can protect our system more securely!

References:

Artur Maj Securing MySQL Xuzhikun MySQL Database Security Configuration Yan Zi translated MySQL Chinese Reference Manual

[Responsible editor: boating]
Close Window]

Related content

· Reinforce your MySQL · Set MySql Data Synchronization · Installation of apache + mysql + php + ZendOptimizer + mod_limitipconn · Install igenus in redhat9.0 · Linux network service software installation memorandum ver 0.3