Installation, optimization, and security settings of MySql5.0.x in CentOS

When the '-O' option is not used, the compiler aims to reduce the compilation overhead and enable the compilation result to be debugged. The statement is independent: if the program is aborted using a breakpoint between two statements

Download Page: http://dev.mysql.com/downloads/mysql/5.0.html#downloads
Go to the bottom of the page and find Source downloads. this is the Source code version. download 1st tarballs.

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 \ for functions that do not require a stack pointer, the pointer is not saved in the register. Therefore, the code for storing and retrieving addresses can be ignored and registers can be used 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/\ specifies the installation directory
-- Without-debug \ Remove debug mode
-- With-extra-charsets = gbk, gb2312, utf8 \ added support for gbk, gb2312, and utf8 Chinese characters
-- With-pthread \ force use of the pthread Library (posix Thread Library)
-- Enable-javaser \ use 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 the 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 <内存<64m，或者内存有128m，但是数据库与web服务器公用内存
My-huge.cnf 1G <内存<2g，服务器主要运行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.

Bytes -----------------------------------------------------------------------------------------------
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

Bytes -----------------------------------------------------------------------------------------------
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. (Are you sure 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 a user)
Mysql> delete from user where host = ''; (delete a 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.

This article is worth reading about Mysql security settings.
Http://www.unixren.com/linux/bencandy.php? Fid = 21 & id = 459
Bytes -----------------------------------------------------------------------------------------------

PhpMyAdmin 2.9.x installation and configuration

PhpMyAdmin is a software written in PHP that can control and operate MySQL databases over the Internet. PhpMyAdmin can be used to operate databases, such as creating, copying, and deleting data.

: Http://www.phpmyadmin.net
The latest version is 2.9.1.1.
Prerequisites for using phpMyAdmin: Apache (or Zeus, IIS), PHP, and MySql must be installed and run properly. Because phpMyAdmin is written in a PHP program, you must be sure that your PHP can connect to MySql normally.

Start Installation below:

1, extract the downloaded file, will get a directory, such as phpMyAdmin-2.9.1.1-all-languages-utf-8-only

2. move the directory to the root directory of your website.
Mv./phpMyAdmin-2.9.1.1-all-languages-utf-8-only/var/www/

3. for the sake of security, we recommend that you modify the directory name so that only you know the location of phpMyAdmin.
Mv/var/www/phpMyAdmin-2.9.1.1-all-languages-utf-8-only/var/www/MyphpAdmin

4. modify the phpMyAdmin configuration file
Cd/var/www/MyphpAdmin
Mv config. sample. inc. php config. inc. php
Config. inc. php is the configuration file. you need to rename config. sample. inc. php to take effect.
Kate config. inc. php

The options starting with $ cfg can be modified. you can modify them according to your actual situation in the following document.
$ Cfg ['blowfish _ secret '] = 'http';/* cookie-based encryption algorithm. enter a string of characters */
$ Cfg ['servers'] [$ I] ['auth _ type'] = 'cooker';/* authentication method for logon. Set local logon to config and remote network logon to cookie */
$ Cfg ['servers'] [$ I] ['host'] = 'localhost';/* host name, which can be viewed by entering uname-n under the terminal */
$ Cfg ['servers'] [$ I] ['connect _ type'] = 'socket';/* use tcp (Mysql on a remote server) or socket (Mysql on the local machine) connection */
$ Cfg ['servers'] [$ I] ['compus'] = true;/* enable compression to increase the speed, if the access is abnormal, change to false (the php version must be later than 4.3.0 )*/
$ Cfg ['servers'] [$ I] ['extension'] = 'mysql';/* Set the database type supported by phpMyAdmin */
$ Cfg ['servers'] [$ I] ['controluser'] = '';/* enter the root user name of Mysql */
$ Cfg ['servers'] [$ I] ['controlpass'] = '';/* Do not enter the Mysql root user password */
$ Cfg ['servers'] [$ I] ['pmadb'] = 'phpmyadmin';/* name of the database containing the connection table structure. No need to modify */
$ Cfg ['servers'] [$ I] ['bookmarktable'] = 'PMA _ bookmark';/* starts with version 2.2.0, phpMyAdmin allows you to store frequently used queries in bookmarks. No need to modify */
$ Cfg ['servers'] [$ I] ['relation'] = 'PMA _ relation';/* from version 2.2.4, you can define an association table, its field is the primary key of another table (external table. No need to modify */
$ Cfg ['servers'] [$ I] ['Table _ info'] = 'PMA _ table_info ';/* No need to modify */
$ Cfg ['servers'] [$ I] ['Table _ coords '] = 'PMA _ table_coords';/* No need to modify */
$ Cfg ['servers'] [$ I] ['PDF _ page'] = 'PMA _ pdf_pages ';/* No need to modify */
$ Cfg ['servers'] [$ I] ['column _ info'] = 'PMA _ column_info ';/* No need to modify */
$ Cfg ['servers'] [$ I] ['History '] = 'PMA _ history';/* No need to modify */
$ Cfg ['uploaddir'] = '';/* No need to modify */
$ Cfg ['savedir'] = '';/* No need to modify */

5. after the configuration is complete, you can use it in the browser.
For example, http: // 192.168.0.1/MyphpAdmin/(note that the case must be entered correctly; otherwise, the page cannot be found)
If the installation is correct, the logon dialog box appears. enter the MySql account and password to log on.

If [size =-1] session_start (): Failed to initialize storage module appears during login.
Modify the following two sentences in the php. ini file:
Session. save_handler = files
Session. save_path = "/tmp" (check the read and write permissions of the/tmp directory)
Then, restart apache.

If #2002-the server does not respond (or the local MySQL server's socket is not correctly configured ),

I Googled and found that many people are troubled by this problem. The solution is centered on mysql. sock. In fact, it is very unlikely to solve the problem by modifying the mysql. sock path. Most of these problems are caused by ip binding (bind-address = 127.0.0.1) in mysql. Here, you only need to change $ cfg ['servers'] [$ I] ['host'] = 'localhost' to '2017. 0.0.1 'or change it to localhost. localdomain.