**
MySQL安装、配置、优化
**
MySQL安装
1、配置bin到path
2、mysql解压版本在根目录创建my.ini
3、初始化数据库:mysqld --initialize --console ,这里会生成一个初始密码,需要记录下来,否则需要删除datadir目录重新初始化
4、安装服务:mysqld --install 服务名 ,服务名可以是任意,最好如:mysql5、mysql8等以便区分(这里以mysql8作为服务名)
mysqld --verbose --help 查看帮助
5、卸载服务: mysqld --remove mysql8
D:\dev\mysql-8.0.11-winx64>mysqld --install MySQL8
Service successfully installed.
启动
6、更改密码:
mysql -u root -p 输入第3步的密码
在mysql shell中执行:ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
**
MySQL配置
[client]
port = 3309
socket = /home/mysql/mysql/tmp/mysql.sock
[mysqld]
!include /home/mysql/mysql/etc/mysqld.cnf #包含的配置文件 ,把用户名,密码文件单独存放
port = 3309
socket = /home/mysql/mysql/tmp/mysql.sock
pid-file = /longxibendi/mysql/mysql/var/mysql.pid
basedir = /home/mysql/mysql/
datadir = /longxibendi/mysql/mysql/var/
tmp dir settings
tmpdir = /home/mysql/mysql/tmp/
slave-load-tmpdir = /home/mysql/mysql/tmp/
#当slave 执行 load data infile 时用
#language = /home/mysql/mysql/share/mysql/english/
character-sets-dir = /home/mysql/mysql/share/mysql/charsets/
skip options
skip-name-resolve #grant 时,必须使用ip不能使用主机名
skip-symbolic-links #不能使用连接文件
skip-external-locking #不使用系统锁定,要使用myisamchk,必须关闭服务器
skip-slave-start #启动mysql,不启动复制
#sysdate-is-now
res settings
back_log = 50 #接受队列,对于没建立tcp连接的请求队列放入缓存中,队列大小为back_log,受限制与OS参数
max_connections = 1000 #最大并发连接数 ,增大该值需要相应增加允许打开的文件描述符数
max_connect_errors = 10000 #如果某个用户发起的连接error超过该数值,则该用户的下次连接将被阻塞,直到管理员执行flush hosts ; 命令;防止黑客
#open_files_limit = 10240
connect-timeout = 10 #连接超时之前的最大秒数,在Linux平台上,该超时也用作等待服务器首次回应的时间
wait-timeout = 28800 #等待关闭连接的时间
interactive-timeout = 28800 #关闭连接之前,允许interactive_timeout(取代了wait_timeout)秒的不活动时间。客户端的会话wait_timeout变量被设为会话interactive_timeout变量的值。
slave-net-timeout = 600 #从服务器也能够处理网络连接中断。但是,只有从服务器超过slave_net_timeout秒没有从主服务器收到数据才通知网络中断
net_read_timeout = 30 #从服务器读取信息的超时
net_write_timeout = 60 #从服务器写入信息的超时
net_retry_count = 10 #如果某个通信端口的读操作中断了,在放弃前重试多次
net_buffer_length = 16384 #包消息缓冲区初始化为net_buffer_length字节,但需要时可以增长到max_allowed_packet字节
max_allowed_packet = 64M #
#table_cache = 512 #所有线程打开的表的数目。增大该值可以增加mysqld需要的文件描述符的数量
thread_stack = 192K #每个线程的堆栈大小
thread_cache_size = 20 #线程缓存
thread_concurrency = 8 #同时运行的线程的数据 此处最好为CPU个数两倍。本机配置为CPU的个数
qcache settings
query_cache_size = 256M #查询缓存大小
query_cache_limit = 2M #不缓存查询大于该值的结果
query_cache_min_res_unit = 2K #查询缓存分配的最小块大小
default settings
time zone
default-time-zone = system #服务器时区
character-set-server = utf8 #server级别字符集
default-storage-engine = InnoDB #默认存储
tmp & heap
tmp_table_size = 512M #临时表大小,如果超过该值,则结果放到磁盘中
max_heap_table_size = 512M #该变量设置MEMORY (HEAP)表可以增长到的最大空间大小
log-bin = mysql-bin #这些路径相对于datadir
log-bin-index = mysql-bin.index
relayrelay-log = relay-log
relayrelay_log_index = relay-log.index
warning & error log
log-warnings = 1
log-error = /home/mysql/mysql/log/mysql.err
log_output = FILE #参数log_output指定了慢查询输出的格式,默认为FILE,你可以将它设为TABLE,然后就可以查询mysql架构下的slow_log表了
slow query log
slow_query_log = 1
long-query-time = 1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /home/mysql/mysql/log/slow.log
#log-queries-not-using-indexes
#log-slow-slave-statements
general_log = 1
general_log_file = /home/mysql/mysql/log/mysql.log
max_binlog_size = 1G
max_relay_log_size = 1G
if use auto-ex, set to 0
relay-log-purge = 1 #当不用中继日志时,删除他们。这个操作有SQL线程完成
max binlog keeps days
expire_logs_days = 30 #超过30天的binlog删除
binlog_cache_size = 1M #session级别
replication
replicate-wild-ignore-table = mysql.% #复制时忽略数据库及表
replicate-wild-ignore-table = test.% #复制时忽略数据库及表
slave_skip_errors=all
key_buffer_size = 256M #myisam索引buffer,只有key没有data
sort_buffer_size = 2M #排序buffer大小;线程级别
read_buffer_size = 2M #以全表扫描(Sequential Scan)方式扫描数据的buffer大小 ;线程级别
join_buffer_size = 8M # join buffer 大小;线程级别
read_rnd_buffer_size = 8M #MyISAM以索引扫描(Random Scan)方式扫描数据的buffer大小 ;线程级别
bulk_insert_buffer_size = 64M #MyISAM 用在块插入优化中的树缓冲区的大小。注释:这是一个per thread的限制
myisam_sort_buffer_size = 64M #MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_max_sort_file_size = 10G #MyISAM 如果临时文件会变得超过索引,不要使用快速排序索引方法来创建一个索引。注释:这个参数以字节的形式给出.重建MyISAM索引(在REPAIR TABLE、ALTER TABLE或LOAD DATA INFILE过程中)时,允许MySQL使用的临时文件的最大空间大小。如果文件的大小超过该值,则使用键值缓存创建索引,要慢得多。该值的单位为字节
myisam_repair_threads = 1 #如果该值大于1,在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)
myisam_recover = 64K#允许的GROUP_CONCAT()函数结果的最大长度
transaction_isolation = REPEATABLE-READ
innodb_file_per_table
#innodb_status_file = 1
#innodb_open_files = 2048
innodb_additional_mem_pool_size = 100M #帧缓存的控制对象需要从此处申请缓存,所以该值与innodb_buffer_pool对应
innodb_buffer_pool_size = 2G #包括数据页、索引页、插入缓存、锁信息、自适应哈希所以、数据字典信息
innodb_data_home_dir = /longxibendi/mysql/mysql/var/
#innodb_data_file_path = ibdata1:1G:autoextend
innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend #表空间
innodb_file_io_threads = 4 #io线程数
innodb_thread_concurrency = 16 #InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制
innodb_flush_log_at_trx_commit = 1 #每次commit 日志缓存中的数据刷到磁盘中
innodb_log_buffer_size = 8M #事物日志缓存
innodb_log_file_size = 500M #事物日志大小
#innodb_log_file_size =100M
innodb_log_files_in_group = 2 #两组事物日志
innodb_log_group_home_dir = /longxibendi/mysql/mysql/var/#日志组
innodb_max_dirty_pages_pct = 90 #innodb主线程刷新缓存池中的数据,使脏数据比例小于90%
innodb_lock_wait_timeout = 50 #InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的 锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
#innodb_flush_method = O_DSYNC
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
disable-auto-rehash #允许通过TAB键提示
default-character-set = utf8
connect-timeout = 3
MySQL优化
**
MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。
一、服务器硬件对MySQL性能的影响
1、 磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例,这种硬盘理论上每秒寻道7200次,这是物理特性决定的,没有办法改变。 MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量 在100万PV以上的Discuz!论坛,由于磁盘I/O的制约,MySQL的性能会非常低下!解决这一制约因素可以考虑以下几种解决方案: 使用RAID-0+1磁盘阵列,注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。
2、CPU 对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU,例如:可以使用两颗Intel Xeon 3.6GHz的CPU,现在我较推荐用4U的服务器来专门做数据库服务器,不仅仅是针对于mysql。
3、物理内存对于一台使用MySQL的Database Server来说,服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存,不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了16G。
二、 MySQL自身因素
当解决了上述服务器硬件制约因素后,让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。 由于my.cnf文件的优化设置是与服务器硬件配置息息相关的,因而我们指定一个假想的服务器硬件环境:
下面,我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明:
#vim /etc/my.cnf以下只列出my.cnf文件中[mysqld]段落中的内容,其他段落内容对MySQL运行性能影响甚微,因而姑且忽略。
代码如下 复制代码
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
#避免MySQL的外部锁定,减少出错几率增强稳定性。
skip-name-resolve
#禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
back_log = 384
#back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占,如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。
read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的 情况;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓 冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示,则需要增大该参数值。
max_connect_errors = 10000000
wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。
thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为42=8
skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!
table_cache=1024
#物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M
#默认为2M
innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=2M
#默认为1M
innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M
#默认为218,调到128最佳
tmp_table_size=64M
#默认为16M,调到64-256最挂
read_buffer_size=4M
#默认为64K
read_rnd_buffer_size=16M
#默认为256K
sort_buffer_size=32M
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32M