How to securely configure and apply MySQL database _ MySQL

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, secure and effective, open license, and multi-platform. What's more, it perfectly integrates with PHP, one of the three major Web languages. However, unfortunately, a default MySQL installation will be caused by the empty root password and

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, secure and effective, open license, and multi-platform. What's more, it works perfectly with PHP, one of the three major Web languages.

Unfortunately, a default MySQL installation will cause overflow due to the empty root password and program vulnerability, making the MySQL installation server frequently attacked. More seriously, the database is often damaged after being attacked, which may cause disastrous consequences. The following describes how to protect data.

Environment requirements

1. system environment:

There is a Red Hat Linux 9.0 custom installation server. The system has installed GCC and some 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 in an independent user/user group. The user and user group have no root directory, Shell, or 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 nobody account used to connect to MySQL 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 mysqlds account" -d dev/null -g mysql -s /sbin/nologin

2. compilation and installation:

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. configuration and startup:

MySQL configuration files must be manually selected and copied 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 properly, the best way is to use the MySQL client to connect to the database.

#/usr/local/mysql/bin/mysql[root@ftp bin]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 687 to server version: 3.23.58Type 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. Chroot preparation:

First, you should create a 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 the programs and files in the mysql directory 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

To open the passwd file, delete all rows except mysql, root, and sys.

# Vi/chroot/etc/group

To open the group File, delete all lines except mysql and root.

5. create a special device file/dev/null:

#ls -al /dev/nullcrw-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 then install it with RPM .)

8. test the MySQL configuration in the Chroot environment:

# Chrootuid/chroot/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 configure the security of the MySQL database. The configuration files are different for Chroot reasons.

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:

The following statement prohibits 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.

To disable the preceding command, add the following statement to 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 usually use the/etc/my. cnf file of the system. If you want to connect to the mysql server, it will look for the/tmp/MySQL. sock file to try to connect to the MySQL server, but the connection here is the mysql server under the chroot. There are two solutions: one is to add -- socket =/chroot/mysql/tmp/mysql. sock after the management command. For example:

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

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;

Change to an unguessed ID: mysql> flush privileges; mysql> quit.

4. delete history 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 that 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/shCHROOT_MYSQL=/chroot/mysql SOCKET=/tmp/mysql.sockMYSQLD=/usr/local/mysql/libexec/mysqldPIDFILE=/usr/local/mysql/var/`hostname`.pidCHROOTUID=/usr/bin/chrootuidecho -n " mysql"case "$1" instart)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}" >&2exit 64;;esacexit 0

The file is located under/etc/rc. d/init. d and named mysqld. Note that the file can be executed:

#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

(T006)

Related articles:

Http://bbs.database.ccidnet.com/htm_data/30/0704/357894.html> six major steps to protect important data in MySQL databases