Shulou(Shulou.com)06/01 Report--

Let's talk about the safest way to set up a MySQL database. The secret of the text is to be close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the safest way to set up a MySQL database.

1. Modify the root password and delete the empty password

The default installation of MySQL root users is an empty password, for security reasons, must be changed to a strong password, the so-called strong password, at least 8 digits, irregular passwords made up of letters, numbers and symbols. Use the command mysaladmin included with MySQL to modify the root password. At the same time, you can log in to the database and modify the field contents of the user table under the database mysql. The modification method is as follows:

# / usr/local/mysql/bin/mysqladmin-u root password "upassword" / / use mysqladmin # mysql > use mysql; # mysql > update user set password=password ('upassword') where user='root'; # mysql > flush privileges; / / forcibly flush the memory authorization table, otherwise the password buffered in memory is still used

2. Delete the default database and database users

In general, the MySQL database is installed locally, and only local php scripts are needed to read mysql, so many users don't need it, especially those installed by default. After initialization, MySQL will automatically generate empty users and test libraries for installation testing, which will pose a threat to the security of the database. It is necessary to delete all of them. Only a single root can be retained in the final state. Of course, users and databases will be added later as needed.

# mysql > show databases; # mysql > drop database test; / / Delete the database test # use mysql; # delete from db; / / Delete the table information stored in the database because there is no database information yet. # mysql > delete from user where not (user='root'); / / Delete the initial non-root user # mysql > delete from user where user='root' and password=''; / / delete the root with an empty password, and try to repeat the operation of Query OK, 2 rows affected (0.00 sec) # mysql > flush privileges; / / force refresh the memory authorization table.

3. Change the default mysql administrator account

The administrator name of the system mysql is root, but in general, the database administrator does not modify it, which, to some extent, facilitates the exhaustive malicious behavior of the system user. At this time, it is changed to a complex user name, please do not set it to admin or administraror, because they are also in the user dictionary that is easy to guess.

Mysql > update user set user= "newroot" where user= "root"; / / change to user name mysql > flush privileges that is not easy to guess

4. Management of passwords

Passwords are an important factor in database security management. Do not save plain text passwords to the database. If your computer is at risk, the intruder can get all the passwords and use them. Instead, use the MD5 (), SHA1 (), or one-way hash function. Do not choose passwords from dictionaries, there are special programs to crack them, please choose at least eight-digit strong passwords made up of letters, numbers and symbols. When accessing the password, the sql statement of mysql's built-in function password () is used to encrypt the password and store it. For example, add a new user to the users table in the following way.

# mysql > insert into users values (1 mysql password (1234), 'test')

5. Use independent users to run msyql

Never run MySQL CVM as a root user. This is dangerous because any user with FILE privileges can create files with root (for example, ~ root/.bashrc). Mysqld refuses to run using root unless explicitly specified with the-- user=root option. Mysqld should be run with an ordinary unprivileged user. As in the previous installation process, set up a separate mysql account in linux for the database, which is used only to manage and run MySQL.

To start mysqld, with another Unix user, add the user option to specify the user name of the [mysqld] group in the / etc/my.cnf option file or the my.cnf option file of the cloud server data directory.

# vi / etc/my.cnf [mysqld] user=mysql

This command enables the CVM to be started with a specified user, whether you start it manually or via mysqld_safe or mysql.server, to ensure that you use the identity of mysql. You can also add the user parameter when starting the database.

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

As another linux user running mysqld without root, you do not need to change the root user name in the user table, because the user name of the MySQL account has nothing to do with the user name of the linux account. Make sure that mysqld runs with only linux users who have read or write permissions to the database directory.

6. Prohibit remote connection to the database

Under the command line netstat-ant, you can see that the default port 3306 is open, and mysqld's network snooping is turned on, allowing users to remotely connect to local repositories through account passwords. By default, remote connections to data are allowed. In order to disable this function, start skip-networking, do not monitor the connection of any TCP/IP of sql, cut off the right of remote access, and ensure security. If you need to manage the database remotely, you can do so by installing PhpMyadmin. If you do need to connect remotely to the database, at least modify the default listening port and add firewall rules to allow only data from the trusted network's mysql listening port to pass through.

# vi / etc/my.cf removes the # skip-networking comment. # / usr/local/mysql/bin/mysqladmin-u root-p shutdown / / stop the database # / usr/local/mysql/bin/mysqld_safe-- user=mysql & / / start mysql with the mysql user in the background

7. Limit the number of connected users

A user of the database connects remotely for many times, which will lead to performance degradation and affect the operation of other users, so it is necessary to limit it. This can be done by limiting the number of connections allowed by a single account by 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 scope of use allowed by a cloud server for an account.

# vi / etc/my.cnf [mysqld] max_user_connections 2

8. Restrictions on user directory permissions

The default mysql is installed in / usr/local/mysql, and the corresponding database files are in the / usr/local/mysql/var directory, so you must ensure that this directory does not allow unauthorized users to access and copy the database, so restrict access to this directory. Make sure that mysqld runs with only linux users who have read or write permissions to the database directory.

# chown-R root / usr/local/mysql/ mysql home directory to root # chown-R mysql.mysql / usr/local/mysql/var / / make sure the database directory permissions belong to the mysql user

9. Command history protection

The shell operation commands related to the database will be recorded in .bash _ history respectively. If these files are read inadvertently, the information such as the database password and database structure will be disclosed, and the operation after logging into the database will be recorded in the .mysql _ history file. If the update table information is used to modify the database user password, the password will also be read, so these two files need to be deleted. At the same time, when logging in or backing up the database and other password-related operations, you should use the-p parameter to prompt for the password, and then enter the password implicitly. It is recommended to leave the above files empty.

# rm .bash _ history .MySQL _ history / / Delete the history # ln-s / dev/null .bash _ history / / leave the shell record file empty # ln-s / dev/null. MySQL _ history / / set the mysql record file

10. Prohibit MySQL from accessing local files

In mysql, to provide reading of local files, using the load data local infile command, the default in version 5.0, this option is turned on by default, the operation will use MySQL to read local files into the database, and then users can illegally get sensitive information, if you do not need to read local files, please be sure to close.

Test: first create a sqlfile.txt file under the test database and separate the fields with commas

# vi sqlfile.txt 1 load data local infile load data local infile sqlfile.txt' into table users fields terminated by,'; / / read in the data # mysql > select * from users +-+ | userid | username | password | +-+ | 1 | sszng | 111 | | 2 | sman | 222nd | +-+- -+-+

If you successfully insert local data into the data, you should disable the "LOAD DATA LOCAL INFILE" command in MySQL at this time. LOAD DATA LOCAL INFILE is used in some attack methods spread on the network, and it is also a means used by many newly discovered SQL Injection attacks! Hackers can also load "/ etc/passwd" into a database table using LOAD DATALOCAL INFILE, and then display it in SELECT, which is fatal to the security of cloud servers. You can add local-infile=0 to the my.cnf, or start mysql with the parameter local-infile=0.

# / usr/local/mysql/bin/mysqld_safe-- user=mysql-- local-infile=0 & # mysql > load data local infile 'sqlfile.txt' into table users fields terminated by','; # ERROR 1148 (42000): The used command is not allowed with this MySQL version-- local-infile=0 option starts mysqld to disable all LOAD DATA LOCAL commands from the CVM. If you need to obtain local files, you need to open them, but it is recommended to close them.

11. Permission control of MySQL CVM

The main function of the MySQL privilege system is to verify the user connected to a given host and to give the user SELECT, INSERT, UPDATE and DELETE permissions on the database (see the user superuser table for details). Its additional features include the ability to have anonymous users and to authorize and manage MySQL-specific functions such as LOAD DATA INFILE.

Administrators can configure tables such as user,db,host to control user access, while user table permissions are superuser permissions. It is wise to grant permissions to only the user table to a super user such as a cloud server or database manager. For other users, you should set the permissions in the user table to'N' and authorize them only on a specific database basis. You can authorize a specific database, table, or column, and FILE permissions give you LOAD DATA INFILE and SELECT. INTO OUTFILE statements read and write 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 them.) FILE permissions allow users to create new files in a directory where the MySQL server has write permissions, but cannot overwrite existing files to set Y or N in the File_priv of the user table. So turn off this permission when you do not need to read the server file

# 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'; / / disable 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 to read the file # ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) / / failed # mysql > select * from loadfile.users into outfile' test.txt' fields terminated by',' ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

For security reasons, always use the SHOW GRANTS statement to check to see who has accessed what. Then use the revoke statement to remove permissions that are no longer needed.

12. Use chroot to control the running directory of MySQL

Chroot is an advanced means of system protection in linux, and its establishment will almost completely isolate it from the main system, that is, if it encounters any problems, it will not endanger the running main system. This is a very effective method, especially when configuring network service programs.

13. Disable support for Web access

If you do not plan to allow Web access to use MySQL databases, if you do not provide Web languages such as PHP, reset or compile your PHP and remove their default support for MySQL. If php and other web programs are used in the CVM, try illegal requests in the form of Web. If you get any form of MySQL error, immediately analyze the cause and modify the Web program in time to block the loophole and prevent MySQL from being exposed to web.

For the security check of Web, it is recommended in the official MySQL documentation. For web applications, at least check the following list:

Try entering single and double quotes (''and'') in Web form. If you get any form of MySQL error, analyze the cause immediately.

Try modifying the dynamic URL by adding% 22 (''),% 23 ('#'), and% 27 ('') to it.

Try changing the data type in dynamic URL, using the characters in the previous example, including numbers and character types. Your application should be secure enough to prevent such modifications and similar attacks.

Try entering characters, spaces, and special symbols instead of numbers in numeric fields. Your application should delete them or generate errors before passing them to MySQL. It is dangerous to pass unchecked values to MySQL!

Check the size of the data before passing it to MySQL.

Connect the application to the database with a user name other than the administrative account. Do not give the application any unwanted access.

14. Database backup strategy

Generally, it can be in the form of local backup and network backup, and can be in the form of mysqldump and direct replication backup which comes with MySQL itself.

Copying data files directly is the most direct, fast and convenient, but the disadvantage is that incremental backup can not be realized. To ensure data consistency, you need to execute the following SQL statement before backing up the file: FLUSH TABLES WITH READ LOCK; flushes all the data in memory to disk and locks the data table to ensure that no new data is written during the copy process. The recovery of the data backed up by this method is also very simple and can be copied directly back to the original database directory.

Using mysqldump, you can load the entire database into a separate text file. This file contains all the SQL commands you need to rebuild your database. This command takes all the schemas (Schema, explained later) and converts them to DDL syntax (CREATE statements, that is, database definition statements), takes all the data, and creates INSERT statements from that data. This tool reverses all the designs in your database. Because everything is included in a text file. This text file can be imported back into MySQL with a simple batch and a suitable SQL statement.

It is very simple to use mysqldump for backup. If you want to back up the database "nagios_db_backup", use the command and use the pipeline gzip command to compress the backup file. It is recommended to use the form of remote backup. You can use Rsync to mount the directory of the backup CVM to the database server, package the backup of database files, and back up the data regularly through crontab:

#! / 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 00 * / home/sszheng/shnagios/backup.sh

Use the command to restore data:

Gzip-d nagios_backup./ (2008-01-24 /) 00/:00.gz nagios_backup. (2008-01-24) 00:00 # mysql-u root-p nagios

< /home/sszheng/nfs58/nagiosbackup/nagios_backup./(2008-01-24/)12/:00 　　三、Mysqld安全相关启动选项 　　下列mysqld选项影响安全： 　--allow-suspicious-udfs 　　该选项控制是否可以载入主函数只有xxx符的用户定义函数。默认情况下，该选项被关闭，并且只能载入至少有辅助符的UDF。这样可以防止从未包含合法UDF的共享对象文件载入函数。　-- local-infile[={0|1}] 　　如果用--local-infile=0启动云服务器，则客户端不能使用LOCAL in LOAD DATA语句。　　 --old-passwords 　　强制云服务器为新密码生成短(pre-4.1)密码哈希。当服务器必须支持旧版本客户端程序时，为了保证兼容性这很有用。 　　(OBSOLETE) --safe-show-database 　　在以前版本的MySQL中，该选项使SHOW DATABASES语句只显示用户具有部分权限的数据库名。在MySQL 5.1中，该选项不再作为现在的 默认行为使用，有一个SHOW DATABASES权限可以用来控制每个账户对数据库名的访问。 　--safe-user-create 　　如果启用，用户不能用GRANT语句创建新用户，除非用户有mysql.user表的INSERT权限。如果你想让用户具有授权权限来创建新用户，你应给用户授予下面的权限： mysql>

GRANT INSERT (user) ON mysql.user TO 'user_name'@'host_name'

This ensures that the user cannot change the permission column directly, which must be granted to other users using the Grant statement.

-- secure-auth

Authentication of accounts with old (pre-4.1) passwords is not allowed.

-- skip-grant-tables

This option causes the CVM to not use the permission system at all. This gives everyone full access to all databases! (you can tell a running server to start using authorization tables again by executing mysqladmin flush-privileges or mysqladmin eload commands, or by executing FLUSH PRIVILEGES statements.)

-- skip-name-resolve

The hostname is not resolved. The column values of all Host in the authorization table must be IP number or localhost.

-- skip-networking

TCP/IP connections are not allowed on the network. All connections to mysqld must be made via Unix sockets.

-skip-show-database

1. If you use this option, only users with SHOW DATABASES privileges are allowed to execute the SHOW DATABASES statement, which displays all database names.

two。 If you do not use this option, all users are allowed to perform SHOW DATABASES, but only the database names for which the user has SHOW DATABASES privileges or partial database permissions are displayed. Note that global permissions refer to the permissions of the database. 、

Is there anything you don't understand about the most secure way to set up a MySQL database above? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.