mysql数据库

数据库基础

MySQL特点：适用于中小规模，关系型数据库
主配置文件：/etc/my.cnf
数据库目录：/var/lib/mysql
日志文件:/var/log/mysqld.log
端口：3306 进程名：mysql
传输协议：TCP
进程所有者：mysql 进程所属组：mysql
常见的关系型数据库：Access、SQL server、Oracle、MySQL
三级模式：一个数据库只能有一个模式，外模式也叫用户模式，内模式也叫存储模式
数据库模型：层次模型、网状模型、关系模型
优势：真正的多用户多线程，快速，便捷，简单的数据库

数据库得基本管理

Information_schema:虚拟库，主要存储了系统中的一些数据库对象得信息
Performance_schema、sys:主要存储数据库服务器的性能参数
Mysql:授权库，主要存储系统用户的授权信息
数据存储流程：连接数据库服务器–>建库（相当于系统文件夹）—>建表（类似于系统文件）–>插入记录–>断开连接
mysql -h服务器 -u用户名 -P密码 进入数据库
quit、exit 退出
不区分大小写，以分号结束或分隔，不支持Tab,\c可废弃当前编写错的操作指令
常用的SQL操作指令：DDL数据库定义语言（create alter drop ）DML数据库操作语言（insert update delete ） DQL数据库查询语言（select） DCL数据库控制语言（grant revoke）DTL数据库事物语言(commit rollback savepoint)
库管理命令：show databases;显示已有的库
Use 库名；切换库
Rename database 库名 to 新库名，不安全会有数据丢失，可以使用脚本修改
Select database();显示当前所在的库
Create database 库名；创建新库
Show tables;显示所在库下已有表
Drop database 库名；删除库
Drop table 表名；删表
Truncate 表名；先删除一个表再创建一个相同的表
Delete from 表名；删除表所有记录
数据库的命名：可以使用数字/下划线/字母，不能使用纯数字，区分大小写具有唯一性，不能使用特殊字符、关键字。
表管理命令：desc 表名；show create table 表名；查看表结构
Select * from 表名；查看表所有记录
Insert into 表名 values（值列表）；插入表记录
Update 表名 set 字段=值；修改表记录
常见的信息种类：数值型、字符型、枚举型、日期时间型
设置数据库的编码：set names utf8/gbk
create database test default character set utf8;

MySQL的安装

tar -xf mysql-5.7.17.tar
yum -y install perl-JSON（依赖包）
rpm -Uvh mysql-community-*.rpm （-U 更新安装）
rpm -qa |grep -i mysql
systemctl restart mysqld 
systemctl enable mysqld
Netstat  -nputl |grep 3306
Ps -ef|grep mysqld
连接服务器
grep password /var/log/mysqld.log
mysql -h localhost -u root -p'密码' 登陆数据库
alter user root@"localhost" identified by "123qqq...A"；修改管理员密码
show global variables like "%password%"查看密码策略
set global validate_password_policy=0;
set global validate_password_length=6;临时修改密码策略
alter user root@"localhost" identified by "123456";

Vim /etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=6
systemctl restart mysqld
Systemctl enable mysqld

数据库的基本操作

Help 命令 可以查看帮助
create database studb;建库
use studb;切换库
create table studb.student(name char,age int, homeaddress char);建表
desc student;查看表结构
Insert into studb.student values(“a”,19,“b”),(“d”,21,“f”);插入表记录
select * from studb.stucrdent;查看表记录
update student set age=18;修改所有行表记录
select * from studb.student;
update student set age=28 where name=“a”;修改a行表记录
select * from studb.student;
delete from student where name=“d”;删除d行表记录
select * from studb.student；
delete from student;删除表所有内容
drop table student;删除表
use studb;
drop database studb;删除库
create table 学生库.学生表(学号 char(9), 姓名 char(3), 性别 char(1))DEFAULT CHARSET=utf8(中文字符集);
基本用法：alter table 表名 执行动作（add添加字段 modify修改字段类型 drop删除字段 rename修改表名 change 修改字段名）；
添加新字段
Alter table 表名 add 字段名 类型（宽度）约束条件After 字段名或first；
alter table t1 add stu_num char(5) after name;添加新字段
alter table t1 add projiect varchar(10) not null first;设为第一个字段，first后不接东西。
修改字段类型
Alter table 表名 modify 字段名 类型（宽度） 约束条件After 字段名first；
Alter table t2 modify sex enum(“m”,”w”) not null default “m” after name; 换字段顺序
Alter table t2 modify stu_num varchar(10）after name;
修改字段名，类型
Alter table 表名 change 源字段名 新字段名 类型（宽度） 约束条件；
Alter table t2 change stu_num stu_id varchar(10);
删除字段
alter table 表名 drop 字段名1，字段名2，……
Alter table t2 drop likes;
修改表名
Alter table 表名 rename 新表名 表对应的文件名也会变
Alter table t2 rename t22;
Rename table 表名 to 新表名
管理表记录
增加表记录：
格式1:添加1条记录,给所有字段赋值
insert into 表名 values (字段值列表);
格式2:添加N条记录,给所有字段赋值
insert into 表名
values
(字段值列表), 第1条表记录
(字段值列表), 第2条表记录
(字段值列表); 第3条表记录
格式3:添加1条记录,给指定字段赋值
insert into 表名 (字段名列表) values (字段值列表);
格式4:添加N条记录,给指定字段赋值
insert into 表名 (字段名列表)
values
(字段值列表), 第1条表记录
(字段值列表), 第2条表记录
(字段值列表); 第3条表记录
根据查询结果插入数据
insert into grade select * from gradebak;
注意事项
字段值要与字段类型相匹配
对于字符类型的字段,要用双或单引号括起来
依次给所有字段赋值时,字段名可以省略
只给一部分字段赋值时,必须明确写出对应的字段名称
查询表记录：
格式1
SELECT 字段1, … …, 字段N FROM 表名;
格式2
SELECT 字段1, … …, 字段N FROM 表名WHERE 条件表达式;
注意事项
使用 * 可匹配所有字段
指定表名时,可采用 库名.表名的形式
更新表记录：
格式1,更新表内的所有记录
UPDATE 表名 SET 字段1=字段1值,字段2=字段2值,字段N=字段N值;
格式2,只更新符合条件的部分记录
UPDATE 表名 SET 字段1=字段1值,字段2=字段2值,字段N=字段N值 WHERE 条件表达式;
注意事项
字段值要与字段类型相匹配
对于字符类型的字段,要用双或单引号括起来
若不使用WHERE限定条件,会更新所有记录
限定条件时,只更新匹配条件的记录
删除表记录：
格式1,仅删除符合条件的记录
DELETE FROM 表名 WHERE 条件表达式;
格式2,删除所有的表记录
DELETE FROM 表名;
格式3：使用truncate删除记录
Truncate table 表名; //连同表空间一并删除
匹配条件
数值比较：
= 等于>、>= 大于、大于或等于<、<= 小于、小于或等于!= 不等于
字符比较/匹配空/非空：
= 相等 != 不相等IS NULL 匹配空IS NOT NULL 非空
逻辑匹配：
OR 逻辑或 AND 逻辑与 ! 逻辑非 ( ) 提高优先级，And的优先级比or高
范围内匹配/去重显示：
in (值列表) 在…里…
mysql> select * from db1.daoru where id not in (49,50,23,23,43);
not in (值列表) 不在…里…
between 数字1 and 数字2 在…之间…
mysql> select * from db1.daoru where id between 10 and 15;
distinct 字段名 去重显示
mysql> select distinct shell from user;
模糊查询：
where 字段名 like ‘通配符’
_ 匹配单个字符、% 匹配0~N个字符
ysql> select * from user where homedir like “___________";
mysql> select * from user where homedir like "”;
mysql> select * from user where homedir like “%y”;
mysql> select * from user where homedir like "%y%”;
正则表达式：
where 字段名 regexp '正则表达式’
正则元字符 ^ $ . [ ] * |
mysql> select * from user where uid regexp “^…$”;
mysql> select * from user where gid regexp “[^123]”;
四则运算：

• 加法 - 减法 * 乘法 / 除法 % 取余数(求模)
  mysql> select uid,gid,uid+gid from user where uid<100;
  mysql> select uid,gid,uid+gid he from user where uid<100;
  mysql> select uid,gid,(uid+gid)/2 arq from user where uid<100;
  mysql> select uid,gid,(uid+gid)*3 ji from user where uid<100;
  mysql> select uid,gid,(uid+gid)*3 ji from user where uid%2=0;
  mysql> update user set uid=uid+1 where uid<=3;
  操作查询结果
  聚集函数：
  MySQL内置数据统计函数
  avg(字段名) //统计字段平均值
  sum(字段名) //统计字段之和
  min(字段名) //统计字段最小值
  max(字段名) //统计字段最大值
  count(字段名) //统计字段值个数
  mysql> select avg(gid) from db1.daoru;
  mysql> select sum(gid) from db1.daoru where id<=10;
  查询结果排序：
  SQL查询order by 字段名 [ asc | desc ];通常是数值类型字段
  mysql> select uid from db1.daoru where id<=10 order by uid desc;
  Select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
  查询结果分组：
  SQL查询 group by 字段名;通常是字符类型字段
  mysql> select 部门,sum(薪水) from 员工表 where 性别=“女” group by 部门；
  查询结果再过滤：
  SQL查询 having 条件表达式;
  SQL查询 where 条件 having 条件表达式;
  SQL查询 group by 字段名 having 条件表达式;
  mysql> select shell from user group by shell having shell="/bin/shutdown";
  限制查询结果显示行数：
  SQL查询 limit N;//显示查询结果前N条记录
  SQL查询 limit N,M; //显示指定范围内的查询记录
  SQL查询 where 条件查询 limit 3;//显示查询结果前3条记录
  SQL查询 where 条件查询 LIMIT 3,3;//从第4条开始,共显示3条
  mysql> select id,name from user where id<=20 limit 10;
  mysql> select id,name from user where id<=20 limit 0,10;
  mysql> select id,name from user where id<=20 limit 3,10;
  mysql> select id,name from user where id<=20 limit 3,1;
  where子查询:
  使用where子查询
  把内层查询结果作为外层查询的查询条件
  select 字段名列表 from 表名 where 条件 (select 字段名列表 from 表名 where 条件);
  带in的子查询：
  mysql> select name from user where name in (select name from t1);
  mysql> select name from user where name in(select user from mysql.user where host=“localhost”);
  带比较运算符的子查询：
  mysql> select name,uid from user where uid > (select avg(uid) from user);
  带exists的子查询：自查寻返回的是true则执行外查询，子查询返回的false则不执行外查询或查不出任何值。
  Select * from tb_row where exists (select * from tb_book where id=27);tb_book表中有满足条件的id=27的记录，则把表中满足条件的记录取出
  带any的子查询：满足子查询的任意一个条件，执行外查询
  Select books,row from tb_book where row<any(select row from tb_row);
  带all的子查询：满足子查询的所有条件，执行外查询
  Select books,row tb_books where row>=all(select row from tb_row);
  左连接查询:（外联接），左表记录会全部显示，右表只显示满足条件的记录
  select 字段名列表 from表a left join 表b on 条件表达式;
  右连接查询:（外连接），右表记录会全部显示，左表只显示满足条件的记录
  select 字段名列表 from表a right join 表b on 条件表达式;
  mysql> select * from t1 left join t3 on t1.uid=t3.uid;
  mysql> select * from t1 right join t3 on t1.uid=t3.uid;
  concat联合多列：
  select id,concat(id,"->",name) info,name from world.city limit 10;
  Select dep_id,group_concat(name) from employee5 group by dep_id;
  

  按部门id分组，将部门id相同的名字拼接在一起
  合并查询（全连接查询）：
  Union：合并查询结果，去除相同的结果
  Select user from tb_book union select user from tb_login;
  Union all:合并所有查询结果
  Select user from tb_book union all select user from tb_login;
  多表查询:
  格式1– select 字段名列表 from 表a, 表b;
  格式2– select 字段名列表 from 表a,表b where 条件;
  以上格式的查询结果叫笛卡尔集
  查询结果的总条目数是 = (表a的记录数 x 表b的记录数)
  mysql> select * from t1,t2;
  mysql> select t1.name,t2.name from t1,t2;
  mysql> select t1.*,t2.name from t1,t2;
  内连接：只匹配符号条件的行，两张表中有相同字段
  mysql> select * from t1,t2 where t1.uid=t2.uid;
  mysql> select * from t1,t2 where t1.uid=2 and t2.uid=2;
  mysql>select t1.name,t1.uid,t2.name,t2.uid from t1,t2 where t1.uid=t2.uid;

数据类型

1.数值型

alter table grade add 学分 int(3) zerofill default 0;零填充5位
Float(M,D) M表示有效位数，D表示小数位
Decimal的精度比float精度高
2.字符型
Char(字符数)
最长255个字符，不够指定字符数是在右边用空格补齐，超出时，无法写入数据。
Varchar(字符数)
最长255个字符，按数据实际大小分配存储空间，超出时255时，自动转换为text类型。
Text/blob
字符大于65535个字符时用。
3.日期时间类型
Datetime（没赋值时默认赋值为空） timestamp(未赋值时，自动以系统时间赋值) date year time
4.枚举类型
Enum(值1，值2，值3)定义选择单个值
Set(值1，值2，值N)定义选择多个值
mysql> create table db1.t6(
-> name char(5),
-> sex enum(“man”,“wuman”),
-> likes set(“eat”,“play”,“game”,“sleep”)
-> );
mysql> desc t6;
5.时间函数
Now()获取系统当前日期和时间
Year()获取指定时间中的年份 select year(19940108);select year ( now() );
Day()获取指定时间中的日期
Sleep（）休眠N秒select sleep(5);
Curdate()获取当前的系统日期
Curtime()获取当前的系统时刻
Month()获取指定时间中的年份select month(now());
Date()获取指定时间中的日期 select time(now());
Time（）获取指定时间中的时刻 select time (now());
Datediff(d1,d2)两个日期相隔的天数
Adddate(d,n)起始日期后多少天的日期
Adddate(d,interval expr type)一个时期后加上一个时间段的日期
Select adddate(“2011-07-01”,interval,’12’year_month);
Subdate(d,n)一个日期减去多少天后的日期
6.数值函数
Abs(x)绝对值函数
Tloor(x)最大整数函数
Pi()圆周率函数
Rand()随机值函数
Truncate（x,y）保留位数函数
Round()最近整数函数,round(x,y)保留小数位数函数
Sqrt()平方根函数
7.字符串函数
Insert(s1,x,len,s2)将字符串s1中x位置开始长度为len的字符串用s2替换
Upper（x）、ucase(x)大写转换函数
Left(x,n)返回字符串中x开始的前n个字符
Rtrim(s)去除字符串结尾的空格
Substring(s,n,len)字符串截取函数
Reverse(s)字符串反序函数
Field（s,s1,s2,s3,s4,……）s在字符串中的位置
8.条件判断函数
Select id,name,row,case when row>=90 then ’优’ when row<90 and row>=70 then ‘良’ else ‘差’ end level from tb_row;
9.系统信息函数
Version()当前数据库版本
user（）、system_user()、session_user()、current_user()查看用户
Database()、schema()当前数据库名
10.其他函数
Password(str)、md5(str)加密函数
Fromat(x,n)格式化函数，将小数四舍五入保留n位小数

约束条件

null 允许为空，默认设置
Not null 不允许为空
Key 索引类型
Default 设置为默认值，缺省为null
Extra 额外
create table db2.t1(name char(3) not null default “”,level int default 300,sex enum(“m”,“w”) not null default “m”);

索引

索引：对记录集的多个字段进行排序的方法
索引优点：加快查询速度，创建唯一性
索引种类：Btree B+tree hash 默认的二叉树算法（Btree）
普通索引 index
一个表中可以有多个index字段，字段值可重复，可以赋null值，key标志是MUL。
建表时指定索引
mysql> create table t3(
-> name char(3),
-> age int,
-> class char(9),
-> index(name),
-> index(class));
在已有表中指定索引create index 索引名 on 表名（字段名）；
create index class on t3(class);
修改数据表结构添加索引
Alter table class add index 索引名 (class);
唯一索引 unique index
索引的值必须唯一，主键是特殊的唯一索引
在建表时指定索引
在已有表时创建索引
修改数据表结构添加索引
全文索引fulltext index
必须是char、varchar、text类型的字段，必须是myisam引擎
创建方式同上
空间索引spatial index
可以提高系统获取空间数据的效率，必须是myisam存储引擎，字段不能为空
创建方式同上
单列索引
为单个字段创建索引
多列索引
为多个字段创建索引
删除索引
删除指定表的索引drop index 索引名 on 表名；
drop index class on t3;
查看索引 show index from 表名；
show index from t3\G;

键值

主键 primary key
外键 foreign key
Primary key主键：
一个表中只能有一个primary key字段，字段值不允许重复，不允许赋null值，多个字段都作为primary key必须一起创建，作为复合主键。Key标志为PRI。经常把表中能够唯一标识记录的字段设置为主键字段【记录编号字段】
建表时指定主键
mysql> create table t4(stu_id char(9),
-> name char(10),
-> primary key(stu_id)
-> );
在已有表中设置primary key字段
Alter table 表名 add primary key(字段名)
Alter table t1 add primary key(name);
字段中有重复或null的值不能设置主键
移除表中的主键字段
Alter table 表名 drop primary key；
Alter table t1 drop primary key;
复合主键：
mysql> create table t3(
-> name char(10),
-> class char(7),
-> pay enum(“yes”,“no”),
-> primary key(name,class)
-> );
insert into t5 values(“bb”,“180909”,“yes”);
insert into t5 values(“bb”,“180909”,“no”);
insert into t5 values(“bb”,“180907”,“no”);
alter table t5 drop primary key;
alter table t5 add primary key(name,class);
Auto_increment:自增长
必须要先有主键才能建自增长，必须要先删自增长才能删主键
mysql> create table t6(
-> id int primary key auto_increment,
-> name char(10),
-> age int default 19,
-> sex enum(“m”,“w”) default “m”
-> );
insert into t5(name,age,sex) values(“bb”,“21”,“m”);
insert into t5(name,age,sex) values(“bb”,21,“m”);
insert into t6(name,age,sex) values(“bb”,“21”,“2”);
show create table t1;查看建表记录
添加自增属性：alter table test1.1 modify id int auto_increment;
外键：
让当前字段的值在另一个表中字段值的范围内选择。条件是表的存储引擎必须是 innodb,字段类型要一致，字段必须是索引类型的一种（primary key），父表的关联字段改变时子表会同步改变。
Foreign key(表A的字段名)references 表B（字段名）
On update cascade同步更新
On delete cascade同步删除
mysql>create table yginfo(yg_id int primary key auto_increment,name char(10))engine=innodb;指定引擎
mysql> desc yginfo;
mysql> insert into yginfo(name) values(“tom”),(“bob”);
mysql> select * from yginfo;
mysql> create table gztab(gz_id int,pay float(7,2),foreign key(gz_id) references yginfo(yg_id) on update cascade on delete cascade)engine=innodb;
mysql> desc gztab;
mysql> show create table gztab;查看表信息
mysql> insert into gztab values(1,20000);
mysql> insert into gztab values(2,20000);
mysql> select * from gztab;
mysql> insert into yginfo(name)values(“haxi”);
mysql> insert into gztab values(3,3999);
mysql> select * from gztab;
mysql> delete from yginfo where yg_id=2;
mysql> select * from gztab;
mysql> select * from yginfo;
mysql> update yginfo set yg_id=8 where name=“tom”;
mysql> select * from yginfo;
mysql> select * from gztab；
删除外键字段：
Alter table 表名 drop foreign key 约束名（约束名通过show create table 表名 来查看）；
show create table gztab;
alter table gztab drop foreign key gztab_ibfk_1;
在已有表的基础上添加外键：
create table gztab(gz_id int,pay float(7,2))engine=innodb;
mysql> alter table gztab add foreign key(gz_id) references yginfo(yg_id) on update cascade on delete cascade;
mysql> show create table gztab;
复制表：
将源表 xxx 复制为新表 yyy
CREATE TABLE yyy SELECT * FROM xxx;
将指定的查询结果复制为新表 zzz
CREATE TABLE zzz SQL查询语句;
mysql> create table user2 select * from user;
mysql> create table user3 select * from user order by uid desc limit 10;
mysql> create table user4 select * from user where 1=2;
mysql> create table user5 select name,uid,shell from user where 1=2;
复制源表 xxx 的结构到新表 vvv
CREATE TABLE vvv SELECT * FROM xxx WHERE FALSE;
复制表结构，包括Key
Create table vvv like ttt;
将源表 vvv 的名称改为 www
ALTER TABLE vvv RENAME TO www;
复制表内容：
Insert into student3_new select * from student3 where sex=”male”;

日志管理

Error log 错误日志 /var/log/mysql.log 用于排查错误
Bin log 二进制日志 用于增量备份
Relay log 中继日志 用于接收复制relication master
Slow log 慢日志 用于调优，查询时间超过指定值
binlog日志：记录所有更改数据的操作
Vim /etc/my.cnf
[mysqld]
Log-bin=取个日志名
Server-id=主机位数字就行
[root@dbsvr1 ~]# systemctl restart mysqld
手动生成新的日志文件:
1.重启mysql服务
2.mysql> flush logs;刷新日志
mysql> show master status;查看当前正在使用的日志文件
3.mysql -uroot -p密码 -e ‘flush logs’ 在数据库外执行命令
4.mysqldump --flush-logs 备份时生成新的日志文件
清理binlog日志：
1.删除指定的binlog 文件 purge master logs to “name.000001”;
2.删除所有binlog日志mysql> reset master;
3.[root@mysql1 dir]# rm -rf /dir/*
[root@mysql1 dir]# systemctl restart mysqld
查看日志文件内容：mysqlbinlog 日志文件
分析binlog日志：
查看日志当前记录格式：mysql> show variables like “binlog_format”;
三种记录格式 :1. statement:每一条修改数据的sql命令都会记录在binlog日志中。2. row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。3. mixed: 是以上两种格式的混合使用。
[root@mysql1 dir]# rm -rf /dir/*
[root@mysql1 dir]# vim /etc/my.cnf
[mysql]
Binlog_format=mixed
[root@mysql1 dir]# systemctl restart mysqld
mysql> insert into user(name,password) values(“xixi”,“i”);
mysql> insert into user(name,password) values(“xixi”,“i”);
mysql> delete from user where id=54;
mysql> delete from user where id=54;
mysql> delete from user where id=54;
[root@mysql1 dir]# mysqlbinlog /dir/name.000001 |grep -i insert
[root@mysql1 dir]# mysqlbinlog /dir/name.000001 |grep -i delete
Binlog恢复数据
日志文件区分记录多个sql命令的方式：偏移量（pos）时间点（time）
执行日志文件里记录的sql命令恢复数据：mysqlbinlog [选项] binlog日志文件 | mysql -uroot -p密码
–start-datetime=“yyyy-mm-dd hh:mm:ss”
–stop-datetime=“yyyy-mm-dd hh:mm:ss”
–start-position=数字
–stop-position=数字
mysql> drop table userdb.user;
[root@mysql1 dir]# mysql -uroot -p123456 userdb < /badir/user.sql
mysql> select * from userdb.user;
mysql> insert into userdb.user(name,uid,gid) values(“ha”,55,55);
mysql> insert into userdb.user(name,uid,gid) values(“ha”,55,55);
mysql> insert into userdb.user(name,uid,gid) values(“ha”,55,55);
mysql> delete from userdb.user where id=59;
[root@mysql1 dir]# mysqlbinlog --start-position=6558 --stop-position=7970 /dir/name.000001 | mysql -uroot -p123456
错误日志
Log-error=/var/log/mysqld.log //指定错误日志的位置
慢日志
Slow_query_log=1
Slow_query_log_file=/var/log/mysql-slow/slow.log
Slow_query_time=3 //超过三秒记录

数据库备份

备份：能够防止由于机械故障以及人为误操作带来的损失
冗余：数据有多份冗余，但不等于备份，只能防止机械故障带来的数据损失
备份方式：物理备份 冷备 cp tar
逻辑方式：mysqldump mysql
完全备份：备份所有的数据库
增量备份：上次备份后新产生的数据
差异备份：完全备份后新产生的数据
物理备份
直接复制数据库文件 适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的数据库版本
备份操作:
cp -rp /var/lib/mysql/数据库 备份目录/文件名
tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*
Flush tables 强制关闭正在使用的表，将所有数据写入到数据文件的文本文件里
恢复操作:
cp -rp 备份目录/文件名 /var/lib/mysql/
tar -zxvf xxx.tar.gz -C /var/lib/mysql/数据库名/
在50主机做物理备份
Mkdir /dbbak
Cp -rp /var/lib/mysql /dbbak
在51主机上使用物理备份文件 恢复数据库
Systemctl stop mysqld
Rm -rf /var/lib/mysql
Scp -r /dbbak/mysql root@192.168.4.51:/var/lib
Chown -R mysql:mysql /var/lib/mysql
Systemctl start mysqld
Systemctl enable mysqld
Mysql -hlocalhost -uroot -p123456
Mysql>Show databases;
使用快照卷备份数据
快照卷和原始卷必须为同一卷组，需要开binlog日志
优点：几乎是热备（创建快照前把表锁上，创建完成后立即释放）
支持所有的存储引擎
备份速度快
无需昂贵的商业软件（它是操作系统级别）
缺点：可能需要跨部门的协调（使用操作系统级别的命令，DBA一般没有权限）
无法预计服务停止时间
数据如果分布在多个卷上比较麻烦（针对存储级别而言）
操作流程：加全局读锁->创建逻辑卷->释放锁->拷贝数据->移除快照
如果数据与逻辑卷不在同一个卷组中，需要迁移数据

1. 准备文件系统
   Lvcreate -n lv-mysql -L 2G datavg
   Mkfs.xfs /dev/datavg/lv-mysql
2. 将数据迁移到逻辑卷
   Systemctl stop mysqld
   Mount /dev/datavh/lv-mysql /mnt
   Cp -a /var/lib/mysql/* /mnt
   Umount /mnt
   Vim /etc/fstab
   /dev/datavg/lv-mysql /var/lib/mysql xfs defaults 0 0
   Mount -a
   Chown -R mysql.mysql /var/lib/mysql
   Systemctl start mysqld
   3.加读锁、创逻快照、释放锁
   Echo “flush tables with read lock;system lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql;unlock tables;”|mysql -hlocalhost -uroot -p 123456
   4.从快照中备份
   Mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt
   Cd /mnt
   Tar -cf /backup/data +%F-mysql-all.tar ./*
3. 移除快照
   Umount /mnt
   Lvremove -f /dev/datavg/lv-mysql-snap
4. 恢复数据
   Tar -xf /backup/2016-12-07-mysql-all.tar -C /var/lib/mysql
   Systemctl start mysqld
5. 写脚本加入周期任务
   Vim /root/mysql_back.sh
   #!/bin/bash
   Back_dir=/backup/date +%F
   [ -d $back_dir ] ||mkdir -p $back_dir
   Echo “flush tables with read lock;system lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql;unlock tables;”|mysql -hlocalhost -uroot -p 123456
   Mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt
   Rsync -a /mnt/ $back_dir
   If [ $? -eq 0 ];then
   Umount /mnt
   Lvremove -f /dev/datavg/lv-mysql-snap
   Fi
   Crontable -e
   0 2 * * * /root/mysql_back.sh
   逻辑备份
   备份的是建库建表插入记录的SQL语句，只适用于中小型数据库，效率较低，保证数据的一致性和服务的可用性
   逻辑备份及恢复：
   备份操作：mysqldump -uroot -p密码 库名 >路径/xxx.sql
   库名表示方式
   –all-databases 或 -A 所有库
   数据库名 单个库
   数据库名 表名 单张表
   -B 数据库1 数据库2 多个库
   –single-transaction //保证innodb一致性和服务的可用性
   –event //备份时间调度器代码
   –routines //备份存储过程和存储函数
   –triggers //备份触发器
   –flush-logs //刷新日志，新产生日志文件
   –master-data=1|2 //记录binlog日志的位置与文件名并追加到文件中
   Mysqlhotcopy -uroot -p密码 选项 数据库1、数据库2…… backupdir/;
   (需要安装perl接口包)
   注意事项
   无论备份还是恢复,都要验证用户权限
   [root@mysql1 ~]# mkdir /badir
   [root@mysql1 ~]# mysqldump -uroot -p123456 -A >/badir/all.sql
   [root@mysql1 badir]# mysqldump -uroot -p123456 userdb >/badir/userdb.sql
   [root@mysql1badir]# mysqldump -uroot -p123456 userdb user >/badir/user.sql
   [root@mysql1badir]#mysqldump -uroot -p123456 -B userdb bd1 >/badir/two.sql
   恢复操作:mysql -uroot -p密码 库名 < 路径/xxx.sql
   恢复的库不存在，要先创建库，必须加库名
   当恢复的是多个库时可以不用写库名
   本机恢复：mysql> create database userdb;
   [root@mysql1 badir]# mysql -uroot -p123456 userdb </badir/user.sql
   mysql> select * from userdb.user;
   其他恢复：[root@mysql1badir]#scp /badir/userdb.sql root@192.168.4.51:/root
   [root@mysql2 ~]# mysql -uroot -p123456 userdb </root/userdb.sql
   mysql> select * from userdb.user
   完全备份缺点：执行的写锁
   恢复时最好关闭binlog日志，不然会产生一些无用的恢复数据的日志
   临时关闭binlog日志 set sql_log_bin=0
   Source 要恢复的文件
   数据库迁移
6. 迁移到MySQL版本相同的数据库
7. 迁移到MySQL版本不同的数据库
8. 迁移到不同的数据库服务器
   导入导出数据
   导出数据：select 语句 into outfile “目标文件” 选项
   #MySQLdump -u用户 -p密码 -T 目标文件 数据库名 表名 “选项”
   #MySQL -u用户 -p密码 -e “select语句” 库名 >目标文件
   #MySQL -u用户 -p密码 --xml|-X -e “select 语句” 数据库名 >目标文件.xml
   #MySQL -u用户 -p密码 --html|-H -e “select 语句” 数据库名 >目标文件.html
   导入数据：
   Load data infile “目标文件” into table 表名 选项
   #Mysqlimport --no-default -u用户 -p密码 -T 库名 目标文件 “选项”
   数据导入：把系统文件内容存储到数据库的表里
   数据导出：把数据库表里记录保存到系统文件里
   表的导入和导出的只备份表记录，不会备份表结构。因此需要通过mysqldump备份表结构，恢复时先恢复表结构，再恢复表记录
   数据导入导出数据时mysql服务检索文件的默认目录是
   /var/lib/mysql-files
   查看默认使用目录及目录是否存在mysql> show variables like “secure_file_priv”;
   修改目录及查看修改结果
   vim /etc/my.cnf
   [mysqld]
   secure_file_priv="/mydata"
   mkdir /mydata
   chown mysql /mydata
   systemctl restart mysqld
   mysql> show variables like “secure_file_priv”;
   数据导入:
   Load data infile “目录名/文件名” into table 表名 fields terminated by “分隔符” lines terminaed by “\n”;
   mysql> create database userdb;
   mysql> use userdb;
   mysql> create table user(name char(35),password char(1),uid int,gid int,comment char(150),homedir char(150),shell char(40));
   mysql> system cp /etc/passwd /mydata
   mysql> system ls /mydata
   mysql> load data infile “/mydata/passwd” into table userdb.user fields terminated by “:” lines terminated by “\n”;
   mysql> alter table userdb.user add id int primary key auto_increment first;
   mysql> select * from user;
   数据导出
   SQL查询 into outfile “目录名/文件名” fields terminated by “分隔符” lines terminated by “\n”;
   注意事项
   导出的内容由SQL查询语句决定
   导出的是表中的记录,不包括字段名
   禁用SElinux
   mysql> select * from userdb.user where id<100 limit 10 into outfile “/mydata/user5.txt”；
   mysql> system cat /mydata/user5.txt
   使用percona数据备份
   能够实现MySQL数据库的热备份，能够对innodb和xtradb存储引擎的数据库进行非阻塞地备份
   安装percona：
   恢复innodb存储引擎
   建新的库和表
   完全备份与完全恢复
   
   

完全备份：
[root@dbsvr1 ~]# innobackupex --user=root --password=123456
/backup（必须是不存在的目录） //完全备份
Ls /backup
完全恢复：
Systemctl stop mysql
Rm -rf /var/lib/mysql
Mkdir /var/lib/mysql 必须要是空目录
[root@dbsvr1 ~]# innobackupex --user=root --password=123456 -
-apply-log /backup //准备恢复数据
[root@dbsvr1 ~]# innobackupex --user=root --password=123456 --copy-back /backup //恢复数据，可以直接使用cp、rsync、scp等工具拷贝到数据库恢复
[root@dbsvr1 ~]# chown -R mysql:mysql /var/lib/mysql
[root@dbsvr1 ~]# systemctl restart mysqld
[root@dbsvr1 ~]# mysql -uroot -p123456
mysql> show databases;
通过binlog日志恢复实时数据
增量备份：
Lsn事物日志序列号
[root@mysql1~]#innobackupex --user=root --passowrd=123456 /fullbak
[root@mysql1 ~]# ls /fullbak/
mysql> insert into db5.a values(3232),(323),(232);
Innobackupex --user=root --password=123456 --incremental /new1dir --incremental-basedir=/fullbak
mysql> insert into db5.a values(3332),(4444),(5555);
Innobackupex --user=root --password=123456 --incremental /new2dir --incremental-basedir=/new1dir
准备恢复数据
systemctl stop mysqld.service
rm -rf /var/lib/mysql
mkdir /var/lib/mysql
Innobackupex --apply-log --redo-only /fullbak/
合并日志
innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new1dir
innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new2dir
Rm -rf /new1dir
Rm -rf /new2dir
拷贝备份数据到数据库目录下,可以使用cp、scp、rsync拷贝
innobackupex --copy-back /fullbak/
修改数据库目录文件的所有者属组用户为mysql
chown -R mysql:mysql /var/lib/mysql
启动数据库服务
systemctl restart mysqld
查看数据
mysql> select * from db5.a;
继续备份
[root@mysql1 ~]# innobackupex --user=root --password=123456 --incremental /new3dir --incremental-basedir=/fullbak --no-timestamp
差异备份：
[root@mysql1~]#innobackupex --user=root --passowrd=123456 /fullbak
[root@mysql1 ~]# ls /fullbak/
mysql> insert into db5.a values(3232),(323),(232);
Innobackupex --user=root --password=123456 --incremental /new1dir --incremental-basedir=/fullbak
mysql> insert into db5.a values(3332),(4444),(5555);
Innobackupex --user=root --password=123456 --incremental /new2dir --incremental-basedir=/fullbak
在完全备份文件中恢复单个表：
mysql> create database db10;
mysql> create table db10.a(id int);
mysql> use db10;
mysql> insert into a values(3);
mysql> insert into a values(3);
mysql> insert into a values(3);
mysql> select * from a;
innobackupex --user=root --password=123456 --databases=“db10” /db10
mysql> drop table a;
innobackupex --user=root --password=123456 --databases=“db10” --apply-log --export /db10
mysql> create table a(id int); //恢复表必须要与原表字段相同
mysql> alter table db10.a discard tablespace;
cp /db10/db10/a.{cfg,exp,ibd} /var/lib/mysql/db10/
chown mysql:mysql /var/lib/mysql/db10/a.*
mysql> alter table a import tablespace;
mysql> select * from a;
cd /var/lib/mysql/db10/
rm -rf a.{cfg,exp}

视图

作用：1.安全，让一些人看到一些字段，一些人看另外一些字段2.便于查询
在数据库服务器中，如果只有xxx.frm结构的是视图，表的存储引擎是memory
相当于一个虚拟的表，原表改变视图跟着改变，视图改变原表也会跟着改变。不会占用空间
创建视图：
Create view 视图名称 as SQL查询;
mysql> create view v1 as select name,uid,gid from db9.user;
create view 视图名称(字段名列表) as SQL查询;
mysql> create view v2(user,u_id,g_id) as select name,uid,gid from db9.user;
查看视图：
Desc 视图名；
show table status like “视图名”;
show table status where comment=“view”\G;
查看视图表对应的基表：
show create view 视图名;
mysql> show create view v1;
使用视图：
查询记录
Select 字段名列表 from 视图名 where 条件;
插入记录
Insert into 视图名(字段名列表) values(字段值列表);
更新记录
Update 视图名 set 字段名=值 where 条件;
删除记录
Delete from 视图名 where 条件;
删除视图：
drop view 视图名;
mysql> drop view v1;
设置字段别名：
当表中有重复的字段名时使用
create view 视图名as select 表别名.源字段名 as 字段别名
from 源表名 表别名 left join 源表名 表别名on 条件;
mysql>create view v2 as select a.name as aname , b.name as bname , a.uid as auid , b.uid as buid from user a left join info b on a.uid=b.uid;
mysql>create view v4 as select t1.name as a,t1.uid as b,t1.shell as c,t2.* from t1,t2 where t1.name=“root” and t2.name=“root”;
mysql>create view v5(a,b,c,d,e,f,g,h) as select * from t1,t2 where t1.name=“root” and t2.name=“root”;
覆盖创建视图：
创建时,若视图已存在,会替换已有的视图
Create or replace view 视图名 as select 查询;
mysql> create or replace view v2 as select * from t1;
重要选项说明：
ALGORITHM = {UNDEFINED | MERAGE |TEMPTABLE}
MERAGE,替换方式（系统和用户的命令一起执行）;TEMPTABLE,具体化方式(系统先执行一次，再执行用户的命令)– UNDEFINED,未定义
Create algorithm=merge view v2 as select * from goods where shop_price>300;
WITH CHECK OPTION对视图做操作时受限制
CASCADED同时满足基表和视图的限制（默认）
LOCAL必须满足视图本身的限制
mysql> create table user2 select name,uid,gid,shell from user where uid>=5 and uid<=100;
Create view v11 as select * from user2 where uid <=50 with local check option;
mysql> select * from v11;
mysql> update v11 set uid=51 where name=“sync”;
mysql> update v11 set uid=50 where name=“sync”;
mysql> select * from v11 where name=“sync”;
mysql> select * from user2 where name=“sync”;
没有用户了，因为uid=51超出了视图表的限制了
mysql> create view v12 as select * from user2 where uid>=30 and uid<=80;
mysql> create view v13 as select * from v12 where uid>=50 with check option;
mysql> update v13 set uid=60 where name=“sync”;
mysql> update v13 set uid=81 where name=“sync”;
mysql> update v13 set uid=49 where name=“sync”;

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，函数有返回值，存储过程没有。并发量少的情况下很少使用存储过程，并发量高的情况下可以提高效率
基本使用：
创建存储过程：delimiter //
create procedure 名称（） begin 功能代码 end //
delimiter ；
delimiter关键字用来指定存储过程的分隔符(默认为;)
若没有指定分割符,编译器会把存储过程当成SQL语句进行处理,从而执行出错,把//改成了分隔符
None:不需要传参 IN：输入参数 OUT：输出参数 INOUT：输入输出参数
mysql> delimiter //
mysql> create procedure db9.p1()
-> begin
-> select count() from db9.user;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call db9.p1(); //调用存储过程
查看存储过程：
Show create procedure 过程名；
mysql> show procedure status;
mysql> select db,name,type,body from mysql.proc where name=“存储过程名”;
mysql> select db,name,type,body from mysql.proc where name=“p1”;
mysql> select db,name,type,body from mysql.proc where name=“p1”;
调用存储过程：call 存储过程名（）；mysql> call t11();
删除存储过程：drop procedure 存储过程名；
mysql> drop procedure t11;
会话变量：会话变量和全局变量叫系统变量 使用set命令定义;
mysql> show session variables;//查看会话变量
mysql> set session sort_buffer_size = 40000;//设置会话变量
mysql> show session variables like “sort_buffer_size”; //查看会话变量
全局变量：全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话。select @@hostname;
mysql> show global variables;//查看全局变量
mysql> show global variables like “%关键字%”; //查看全局变量
局部变量：只在某个存储过程中的begin/end块中有效。其有效范围仅限于该语
句块中,语句块执行完毕后,变量失效。declare专门用来定义局部变量。调用局部变量时,变量名前不需要加@
mysql> delimiter //
mysql> create procedure db9.p2()
-> begin
-> declare j int default 9;
-> declare k char(3);
-> set k=“bob”;
-> select j,k;
-> end
-> //
mysql> delimiter ;
光标的使用
在存储过程中查询可能返回多条记录，如果数据量非常巨大，则需要光标逐一读取结果集中的记录，是一种用于轻松处理多条数据的机制,用到存储过程中
声明光标：declare 光标名 cursor for select语句
Declare info_of_student cursor for select sid,name,age,sex from studentinto
Where sid=1;
打开光标：open 光标名
Open info_of_student
使用光标：fetch 光标名 into 存放光标数据的参数名，必须事先定义好
Fetch info_of_student into tmp_name,tmp_tel
关闭光标：close 光标名
Close info_of_student
参数类型：
In 输入参数 给存储过程传递数据
mysql> create procedure db9.p4(in x char(10)) begin select name from db9.user where name=x; end//
mysql> delimiter ;
mysql> call db9.p4(“sync”);
mysql> set @z=root;
mysql> call db9.p4(@z);
Out 输出参数 接受从存储过程输出的数据
mysql> delimiter //
mysql> create procedure db9.p5(in shellname char(30),out num int)
-> begin
-> select count(name) into num from db9.user where shell=shellname;
-> select num;
-> end
-> //
mysql> delimiter ;
mysql> call db9.p5("/bin/bash",@);
mysql> call db9.p5("/sbin/nologin",@);
Inout 输入/输出参数 既可以输入又可以输出
mysql> create procedure db9.p6(inout x char(30)) begin select name,shell from db9.user where shell=x; select count() into x from db9.user; select x; end//
mysql> delimiter ;
mysql> set @name="/sbin/nologin";需要定义一个变量调用
mysql> call db9.p6(@name);
算数运算：

mysql> delimiter //
mysql> create procedure db9.p3()
-> begin
-> declare x int;
-> declare y int;
-> declare z int;
-> select count(shell) into x from db9.user where shell="/bin/bash";
-> select count(shell) into y from db9.user where shell="/sbin/nologin";
-> set z=x+y;
-> select x,y,z;
-> end
-> //
mysql> delimiter ;
mysql> call db9.p3();
流程控制
if语句
If 条件 then
……
Else if 条件 then
……
Else
……
End if
Delimiter //
Create procedure example_if(in x int)
Begin
If x=1 then
Select 1;
Else if x=2 then
Select 2;
Else
Select 3;
End if
End //
Delimiter ;
Call example_if(1)
case语句
Case （条件）
When 条件 then ……；
When 条件 then ……；
……
End case
while语句
While 条件 do
……
End while
loop语句
Loop
……
End loop
repeat语句
Repeat
……
Until 条件
End repeat
Iterate label 重新开始一个循环
选择结构：if 条件判断 then 代码 end if；if 条件判断 then 代码1 else 代码2 end if；
mysql> create procedure db9.p21() begin if 1<=2 then select user();end if;select * from db9.user where id=1; end // delimiter ;
mysql> call db9.p21();
mysql> delimiter //
mysql> create procedure db9.p22(in line int)
-> begin
-> if line <10 then
-> select * from db9.user where id<=line;
-> else
-> select * from db9.user where id>=line;
-> end if;
-> end
-> //
mysql> delimiter ;
mysql> call db9.p22(11);
循环结构：while 条件判断 do 循环体 end while；
mysql>Delimiter // create procedure db9.p23() begin declare x int default 1;declare y int default 1; while x <=6 do select y;set x=x+1; end while;end // delimiter ;
Loop 循环体 end loop；
mysql> create procedure db9.p24() begin declare y int default 1; loop select y; end loop; end// delimiter ;
Repeat 循环体 until 判断条件 end repeat；
mysql> create procedure db9.26() begin declare y int default 1; repeat select y; set y =y+1; until y=7 end repeat; end//
mysql> delimiter ;mysql> call db9.26();
Delimiter //
Create procedure db9.28()
Begin
Declare z int default 0;
Declare j int;
Declare y int default 1;
Declare x int;
Select count(*) into x from db9.user;
While y <=x do
Select uid into j from db9.user where id=y;
If j % 2=0 then
Select name,uid from db9.user where id=y;
Set z=z+1;
End if;
Set y = y+1;
End while;
Select z;
End //
Delimiter ;
Call db9.28();

触发器

触发器：相当于一个特殊存储过程，但满足一个条件时就会被触发执行
语法结构：
Create trigger 触发器名称 after|before 触发条件 on 表名 for each row;
Begin
触发器程序体
End
<触发器名称> //和库表命名相同
Before|after //触发器时机
Insert|update|delete //触发条件
On 表名 //标识建立触发器的表名，即在哪张表上建立触发器
For each row //触发器的执行间隔，即每隔一行执行一次，而不是对整个表执行一次
触发器程序体 //要触发的SQL语句，可顺序，判断，循环等
Delimiter //
create trigger student_insert_trigger after insert on student for each row begin update stu_total set total=total+1;end //
create trigger student_delete_trigger after delete on student for each row begin update stu_total set total=total-1;end //
创建具有多条件语句的触发器：
delimiter //
create trigger student_update_trigger after update on student for each row begin update student_1 set name=new.name where id=old.id;end //
删除和更新必须要使用主键为条件，否则造成的结果是灾难性的
Create trigger tab11_after_update_trigger after update on tab1 for each row begin update tab111 set id=new,id,name=new.name,sex=new.sex,age=new.age where id=old.id;end //
Create trigger t1_insert_trigger after insert on t1 for each row begin update t2 set total_num=total_num+1,total_salary=total_salary+new.salary;end //
查看触发器:
Show triggers;
Select * from information_schema.triggers;
使用触发器：执行触发事件
删除触发器：drop trigger 触发器名；

存储函数

语法结构：
Create function 函数名（参数 参数类型）returns 返回值类型
Begin 有效的函数体 end //
调用：select 函数名（实参列表）
set global log_bin_trust_function_creators=TRUE;需要开启，才能创建function
Create function fun1(str char(20)) returns char(50) begin return concat(“”,str,”!”); end //
Create function name_from_employee(x int) returns varchar(50) begin return (select emp_name from employee where emp_id=x); end //

Mysql存储引擎

带的功能程序，处理表的处理器，不同的存储引擎有不同的功能和数据存储方式。
管理工具 连接池 SQL接口 分析器 优化器 查询缓冲（空间由系统的物理内存获取用来存储查找过的数据） 存储引擎 文件系统（硬盘）
查看可支持的存储引擎类型：show engines\G;show variables like “have%”;
修改默认存储引擎：default-storage-engine=存储引擎名称
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
Myisam存储引擎：
主要特点：支持表级锁（给整张表加锁，当一张表处理完成后才能访问），插入数据快，
不支持事物、事物回滚、外键
相关的表文件
– 表名.frm（表结构）
– 表名.MYI（索引信息）
– 表名.MYD（表数据）
Memory存储引擎：表存储在内存中，主要用于对查询速度要求很高，对安全要求不高的数据库。
InnoDB存储引擎：
主要特点：支持行级锁定（只给被访问的行加锁，当前行处理完成后才能被访问）支持事物（从连接数据库到断开连接的这一个过程）、事物回滚（在事物执行过程中，任何一步操作没有正确，回复之前所有的操作）、外键，插入速度慢
事物日志文件：记录对innodb存储引擎的表执行的操作
相关的表文件：
表名.frm(表结构)
表名.ibd（索引信息和数据）
查询操作多的表使用myisam 存储引擎，节省资源
写操作多的表使用innodb存储引擎，并发访问量大
锁机制：
主要管理共享资源的并发访问，用于实现事务的隔离性，是针对存储引擎的
锁的开销越大，颗粒越小，并发度越高
表级锁：是在服务器层实现的
行级锁：是在存储引擎层实现的
页级锁:对整个页面(MySQL管理数据的基本存储单位)进行加锁
读锁(共享锁):支持并发读select
写锁(互斥锁、排它锁):是独占锁,上锁期间其他线程不能读表或写表insert update delete
查看当前的锁状态：show status like “%lock%”;
加表级锁：lock table table_name write;
释放表级锁：unlock tables;
阻塞：由于资源不足引起的排队等待现象
死锁：由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放

事物

事务特性(ACID)：
Atomic :原子性
事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败。
Consistency : 一致性
事务操作的前后,表中的记录的变化逻辑上成立，一个加了500另一个则减去500。
Isolation :隔离性
事务操作未结束时，每一步的操作对其他会话不产生影响。
Durability :持久性
事务完成后，所产生的结果不能撤销
Show variables like “%auto%”; 查看提交状态
Set autocommit=off;
创建事物：开启事务->事务的步骤->提交事务
启动使用事物：start transaction;
提交事物：commit；
撤销事物：rollback；
事物周期：从初始化事物到事物提交的一个过程

事件

可以作为定时任务调度器，取代部分操作系统的计划任务才能完成的工作，事件调试器是定时触发执行的，是某一段时间执行一些语句。
查看事件是否开启：show variables like “event_scheduler”
Select @@event_scheduler
Show processlist
开启事件：set globle event_scheduler=on
在My.ini或my.cnf中
Event_scheduler=on
创建事件：create event if not exists e_test on schedule every 5 second on completion preserve do insert into tb_eventtest(user,creattime) values(“root”,now())
修改事件：alter event e_test on schedule every 30 second on completion preserve do insert into tb_eventtest(user,createtime) values(“root”,now())
暂停事件：alter event e_test disable
启动事件：alter event e_test en
删除事件：drop event e_test

Mysql管理工具

phpMyAdmin：
基本思路
1.安装httpd、mysql、php-mysql及相关包
yum -y install httpd php php-mysql
2.启动httpd服务程序
systemctl start httpd ; systemctl enabled httpd
3.解压phpMyAdmin包,部署到网站目录
tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
cd /var/www/html/
mv phpMyAdmin-2.11.11-all-languages/ phpmyadmin
chown -R apache:apache phpmyadmin/
cp phpmyadmin/config.sample.inc.php phpmyadmin/config.inc.php
4.配置config.inc.php,指定MySQL主cd /var/www/html/机地址
Vim /var/www/html/phpmyadmin/config.inc.php
17 $cfg[‘blowfish_secret’] = ‘a8b7c6d’; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
31 $cfg{[}^{\prime }Server{s}^{\prime }][$i][‘host’] = ‘localhost’;
5.创建授权用户
在数据库中授权用户
6. 浏览器访问、登录使用
http://192.168.4.4/phpmyadmin

mysql主从同步

主从同步
Mysql 主-从
确保主从数据库服务器的数据一致，避免机械故障造成数据损失，不能避免误删除带来的损失，包括需要同步的所有库，可以离线备份主服务的库
配置主服务器启用binlog日志
主从复制原理：

1. 主库将执行的DDL DML DCL语言记录在binlog日志中
2. 从库的IO线程将主库的binlog日志复制到自己的中继日志中
3. 从库的SQL线程将中继日志中事件重放到数据库中
   Mysql>show master status;
   Vim /etc/my.cnf
   Server_id=51 用户值
   Log_bin=master51 日志名避免
   Binlog_format=”mixed” 日志格式
   [root@mysql2 ~]# systemctl restart mysqld
   mysql>grant replication slave on . to master51@"%" identified by “123qqq…A”;授权用户
   mysql>show master status;查看日志状态
   配置从服务器
   Mysql>Show slave status;显示是否是从库
   [root@mysql3 ~]# vim /etc/my.cnf
   Server_id=52
   [root@mysql3 ~]# systemctl restart mysqld
   mysql> change master to
   -> master_host=“192.168.4.51”,主服务器IP地址
   -> master_user=“master51”,被授权用户的用户名
   -> master_password=“123qqq…A”,授权密码
   -> master_log_file=“master51.000002”,当前的binlog日志
   -> master_log_pos=441;偏移量，mysql> show master status;查出偏移量
   mysql> start slave;启动slave进程
   mysql> show slave status\G;查看状态信息
   Slave_IO_Running: Yes 必须都连接成功
   Slave_SQL_Running: Yes
   排错：先停slave进程，再改错误信息报uuid错误：改 vim /var/lib/mysql/auto.cnf的值
   测试配置
   主服务器51：mysql> create database db6;
   mysql> create table db6.a(id int);
   mysql> show master status;
   mysql> grant insert,update,select on db6.* to webuser@"%" identified by “123qqq…A”;
   客户50：mysql> insert into db6.a values(33);
   mysql> select * from db6.a;
   主从服务器的数据可以同步更新
   如果原来有数据需要备份出来，加上–master-date=1,进入从库使用source导入库，就不需要写master-log-file和master-log-pos
   恢复独立库
   删除以下文件：
   master.info 主库信息
   relay-log.info 中继日志信息
   主机名-relay-bin.xxxxxx 中继日志
   主机名-relay-bin.index 索引文件
   主库配置选项：（主服务器）
   binlog_do_db=name 设置Master对哪些库记日志
   binlog_ignore_db=name 设置Master对哪些库不记日志
   从库配置选项：
   log_slave_updates记录从库更新,允许链式复制(A-B-C)
   relay_log=dbsvr2-relay-bin 指定中继日志文件名
   replicate_do_db=mysql仅复制指定库,其他库将被忽略,此选项可设置多条(省略时复制所有库)（日志格式只能是ROW）
   replicate_ignore_db=test不复制哪些库,其他库将被忽略,（日志格式只能是ROW）ignore-db与do-db只需选用其中一种
   以GTID的方式
   主库
4. log-bin server-id=数字 gtid_mode=on enforce_gtid_consistency=1
5. Grant replication
6. 备份数据库
   从库
7. server-id=数字 gtid_mode=on enforce_gtid_consistency=1 (可选参数master-info-repository=table relay-log-info-repository=table)
8. 导入数据
9. Change master to master_host=”master1”,master_user=”授权用户”,mster_password=”授权密码”，master_auto_position=1;
10. Start slave;
11. Show slaves status\G;
    Mysql -主多从
    配置主服务器（启用binlog日志，用户授权，重启服务，查看日志状态）
    配置第一台从服务器
    像一主一从的从服务器那样配
    配置第二台从服务器
    像一主一从的从服务器那样配
    Mysql 主-从-从
    配置第一个主服务器
    像一主一从的主服务器那样配
    配置第一个从服务器
    [root@mysql5 mysql]# vim /etc/my.cnf
    [mysqld]
    server_id=id 号
    log-bin=日志名
    Binlog_format=‘mixed’
    log_slave_updates
    systemctl restart mysqld
    mysql> grant replication slave on . to 用户名@‘第2台从库的IP地址’ identified by ‘密码’;
    mysql> CHANGE MASTER TO MASTER_HOST=‘主库IP地址’,
    -> MASTER_USER=‘用户名’,
    -> MASTER_PASSWORD=‘密码’,
    -> MASTER_LOG_FILE=‘binlog日志文件名’,
    -> MASTER_LOG_POS=偏移量;
    mysql> start slave;
    mysql> show slave status\G;
    配置第二个从服务器
    把第一个从服务器作为自己主服务器配置

读写分离

构建读写分离：

在代理服务器上安装rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
创建主从结构master和slave
修改代理服务器配置 /etc/maxscale.cnf
9 [maxscale]
10 threads=auto线程数，根据CPU自动添加
18 [server1] //定义数据库服务器主机名
19 type=server
20 address=192.168.4.53 //master主机ip地址
21 port=3306
22 protocol=MySQLBackend //后端数据库
23
24 [server2] //定义slave数据库服务器
25 type=server
26 address=192.168.4.54 //slave主机ip地址
27 port=3306
protocol=MySQLBackend
28 [MySQL Monitor] //定义监控的数据库服务器
36 type=monitor
37 module=mysqlmon
38 servers=server1,server2 //监控的数据库列表，不能写ip
39 user=scalemon //监视数据库服务器时连接的用户名scalemon
40 passwd=111111
41 monitor_interval=10000 //监视的频率 单位为秒
52 #[Read-Only Service] //不定义只读服务器
53 #type=service
54 #router=readconnroute
55 #servers=server1
56 #user=myuser
57 #passwd=mypwd
58 #router_options=slave
63[Read-Write Service]//定义读写分离的数据库节点
64 type=service
65 router=readwritesplit
66 servers=server1,server2
67 user=maxscaled 定义路由用户
68 passwd=111111 定义路由用户密码
69 max_slave_connections=100%
75 [MaxAdmin Service] 定义管理服务，不改
76 type=service
77 router=cli
85 #[Read-Only Listener]//不定义只读服务使用的端口号
86 #type=listener
87 #service=Read-Only Service
88 #protocol=MySQLClient
89 #port=4008
91[Read-Write Listener]//定义读写服务使用的端口号
92 type=listener
93 service=Read-Write Service
94 protocol=MySQLClient
95 port=4006
97 [MaxAdmin Listener] //管理服务使用的端口号
98 type=listener
99 service=MaxAdmin Service
100 protocol=maxscaled
101 socket=default
102 port=4016
在主、从数据库服务器创建授权用户（在主库上授权）
mysql> grant replication slave, replication client on . to scalemon@’%’ identified by ‘111111’; //创建监控用户
mysql> grant select on mysql.* to maxscaled@‘%’ identified by ‘111111’; //创建路由用户
mysql> grant all on . to student@’%’ identified by ‘111111’; //创建访问数据用户
启动服务
[root@mysql7 ~]# maxscale -f /etc/maxscale.cnf
[root@mysql7 ~]# ss -nutpl |grep maxscale
#pkill -9 maxscale 没启动就杀死后检查
测试配置
在maxscale本机连接管理端口：
maxadmin -uadmin -pmariadb -P自己设置的管理端口
MaxScale> list servers
在客户端访问maxscale代理
mysql -h服务器地址 -P端口 -u用户名 -p密码
Mysql -h 192.168.4.56 -ustudent -p123456 -P4006
在从库插入数据后，客户端能够查到

配置多实例

解压软件、修改目录名、调整PATH变量
tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
Mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
Ls /usr/local/mysql
[root@mysql1 mysql]# mv /etc/my.cnf /root
有独立的Sock文件 服务日志文件 存放pid号文件 端口号 数据库目录
配置文件
Vim /etc/my.cnf
[mysqld_multi] //启用多实例
mysqld=/usr/local/mysql/bin/mysqld_safe //指定进程文件路径
mysqladmin=/usr/local/mysql/bin/mysqladmin //指定管理命令路径
user=root //指定进程用户
[mysqld1] //实例进程名称 ,X表示实例编号,如 [mysqld2]
datadir=/data1 //数据库目录 ,要手动创建
port=3307 //端口号
log-error=/data1/mysqld3307.err //错误日志位置
socket=/data1/mysqld3307.sock //指定sock文件的路径和名称
pid-file=/data1/mysqld3307.pid //进程pid号文件位置
[myslqd2]
datadir=/data2
port=3308
log-error=/data2/mysqld3308.err
socked=/data2/mysqld3308.sock
pid-file=/data2/mysqld3308.pid
[root@mysql1 mysql]# mkdir /data1
[root@mysql1 mysql]# mkdir /data2
启动实例进程

mysqld_multi start 实例编号 //启动实例进程

[root@A-rhel7-1809bin]#/usr/local/mysql/bin/mysqld_multi start 1
[root@mysql1 mysql]# ls /data1
[root@mysql1 mysql]# ss -anptul |grep 3307
本机连接
[root@localhost bin]# ./mysql -uroot –p初始密码 -S sock文件
mysql> alter user user() identified by ‘新密码’;
[root@localhost bin]# ./mysql -uroot -p新密码 -S sock文件
[root@mysql1 mysql]# /usr/local/mysql/bin/mysql -uroot -p’pqyfz9u5dZ:a’ -S /data1/mysqld3307.sock
mysql> alter user user() identified by “123456”;
mysql> show databases;
mysql> create database db1;
mysql> create table db1.a(id int);
mysql> insert into db1.a values(33)
mysql> exit
[root@localhost bin]# ./mysqld_multi --user=root --password=密码 stop 实例编号 //停止实例进程
[root@mysql1 mysql]#/usr/local/mysql/bin/mysqld_multi --user=root --password=123456 stop 1
以同样的方式启动第二个实例

集群(高可用)

MHA集群
MHA Manager(管理节点)MHA Node(数据节点)

环境准备
还原51-55的所有库——>在51-56上解压mha-soft-student.zip， unzip mha-soft-student.zip，安装mha-soft-student中的perl-*依赖包——>在51-56上安装所有perl-依赖包——>配置51-55互相能够ssh无密码认证登陆ls ~/.ssh/id_ ssh-key-gen Ssh-copy-id root@192.168.4.x ——>管理主机56 ssh免密登陆所有主机51-55——>在数据库服务器51-56上安装 mha-node cd mysql/soft/mysql/mha-soft-student/ rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm——>在管理主机56上安装mha-manager [root@56 mha4mysql-manager-0.56]# tar -zxf mha4mysql-manager-0.56.tar.gz cd /root/mysql/soft/mysql/mha-soft-student/mha4mysql-manager-0.56 perl Makefile.pl
change master to master_host=“192.168.4.52”,master_user=“repluser”,master_password=“123qqq…A”,master_log_file=“master52.000001”,master_log_pos=703;
make&&make install
配置master51主节点
vim /etc/my.cnf
[mysqld]
plugin-load =
“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1 (半同步复制)
server_id=51
log-bin=master51 （开启binlog日志）
binlog-format=“mixed”
relay_log_purge=off 不自动删除本机的中继日志文件
systemctl restart mysqld
mysql> grant replication slave on . to repluser@"%"
identified by “123qqq…A”;
mysql> show master status;
配置备用1主节点52
#vim /etc/my.cnf
[mysqld]
plugin-load =
“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
server_id=52
log-bin=master52
binlog-format=“mixed”
relay_log_purge=off
systemctl restart mysqld
mysql> change master to
-> master_host=“192.168.4.51”,
-> master_user=“repluser”,
-> master_password=“1234546”,
-> master_log_file=“master51.000001”,
-> master_log_pos=441;
mysql> start slave;
mysql> show slave status\G;
配置备用2主节点53
vim /etc/my.cnf
[mysqld]
plugin-load =
“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
server_id=53
log-bin=master53
binlog-format=“mixed”
relay_log_purge=off
Systemctl restart mysqld
mysql> change master to
-> master_host=“192.168.4.51”,
-> master_user=“repluser”,
-> master_password=“123456”,
-> master_log_file=“master51.000001”,
-> master_log_pos=441;
mysql> start slave;
mysql> show slave status\G;
配置从服务器slave54
#vim /etc/my.cnf
[mysqld]
server_id=54
plugin-load=“rpl_semi_sync_slave=semisync_slave.so”
rpl-semi-sync-slave-enabled = 1
relay_log_purge=off
Systemctl restart mysqld
mysql> change master to
-> master_host=“192.168.4.51”,
-> master_user=“repluser”,
-> master_password=“123456”,
-> master_log_file=“master51.000001”,
-> master_log_pos=441;
mysql> start slave;
mysql> show slave status\G;
配置从服务器slave55
与slave54相同
在52和53上都授权mysql> grant replication slave on . to repluser@”%” identified by “123qqq…A”;
mysql> show grants for repluser@"%";
配置管理主机56
mkdir /etc/mha
[root@56 conf]# cp /root/mysql/soft/mysql/mha-soft-student/mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha/
[root@56 conf]# ls /etc/mha/
在主机51上授权grant all on . to root@"%" identified by “123qqq…A”;
Cp /root/mysql/soft/mysql/mha-soft-student/master_ip_failover /etc/mha/
chmod 755 /etc/mha/master_ip_failover
vim /etc/mha/master_ip_failover
35 my $vip = ‘192.168.4.100/24’; # Virtual IP
Vim /etc/mha/app1.cnf
[server default]
manager_workdir=/etc/mha //管理工作目录
manager_log=/etc/mha/manager.log //管理日志
master_ip_failover_script=/etc/mha/master_ip_failover
//自动failover的切换脚本
ssh_user=root
ssh_port=22 //免密登陆信息
repl_user=repluser //主从同步用户名
repl_password=123qqq…A //主从同步密码
user=root //数据库用户名
password=123qqq…A //密码
[server1]
hostname=192.168.4.51
candidate_master=1 //表示要竞选主库
[server2]
hostname=192.168.4.52
candidate_master=1
[server3]
hostname=192.168.4.53
candidate_master=1
[server4]
hostname=192.168.4.54
no_master=1 //表示不竞选主库
[server5]
hostname=192.168.4.55
no_master=1 //表示不竞选主库
在51中配置临时ip ifconfig eth0:1 192.168.4.100/24
ifconfig eth0:1
在56上测试
测试ssh密钥对认证登陆
masterha_check_ssh --conf=/etc/mha/app1.cnf
测试主从同步状态
masterha_check_repl --conf=/etc/mha/app1.cnf
启动MHA_Manager
masterha_check_status --conf=/etc/mha/app1.cnf 查看状态
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf（删除宕机主库配置） --ignore_last_failover（忽略最后故障切换）
在主库51上添加访问数据库时使用的连接用户
Create database db8;
Create table db.a(id int);
mysql> grant select,insert on db8.a to admin2@"%" identified by “123qqq…A”;
在客户端50主机连接vip地址 访问集群
[root@50 ~]# mysql -h192.168.4.100 -uadmin2 -p123qqq…A
mysql> insert into db8.a values(999);
mysql> select * from db8.a;
停止51主机的数据库服务
systemctl stop mysqld.service
在2个备用的主库52和53 查看谁获取到了vip地址192.168.4.100
Ifconfig eth0:1
在50上开新终端连接数据库服务 依然连接vip地址192.168.4.100 并且可以正常查看和存储数据
查看管理主机56的app1.cnf文件 主库51的配置change master to master_host=“192.168.4.52”,master_user=“repluser”,master_password=“123qqq…A”,master_log_file=“master52.000001”,master_log_pos=703;会自动消失
Vim /etc/mha/app1.cnf
维护mha集群
把坏掉的数据库服务器51在添加到集群里
启动51主机的数据库服务器，并配置为当前主库的从数据库
mysql> change master to master_host=“192.168.4.52”,master_user=“repluser”,master_password=“123qqq…A”,master_log_file=“master52.000002”,master_log_pos=709;
mysql> show slave status\G;
启动管理主机56的管理服务，并查看状态信息
[root@56 mha-soft-student]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover
[root@56 ~]# masterha_check_status --conf=/etc/mha//app1.cnf
galera集群

这个集群每个结点都可以读写数据，同步数据
部署
在galera官网下载打了wsrep补丁的MySQL,实际上就是mysql.在每个结点安装galera和mysql-wsrep
Systemctl start mysqld systemctl enable mysqld
在所有结点授权同一个用户
Grant all on . to sst@”192.168.122.%” identified by “123456”;
Flush privileges;
在所有结点配置
Vim /etc/my.cnf
可选配置
Server-id=1
Binlog_format=row
Default_storage_engine=Innodb
Innodb_file_per_table=1 //独立的表空间
Innodb_autoinc_lock_mode=2
集群配置
Wsrep_on=ON //打开集群的功能
Wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so //指定集群同步数据使用的库文件
Wsrep_cluster_name=”galera” //取一个集群名
Wsrep_cluster_address=”gcomm://” //加入集群的地址，后面添加IP1，IP2，……
Wsrep_node_name=”galera1” //当前结点的名字
Wsrep_node_address=”192.168.122.10” //当前结点的IP
Wsrep_sst_auth=用户名：密码 //授权用户的名字和密码
Wsrep_sst_method=rsync //同步数据的的方式
Systemctl restart mysqld ss -anptul|egrep “3306|4567”
Show status like “wsrep%” 可以看到结点数、结点IP和状态
测试
在所有结点上都能够同步数据，他们的数据是保存在自己的磁盘上；主结点宕掉后需要手动添加到集群中，其他集群宕掉后自动添加到集群中

Mysql+lnmp

搭lnmp环境
配置php支持mysql
Yum -y install php-mysql
Php -m |grep -i mysql
测试
[root@51 mongo-1.6.16]# php -m |grep mysql

[root@51 html]# vim /usr/local/nginx/html/reg.html
<html>
  <form action="reg.php" method="post">
      <h1> reg login user info :</h1>
      <h3>LoginName: </h3>
      <input type="text" name="name" size="20" maxlength="255"/>
      <br />
      <br />
      <h3>LoginPass: </h3>
      <input type="text" name="password" size="20" maxlength="255"/>
      <br />
      <br />
      <h3><input type="submit" name="submit" value="SUBMIT" /></h3></p>
  </form>
</html>
[root@51 html]# vim /usr/local/nginx/html/reg.php
<?php
$servername = "192.168.4.52";
$username = "webuser";
$password = "123qqq...A";
$dbname = "gamedb";
$conn = new mysqli($servername, $username, $password, $dbname);
if($conn->connect_error){
  die("link fail: " . $conn->connect_error);
}else{
  echo "link mysql Ok";
  echo "       ";
}
$username=$_POST['name'];
$userpassword=$_POST['password'];
$sql="insert into user (name,password) values ('$username','$userpassword')";
if($conn->query($sql)){echo "insert data ok"; }
$conn->close();
?>

在浏览器中访问52数据库中会有数据产生。
http://139.9.39.148/reg.php

Mycat

功能：读写分离、高可用、数据分片
适合大量写入数据的存储需求
数据切分
分库分表:将存放在一个数据库 ( 主机 ) 中的数据，分散存放到多个数据库 ( 主机 ) 中,达到分散单台设备负载的效果
垂直分割：将表按字段纵向切分 ，分散到多个数据库中或将单个数据库的多个表进行分类,按业务类别分散到不同的数据库上。
水平分割：按照表中某个字段的某种规则,把表中的许多记录按
行切分,分散到多个数据库中
环境部署

mycat 服务提供 10 种分片规则:
1枚举法 sharding-by-intfile
2 固定分片 rule1
3 范围约定 auto-sharding-long
4 求模法 mod-long
5 日期列分区法 sharding-by-date
6 通配取模 sharding-by-pattern
7 ASCII 码求模通配 sharding-by-prefixpattern
8 编程指定 sharding-by-substring
9 字符串拆分 hash 解析 sharding-by-stringhash
10 一致性 hash sharding-by-murmur
恢复54和55为独立库
装包
安装JDK：
[root@56 ~]# rpm -qa |grep -i jdk
java-1.8.0-openjdk-headless-1.8.0.131-11.b12.el7.x86_64
copy-jdk-configs-2.2-3.el7.noarch X
java-1.8.0-openjdk-1.8.0.131-11.b12.el7.x86_64
java-1.8.0-openjdk-devel.x86_64
Java -version 查看版本信息
安装mycat:
[root@56mysql]#tar -zxf Mycat-server-1.4-beta-20150604171601-linux.tar.gz //免安装,解压即可使用
[root@56 mysql]# mv mycat/ /usr/local/
[root@56 mysql]# ls /usr/local/mycat/
修改配置文件：
Bin //mycat命令,如 启动 停止 等
Catlet //扩展功能
Conf //配置文件
Lib //mycat使用的jar
Log //mycat启动日志和运行日志
wrapper.log //mycat服务启动日志
.xml:扩展标记语言
server.xml //设置连mycat的账号信息
schema.xml //配置mycat的真实库表
rule.xml //定义mycat分片规则
… … //定义连mycat用户信息
… … //指定数据节点
… … //指定数据库地址及用户信息
Vim /usr/local/mycat/conf/server.xml
34 //连mycat的用户名
35 123456/property>
36 TESTDB
37
[root@56 conf]# cp schema.xml /tmp/
[root@56 conf]# sed -i “56,71d” schema.xml
[root@56 conf]# vim schema.xml

注释掉不使用的用户
Vim schema.xml

通过tail -f /usr/local/mycat/log/mycat.log日志查看连接情况

Galera+mycat
1.关闭防火墙，selinux，主机解析，时间同步，部署galera集群
1)配置Yum源
[root@mysql1 ~]# vim /etc/yum.repos.d/galera.repo
[galera]
name=galera
baseurl=http://releases.galeracluster.com/galera-3/centos/7/x86_64/
gpgcheck=0
Enabled=1
[root@mysql1 ~]# vim /etc/yum.repos.d/mysql_wsrep.repo
[mysql_wsrep]
name=mysql_wsrep
baseurl=http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/
gpgcheck=0
Enabled=1
[root@mysql1 ~]# yum list | grep -E “wsrep|galera”
[root@mysql1 ~]# yum -y install mysql-wsrep-5.7.x86_64 galera-3.x86_64
此处可以只在一台机器上做，然后开启缓存(keepcache=1)，下载下来包之后，搭建一个ftp服务器，另外两台机器直接做一个yum源即可安装
2)开启服务并且数据库修改密码
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# systemctl enable mysqld
[root@mysql1 ~]# grep “temporary password” /var/log/mysqld.log | awk '{print KaTeX parse error: Expected 'EOF', got '}' at position 3: NF}̲' [root@mysql1 …PATH:$JAVA_HOME/bin
[root@proxy1 ~]# source /etc/profile
[root@proxy1 ~]# java -version
2) 安装mycat
tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local
3) 创建库，并且授权给mycat一个账户
[root@mysql1 ~]# mysql -pQfcloud123.
mysql> create database galera;
mysql> grant all on galera.* to mycat@‘10.30.162.%’ identified by ‘Qfcloud123.’;
mysql> flush privileges;
配置mycat
[root@proxy1 ~]# cd /usr/local/mycat/conf/
[root@proxy1 conf]# vim server.xml

123456
galera_test

[root@proxy1 conf]# vim schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat="">http://io.mycat/">




show status like ‘wsrep%’







</mycat:schema>
[root@proxy1 ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server…
[root@proxy1 ~]# ss -antp | grep java
[root@proxy1 ~]# jps

3.测试
所有结点都正常时writehost负责写操作，其他writehost负责读操作
当第一个writahost失效时，另一个writehost负责写操作，其他的writehost负责读操作
当只有一个writehost时，同时负担读写

Mysql性能优化

影响数据库查询速度的四要素：sql查询速度、服务器硬件、网络带宽、磁盘IO
最好不要在主库服务器上数据备份，大型活动前取消这样的计划
减少从服务器的数量（从服务器会从主服务器复制binlog日志）
进行分级缓存（避免前端大量缓存失效）
避免使用select * 进行查询
分离业务网络和服务器网络
大表问题：
很难在短时间内过滤出需要的数据，建立索引需要很长时间，修改表结构需要长时间锁表，造成长时间主从延迟
分库分表把一张大表分成多个小表
大事务问题：
锁定数据太多，回滚时间长，执行时间长
避免一次处理太多数据，可以分批处理
移出不必要的select操作，保证事务中只有必要的写操作
影响MySQL性能因素：
服务器硬件，服务器系统（系统参数优化），存储引擎（MyISAM： 不支持事务，表级锁InnoDB: 支持事务，支持行级锁，事务ACID）数据库参数配置，数据库结构设计和SQL语句（重点优化）
Innodb存储引擎设置独立的表空间（在配置文件中加上innodb_file_per_table）
系统表空间转移到独立表空间方法：
1、使用mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出 ）可以在从服务器上操作。
2、停止MYsql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。
3、重启MYSQL，并重建Innodb系统表空间。
4、 重新导入数据。
或者 Alter table 同样可以的转移，但是无法回收系统表空间中占用的空间。
配置参数：
确定可以使用的内存上线，内存的使用上限不能超过物理内存，否则容易造成内存溢出；（对于32位操作系统，MySQL只能试用3G以下的内存。）
确定MySQL的每个连接单独使用内存
MySQL是单进程多线程（而oracle是多进程），也就是说MySQL实例在系统上表现就是一个服务进程，即进程；
MySQL实例是线程和内存组成，实例才是真正用于操作数据库文件的；
一般情况下一个实例操作一个或多个数据库；集群情况下多个实例操作一个或多个数据库。
Innodb_buffer_pool_size，定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；
key_buffer_size，定义了MyISAM所使用的缓存池的大小，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间；
select sum(index_length) from information_schema.talbes where engine=‘myisam’
即使开发使用的表全部是Innodb表，也要为MyISAM预留内存，因为MySQL系统使用的表仍然是MyISAM表。
max_connections 控制允许的最大连接数， 一般2000更大。
不要使用外键约束保证数据的完整性。
性能优化顺序：

1. 数据库结构设计和SQL语句
2. 数据库存储引擎的选择和参数配置
3. 系统选择和优化
4. 硬件升级
   提高MySQL系统的性能、响应速度（硬件替换、参数调整、查询优化）
   参数调整：
   Show status like “value”;
   Show global status like “查看全局状态量”;
   Show golbal variables like “查看全局变量”;
   连接数和连接超时
   max_connections 允许的最大并发连接数
   mysql> show variables like “%conn%”;
   mysql> set global max_connections=200;临时修改最大并发量
   Vim /etc/my.cnf
   max_connections=200; 永久设置最大连接并发数
   Connect_timeout 等待连接超时，默认10秒，仅登陆有效
   Wait_timeout 等待关闭连接的不活动超时秒数，默认28800秒8小时
   show global status like “max_used_connections”;曾经有过的最大并发连接数
   查看默认的最大连接数：show variables like “max_connections”;
   max_used_connections/max_connections=0.85时为理想比率
   Flush status;刷新状态
   Flush privileges;刷新特权
   Flush logs;刷新日志
   Flush tables;刷新表
   缓存参数
   Innodb_buffer_pool_size 用于innodb引擎的缓存池大小
   key_buffer-size 用于MyISAM引擎的关键索引缓存大小
   sort_buffer_size 为每个要排序的线程分配此大小的缓存空间
   read_buffer_size 为顺序读取表记录保留的缓存大小
   thread_cache_size 允许保存在缓存中被重用的线程数量
   table_open_cache 为所有线程缓存可打开的表的数量
   查询优化：
   分析查询语句：explain select语句
   Describe select语句
   使用索引查询可以提高查询的速率
5. 使用关键字like优化索引查询
6. 查询语句中使用多列索引
7. 查询语句中使用关键字or
   优化数据库结构
8. 字段特别多且有些字段使用概率很低的表，可以将其分解为多个表
9. 增加中间表
   优化插入记录
10. 禁用索引
    禁用索引alter table 表名 disable keys
    启用索引altere table 表名 enable keys
11. 禁用唯一性检查
    禁用唯一性检查set unique_checks=0
    启用唯一性检查set unique_checks=1
12. 优化insert语句
    同时插入多条记录，能用load date infile语句就用这个语句
    分析表检查表优化表，都会加上只读锁
    分析表：分析关键字的分布analyze table 表名
    检查表：检查表的错误check table 表名
    优化表：消除删除或更新所造成的空间浪费 optimize table 表名
    查询高速缓存
    检查高速缓存是否开启show variables like “%querry_cache%”;
    使用高速缓存select sql_cache * from 表名
    不使用高速缓存查询select sql_no_cache * from 表名
    优化多表查询
    使用子查询优化多表查询，通过服务器变量优化查询
    优化表设计（myisam、isam）
    优先考虑使用特定字段长度，后考虑变长字段，使用optimize table 表名优化
    错误日志 记录启动/运行/停止过程中的log-error[=name]
    查询日志 记录客户端连接和查询操作 general-log
    general-log-file=
    慢查询日志 记录耗时较长或不使用索引的查询操作 slow-query-log slow-query-log-file= long-query-time=
    启用慢查询 slow-query-log
    指定慢查询日志文件 slow-query-log-file
    超过时间(默认10秒) long-query-time
    记录未使用索引的查询 log-queries-not-using-indexes
    [root@54 mysql]# tail -f 54-slow.log 动态显示慢查询日志
    [root@54 mysql]# mysqldumpslow 54-slow.log 统计慢查询日志
    启用慢查询日志[ root@master ~] # vim /etc/my .cnf
    slow_query _log=1
    slow_query _log_file=mysql- slow.log
    long_query _time=5
    log_queries_not_using_indexes=1
    [ root@master ~] # systemctl restart mysqld
    查询缓存
    mysql> show variables like “query_cache%”;查询缓存大小
    mysql> show global status like “qcache%”;查询缓存统计
    Qcache_inserts 查询请求总量
    Qcache_hits 在查询缓存中找到的量

数据库安全

1. 为操作系统和安装软件打补丁
2. 关闭不使用的系统服务和端口
3. 审计服务器的用户账号
4. 设置MySQL的root密码
   1）初始化安全脚本
   安装完MariaDB或MySQL后，默认root没有密码，并且提供了一个任何人都可以操作的test测试数据库。有一个名称为mysql_secure_installation的脚本，该脚本可以帮助我们为root设置密码，并禁止root从远程其他主机登陆数据库，并删除测试性数据库test。
   密码安全手动修改MariaDB或MySQL数据库密码的方法：
   [ root@proxy ~]# mysqladmin - uroot - predhat password 新密码
   MariaDB[(none)]>set password for root@‘localhost’=password( ‘redhat’);
   你的密码被明文记录 了，下面来看看明文密码：
   [ root@proxy ~] # cat .bash_history
   [ root@proxy ~] # cat .mysql_history
   可以使用SSH远程连接服务器后，再从本地登陆数据库（避免在网络中传输数据，因为网络环境中不知道有没有抓包者）。 或者也可以使用SSL对MySQL服务器进行加密，类似与HTTP+SSL一样，MySQL也支持SSL加密（确保网络中传输的数据
   是被加密的）。
   恢复密码及设置
   重置密码：
   1.停止MySQL服务程序
   2.跳过授权表启动MySQL服务程序
   要注释掉有关密码的设置 skip-grant-tables写入/etc/my.cnf配置文件
   重设root密码(更新user表记录)
   Update mysql.user set authentication_string=password(“654321”) where user=“root” and host=“localhost”;
   mysql> flush privileges;
   quit;
   3.以正常方式重启MySQL服务程序
   修改密码：
   [root@localhost ~]# mysqladmin -uroot -p password “新密码”
   创建授权
   用户授权：在数据库服务器上添加可以连接数据库服务器的用户并设置访问权限及密码
   Grant 权限列表（all 所有权限 usage 无权限 ） on 库名.表名 to 用户名@“客户端地址” Identified by “密码” With grant option; 是否有授权权限
   mysql> grant all on . to mydba@"%" identified by “123456” with grant option;
   [root@mysql2 ~]# mysql -h192.168.4.50 -umydba -p123456
   mysql> select user();显示当前位置
   mysql> show grants;显示访问权限
   注意事项
   当库名.表名 为 . 时,匹配所有库所有表
   授权设置存放在 mysql 库的 user 表
   权限列表
   all:匹配所有权限
   SELECT,UPDATE,INSERT … …
   SELECT,UPDATE (字段1, … … , 字段N)
   客户端地址
   %:匹配所有主机
   192.168.1.%:匹配指定的一个网段
   192.168.1.1:匹配指定IP地址的单个主机
   %.tarena.com:匹配一个DNS区域
   svr1.tarena.com:匹配指定域名的单个主机
   User表：
   Show grants for root@”localhost”;
   Show grants for mysql.sys@”localhost”;查看mysql.sys在本机登陆的权限
   授权库 mysql ,主要的几个表
   user表,存储授权用户的访问权限
   db表,存储授权用户对数据库的访问权限
   tables_priv表,存储授权用户对表的访问权限
   columns_priv表,存储授权用户对字段的访问权限
   用户查看自己的权限– SHOW GRANTS;
   管理员可查看其他用户的权限SHOW GRANTS FOR 用户名@‘客户端地址’;
   Select user,host from mysql.user;
   重设用户密码:
   授权用户连接后修改密码
   set password= password(“新密码”);
   管理员重置授权用户连接密码
   set password for 用户名@‘客户端地址’=passowrd(“新密码”);
   撤销授权
   撤销用户权限:
   REVOKE 权限列表 ON 库名.表名FROM 用户名@“客户端地址”;
   Revoke grant option on . from mydba@"%";
   mysql> show grants for mydba@"%";
   mysql> revoke delete on . from mydba@"%";
   删除授权用户：
   1.Select user from mysql.user where user=”mydba”;
   Delete from mysql.user where user=”mydba” and host=”%”;
   Drop user sqler02@”localhoat”;
   刷新权限：Flush privileges;
   Mysqladmin flush-privileges
   Mysqladmin reload
   2.drop user 用户名@“客户端地址”；
   Drop user sqler02@”localhost”;
   mysql> select user,host from mysql.user;
   mysql> show grants for sqler02@“localhost”;
   创建用户：create user 用户名 identified by “密码”;
   删除用户：drop user 用户名；
   重命名用户：rename user 原名 to 新名；

php中使用MySQL数据库

PHP使用数据库分为5步：连接MySQL服务器、选择数据库、SQL语句、关闭结果集、断开MySQL服务器
连接MySQL服务器：mysql_connect(),mysql_pconnect()
选择数据库：mysql_select_db()
执行sql语句：mysql_query()
返回结果集到数组：mysql_fetch_array()
返回结果集获取一行作为对象：mysql_fetch_object()
从结果集中获取一行作为枚举数组：mysql_fetch_row()
获取查询结果集中的记录数：mysql_num_row()
关闭结果集（释放内存）：mysql_free_result()
断开MySQL数据库连接：mysql_close()
查询结果返回到数组：mysql_fetch_assoc()