mysql运维之---每日一得01

2015年7月1日-------------------

1、MHA修复宕机的机器
首先cat /var/log/manager.log|grep -i "All other slaves should start"确定change master命令，把宕掉的数据库给启动，登陆进去后，slave status为空，使用change master命令设置应用的主节点，启动slave进程
然后设置read_only=1，最后检查复制环境，必须启动mha manager的监控（ps aux|grep perl）并查看状态，删除app1.failover.complete，并把# mysql -e "set global relay_log_purge=0"
2、主从复制中，使用alter event把事件enable，不会影响从库的事件状态SLAVESIDE_DISABLED，进行切换后，现在的主库事件状态SLAVESIDE_DISABLED，需要手动进行enable，可以使用如下方式：
select concat('alter event ',EVENT_SCHEMA,'.',EVENT_NAME,' disable;') from information_schema.events;

2015年7月2日------------------

表结构：

CREATE TABLE `question_2` (
  `qid` int(11) NOT NULL DEFAULT '0',
  `QuestionID` varchar(50) NOT NULL COMMENT '只做数据冗余，不做查询条件，不添加索引',
  `UserID` int(11) DEFAULT NULL,
  `QuestionTitle` varchar(500) NOT NULL,
  `Age` int(11) NOT NULL,
  `Month` int(11) NOT NULL,
  `CatalogID` int(11) NOT NULL,
  `Sex` int(11) NOT NULL,
  `QuestionDesc` longtext NOT NULL,
  `QuestionTag` varchar(400) DEFAULT NULL,
  `Score` int(11) DEFAULT NULL,
  `Anonym` int(11) DEFAULT '0',
  `CommentCount` int(11) NOT NULL DEFAULT '0',
  `Source` int(11) DEFAULT NULL,
  `IsAutoAdd` int(11) DEFAULT '0',
  `QuestionStatus` int(11) DEFAULT NULL,
  `OperateStatus` int(11) DEFAULT '0',
  `OperateTime` datetime DEFAULT NULL,
  `CreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '展示时间',
  `UpdateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`qid`),
  KEY `idx_2_uid_ctime_qstatus` (`UserID`,`CreateTime`,`QuestionStatus`,`OperateStatus`),
  KEY `idx_2_qstatus_opstatus_sc_so` (`QuestionStatus`,`OperateStatus`,`Age`,`Score`,`Source`),
  KEY `idx_2_ctime_qstatus_opstatus` (`CreateTime`,`QuestionStatus`,`OperateStatus`,`CatalogID`,`Age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select count(*) from question_2;-- 4086112
explain select * from `question_2` where `questionstatus` >= 0 and `operatestatus` =2 and `age` in ('1','2') order by qid desc limit 60000,20;
explain select qid from `question_2` where `questionstatus` >= 0 and `operatestatus` =2 and `age` in ('1','2') order by qid desc limit 60000,20;
select count(*) from `question_2` where `questionstatus` >= 0;-- 4064825/4086112
select count(*) from `question_2` where `questionstatus` >= 0 and `operatestatus` =2;-- 3649271/4086112
---------------优化后的sql
explain select * from question_2 inner join 
(select qid from `question_2` where `questionstatus` >= 0 and `operatestatus` =2 and `age` in ('1','2') order by qid desc limit 60000,20) a using (qid);

2015年7月10日------------

3、先给root@localhost创建密码，然后删除mysql.user password=''的用户，记得保留一个本地管理员的用户
mysql> set autocommit=0;
mysql> select * from mysql.user where password is null or password='';
mysql> delete from mysql.user where password is null or password='';
mysql> select host,user,password from mysql.user where password is null or password='';
mysql> commit;
mysql> flush privileges;  ##立刻刷新权限表
4、max_connect_errors：与主机中断的数目超过这个数值，该主机会阻塞之后的连接，可以使用flush hosts命令解锁锁定的主机
5、mysqldump某个库，备份失败
（1）、请检查backup权限是否足够
（2）、是否导出不完整那个库的视图、存储过程、事件等是否访问了一些不存在的表或字段
mysql> create database testdump;
mysql> GRANT ALL PRIVILEGES ON testdump.* TO 'testdump'@'192.168.0.%' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql -h 192.168.0.3 -utestdump -p ##在192.168.0.4上进行连接，通过192.168.0.4出去
mysql> select user();
+----------------------+
| user()               |
+----------------------+
| testdump@192.168.0.3 |
+----------------------+
delimiter $$
CREATE PROCEDURE `test_continue_proc`(IN `v_session_id` int)
BEGIN
declare v_incre int default 0;
while v_incre < 100000 do
insert into test.testincre values(v_incre,concat('yangsq',v_incre,v_session_id));
set v_incre=v_incre+1;
select sleep(10);
end while;
END
$$
delimiter ;
6、日志相关的参数
general-log:查询日志，记录客户端的所有语句。
slow-query-log/long-query-time/slow-query-log-file
log-error/log-bin（默认是关闭的）/binlog-format/binlog_cache_size

binlog日志的删除-----
show master logs;
show slave hosts;
（1）reset master：删除所有的binlog日志，新日志编号从000001开始 例如：在从库执行reset master后，查看show slave status\G没有影响
（2）purge master logs to 'mysql-bin.xxxxxx'：删除xxxxxx编号之前的所有日志
system ls -ltr mysql-bin.*
（3）purge master logs before 'yyyy-mm-dd hh24:mi:ss'：删除指定日期之前产生的所有日志
（4）--expire-logs-days：过了指定的天数后，binlog会被自动删除
mysql> reset slave;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
/*
mysql> start slave; 然后
2015-07-09 18:29:21 18071 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000012', position 8344
2015-07-09 18:29:46 18071 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2015-07-09 18:29:46 18071 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2015-07-09 18:29:46 18071 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './yaolansvr_slave01-relay-bin.000001' position: 4
2015-07-09 18:29:46 18071 [Note] Slave I/O thread: connected to master 'repl1@192.168.0.3:3306',replication started in log 'FIRST' at position 4
2015-07-09 18:29:51 18071 [ERROR] Slave SQL: Error 'Duplicate entry '10122588' for key 'PRIMARY'' on query. Default database: 'stat'. Query: 'INSERT INTO `stat`.`seo_comment_src` (`AutoIncreCID`, `CommentID`, `AutoIncreQID`, `QuestionID`, `ExpertID`, `CommentContent`, `UserID`, `UserName`, `IsBestComment`, `CDate`, `InsertDate`, `InsertStatus`, `pkid_incaiji`, `id`) VALUES (NULL, '1503041036297b89d2c4', NULL, '15030409501268517b3a', NULL, '你好，根据你描述的情况，一般正常闭合是在1.5岁左右的，你宝宝这个可能是血钙偏高的问题。', '1970798', 'xu978322', '0', '2015-02-26 20:23:41', '2015-03-04 10:36:29', '0', '390410', '10122588')', Error_code: 1062
2015-07-09 18:29:51 18071 [Warning] Slave: Duplicate entry '10122588' for key 'PRIMARY' Error_code: 1062
2015-07-09 18:29:51 18071 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 13372
2015-07-09 18:30:20 18071 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
2015-07-09 18:30:20 18071 [Note] Slave I/O thread killed while reading event
2015-07-09 18:30:20 18071 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000012', position 8344
*/
7、主库使用statement binlog format，主库执行load data infile语句会在/tmp产生SQL_LOAD_MB-*文件，从库也是执行load data infile语句，并把主库/tmp的SQL_LOAD_MB-*文件拷贝到从库的/tmp下
如果导入的文件超过1G，则从库报错：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'

max_allowed_packet-The protocol limit for max_allowed_packet is 1GB
slave_max_allowed_packet-The default and maximum value is 1073741824 (1 GB)
select * from testinfile into outfile 'testinfile20150710' fields terminated by ',';
load data infile 'testinfile20150710' into table test.testinfile fields terminated by ',';

2015年7月15日-------------

8、某些常用命令
? contents/? auto_increment
删除test1库下所有以tmp为前缀的表：select concat('drop table test.',table_name,';') from information_schema.tables t1 where t1.table_schema='test' and table_name like 'test%';
# mysql -e "select concat('drop table test.',table_name,';') from information_schema.tables t1 where t1.table_schema='test' and table_name like 'test%';"|grep -Evi "&&|$$"
9、mysql客户端程序使用system clear命令可以清屏；sql_mode的值其实都是一些原子模式的组合，类似于角色和权限的关系
10、##查看所有库的不同引擎的数据大小、索引大小
select  engine,  
round(sum(data_length) /1024/1024, 1) as "data mb",  
round(sum(index_length)/1024/1024, 1) as "index mb",  
round(sum(data_length + index_length)/1024/1024, 1) as "total mb",  
count(*) "num tables"  
from  information_schema.tables  
where  table_schema not in ("information_schema", "performance_schema")  
group by  engine;
11、##查看某个库下所有表的数据表大小、索引大小
select table_schema,table_name,round(data_length/1024/1024,1) as "data  mb",round(index_length/1024/1024,1) as "index mb",round((data_length+index_length)/1024/1024,1) as "total mb" from information_schema.tables where table_schema='yaolanbbs2015';

2015年8月5日-------------

12、监控myisam的索引缓存使用情况
key_buffer_size：索引缓存大小，是对MyISAM表性能影响最大的一个参数索引命中缓存率：
key_buffer_read_hits=(1-Key_reads/Key_read_requests)*100% 
key_buffer_write_hits=(1-Key_writes/Key_write_requests)*100%
mysql> show status like 'key_read%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| Key_read_requests | 1337113881 |
| Key_reads         | 957612     |
+-------------------+------------+
2 rows in set (0.00 sec)
mysql> show status like 'key_blocks%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Key_blocks_not_flushed | 0        |
| Key_blocks_unused      | 16686929 |
| Key_blocks_used        | 1155344  |
+------------------------+----------+
3 rows in set (0.00 sec)
mysql> select 957612/1337113881;
+-------------------+
| 957612/1337113881 |
+-------------------+
|            0.0007 |
+-------------------+
1 row in set (0.00 sec)
mysql> select @@key_buffer_size;
+-------------------+
| @@key_buffer_size |
+-------------------+
|       22548578304 |
+-------------------+
1 row in set (0.00 sec)
mysql> select 1155344*1024/22548578304;
+--------------------------+
| 1155344*1024/22548578304 |
+--------------------------+
|                   0.0525 |
+--------------------------+
13、Innodb_buffer_pool_wait_free
一般情况，通过后台向InnoDB缓冲池写。但是，如果需要读或创建页，并且没有干净的页可用，则它还需要先等待页面清空。该计数器对等待实例进行记数。如果已经适当设置缓冲池大小，该值应小。
Innodb_buffer_pool_pages_data：包含数据的页数(脏或干净)。
Innodb_buffer_pool_pages_free：空页数。
Innodb_data_read：至此已经读取的数据数量（字节）。
Innodb_data_reads：数据读总数量。
Innodb_data_writes：数据写总数量。
Innodb_data_written：至此已经写入的数据量（字节）。
innodb buffer pool命中百分比：Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100 = InnoDB Buffer Pool hit ratio
show status like 'innodb_buffer_pool_%'与select @@innodb_page_size;

2015年8月20日-----

12、query_cache_size调整
query_cache有一个致命的缺陷，当某个表的数据有任何的变化，会导致所有引用了该表的select语句在query cache中的缓存数据失效。所以，当数据变化非常频繁的情况下，使用query cahche可能会得不偿失。
mysql> show status;
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
mysql> show status like 'qcache_%';
mysql> select @@query_cache_type;
select sql_no_cache * from pre_data_post where id=527352;

13、select current_user();
SET PASSWORD [FOR user] =
    {
        PASSWORD('cleartext password')
      | OLD_PASSWORD('cleartext password')
      | 'encrypted password'
    }
mysql> grant select,insert,update,delete on test.* to testpriv@'192.168.0.%' identified by '123456';
mysql> set password for testpriv@'192.168.0.%'=password('yangsq123');

14、mysql> set global log_output='file,table';
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'log_output';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+

mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|          5.000000 |
+-------------------+
1 row in set (0.00 sec)

mysql> set global long_query_time=0.01;
mysql> select sql_no_cache * from sakila.payment t1,sakila.customer t2 where t1.customer_id=t2.customer_id;
mysql> select * from mysql.slow_log where start_time between curdate()-1 and curdate()+1 order by query_time desc;
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------+-----------+
| start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text                                                                                            | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------+-----------+
| 2015-07-24 15:59:52 | root[root] @ localhost [] | 00:00:00   | 00:00:00  |         0 |             0 |    |              0 |         0 |     16803 | truncate table mysql.slow_log                                                                       |         5 |
| 2015-07-24 16:00:05 | root[root] @ localhost [] | 00:00:00   | 00:00:00  |     16049 |         16648 |    |              0 |         0 |     16803 | select sql_no_cache * from sakila.payment t1,sakila.customer t2 where t1.customer_id=t2.customer_id |         5 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------+-----------+

15、MyISAM表索引和数据是分开的，OPTIMIZE TABLE可以整理数据文件，并且可以对索引树进行排序以便更快地查找键值。alter table xxx engine=innodb它会起到优化作用，它会创建整个表，删掉空闲空间。

16、USAGE权限，可以创建账户而不授予任何权限，只能够登录，除了内置的test库，对其他库没有任何权限，show databases只能列出information_schema/test。
usage权限不能被回收，也即REVOKE用户并不能删除用户。
mysql> show grants for root@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
mysql> revoke all privileges,grant option from root@'127.0.0.1';
mysql> show grants for root@'127.0.0.1';
+------------------------------------------+
| Grants for root@127.0.0.1                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' |
+------------------------------------------+

17、对表做优化是一个非常危险的操作，千万不要修复表时，执行中断操作（网络、ctrl+c），例如mysiam表中断优化，is marked as crashed and last (automatic?) repair failed
解决：不停主库，将从库optimize的后的表物理拷贝到主库，覆盖主库数据文件
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'long_query_time';