Installation, optimization, and security settings of MySql 5.0.x in CentOS

Groupadd mysql

Create a user group named mysql

Useradd-g mysql

Create a user named mysql under the mysql user group

Gunzip | mysql-VERSION.tar.gz | tar-xvf-

Decompress the downloaded .gz File

Cd mysql-VERSION

Enter the decompressed directory

CFLAGS = "-O3-mcpu = pentium4" CXX = gcc CXXFLAGS = "-O3-march = pentium4-felide-constructors-fno-exceptions-fno-rtti ". /configure -- prefix =/home/mysql/-- without-debug -- with-unix-socket-path =/home/mysql/tmp/mysql. sock -- with-client-ldflags =-all-static -- with-mysqld-ldflags =-all-static -- enable-validator -- with-extra-charsets = gbk, gb2312, utf8 -- without-innodb -- without-isam -- with-pthread -- enable-thread-safe-client

Configure mysql

Gcc parameters:

-O3

-O

-O1

Optimization. For large functions, optimizing compilation takes a little more time and a considerable amount of memory.

Without the '-O' option, the compiler aims to reduce compilation overhead and enable debugging of compilation results. the statement is independent: If you stop a program with a breakpoint between two statements, you can assign a value to any variable, or point the program counter to another statement in the function body, and precisely obtain the expected results from the source program.

When the '-O' option is not used, only the variables declared with register are allocated with registers. The compilation result is slightly inferior to those without the'-O' option.

With the '-O' option, the compiler will try to reduce the size and execution time of the target code.

If the '-O' option is specified, the'-fthread-jumps 'and'-fdefer-pop' options are enabled. the '-fdelayed-branch' option is enabled on a machine with a delay slot. on a machine that supports debugging even if there is no frame pointer (frame pointer), the '-fomit-frame-pointer' option is enabled. other options may be enabled on some machines.

-O2

More optimizations. in addition to optimization options that involve space and speed switching, almost all optimization work is performed. for example, loop unrolling and inlining are not performed ). compared with the-O option, this option increases both the Compilation Time and the running effect of the generated code.

-O3

More optimizations. Apart from opening what-O2 does, it also opens the-finline-functions option.

-O0

Not optimized.

If multiple-O options are specified, the last option takes effect regardless of the number.

-Mcpu = pentium4 optimized compilation based on the CPU type, which can make your mysq performance better! Optional item lot: i386, i386, i586, i686, pentium, pentium-mmx, pentiumpro, pentium2, pentium3, pentium4, k6, k6-2, athlon, athlon-tbird, k6-3, athlon-xp, athlon-mp, winchip-c6, winchip2, c3.

-Fomit-frame-pointer does not store pointers in registers for functions that do not require a stack pointer. Therefore, you can ignore the code for storing and retrieving addresses and use registers for common purposes. One option is enabled for all "-O" levels, but it is valid only when the debugger can run without relying on the stack pointer. We recommend that you set it explicitly without debugging.

Configure parameters:

-- Prefix =/home/mysql/Specify the installation directory

-- Remove debug mode from without-debug

-- With-extra-charsets = gbk, gb2312, and utf8 are supported by adding gbk, gb2312, and utf8 Chinese Characters

-- With-pthread enforces the use of the pthread Library (posix thread Library)

-- Enable-aggreger uses the assembly version of some character functions.

-- Enable-thread-safe-client: Compile the client in thread mode

-- With-client-ldflags =-all-static compile the client in pure static mode

-- With-mysqld-ldflags =-all-static compile server in pure static mode

-- Without-isam removes the isam table type support, which is rarely used now. The isam table is a platform-dependent table.

-- Without-innodb remove innodb table support. innodb is a table that supports transaction processing and is suitable for enterprise-level applications.

Make

Compile

Make install

Install

Cp support-files/my-medium.cnf/etc/my. cnf

Copy the mysql configuration file to the/etc directory and rename it my. cnf

/Home/mysql has 5 my-xxxx.cnf files below

My-small.cnf minimum configuration installation, memory <= 64 M, minimum data

My-large.cnf memory = 512 M

My-medium.cnf 32 M <memory <64 M, or memory 128 M, but the database and web server public memory

My-huge.cnf 1G <memory <2G, the server mainly runs mysql

My-innodb-heavy-4G.cnf Max configuration installation with at least 4 GB memory

Cd/home/mysql

Go to the installation directory

Bin/mysql_install_db -- user = mysql

Create a data table as a mysql user

Chown-R root.

Set the owner of the mysql home directory (that is,/home/mysql) as the root user. This is a command in the official document, but it is strange that if you set the main directory owner of mysql as a root user, mysql cannot be started after running bin/mysqld_safe -- user = mysql. The problem lies in "permission". Run chown-R mysql. You can run the following command to start mysql normally. Is the official document incorrect? I hope you will discuss it together.

Chown-R mysql var

Set the owner of the var directory to a mysql user

Chgrp-R mysql.

Set the owner of the mysql main directory to the mysql user group (Note: Unlike the previous command, this command authorizes the user group)

Bin/mysqld_safe -- user = mysql &

Start mysql. If everything is normal, no prompt will be displayed after running this command.

Bin/mysqladmin-u root password

Modify the password of the root user. Here, the root user refers to the root user of mysql, which has nothing to do with the root user of Linux. The green password is the new password you need to set. Remember!

Bin/mysql-u root-p

If it is normal, you can log on with this name. After you enter the password, the prompt "mysql>" appears, indicating that the logon is successful. Exit with the quit command

Run the following command to set mysql to run automatically upon startup:

Cd mysql-VERSION

Enter the decompressed directory again, that is, the source code directory.

Cp support-files/mysql. server/etc/init. d/mysql

Copy the mysql. server File to the/etc/init. d/directory and rename it mysql.

Chmod 755/etc/init. d/mysql

Grant the "execution" permission to the/etc/init. d/mysql file.

Chkconfig-level 345 mysql on

Added to Automatic startup, with a running level of 3 4 5

Service mysql restart

Restart mysql Service

Q: Why do I prompt "connect fail: Can't connect to local Mysql server through socket '/home/MySQL/tmp/mysql. sock' (13)" When I connect to mysql using PHP )"

A: This is because PHP cannot normally connect to the Mysql socket, that is, the mysql. sock file. First, check whether the mysql. sock file exists in the/home/mysql/tmp/directory. If not, it may be that mysql is not started properly; If yes, it may be that the permission for the/home/mysql/tmp/directory is insufficient, use chmod 755/home/mysql/tmp to solve this problem.

--------------------------------

Mysql optimization settings

Open the/etc/my. cnf file and modify the following settings. If not, add it manually. When adjusting the settings, please do your best, which depends on the configuration of your server, especially the memory size. The following settings are suitable for servers with 1 GB of memory, but are not absolute.

# Specify the size of the index buffer, which determines the index processing speed, especially the index read speed. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above status values can be obtained using show status like 'key _ reads ). Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details.

Key_buffer = 384 M

# The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50.

Back_log = 200

# Maximum size of a package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. By default, if this value is too small, a large (possibly incorrect) package will be captured. If you are using a large BLOB column, you must add this value. It should be as big as the largest BLOB you want to use.

Max_allowed_packet = 4 M

# Number of customers allowed at the same time. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the Too connector connections error. The default value is 100.

Max_connections = 1024

# Specify the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding status values can be obtained using show status like 'open _ tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.

Table_cache = 512

# Buffer required for sorting by each thread

Sort_buffer_size = 4 M

# When a query continuously scans a table, MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of the buffer. If you think continuous scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

Read_buffer_size = 4 M

# Accelerate the read data after the sorting operation and increase the speed of reading the classified rows. If you are performing the group by or order by operation on a table that is far greater than the available memory, you should increase the value of read_rnd_buffer_size to accelerate row reading after the sorting operation. I still don't understand the usefulness of this option ......

Read_rnd_buffer_size = 8 M

# Used for repair table. Do not understand the usefulness of this option, Baidu also found a wide range of settings, 128 M, 64 M, 32 M, etc., choose one.

Myisam_sort_buffer_size = 64 M

# Number of threads that can be reused and saved in. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in Connections and Threads_created states, you can see the role of this variable.

Thread _ cache_size = 128

# Cache query results. When a SELECT statement is executed for the first time, the server remembers the text content of the query and the returned results. When the server encounters this statement next time, it will not execute this statement again. Instead, it directly retrieves results from the query cache and returns the results to the client.

Query_cache_size = 32 M

# Maximum number of concurrent threads, cpu x 2

Thread_concurrency = 2

# Set the timeout time to avoid persistent connections

Wait_timeout = 120

# Disable unnecessary table types. Do not add this type if necessary.

Skip-innodb

Skip-bdb

About mysql optimization settings and check, this article is worth reading http://tech.itdb.cn/n/200607/27/n20060727_30398.shtml

--------------------------------

Mysql Security Settings

Open the/etc/my. cnf file and modify the following settings. If not, add it manually.

# Cancelling the external lock of the file system

Skip-locking

# Do not perform domain name anti-resolution, pay attention to the resulting permission/authorization issues

Skip-name-resolve

# Prohibit 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!

Local-infile = 0

# Close the remote connection, that is, port 3306. This 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. (You must make sure that you do not need to connect to mysql remotely)

Skip-networking

After modifying my. cnf, you also need to adjust the mysql user name, account, and default database.

First, log on to mysql and enter/home/mysql/bin/mysql-u root-p in the terminal window.

Then, you will be prompted to enter the correct password. after entering the correct password, the mysql> prompt will appear.

Enter the following command:

Mysql> use mysql;

Mysql> update user set user = "centos" where user = "root"; (change the root user name of mysql to centos to prevent brute force cracking of the root password)

Mysql> select Host, User, Password, Select_priv, Grant_priv from user;

Mysql> delete from user where user = "; (delete user)

Mysql> delete from user where password = "; (delete user)

Mysql> delete from user where host = "; (delete user)

Mysql> drop database test; (delete the default test database)

Mysql> flush privileges; (refresh the mysql cache to make the preceding settings take effect immediately)

Mysql> quit;

To make the preceding optimization and security settings take effect, restart the Mysql service or Linux.

From: Blackter 'Security Blog