mysql运维之---知识积累

一、mysql sql处理业务类

1.1、通过生日计算周岁

select date_format(from_days(to_days(now())-to_days('1788-11-26')),'%Y')+0;

1.2、mysqladmin命令修改密码，-h指定数据库服务器的ip

# /usr/mysql/bin/mysqladmin -h 192.168.0.%  -uyangsq -p password
Enter password: 
/usr/mysql/bin/mysqladmin: connect to server at '192.168.0.%' failed
error: 'Unknown MySQL server host '192.168.0.%' (2)'
Check that mysqld is running on 192.168.0.% and that the port is 3306.
You can check this by doing 'telnet 192.168.0.% 3306'
# /usr/mysql/bin/mysqladmin -h 192.168.0.3  -uyangsq -p password

Enter password: 
New password: 
Confirm new password: 

1.3、关闭多个服务器，必须连接到指定的端口号

# /usr/mysql/bin/mysqladmin --port 3306 shutdown

1.4、在表的某个字段后边添加新的字段名

alter table stuscore add column classid int not null after course;

1.5、多表关联删除多表
delete t1,t2 from class t1 inner join stuscore t2 on t1.classid=t2.classid and t1.classid in(1,2);

1.6、服务器维护许多提供操作相关信息的状态变量。用FLUSH STATUS语句可以将许多状态变量重设为0。

show status like '%thread%';

Threads_connected：当前打开的连接的数量。

Threads_created：创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
Threads_running：激活的（非睡眠状态）线程数。

1.7、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' |
+------------------------------------------+

1.8、shell中-n不为空返回真，使用-n判空，必须考虑把变量用""包含

1.9、表a关联表b，b的某个字段值更新a的某个字段值

UPDATE a,b set b.qid = a.qid, b.q_index = a.question_index, b.c_index = a.comment_index where a.questionid=b.questionid;
UPDATE a inner join b on a.questionid=b.questionid set b.qid = a.qid, b.q_index = a.question_index, b.c_index = a.comment_index;

1.10、计算某个库数据和索引所占空间的大小

select table_schema,sum(round(data_length/1024/1024,1)) as "data  mb",sum(round(index_length/1024/1024,1)) as "index mb",sum(round((data_length+index_length)/1024/1024,1)) as "total mb" from information_schema.tables where table_schema='activity2015';

1.10、unix时间戳转化为日期

select from_unixtime(1444275889,'%Y-%m-%d %H:%i:%S');

1.11、将域分隔符\t替换为,，然后以,为域分隔符load数据

:%s/\t/,/g
load data local infile '/home/hadaemon/esb_applog_20151011v2.log.ip.txt' into table ipaddr fields terminated by '\t';

1.12、###为表loaddata添加新的字段ipid放在首位，并导入部分字段，使ipid递增。
mysql> show create table loaddata\G
*************************** 1. row ***************************
       Table: loaddata
Create Table: CREATE TABLE `loaddata` (
  `ipaddr` varchar(20) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  `province` varchar(20) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> alter table loaddata add ipid int primary key auto_increment first;

mysql> select * from loaddata;
+------+---------------+---------+----------+--------+
| ipid | ipaddr        | country | province | city   |
+------+---------------+---------+----------+--------+
|    1 | 112.17.246.68 | 中国    | 浙江     |        |
|    2 | 223.104.19.19 | 中国    | 河南     |        |
|    3 | 120.15.90.88  | 中国    | 河北     | 衡水   |

mysql> truncate table loaddata;
mysql> load data local infile '/root/loaddata.txt' into table loaddata fields terminated by ',' (ipaddr,country,province,city);
mysql> select * from loaddata;
+------+---------------+---------+----------+--------+
| ipid | ipaddr        | country | province | city   |
+------+---------------+---------+----------+--------+
|    1 | 112.17.246.68 | 中国    | 浙江     |        |
|    2 | 223.104.19.19 | 中国    | 河南     |        |
|    3 | 120.15.90.88  | 中国    | 河北     | 衡水   |
+------+---------------+---------+----------+--------+