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;
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,INSERT,UPDATE,DELETE,CREATE,DROP ON dbname.* TO 'admin'@'%' 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服务即可
