• 数据库的相关操作


    数据库类型

    mysql
    

    一、版本介绍和选择

    oracle MySQL	8.**0**
    MariaDB
    

    PerconaDB

    主流版本

    mysql   5.6  	5.6.36  5.38  5.6.40
    mysql   5.7	  	5.7.18  5.7.20  5.7.22
    

    企业版本

    6-12月之间的GA
    

    在Linux中启动数据库

    /etc/init.d/mysqld start
    

    二、MYSQL的体系结构

    MYSQL C/S结构

    2种连接方法:TCP/IP(远程,本地);SOCKET(本地)
    mysql -uroot -poldboy123 -h 10.0.0.128 -p3306
    mysql -uroot -poldboy123 -s /tmp/mysql.sock
    

    实例:

    mysqld(最重要)--->master thread--->N Thread  --->内存结构(员工)
    

    1、mysqld 的三层结构

    1、连接层
    	-提供链接协议(TCP/IP,Socket)
    	-提供用户验证
    	-提供专用连接线程
    2、SQL层
    	- 接受上层命令
    	- 提供语法检测
    	- 语义(SQL类型),权限
    	- 专用解析器解析SQL,解析成执行计划
    	- 优化器:帮我们选择一个代价最低的执行计划(cpu,IO,MEM)
    	-执行器:按照优化器的选择,执行SQL语句,得出获取数据的方法
    	-查询缓存:默认关闭, 一般会使用redis产品替代
    	-记录产品日志:查询日志,二进制日志
    3、存储引擎层
    	- 按照SQL层结论,找到想应数据,结构化成表的形式
    		
    

    2、MySQL的逻辑结构

    库(schema):存储表的地方
    表(table):二维表
        元数据:
            -表名
            -表的属性(表的大小,权限,存储引擎,字符集等)
            -列:列名,列属性(数据 类型,约束,其他定义)  
    ======================================
            -记录:数据行
    ======================================
    

    三、MySQL安装

    1、https://www.mysql.com/ MySQL官网

    2、点击DOWNLOADS

    3、点击Archives

    4、点击MYSQL Community Server

    5、

    四、sql语句(SQL92)

    SQL种类

    DDL数据定义语言;
    DCL数据控制语言;
    DML数据操作语言; 
    DQL数据查询语言
    

    SQL语句的操作对象

    库
    表
    

    不同分类语句的作用

    DDL:

    	-库:
    	CREATE DATABASE(创建)
    	DROP DATABASE(删除)
    	ALTER DATABASE(修改)
    	SQL语句建库规范第一条:
    	1、关键字大写(非必须),字面量(用户定义的,必须)
    	2、库名,只能小写,不能有数字开头,不能是预留的关键字
    	3、库名必须和业务名字有关,例如his_user;
    	4、必须加字符集
    	-表
    	CREATE TABLE(创建)
    	DROP TABLE(删除)
    	ALTER TABLE (修改)
    ==================================	
    CREATE TABLE t1 (
    id  INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    sname  VARCHAR(20) NOT NULL COMMENT '用户姓名',
    gender ENUM('f','m','u') NOT NULL DEFAULT 'u' COMMENT '用户性别',
    telnum CHAR(11) NOT NULL UNIQUE COMMENT '手机号',
    tmdate DATETIME NOT NULL DEFAULT NOW() COMMENT '录入时间'
    )ENGINE INNODB CHARSET utf8mb4;
    
    =================================
    	SQL语句建库规范第二条:
    	1、关键字大写(非必须),字面量(用户定义的,必须)
    	2、表名,只能小写,不能有数字开头,不能是预留的关键字
    	3、库名必须和业务名字有关,例如his_user;
    	4、必须加存储引擎和字符集
    	5、使用的数据类型
    	6、必须要有主键
    	7、尽量加非空选项
    	8、字段唯一性
    	9、必须加注释
    	10、避免使用外键,
    	11、建立合理的索引
    

    DCL:

    	grant
    	revoke
    	lock
    

    DML:

    insert
    update
    delete
    -SQL语句按批量插入数据
    -update必须加where条件
    -delete尽量替换为update
    -如果有清空全表需求,不要用delete,推荐使用truncate
    

    DQL:

    selete
    show
    SQL语句规范第四条:
    1. select语句避免使用 select * from t1;---->  select id,name from t1;
    2. select语句尽量加等值的where条件,例如:select * from t1 where id=20;
    3、select语句 对于范围查询,例如:select * from t1 where id>200; 尽量添加limit或者  id>200 and id<300 union all id>300 and id<400 
    4、select的where条件,不要使用<>like '%name' not in not exist
    5、不要出现3表以上的表连接,避免子查询
    6、where条件中不要出现函数操作
    

    SQL语句规范第五条

    1、少于10位的数字int,大于10位数的char,例如手机号
    2、char和varchar选择时,字符长度一定不变的可以使用char,可变的尽量使用varchar,在可变长度的存储时,将来使用不同的数据类型,对于索引树的高度有影响的
    3、选择合适的数据类型
    4、合适的长度
    

    ===============

    五、MySQL 5.7 初始化配置

    5.1 初始化数据:

    /usr/local/mysql/bin/mysqld --initialize-insecure  --user=mysql --datadir=/opt/mysql/data --basedir=/opt/mysql
    

    5.2 配置文件

    vim /etc/my.cnf
    [mysqld]
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/mydata
    socket=/tmp/mysql.sock
    log_error=/var/log/mysql.log
    user=mysql
    port=6606
    [mysql]
    socket=/tmp/mysql.sock
    

    作用:

    1.影响服务端的启动
    标签: [mysqld][mysqld_safe]  [server] ...
    [mysqld]
    basedir=/opt/mysql              
    datadir=/opt/mysql/data
    user=mysql
    socket=/tmp/mysql.sock
    port=3306 
    server_id=6
    
    2.影响客户端连接
    标签: [client][mysql]  [mysqldump] ....
    [mysql] 
    socket=/tmp/mysql.sock
    

    5.3 创建相关目录

    mkdir -p /data/330{7..9}/data 
    

    5.4创建配置文件

    cat>> /data/3307/my.cnf<<EOF
    [mysqld]
    basedir=/opt/mysql              
    datadir=/data/3307/data
    user=mysql
    socket=/data/3307/mysql.sock
    port=3307 
    server_id=3307
    EOF
    
    cp /data/3307/my.cnf /data/3308 
    cp /data/3307/my.cnf /data/3309 
    
    
    
    sed -i 's#3307#3308#g' /data/3308/my.cnf 
    sed -i 's#3307#3309#g' /data/3309/my.cnf 
    

    5.5 初始化数据

    mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
    mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
    mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql
    

    5.6 启动多实例

    chown -R mysql.mysql /data/*
    
    
     mysqld_safe --defaults-file=/data/3307/my.cnf &
     mysqld_safe --defaults-file=/data/3308/my.cnf &
     mysqld_safe --defaults-file=/data/3309/my.cnf &
    

    验证:

    [root@standby data]# netstat -lnp|grep 330
    

    5.7、 systemd管理多实例

    cat >> /etc/systemd/system/mysqld3307.service <<EOF
    [Unit]
    Description=MySQL Server
    Documentation=man:mysqld(8)
    Documentation=![img](file:///C:Users26685AppDataLocalTemp\%W@GJ$ACOF(TYDYECOKVDYB.png)http://dev.mysql.com/doc/refman/en/using-systemd.html
    After=network.target
    After=syslog.target
    [Install]
    WantedBy=multi-user.target
    [Service]
    User=mysql
    Group=mysql
    ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
    LimitNOFILE = 5000
    EOF
    
    cp  /etc/systemd/system/mysqld3307.service   /etc/systemd/system/mysqld3308.service 
    cp  /etc/systemd/system/mysqld3307.service   /etc/systemd/system/mysqld3309.service 
    sed -i 's#3307#3308#g'   /etc/systemd/system/mysqld3308.service
    sed -i 's#3307#3309#g'   /etc/systemd/system/mysqld3309.service
    

    [root@standby ~]# systemctl start mysqld3307
    [root@standby ~]# systemctl start mysqld3308
    [root@standby ~]# systemctl start mysqld3309
    [root@standby ~]# netstat -lnp|grep 330
    [root@standby ~]# systemctl stop mysqld3309
    [root@standby ~]# systemctl stop mysqld3308
    [root@standby ~]# systemctl stop mysqld3307

    [root@standby ~]# systemctl enable  mysqld3307
    [root@standby ~]# systemctl enable  mysqld3308
    [root@standby ~]# systemctl enable  mysqld3309

    六、忘记密码处理:

    mysqladmin -uroot -p password 123
    
    select user,authentication_string,host from mysql.user;
    
    1.停数据库
    /etc/init.d/mysqld stop
    2.启动数据库为无密码验证模式
    mysqld_safe --skip-grant-tables --skip-networking  &
    update mysql.user set authentication_string=PASSWORD('456') where user='root' and host='localhost';
    /etc/init.d/mysqld restart
    
    [root@standby ~]# mysql -uroot -p123
    [root@standby ~]# mysql -uroot -p456
    
    

    七、数据类型和字符集

    int 最多存10位数字
    -2^31` 2^31-1
    2^32 10位数
    浮点:
    字符串类型
    char	定长,存储数据效率较高,对于变化较多的字段,空间浪费较多
    varchar	变长,存储时判断长度,存储会有额外开销按需分配存储空间,
    enum(枚举):	
    时间类型:datetime,timestamp,date,time
    
    

    八、索引及执行计划

    8.1 索引

    • 作用:优化查询,select查询有三种情况:缓存查询(不在MySQL中进行数据查询),全表扫描,索引扫描

    8.2索引种类

    Btree(btree b+tree b*tree)
    Rtreee
    HASH
    FullText
    

    Btree分类

    聚集索引:基于主键,自动生成的,一般是建表时创建主键,自动 选择唯一键作为聚集索引。
    辅助索引:人为创建的 (普通,覆盖) 
    唯一索引:人为创建(普通索引,聚集索引)
    

    聚集索引和辅助索引的区别:

    1、叶子结点,按照主键列的顺序,存储的整行数据 ,就是真正的数据页
    2、辅助索引:叶子结点,列值排序之后,存储到叶子结点+主键对应的主键的值,便于会表查询
    

    8.4 索引管理命令

    8.4.1索引键(key),表中的某个列

    创建普通辅助索引(MUL)
    alter table blog_userinfo add key idx_email(email);
    create index idx_phone on blog_userinfo(phone);
    查看索引
    desc blog_userinfo;
    show index from blog_userinfo;
    删除索引
    alter table blog_userinfo drop index idx_email;
    drop index idx_phone on   blog_userinfo;
    
    前缀索引
    
    select count(*),substring(password,1,20) as sbp  from blog_userinfo group by sbp;
    alter table blog_userinfo add index idx(password(10));
    唯一键索引 (UNI ,不能有重复值)
    alter table 表名  add unique key uni_email(email);
    覆盖索引(联合索引)
    	-作用:不需要会表查询,不需要聚集索引,所有查询的数据都从辅助索引中获取
    
    

    8.5重要的字段:

    8.5.1 type:查询类型

    作用:
    	-可以 判断出,全表扫描还是索引扫描 (all就是全索引扫描,其他的就是索引扫描)
    	-对于索引扫描 来讲,又可以细化分,可以判断出事哪一种类 的索引扫描 
    type的具体类型介绍:
    	All :全表扫描
    	Index:全索引扫描
    		-例子:desc select countrycode from city;
    	range:索引范围扫描
    		< ,>,<=,>=,in,or ,between ,and,like 'CH%'
    in或者or改写成union 
    select *from city where countrycode='CHN'
    union all 
    select *from city where countrycode='USA';
    ref:辅助索引的等值查询
    select *from city where countrycode='CHN';
    eq_ref:多表链接查询(join on)
    const,system主键或唯一键等值查询
    
    
    

    九、 后端服务器

    ping端口号(22,80,443,3306,6397,8080,8000)

    ssh:
    	cpu:
    	mem:
    	IO:
    
    

    查看CPU

    cat /proc/cpuinfo
    
    

    查看所有进程

    ps aux
    
    

    yum install -y sysstat

    服务器的启停:

    ​ 真实硬件:远程管理卡,fence设备等

    ​ 虚拟化产品:kvm,openstack, docker, k8s,vmware esxi

    自动装系统:

    ​ 真实硬件:kickstart + cobbler

    ​ 虚拟化产品:克隆,启动新容器

    自动化配置:

    ​ ansible ,saltstack (批量化配置)

    生命周期管理:

    ​ 启停服务,监控:zabbix(硬件)系统

    Devops:代码上线发布

    现在用git和jenkins发布

    堡垒机(jumpserver)

    VPN

    数据库审核:
    ​ 危险性操作
    ​ SQL性能审计 (全表扫描,抓取执行事件过长的语)
    ​ 性能参数审核,根据性能指标,提出性能优化建议
    ​ 数据库对象监控,提出整改建议
    ​ explain

    存储引擎

    作用:和磁盘的数据打交道

    简介:MySQL基于存储引擎管理,表空间数据文件

    存储引擎种类

    Innodb存储引擎
    ibd:存储表的数据行和索引
    frm:表基本结构信息
    Myisam存储引擎
    frm:表基本结构信息,myi:存索引,myd:存数据行
    
    

    事务

    保证交易的完整性
    	ACID特性
    	Atomic(原子性)
    	所有语句作为一个单元全部成功执行或全部取消。不允许出现中间过程.
    	Consistent(一致性)
    	如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态。 
    	Isolated(隔离性)
    	事务之间不相互影响。
    	两个方面:  修改同一行 , 一致性读
    	Durable(持久性)
    	事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
    
    

    十、日志

    错误日志修改

    log_error=/var/log/mysql.log   分析[error]
    
    

    二进制日志(binog,逻辑型日志)

    作用:记录了所有变更类的语句,可以做数据恢复和操作的审计
    DDL,DCL:以语句方式(startement)记录
    DML:默认是以行模式记录(row模式 ,数据行的变化)
    
    

    配置方法

    查看:show variables like 'log_bin';
    log_bin=/opt/mysql/data/mysql-bin
    binlog_format=row
    server_id=6
    sync_binlog=1
    

    查看日志信息

    mysql> show binary logs;
    mysql> show master status; 查看正在使用的日志
    
    

    日志内容的查看

    按事件查看日志内容

    mysql> show binlog events in 'mysql-bin.000012'; #000012事件
    
    

    直接查看日志内容

    mysqlbinlog --base64-output=decode-rows -vvv /opt/mysql/data/mysql-bin.000012 |more
    
    

    截取二进制日志

    [root@standby data]# mysqlbinlog --start-position=219 --stop-position=186613 /opt/mysql/data/mysql-bin.000012 >/tmp/binlog.sql
    
    

    慢日志(slow-log)

    记录慢语句的日志文件

    vim /etc/my.cnf
    配置:
    slow_query_log=1
    slow_query_log_file=/opt/mysql/data/standby-slow.log
    long_query_time=1	
    log_queries_not_using_indexes=1
    
    
    

    十一、备份恢复

    **备份的种类 **

    逻辑备份:SQL语句备份
    物理备份:数据页备份
    
    

    逻辑备份工具的介绍

    select xxxx from t1  into outfile '/tmp/redis.txt';
    
    mysql -uroot -p123 -e "select concat('hmset city_',id,' id ', id,' name ',name,' countrycode ',countrycode,' district ',district,' population ',population) from world.city limit 10 "|redis-cli	 
    	 
    
    

    mysqldump(逻辑备份工具)

    建个目录:mkdir /backup 用于放备份文件

    -A 全库备份
    ​ mysqldump -uroot -p123 -A >/backup/full.sql

    -B 备份一个或多个指定库
    ​ mysqldump -uroot -p123 -B world bbs >/backup/wb.sql

    备份单库中的表
    ​ mysqldump -uroot -p123 world city country >/backup/ccc.sql

    主从复制:

    ​ 基于二进制日志完成的

  • 相关阅读:
    python基础_数据类型
    python基础_注释和变量
    SQL-32 将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分
    SQL-31 获取select * from employees对应的执行计划
    SQL-30 使用子查询的方式找出属于Action分类的所有电影对应的title,description
    SQL-29 使用join查询方式找出没有分类的电影id以及名称
    SQL-28 查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
    SQL-27 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。 提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
    SQL-26 (二次分组)汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
    SQL-25 获取员工其当前的薪水比其manager当前薪水还高的相关信息
  • 原文地址:https://www.cnblogs.com/xuecaichang/p/10310962.html
Copyright © 2020-2023  润新知