linux学习笔记28-mysql的配置

mysql的配置

1、mysql配置文件/etc/my.cnf中的一些配置：

[mysqld]  

port            = 3306       

#指定MySQL侦听的端口          

socket          = /tmp/mysql.sock  

#为MySQL客户程序与服务器之间的本地通信指定一个套接字文件（Linux下默认是/var/lib/mysql/mysql.sock文件）

skip-locking

#避免MySQL的外部锁定，减少出错几率增强稳定性。

skip-name-resolve

#禁止MySQL对外部链接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间，开启该项后，所有远程主机连接授权都要使用IP地址方式

key_buffer_size = 16M

#用于设定索引块的缓冲区大小，增加key_buffer_size可以得到更好处理的索引（对所有读和多重写）。索引被所有的线程共享，具体大小视内存大小设定

max_allowed_packet = 1M  

#接收的数据包大小；增加该值十分 安全，因为仅当需要时才会分配额外内存，如，仅当你发出长查询或MySQL必须返回大的结果行时MySQL才会分配更多内存；该变量之所以取较小默认值是一种预防措施，以捕获客户端你和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出

table_open_cache = 64  

#MySQL每打开一个表 ，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓冲中找不到相应信息时，才会去磁盘上读取，默认值64，假定系统有200个并发连接，则需将参数设置为200×N（N为每个连接所需的文件描述符数目）；当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，会导致客户端失效，连接不上；

sort_buffer_size = 512K  

#MySQL执行排序使用的缓冲大小，针对一个线程分配，N个线程，就需要乘以N。要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段，如果不能，可以尝试增加sort_buffer_size变量的大小；

net_buffer_length = 8K  

read_buffer_size = 256K

#读取查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，如果对表的顺序扫描请求非常频繁，并且频繁扫描速度太慢，可以增加该值及内存缓冲区大小提高其性能

join_buffer_size=256K

#联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，对应每个线程

query_cache_zize=2M

#指定MySQL查询结果缓冲区的大小

read_rnd_buffer_size = 512K  

#随机读缓冲区大小，当按任意顺序（例如安装排序顺序）进行读取时，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值，但MySQL会为每个客户连接发放该缓冲区间，所以应尽量适当设置该值，以避免内存开销过大

myisam_sort_buffer_size = 8M

#MyISAM表发生变化时重新排序所需的缓冲，公共的

thread_concurrency       =4

#最大并发线程数，取值为服务器逻辑CPU数量×2

thread_cache              =8

#该值表示可以重新利用保存在缓存中线程的数量，当断开连接时若缓存中还有空间，那么客户端的线程将被放到缓冲中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么线程将被重新创建，设置规律为：1G内存设置为8，2G内存设置为16，4G以上设置为64

max_connections           =100

#MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为连接数越多，每个连接会占用越多的内存。可以通过’conn%’通配符查看当前状态的连接数量，已确定该值大小

max_connect_errors        =500

对于同一主机，如果超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST.

open_files_limit          =65535

#MySQL打开的文件描述符限制，默认最小 1024

wait_timeout               =8

#表示空的连接超时时间，默认28800s，这个参数必须与interactive_timeout一起使用

interactive_timeout        =8

long_query_time             =1

#慢查询日志的超时时间

log_slow_queries            =/path/to/slow_queries

#慢查询日志路径，必须 和long_query_time一起使用

 

2、忘记root密码

编辑mysql主配置文件my.cnf在[mysqld]字段下添加参数skip-grant，重启数据库服务，在进入数据库就不用授权了

 

修改root密码：

1. #mysql -uroot  

2. #use mysql  

3. #update user set password=password('123456') where user='root';  

4. #flush privileges;  

 

保存后，将my.cnf中的skip-grant注释掉，此时mysql恢复认证：

 

3、skip-innodb  不使用innodb引擎，innodb属于重量级数据库，一般网站不适用，对于访问量非常大的网站才需要使用；

4、配置慢查询日志

5. #log_slow_queries = /path/to/slow  

6. #long_query_time = 1  

5、mysql常用操作

1. #mysql -uuer -ppassword  socket              登录mysql，-S可以指定socket文件

2. #mysql -uroot -p123456 -h127.0.0.1 -P3306   使用tcp/ip登录mysql

3. #select version();                            查看数据库版本

4. #show databases;                              查看所有的数据库

5. #use databasename                             选择指定数据库

6. #show tables;                                  查看数据库里所有表

7. #desc tablename;                               查看指定表

8. #show create table tablename\G;               查看指定表的建表语句;

9. #select user();                                 查看当前用户

10. #select database();                             查看当前库

11. #create database db1;                           创建数据库db1

12. #create table `tab1` (`id` int(4),`name` char(50)) ENGINE=MyISAM DEFAULT CHARSET=gbk;              

13.                                                  创建表tab1

14. #insert into tab1(id,name) values(1,'wei');    表tab1插入数据

15. #select * from tab1;                             查看tab1的数据

16. #update tab1 set id=2 where name='wei';        修改tab1指定数据

17. #delete from tab1 where id=2;                   删除它tab1指定行

18. #truncate table tab1;                            清空表tab1

19. #drop table tab1;                                 删除表tab1

20. #drop database db1;                               删除数据库db1

21. #grant all on wei.* to 'wei1'@'127.0.0.1' identified by '123456';   创建用户同时进行授权

22. #show status;                                     显示mysql运行状态

23. #show variables like '%version%';               不加like筛选，显示所有参数，like支持%通配符筛选

24. #set global max_connections=1000;               修改参数

25. #show processlist;                                查看连接的队列

26. #select count(*) from mysql.user;                查询表行数

27. #repair table tab1 user_frm;                     修复表tab1,user_frm表示使用frm恢复索引

 

6、mysql的备份与恢复

28. #mysqldump -uroot -p123456 db1 > /tmp/1.sql             备份数据库db1

29. #mysql -uroot -p123456 db1 < /tmp/1.sql                  恢复数据库db1

30. #mysqldump -uroot -p123456 db1 tab1 > /tmp/tab1.sql     备份数据库db1的表tab1

31. #mysql -uroot -p123456 db1 < /tmp/tab1.sql               恢复数据库db1的表tab1

32. #加入-d参数，则可以只备份建表语句，而不备份数据：

33. #mysqldump -uroot -p123456 -d db1 tab1 > /tmp/tab1.sql

34. #使用--default-character-set=gbk，则可以设定备份时数据字符集格式，防止乱码：

35. #mysqldump -uroot -p123456 --default-character-set=gbk db1 tab1 > /tmp/tab1.sql

36. #mysql -uroot -p123456 --default-character-set=gbk db1 tab1 </tmp/tab1.sql

Xtrabackup可以用于innodb的增量备份。