Linux运维MySQL篇目

陪小七许个愿

已于 2024-08-19 21:03:53 修改

阅读量484

点赞数 22

文章标签： mysql linux 运维

于 2024-08-19 20:48:48 首次发布

本文链接：https://blog.csdn.net/qq_59671942/article/details/141334596

版权

一、数据库概念

二、MySQL安装(初步)

1.yum安装

下载rpm包（官网寻找MySQL :: Download MySQL Yum Repository）

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工具包

配置mysql的yum源

vim /etc/yum.repos.d/mysql-community.repo
打开mysql5.7的
enabled = 1 
gpgcheck=0
关闭8.0的

安装数据库并启动

yum install -y mysql-community-server
systemctl start mysqld

查找密码登录

grep password /var/log/mysqld.log  ---->获取初始密码
mysql -uroot -p[初始密码]

修改密码

第一种：
mysql> alter user 'root'@'localhost' identified  by '[密码]'；
mysql> flush privileges;
第二种：
mysqladmin -u root -p'[旧密码]' password '[新密码]'

2.编译安装

清理安装环境

yum erase mariadb mariadb-server mariadb-libs mariadb-devel -y
userdel -r mysql
rm -rf /etc/my*
rm -rf /var/lib/mysql

创建mysql用户

useradd -r mysql -M -s /bin/false

下载tar包(MySQL :: Download MySQL Community Server (Archived Versions))

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.27.tar.gz

安装编译工具

yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make
yum -y install cmake

mkdir -p /usr/local/{data,mysql,log}

解压

tar xzvf mysql-boost-5.7.27.tar.gz -C /usr/local/

编译安装

cd /usr/local/mysql-5.7.27/

cmake . \
-DWITH_BOOST=boost/boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DINSTALL_MANDIR=/usr/share/man \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1

初始化

cd /usr/local/mysql
chown -R mysql.mysql .
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data   
----->获取初始密码

修改配置文件

vim /etc/my.cnf 

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8

[mysqld]
port = 3306
user = mysql
basedir = /usr/local/mysql  #指定安装目录
datadir = /usr/local/mysql/data  #指定数据存放目录
socket = /tmp/mysql.sock
character_set_server = utf8


参数详解：
[client]
# 默认连接端口
port = 3306
# 用于本地连接的socket套接字
socket = /tmp/mysql.sock
# 编码
default-character-set = utf8

[mysqld]
# 服务端口号，默认3306
port = 3306
# mysql启动用户
user = mysql
# mysql安装根目录
basedir = /usr/local/mysql
# mysql数据文件所在位置
datadir = /usr/local/mysql/data
# 为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
socket = /tmp/mysql.sock
# 数据库默认字符集,主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
character_set_server = utf8

启动mysql

cd /usr/local/mysql
./bin/mysqld_safe --user=mysql &  后台启动

命令直接启动
vim /etc/profile
最后插入编辑export PATH=/usr/local/mysql/bin:$PATH  后台进行运行
source /etc/profile  加载配置文件

配置systemctl管理启动

vim /usr/lib/systemctl/system/mysql.service

编辑配置
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network-online.target
Wants=network-online.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=simple
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql      
LimitNOFILE = 10000
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false

加载管理
systemctl daemon-reload

3.二进制安装

官方下载二进制包，解压初始化即可直接使用不用安装

4.扩展

配置文件修改密码强度
vim /etc/my.cnf
设置validate_password=off

三、数据库引擎

1.概念

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引、锁表等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

2.mysql引擎

功能

读写功能

事务功能

锁定

备份和恢复

优化

特殊功能

种类

InnoDB存储引擎

MyISAM存储引擎

MEMORY内存型引擎

Archive归档引擎

使用

InnoDB存储引擎：数据表需要提供提交、回滚、并要求实现并发控制

MyISAM存储引擎：数据表主要用来插入和查询记录

MEMORY内存型引擎：临时存放数据，数据量不大，并且不需要较高的数据安全性，存放查询的中间结果

3.引擎使用命令常用

show engines                                    #存储引擎查看

alter table [表名] engine=[引擎]                 #修改某个表的存储引擎

show variables like '%storage_engine%'          #看你的mysql当前默认的存储引擎 

select version()                                #查看Mysql服务器上的版本

create table [表名](字段...) engine=[存储引擎]     #创建表时候指定引擎

四、事务

事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这四个特性也简称ACID性。

（1）原子性：事务是应用中最小的执行单位，就如原子是自然界最小颗粒，事务要么成功，要么撤回.具有不可再分的特征一样。事务是应用中不可再分的最小执行体。（最小了，不可再分了）

（2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态比如：当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。

（3）隔离性：当涉及到多用户操作同一张表时，数据库会为每一个用户开启一个事务。各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不相互影响。（说白了，就是你做你的，我做我的！）

（4）持续性：持续性也称为持久性指事务一旦提交对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。即使数据库崩溃了，我们也要保证事务的完整性。

五、SQL语句

SQL（Structured Query Language 即结构化查询语言）
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。
DDL语句Database Define Language 数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML语句Database Manage Language 数据库操纵语言（对记录的操作）：插入数据INSERT、删除数据DELETE、更新数据UPDATE
DCL语句Database Control Language 数据库控制语言（和权限有关）：例如控制用户的访问权限GRANT、REVOKE
DQL语句Database Query Language 数据库查询语言：查询数据SELECT

1.库

1.创建库
create database [库名] default character set [字符集];
2.查看数据库
show databases;
3.进入数据库
use 库名;
4.查看当前所在的库
select database();
5.查看当前库下所有的表格
show tables	;
6.删除库
drop  database  [库名]

2.表

1.创建表
create table [表名](
    [字段名]  [类型]([宽度])  [约束条件...],
    [字段名]  [类型]([宽度])  [约束条件...],
    ...
    [字段名]  [类型]([宽度])  [约束条件...]
)[存储引擎]  [字符集];

2.修改表名
rename  table  旧表名  to  新表名
alter  table  rename  旧表名 to  新表名
3.查看表状态
show table status like '表名' \G

tips:
在同一张表中，字段名是不能相同
drop table 表名

引擎定义
engine=[引擎]
字符集定定义
default character set [字符集]

3.约束

primary key	主键约束
unique	唯一约束
not null	非空约束
default	默认约束
auto_increment	自动增长约束
foreign key	外键约束

primary key

1.创建表时添加主键约束
create table [表名](
    [字段名] [字段类型] primary key
);


2.表级上添加主键约束
create table [表名](
    [字段名1] [字段类型1],
    [字段名2] [字段类型2],
    .....
    [字段名n] [字段类型n],
    [constraint 主键约束名] primary key([字段名...])
);


3.给已有表添加主键约束
alter table 表名 add [constraint 主键约束名] primary key([字段名...])


4.删除主键约束
alter table [表名] drop primary key

unique key

1.创建表的时候进行添加唯一约束
create table [表名](
    [字段名] [字段类型] unique,
);


2.表级添加唯一约束
create table [表名](
    [字段名1] [字段类型1],
    [字段名2] [字段类型2],
    .....
    [字段名n] [字段类型n],
    [constraint 唯一约束名] unique([字段名...])
);


3.给已有表添加唯一约束
alter table 表名 add [constraint 唯一约束名] unique key([字段名...])


4.删除唯一约束
alter table [表名] drop index [唯一约束名]
tips：
如果单个字段没有指定唯一约束名，则默认的唯一约束名为字段名。
如果是多个字段组合为唯一约束时候，默认的唯一约束名为第一个字段的名称。
如果指定了约束名则删除的时候写约束名。

not null

1.创建表的时候天剑非空约束
create table [表名](
    字段名 字段类型 not null 
);


2.给已有表添加非空约束
alter table [表名] modify [原字段名] [原字段类型] not null


3.删除非空约束
alter table [表名] modify [原字段名] [原字段类型] null

auto_increment

1.创建表的时候添加自动增长约束
create table [表名](
    [字段名] [字段类型] auto_increment
);


2.给已有表添加自动增长约束
alter table [表名] modify [字段名] [字段类型] auto_increment


3.删除自增长约束
alter table [表名] modify [字段名] [字段类型]

foreign key

1.创建表的时候添加外键约束
create table [表名](
    [字段名]  [字段类型],
    [constraint  外键约束名]  foreign key(字段名)  references  表(字段名)  
);


2.在已有表中添加外键约束
alter table [表名] add [constraint 外键约束名] foreign key(字段名) references 表(字段名)


3.删除外键约束
alter table [表名] drop foreign key [外键约束名]

tips:
在后面加on delete set null on update cascade

default

1.创建表的时候添加默认值约束
create table [表名](
    [字段名]  [字段类型]  default  [value]
);


2.给已有表添加默认值约束
alter table [表名] modify [字段名] [字段类型] default [value]


3.删除默认值约束
alter table [表名] modify [字段名] [字段类型]

tips：

key都用drop删除

其余用modify删除

4.表操作

insert (插入)

insert   into   [表名](字段名1...n)   values(记录...)
insert   into   [表名]    set  [字段]=[值],...  

tips:
不填的位置要使用 null
字段之间,隔开
不写字段位置认为插入所有字段记录
添加的记录与表头要对应

delete (删除)

delete  from  [表名]  where  [条件]             删除那个记录
delete  from  [表名]                            删除所有记录

update (修改)

update  [表名]  set  [字段]=[value]  where  [条件]
tips:
[条件]用来定位哪一条记录

select (查询)

简单查询

select   *   from   [表名]                                 查询所有表中所有记录
select  [字段],...  from  [表名]                            查询多个字段记录
select  [字段],...  from  [表名]   where  [条件]             根据条件查询记录
select  [字段] as '[名字]',...  from  [表名]                 显示字段别名
select  distinct  [字段],...   from  [表名]                 查询道德记录避免重复   
select  [函数名](参数),...      from  [表名]                 查询函数所产生的结果

alter (修改)

增加列：
alter  table [表名]  add  [列名]  [字段类型]  [约束]
alter  table [表名]  add  (多个字段)
修改列名字字段类型
alter  table [表名]  change  [旧列名]  [新列名]  [字段类型/旧字段类型]  [约束]
修改字段类型;
alter  table [表名]  modify  [旧列名]  [新字段类型]  [约束]

tips：
after [字段]  在哪个字段之后
first [字段]  在首位

复制表

create table [新表] (select [字段],... from [旧表])

tips：

条件查询

1. and    和/并且   ------同时满足所有条件查询记录
select  字段1,字段2...  from  表名  where  条件1 and 条件2

2. or     或/或者   ------满足一个条件查询记录
select  字段1,字段2...  from  表名  where  条件1 or  条件2

3. between  and    ------满足在范围之内的查询记录
select  字段1,字段2...  from  表名  where  between  [范围1]  and  [范围2]

4. is null         ------满足这一项是空的查询记录
select  字段1,字段2...  from  表名  where  [字段]  is null

5. is not null     ------满足这一项是非空的查询 记录
select  字段1,字段2...  from  表名  where  [字段]  is not null

6. in              ------查询在这几个子集之内的记录
select  字段1,字段2...  from  表名  where  [字段]  in ([固定value])

7. not in          ------查询在不在这几个子集之内的记录
select  字段1,字段2...  from  表名  where  [字段] not in ([固定value])

8. order by        ------排序查询，默认升序 desc降序
select  字段1,字段2...  from  表名  order by [字段]

9. limit           ------限制查询多少行 v1:0->默认第一行以此类推,v2:显示多少条  v:前[v]行
select  字段1,字段2...  from  表名  limit [v1],[v2]    	
select  字段1,字段2...  from  表名  limit [v]

10. group  by      ------根据字段分组查询
select  字段1,字段2...  from  表名  group by [字段] 
select  字段1,GROUP_CONCAT([字段])...  from  表名  group by [字段]          查看分别是谁显示在一条记录中

11. with rollup    ------可以实现在分组统计数据基础上再进行相同的统计
select  字段1,字段2...  from  表名  group by [字段] with rollup

12. 函数
（1）coalesce 来设置一个可以取代 NUll 的名称
coalesce(a,b,c)
如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
（2）max() 最大值
max([字段])
查询最大的一条记录
（3）min()  最小值
min([字段])
查询最小的一条记录
（4）avg()  平均值
avg([字段])
查询记录的平均值
（5）now()  现在的时间
select now()
现在时间
（6）sum()  计算和
sum([字段])
查询某字段的记录和
（7）count() 计算记录数量
count([字段])
查询记录某字段的数量

not取反



NULL说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。

多表查询

内连接  左外连接  右外连接  全外连接

内连接

from  [表1]  inner join  [表2]  on  [条件]

左外连接

from  [表1]  left [outer] join  [表2]   on  [条件]

右外连接

from  [表1]   right [outer] join  [表2]  on [条件]

全外连接 (mysql不支持)

from  [表1]  full [outer] join  [表2]  on [条件]
...from  [表1]   right [outer] join  [表2]  on [条件] union ...from  [表1]  left [outer] join  [表2]   on  [条件]

六、MySQL索引

普通索引（INDEX）：索引列值可重复

唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL

主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引

全文索引（FULL TEXT）：给每个字段创建索引

普通索引常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。

唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。

主键索引是行的唯一标识，因而其主要用途是检索特定数据。

全文索引效率低，常用于文本中内容的检索。

1. 普通索引

1.创建表时创建
create table [表名](
    [字段]  [类型]  [约束],
    ...
    index [索引名] ([字段]([长度]))
)
2.表结构创建
create  index  [索引名]  on  [表名]([字段]([长度]))
3.修改表结构
alter  table  [表名]  add  index  [索引名]([字段]([长度]))
4.删除索引
drop  index   [索引名]  on  [表名]
alter table   [表名]  drop index  [索引名]

2. 唯一索引

1.创建表时创建
create table [表名](
    [字段]  [类型]  [约束],
    ...
    unique index [索引名] ([字段]([长度]))
)
2.表结构创建
create  unique index  [索引名]  on  [表名]([字段]([长度]))
3.修改表结构
alter  table  [表名]  add  unique index  [索引名]([字段]([长度]))
4.删除索引
drop  unique index   [索引名]  on  [表名]
alter  table   [表名]  drop unique index  [索引名]

3. 主键索引

1.创建表时创建
create table [表名](
    [字段]  [类型]  [约束],
    ...
    primary key ([字段])
)
2.修改表结构创建
alter table [表名] add primary key ([字段]);
3.删除主键索引
alter table [表名] drop primary key;

查看索引

show index from [表名];

七、权限管理

1.登录

本地登录
mysql  -u[用户名]  -p[密码]

远程登录
mysql  -u[用户名]  -p[密码]  -h[远程ip地址]  -P[端口号]

tips：
-h	  指定主机名            【默认为localhost】
-P	  MySQL服务器端口       【默认3306】
-u	  指定用户名            【默认root】
-p	  指定登录密码          【默认为空密码】
-e	  接SQL语句，可以写多条拿;隔开
-D    mysql为指定登录的数据库

修改端口rpm安装：vim /etc/my.cnf
在到【mysqld】标签下面添加port=指定端口。重启服务

2.用户管理

创建用户：
create  user  [用户名]@'[ip地址]'  identified by  '[密码]'
flush privileges       #更新授权表


删除用户：
drop  user  [用户名]@'[ip地址]'
delete  from  mysql.user  where user='[用户名]'  and  host='[ip地址段]'

修改密码：
                            ----------仅对修改root用户密码生效----------
mysqladmin  -uroot  -p'[root的密码]'  password  '[新密码]'   
mysql>  set password = '新密码'
                            ----------root修改其他用户密码生效----------
mysql>  set password for '[用户]'@'[ip地址]' = '[新密码]'



tips：
'%'              代表允许所有主机远程登陆
'localhost'      代表本机ip地址只允许本地登录
[ip地址前三段].%  代表这段ip的所有网段，但不包括localhost

3.授权

查看权限
show  grants  for  '[用户]'@'[ip地址]' \G            #查看当前其他用户权限
show grants \G                                      #查看当前用户权限


授权：
grant  [权限]  on  '[库]'.'[表]'  to  '[用户]'@'[ip地址]'  identified by  '[密码]'
flush privileges       #更新授权表


修改远程登录：
use mysql
update user set host = '[ip地址段]' where user = '[用户名]'
flush privileges


移除权限：
revoke  [权限]  on  [库].[表]   from  '[用户]'@'[ip地址]'
flush privileges       #更新授权表


tips:
加入 identified by 后可以创建用户并授权 但是mysql8.0以后不能使用这样子创建用户
*.*表示所有数据库和所有表
all 代表所有权限
权限列表：
| 权限                   | 权限级别                | 权限说明                   
| :--------------------- | :--------------------- | :------------------------
| CREATE                 | 数据库、表或索引        | 创建数据库、表或索引权限                  
| DROP                   | 数据库或表              | 删除数据库或表权限                       
| GRANT OPTION           | 数据库、表或保存的程序   | 赋予权限选项 #小心给予                 
| ALTER                  | 表                      | 更改表，比如添加字段、索引等               
| DELETE                 | 表                      | 删除数据权限                             
| INDEX                  | 表                      | 索引权限                               
| INSERT                 | 表                      | 插入权限                               
| SELECT                 | 表                      | 查询权限                               
| UPDATE                 | 表                      | 更新权限                               
| LOCK TABLES            | 服务器管理               | 锁表权限                               
| CREATE USER            | 服务器管理               | 创建用户权限                             
| REPLICATION SLAVE      | 服务器管理               | 复制权限                                 
| SHOW DATABASES         | 服务器管理               | 查看数据库权限

八、日志管理

1.mysql日志主要类型

错误日志：启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqld.log #默认开启

通用查询日志：所有的查询都记下来。 #默认关闭，一般不开启

二进制日志(bin log)：实现备份，增量备份。只记录改变数据，除了select都记。

中继日志(Relay log)：读取主服务器的binlog，在slave机器本地回放。保持与主服务器数据一致。

slow log：慢查询日志，指导调优，定义某一个查询语句，执行时间过长，通过日志提供调优建议给开发人员。

DDL log：定义语句的日志。

2.开启日志

1.错误日志开启：
vim /etc/my.cnf
配置文件：
[mysqld]
...
log-error=/var/log/mysqld.log

2.二进制日志开启：
vim /etc/my.cnf
配置文件：
[mysqld]
...
log-bin=/var/log/mysql-bin/mylog
创建目录并修改属组属主：
mkdir  /var/log/mysql-bin
chown mysql.mysql /var/log/mysql-bin/

tips:
mysqlbinlog [binlog日志路径]   查看binlog日志

binlog文件
at [起始位置]
end_log_pos [结束位置]

对bin-log日志进行操作
mysql> purge binary logs to 'mylog.[第几个log]'  删除mylog.[第几个log]之前的log日志不包含自身
mysql> flush logs;        会截断上一个mysqlog 增加一个mysqlog
systemctl restart mysqld; 会截断上一个mysqlog 增加一个mysqlog
mysql> reset master;      删除所有binlog

临时开启和关闭binlog
mysql> set sql_log_bin=0; 临时关闭binlog
mysql> set sql_log_bin=1; 临时开启binlog

插入时无法记录的问题：
mysql> set binlog_format=statement;   临时解决insert插入不到binlog日志
my.cnf:
[mysqld]
binlog_format=statement



3.启动慢查询日志
vim /etc/my.cnf
配置文件：
[mysqld]
...
slow_query_log=1  #开启
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3    #设置慢查询超时间，单位是秒

创建目录并修改属组属主：
mkdir /var/log/mysql-slow/
chown mysql.mysql /var/log/mysql-slow/

tips：
1.解决binlog日志不记录insert语句
登录mysql(临时)
set binlog_format=statement
在my.cnf中：
[mysqld]
...
binlog_format=statement
重启服务

2.expire_logs_days = 7  #binlog日志自动删除/过期的天数。默认值为0,表示不自动删除

九、数据备份与恢复

1.percona-xtrabackup 物理备份

安装percona-xtrabackup

1.安装最新rpm包
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
2.启用存储库
percona-release enable-only tools release
3.安装percona-xtrabackup
yum install -y percona-xtrabackup-24   适用于mysql5.7
yum install -y percona-xtrabackup-80   适用于mysql8.0
4.查看版本
innobackupex -v

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
innobackupex version 2.4.29 Linux (x86_64) (revision id: 2e6c0951)

5.success完成

完全备份

1.创建备份目录
mkdir  /xtrabackup/full  -p
2.备份数据
innobackupex  --user=[mysql用户名]  --password='[mysql密码]'  /xtrabackup/full
3.查看备份数据
cd /xtrabackup/full/
ls
4.停止mysql并删除所有数据
systemctl stop mysqld
rm  -rf  /var/lib/mysql/*  /var/log/mysqld.log  /var/log/mysql-slow/slow.log
5.重演恢复
innobackupex --apply-log /xtrabackup/full/[备份目录]/
6.确认数据库目录
[mysqld]
datadir=/var/lib/mysql
7.恢复数据
innobackupex --copy-back /xtrabackup/full/[备份目录]/
8.修改权限
chown mysql.mysql  /var/lib/mysql  -R


tips：
innobackupex  [命令参数]  [备份文件目录路径]
--apply-log		           在进行数据库恢复操作之前执行,以确保数据的一致性和完整性
--redo-only     	       不回滚未提交事务
--copy-back     	       恢复备份目录
--incremental		         指定增量备份
--incremental-basedir	   指定基于哪个备份做增量备份，最后是增量备份保存的目录。
--incremental-dir        指定增量目录

增量备份

1.创建备份目录
mkdir  /xtrabackup/full  -p
2.进行全备份
innobackupex  --user=[mysql用户名]  --password='[mysql密码]'  /xtrabackup/full
3.进行数据修改
4.进行第二次增量备份
innobackupex --user=[mysql用户名]  --password='[mysql密码]' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/[第一次备份的目录]/
5.修改数据
6.进行第三次增量备份
innobackupex --user=[mysql用户名]  --password='[mysql密码]' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/[第二次次备份目录]/
7.停止数据库服务
systemctl stop mysqld
8.清理环境
rm -rf /var/lib/mysql/*
9.依次重演回滚redo log－－> 恢复数据
(1)第一次完全备份重演回滚
innobackupex --apply-log --redo-only /xtrabackup/full/[第一次备份目录]/
(2)第二次增量备份重演回滚
innobackupex --apply-log --redo-only /xtrabackup/full/[第一次备份目录]/  --incremental-dir=/xtrabackup/full/[第二次备份目录]/
(3)第三次增量备份重演回滚
innobackupex --apply-log --redo-only /xtrabackup/full/[第一次备份目录]/  --incremental-dir=/xtrabackup/full/[第三次备份目录]/
(4)恢复数据
innobackupex --copy-back /xtrabackup/full/[第一次备份目录]
10.修改权限
chown -R mysql.mysql /var/lib/mysql
11.启动
systemctl start mysqld

差异备份

1.创建备份目录
mkdir  /xtrabackup/full  -p
2.初始化数据库数据
3.第一次进行全备份
innobackupex  --user=[mysql用户名]  --password='[mysql密码]'  /xtrabackup/full
4.进行第二次修改数据
5.第二次进行增量备份
innobackupex --user=[mysql用户名]  --password='[mysql密码]' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/[第一次备份的目录]/
6.进行第三次数据修改此次不做备份
7.进行第四次数据修改
8.进行差异备份
innobackupex --user=[mysql用户名]  --password='[mysql密码]' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/[第一次备份的目录]/
9.停止数据库
systemctl stop mysqld
10.清除数据
rm -rf /var/lib/mysql/*
11.回滚日志
innobackupex --apply-log --redo-only /xtrabackup/full/[第一次备份的目录]
12.将第一次备份数据和第四次备份数据进行合并
innobackupex --apply-log --redo-only /xtrabackup/[第一次备份的目录]  --incremental-dir=/xtrabackup/[最后一次备份的目录]
13.恢复数据
innobackupex --copy-back /xtrabackup/[第一次备份的目录]
14.修改权限
chown -R mysql.mysql /var/lib/mysql
15.启动数据库
systemctl start mysqld

2.mysqldump逻辑备份

备份

远程备份

本机备份

远程备份：
mysqldump  -h 服务器  -u用户名  -p密码  数据库名  > 备份文件.sql
本机备份：
mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql

备份选项：
-A, --all-databases #备份所有库

-B, --databases  #备份多个数据库

-F, --flush-logs #备份之前刷新binlog日志

--default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

--no-data，-d #不导出任何数据，只导出数据库表结构。

--lock-tables #备份前，锁定所有数据库表

--single-transaction #保证数据的一致性和服务的可用性

-f, --force #即使在一个表导出期间得到一个SQL错误，继续。



tips：
--single-transaction           服务运行期间备份可以选择添加

备份表：
mysqldump  -u[mysql用户名]  -p[密码]  [数据库] [表名] ... > [备份到的那个路径下的文件].sql
备份库：
mysqldump  -u[mysql用户名]  -p[密码]  -B  [数据库] ... >  [备份到的那个路径下的文件].sql    备份多个数据库     
mysqldump  -u[mysql用户名]  -p[密码]  -A   >  [备份到的那个路径下的文件].sql                全部数据库备份
mysqldump  -u[mysql用户名]  -p[密码]  [数据库]   >  [备份到的那个路径下的文件].sql           单个数据库备份

备份表结构：
mysqldump  -u[mysql用户名]  -p[密码]  -d  [数据库] [表]  >   [备份到的那个路径下的文件].sql

恢复

恢复表:
mysql交互下恢复：
mysql> set sql_log_bin=0;
mysql> use [数据库];
mysql> source [备份到的那个路径下的文件].sql;

tty下恢复：
mysql  -u[mysql用户名]  -p[密码]  [数据库]  < [备份到的那个路径下的文件].sql

恢复库：
mysql  -u[mysql用户名]  -p[密码]   < [备份到的那个路径下的文件].sql

恢复表结构：
mysql -u[mysql用户名]  -p[密码] -D [数据库] < [备份到的那个路径下的文件].sql

数据的导入导出（无表结构）

1.创建一个安全文件目录
mkdir  [目录路径] -p
2.修改权限
chown  -R mysql.mysql [目录路径]
3.编辑配置文件
vim /etc/my.cnf
[mysqld]
secure_file_priv=[目录路径]
4.重启mysql
systemctl restart mysqld
5.备份表结构
mysqldump  -u[mysql用户名]  -p[密码]  -d  [数据库] [表]  >   [备份到的那个路径下的文件].sql
6.导出数据
mysql> select * from [表] into outfile '[安全文件目录路径]/[文件名].sql';
7.恢复表结构
mysql -u[mysql用户名]  -p[密码] -D [数据库] < [备份到的那个路径下的文件].sql
8.导入数据
mysql> load data infile '[安全文件目录路径]/[文件名].sql' into table [表];

导出
mysql> select * from [表] into outfile '[安全文件目录路径]/[文件名].sql';
导入
mysql> load data infile '[安全文件目录路径]/[文件名].sql' into table [表];

通过binlog恢复数据

1.开启日志
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/var/log/sql-bin/mylog
2.创建目录
mkdir /var/log/sql-bin -p
3.修改权限
chown -R mysql.mysql /var/log/sql-bin
4.重启mysql
systemctl restart mysqld

1.刷新log
mysql> flush logs
2.查看起始位置
mysqlbinlog  /var/log/sql-bin/mylog.000002
找到你要恢复的位置的语句查看他的起始位置
3.恢复
 mysqlbinlog --start-position [起始位置] --stop-position [结束位置] mylog.000002 | mysql -u[mysql用户名] -p'[密码]'

十、MySQL的优化

优化引擎：
show  engines;                                          查看引擎种类

show  variables  like '%storage_engine%';                查看当前mysql的默认引擎

show create table [表名];                                 查看建表信息

create table [表名]([字段])engine=[指定引擎类型];          建表时指定引擎

vim   /etc/my.cnf                                         修改默认创建引擎                                           
[mysqld]
default-storage-engine=INNODB             设置默认引擎
innodb-buffer-pool-size=[大小]             缓存 InnoDB 数据和索引的内存缓冲区的大小
这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。


alter table t2 engine=[引擎类型];          更改引擎类型      

查看错误语句：
show warnings      查看最近一个sql语句产生的错误警告，看其他的需要看.err日志
show processlist   显示系统中正在运行的所有进程。
show errors        查看最近一个sql语句产生的错误信息


字符集设置：
vim  /etc/my.cnf
[mysqld]
character_set_server = utf8

create database [数据库] CHARACTER SET = utf8;
create table [表名]([字段])CHARACTER SET = utf8;

show variables like '%query%'        查看是否设置成功

mysql连接数：
show variables like '%max_connections%';   查看最大连接数

vim /etc/my.cnf
[mysqld]
max_connections = 1024    并发连接数，根据实际情况设置连接数
connect_timeout = 5       连接超时，单位秒 ----超时时间，默认30秒

十一、主从复制

1.原理

实现整个主从复制，需要由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日志

SQL 负责写数据

2.主从复制的步骤

步骤一：主库db的更新事件(update、insert、delete)被写到binlog

步骤二：从库发起连接，连接到主库

步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库

步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.

步骤五：从库还会创建一个SQL线程，从relay log里面读取内容，将更新内容写入到slave的db.

3.M-S 架构GTID 基于事务ID复制

工作原理

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操作
1.编辑配置文件
vim  /etc/my.cnf
[mysqld]
server-id=1
log-bin=/var/log/mysql/sql-bin/mylog          
gtid_mode=ON                                  
enforce_gtid_consistency=1              

2.修改binlog目录属组
mkdir -p /var/log/mysql/
chown mysql.mysql [log-bin的值] -R
systemctl restart mysqld

3.创建主从同步用户
mysql> grant replication  slave,super,reload  on   *.* on '[用户名]'@'[ip地址]' identified by '[密码]';
mysql> flush privileges;

slave操作：
1.编辑配置文件
vim /etc/my.cnf
[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

2.重启mysql服务
systemctl restart mysqld

3.增加主机连接
mysql>  change master to
     -> master_host='[主机ip地址]',
     -> master_user='[主机所创建的用户名]',
     -> master_password='[主机所创建的用户名的密码]',
     -> master_auto_position=1;

4.启动slave
mysq> start slave；
mysq> show slave status \G;




tips:
1.stop slave;         停止从库
2.reset slave  all    清楚从库信息
3.reset master        清除master上主从信息
4.参数详解：
gtid_mode                          用于控制GTID的使用模式    on  off
server-id                          用于设置MySQL服务器的唯一标识符   任意数字
enforce_gtid_consistency           用于控制是否强制实施GTID（全局事务标识符）的一致性  1 0
master-info-repository             用于指定主服务器信息的存储位置存储在文件中还是存储在表   table file 
relay-log-info-repository          用于指定中继日志（relay log）信息的存储位置，存储在文件中还是存储在表  table file
log-bin                            开启binlog日志   日志路径

4.binlog主从复制

master：
1.编辑配置文件
vim /etc/my.cnf
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

2.创建目录并授予权限
mkdir -p /var/log/mysql/
chown mysq.mysql /var/log/mysql/ -R
systemctl restart mysqld

3.创建主从同步用户
mysql> grant replication  slave,super,reload  on   *.* on '[用户名]'@'[ip地址]' identified by '[密码]';
mysql> flush privileges;

4.查看binlog 的 position
mysql> show  master status \G   ---->[记录log] 和 [position]

slave：
1.编辑配置文件
vim /etc/my.cnf
[mysqld]
server-id=2

2.增加主机连接
mysql>  change master to
     -> master_host='[主机ip地址]',
     -> master_user='[主机所创建的用户名]',
     -> master_password='[主机所创建的用户名的密码]',
     -> master_log_file='[记录log]',
     -> master_log_pos=[position] ;

3.开启slave
mysq> start slave；
mysq> show slave status \G;


tips：

5.MyCat读写分离

概念

将数据库的读和写操作分不到不同的数据库节点上。主服务器负责处理写操作和实时性要求较高的读操作，从服务器负责处理读操作。

读写分离的优势

1. 分摊服务器压力，提高机器的系统处理效率。

2. 在写入不变，大大分摊了读取，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了。

3. 增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务。

部署MyCat

准备一台新的服务器
1.安装jdk1.8
wget https://download.oracle.com/otn/java/jdk/8u411-b09/43d62d619be4e416215729597d70b8ac/jdk-8u411-linux-x64.tar.gz
tar -xzvf jdk-8u411-linux-x64.tar.gz -C /usr/local/
cd /usr/local/
mv jdk1.8.0_221/ java

2.配置环境变量
vim /etc/profile
最后加入：
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
启用：
source /etc/profile

3.下载MyCat安装
wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
or
rz 

tar xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/


4.修改配置文件server.xml和schema.xml
server.xml:
vim /usr/local/mycat/conf/server.xml

更换位置：
<user name="[逻辑库用户名]">  //远程登录逻辑库的用户名
  <property name="password">[逻辑库密码]</property> //远程登录逻辑库的密码
  <property name="schemas">syh_db</property>  //显示的逻辑库database名称
  ...
</user>
<!--均可自定义，自定义化配置-->

scheam.xml:
vim /usr/local/mycat/conf/schema.xml

更换位置：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="syh_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
  </schema>
  <dataNode name="dn1" dataHost="localhost1" database="syh_db" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.252.142:3306" user="mycat" password="@Syh2025659">
      <readHost host="hostS1" url="192.168.252.141:3306" user="mycat" password="@Syh2025659" />
      <readHost host="hostS2" url="192.168.252.143:3306" user="mycat" password="@Syh2025659" />
    </writeHost>
  </dataHost>
</mycat:schema>

tips:
配置模块解析
逻辑库和分表设置：
<!--这里定义的是分表的信息--> 
<schema name="[逻辑库名称]"           // 逻辑库名称,与server.xml的一致
        checkSQLschema="false"    	 // 不检查sql
        sqlMaxLimit="100"            // 最大连接数
        dataNode="[节点名称]">       //  数据节点名称       
</schema>

<!--数据节点-->
<dataNode name="[节点名称]"                  // 此数据节点的名称
          dataHost="[主机组虚拟名称]"        // 主机组虚拟的名称
          database="[数据库真实名称]" />     // 真实的数据库名称
</dataNode>

<!--这里可以配置关于这个主机组的成员信息，和针对这些主机的健康检查语句-->
<dataHost name="localhost1"                       // 主机组
          maxCon="1000" minCon="10"               // 连接
          balance="0"                             // 负载均衡
          writeType="0"                           // 写模式配置
          dbType="mysql" dbDriver="native"        // 数据库配置
          switchType="1"  slaveThreshold="100">
</dataHost>


<!--健康检查-->
<heartbeat>select user()</heartbeat>  //对后端数据进行检测，执行一个sql语句，user()内部函数


<!--读写配置-->
<writeHost host="hostM1" url="192.168.252.142:3306" user="mycat" password="@Syh2025659">
      <readHost host="hostS1" url="192.168.252.141:3306" user="mycat" password="@Syh2025659" />
      <readHost host="hostS2" url="192.168.252.143:3306" user="mycat" password="@Syh2025659" />
</writeHost>




5.每台机子上创建授权用户和在主节点上创建库
mysql> create database syh_db
mysql> grant all on syh_db.* to mycat@'%' identified by '@Syh2025659';
mysql> flush privileges;

6.启动前配置
vim /usr/local/mycat/conf/wrapper.conf
配置：
wrapper.startup.timeout=300 
wrapper.ping.timeout=120

/usr/local/mycat/bin/mycat start

7.测试是否启动成功
jps
netstat -tnpl    8066端口是否开启

cat  /usr/local/mycat/logs/wrapper.log  查日志排错

8.登录mycat客户端
mysql  -u mycat -p @Syh2025659 -h [MyCat客户端ip地址] -P 8066

十二、其他操作

字符集

永久设置默认字符集：
vim /etc/my.cnf
[mysqld]
...
collation-server=utf8_general_ci
character-set-server=utf8
...
[mysql]
...
default-character-set=utf8
...


建表创建字符集：
create table [表名] (
  ...
)default charset=utf8;

修改表的字符集：
alter table [表名] default character set utf8

set global 要修改的字段=utf8

MySQL破解密码

1.配置文件修改
vim /etc/my.cnf
[mysqld]
skip-grant-tables

mysql> select * from mysql.user \G;
mysql> update mysql.user set authentication_string=password('[密码]') WHERE user='root' AND host='localhost';

2.二进制启动修改
systemctl stop mysqld
mysqld --skip-grant-tables --user=mysql &
mysql -uroot

mysql> select * from mysql.user \G;
mysql> update mysql.user set authentication_string=password('[密码]') WHERE user='root' AND host='localhost';