Introduction to the practical configuration techniques of MySQL Databases

The following articles mainly describe the actual configuration skills of the MySQL database. We all know that the remote service started by the relevant root user is a secret of security, the main reason is that if the related service program has some problems, remote attackers are very likely to gain full control of the host.

MySQL (the best combination with PHP) from version 3.23.15

A small change was made at the beginning. After the default installation, the service should be started by MySQL (the best combination with PHP) users, and root users are not allowed to start the service. If you have to use the root user for startup, you must add -- user = root.

(./Safe_MySQL (the best combination with PHP) d -- user = root &). Because MySQL (the best combination with PHP) contains the load data infile and SELECT... into outfile SQL statements, if the root user starts

MySQL (the best combination with PHP) server, the MySQL database user has the write permission of the root user. However, MySQL (the best combination with PHP) imposes some limitations. For example, load data infile can only read globally readable files.

, SELECT... into outfile cannot overwrite existing files.

Local log files cannot be ignored, including shell logs and MySQL logs (the best combination with PHP. Some users log on to or back up the MySQL database locally for the convenience of the diagram, sometimes in the command line parameters

The Database Password is directly included in the data, for example:

Shell>/usr/local/MySQL (the best combination with PHP)/bin/MySQL (the best combination with PHP) dump-uroot-ptest test> test. SQL

Shell>/usr/local/MySQL (the best combination with PHP)/bin/MySQL (the best combination with PHP)-uroot-ptest

These commands will be recorded by shell in history files. For example, bash will write the. bash_history file in the user directory. If these files are accidentally read, the database password will be leaked.

. The SQL commands executed after the user logs on to the database are also recorded in the. MySQL (the best combination with PHP) _ history file in the user directory by MySQL (the best combination with PHP. If the MySQL database user uses an SQL statement to modify the Database Password

The. MySQL (the best combination with PHP) _ history file leaks. Therefore, when logging on to and backing up a shell, do not add a password after-p, but enter the MySQL database password after prompting.

In addition, we should not allow these two files to record our operations, just in case.

Shell> rm. bash_history. MySQL (the best combination with PHP) _ history

Shell> ln-s/dev/null. bash_history

Shell> ln-s/dev/null. MySQL (the best combination with PHP) _ history

These two commands link these two files to/dev/null, so our operations will not be recorded in these two files.

Notes for programming

No matter which programming language is used to write programs that connect to the MySQL database (the best combination with PHP), there is a rule that never trust the data submitted by users!

For numeric fields, we need to use the query statement: SELECT * FROM table where id = '000000'. Do not use a query statement like SELECT * FROM table where id = 234.

. MySQL (the best combination with PHP) automatically converts a string to a numeric character and removes the numeric character. If the data submitted by the user is processed by MySQL (the best combination with PHP) _ escape_string, we can completely eliminate it.

Notes for various programming languages:

1) All Web programs:

A) try to enter single quotation marks and double quotation marks in the Web form to test possible errors and find out the cause.

B) modify the URL parameter % 22 ('"'), % 23 ('#'), and % 27 (''').

C) For numeric field variables, our application must perform strict checks, otherwise it is very dangerous.

D) check whether the data submitted by the user exceeds the length of the field.

E) do not grant excessive access permissions to users who connect their programs to the MySQL database.

2) PHP:

A) check whether the data submitted by the user has been processed by addslashes before the query. After PHP 4.0.3, it provides a MySQL-based (best combination with PHP) c api function MySQL (the best combination with PHP) _ escape_string ().

3) MySQL (the best combination with PHP) c api:

A) check whether the query string is called using MySQL (the best combination with PHP) _ escape_string () API.

4) MySQL (the best combination with PHP) ++:

A) check whether the query string is processed using escape and quote.

5) Perl DBI:

A) check whether the quote () method is used for the query string.

6) Java JDBC:

A) check whether the PreparedStatement object is used for the query string.

Tips

1) if you accidentally forget the root password of MySQL (the best combination with PHP), we can start MySQL (the best combination with PHP) the -- skip-grant-tables parameter is added to the server to skip the authentication of the authorization table (. /safe_MySQL (the best combination with PHP) d

-- Skip-grant-tables &), so that we can directly log on to the MySQL (best combination with PHP) server, and then modify the password of the root user, restart MySQL (the best combination with PHP) to log on with a new password.

2) When the MySQL (best combination with PHP) server is started, the -- skip-show-database is added so that general database users cannot browse other MySQL databases.

3) Add the -- chroot = path parameter when starting the MySQL (best combination with PHP) server, so that the MySQL (best combination with PHP) d daemon runs in the chroot environment. In this way, the SQL statements LOAD DATA INFILE and SELECT...

OUTFILE is limited to read and write files under chroot_path. Note that after MySQL (the best combination with PHP) is started, a MySQL (the best combination with PHP). sock file is created, which is in the/tmp directory by default. Used

After chroot, MySQL (the best combination with PHP) will create MySQL (the best combination with PHP) in chroot_path/tmp ). sock file. If you do not have the chroot_path/tmp directory or start MySQL (the best combination with PHP), you cannot write data to this directory.

Create the MySQL (the best combination with PHP). sock file. MySQL (the best combination with PHP) will fail to start. For example, if we add -- chroot =/usr/local/MySQL (the best combination with PHP)/startup parameters, we 'd better establish a MySQL startup (the best combination with PHP) users can write

/Usr/local/MySQL (the best combination with PHP)/tmp directory, of course we can also use -- socket = path to specify MySQL (the best combination with PHP ). the path of the sock file, but this path must be in chroot_path.

4) Add the -- log-slow-queries [= file] parameter when starting the MySQL (the best combination with PHP) server, so that MySQL (the best combination with PHP) d. Write the time when the SQL command execution time exceeds long_query_time to the file. If no

With the Specified = file, MySQL (the best combination with PHP) d will write to the hostname-slow.log under the data directory by default. If only filename is specified and no path is specified, MySQL (the best combination with PHP) d will also write filename

Data Directory. We can use this log file to find out the query statements that have been executed for a long time, and optimize them as much as possible to relieve the burden on the MySQL (the best combination with PHP) server.

5) if we only need to use MySQL (the best combination with PHP) Service locally, we can add the -- skip-networking startup parameter to make MySQL (the best combination with PHP) no listening for any TCP/IP connections, increasing security

The above content is an introduction to the MySQL database configuration skills. I hope you will gain some benefits.