免编译版linux系统mysql安装配置优化手册V21.docx

1、免编译版linux系统mysql安装配置优化手册V21（免编译版）linux系统mysql安装配置优化手册V2.1Version: 2.1Author: reedTime: 2012-05-141. 适用的安装包1.1 mysql-5.1.48-linux-x86_64-glibc23.tar.gz（64位）1.2 安装mysql-5.1.48的32位版本请参考（编译版）linux系统mysql安装配置优化手册V2.01.3 mysql-5.0.41-linux-i686-glibc23.tar.gz（32位）注：本次安装的linux系统环境为64位（查看版本：#uname -m），则mysq

2、l也对应要64位。2. 建立mysql需要的用户和组2.1 #groupadd mysql2.2 #useradd g mysql mysql s /sbin/nologin（s /sbin/nologin参数为设置不允许mysql用户进行系统登录）3. 解压缩# cd /usr/local# tar -xzvf mysql-5.1.48-linux-x86_64-glibc23.tar.gz# ln -s /usr/local/mysql-5.1.48-linux-x86_64-glibc23 mysql4. 生成系统数据库#cd /usr/local/mysql# scripts/mysq

3、l_install_db -user=mysql5. 修改mysql目录权限# chown -R root /usr/local/mysql# chgrp -R mysql /usr/local/mysql# chown -R mysql /usr/local/mysql/data注：操作mysql数据库的用户是mysql 所以要拥有数据库目录的所有权6. 修改配置文件把配置文件复制到/etc下# cp /usr/local/mysql/support-files/my-f /etc/f注：如果你的内存64M，则复制my-f为/etc/f如果内存是128M，则复制my-f为/etc/f如果内存

4、是512M，则复制my-f为/etc/f如果内存是1-2G，则复制my-f为/etc/f如果内存是4G，则复制my-innodb-heavy-4G.cnf为/etc/f7. 设置mysql中文编码问题注：这一步在创建用户数据库之前完成，并要重启MYSQL#vi /etc/fclient#password = your_passwordport = 3306socket = /tmp/mysql.sock#添加的内容default-character-set=gb2312mysqldport = 3306socket = /tmp/mysql.sock#添加的内容default-characte

5、r-set=utf88. 设置Mysql执行程序的path环境变量#vi /etc/profile在最后处添加：PATH=$PATH:/usr/local/mysql/bin export PATH保存退出。#source /etc/profile9. 设置随系统开机启动9.1 启动mysql，如果一切正常的话，运行此命令后，不会有错误提示#/usr/local/mysql/bin/mysqld_safe -user=mysql &注：在Unix和NetWare中推荐使用mysqld_safe来启动mysqld服务器。mysqld_safe增加了一些安全特性，例如当出现错误时重启服务器并向错误

6、日志文件写入运行时间信息。9.2 将mysql.server这个文件copy到/etc/init.d/目录下，并更名为mysql#cp support-files/mysql.server /etc/init.d/mysql9.3 给/etc/init.d/mysql这个文件赋予“执行”权限#chmod 755 /etc/init.d/mysql9.4 加入到开机自动运行，运行级别为3 4 5#chkconfig -level 345 mysql on9.5 重启mysql服务#ps ef|grep mysql#kill -9 id#service mysql start注：如果这里启动失败报

7、错，类似这样的错误信息“Starting MySQL.Manager of pid-file quit without updating file.失败”，不要担心，先看下mysql的日志究竟报了什么错，#vi /usr/local/mysql/data/*.err（星号表示你主机的名字），错误的日志如下：InnoDB: Error: data file ./ibdata1 is of a different sizeInnoDB: 640 pages (rounded down to MB)InnoDB: than specified in the .cnf file 64000 pages

8、!InnoDB: Could not open or create data files.InnoDB: If you tried to add new data files, and it failed here,InnoDB: you should now edit innodb_data_file_path in f backInnoDB: to what it was, and remove the new ibdata files InnoDB createdInnoDB: in this failed attempt. InnoDB only wrote those files f

9、ull ofInnoDB: zeros, but did not yet use them in any way. But be careful: do notInnoDB: remove old data files which contain your precious data!120410 9:43:24 ERROR Plugin InnoDB init function returned error.120410 9:43:24 ERROR Plugin InnoDB registration as a STORAGE ENGINE failed.120410 9:43:24 ERR

10、OR Unknown/unsupported table type: InnoDB120410 9:43:24 ERROR Aborting从日志看到，文件ibdata1跟配置文件里面设置的值不一致，无法创建或打开该文件，如果你想新增一个datafile，同时又失败了，你应该把移除旧的datafile，然后重新启动mysql，这样就OK啦。10. 修改mysql超级用户root密码# mysqladmin -u root -p password 123456 （注：此处改密码为123456为例）Enter password: 回车就行 ，默认的密码为空11. 优化、安全设置#vi /etc/f

11、添加以下内容（注：所设置的某些参数要根据实际进行调整）：default-storage-engine=InnoDBinnodb_file_per_tableinnodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M:autoextendinnodb_log_file_size = 500Minnodb_log_files_in_group = 4innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2inn

12、odb_buffer_pool_size = 4Ginnodb_additional_mem_pool_size = 16Mmax_connections = 65535tmp_table_size = 200Mmax_heap_table_size = 256Mquery_cache_size= 64Mquery_cache_limit = 4Mwait_timeout = 300interactive_timeout = 300thread_cache_size = 64skip-external-lockingkey_buffer = 256Mmax_allowed_packet = 4

13、Mtable_cache = 512sort_buffer_size = 6Mnet_buffer_length = 8Kread_buffer_size = 4Mread_rnd_buffer_size = 1024Kmyisam_sort_buffer_size = 64M# Try number of CPUs*2 for thread_concurrencythread_concurrency = 4local-infile = 0#以下这些系统缺省配置，跟上面的配置有冲突，需要屏蔽：#key_buffer_size = 256M#max_allowed_packet = 1M#tab

14、le_open_cache = 256#sort_buffer_size = 1M#read_buffer_size = 1M#read_rnd_buffer_size = 4M#myisam_sort_buffer_size = 64M#thread_cache_size = 8#query_cache_size= 16M附注解：#缺省存储引擎default-storage-engine=InnoDB#使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间innodb_file_per_table#数据文件个数及最后一个文件自动扩充2G为限innodb_data_file

15、_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M:autoextend#日志文件大小innodb_log_file_size = 500M#指定日志组的文件个数innodb_log_files_in_group = 4#事务在内存中的缓冲。这个参数设置 InnoDB 用来往磁盘上的日志文件写操作的缓冲区的大小。这有点像 Oracle 的 log_buffer ，通过内存缓冲来延缓磁盘 I/O 以提高访问的效率。 因为 MySQL 每秒都会将日志缓冲区的内容刷新到日志文件，因此无需设置超过 1

16、秒所需的内存空间。通常设置为 8 16MB 就足够了，默认值是 1MB。innodb_log_buffer_size = 8M#控制事务的提交方式。抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即

17、使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。innodb_flush_log_at_trx_commit = 2#缓存innodb表的索引，数据，插入数据时的缓冲。官方的说明是# You can set ._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high。所以要根据本机实际的内存设定，这里只设置4G。（很有用）innodb_buffer_pool_size = 4G#用来存放Innodb的内部目录。这个参数用来设置 InnoDB 存储

18、的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多，你需要在这里分配越多的内存。对于一个相对稳定的应用，这个参数的大小也是相对稳定的，也没有必要预留非常大的值。如果 InnoDB 用光了这个池内的内存， InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。默认值是 1MB ，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小。innodb_additional_mem_pool_size = 16M#最大连接数（很有用）max_connections = 65535#临时表大小。mysql 的配置文件中，tmp_table_size 的

19、默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。tmp_table_size = 200M#这个变量定义了用户可以创建的内存表(memory table)的大小。和tmp_table_size一起限制了内部内存表的大小。max_heap_table_size = 256M#查询结果缓存。第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。服务器下一次碰到这个语句的时候，它不会再次执行该语句。作为代替，它

20、直接从查询缓存中的得到结果并把结果返回给客户端。query_cache_size= 64M#指定单个查询能够使用的缓冲区大小，缺省为1M。query_cache_limit = 4M#服务器在关闭连接之前在一个连接上等待行动的秒数，默认数值是28800，即如果没有事情发生，服务器在 8个小时后关闭连接。wait_timeout = 300#服务器在关闭连接前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户，默认数值是28800。interactive_timeout = 300#每建立一个连接

21、，都需要一个线程来与之匹配。用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。根据物理内存设置规则如下：1G设置为8，2G为16，3G为32，大于3G设置为64。thread_cache_size = 64#取消文件系统的外部锁。当外部锁定（external-locking）起作用时，每个进程若要访问数据表，则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁，因此在单服务器环境下external

22、locking会让MySQL性能下降。所以在很多Linux发行版的源中，MySQL配置文件中默认使用了skip-external-locking来避免external locking。skip-locking（注：以后用Mysql版本参数用skip-external-locking）#指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可

23、以使用show status like key_reads获得）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是 MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。key_buffer = 256M#一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺 省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。如：通常通

24、过MySQL的load data local infile语句将一个文本文件中的内容导入到数据库中，这样速度会很快，但今天发现如果文本的大小超过1M时，出现异常：“Packets larger than max_allowed_packet are not allowed”。max_allowed_packet = 4M#指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的 状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_ta

25、bles等于 table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用show status like Open_tables获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能 不稳定或者连接失败。table_cache = 512#每个线程排序所需的缓冲。sort_buffer_size = 6M#用于建立连接时的连接缓冲和结果缓冲。最小值是1K，最大值是1M。但有需要时，可以动态扩大到 max_allowed_packet的大小。貌似跟mysqldu

26、mp时有关。net_buffer_length = 8K#当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。read_buffer_size = 4M#加速排序操作后的读数据，提高读分类行的速度。如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。仍然不明白这个选项的用处read_rnd_buffer_size = 1024K#用于REPAIR

27、TABLE。不明白这个选项的用处，XX上找到的设置方向也是五花八门，有128M、64M、32M等，折中选一个。myisam_sort_buffer_size = 64M# Try number of CPUs*2 for thread_concurrency。最大并发线程数，为CPU个数的2倍（根据实际情况设置）thread_concurrency = 4（查看本机CPU个数命令：#grep physical id /proc/cpuinfo | sort | uniq | wc -l）#禁止MySQL中用“LOAD DATA LOCAL INFILE”命令。这个命令会利用MySQL把本地文件

28、读到数据库中，然后用户就可以非法获取敏感信息了。网络上流传的一些攻击方法中就有用它的，它也是很多新发现的SQL Injection攻击利用的手段！local-infile = 012. mysql常用查看系统参数命令参考官方文档：13. mysql常用命令导出整个数据库结构和数据mysqldump -uroot -p123456 database dump.sql导出单个数据表结构和数据mysqldump -uroot -p123456 database tablename tablename.sql导出整个数据库结构（不包含数据，-d参数）mysqldump -d -uroot -p1234

29、56 database dump.sql导出单个数据表结构（不包含数据）mysqldump -d -uroot -p123456 database table dump.sql导出整个数据库数据（不包含结构，-t参数）mysqldump -t -uroot -p123456 database dump.sql导出部分表数据（忽略几张表，连同结构都会忽略，-ignore-table参数）mysqldump -uroot -p123456 -ignore-table=database.tablename -ignore-table=database.tablename -ignore-table=

30、database.tablename database database.sql以一行一行形式导出数据（-skip-extended-insert参数）mysqldump -uroot -p123456 -skip-extended-insert databae tablename dump.sql导出数据库（记录：file和position记录的位置就是slave从master端复制文件的起始位置，此参数一般用于同步,-master-data=1参数）mysqldump -uroot -p -master-data=1 database database.sql根据某个条件导出数据（-w参数

31、）mysqldump -uroot -p123456 database table -w条件语句（where后面的sql语句）dump.sql脚本自动执行source命令方法（-e参数）mysql（注意：是mysql，而不是mysqldump） -uroot -p123456 database -e source xxx.sql删除权限REVOKE privileges ON 数据库名.表名 FROM user_name 更改表字段alter table 表名 modify 字段 varchar(20);删除字段ALTER TABLE 表名 drop 字段;更新表update 表 set 字段 where 条件加主键alter table 表名 add PRIMARY KEY (字段