MySQL Security reinforcement under lamp

1. Modify the root user password to remove the empty password

The default installed MySQL root user is a blank password, for security purposes, must be modified as a strong password, so-called strong password, at least 8 bits, composed of letters, numbers and symbols of irregular passwords. Use the MySQL command mysaladmin to modify the root password, you can also log in to the database, modify the database MySQL User table field content, modify the method as follows:

Note: The installed MAPN default MySQL password is root

A. Log in to MySQL

/usr/local/mysql/bin/mysql-u root-p 123456

B. After entering the MySQL console you will see four database information_schema,test,mysql,ftp (these are for MAPN)

To modify the user table in MySQL database:

　　Update user set Password=password ("ABCdef") where user= "root";

C.#mysql > Flush Privileges;//force Flush Memory Authorization table, otherwise use memory-buffered password

2. Delete the default database and database users

In general, the MySQL database is installed locally and requires only local PHP scripts to read MySQL, so many users do not need to, especially the default installed users. MySQL initialization will automatically generate empty users and test libraries, to install the test, which will be a threat to the security of the database, it is necessary to delete all, the final state only a single root can be, of course, later on as needed to increase the user and database.

#mysql > show databases;

#mysql > DROP database test; Delete Database test

#use MySQL;

#delete from DB; Delete the table information that holds the database because there is no database information.

#mysql > Delete from the user where not (user= ' root '); Remove the initial non-

The root user

#mysql > Delete from user where user= ' root ' and password= '; Delete the root of the empty password and try to repeat the operation

Query OK, 2 rows Affected (0.00 sec)

#mysql > Flush Privileges; Force flush Memory Authorization table.

3. Change the default MySQL administrator account

4. About Password management

Password is a very important factor in database security management, do not save plaintext passwords to the database. If your computer is in danger of security, intruders can get all the passwords and use them. Instead, use the MD5 (), SHA1 (), or one-way hash functions. Also do not choose the password from the dictionary, there is a special program to crack them, please choose at least eight bits, composed of letters, numbers and symbols strong password. When accessing the password, use the SQL statement of MySQL's built-in function password () to encrypt and store the password. For example, add a new user to the Users table in the following ways.

#mysql > INSERT into users values (1,password (1234), ' Test ');

5. Run MSYQL with an independent user

Never run a MySQL server as a root user. This is dangerous because any user with file permissions can create a file with root (for example, ~ROOT/.BASHRC). Mysqld refuses to run with root unless it is clearly specified with the –user=root option. Mysqld should be run with ordinary non-privileged users. As in the previous installation process, create a separate MySQL account for the database that is used for managing and running MySQL.

To start mysqld with another UNIX user, increase the user option to specify the username of the [mysqld] group in the MY.CNF options file of the/ETC/MY.CNF options file or the server data directory.

#vi/etc/my.cnf

[Mysqld]

User=mysql

This command enables the server to be started with the specified user, regardless of whether you start manually or through Mysqld_safe or mysql.server, ensuring the identity of MySQL is used. You can also start the database by adding the user parameter.

#/usr/local/mysql/bin/mysqld_safe–user=mysql &

As other Linux users without root running mysqld, you do not need to change the root user name in the user table because the username of the MySQL account is not related to the username of the Linux account. Ensure that Mysqld runs only with Linux users who have read or write access to the database directory.

6. Disable remote connection to the database

See under command line Netstat-ant, the default port of 3306 is open, at this time open the Mysqld network listens, allow the user to remote through the account password to connect the local data base, by default is allowed to connect remotely. To disable this feature, start skip-networking, do not listen for any TCP/IP connections to SQL, cut off remote access rights, and ensure security. If you need to manage the database remotely, you can do so by installing phpMyAdmin. If you do need to remotely connect to the database, at least modify the default listening port, and add firewall rules that allow only the data for the trusted network's MySQL listening port to pass.

# VI/ETC/MY.CF

Remove the #skip-networking comment.

#/usr/local/mysql/bin/mysqladmin-u Root-p shutdown//Stop database

#/usr/local/mysql/bin/mysqld_safe–user=mysql &//Background with MySQL user start MySQL

　7. Limit the number of connected users

It is necessary to restrict a user of a database from multiple remote connections, which can degrade performance and affect other users ' operations. You can do this by restricting the number of connections allowed for a single account, setting the Max_user_connections variable in the mysqld of the my.cnf file. The grant statement can also support resource control options to limit the extent to which a server is allowed to use an account.

#vi/etc/my.cnf

[Mysqld]

Max_user_connections 2

8. User Directory permission limits

The default MySQL is installed in/usr/local/mysql, and the corresponding database file is in the/usr/local/mysql/var directory, therefore, it must be ensured that the directory cannot be accessed by unauthorized users after the database package copy away, so to restrict access to the directory. Ensure that Mysqld runs only with Linux users who have read or write access to the database directory.

# chown-r root/usr/local/mysql///mysql home directory to root

# Chown-r Rotot/usr/local/mysql/var//Ensure that the database directory permissions belong to the MySQL user

9. Command history protection

Database-related shell operations commands are recorded in. Bash_history, and if these files are inadvertently read, it can cause information disclosure such as database password and database structure, and the operation after logging into the database will be recorded in the. mysql_history file, If you use the Update table information to modify the database user password, you will also be read the password, so you need to delete the two files, while in the login or backup database and other password-related operations, should use the-p parameter to enter the password, implicitly enter the password, it is recommended to empty the above file.

# rm. bash_history. Mysql_history//Delete history

# ln-s/dev/null. Bash_history//Empty the shell record file

# ln-s/dev/null. Mysql_history//Empty the MySQL log file

10. Disable MySQL access to local files

In MySQL, you provide a read of the local file, using the Load data local infile command, By default in version 5.0, this option is turned on by default, which uses MySQL to read the local files to the database, then the user can illegally obtain sensitive information, if you do not need to read local files, be sure to close. MySQL should be banned with the "LOAD DATA LOCAL INFILE" command. Some of the attacks that are circulating on the network are useful in its load DATA LOCAL infile, and it is also a means to exploit many newly discovered SQL injection attacks! Hackers can also load "/etc/passwd by using the load datalocal infile "into a database table, and then can display it with SELECT, this operation is fatal to the security of the server." You can add local-infile=0 to the MY.CNF, or local-infile=0 start MySQL with a parameter.

#/usr/local/mysql/bin/mysqld_safe–user=mysql–local-infile=0 &

#mysql > Load Data local infile ' sqlfile.txt ' into table the users fields terminated by ', ';

#ERROR 1148 (42000): The used command isn't allowed with this MySQL version

The –local-infile=0 option starts mysqld Disable all the load DATA local commands from the server side, if you need to get local files, it needs to be opened, but it is recommended to close.

11.MySQL Server Permissions Control

The main function of the MySQL privilege system is to verify the user connected to a given host and give the user the Select, INSERT, update, and delete permissions on the database (see the User Super User table). Its additional functionality includes the ability to have anonymous users and for MySQL-specific functions such as load DATA infile to authorize and manage operations.

Administrators can configure tables such as User,db,host to control user access, and user table permissions are superuser privileges. It is advisable to grant the user table permissions only to Superuser, such as the server or database supervisor. For other users, you should set the permissions in the user table to ' N ' and authorize only on a specific database basis. You can authorize specific databases, tables, or columns, and file permissions give you the load DATA infile and select ... into OutFile statement reads and writes files on the server, and any user granted file permission can read or write any file that the MySQL server can read or write. (indicates that the user can read files in any database directory because the server can access the files). The file permission allows the user to create a new file under a directory where the MySQL server has write permissions, but cannot overwrite the existing file in the user table's File_priv setting Y or N. , so when you don't need to read the server file, turn off the permission.

#mysql > Load Data infile ' sqlfile.txt ' into table loadfile.users fields terminated by ', ';

Query OK, 4 rows Affected (0.00 sec)//read local information sqlfile.txt '

Records:4 deleted:0 skipped:0 warnings:0

#mysql > Update user set file_priv= ' N ' where user= ' root '; Prohibit Read permission

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

mysql> flush Privileges; Refresh Authorization Table

Query OK, 0 rows Affected (0.00 sec)

#mysql > Load Data infile ' sqlfile.txt ' into table users fields terminated by ', '; Re-login Read file

#ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using password:yes)//failure

# mysql> SELECT * from loadfile.users to outfile ' test.txt ' fields terminated by ', ';

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)

For security reasons, always check with the show grants statement to see who has accessed what. Then use the REVOKE statement to delete the permissions that are no longer needed.

12. Use the chroot mode to control the MySQL running directory

Chroot is a high-level system protection in Linux that is almost completely isolated from the main system, that is, if there is any problem, it will not compromise the running Master System. This is a very effective approach, especially when configuring a network service program.

Over crontab scheduled backup data:

#!/bin/sh

Time= ' date + ' ("%F") "%R"

$/usr/local/mysql/bin/mysqldump-u Nagios-pnagios Nagios | Gzip >/home/sszheng/nfs58/nagiosbackup/nagios_backup. $time. gz

# crontab-l

# m H Dom Mon Dow command

XX * * * */home/sszheng/shnagios/backup.sh

To recover data using commands:

gzip-d nagios_backup.\ (2008-01-24\) 00\:00.gz

Nagios_backup. (2008-01-24) 00:00

#mysql –u root-p Nagios

MYSQLD security-related startup options

–LOCAL-INFILE[={0|1}]

If you start the server with –local-infile=0, the client cannot use the local in LOAD data statement.

–old-passwords

Forces the server to generate a short (pre-4.1) password hash for the new password. This is useful in order to ensure compatibility when the server must support older versions of the client program.

(OBSOLETE) –safe-show-database

In previous versions of MySQL, this option enabled the show databases statement to show only the names of the databases with partial permissions for the user. In MySQL 5.1, this option is no longer used as the default behavior, and a show databases permission can be used to control each account's access to the database name.

–safe-user-create

If enabled, the user cannot create a new user with the GRANT statement unless the user has INSERT permission for the Mysql.user table. If you want the user to have permission to create a new user, you should grant the user the following permissions:

Mysql> GRANT INSERT (user) on Mysql.user to ' user_name ' @ ' host_name ';

This ensures that the user cannot directly change the permission column and must use the GRANT statement to grant the permission to other users.

–secure-auth

An account with an old (pre-4.1) password is not allowed to be authenticated.

16.information_schema Safety

From the user's point of view, Information_schema is just a plug-in way of the storage engine, compile and install the time

–disable-information-schema on the line.

Added trigger_acl check for i_s.triggers

Through the article, we have to learn the contents of lamp safety, will not be able to look at the previous article, I hope your lamp running more and more good!

MySQL Security reinforcement under lamp