数据库（Mysql）
默认端口
### port = 3306
数据文件默认位置
/usr/local/mysql/data
数据库的引擎
### InnoDB存储引擎:默认引擎,最常用的。（支持事务）
### 查看当前默认存储引擎
show variables like '%storage_engine%';
数据库的种类
### 关系型数据库
### 非关系型数据库。
sql语句
# SQL（Structured Query Language 即结构化查询语言）
# SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。
# DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
# DML语句 数据库操纵语言（对记录的操作）: 插入数据INSERT、删除数据DELETE、更新数据UPDATE
# DCL语句 数据库控制语言（和权限有关）: 例如控制用户的访问权限GRANT
# DQL语句 数据库查询语言: 查询数据SELECT
生产环境常用数据库
### 关系型数据库:Oracle、SQL Server、MySQL/MariaDB等。
### 非关系型数据库:MongoDB、Memcached、Redis
关系型数据库
### 关系型数据库在存储数据时实际就是采用的一张二维表
### 市场占有量较大的是 MySQL 和 Oracle 数据库,而互联网场景最常用的是 MySQL 数据库。
### 通过 SQL 结构化查询语言来存取、管理关系型数据库的数据。
### 关系型数据库在保持数据安全和数据一致性方面很强,遵循 ACID 理论。
非关系型数据库
### 非关系型数据库也被称为 NoSQL 数据库,NoSQL 的本意是 “Not Only SQL”,指的是非关系型数据库
### NoSQL 格式灵活:存储数据的格式可以是key-value形式、文档形式、图片形式等等使用灵活,应用场景广泛。
### NoSQL 数据库为了灵活及高性能、高并发、速度快而产生。
### NoSQL 数据库领域,当今的最典型产品为 Redis（持久化缓存）、MongoDB、Memcached（纯内存）等。
### NoSQL 数据库没有标准的查询语言（SQL）。
### NoSQL 成本低:nosql数据库部署简单,基本都是开源软件。
程序连接数据库的方式
ODBC --------- PHP<.php>
JDBC ----------- JAVA <.jsp>
下载（yum安装5.7）
### 下载
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
### 安装mysql的yum仓库
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum -y install yum-utils
#（安装yum工具包）
### 配置yum源
vim /etc/yum.repos.d/mysql-community.repo
# （修改选择安装的版本）
# （enabled=1）为安装的版本、（enabled=0）为不安装的版本。
# （gpgkey=1）为校验、（gpgkey=0）为不校验,为防止报错建议关闭校验
### 安装数据库并设置开机启动
yum install -y mysql-community-server
systemctl start mysqld
systemctl enable mysqld
查看mysql服务器上的版本
进入数据库后 select version();
查找密码
grep password /var/log/mysqld.log
（仅用于默认生成的密码,修改密码后将无法使用）
修改密码
mysqladmin -u root -p'旧密码' password '新密码'
# 登录数据库后
SET PASSWORD='新密码';
# 登录数据库后修改其他用户密码
use mysql
SET PASSWORD FOR 用户名@'用户登陆方式'='新密码'
设置密码强度
vim /etc/my.cnf
validate_password=off
# （添加这行到最后,重启mysql后可以设置弱密码）
忘记密码/破解密码
pkill mysql
mysqld --skip-grant-tables --user=mysql &
# 免密码登录（不安全）
mysql -uroot
#登陆后
UPDATE mysql.user SET authentication_string=password('新密码') WHERE user='root' AND host='localhsot';
# 设置新密码
FLUSH PRIVILEGES;
# 刷新授权表
# 退出后
# 编辑配置文件将skip-grant-tables参数注释
# 重启mysql
登录
mysql -uroot -p'密码'
查看当前使用的用户
SELECT CURRENT_USER();
查看/创建/删除/进入（使用）库
### 查看
show databases;
### 查看当前所在库
select database();
### 创建
create database 库名;
### 删除
drop database 库名;
表数据类型
### 整型（整数）
tinyint(n)
# 范围（0--255）
smallint(n)
# 范围（-32,768--32,767）
mediumint(n)
# 范围（-8388608--8388607）
int(n)
# 范围（-2,147,483,648--2,147,483,647）
bigint(n)
# 范围（-9,223,372,036,854,775,808--9,223,372,036,854,7）
### 浮点数类型（整数+小数）
float(5,3)
#一共5位,小数占3位 ,做了限制
### 字符串类型（char可以少于规定长度,但不能大于规定长度）
char（n）
# n范围（0--255）
# 列的长度固定为创建表时声明的长度: 0 ~ 255
varchar（n）
# n范围（0--65535）
### 枚举类型
enum（'',''）
# 如:enum('A','B')
# 只能从A,B两个里面选一个
### 日期类型
year、date、time、datetime、timestamp
# 插入年份时,建议使用准确年限,防止出现年份错误
表完整性约束（desc 表名;看输出的后半部分）
### 常见约束
# PRIMARY KEY (PK) 标识该字段为该表的主键,是可以唯一的标识记录,不可以为空 UNIQUE+NOT NULL
# FOREIGN KEY (FK) 标识该字段为该表的外键,实现表与表之间的关联
# NULL 标识该字段是否允许为空,默认为NULL。
# NOT NULL 标识该字段不能为空,可以修改。
# UNIQUE KEY (UK) 标识该字段的值是唯一的,可以为空,一个表中可以有多个UNIQUE KEY
# AUTO_INCREMENT 标识该字段的值自动增长（整数类型,而且为主键）
# DEFAULT 为该字段设置默认值
# UNSIGNED 无符号,正数
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件];
)
### 主键
# 添加主键
alter table 表名 add primary key (字段名);
# （用于已存在的表添加）
create table 表名(字段1 char(20),字段2 char(150),primary key(字段1));
# （创建表同时设置主键）
# 删除主键
alter table 字段1 drop primary key;
### 自增--必须与主键组合使用
# 添加自增（新表）
create table 表名(字段1 类型 KEY AUTO_INCREMENT,字段2 类型);
# 默认开始为1,编号如果出现重复则报错,手动插入值如果大于编号则下一个编号为手动插入编号+1
#如:CREATE TABLE department3 (dept_id INT PRIMARY KEY AUTO_INCREMENT,dept_name VARCHAR(30),comment VARCHAR(50));
# 添加自增（已存在的表）
ALTER TABLE 表名 MODIFY 字段 类型 AUTO_INCREMENT;
ALTER TABLE 表名 MODIFY 字段 类型 AUTO_INCREMENT PRIMARY KEY;
# 同时添加为主键
# 删除自增
alter table 表名 change 字段1 字段1 类型 not null;
# 如: ALTER TABLE department3 CHANGE dept_id dept_id INT NOT NULL;
### 设置唯一约束 UNIQUE （字段添加唯一约束之后,该字段的值不能重复,也就是说在一列当中不能出现一样的值。）
create table 表名(字段1 类型 UNIQUE,字段2 类型);
表的创建
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
# ==在同一张表中,字段名是不能相同
# ==宽度和约束条件可选
# ==字段名和类型是必须的
如:create table t1(id int,name varchar(50),sex enum('m','f'),age int);
表的相关查看
### 查看当前所在库有哪些表
show tables;
### 查看表结构
desc 表名;
### 查看表里面所有记录
select * from 表名;
select * from 库名.表名;
### 查看表的状态
show table status like '表名'
# 横向查看
show table status like '表名'\G
# 竖向查看
修改表名
rename table 旧表名 to 新表名;
alter table 旧表名 rename 新表名;
删除表
drop table 表名;
添加/删除表字段
### 添加单个字段
alter table 表名 add 字段 类型;
### 添加多个字段
alter table 表名 add （字段 类型,字段 类型,字段 类型）;
### 把字段添加到第一个
alter table 表名 add 字段 类型 first;
### 把字段添加到***（字段）后面
alter table 表名 add 字段 类型 after ***;
### 删除字段
alter table 表名 drop 字段;
修改字段和类型
### 修改名称
alter table 表名 change 旧字段 新字段 类型;
# 将旧字段改名为新字段,change修改字段名称、类型、顺序
alter table 表名 change 旧字段 新字段 类型 after 字段;
# 将旧字段改名为新字段,并且放在字段后
### 修改字段类型、约束、顺序
alter table 表名 modify 字段 类型;
# modify不能修改字段名称
插入数据（添加记录）
# 添加一条
insert into 表名(字段1,字段2,字段3,字段4) values(字段1类型限制内容,字段2类型限制内容,字段3类型限制内容,字段4类型限制内容);
# values内插入的内容是所对应字段类型限制的范围。如“字段2”的类型是char,那么对应的values所对应则是char对应的字符串。
# 插入字符串要""引号
# 例:insert into t3(id,name,sex,age) values(1,"tom","m",18);
# 注:添加的记录与表头要对应,
# 添加多条记录
insert into 表名(字段1,字段2,字段3,字段4) values(字段1类型限制内容,字段2类型限制内容,字段3类型限制内容,字段4类型限制内容),(字段1类型限制内容,字段2类型限制内容,字段3类型限制内容,字段4类型限制内容);
# 例:insert into t3(id,name,sex,age) values(2,"jack","m",19),(3,"xiaoli","f",20);
# 用set添加记录
insert into 表名 set 字段1=字段1类型限制内容,字段2=字段2类型限制内容,字段3=字段3类型限制内容;
# 例:insert into t3 set id=4,name="zhangsan",sex="m",age=21;
# 更新记录
update 表名 set 修改的字段 where 给谁修改;
# 例:update t3 set id=6 where name="xiaoli";
删除表记录
# 删除单条记录
delete from 表名 where 字段1=字段1类型限制内容;
# 例:delete from t3 where id=6;
# 删除那个记录,等于会删除那个整条记录
# 删除所有记录
delete from 表名;
单表查询
select 字段名称,字段名称2 from 表名 条件;
# 条件如果不需要可以不加
select * from 表名;
# 查询这个表里面所有内容,“*”代表所有。
# 多字段查询举例
select 字段名称,字段名称2 from 表名;
# 例:select id,name,sex from employee5;
# 有条件查询举例:where
select 字段名称,字段名称2 from 表名 whrer 条件;
# 例:select id,name from employee5 where id<=3;
# 统计记录数量:count()
select count() from 表名;
# 例:select count(*) from employee5;
# 统计employee5内记录数量
# select count(id) from employee5;
# 统计employee5内id字段记录的数量
# 避免重复DISTINCT:表里面的数据有相同的
select distinct 字段 from 表名;
# 例:select distinct post from employee5;
# 多条件查询:and （和,并且）
select 字段,字段2 from 表名 where 条件 and 条件;
# 多条件查询:or （或者）
关键字 BETWEEN AND 什么和什么之间。 条件 or 条件;
# 关键字 BETWEEN AND 什么和什么之间。
select 字段,字段2 from 表名 where 字段1 BETWEEN 值 AND 值;
# 例:SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
# 查找employee5表内salary字段内5000~15000之间的记录
# 取反:not
select 字段名称,字段名称2 from 表名 whrer not 条件;
# 例:select name,dep_id,salary from employee5 where not salary>5000;
# 查询employee5表内salary不大于5000的记录;
# 关键字is null (空的)
SELECT 字段1,字段2 FROM 表名 WHERE 字段1 IS NULL;
# 查找表内字段1是空的数据
SELECT 字段1,字段2 FROM 表名 WHERE 字段1 IS NOT NULL;
# 查找表内字段1不为空的记录
# 排序查询 order by（默认从小到大）
select 字段1,字段2 from 表名 order by 字段1;
# 将表内字段1按照从小到大排序的方式查看
select 字段1,字段2 from 表名 order by 字段1 desc;
# 将表内字段1按照从大到小排序的方式查看
# limit 限制
select 字段 from 表名 limit 值;
# 例:select * from employee5 limit 5;
# 查看employee5表前5行内所有数据
# *代表所有
select 字段1.字段2 from 表名 order by 字段1 desc limit 0,1;
# 将表从大到小排序将字段1从第1行开始,打印1行
# 注意:
# 末尾0,1:从第几行开始,打印（显示）几行
# 0-------默认第一行
# 1------第二行 依次类推...
# 不加desc则为默认升序
# 分组查询 group by
select count(字段1),字段2 from 表名 group by 字段2;
# 统计表内字段1的数量并分组查询字段2
# count可以计算字段里面有多少条记录,如果分组会分组做计算
# 函数
max() 最大值
# 例:select max(salary) from employee5;
min()最小值
# 例:select min(salary) from employee5;
avg()平均值
# 例:select avg(salary) from employee5;
sum() 计算和
# 例:select sum(salary) from employee5 where post='sale';
now() 现在的时间
# 在数据库直接使用
复制表
# 复制表结构+记录
create table 新表名 select * from 旧表名;
# 复制单个字段和记录
create table 新表名(select 字段1,字段2 from 旧表名);
登录和退出MySQL
# 本地登录客户端
mysql -u用户名 -p用户密码
# 远程登录
mysql -u用户名 -p密码 -h ip地址 -P 端口号（如果没有改端口号就不用-P指定端口）
# 查看都有哪些用户
SELECT user, host FROM mysql.user;
# 修改端口（rpm安装）
vim /etc/my.cnf
# 在最后加入
port=指定端口
### 使用参数
-h 指定主机名 【默认为localhost】
-大P MySQL服务器端口 【默认3306】
-u 指定用户名 【默认root】
-p 指定登录密码 【默认为空密码】
-e 接SQL语句,可以写多条拿;隔开
-D 远程登陆时使用指定登录数据库
# 例:mysql -h192.168.246.123 -P 3306 -uroot -pqf123 -D mysql -e 'select * from user;'
创建用户 create user （完成后需要更新授权表 FLUSH PRIVILEGES; ）
create user 用户名@'登陆方式' identified by '密码';
# 登陆包括localhost（只允许本地用户登录）,%（允许所有主机远程）也可以指定某个ip允许登陆。也可以是一个网段（192.168.12.%）。
# 创建后更新授权表
FLUSH PRIVILEGES;
删除用户 （完成后需要更新授权表 FLUSH PRIVILEGES; ）
# DROP USER语句删除
DROP USER '用户名'@'用户登陆方式';
# DELETE语句删除
DELETE FROM mysql.user WHERE user='用户名' AND host='用户登陆方式';
授权 GRANT （完成后需要更新授权表 FLUSH PRIVILEGES; ）
GRANT 权限 ON 库名.表名 TO '用户名'@'用户登录方式';
# 授权后刷新授权表
FLUSH PRIVILEGES;
# 库名.表名
*.*
# *.*为所有库下的所有表
## 常用权限
ALL 所有权限（不包括赋予权限的权限）
CREATE | 数据库、表或索引 | 创建数据库、表或索引权限
DROP | 数据库或表 | 删除数据库或表权限
GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 #小心给予
ALTER | 表 | 更改表,比如添加字段、索引等
DELETE | 表 | 删除数据权限
INDEX | 表 | 索引权限
INSERT | 表 | 插入权限
SELECT | 表 | 查询权限
UPDATE | 表 | 更新权限
LOCK TABLES | 服务器管理 | 锁表权限
CREATE USER | 服务器管理 | 创建用户权限
REPLICATION SLAVE | 服务器管理 | 复制权限
SHOW DATABASES | 服务器管理 | 查看数据库权限
查看权限
# root用户查看自己的权限
SHOW GRANTS\G
# root用户查看别人的权限
SHOW GRANTS FOR 用户名@'用户登陆方式'\G
# 普通用户查看自己的权限
SHOW GRANTS\G
移除权限（完成后需要更新授权表 FLUSH PRIVILEGES; ）
REVOKE 权限 ON 数据库.数据表 FROM '用户'@'用户登陆方式';
# 被回收的权限必须存在,否则会出错
# 整个数据库,使用 ON datebase.*;
# 特定的表:使用 ON datebase.table;
修改登录方式（完成后需要更新授权表 FLUSH PRIVILEGES; ）
use mysql
update user set host = '登陆方式' where user = '用户名';
# 修改登陆方式
FLUSH PRIVILEGES;
# 刷新授权表
mysql库里面权限控制机制的表
user db tables_priv columns_priv
# 用户认证
查看mysql.user表
# 权限认证
# 以select权限为例:
先看 user表里的select_priv权限
Y:不会接着查看其他的表 拥有查看所有库所有表的权限
N:接着看db表
db表: #某个用户对一个数据库的权限。
Y:不会接着查看其他的表 拥有查看所有库所有表的权限
N:接着看tables_priv表
tables_priv表:#针对表的权限
table_priv:如果这个字段的值里包括select 拥有查看这张表所有字段的权限,不会再接着往下看了
table_priv:如果这个字段的值里不包括select,接着查看下张表还需要有column_priv字段权限
columns_priv:针对数据列的权限表
column_priv:有select,则只对某一列有select权限
没有则对所有库所有表没有任何权限
# 注:其他权限设置一样。
# 授权级别排列
mysql.user #全局授权
mysql.db #数据库级别授权
其他 #表级,列级授权
日志管理
Error Log
# 错误日志 :启动,停止,关闭失败报错。rpm安装日志位置 /var/log/mysqld.log #默认开启
# 如果想修改日志则
# vim /etc/my.cnf
# 修改
# log-error=指定路径
# 默认路径为:/var/log/mysqld.log
General query log
# 通用查询日志:所有的查询都记下来。 #默认关闭,一般不开启
Binary Log
# 二进制日志(bin log):实现备份,增量备份。只记录改变数据,除了select都记。
# 默认为关闭,因为使用比较频繁,所以建议开启
# vim /etc/my.cnf
# 添加
# log-bin=指定目录/日志文件名
# 默认目录为:/var/lib/mysql
# server-id=1
# #AB复制的时候使用,为了防止相互复制,会设置一个ID,来标识谁产生的日志
# binlog_format=statement
# 解决binlog日志不显示insert语句
# 需要创建指定路径所对应的目录并且属组和属组给mysql
# mkdir 指定目录
# chown mysql.mysql 指定目录
# systemctl restart mysqld 重启数据库
# 临时暂停binlog（进入数据库后使用）
SET SQL_LOG_BIN=0; #关闭
# 临时开启（进入数据库后使用）
SET SQL_LOG_BIN=1; #开启
# 临时解决binlog不显示insert（进入数据库后使用）
set binlog_format=statement;
Realy log
# 中继日志(Relay log):读取主服务器的binlog,在slave机器本地回放。保持与主服务器数据一致。
Slow query log
# slow log:慢查询日志,指导调优,定义某一个查询语句,执行时间过长,通过日志提供调优建议给开发人员。
# binlog日志查看及删除
# 查看
mysqlbinlog 文件名 -v
# 文件名在binlog指定目录下可以查看
# 查看后内容解释
at 数字
# 时间的开始位置
end_log_pos 数字
# 事件结束的位置(position)
数字 *:*:*:*
# 时间点
# binlog日志的截断与删除
在数据库外重启mysqld 会截断
在数据库内
flush logs;
# 会截断
reset master;
# 删除所有binlog,不要轻易使用,相当于:rm -rf /
PURGE BINARY LOGS TO '文件名';
# 删除文件名之前的日志（文件名在binlog指定目录下可以查看）
##########################
数据备份与恢复
# 备份类型
# 物理备份:直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。
1、热备(hot backup)
# 在线备份,数据库处于运行状态
# 对应用基本无影响(但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做)
2、冷备(cold backup)
# 备份数据文件,需要停机,是在关闭数据库的时候进行的
# 备份 datadir 目录下的所有文件
3、温备(warm backup)
# 针对myisam的备份(myisam不支持热备),备份时候实例只读不可写,数据库锁定表格（不可写入但可读）的状态下进行的
# 对应用影响很大
# 通常加一个读锁
# 逻辑备份:备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL）,适用于中小型数据库,效率相对较低。
# 物理和逻辑备份的区别
| - | 逻辑备份 | 物理备份
| ---------- | ------------------------------ | ----------------------
| 备份方式 | 备份数据库建表、建库、插入sql语句 | 备份数据库物理文件
| 优点 | 备份文件相对较小,只备份表中的数据与结构 | 恢复速度比较快
| 缺点 | 恢复速度较慢(需要重建索引等) | 备份文件相对较大(备份表空间,包含数据与索引)
| 对业务影响 | I/O负载加大 | I/O负载加大
| 代表工具 | mysqldump | ibbackup、xtrabackup,mysqlbackup
# 备份工具
1、ibbackup
- 官方备份工具
- 收费
- 物理备份
2、xtrabackup
- 开源社区备份工具
- 开源免费,上面的免费版本(老版本有问题,备份出来的数据可能有问题)
- 物理备份
3、mysqldump
- 官方自带备份工具 开源免费
- 逻辑备份(速度慢)
4、mysqlbackup
- mysql 官方备份工具
- innodb 引擎的表mysqlbackup可以进行热备
- 非innodb表mysqlbackup就只能温备
物理备份,备份还原速度快
# 物理备份（xtrabackup）
# Xtrabackup是开源免费的支持MySQL 数据库热备份的软件,它不暂停服务创建Innodb热备份
# 安装xtrabackup
wget https://www.percona.com/downloads/percona-release/percona-release-0.1-4/redhat/percona-release-0.1-4.noarch.rpm
rpm -ivh percona-release-0.1-4.noarch.rpm
vim percona-release.repo
# 修改配置文件将检查安装包关闭,防止校验失败
yum -y install percona-xtrabackup-24.x86_64
# 如果安装失败
# 方法1
先安装yum install mysql-community-libs-compat -y
再安装yum -y install percona-xtrabackup-24.x86_64
# 方法2
先安装percona-xtrabackup
再安装mysql
或者先将mysql源back了,重新建立yum缓存。在安装percona-xtrabackup。
# 完整备份及恢复
# 备份
mkdir -p /xtrabackup/all
# 创建备份目录
innobackupex --user=root --password='密码' /xtrabackup/all
# 备份
# 恢复
# 完全备份恢复流程
# 1. 停止数据库
# 2. 清理环境
# 3. 重演回滚－－> 恢复数据
# 4. 修改权限
# 5. 启动数据库
ls/xtrabackup/all
# 查看备份目录下是否备份成功
systemctl stop mysqld
# 关闭数据库
# 模拟数据丢失
rm -rf /var/lib/mysql/*
rm -rf /var/log/mysqld.log
rm -rf /var/log/mysql-slow/slow.log
innobackupex --apply-log /xtrabackup/all/<备份文件名>
# 重演恢复
cat /etc/my.cnf
# 查看是否未datadir=/var/lib/mysql
# 恢复之前需要确认配置文件内有数据库目录指定,不然xtrabackup不知道恢复到哪里
innobackupex --copy-back /xtrabackup/all/<备份文件名>
# 数据恢复
chown mysql.mysql /var/lib/mysql -R
# 给数据库所在目录属主及组修改,防止因属组不同导致无法恢复
systemctl start mysqld
# 重启服务,恢复完成
# 增量备份
# 备份
innobackupex --user=root --password='密码' /xtrabackup/
# 先创建一个完整备份
innobackupex --user=root --password='密码' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/<完整备份文件名>/
# --incremental-basedir:基于哪个增量
# 在数据库中插入第二次数据并备份
innobackupex --user=root --password='密码' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/<上一次的增量备份文件名>/
# 基于前一天的备份为目录备份
......
ls /xtrabackup/
# 查看备份文件
# 恢复
# 增量备份恢复流程
# 1. 停止数据库
# 2. 清理环境
# 3. 依次重演回滚redo log－－> 恢复数据
# 4. 修改权限
# 5. 启动数据库
systemctl stop mysqld
# 停止数据库
rm -rf /var/lib/mysql/*
# 清理环境
innobackupex --apply-log --redo-only /xtrabackup/<完整备份文件名>
# 依次回滚redo log
innobackupex --apply-log --redo-only /xtrabackup/<完整备份文件名> --incremental-dir=/xtrabackup/<增量备份文件名1>
# --incremental-dir:增量目录
# 在完整备份基础上增加增量目录恢复
innobackupex --apply-log --redo-only /xtrabackup/<完整备份文件名> --incremental-dir=/xtrabackup/<增量备份文件名2>
# --incremental-dir:增量目录
# 在完整备份基础上增加另外一个增量目录恢复
innobackupex --copy-back /xtrabackup/<完整备份文件名>/
# 恢复数据
chown mysql.mysql /var/lib/mysql -R
# 给数据库所在目录属主及组修改,防止因属组不同导致无法恢复
systemctl start mysqld
# 重启服务,恢复完成
# 差异备份
# 备份（仅备份与完整备份不同的数据,所以没个差异备份都可以与完整备份进行合并恢复）
innobackupex --user=root --password='密码' /xtrabackup
# 先创建一个完整备份
innobackupex --user=root -password='密码' --incremental /xtrabackup --incremental-basedir=/xtrabackup/<完全备份目录>
# 基于完整备份进行差异备份
innobackupex --user=root -password='密码' --incremental /xtrabackup --incremental-basedir=/xtrabackup/<完全备份目录>
# 基于完整备份进第二次差异备份
innobackupex --user=root -password='密码' --incremental /xtrabackup --incremental-basedir=/xtrabackup/<完全备份目录>
# 基于完整备份进行第三次差异备份
# 恢复
# 差异备份恢复流程
# 1. 停止数据库
# 2. 清理环境
# 3. 重演回滚redo log（完整备份,某次差异）－－> 恢复数据
# 4. 修改权限
# 5. 启动数据库
systemctl stop mysqld
# 停止数据库
innobackupex --apply-log --redo-only /xtrabackup/<完全备份目录>
# 恢复全量的redo log
innobackupex --apply-log --redo-only /xtrabackup/<完全备份目录> --incremental-dir=/xtrabacku/<某个差异备份>
# 恢复差异的redo
innobackupex --copy-back /xtrabackup/<完全备份目录>
# 恢复数据
chown mysql.mysql /var/lib/mysql -R
# 给数据库所在目录属主及组修改,防止因属组不同导致无法恢复
systemctl start mysqld
# 重启服务,恢复完成
# 逻辑备份（mysqldump---- 推荐优先使用）
# mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
# 如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。
# 远程备份
mysqldump -h 服务器 -u用户名 -p密码 数据库名 (表名) > 备份文件名路径
# 一般备份文件名以文件名.sql结尾,以便管理
# 本地备份
mysqldump -u用户名 -p密码 数据库名 (表名) > 备份文件名路径
# 一般备份文件名以文件名.sql结尾,以便管理
# 常用备份选项
-A, --all-databases
#备份所有库
-B, --databases
#备份多个数据库
--no-data,-d
#不导出任何数据,只导出数据库表结构。
# 本地备份及恢复示例
# 备份表
mysqldump -u root -p1 db1 t1 > /mybak/db1.t1.bak
# 单表备份,将db1库内t1表备份到/mybak,备份文件名为db1.t1.bak
mysqldump -u root -p1 db1 t1 t2 > /mybak/db1.2.bak
# 多表备份,将db1库内t1和t2表备份到/mybak,备份文件名为db1.2.bak
# 多表备份,表名之间空格分隔
# 备份库
mysqldump -u root -p1 db1 > /mybak/db1.bak
# 单库备份,将db1库内的全部表备份到/mybak,备份文件名为db1.bak
mysqldump -u root -p1 -B db1 db2 db3 > /mybak/db123.bak
# 多库备份,将db1,db2,db3库内的全部表备份到/mybak,备份文件名为db123.bak
mysqldump -u root -p1 -A > /alldb.bak
# 备份所有库所有表到/mybak,备份文件名为alldb.bak
# 备份表结构
mysqldump -uroot -p123456 -d database table > /dump.sql
# 仅备份database库内table表的表结构备份到/mybak,备份文件路径为/dumpsql
# 仅数据备份（导出数据）
show variables like "secure_file_priv";
# 查询导出数据的存放的目录。
# 修改数据存放的目录
mkdir 目录名
# 创建一个新的目录
chown mysql.mysql 目录地址
# 修改这个目录属主和属组
vim /etc/my.cnf
#编辑mysql配置文件
secure_file_priv=目录地址
# 修改默认存放的目录为自己想修改的目录
# 重启mysql文件生效
select * from t3 into outfile '/var/lib/mysql-files/test.t3.bak';
# 仅备份数据需要先到对应的库
# 备份t3表内的所有数据到/var/lib/mysql-files目录,文件名为test.t3.bak
# /var/lib/mysql-files/ 路径为默认查询导出数据存放的目录
show variables like "secure_file_priv";
# 查询导出数据的存放的目录。
# 恢复库
mysql> create database db;
#登录数据库后创建一个与原库名相同的库
mysql -uroot -p'qf123' db1 < /mybak/db1.bak
# 将备份文件内的db1库恢复到新创建的db1库
# 恢复表
## 数据库内
# 原理:将备份内的sql语句重新自动执行
set sql_log_bin=0;
#停止binlog日志
source /mybak/db1.t1.bak;
# 将t1表恢复
## 数据库外
mysql -u root -p1 库名 < 备份文件路径
# 恢复表结构
# 登陆数据库创建一个库
mysql> create database t1;
mysql -u root -p1 -D t1 < /db1.t1.bak
# 将db1.t1.bak文件内的SQL 语句导入到名为 t1 的数据库中
# 数据导入
# 如果将数据导入别的表,需要创建这个表并创建相应的表结构。（数据导入需要表结构与原表结构相同,否则将导入失败）
load data infile '/var/lib/mysql-files/test.t3.bak' into table t3;
# 将/var/lib/mysql-files/test.t3.bak里的数据回复到t3表内
# 通过binlog恢复
# binlog日志出于开启状态
# 在binlog日志内根据位置恢复,找到要恢复的sql语句的起始位置、结束位置
# at 数字 （这个数字是开始位置）
# end_log_pos （这个数字是结束位置）
cd /日志所在目录（/etc/my.cnf内有）
# 进入到日志所在目录
ls
# 查看目录内所为日志文件
mysqlbinlog 日志名（文件名）
#查看binlog日志内容
mysqlbinlog --start-position 数字（起始位置） --stop-position 数字（结束位置） 日志文件 | mysql -uroot -p'密码'
mysql的索引
# 索引的作用
索引相当于书中的目录,可以提高数据检索的效率,降低数据库的IO。
# 索引的分类
# 普通索引（INDEX）:索引列值可重复
# 唯一索引（UNIQUE）:索引列值必须唯一,可以为NULL
# 主键索引（PRIMARY KEY）:索引列值必须唯一,不能为NULL,一个表只能有一个主键索引
# 全文索引（FULL TEXT）:给每个字段创建索引
# 索引的创建/删除/查看
# 创建索引(索引名可以自定义,一般以字段+index命名)
# 普通索引
# 在创建表时指定
create table 表名(字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件, index 索引名 (字段1(约束)));
# 如:create table student1(id int not null, name varchar(100) not null, birthdy date, sex char(1) not null, index nameindex (name(50)));
# 基于表结构创建
create table 表名(字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件);
create index 索引名 on 表名(字段(约束条件));
# 如:create table student2(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
# create index nameindex on student2(name(50));
# 修改表结构创建
create table 表名(字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件);
alter table 表名 add index 索引名(字段(约束条件));
# 如:create table student3(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
# alter table student3 add index nameIndex(name(50));
# 唯一索引
# 在创建表时指定
create table 表名(字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件, unique index 索引名 (字段1(约束)));
# 如:create table student4(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex (id));
# 基于表结构创建
create table 表名(字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件);
create unique index 索引名 on 表名(字段(约束条件));
# 如:create table student5(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
# create unique index idIndex ON student5(id);
# 删除索引
#普通索引
# 直接删除
drop index 索引名 ON 表名;
# 如:drop index nameIndex ON student1;
# 修改表结构删除
alter table 表名 drop index 索引名;
# 如: alter table student2 drop index nameIndex;
# 唯一索引
# 直接删除
drop index 索引名 on 表名;
# 如:drop index idIndex on student4;
# 修改表结构删除
alter table 表名 drop index 索引名;
# 如: alter table student2 drop index nameIndex;
# 查看索引
show index from 表名;
AB复制（主从复制）
# 什么是主从复制
主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。
# 主从复制的作用
1.做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。。
2.读写分离,使数据库能支撑更大的并发。
1--在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力;（主库写,从库读,降压）
2--在从服务器进行备份,避免备份期间影响主服务器服务;（确保数据安全）
# 主从复制的原理
#通过binlog日志做主从同步的原理
# 原理:
实现整个主从复制,需要由slave服务器上的IO进程和Sql进程共同完成.
要实现主从复制,首先必须打开Master端的binary log（bin-log）功能,因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志,然后再在自己slave端完全顺序的执行日志中所记录的各种操作。
===========================================
1. 在主库上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。
2. 备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库SQL线程读取中继日志中的事件,将其重放到备库数据库之上。
============
master 负责写 -----A
slave relay-log -----B
I/o 负责通信读取binlog日志并写入slave中继日志
SQL 负责写数据
# 过程
步骤一:主库db的更新事件(update、insert、delete)被写到binlog
步骤二:从库发起连接,连接到主库
步骤三:此时主库创建一个binlog dump thread线程,把binlog的内容发送到从库
步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log.
步骤五:还会创建一个SQL线程,从relay log里面读取内容,将更新内容写入到slave的db.
# GTID主从复制
# 什么是GTID？（不用指定binlog）
全局事务标识:global transaction identifiers
是用来代替传统复制的方法,GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。
# GTID工作原理
1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog,写入到本地的relay log中。
3、sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
# 主服务器（master）操作
vim /etc/my.cnf
# 编辑配置文件加入以下内容
server-id=1
# 定义server id master必写
log-bin = <binlog存放路径>
# 开启binlog日志,master比写
gtid_mode = ON
# 开启gtid
enforce_gtid_consistency=1
# 强制gtid
systemctl restart mysqld
# 重启服务
mysql> grant replication slave,reload,super on *.* to '用户名'@'%' identified by '密码';
# 进入数据库后创建一个用户用于进行主从同步,给相对应权限登录方式为允许任何方式登录,这个用户仅用于主从复制
# 注:生产环境中密码采用高级别的密码,实际生产环境中将'%'换成slave（从服务器）的ip
mysql> flush privileges;
# 更新授权表
# 注意:如果不成功删除以前的binlog日志
# replication slave:拥有此权限可以查看从服务器,从主服务器读取二进制日志。
# super权限:允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
# reload权限:必须拥有reload权限,才可以执行flush [tables | logs | privileges]
# 从服务器（slave）操作
vim /etc/my.cnf
# 编辑配置文件加入以下内容
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
systemctl restart mysqld
# 重启服务
mysql -uroot -p'密码'
# 登陆mysql
mysql> \e
# 进入数据库后执行\e编辑添加配置
change master to
master_host='master1',
#主ip 地址 最好用域名
master_user='授权用户',
#主服务上面创建的用户
master_password='授权密码',
master_auto_position=1;
mysql> start slave;
# 启动slave
mysql> show slave status\G
# 查看状态,验证sql和IO是不是yes。
# 是则配置成功
# 主服务器（master）宕机如何切换
# 在从服务器（salve）执行
mysql> stop slave;
mysql> reset master;
show variables like 'read_only';
# 查看是否只读模式
只读模式需要修改my.cnf文件,注释read-only=1并重启mysql服务。
或者不重启使用命令关闭只读,但下次重启后失效:set global read_only=off;
show slave status \G;
# 查看show slave status \G;
在程序中将原来主库IP地址改为现在的从库IP地址,测试应用连接是否正常
# 后端会进行修改
# binlog日志主从复制
# 主服务器
mkdir /var/log/binlog
# 创建binlog日志存放位置
chown mysql.mysql /var/log/binlog
# 修改属主和属组
vim /etc/my.cnf
# 编辑配置文件添加以下内容
log-bin=/var/log/binlog/ （binlog日志存放位置）
server-id=1
systemctl restart mysqld
# 重启服务
mysql> grant replication slave,reload,super on *.* to '用户名'@'%' identified by '密码';
# 进入数据库后创建一个用户用于进行主从同步,给相对应权限登录方式为允许任何方式登录,这个用户仅用于主从复制
# 注:生产环境中密码采用高级别的密码,实际生产环境中将'%'换成slave（从服务器）的ip
mysql> show master status \G
# 查看File及Position
# 从服务器
vim /etc/my.cnf
# 编辑配置文件添加以下内容
server-id=2
systemctl restart mysqld
# 重启服务
mysql -uroot -p'密码'
# 登陆mysql
mysql> \e
# 进入数据库后执行\e编辑添加配置
change master to
master_host='master1',
#主ip 地址 最好用域名
master_user='授权用户',
#主服务上面创建的用户
master_password='授权密码',
MASTER_LOG_FILE='主服务的File',
MASTER_LOG_POS=主服务器的Position;
mysql> start slave;
# 启动slave
mysql> show slave status\G
# 查看状态,验证sql和IO是不是yes。
# 是则配置成功
# 参数解释
CHANGE MASTER TO
MASTER_HOST='master2.example.com', #主服务器ip
MASTER_USER='replication', #主服务器用户
MASTER_PASSWORD='password', #用户密码
MASTER_PORT=3306, #端口
MASTER_LOG_FILE='master2-bin.001', #binlog日志文件名称
MASTER_LOG_POS=4, #日志位置
# 主从复制常见错误故障排查
UUID一致,导致主从复制I/O线程不是yes
致命错误:由于master和slave具有相同的mysql服务器uuid,导致I/O线程不进行;这些uuid必须不同才能使复制工作。
检查主从server_id:
# 主库
mysql> show variables like 'server_id';
# 从库
mysql> show variables like 'server_id';
server_id如果不一样,则排除server_id的原因。
检查主从状态:
# 主库
mysql> show master status;
# 从库
mysql> show master status;
File一样,则排除File原因。
检查uuid
vim /var/lib/mysql/auto.cnf
#uuid相同则修改uuid并重启服务
mysql优化
# 引擎
# 查看引擎:
mysql> show engines;
mysql> SHOW VARIABLES LIKE '%storage_engine%';
mysql> show create table t1; ---查看建表信息
# 临时指定引擎:
mysql> create table innodb1(id int)engine=innodb;
# 修改默认引擎:
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB ----引擎
# 修改已经存在的表的引擎:
mysql> alter table t2 engine=myisam;
# mysql常用命令
mysql> show warnings
# 查看最近一个sql语句产生的错误警告,看其他的需要看.err日志
mysql> show processlist
# 显示系统中正在运行的所有进程。
mysql> show errors
# 查看最近一个sql语句产生的错误信息
# 字符集设置
# 临时:
mysql> create database db1 CHARACTER SET = utf8;
mysql> create table t1(id int(10)) CHARACTER SET = utf8;
# 查看当前使用的字符集
mysql> SHOW VARIABLES LIKE 'character_set_%';
# 永久修改5.7/ 5.5版本字符集设置:
[mysqld]
character-set-server=utf8mb4 #设置MySQL服务器的默认字符集为utf8mb4
collation-server=utf8mb4_unicode_ci #设置服务器的默认排序规则
[client]
default-character-set = utf8mb4 #所有客户端工具的默认字符集设置
[mysql]
default-character-set=utf8mb4 #专门针对mysql命令行客户端的字符集设置
# 慢查询:
查看是否设置成功:
mysql> show variables like '%query%';
# 优化方案
1.开启慢查询 ---分析sql语句,找到影响效率的 SQL
2.创建索引 --提升数据的检索效率
# 当连接数的数值过小会经常出现ERROR 1040: Too many connections错误。
# 这是是查询数据库当前设置的最大连接数
# mysql> show variables like '%max_connections%';
# 强制限制mysql资源设置:
vim /etc/my.cnf
max_connections = 1024
# 并发连接数,根据实际情况设置连接数。
connect_timeout= 5
# 单位秒 ----超时时间,默认30秒
wait_timeout=10
# 终止空闲时间超过10秒的链接,避免长连接
max_connect_errors=10
# 10次连接失败就锁定,使用flush hosts 解锁,或mysqladmin flush-hosts -uroot -p'密码'解锁
# innodb引擎:
innodb-buffer-pool-size //缓存 InnoDB 数据和索引的内存缓冲区的大小
innodb-buffer-pool-size=# ----值
这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。
# vim /etc/my.cnf
innodb-buffer-pool-size=2G
# 查看mysql基本状态
[root@mysql-master ~]# mysqladmin status -uroot -p'Disallow'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Uptime: 23963 Threads: 12 Questions: 1365 Slow queries: 0 Opens: 117 Flush tables: 1 Open tables: 110 Queries per second avg: 0.056
# 常见提示
uptime 启动时间
Threads 线程数量,（打开的会话）
Questions 所有查询的数量
Slow queries 慢查询是否打开
Opens 服务器已经打开的数据库表的数量
flush tables 执行清空表的缓存次数
Open_tables 通过命令打开的表的数量
Queries per second avg:select语句平均查询时间
# mysql监控项
监控项 含义
磁盘空间 实例的空间占用历史趋势,单位GB
连接数 当前总连接数
CPU使用率 mysql实例CPU使用率(占操作系统总数)
数据库的连接数
mysql的主从同步状态
主从延迟时间
进程、端口
# 记录未使用索引的查询
vim /etc/my.cnf
log_queries_not_using_indexes = 1
# 记录未使用索引的查询
# 控制 Binlog 刷盘频率数据安全
vim /etc/my.cnf
sync_binlog=1
# 控制 Binlog 刷盘频率数据安全:设为 1（每次提交同步）性能优先:设为 0（由系统决定)
#慢查询日志分析工具
mysqldumpslow -s t /var/log/mysql/slow.log
# 慢查询日志分析工具
# 查询IO及SQL是否主从同步成功（2个yes）
[root@mysql-slave ~]# mysql -uroot -p'Disallow' -e "show slave status\G" 2> /dev/null | egrep 'Slave_IO_Running: | Slave_SQL_Running:' | awk '{print $2}' | grep Yes | wc -l
扫一扫
2924
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
