mysql数据库相关知识总结

1. 安装数据库

1.1 官网安装

https://www.mysql.com

1.2 yum安装 5.7 版本

yum install mariadb-server -y

开启服务：systemctl start mariadb.service 初始化：mysql_secure_installation#初始化设置 先输入密码 一路回车

1.3 登录数据库

mysql -u root -p 
grep password /var/log/mysqld.log#若自带密码则先查看密码在登陆

1.4 卸载mysql

要把依赖也删除 可以用 yum history undo

1.5 mysql命令客户端常用选项

-A, --no-auto-rehash 禁止补全

-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
mysql  -uroot    -h192.168.91.100     -p'密码'  


-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e   "SQL" 执行SQL命令
-V, --version 显示版本
-v  --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置

2.1 多实例

多实例类似微信双开，端口号类比微信账号，数据库类比聊天窗口，表类比聊天记录MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307、3308等），同时运行多个MySQL服务进程，这些服务进程通过不同的Socket监听不同的服务端口来提供服务。多实例可能是MySQL的不同版本,也可能是MySQL的同一版本实现

• 多实例的好处

可有效利用服务器资源。当单个服务器资源有剩余时，可以充分利用剩余资源提供更多的服务，且可以实现资源的逻辑隔离节约服务器资源。例如公司服务器资源紧张，但是数据库又需要各自尽量独立的提供服务，并且还需要到主从复制等技术，多实例就是最佳选择

• 多实例弊端

存在资源互相抢占的问题。比如：当某个数据库实例并发很高或者SQL查询慢时，整个实例会消耗大量的CPU、磁盘I/O等资源，导致服务器上面其他的数据库实例在提供服务的质量也会下降,所以具体的需求要根据自己的实际情况而定

2.2 ## 安装 mycli 插件 客户端工具

[root@localhost opt]#yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel libffi-devel
[root@localhost opt]#tar zxvf Python-3.7.7_.tgz
[root@localhost Python-3.7.7]#cd Python-3.7.7/
[root@localhost Python-3.7.7]#./configure --prefix=/usr/local/Python-3.7.7/
[root@localhost Python-3.7.7]#make  -j2 &&   make install
[root@localhost Python-3.7.7]#make install
[root@localhost Python-3.7.7]#ln -s  /usr/local/Python-3.7.7/bin/python3.7  /usr/bin/python37
[root@localhost Python-3.7.7]#
ln -s  /usr/local/Python-3.7.7/bin/python3.7  /usr/bin/python37
ln -s /usr/local/Python-3.7.7/bin/pip3.7 /usr/bin/pip37
[root@localhost Python-3.7.7]#pip37 install --upgrade pip -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com
[root@localhost Python-3.7.7]#pip37 install mycli -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com

[root@localhost Python-3.7.7]#ln -s /usr/local/Python-3.7.7/bin/mycli /usr/bin/mycli


[root@localhost Python-3.7.7]#mycli -u root -p 12312

sql 语言

3.1 ## 关系型数据库的常见组件

3.2 SQL语言规范

3.3 SQL 语句的构成

4. 管理数据库

4.1 创建数据库

4.2 新建数据库

4.3 新建数据库 指定字符集

4.4 先判断数据库是否存在

mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
#可以查看警告信息
+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Note  | 1007 | Can't create database 'db1'; database exists |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

4.4 修改数据库属性

4.5 删除数据库

查看数据库

show databases;

4.6 数据类型

4.1.1 整数型

• tinyint(m) 1个字节 范围(-128~127) 有一个正或负的表示符
• smallint(m) 2个字节 范围(-32768~32767)
• mediumint(m) 3个字节 范围(-8388608~8388607)
• int(m) 4个字节 范围(-2147483648~2147483647)
• bigint(m) 8个字节 范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

4.1.2 浮点型(float和double)，近似值

• float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数
• double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

4.1.3定点数

在数据库中存放的是精确值,存为十进制 decimal(5.2) 100.01 - 999.99

格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时

4.1.4 字符串

char(n) 固定长度，最多255个字符,注意不是字节如果超过限制 1是不让你录入， 2是将多余部分截断

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

char与varchar的比较； dev.mysql.com/doc/refman/…

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此

2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节

3.char类型的字符串检索速度要比varchar类型的快

面试题：varchar(50) 能存放几个 UTF8 编码的汉字？

4.2 创建表

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...)
#字段信息
col type1 
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

（ 2 ） 建立表从 查询中来 create table 新表名 select user,host from mysql.user;

4.3 表查看

4.4 表修改

4.4 单表查询

语法：

SELECT
 [ALL | DISTINCT | DISTINCTROW ]
 [SQL_CACHE | SQL_NO_CACHE]
 select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
    [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
    [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR UPDATE | LOCK IN SHARE MODE]

select  age from students; 
select  age,name  from students;

4.4.1 过滤查询

select  age from students; 
select  age,name  from students;

简单事例

select * from students  where name="xi ren" 
select * from students where age=20;    #年龄等于20岁
select * from students where age >20;   #大于20岁
select * from students where age >=20 and age<=30; #20岁到30岁

例子： 不连续的查询: IN (element1, element2, ...)

select * from students where age  in (20,22,30);

例子：空查询: IS NULL, IS NOT NULL

select * from students where classid is null;
select * from students where classid is not null;

例子： 字段使用别名

4.4.2 AND OR 且 或

语法： 语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE "匹配表达式";

复制表结构：

create table test like students; #test表复制students表的表结构

数学函数：

聚合函数

备份

5.1备份恢复概述

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

参考链接: www.toutiao.com/a6939518201…

5.1.2备份类型

• 完全备份，部分备份

  完全备份：整个数据集

  部分备份：只备份数据子集，如部分库或表

• 完全备份、增量备份、差异备份

  增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

  增量备份 还原规则就是 先备先还

差异备份：仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单， 直接还原最新的备份

注意：二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份

• 冷备：读、写操作均不可进行，数据库停止服务
• 温备：读操作可执行；但写操作不可执行
• 热备：读、写操作均可执行

MyISAM：温备，不支持热备 不支持 事务

InnoDB：都支持

备分!

5.1.3备份什么

• 数据
• 二进制日志、InnoDB的事务日志
• 用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
• 服务器的配置文件

5.1.4备份注意要点

• 能容忍最多丢失多少数据
• 备份产生的负载
• 备份过程的时长
• 温备的持锁多久
• 恢复数据需要在多长时间内完成
• 需要备份和恢复哪些数据

5.1.5还原要点

• 做还原测试，用于测试备份的可用性
• 还原演练，写成规范的技术文档

5.1.6备份工具

• cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
• mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
• MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
• mysqlbackup：热备份， MySQL Enterprise Edition 组件
• mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

5.1.7实战案例：数据库冷备份和还原

通过打包备份 数据库文件夹的方式备份（一般用于数据库迁移）

备份方式： 冷备份，一定要先停数据库

备份工具： cp tar 等 scp rsync （同步软件）

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件），表名.ibd(表数据文件)。 实例：

[root@localhost ~]#systemctl stop mysqld
[root@localhost ~]#scp -r  /var/lib/mysql/    192.168.91.101:/data/
#将数据库文件整个打包
[root@localhost ~]#scp  /etc/my.cnf   192.168.91.101:/etc/
#如果修改了配置文件需要一起复制


#模拟破坏数据
[root@localhost ~]#rm -rf /var/lib/mysql


#备份节点
[root@localhost ~]#scp -r  /opt/mysql/    192.168.91.100:/var/lib/

5.2.1 mysqldump 说明

逻辑备份工具：

mysqldump, mydumper, phpMyAdmin

Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份

mysqldump 是完全备份 命令格式:

mysqldump [OPTIONS] database [tables]   
#选择数据库   也可以选择数据库和表  只能备份表

#备份表
mysqldump   -uroot hellodb  students >  b.sql
#备份 hellodb 数据库中的 students表  重定向

#还原
mysql> source  /root/b.sql


#备份数据库
mysqldump   -uroot hellodb
#此处需要先建数据库， 备份只有数据库下的所有表



mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
#-B  可以选择多个数据库  可以备份数据库
mysqldump  -B  hellodb   >  hb.sql

mysqldump  -B  hellodb  mysql  >  all.sql
#一次备份多个数据库











mysqldump [OPTIONS] -A [OPTIONS]       
#备份所有数据库   也可以备份数据库
mysqldump  -A  >all.sql

mysqldump  -A  --source-data=2  > test.sql

#生成新的二进制日志
mysqldump  -A  --source-data=2  -F > test.sql
mysql> show master log 

mysqldump 常见通用选项：

-u, --user=name     User for login if not current user
-p, --password[=name]  Password to use when connecting to server
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name…  #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集


--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）

-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--singletransaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact        #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data    #只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force       #忽略SQL错误，继续执行
--hex-blob        #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，
BIT的数据类型的列时使用，避免乱码
-q, --quick     #不缓存查询，直接输出，加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项： MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项：

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

选项	含义
-A, --all-databases	#备份所有数据库，含create database
-B, --databases db_name…	#指定备份的数据库，包括create database语句
-E, --events：	#备份相关的所有event scheduler
-R, --routines：	#备份所有存储过程和自定义函数
--triggers：	#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8	#指定字符集
--master-data[=#]：	#此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x	--lock-all-tables功能（除非开启-- single-transaction），#注意：MySQL8.0.26版以后，此选项变为--source-data
-F, --flush-logs	#备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， 配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact	#去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data	#只备份表结构,不备份数据,即只备份create table
-t, --no-create-info	#只备份数据,不备份表结构,即不备份create table
-n,--no-create-db	#不备份create database，可被-A或-B覆盖
--flush-privileges	#备份mysql或相关时需要使用
-f, --force	#忽略SQL错误，继续执行
--hex-blob	#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick	#不缓存查询，直接输出，加快备份速度

5.2.1.1 mysqldump 备份表

备份表 将 hellodb 数据库下的students表 打印出来

再将 打印出来的数据库文件保存到文件中

测试进入数据库将 students 表删除

5.2.1.2 mysqldump备份数据库

错误示范， 这样备份只是将hellodb 数据库中的所有表进行了备份， 并没有备份数据库, 必须先选择数据库再还原

备份单个数据库

一次备份多个

5.2.1.3 备份所有数据库

-A 备份所有数据库， 4个原始数据库中 只有mysql包含 [root@ubuntu2204 ~]# mysqldump -uroot -A > all.sql 验证备份的数据库

[root@ubuntu2204 ~]#grep -i '^create database'   database-all.sql 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

5.2.1.4 --source-data（--master-data） 选项

[root@ubuntu2204 ~]#mysqldump -uroot  -A  --source-data=2  > database-all.sql
# 默认值 为1开启     2为注释

[root@ubuntu2204 ~]#grep  -i '^-- change master to'  database-all.sql  
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=34440;
# 此处可以知道上一次备份的结束位置 

[root@ubuntu2204 ~]#mysqlbinlog  -v    /var/lib/mysql/binlog.000004 |grep -n  "^# at"
# 过滤二进制日志 可以得到最后一条指令的位置

5.2.1.5 -F 备份完成后生成新的二进制日志

[root@ubuntu2204 ~]#mysqldump -uroot  -A  --source-data=2  -F > database-all.sql
[root@ubuntu2204 ~]#ls    /var/lib/mysql/binlog*

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       180 | No        |
| binlog.000002 |       404 | No        |
| binlog.000003 |     23714 | No        |
| binlog.000004 |     34440 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |    34440 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.2.2 实战备份 ：恢复误删除的表

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

vim /etc/my.cnf
log_bin=/data/mysql-bin
server-id = 1
chown mysql.mysql /data/ -R
systemctl restart   mysqld


#######2:30  执行全备
mysqldump -uroot -pabc123 -A -F --single-transaction --master-data=2 > /opt/all.sql

#完全备份后数据更新
insert students (name,age,gender) values('rose',20,'f');
insert students (name,age,gender) values('jack',20,'f');

##############10:00  误删除了一个students的表
drop table students;



###########后续其余的表继续更新
 insert teachers (name,age,gender)values('test',30,'M');
 insert teachers (name,age,gender)values('test1',30,'M');
 
 
 
#####10点10分发现进行还原
#停止数据库访问



[root@localhost ~]#grep '\-\- CHANGE MASTER TO'  /opt/all.sql 
#从完全备份中，找到二进制位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;


 
#备份  完全备份后的二进制日志
mysqlbinlog --start-position=154 /data/mysql-bin.000002 >> /opt/inc.sql



#找到  删除的语句
[root@localhost ~]#grep -i "^drop table"  /opt/inc.sql 
DROP TABLE `students` /* generated by server */

#删除 删表的那一行
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql 



#登录数据库还原
set sql_log_bin=0;     #先关闭二进制日志    临时关闭  
set sql_log_bin=1; 


source     /opt/inc.sql