linux MySQL 调优

 

设置更改MySQL root的密码
·   默认root密码是空，可以直接登录
PATH=$PATH：/usr/local/mysql/bin 加入到/etc/profile中
·    使用MySQLadmin -uroot password yourname 设置密码
·    更改root密码 mysqladmin -uroot -p password newpass
·    链接mysql mysql -uroot -p ：mysql -uroot -p -hip -Pport
# PATH=$PATH:/usr/local/mysql/bin 临时生效
 mysql -uroot -p
 vim /etc/profile    PATH=$PATH:/usr/local/mysql/bin
source /etc/profile  永久生效

mysqladmin -uroot password '123456'
mysqladmin -uroot -p123456 password '654321'
 mysql -uroot -p654321 -hlocalhost -p3306

常用命令
·     查看都有那些库 show databases；
·     查看某个库的表use db；show tables；
·  查看标的字段 desc tb；
·  查看建表语句 show create table tb；
·  当前是那个用户 select user（）；
·  当前库select database（）；
 cd /data/mysql/查看库的文件
了解两种引擎的区别
·  创建库create database db1；
·  创建表 create table tb1 (`id` int(4), `name` char(40));
·  查看数据库版本 select version（）；
·  查看mysql状态 show status；
·  修改mysql参数 show variables like 'max_connect%';
set global max_connect_errors=1000; 临时修改
vim /etc/my.conf
log-bin=mysql-bin
max_connection = 1000
 /etc/init.d/mysqld restart
重启mysql
·  查看mysql队列show processlist；-e 输入命令 不登陆执行命令可以写在脚本里
·  创建普通用户并授权graht all on *.* to user1 identified by'111222';  db1库的名字 tb1表的名字
 · grant all on db1.* to 'user1@172.0.0.1' identified by '1234567';
·  mysql -uuser1 -p1234567 -h172.0.0.1 就可以登陆了
·  grant all on db1.* to 'user2'@'%' identified by '232323'
·  更改密码
  update mysql.user SET password=PASSWORD("123456") WHERE user='user1';

 flush privileges;  刷新缓存

·  select count（*）user； 有多少列
·
·  查询语句
·  mysql> select count(*) from mysql.user;
mysql.user表示mysql库的user表；count(*)表示表中共有多少行
·  mysql> select * from mysql.db;这个用来表示查询mysql库的db表中的所有数据，
   也可以查询单个字段或者多个字段:
mysql> select db from mysql.db;
mysql> select db,user  from mysql.db;
   同样，在查询语句中可以使用万能匹配 “%”
mysql> select * from mysql.db where host like '10.0.%';
·  插入一行
mysql>  insert into tb1 values(1, 'abc');

·  更改表的某一行
 insert into tb1 values (2, 'aaa');
 insert into tb1 (id) values (3,);

mysql> update db1.t1 set name='aaa' where id=1;
·   清空表数据
mysql> truncate table db1.t1;
·   删除表
mysql> drop table db1.t1;
·  删除数据库
mysql> drop database db1;
repair table ......use_frm;
·  mysql数据库的备份与恢复
 mysqldump  -uroot -p'yourpassword' mysql >/tmp/mysql.sql
 mysqldump -uroot -p -hlocalhost -p3306 discuz > 1.sql
mysqldump -uroot -p -hlocalhost -p3306 --default-character=gbk discuz > 2.sql
mysql -uroot --default-character=gbk discuz < 2.sql
·  使用 mysqldump 命令备份数据库，-u 和 -p 两个选项使用方法和前面说的 mysql 同样，而后面的 “mysql” 指的是库名，然后重定向到一个文本文档里。备份完后，可以查看 /tmp/mysql.sql 这个文件里的内容
·   mysqldump -uroot -p -hlocalhost -p3306 --default-character=gbk discuz > 1.sql
--default-character=gbk
mysql -uroot --default-character=gbk discuz < 1.sql

·  恢复和备份正好相反:
 mysql -uroot -p'yourpassword' mysql </tmp/mysql.sql
·   repair table tb1 [use frm];
修复表
·

·  mysql root密码忘记了
·vim /e    tc/my.cnf
·加入 skip-grant 重启 mysql 不输出密码就能登录
 update mysql.user SET password=PASSWORD("123456") WHERE user='root'
·一台mysql服务器启动多个端口
安装步骤和之前一样
·  先配置好配置文件
·  根据配置文件，连续多次初始化
./scripts/mysql_install_db --dtatdir=/home/mysql1 --userdir=mysql
·启动/usr/local/mysql/bin/mysqld_multi start [n]

·停止/usr/local/mysql/bin/mysqld_multi stop [n]
·停止需要授权

·
      mysq调优
·架构层；做从库；实现读写分离
·系统层次；增加内存；给磁盘做raid0或者raid5以增加磁盘的读写速度；
可以重新挂载磁盘，并加上noatime，这样可以较少磁盘的i/o;
·mysql本身调优
1）如果在未配置主从同步，可以把bin-log功能关闭，减少磁盘i/o
vim /etc/my.cnf 关掉它 log-bin=mysql-bin

2）在 my.cnf中加上skip-name-resolve，这样可以避免由于解析主机名延迟造成mysql执行慢
3）调整几个关键的buffer和cache；调整的依据；主要更具库的状态来调试
应用层次；查看慢查询日志，根据慢查询日志优化程序中的sql语句比如增加索引
MySQL调优可以从几个方面来做：
1. 架构层：
做从库，实现读写分离；

2.系统层次：
增加内存；
给磁盘做raid0或者raid5以增加磁盘的读写速度；
可以重新挂载磁盘，并加上noatime参数，这样可以减少磁盘的i/o;

3. MySQL本身调优：
(1) 如果未配置主从同步，可以把bin-log功能关闭，减少磁盘i/o
(2) 在my.cnf中加上skip-name-resolve,这样可以避免由于解析主机名延迟造成mysql执行慢
(3) 调整几个关键的buffer和cache。调整的依据，主要根据数据库的状态来调试。如何调优可以参考5.

4. 应用层次：
查看慢查询日志，根据慢查询日志优化程序中的SQL语句，比如增加索引

5. 调整几个关键的buffer和cache

1) key_buffer_size  首先可以根据系统的内存大小设定它，大概的一个参考值：
1G以下内存设定128M；2G/256M; 4G/384M;8G/1024M；16G/2048M.
这个值可以通过检查状态值Key_read_requests和 Key_reads,
可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，
至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。
注意：该参数值设置的过大反而会是服务器整体效率降低!


2) table_open_cache 打开一个表的时候，会临时把表里面的数据放到这部分内存中，一般设置成1024就够了，
它的大小我们可以通过这样的方法来衡量： 如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，
那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。
注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。


3) sort_buffer_size 查询排序时所能使用的缓冲区大小,该参数对应的分配内存是每连接独占!如果有100个连接，
那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在4GB左右的服务器推荐设置为4-8M。


4) read_buffer_size 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!


5) join_buffer_size 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!


6) myisam_sort_buffer_size 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，
一般4G内存给64M即可。


7) query_cache_size MySQL查询操作缓冲区的大小，通过以下做法调整：SHOW STATUS LIKE ‘Qcache%’;
如果Qcache_lowmem_prunes该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
如果该值非常大，则表明经常出现缓冲不够的情况，需要增加缓存大小;Qcache_free_memory:查询缓存的内存大小，
通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，我们可以根据实际情况做出调整。
一般情况下4G内存设置64M足够了。


8) thread_cache_size 表示可以重新利用保存在缓存中线程的数，参考如下值：1G  —> 8 2G  —> 16 3G  —> 32  >3G  —> 64
除此之外，还有几个比较关键的参数：


9) thread_concurrency 这个值设置为cpu核数的2倍即可


10) wait_timeout 表示空闲的连接超时时间，默认是28800s，这个参数是和interactive_timeout一起使用的，
也就是说要想让wait_timeout 生效，必须同时设置interactive_timeout，建议他们两个都设置为10


11) max_connect_errors 是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。
与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000


12) max_connections 最大的连接数，根据业务请求量适当调整，设置500足够


13) max_user_connections 是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。通常我们设置为100足够