PL/SQL
基础用法
数据库相关
show databases;
show table status;
show open tables;
show create database leetcode;
create database my_test;
show databases;
drop database my_test;
数据库引擎
select distinct engine
from information_schema.TABLES;
create database learn;
use learn;
show tables;
drop table if exists tb_emp1;
create table tb_emp1
(
id int(11) comment '员工编号',
name varchar(25) comment '员工名称',
depId int(11) comment '部门',
salary float comment '工资'
);
describe tb_emp1;
/·栏位 就是columns
加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
· 删去一个栏位: DROP "栏位 1"
· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"/
alter table tb_emp1
drop column salary;
alter table tb_emp1
add column salary int(11) not null;
alter table tb_emp1
modify salary int(11);
alter table tb_emp1
modify salary int(11);
alter table tb_emp1
change depId depId int(2) not null;
显示所有约束
select *
from information_schema.TABLE_CONSTRAINTS
where TABLE_NAME = 'tb_emp1';
show create table tb_emp1;
charu
insert into tb_emp1(id, name, depId, salary)
values (1, 'xsj', 1, 0);
select *
from tb_emp1;
describe tb_emp1;
添加约束
alter table tb_emp1
add constraint tb_id primary key (id);
show create table tb_emp1;
update tb_emp1
set salary=2000;
alter table tb_emp1
add constraint tb_name_unique unique (name);
alter table tb_emp1
add constraint ch_salary check ( salary > 1800 );
alter table tb_emp1
drop check ch_salary;
alter table tb_emp1
add check (salary < 90000);
alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)
/唯一性是index
not null 是约束
check 是check/
alter table tb_emp1
drop index tb_name_unique;
alter table tb_emp1
change salary salary float not null;
删除主键
alter table tb_emp1
drop primary key;
alter table tb_emp1
drop check tb_emp1_chk_1;
放在最后边
show index from tb_emp1;
show create table tb_emp1;
desc tb_emp1;
命令行里可以
show create table tb_emp1 \G;
alter table tb_emp1 rename to tb1;
show tables;
alter table tb1
change name name2 varchar(40) unique;
alter table tb1
drop index tb_name_unique;
放在最后边
show index from tb1;
show create table tb1;
desc tb1;
在某个字段后边插入一个新列
alter table tb1
add column name varchar(12) after name2;
alter table tb1
drop column name;
desc tb1;
show engines;
外键
create table if not exists tclass
(
gradeId int(3) not null auto_increment primary key,
name varchar(128) not null unique
);
insert into tclass
values (null, '1-1班'),
(null, '1-2班'),
(null, '1-3班');
select *
from tclass;
create table if not exists student
(
id int(5) not null primary key auto_increment,
name varchar(128) not null,
class int(3) not null,
age int(3),
constraint check ( age > 0 ),
key classId (class),
constraint f_class_id foreign key (class) references tclass (gradeId)
);
insert into student
values (null, 'llz', 3, 1);
insert into student
values (null, 'xsj', 1, 28);
insert into student
values (null, 'xt', 2, 25);
select *
from student;
alter table student
modify id int(11) unsigned auto_increment;
show index from student;
show create table student;
alter table student
drop foreign key f_class_id;
alter table student
drop index classId;
create index classId on student (class);
alter table student
add constraint f_class_id foreign key (class) references tclass (gradeId)
on delete cascade on update no action;
select *
from student;
select *
from tclass;
禁用外键
set foreign_key_checks = 0;
set foreign_key_checks = 1;
show tables;
select *
from tb1;
alter table tb1
modify salary float(4, 2);
update tb1
set tb1.salary=20.1272
where id = 1;
alter table tb1
drop check ch_salary;
alter table tb1
modify tax decimal(5, 4) default 0;
alter table tb1
modify depId int(2);
alter table tb1
modify id int(11) auto_increment;
select *
from tb1;
desc tb1;
insert into tb1 (id, name2, depid, salary, tax)
values (null, 'bjj2', 9999, 32.11, 2.343);
alter table tb1
add column y year;
select *
from tb1;
update tb1
set y='0'
where id = 1;
update tb1
set y=21
where id = 2;
update tb1
set y='21'
where id = 4;
update tb1
set y='2177'
where id = 5;
alter table tb1
add column date datetime;
select now();
insert into tb1 (date)
values (datediff(now(), 1));
select *
from tb1;
alter table tb1
add column time time;
select *
from tb1
where id = 8;
insert into tb1 (time)
values ('041123');
desc tb1;
select curdate();
select current_time();
select current_timestamp();
select now();
select pow(2, 8);
create table if not exists chars
(
id int not null primary key auto_increment,
c1 char(2),
c2 varchar(255),
t1 tinytext,
t2 text(65535)
);
desc chars;
insert into chars
values (null, '12', 'sldjkjdddd', 'ss', 'mediumtext');
select *
from chars;
select length(c1)
from chars;
show tables;
desc tb1;
alter table tb1
modify date datetime default now();
insert into tb1(tax)
values (2.3);
select *
from tb1;
select mod(10, 3);
select mod(-10, 3);
select rem(10, -3);
select -10 % 3;
drop function rem;
create function rem(x int, y int)
returns int
DETERMINISTIC
BEGIN
return x - sign(x / y) * floor(abs(x / y)) * y;
end;
select rem(10, 3);
select rem(-16, 3);
select mod(-16, -3);
运算符
select null = null n, 1 = 1 a, 1 = 2 c, 1 <=> 1 d, null <=> null f;
select 1 <= 2, 'goo' < 'goo0';
select isnull(null);
select power(1.05, 10);
select least(1, 2, 3, 4, -3);
select greatest(2, 3, 4, 422);
select null and null and 1, 2 and 1, 1 and 0;
select 1 or null, null or null, 0 or null, 0 or 1;
select 1 XOr 1, 2 xor 1, 0 xor 3, null xor 1, null xor null;
select binary 'a' = 'A', 'a' = 'A';
select 4 && 8, -2 || NULL, NULL XOR 0, 0 XOR 1, !2;
1,1,1,1,1,0,1
select 36 > 27, 15 >= 8, 40 < 50, 15 <= 15, NULL <=> NULL, NULL <=> 1, 5 <=> 5;
select rand(), rand(10);
select round(1.23), round(122.33332, -1);
select round(1.23), round(-1.3532, 2);
show function code rem ;
show variables like '%with-debug%';
select degrees(pi());
select radians(90);
select round(pi(), 25);
select length('我么') xx;
select char_length('我们') x;
为什么varcahr(6)的可以插入6个汉字，或者六个字符。
show tables;
select length(cl)
from chars;
alter table chars
add column cl varchar(6);
update chars
set cl='我们都是好孩'
where id = 1;
update chars
set cl='123456'
where id = 1;
select lpad('xsj', 1, '-');
select trim('ss' from 'ssswess');
select trim('-' from repeat('123-', 3));
select strcmp('xy', 'xz');
select locate('j', 'xsj');
select instr('xsj', 'j');
select from_unixtime(123);
select unix_timestamp();
select extract(year_month from now());
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 minute);
select date_add(now(), interval 1 day);
select date_add(now(), interval 1 month);
select addtime(now(), 1), now();
select ifnull(2, 1);
select version();
select connection_id();
show processlist;
show full processlist;
show schemas;
select user();
select charset('');
select last_insert_id();
select md5('123456');
select md5('123456');
select sha('123456');
select sha('123456');
select sha2('123456', 224);
select conv(10, 10, 8);
lock
select get_lock('lock1', 10);
select is_free_lock('lock1');
select is_free_lock('lock2');
select is_used_lock('lock1');
select is_used_lock('lock2');
select release_lock('lock1');
show tables;
select benchmark(12, 'select * from salary');
select benchmark(10000, sha(rand()));
转换
select cast(100 as char(2));
select cast('2020-01-01' as date);
select current_user();
函数
select 18 / 5, 19 % 5, mod(19, 5), degrees(pi() / 4), pow(9, 4), round(pi(), 2);
select char_length('hello'), length('he llo'), length('我们') we;
字符串
select substr('Nice to meet you!', 9, 4),
repeat('Cheer', 3),
reverse('voo'),
concat_ws(' ', 'we', 'are', 'good');
日期
show processlist;
select SHA('MYSQL');
select MD5('MYSQL'), conv(100, 10, 8), format(3.21222, 4);
循环
with recursive cte(n) as (
select 1
union
select n + 1
from cte
where n < 8
)
select *
from cte;
show tables;
use leetcode;
类似connect by
select *
from t_areainfo;
with recursive father as (
select id, name, parentId from t_areainfo where id = 26
union all
select g.id, g.name, g.parentId from t_areainfo g
inner join father f on f.parentId = g.id
)
select group_concat( name order by id separator '-') from father;
用户权限
查询
select * from sales for update ;
select * from sales ;
show databases ;
show tables;
插入
insert into sales (id, year, country, product, profit)
values (20,2020,'china','computer',100);
distinct ,先distinct 然后在limit
select * from sales order by id limit 1,1;
select distinct year from sales limit 1,1;
select distinct year from sales limit 1 offset 1;
uuid
select upper(replace(uuid(),'-',''));
select uuid();
if null
select if(product='Phone','mobile',product) from sales;
设置id
update sales set ids=uuid();
dense，窗口函数
select country,profit, dense_rank() over (partition by country order by profit) rk,id
from sales;
select country,profit, row_number() over (partition by country order by profit) rk,id
from sales;
select country,profit, rank() over (partition by country order by profit) rk,id
from sales;
新建用户
select * from mysql.user;
select Host,User,authentication_string from mysql.user ;
use mysql;
use leetcode;
create user xsj@'localhost' identified by '123456';
create user xsj2@'localhost' identified by '123456';
create user xsj3@'localhost' identified by '123456';
flush privileges ;
直接插入数据库的方式新建用户，密码没有加密
insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values ('localhost','shengjie','123456','','','');
flush privileges ;
SHOW GRANTS FOR 'root'@'localhost';
drop user xsj2@'localhost';
drop user xsj3@'localhost';
drop user shengjie@'localhost';
flush privileges;
给用户赋予权限
grant insert,update,select ON leetcode.*
To xsj@'localhost' identified by 'shengjie';
show databases ;
grant select, insert, update, delete on . to shengjie@'%' identified by '123456';
select host,User,authentication_string
from mysql.user where User like 'x%';
create user a1 identified by '123',a2 identified by '123';
drop user a1,a2;
create user xsj@'localhost' identified by '123456';
flush privileges;
with grant option 可以将自己的权限赋予别
grant select,update,insert on leetcode.* to xsj@'localhost' with grant option ;
这个是只能在本地连接把
create user leet@'localhost' identified by 'leet';
grant select,update,insert on leetcode.* to leet@'localhost' ;
修改密码
set password for xsj@'localhost' = 'shengjie';
flush privileges ;
set password for leet@'localhost' ='123456';
select host,User,authentication_string from mysql.user;
显示用户权限
show grants for leet@'localhost';
show grants for root@'%';
grant update,select,insert,delete on leetcode.* to leet@'localhost' with grant option ;
show grants for leet@'localhost';
删除权限
revoke update on leetcode.* from leet@'localhost';
flush privileges;
revoke all privileges on leetcode.* from leet@'localhost';
revoke grant option on leetcode.* from leet@'localhost';
revoke all privileges ,grant option from leet@'localhost';
时间日期
select now() +10;
select date_add(now(),interval 7 year );
select date_add(now(),interval 7 hour );
select datediff(date_add(now(),interval 7 day ),now());
select year(now());
select date (now());
select timestamp (now());
select time(now());
select date_format(now(),'%Y-%m-%d %H:%i:%S');
group，group_concat
select country,group_concat(product,'-',year) from sales group by country with rollup ;
select country,group_concat(product,'-',year) from sales group by country ;
select ifnull(year,1),ifnull(country,1),group_concat(year),group_concat(country) from sales group by year,country with rollup;
视图 关键词as
create view sales_group3 as select year,country,group_concat(country) from sales group by year, country;
select * from sales_group3;
describe sales_group;
show tables ;
drop view sales_group3;
select * from sales_group;
普通索引
create index sales_id on sales(id);
唯一索引
CREATE UNIQUE INDEX index_id ON sales(id);
create index country_4 on sales(country(4));
显示索引
show index from sales;
drop index index_id on sales;
EXPLAIN select * from sales where country like 'c%';
EXPLAIN select * from sales where id=2;
EXPLAIN select now();
select now();
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation_server';
select Host,User from mysql.user;
触发器
create trigger ids_trigger
before insert on sales
for each ROW
BEGIN
SET new.ids=REPLACE(UUID(),'-','');
END;
备份
use seasons;
show tables;
use leetcode;
select * from sales;
select * from sales into outfile 'my.txt';
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_bin';
SHOW binary logs;
SHOW VARIABLES LIKE '%general%';
数据库约束
数据库相关
show databases ;
show table status ;
show open tables ;
show create database leetcode;
create database my_test;
show databases;
drop database my_test;
数据库引擎
select distinct engine from information_schema.TABLES;
create database learn;
use learn;
show tables;
drop table if exists tb_emp1;
create table tb_emp1(
id int(11) comment '员工编号',
name varchar(25) comment '员工名称',
depId int(11) comment '部门',
salary float comment '工资');
describe tb_emp1;
/·栏位 就是columns
加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
· 删去一个栏位: DROP "栏位 1"
· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"/
alter table tb_emp1 drop column salary;
alter table tb_emp1 add column salary int(11) not null;
alter table tb_emp1 modify salary int(11);
alter table tb_emp1 modify salary int(11);
alter table tb_emp1 change depId depId int(2) not null ;
显示所有约束
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME='tb_emp1';
show create table tb_emp1;
charu
insert into tb_emp1(id,name,depId,salary)values (1,'xsj',1,0);
select * from tb_emp1;
describe tb_emp1;
添加约束
alter table tb_emp1 add constraint tb_id primary key (id);
show create table tb_emp1;
update tb_emp1 set salary=2000;
alter table tb_emp1 add constraint tb_name_unique unique(name) ;
alter table tb_emp1 add constraint ch_salary check ( salary>1800 );
alter table tb_emp1 drop check ch_salary;
alter table tb_emp1 add check(salary<90000);
alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)
/唯一性是index
not null 是约束
check 是check/
alter table tb_emp1 drop index tb_name_unique;
alter table tb_emp1 change salary salary float not null ;
删除主键
alter table tb_emp1 drop primary key ;
alter table tb_emp1 drop check tb_emp1_chk_1;
放在最后边
show index from tb_emp1;
show create table tb_emp1;
存储过程
正则表达式
show tables;
create table if not exists reg
(
name varchar(100) not null
);
select *
from reg;
/insert into reg values ('xxs');
insert into reg values ('xxs,a,b,d,c,,ds,d,');
insert into reg values ('xxs238sd');
insert into reg values ('37943');
insert into reg values ('2.31');
insert into reg values ('2.31d');
insert into reg values ('2.2.1');/
2***d结尾的。
select *
from reg
where regexp_like(name, '^2.*d$');
不等于1,2,3
select *
from reg
where regexp_like(name, '[^123]');
select *
from reg
where regexp_like(name, '[^0-9.]');
select *
from reg
where regexp_like(name, '[a-z]+');
select *
from reg
where regexp_like(name, ',');
select *
from reg
where regexp_like(name, '2\..*?');
select name, regexp_like(name, '(2{1})')
from reg;
select *
from reg
where regexp_like(name, '[0-9]{2,3}');
select name, regexp_instr(name, '[,]+', 1, 2)
from reg;
select name, regexp_replace(name, 'x', 'a')
from reg;
select 'Bn' REGEXP '^Ba*n';
select 'pix' REGEXP '^(pi|apa)$';
-> 0（表示不匹配）
select substr('xsjsss', 3, 2);
select name
from reg;
select *
from reg
where regexp_like(name, '2|,');
select name, regexp_substr(name, '[^,]+', 1, 1)
from reg;
select *
from reg;
select *
from reg
where regexp_like(name, '.2.d$');
循环查找
DROP TABLE IF EXISTS t_areainfo;
CREATE TABLE t_areainfo
(
id int(11) NOT null AUTO_INCREMENT,
level int(11) DEFAULT '0',
name varchar(255) DEFAULT '0',
parentId int(11) DEFAULT '0',
status int(11) DEFAULT '0',
PRIMARY KEY (id)
) ENGINE = InnoDB
AUTO_INCREMENT = 65
DEFAULT CHARSET = utf8;
select *
from t_areainfo
where parentId = 4
or id = 4
order by id, parentId;
通过当前id获取父类
DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
DETERMINISTIC
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp = '$';
SET sTempChd = cast(areaId as char);
WHILE sTempChd is not NULL
DO
SET sTemp = CONCAT(sTemp, ',', sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId, sTempChd) > 0;
END WHILE;
return sTemp;
END;
select concat('a',',','b');
select cast(1 as char);
创建存储过程。
select queryChildrenAreaInfo(4);
select *
from t_areainfo
where FIND_IN_SET(id, queryChildrenAreaInfo(4));
drop function if exists test;
create function test()
returns varchar(100)
DETERMINISTIC
begin
return (select group_concat(name) name from reg where name like 'x%');
end;
select test() name;
创建一个存储函数
drop function if exists test_pro;
create function test_pro()
returns int
DETERMINISTIC
BEGIN
declare ids int default 1;
select id into ids from sales where id = 2;
set ids = 10;
return ids;
end;
select test_pro() ids;
loop循环
drop function if exists test_add;
create function test_add(max int)
returns int
DETERMINISTIC
BEGIN
declare num int default 0;
declare sum int default 0;
add_num:
loop
set num = num + 1;
set sum = sum + num;
if num >= max then
leave add_num;
end if;
end loop add_num;
return sum;
end;
select test_add(12);
for 循环
create function findParent(chdId int)
returns varchar(4000)
DETERMINISTIC
BEGIN
declare allId varchar(4000);
declare nowIds varchar(4000);
set allId = '$';
set nowIds = cast(chdId as char);
while nowIds is not null
do
set allId = concat(allId, ',', nowIds);
select group_concat(parentId) into nowIds from t_areainfo where find_in_set(id, nowIds);
end while;
return allid;
end;
select findParent(26);
select * from t_areainfo;
select replace(group_concat(name),',','-') addr from t_areainfo where find_in_set(id,findParent(5))
order by id;
其他
show tables;
select '1' where find_in_set('c','a,b,n');
insert into reg values (0);
select * from reg;
select * from reg where name!='0';
select cast('2.2.3' as float );
select locate('a','bsada');
select replace(' sldj sd ',' ','');
select CHAR_LENGTH('sld中文');
select character_length('sl中午');
select concat('a','b');
select concat_ws('-','a','b','c','d');
select format(2932472.13,1);
select round(323423.323212,4);
select left('foijfldsfj',3);
select concat('a',space(10),'w');
select curdate();
select curtime();
select current_timestamp();
select adddate(now(),-7);
select addtime(now(),100);
select dayofweek(now());
select dayofweek(date('2020-10-10'));
select date('2020-01-10 12:12:12');
select sysdate();
select CONNECTION_ID();
select database();
select current_user();
select LAST_INSERT_ID();
标准格式化
select minute(date_format('2020-01-01 23:10:12','%Y-%m-%d %T'));
select date_format('2020-01-01 23:10:12','%Y-%m-%d %T');
select date_format(now(),'%Y-%m-%d %r');
1.mod 和rem的区别。
首先先说fix和floor取整的区别。floor取整时向负无穷取，fix向零取。也就是当为正数时两个没有区别。负数时，fix比floor大1。
再说mod=floor(x/y)，rem=fix(x/y)。
一、left join
1、left join on
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、left join on and
（1）如果and语句是对左表进行过滤的，那么不管真假都不起任何作用。
（2）如果and语句是对右表过滤的，那么左表所有记录都返回，右表筛选以后再与左表连接返回。
3、left join on where
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了。
（1）此时相当于inner join on
（2）此时on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。
4、inner join on and 和 inner join on where
无区别，不管是对左表还是右表进行筛选，on and 和 on where都会对生成的临时表进行过滤。
二、group by
select xxx_name, count(*) as counter
from table_y
where where_condition -- 分组前过滤条件
group by xxx_name --分组名称
having having_condition -- 对分组后的数据进行过滤，因为分组后的数据都是与分组有关的。
order by zzz
分布式
1.启动mysql 容器
docker run --name mysql-lates --restart always
-v /etc/timezone:/etc/timezone
-v /etc/localtime:/etc/localtime
-p 3306:3306 -p 33060:33060
-e MYSQL_ROOT_PASSWORD=shengjie
-e MYSQL_ROOT_HOST='%'
-e LANG=C.UTF-8 -d mysql:latest
创建数据库
create database seasons
创建数据表
是否需要用powerdesigner设计呢？
2.用idea 连接mysql
其实在idea 里写代码挺方便的。
3.数据库设置
3.1 设置时区
查看linux时区
date -R
设置时区
tzselect
然后一步步选择就好了。
设置好tzselect后，一定要重写/etc/localtime
rm /etc/localtime
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
进入容器
docker exec -it mysql8 bash
到目标目录
cd /etc/mysql
追加到文件末尾
echo "default-time-zone = '+08:00'">>my.cnf
重启mysql
然后连接上mysql用select NOW(); 查看是否设置成功
cd /etc/mysql
cp my.cnf my.cnf.back
echo "character-set-server=utf8">> my.cnf
停止mysql服务
mysqladmin -uroot -proot shutdown
启动
mysqld
a、注意理解mysqld与mysql。mysqld为mysql服务器或者称之为服务器上的实例，用于提供客户端访问。mysql为客户端访问管理工具。
b、可以通过三种方式启动mysql服务器(mysqld，mysqld_safe，service mysql start)
c、对于mysqld_safe，service mysql start方式，总是通过调用mysqld_safe来启动mysqld进程，以防止mysqld意外宕掉而提供重启服务。
d、可以通过mysqladmin shutdown以及service mysql stop来终止mysql服务器
备份
导出所有数据库
mysqldump -u username -P --all-databases>filename.sql
导出指定数据库
mysqldump -u username -P -databases d1 d2 d3>filename.sql
导出某个表
mysqldump -u username -P d1 t1,t2>filename.sql
daoru all
mysql -uroot -p < ss.sql
msyql -uroot -p databsesname <ss.sql
my.cnf设置错误日志路径
log-error=dir/{filename}
查询错误日志路径
SHOW VARIABLES LIKE 'log_error';
删除错误日志
mysqladmin -uroot -p flush-logs
查看日志
mysqlbinlog filename.number
4.一个可以解决市区和编码问题
Docker file for date and locale set
VERSION 0.0.3
Author: bolingcavalry
基础镜像
FROM centos:7
作者
MAINTAINER BolingCavalry zq2599@gmail.com
定义时区参数
ENV TZ=Asia/Shanghai
设置时区
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo '$TZ' > /etc/timezone
安装必要应用
RUN yum -y install kde-l10n-Chinese glibc-common
设置编码
RUN localedef -c -f UTF-8 -i zh_CN zh_CN.utf8
设置环境变量
ENV LC_ALL zh_CN.utf8
5、配置备份
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
Custom config should go here
!includedir /etc/mysql/conf.d/
default-time-zone = '+08:00'
character-set-server=utf8
server-id=152
log-bin=mysql-bin
binlog-do-db=oauth
查看masate状态
show master status;
查看日志内容
show binlog events in ''
-- 恢复数据方法如下：
mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest
下面是binlog日志恢复中一些常用的参数
--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position：从二进制日志中读取指定position 事件位置作为开始。
--stop-position：从二进制日志中读取指定position 事件位置作为事件截至