Mysql基础

蒙煋Evanflare
96 阅读18分钟

Linux中MySQL的安装与使用

想要在Linux上安装MySQL服务,首先要做的就是找到MySQL的安装包

有不同的的安装包,rpm的,tar.gz的

选对安装包是关键

注意

最新安装经验请见

./linux 服务初始化.md

在该文件中的linux常见服务安装中的mysql部分

第一步 下载正确的安装包

两种途径下载安装包: 1 官方网站 dev.mysql.com/downloads/m…

官方网站好处不用多说,但就是下载速度慢

2 国内镜像网

例如 阿里云镜像 清华镜像网 等等,下载速度很快

但是很难去寻找下载的路径

第二步 配置Linux的安装环境

如果之前下载过MySQL,需要清理旧的mysql文件

/etc/my.cnf文件

/etc/init.d/mysql

/usr/bin和/usr/share下的mysql相关的文件

这些文件都需要删除

配置MySQL用户

useradd 命令

解压安装包

通过tar -zvxf 安装包 -C 目录

将文件解压到指定目录这里推荐解压到 /usr/local/mysql文件夹中

修改权限

刚刚解压的MySQL目录,使用命令

chown -R mysql:mysql /usr/local/mysql

将文件所有者改成mysql用户

使用chmod修改文件权限

chmod -R 755 /usr/local/mysql

调用MySQL的初始化程序

进入mysql文件中的bin命令

执行如下命令

./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize

在执行这个命令之前,需要确保两几件事情

1 /usr/local/data目录存在 如果不存在就创建

2 /usr/local/data目录必须为空,方可成功初始化

3 /usr/local/data目录权限必须具备

创建MySQL快捷启动方式

ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql #创建mysql快捷方式

以及一些MySQL服务的命令

service mysql start

service mysql stop

service mysql restart

netstat -anp|grep mysqld #查看mysql服务

配置mysql启动文件

在/etc/my.cnf文件中,配置MySQL的启动配置文件

这个配置文件中配置MySQL中的启动参数,帮助优化MySQL的性能

 [mysqld]
     basedir = /usr/local/mysql
     datadir = /usr/local/mysql/data
     socket = /usr/local/mysql/mysql.sock
     character-set-server=utf8
     port = 3306
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  [client]
    socket = /usr/local/mysql/mysql.sock
    default-character-set=utf8
 #[mysqld]
 #datadir=/var/lib/mysql
 #socket=/var/lib/mysql/mysql.sock
 # Disabling symbolic-links is recommended to prevent assorted security risks
 #symbolic-links=0
 # Settings user and group are ignored when systemd is used.
 # If you need to run mysqld under a different user or group,
 # customize your systemd unit file for mariadb according to the
 # instructions in http://fedoraproject.org/wiki/Systemd
   
 #[mysqld_safe]
 #log-error=/var/log/mariadb/mariadb.log
 #pid-file=/var/run/mariadb/mariadb.pid
   
 #
 # include all files from the config directory
 #
 #!includedir /etc/my.cnf.d

配置mysql可通过service命令启动

使用cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld命令

赋予权限，使用chmod +x /etc/rc.d/init.d/mysqld命令；

使用chkconfig --add mysqld创建mysql服务。

检查mysql服务是否生效，使用chkconfig --list mysqld命令。

配置全局环境变量

编辑/etc/profile文件，使用vim /etc/profile命令，在profile文件中添加如下两行配置，使用:wq命令保存后退出。

配置mysql数据库

先通过命令service myysql start启动mysql服务

登录MySQL客户端

在最开始初始化的时候,会给一个mysql的初始密码,记住

使用mysql -uroot -p

然后输入初始密码登录进入MySQL

修改MySQL密码

使用alter命令,修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

设置数据库允许远程登录

1）切换数据库，使用use mysql;命令。

2）修改mysql库中host值，使用update user set host='%' where user='root' limit 1;命令。

3）刷新mysql权限，使用flush privileges;命令。

MySQL数据备份和迁移

备份和恢复的区别,就是恢复还要保证服务重新正常运行

参考1:www.cnblogs.com/gered/p/104…

参考2:blog.csdn.net/dwjriver/ar…

备份知识

备份方法备份速度恢复速度便捷性功能一般用于
cp快快一般、灵活性低很弱少量数据备份
mysqldump慢慢一般、可无视存储引擎的差异一般中小型数据量的备份
lvm2快照快快一般、支持几乎热备、速度快一般中小型数据量的备份
xtrabackup较快较快实现innodb热备、对存储引擎有要求强大较大规模的备份

在了解这四种备份方式之前,我们先了解备份的几种情况

备份类型

完全备份和部分备份

部分备份又分为增量备份和差异备份

  • 增量备份指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦
  • 差异备份指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单

备份方式

备份温度(程度)

  • 热备份 热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响
  • 温备份 温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
  • 冷备份 冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

mysql中的不同的存储引擎对于备份方式还有不同的支持

  • MyISAM 热备 × 温备 √ 冷备 √
  • InnoDB 热备 √ 温备 √ 冷备 √

简单来讲就是InnoDB厉害,热备份都支持

物理与逻辑备份

  • 物理备份 物理备份一般就是通过**tar** , **cp**等命令直接打包复制数据库的数据文件达到备份的效果
  • 逻辑备份 逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

备份什么东西

需要备份什么?

  1. 数据
  2. 二进制日志,InnoDB事务日志
  3. 代码(存储过程,存储函数,触发器,事件调度器)
  4. 服务器配置文件

备份工具

mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备

cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份

lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份

mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎

xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

备份策略

  • 直接cp,tar复制数据库文件 简单粗暴
  • mysqldump+复制BIN LOGS 中等数据量,简单.先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
  • lvm2快照+复制BIN LOGS 一般数据量,不应过分影响服务,使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
  • xtrabackup 数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

Bin logs

顾名思义就是binary log的意思,使用二进制形式保存日志,这个日志记录了对于数据库的所有更改操作

好处就是内存占用小,读取速度快,不过需要配置开启

二进制日志需要在mysql配置文件中添加 log_bin=on 开启

备份详细步骤

cp命令直接复制数据库文件

因为mysql实际上的数据也是保存在文件中的,所以我们可以直接找到数据存储的位置进行复制,但是这种操作必须要复制全部,而且复制的时候要LOCK TABLE也就是阻止继续修改表

具体的操作步骤是

  1. 找到数据库数据文件位置,可以通过查看配置文件的方式,在linux中,在/etc/rc.d/init.d文件夹中,找到mysql服务的配置文件,找到datadir属性的值,大概率就是/usr/local/mysql/data文件夹 或者在my.cnf文件中找

  2. 进入数据管理系统,刷新缓存然后加锁

     mysql> FLUSH TABLES WITH READ LOCK;    #向所有表施加读锁
 # flash tables with read lock;

  3. 使用cp命令工具复制文件

     cp -a /usr/local/mysql/data/* /usr/local/mysql/saves

  4. 恢复数据

将这个备份文件夹中的内容拷贝到mysql数据文件夹中即可,记得要删干净再复制进去防止冲突

MySQLdump脚本命令进行备份

这个命令式mysql提供的备份工具,可以将数据库中的数据备份成一个文本文件,实际上就是数据库逻辑文件,里面就是一些insert,create语句.

缺点就是如果数据库中的数据或者表结构很多很复杂的话,备份过程和恢复过程都会很慢,备份文件也会很大

 mysqldump -u $用户名$ -h $主机$ -p 
 $数据库名字$ 
 $表名$,$表名....
 >
 $文件名.sql$
 -------------------
 请输入密码: 

 #例如
 mysqldump -u root -h localhost -p
 mybatabse
 user, information
 > 我的备份1.sql
 -------------------
 请输入密码:  

如果需要备份多个数据库,需要使用--databases选项,以及使用空格分割数据库名

 mysqldump -uroot -p
 --databases
 test mysql
 > somefile.sql

如果想要备份所有的数据库,就直接使用--all-databases选项即可

 mysqldump -uroot -p
 --all-databases
 > save-file.sql

使用lvm2-snapshot进行备份

实际上并不懂

使用Xtrabackup备份

先下载这个工具,通过wget可以加载

需要注意,对于mysql-8以上我们需要安装xtrabackup-8以上的版本才行,另外可能需要安装 libgcrypt.so.11之类的依赖

可以到rpmfind.net/linux/rpm2h…这里下载之后安装到服务器中

xtrabackup也可以在广网下载-后传入服务器进行安装.

注意!!!

下载的xtrabackup版本很重要,因为这个工具对于mysql的版本很敏感,在下载的时候要确认是否支持

 # 下面这个命令可以从网上下载安装包到本地目录
 wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   
 # 然后直接用yum安装这个安装包
 yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

新手直接搞

xtrabackup --backup -u 用户名 -p --target-dir=保存路径

Mysql学习地址

最推荐地址: 易百教程 www.yiibai.com/mysql

实战经验: pdai.tech pdai.tech/md/db/sql-m…

SQL基础

show语法

我们可以show很多东西,例如数据库有哪些,表有哪些,表的结构是怎样的,配置信息之类的

显示表的创建信息

show create table $table_name$;

常用语法

常用的create语法

create table `users`
(
	id int not null auto_increment comment 'primary key auto_increment',
	name char(8) not null default 'null',
	age tinyint not null default '0',
	password varchar(16) not null default '123456',
	active boolean not null default false;
	primary key(`id`)
)Engine=innoDB auto_increment=1 deafult charset=utf8mb3;
create database if not exists mysql;
create index users_main_index
on users(id,name,password,age);
//添加前缀索引
alter table uses add key(password(8));# 这个是前缀索引text,blob这些长文本
create view users_help
as select id,password from users where active = true;

create database $你的数据库名$;
# 创建数据库
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL DEFAULT 'null',
  `password` char(8) NOT NULL DEFAULT '123456',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;

# 创建table全语法
create table 
$你的表$(
$列名$ $类型$ $约束$,
    ...
    ,
    $表级约束...$
);
# 创建表
create index 
$你的索引名字$ 
on 
$索引所在的表名字$ ($col_name1$, $col_name2$,...);
# 创建索引

例子

# create database;
create database mydatabase;
# creat table
create table user(
id int primary key,
    age int not null default "0"
);
# create index
create index 
user_index_1
on
user(name,age);
# creat view
create view
user_view
as
select name from user where age > 10;

常用的Alter语句

alert table users
add removed_status tinyint not null default false;
alert table users drop removed_status;
alert table users add primary key (id);
alert table users modify id bigint;

# 改动表结构
alert table $你的表$
$change-name$
	如果是 change 列名 改成列名 新的类型 新的列级约束 一些东西...
	如果是 add 列名 类型 列级约束 一些东西...
	如果是 drop column 列名...
	如果是 modify 列名 列基本信息

例子

# alter table
alert table 
user
change 
gender sex
varchar(8)
not null
default '男';
# alter drop
alert table
user
drop
sex;
#alert add
alert table
user
add 
comment  text default '空';
# alert modify
alert table users modify id bigint auto_increment;

常用的insert语法

insert into users values(0或者null或者default或者直接不写,'用户1',11,null,null,...),(0,'用户2'...);# 我们插入的数据行必须和表的结构相同
insert into users(name,password) values('用户1',"123"),('用户2','234')....;
insert into users  select * from ol-uses;

# 普通的插入
insert into $你的表$(列名1,列名2,...)
values(data1,data2,...),(data1,data2,...),...;
# 插入查询结果
insert into $你的表$(列名1,列名2)
select col1,col2 from $表名$;
# 保存为一个新的表
create table $你的表名$
as 
select * from $表名$;
-- 有点像是create view 的语法
# 覆盖插入(强行插入)
-- 如果你的插入数据中数据违反了primary key约束,此时mysql会拒绝执行,但是你想要更新数据让mysql执行下去
-- 此时在INSERT语句中指定ON DUPLICATE KEY UPDATE选项(on duplicate key update)，MySQL将插入新行或使用新值更新原行记录。
INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
ON DUPLICATE KEY 
UPDATE 
   task_id = task_id + 1, 
   subject = 'Test ON DUPLICATE KEY UPDATE';
-- 后面的语句相当于是update语句,意思就是当发现重复(duplicate)的时候就执行更新

例子

# insert values
insert into 
user(name,gender)
values
('例子1','男'),
('例子2','女');
# or
insert into
user
values(
    'name1','女'
),('name2','男');
# insert from select
insert into
user(name,gender)
select name,gender from user2 where name like '王%';

常用的update/delete语法

update ignore users set password='123456', active = true where name in select name from lost-password-users;

# 更新某些数据,修饰符详细看 https://www.yiibai.com/mysql/update-data.html
update[修饰符] 
$表名$ set 列名 = $新的数据$,列名 =... where 条件表达式;
# 删除一些数据
delete from $表名$ where 条件表达式;
#(通常使用delete之前,先用相等的条件select一下,检查表达式是否有问题)
# 删除所有数据(清空)
TRUNCATE TABLE $表名$;
# 实际上如果使用delete不指定where条件那么也是删除所有数据的意思
delete from $表名$;

例子

# update 
update
user
set
name = '空'
where
age < 1;
# delete
delete from user where name='空';

常用的select语法

基础结构

select 
$输出表结构$
from 
$数据来源$
where 
$条件表达式$
$输出数据表的操作$

distinct输出结构

# 确保我们输出的结构数据不会有重复值例如 1,2 出现,再发现1,2就不会输出
select
distinct $输出结构$
from
...

limit--输出数据表操作

这个操作是在最后执行的,对输出数据表进行操作

select
$输出结构$
from
$数据来源$
where
$条件表达式$
limit $行数$,$每行多少个$;

order by--输出数据表操作

select
...
where
$条件表达式$
order by  
$列名$ $升序asc降序desc$,
$列名2$ $升序asc....

group --表达式操作

这个操作属于条件表达式的一部分,必须写在where条件表达式后面,在order by前边

select
*
from
user_table
where
$条件表达式$
group by $字段名$
having $分组依据$;

这里的分组依据如果不填写,那么就按照相同的值一组;

#一个例子
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;
# 另一个例子
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

简单条件表达式

# 操作符
=	#等于
<	#小于
>	#大于
<> !=	#不等于
<= !>	#小于等于
>= !<	#大于等于
BETWEEN	#在两个值之间
IS NULL	#为 NULL 值

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

select 
*
from 
table_name
where
age in (10,11,12);

NOT 操作符用于否定一个条件。

通配符条件表达式

用于字符串的查询

  • % 匹配 >=0 个任意字符；
  • _ 匹配 ==1 个任意字符；
  • [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

这个查询效率不是很高,所以不要滥用!!!

select 
*
from
user_table
where
name like '[^AB]%'

子查询条件表达式

子查询条件表达式只能返回一个字段的值,也就是最多是一列,而不是一个表,给父查询使用

select 
*
from
user_table
where 
age in
(select age where gender='男');

子查询输出结构

子查询也可以写在输出结构上

子查询只能返回一个字段的值,也就是最多是一列,而不是一个表,给父查询使用

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

注意我们引用父查询中的字段,对于子查询来说就相当于是一个常量,不会变化,而子查询返回的数据是随着父查询每查询一行而变化的.

输出结构

我们在输出结构上可以通过,分隔不同的字段

# 常用格式
select
column1,column2,...
from
user_table;
# 计算字段结构
select
column1*column2 , column2*column3
from 
user_table;
# 使用as 改变字段名
select
column1*column2 as col_1, column2*column3 as col_2
from
user_table;
-- 这样的话在输出结果表里面列名就变成as指定的名字,否则是运算表达式作为列名

函数运用在输出结构上

不同的DBMS拥有的函数不一样,所以这里不再过多复习

常用函数
  • 日期格式: YYYY-MM-DD
  • 时间格式: HH:MM:SS
函 数说 明
AddDate()增加一个日期(天、周等)
AddTime()增加一个时间(时、分等)
CurDate()返回当前日期
CurTime()返回当前时间
Date()返回日期时间的日期部分
DateDiff()计算两个日期之差
Date_Add()高度灵活的日期运算函数
Date_Format()返回一个格式化的日期或时间串
Day()返回一个日期的天数部分
DayOfWeek()对于一个日期，返回对应的星期几
Hour()返回一个时间的小时部分
Minute()返回一个时间的分钟部分
Month()返回一个日期的月份部分
Now()返回当前日期和时间
Second()返回一个时间的秒部分
Time()返回一个日期时间的时间部分
Year()返回一个日期的年份部分
select now();
# 可以得到当前的时间

数据来源

数据来源可以是一个表

也可以是临时的表,这个临时的表是通过现有的表进行操作动态表示的

例如

 table_A inner join table_B on table_A.id = table_B.id

中级

数据来源

连接操作,来构成一个动态生成的临时表

内连接inner join

也叫做等值连接,是最常用的连接

SELECT A.value, B.value
FROM table_A AS A INNER JOIN table_B AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

如果没有指明等值条件,那么会返回笛卡尔积表,也就是全排列组合的表,非常耗费算力

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

实际上就是等值连接的一种特例

外连接

外连接保留了一些没有匹配的项目,就像是补充数据一样,只允许数据加入,不允许数据筛除

左外连接保留左表没有匹配的行

右外连接保留右表中的行

全外连接保留两张表中没匹配的行

只不过这样形成的表模式会很大(列会很多)

组合查询

我们可以将两个select语句得到的结果表联合在一起成为一张结果表

每个查询必须包含相同的列、表达式和聚集函数。 (结果表必须要相同的模式)

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

# 一个例子
SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2
order by col desc;

视图查询

视图的作用来自于我们进行多表查询的时候往往形成的临时表,子查询等等都比较多,复杂.

视图就是一个临时表的动态描述,当我们调用视图的时候相当于就开始运行视图所代表的子查询构成临时表

所以说视图是一种虚拟表,就相当于一句话,例如A表和B表自然连接形成的表,但是这张表只是描述没有形成实际的数据

# 使用视图就像是一张普通的表一样
# 创建视图的语法
create view
myview 
AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

我们可以给我们的视图一些表的权限,例如只读权限,这样用户就只能读取这些视图的内容了

注意:视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

存储过程

实际上就是封装了sql语句的一些函数,因为存储过程是预编译的所以效率很高,而且是封装,所以外边看不到过程,安全性也很高.代码复用程度也高.

存储过程的语法

首先需要知道最外层框架

create procedure 
$名字$( 参数 名字 类型)
...sql 语句 块 ...

先解释一下这个参数,存储过程会定义三种参数

  1. in 传入参数
  2. out 输出参数
  3. iout 输出输入参数一体

例如

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;

可以看到,首先定义了一个分隔符,这个分隔符是用来表示结束的意思

因为我们的sql语句块中的sql语句也会用到;号,如果不加以区分会让误以为是sql语句块的结束

所以我们先定义一个结束符号

delimiter //

然后使用begin ... end 表示语句块 然后再使用 // 表示结束(这个是因为我们前边定义//未结束符所以写这个)

使用declare 声明变量

#声明变量的具体语法
declare $名字$ $类型$;

使用 into 表示赋值

# 使用into 赋值的一些例子
declare muilti_age int default 值;
select age*age from user where gender='男' into muilti_age;
select muilti_age;

调用存储过程

通过关键字call 进行调用

call $procedure名字$(@变量名)
select @变量名
-- 或者使用execute
execute $存储过程名$(参数值,参数值,...);

游标 在结果表中移动

正常情况下我们就只能一行一行的向下读取结果表,有些情况下我们可能需要向上查询上一行的数据,或者跳到最后一行?

这个情况下我们就可以创建游标,告诉数据库管理系统,给我花点空间创建游标(在没有游标的情况下数据库消耗最小)

声明游标

# 以下是声明游标的语法
declare $游标名字$ 
cursor for
$select语句(也就是结果集)$

在声明游标之后,我们就可以使用游标了

使用游标第一步,打开游标,这样就会为游标开启内存,进行计算获取数据

open $游标名$;

使用完毕之后要关闭游标,这样就可以释放游标消耗的内存,下次再要使用直接open即可

close $游标名＄;
-- 如果不明确的关闭游标，mysql 将会在到达end 语句的时候自动关闭它。

使用游标

可以使用 fetch 语句分别访问它的每一行。

fetch $游标名$ into $变量1$,$变量2$,...;
-- 这样就可以自动获取游标所在的行的值,into 给变量;并且自动进入下一行;

fetch会自动将游标向下移

    #声明游标，把查询到的工资结果集从高到低排序
    DECLARE emp_cur CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    
    OPEN emp_cur;   #打开游标
    
    #用循环语句遍历，知道总薪资满足题目要求
    REPEAT
        #开始使用游标
        FETCH emp_cur INTO one_sal;
        
        SET sum_sal = sum_sal + one_sal;
        SET count_emp = count_emp + 1;
        
        UNTIL sum_sal >= limit_total_salary
        
    END REPEAT;
    CLOSE emp_cur;  #关闭游标

repeat和util注意使用即可

repeat
fetch 游标 into 变量;
UNTIL 条件表达式;
end repeat

触发器

会检测到某些条件自动执行某些语句

以下几种操作可以实现设置触发器

  1. delete
  2. insert
  3. update

都是对于数据的操作

声明触发器

create trigger
$触发器名字$
[after/before]
$监听操作1$,$监听操作2$
on
$表名$
FOR EACH ROW
$触发之后执行sql语句$;

在MySQL中，有两个内置的特殊表：NEW和OLD，它们都只在触发器中使用。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

高级

事务管理

mysql的事务隐式开启,没执行一条sql都会当做一个事务执行提交执行.

当出现 START TRANSACTION 语句时，会关闭隐式提交；

当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

这个时候也就是说我们start transaction之后;

我们发送的sql语句都会被保存起来,不会立马执行;

等到我们commit或者rollback才会一起执行;

通过设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。所以我们不同的连接设置这个不会相互影响.

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点

# 设置保存点
start transaction
// ...
savepoint delete1
// ...
ROLLBACK TO delete1
// ...
commit

权限管理

mysql的账户号信息,权限信息都保存在mysql这个数据库中

创建用户

create user
$用户名$
identified by
$你的密码$;

修改用户名

rename $用户名$ to $新的用户名$;

删除用户

drop user $用户名$;

查看用户权限

show grants for $用户名$;

授予权限

#基本结构
grant 
$语句权限(insert,select等等)$ 
on
$数据库$.$表名(可以使用通配符)$
to
$用户名$

-- 这里介绍一下用户名的格式,是 username@login_ip的格式

删除权限

GRANT 和 REVOKE 可在几个层次上控制访问权限:

  • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
  • 整个数据库，使用 ON database.*；
  • 特定的表，使用 ON database.table；
  • 特定的列；
  • 特定的存储过程。
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

revoke from

sql语句优化

主要的问题就是知道一些操作对于sql语句的影响

1 反向筛选无法使用索引

如果查询语句中的where条件是not怎么怎么样,那么这条查询语句无法使用到表中的索引,会导致查询效率大大降低

2 模糊查询的坑

如果模糊查询的通配符在开头,那么会导致大量的匹配结果,同样也无法使用索引,所以查询效率极低

建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询

3 数据区分不明显的不建议创建索引

索引要高效,一定要数据范围很大,区分很广的那种,这样我们一个判断就可以排除很多数据

4 字段的默认值不要为 null

学过数据预处理都知道,缺失值很恼火,默认值不要设置为null,我们需要而外的操作去处理这些null值

5 在字段上进行计算不能命中索引

我们最好在输出结构中,直接将索引字段作为输出,而不是对索引字段进行运算

select name from user where FROM_UNIXTIME(create_time) < CURDATE();
应该修改为:
select name from user where create_time < FROM_UNIXTIME(CURDATE());

6 最左前缀问题

我们使用的复合索引的时候,我们如果不带上左前索引字段,那么你直接用后方的字段是不管用的!

例如我们有索引username,pwd

# 下面的是ok的
select username from user where username='zhangsan' and pwd ='axsedf1sd'

select username from user where pwd ='axsedf1sd' and username='zhangsan'

select username from user where username='zhangsan'

# 直接使用后方的索引是不行的
select username from user where pwd ='axsedf1sd'

7 如果明知道只有一条数据结果

可以直接使用limit来终止查找

# 加上limit
select name from user where username='zhangsan' limit 1;

8 不要让数据库帮我们做强制类型转换

如果类型不对,数据库是不知道的,他会先按照你的类型查找索引,发现索引类型不一样就会进行全表扫描

这样才知道该转换成什么类型

select name from user where telno=18722222222
#这样虽然可以查出数据，但是会导致全表扫描。需要修改为
select name from user where telno='18722222222'

9如果需要进行 join 的字段两表的字段类型要相同

不然也不会命中索引。

mysql知识体系

mysql的字段数据类型

基本的类型

整型

使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

8 tinyint 1个字节 # mysql没有布尔类型,使用tinyint进行表示 可以使用boolean 或者bool作为tinyint的表名,0表示false,非零值表示true

16 smallint 2个字节

24 mediumint 3个字节

32 int 4个字节

64 bigint 8个字节

可以看到,mysql设置了很多小的字节选择

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义

浮点数

float,double,以及decimal

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

字符串

主要有char和varchar两种

char

char类型是定长的,我们可以通过设置char(8)设置使用多少个字符存储这个字符串(不会使用空格补齐内存,会删除末尾的空格!!!!)

varchar

而varchar可以根据值的变化,来动态分配中存储空间,比如我们想要将男,改为沃尔玛购物袋,如果是之前使用char(2)就不能正常输入了.

不会自动用空格补齐，而是采用额外的 1 ~ 2 个字节记录实际长度；当实际长度小于等于 255 时，额外占用 1 个字节去存储实际长度，大于 255 时，则额外使用 2 个字节

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内.

所以如果是太长的字符串,我们推荐使用后面提供的类型.text或者blob

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

日期和时间

一共有两种方式保存时间

DateTime

这种就是java中的本地时间

相当于是timestamp加上zone

包含全部的信息

使用的是8字节的存储空间

能够保存从 1001 年到 9999 年的日期和时间，精度为秒

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法

TimeStamp

也就是时间戳,只包含了绝对时间,没有包含时区信息

使用 4 个字节就像是int一样，只能表示从 1970 年 到 2038 年。

MySQL 提供了 from_unixtime() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高

优化方向

  1. 内存占用用更小的,更好,因为是更小通常更快,占用的内存,磁盘空间少,
  2. 简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；
  3. 尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列

字符串类型优化

关于char和varchar的选择

varchar的作用

下面这些情况使用VARCHAR是合适的：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

这样即使不使用varchar也会难以对齐,而使用了varchar之后还解决了局部题没有增加总体存储空间

当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

char的好处

CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片？）

关于varchar(5)和varchar(200)的区别

在varchar括号里面的数字指的是最大字符串的长度

mysql在存储的时候会额外花费空间存储字符串的时间长度

mysql在保存内存值的时候通常都是按照最大值空间进行保存的

所以在使用临时表进行排序等操作就会非常占用内存空间,利用磁盘临时表也表现很糟糕

所以最好还是用实际需要的大小

BLOB和text类型

BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储。

MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引

表示符的优化

一般就是我们的主键

整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT。 如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。

对于完全随机的字符串也要注意,这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢.

也会导致局部性原理失效,所有的select ,insert 语句都会变得很慢(缓存失效)

存储引擎

mysql主要有两种存储引擎,InnoDB和MyISAM

InnoDB

InnoDB默认使用的事务型存储引擎,只有当需要他不具有的特性的时候才选择使用其他存储引擎

特点

实现了四个标准的隔离级别,默认情况下是可重复度级别

四个事务隔离级别

  1. 读未提交

事务中的修改操作立即生效,而不用等到事务提交;事务内部可以读取其他事物未提交的数据.

可以读取其他事务未提交的修改数据

适用于对数据一致性不高的场景,例如读取实时监控数据

  1. 读已提交

只有事务提交之后才会执行修改操作;事物内部只能读取到其他事务已经提交了的数据.

适用于大部分的场景,保证了数据的较高一致性

  1. 可重复读

提交后才会生效修改操作;事物内部只能读取到事务开始前的快照数据,事务执行期间,其他事务都不知道这个事务修改了数据.

使用场景:

适用于需要保证读取数据的一致性的应用.

  1. 串行化

将并发的事务变成串行执行,这样可以保证绝对不会出现问题,但是性能低下.

在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取.

MyISAM

这个引擎不支持事务操作

并发时,只能锁定整张表,不能锁定某一行(这表示很大大降低并发性能)

读写相互堵塞,也就是不能同时读写

只会缓存索引,不会缓存数据,这表示无论如何都还是要到磁盘中读取数据,只不过如果缓存了索引,可能查找效率很快

读取速度较快,占用资源较少,因为设计的很简单

不支持外键约束

支持全文索引

二、MyISAM引擎适用场景

由MyISAM的特性，我们就可以简单的列举MyISAM引擎适用场景了。 1、不需要事务支持的场景。 2、读取操作比较多，写入和修改操作比较少的场景。 3、数据并发量较低的场景。 4、硬件条件比较差的场景。 5、在配置数据库读写分离场景下，MySQL从库可以使用MyISAM索引

索引(B+树)

先了解一下B 树也就是blance树平衡树.

B树对于任何子节点的查找时间基本上都相同,所以非常适合用来作为索引结构

???难以理解???

与红黑树的比较

  1. B+树所需要的查询次数更少

因为B+树出度一般都很大,而红黑树的出度为2,所以树高红黑树更高,所以查询次数就更多

  1. B+树利用了计算机的预读特性

为了减少磁盘I/O,磁盘一般都不是严格读取,而是会进行预读(也就是读取一部分后面的内容到缓存中使得这次I/O操作不是那么亏)

B+树叶子结点的数据是顺序访问指针,所以访问会很快

MySql索引

不同的存储引擎有不同

的索引类型和实现

B+树索引

大多数mysql引擎的默认索引,用于查找，还可以用于排序和分组

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。 (就是之前sql优化中的左前缀问题)

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引.

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

哈希索引

哈希索引能够以O(1)的时间进行查找,但是失去了有序性

无法用于排序和分组

只能用于精确查找,无法用于部分和范围查找

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

InnoDB在5.6.4之后支持全文索引,MyISAM支持全文索引

就是查找字符串中的关键词,查找条件使用match aginst而不是普通的where

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

空间数据索引

MyISAM支持,需要GIS相关函数进行维护

索引的优化

  1. 索引列必须是独立的,而不能是表达式的一部分,也不能是函数参数
  2. 使用多个列作为条件查询的时候最好设置多列索引
  3. 索引顺序也很重要,选择性最强的(指的是不重复的索引值和记录总数的比)索引放在前边,这样查询效率高
  4. 前缀索引 alter table 表名 add key(列名(字符长度))主要用于text,varchar,blob等长字符串的索引,只索引一部分
  5. 覆盖索引 指的就是索引就包含了所有数据,不需要回表获取数据

mysql性能优化

要进行mysql性能优化,首先要发现sql问题

使用explain语句进行分析

explain语句可以分析select查询语句的执行,所以可以根据explain来优化select语句

其中比较关键的explain返回的字段有

  1. select_type 查询类型,简单查询,联合查询,子查询等
  2. key 使用的索引
  3. rows 扫描的行数

思路1 优化数据访问

减少需要扫描的数据量

只返回必要的列,最好不要使用select * 语句

只返回必要的行,使用limit限制需要得到的数据量,这样mysql会提前返回

缓存需要重复查询的数据

使用索引来覆盖查询,这样减少会表操作

思路2 重构大查询方式

1 .可以切分大查询成为小查询

有些查询需要操作很大的数据量,而且是一次性执行,这样会阻塞小的查询

我们可以将之分解成几次操作

# 例如
delete from message where create_time < date_sub(now()-INTERVAL 3 MONTH);
# 我们分解成操作10000条数据
rows_affected=0
do{
 rows_affected = do_query(
 "delete from messages where create_time < date_sub(now() - INTERVAL 3 MONTH) limit 10000;")
}while rows_affected > 10000

2 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有:

缓存不容易失效,因为在大连接查询中,如果有一个表的数据变化,那么缓存查询就不可用了,这样整个大连接查询就变得难受.而分解后,即使一个查询缓存无法使用,其他的表也可以正常使用查询缓存

可以形成有效的缓存记录,这样的记录更可能被其他查询用到,而大连接查询的缓存多是无效缓存(别的查询根本不可能用到)

减少锁竞争

在应用层进行连接,可以更加灵活,可伸缩

查询本身效率也可能会有所提升

建立索引的原则

需要知道一些常识

索引会从左到右,依次匹配索引,直到遇到范围查询(>,<,between,like等),而=,和in可以打乱顺序,mysql会自动识别成索引实际的顺序

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

mysql主从复制

也就是从库复制了主库中的数据,分担主库的压力,另外在主库掉线从库也可以顶上

为了保证数据一致性,只允许主库进行写入操作,叫做读写分离

基于二进制日志文件,在主从库之间传递增量

底层实现

image-20240330155045786

也就是每个库,都有自己的日志,日志作为最高优先级,日志先变化再改变数据

根据传递的日志,改变自身的日志

开启步骤

第一步 服务器之间的联通

我们先把服务器之间的网络搞定,mysql的端口防火墙打开

第二步 主数据库配置

主库需要向从库发送新的数据,所以需要给主库配置从库信息

修改/etc/my.cnf也就是mysql的配置文件

  1. 配置数据库id

server-id= 唯一id

  1. 配置读写功能,需要我们手动关闭slave服务器的写功能

read-only=0 # 1表示开启只读模式,0表示关闭

  1. 可选设置,比如同步的数据库是?不要同步的数据库是?
binlog-ignore-db=不需要同步的数据库
binlog-do-db=需要同步的数据库

重启mysql服务service mysqld restart

mysql账号配置

用于从库连接主库的账号,所以我们在主库中配置用户

create user 'use_to_cp'@'%' IDdentified by '密码';
# 赋予主从复制所有数据权限
grant replication slave on *.* to 'use_to_cp'@'%';

查看增量日志进度

redis是是通过环形缓冲区来做的主从复制,mysql也需要

通过

show master status;

可以查看主库中的binlog的进度序号,从库因此来知道与主库之间的数据代差,从而索要对应的增量数据库

总结

可以发现,主数据库几乎不需要做什么配置,只需要给一个权限和配置id就行了

第三步 从库配置

修改配置文件

也是/etc/my.cnf文件

  1. server-id=唯一的id
  2. read-only=1 #关闭从库的写功能,这样才能保证数据一致性 super-read-only=1禁止root的写权限

mysql配置

登录slave数据库,给数据库认一个大哥,带上之前设置的内部用户权限

change replication source to source_host='主库ip',source_user='登录主库的用户名',source_password="密码",source_log_file='这个要到master数据库中通过show master status中的文件名',source_log_pos='也是自己看'

image-20240330161517447

然后启动复制,此时slave数据库就去找master数据库要东西了

start replia;# 或者start slave

查看从服务器的日志

show replia status;

image-20240330161940688

主要查看这两个数据,io负责读取二进制日志,sql负责反映到自身

mysql分表分库

当我们的数据量特别大的时候,如果这些数据集中在一个数据库,甚至一张表里面,那么对于索引的建立,维护,查询都会产生很大的压力和问题

我们需要将数据分解成不同的表和库,这样可以减少我们搜索和存储的成本

分表分库有几种角度

一种是水平拆分与垂直拆分角度,实际上水平拆分保留了原始结构,而垂直拆分改变了结构

一种是分库还是分表角度,实际上分库还是分表只是拆分粒度上的不同

垂直拆分

垂直分库

就是把表作为拆分单位,将同一数据库中的不同的表拆分到不同的服务器中(不同的服务器中的数据库)

垂直分表

将一张表中的字段作为单位,拆分到不同的表中(相)

水平切分

又叫做sharding,也就是将数据分摊到其他相同结构的表中,不改变结构

需要考虑的是,哪些数据需要切分到哪个表中?

一种方法是按照hash(key)进行分表,落在指定范围内的数据就由对应的表进行存储

Sharding 策略

  • 哈希取模: hash(key) % NUM_DB
  • 范围: 可以是 ID 范围也可以是时间范围
  • 映射表: 使用单独的一个数据库来存储映射关系

水平分库

将一个数据库中的数据拆分到不同的数据库中,这些数据库拥有一样的表结构,一样的表,但是数据记录不一样

水平分表

将一张表分散到其他数据库中的其他表中,表结构是一样的

基本印象

mysql并没有提供分库分表的机制,需要我们手动的实现

而实现分库分表的原理实际上和redis等分布式数据库是一样的,根据hash值进行区分

遇到的问题与技术

按照hash值来决定访问那台数据库,需要应用程序来做,所以不行,我们因该减少应用程序耦合程度

最好有一台服务器作为中转接口,这样我们的应用程序就不需要管了

两种技术:

  1. shardingJDBC利用java的AOP机制,在本地执行SQL的时候进行拦截,路由,效率很高但是需要自行实现
  2. MyCat 中间件,不需要自行编码,很简单,但是性能弱一点

MyCat

实际上是java开发的,alibaba开发的Mysql中间件(也就是不会影响数据库和应用程序任何一方)

mycat不仅仅可以连接mysql,实际上nosql以及其他数据库也可以连接

mycat基本原理

就是mycat伪装了mysql的协议,这样应用程序访问mycat就完成没有影响

mycat再去分析到底访问那个服务器数据库,拿到数据之后再还给应用程序

应用程序每次请求相当于进行了两次请求,使用AOP就不会产生请求的消耗

mycat可以支持主从复制,数据分片

MySQL 日志系统

参考1: www.cnblogs.com/roverliang/…

日志系统种类

  1. 错误日志（error）,MySQL服务实例启动、运行或者停止等相关信息。
  2. 普通查询日志（general）,MySQL服务实例运行的所有SQL语句或者MySQL命令。
  3. 二进制日志（binary）,对数据库执行的所有更新语句，不包括select 和show语句。
  4. 慢查询日志（slow），执行时间超过long_query_time 设置值的SQL语句，或者没有使用索引的SQL语句。

MySql的日志默认以文件的形式存放在数据库根目录下,通过查询配置文件中的datadir属性值可以找到这个数据目录

mysql配置文件读取顺序

  1. /etc/my.cnf 全局配置文件
  2. /etc/mysql/my.cnf mysql配置文件
  3. /usr/local/mysql/etc/my.cnf 本地配置文件
  4. ~/.my.cnf 用户配置文件

通过命令查看默认配置位置

mysql --help|grep 'cnf'

更加准确的是看mysqld中conf变量的赋值

在/etc/rc.d/init.d/mysqld中,这个是服务启动的脚本

[root@wainyz-linux-host02 ~]# mysql --help |grep cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /usr/local/mysql/my.cnf ~/.my.cnf

错误日志

错误日志很有用,当我们的mysql出现问题的时候查询错误日志可以帮助我们高效的定位错误发生的原因

MySQL的错误日志主要记录MySQL服务实例每次启动、停止的详细信息，以及MySQL实例运行过程中产生的警告或者错误信息。和其他的日志不同，MySQL的error日志必须开启，无法关闭。

查看错误日志路径

show variables like 'log_error';

mysql实例一定知道日志在哪里,不然它怎么编辑呢?我们可以向mysql查询日志目录

普通查询日志

MySQL普通查询日志记录MySQL服务实例所有的操作，如select、update、insert、delete等操作，无论该操作是否成功执行。还有MySQL客户机与MySQL服务端连接及断开的相关信息，无论连接成功还是失败。

如果开启MySQL的普通查询日志将会大幅度的降低数据库的性能，因此建议关闭普通查询日志。只有在特殊时期，如需要追踪某些特殊的查询日志，可以临时打开普通的查询日志。

可见普通日志记录的东西有点宽泛,默认是不开启的.

# 查看数据库是否开启普通日志
show variables like 'general_log';

开启和关闭

# 通过设置系统变量global.general_log来开启和关闭,都知道mysql中使用tiniy int代表boolean
set @@global.general_log = 1;

但是通过这种方式修改MySQL的变量只会在当前的MySQL实例运行期间生效，一旦MySQL重启，则会重新恢复到默认的状态。永久生效的方式是修改mysql 的my.cnf 文件。在配置文件后添加：general_log = 1

查看日志所在位置

show variables like 'general_log_file';

将日志保存在数据库中

值得注意的是我们可以将日志保存在数据库中,这样方便查询

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

MySQL慢查询日志

慢查询日志只会保存查询成功的记录

跟踪的主要是执行时间过长和没使用索引的查询语句,包括select,update,delete以及insert语句(有where的都算是查询)

查看慢查询日志是否开启

show variables like 'slow_query_log';

开启和关闭

和之前的一样

set @@global.slow_query_log = 1;

查看日志文件路径

show variables like 'slow_query_log_file';

慢查询时间阈值

show variables like 'long_query_time';

# 修改就是
set global long_query_time = 3.0;

MySql权限管理

在开启了general_log之后,发现很多陌生的ip一直在访问我的mysql服务器,黑客行为试图破解我的密码.

所以关闭其他的ip的访问是很重要的.

1 登录权限

如果只是修改登录ip的话很简单,我们可以直接通过查看和修改mysql数据库中的user表就行

 # 查看用户的登录ip
 select user,host from user where user='root';
 -- 这个例子就是查看root用户有那些登录ip
 ​
 # 修改登录ip
 update user set host="localhost" where user='root';

记得flush privileges;刷新权限

avatar
文章
阅读
粉丝