mysql数据库-运维合集

RDBMS 术语

数据库: 数据库是一些关联表的集合
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
表头(header): 每一列的名称;
列row: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。每一行用来描述某物的具体信息;
行col：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
值value: 行的具体信息, 每个值必须与该列的数据类型相同;
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
键(key): 表中用来识别某个特定的人\物的方法, 键的值在当前列中具有唯一性。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

整删改查操作

库操作

create databae dbname;
mysqladmin -u root -p create DBNAME

use dbname;
select database()； #查询当前正在使用的数据库名称 
drop database dbname;

表操作

# 创建表
# CREATE TABLE table_name (column_name column_type);
create table mytable(name varchar(20),sex(char(1),birth date);
CREATE TABLE IF NOT EXISTS `test_tb`(
   `tb_id` INT NOT NULL AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `tb_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

show tables;
DROP TABLE table_name;

# 插入数据
INSERT INTO test_tb
   (title,author, submission_date)
   VALUES
   ("MySQL", "LESSON", NOW()); 

# 查询数据
select * from test_tb;
SELECT * from test_tb WHERE title='MySQL';

# 更新数据
UPDATE test_tb SET title='python' WHERE id=1;

# 删除数据
DELETE FROM test_tb WHERE runoob_id=1;

# 查看表结构
SHOW COLUMNS FROM test_tb;
SHOW CREATE TABLE test_tb;

账号与授权

create user 'admin@'%' identified by '123456';  #创建远程管理员账户
set password for admin=password('123456');  #设置密码
rename user admin to guest;
drop user guest; show grants for guest;
select * from mysql.user\G
select host,user,password from mysql.user\G
grant all on mysql.* to guest;
grant all privileges on *.* to 'admin'@'%';
update user set password=password('123456') where user='root' and host='localhost';
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, SHOW DATABASES ON *.* TO 'user'@'%' IDENTIFIED BY '123456';
grant all privileges on *.* to 'user'@'%' IDENTIFIED BY '123456'; 
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON dbname.* TO 'admin'@'172.16.0.%' IDENTIFIED BY '123456';
revoke select on mysql.* from admin;    #撤销权限
revoke all on mysql.* from jifei; alter user root@'localhost' identified by 'root';
alter user root@'localhost' identified by 'root';
FLUSH PRIVILEGES;    #用于数据库用户信息更新后,重启mysql服务器也可以
SELECT host, user, password FROM mysql.user WHERE user = 'admin';

#修改不同客户端连接的限制  Client does not support authentication protocol requested by server；
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

匹配符(条件查询)

> 、< 、<= 、>= 、= 、<>
BETWEEN...AND
 IN( 集合)
LIKE 模糊查询
_  单个任意字符
%  多个任意字符
IS NULL
and  或 &&
or  或 ||
not  或 !

MySQL三大类数据类型

数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型

数字类型
整数: tinyint、smallint、mediumint、int、bigint
浮点数: float、double、real、decimal
日期和时间: date、time、datetime、timestamp、year
字符串类型
字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

函数

MySQL函数用来实现数据库操作的一些高级功能, 这些函数大致分为以下几类: 字符串函数、数学函数、日期时间函数、搜索函数、加密函数、信息函数

其他操作

查看数据库的占用空间大小

use information_schema;

#查看所有数据库大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data_size from tables;

#查看home库的大小 #查看home库下的members表的大小
SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESCselect concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

# 查看所有数据库容量大小 
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';

#查看所有数据库各表容量大小 
select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from 
information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc;  

select table_schema as '数据库', table_name as '表名', table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;

开启慢查询

show variables like '%version%';    #显示当前数据库中与版本号相关的东西 
slow_query_log 的值为ON为开启慢查询日志，OFF则为关闭慢查询日志 
slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式） 
long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒 
log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启 
show variables like '%query%';    
show global status like ‘%slow%’;    #查询当前慢查询的语句的个数 
select sleep(1);   #制造慢查询语句 
set global slow_query_log=1;    #开启慢查询功能，1是开启，0是关闭 
set global long_query_time=1;  #慢查询时间设置为1秒，5.6之后允许设置少于1秒，例如0.1秒 
set global slow_query_log_file='/tmp/slow_querys.log'; 
set global log_output='TABLE';    #设置慢查询记录到表中 

#查看零时慢查询功能设置是否成功 
show variables like 'long%'; 
show variables like 'slow%'; 
mysqldump slow -s c -t 10 `/usr/local/mysql/data/community-dev3-slow.log`

状态查询

select user(); #查询当前登陆的用户
show engine innodb status;   #看看InnoDB所有的数据都已经同步到磁盘上去了
how status like '%threads%';  #查看连接数
show variables like '%connection%';
mysql -uroot -p123456 -h127.0.0.1 hellodb -e show tables

字符集设置

遵循的标准：数据库，表，字段和页面或文本的编码码统一
show variables like '%character%';         查看数据库字符集
/etc/my.cnf        default-character-set = utf8    修改字符集为utf8
alter database mydb character set utf8;
create database mydb character set utf8;

临时设置数据库字符集
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

忘记密码重置

忘记root密码更改配置文件采取使用免密启动，更改密码后再还原重启，跳过权限表的限制，不用密码验证，直接登录数据my.cnf在 [mysqld] 中添加 skip-grant-tables

# 1 my.cnf  [mysqld] 中添加 skip-grant-tables字段

# 2 重启DB，重置密码
service mysqld restart
mysql -h localhost
use mysql;
update user set password = password('new-password') where user='root';
update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';
flush privileges;

# 3 删除skip-grant-tables重启mysql服务即可

mysql 慢查询关闭

#/bin/bash
DDT="eab0a9898a707bcd3eebc8d6594b70cd9c45b8a2d75aa92b6b57aef0b68411a5"
apihost="rr-23e8skds0htc2z4vo.mysql.rds.aliyuncs.com"  
passwd='123456'
for ((;;)) ;do
    mysqladmin -h${apihost} -u${user} -p${passwd} processlist | awk -F '[|]' '{print  $2 ","$3 ","$4"," $5"," $6 ","$7 ","$8 ","$9}' | grep -v - | awk -F'[,]' '{if ($5~/Query/ && $8~/^[ ]SELECT/ && $6 > 5) print $0}'|while read line;do
        echo " $line" >> /tmp/mysql.warr
        id=`echo $line | awk '{print $1}'`
        mysqladmin -h${apihost} -uroot -p${passwd}  kill ${id}
	/usr/bin/python /data/script/rds/DingDing.py ${DDT} "API慢查询： ${line}"
    done
    sleep 5
done


#!/usr/bin/python
# DingDing.py
import json,urllib2,time
import sys
Token = sys.argv[1]
Message = sys.argv[2]
def dd():
    url = "https://oapi.dingtalk.com/robot/send?access_token=%s" %(Token)
    con = {"msgtype":"text","text":{"content":Message}}
    jd = json.dumps(con)
    req = urllib2.Request(url,jd)
    req.add_header('Content-Type', 'application/json')
    response=urllib2.urlopen(req)
    #print response,time.ctime()
if __name__ == '__main__':
    dd()

剩下的交给DBA

posted @ 2021-06-04 06:31 AlexG 阅读(174) 评论(0) 编辑收藏举报

刷新页面返回顶部

Alex·G