官网:http://dev.mysql.com/downloads/mysql
配置mysql数据库:
下载源码包解压:切换到目录下
yum -y install mysql-community-*.rpm
初始随机密码生成的文件: cat /var/log/mysqld.log
mysql -uroot -p'初始随机密码'
show database; #查看数据库
#设置密码
alter user root@"localhost" identified by '密码' #密码不能过于简单不少8位数字,字母,下划线
\c 结束操作 \h 查看帮助
show variable like '%password%' ;
set global validate_password_policy=0;
set global validate_password_length=6;
vim /etc/my.cnf 在[mysql] 下插入
validate_password_policy=0
validate_password_length=6
:wq
修改完毕,重启服务
systemctl restart mysqld
数据库基础:
连接方式:命令行连接
mysql -uroot -p12344 mysql数据库名
select database();
数据库:类似文件夹
建表:类似文件
插入记录:类似文件内容
SQL命令使用规则:
sql命令不区分大小写
每条sql命令以;结束
\c终止sql命令
常用的sql命令分类:
DDL 数据定义语言 如:create alter drop
DML 数据操作语言 如:insert update delete
DCL 数据控制语言 如: grant revoke
DTL 数据事物语言 如:commit rollback savepoint
库管理命令:
show databases; 显示已有的库
select user(); 显示连接用户
use 库名; 切换库
select database(); 显示当前所在的库
create database 库名; 创建新库库名区分大小写,字母数字下划线组成 /var/lib/mysql
show tables ; 显示已有的表
drop database 库名; 删除库
var/lib/mysql
表:
create table 库名.表名(name char(10),age int);
show tables;
desc 表名; 查看表字段名类型
insert into 库名.表名 values ("bob",19);
select * from 库名.表名;
select name,age 库名.表名;
update 库名.表名 set 字段名="";
delete from 库名.表名; 删除表记录不加where,删全部
数据类型:
1.数值类型: 体重
2.字符类型: 姓名 地址
3.枚举类型: 兴趣爱好,性别,专业
4日期时间类型:出生日期等
字符类型:
定长:char(最大255个)不够不全,超出无法写入
变长:varchar(65532根据实际大小分配存储空间,超出无法写入)
大文本类型:text/blob 大于65532
show create table 表名 \G;
creat table 表名(姓名 char(16),地址 varchar(30))default charset=utf8;
数值类型:
整数型:tinyint(有符号-128~127,无符号范围0~255)
smallint(有符号-32768~32767,无符号范围:0~65535)
mediumint,int,bigint;无符号unsigned使用无符号范围
浮点型:
float:单精度(有符号范围-3.402823……到-1.175494……)无符号去掉符号。
double:双精度(有符号范围-1.1797693到-2.2250738)无符号去掉符号。
create table t4(name char(10) ,pay float(7,2); 总位数为7位,2位是小数点。
日期时间类型:
date:范围1901-2155 格式:yyyymmdd
year:范围1901-2155 格式:yyyy
time:格式HH:MM:SS
datetime:范围1000-01-01 00:00:00:00
timestamp:范围1970-01-01 00:00:00-2038-1-19yyyymmddhhmmss
create table t5(name char(10),s_year year,up_time time,birthday date;party datetime);
时间函数:
curtime() 获取当前系统时间
curdate() 获取当前系统日期
now() 获取当前日期和时间
year() 获取年
month() 获取月
year() 获取日
date() 获取日期
time() 获取时间
select curtime();
select now() ; 日期时间
select date(now());
select time(now());
insert into t5 values ('tom",2000.time(now(),curdate(),now());
注册账号时间 datetime now()
当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime的值为null空
year:要求使用四位数赋值,两位是为20或者是19开头
例如:datetime和timestamp
create table t7( name char(10),meetting datetime,party timestamp);
insert into t7 values('ss',now,now());
insert into t7(name,meeting) values ('bob',20231120); timestamp-party字段不为空
insert into t7(name,party) values("jerry",19731010101010); timedate-meetting字段为空
枚举类型:
字段值必须在设置的范围内选择,
enum 单选:
set 多选:
create table t6(name char(15),sex enum("boy","girl","no"),likes set("eat","money")
insert into t6 values("bob","man","girl,book"); 报错超出值类型范围
字段约束条件:null key default extra
null:是否为空 :yes not null
key:键值
default:null
extra:额外设置
desc t7 查看表
create table t8(name char(10) not null,age tinyint unsigned default "77"
class char(7) not null defualt "nsd");
insert into t8 values(null,null,null) ; name报错,字段不为空值,class也不为空值
insert into t8 values ("null",null,""); 不报错空,字符不为空
设置约束条件
null 可以为空;
not null 不允许为空;
key 键值类型;default 设置默认值,缺省为null
extra 额外设置
修改表结构:
alter table 库名.表名 执行动作
add 添加字段
modify 修改字段类型
change 修改字段名
drop 删除字段
rename 修改表名
添加新字段:
alter table 库名.表名 add name char(15) [after字段名|first];
desc t5;
alter table t5 add email varchar (30) not null default " test@.com";
alter table t5 add stu_id char(9) first;
alter table t5 add sex enum('boy','girl') default "boy" after name;
修改字段类型:
alter table 库名.表名 modify 字段名 类型(宽度) 约束条件[after字段名 | first];
alter table t5 modify sex enum('man','woman','boy') default 'man'; 包含已设置的值才能设置
alter table t5 modify name varchar(16);
alter table t5 modify name varchar(15) not null default "ss" after sex;
删除表字段:
alter table 库名.表名 drop 字段名;
alter table db1.t2 drop user_num,drop sex ; 删多字段
alter table table t5 drop stu_id
select * from t5;
修改表名:
alter table 表名 rename 新表名;
alter table t2 rename school;
修改字段名:
alter table 表名 change 原表名 新表名;
alter table t5 change s_year csnf year;
普通索引
什么是索引类似目录,优缺点可以快查询,修改时要调整降低写入数据占存储空间,规则,查看,创建
算法:Btree B+tree hash
字段五种键值:
index 普通索引
unique 唯一索引
fulltext 全文索引
primary key 主键
foreign key 外键
要求:一个表可以有多个index字段,字段的值允许重复,可以赋予null值
index字段标志MUL
通常把作为查询条件的字段设置为index字段,建表的时候创建索引
index(字段名),index(字段名)……
create table tea4(id char(60) not null, name varchar(4) not null,age int(3) not null,
gender enum(;'boy'.'girl') default 'boy';index(id),index(name));
在已有表创建索引字段
create index 索引名 on 表名(字段名);
删除索引
drop index 索引名 on 表名;
查看索引
show index from 表名 \G;
主键使用规则:
字段值不允许重复,且不允许赋null值
一个表中只能有一个primary key 字段
多个字段都作为主键,称为复合主键,必须一起创建。
主键字段的标志时PRI
主键通常与auto increment 连用
通常把表中唯一标识记录的字段设置为主建[记录编号字段]
建表时创建主键:
create table t10(name char(3) primary key,id int );
desc t10;
insert into t10 values('bob',11);
insert into t10 values('bob',2); 报错 "null",""都不为空
已有字段设置为主键:
alter table 表名 add primary key(字段名列表);
字段不能重复,不为空,否则失败
delete from t6; 删除表的字段值
alter table t6 add primary key(name);
删除主键:
alter table 表名 drop primary key;
与auto_increment 最近一条自增长1
create table t11(id int primary key auto_increment,name char(20);
delete from t11; 也是之前记录最近加一;
复合主键使用:
表中多个字段一起做主键,插入记录时,不允许字段的值同时重复
建表时创建primary key(字段名列表);
在已有的表里创建
alter table 表名 add primary key(字段列表);
create table t14(cip char(22) ,port int,satus enum("boy","gir"),primary key(cip ,port ));
desc t14;
alter table t14 drop primary key; 删复合主键删掉可以重复,不允许为空
delete from t14; 全删表内容这是一种粗爆方法,可以创建复合主键解决重复
外键功能要求:
插入记录时,字段值在另一个字段值范围内选择。
表存储引擎必须是innodb,字段类型要一致
被参考字段必须要是索引类型的一种primay key)
命令格式:
create table 表名(字段名列表,foreign key(字段名)references 表名(字段名)指定外键
on update cascade 同步更新
on delete cascade 同步删除 )engine=innodb; 存储引擎
create table yg(yg_id int primary key auto_increment,name char(15))engine=innodb;
insert into yg(name)values (”bob"),("tom");
create table gz(gz_id int ,pay float(7,2),foreign key(gz_id) references yg(yg_id) on update cascade on delete cascade)engine=innodb;
update yg set yg_id=6 where neme="tom"
alter table gz add primary key (gz_id) 外键字段设置为主键,不允许为空,不重复
删除外键:
show create table gz \G; 查看外键名constraint
alter table 表名 drop foreign key 名称;
数据导入导出:
数据导入:把系统文件的内容存储到数据表里
数据导出:把表记录存储到系统文件里
批量管理数据:
show variables like "%secure_file%"
/var/lib/mysql-files/ 默认
vim /etc/my.cnf 在[mysqld]下面插入修改位置
secure_file_priv="/mysql" #自己创建出来目录
mkdir /mysql 在系统上创建
chown mysql:mysql /mysql
systemctl restart mysqld
show variables like "%secure%";
书局导入步骤:
把系统文件的内容存储到数据库的表里
数据导入步骤默认只有root用户有数据导入权限,建表
create table user(name char(50),password char(1),uid int ,gid int , comment varchar(250),);
system cp /etc/passwd /var/lib/mysql-files/
导入命令格式:
load data infile "目录名/文件名”
into table 库名.表名
fields terminated by “分隔符"
lines terminated by "\n";
例子:
load date infile "/var/lib/mysql-files/passwd" into table db2.user fields terminated by ":" lines terminated by "\n";
添加行号
alter table db2.user add id int primary key auto_increment first;
select * from db2.user;
注意事项:字段分隔要与文件一致,表字段类型和字段个数与文件内容匹配
导入数据时指定文件的绝对路径。
数据导出:
select * from user limit 3 into outfile "/var/lib/mysql-files/user.txt;
select * into outfile “目录名/文件名” [fields terminated by "分隔符“]
[lines terminated by "\n"];
数据导出事项:
导出数据行数由sql查询决定。
导出的是表记录,不包括字段名。
自动创建存储数据的文件。
存储数据文件,具有唯一性。
管理表记录:
插入表记录,查询表记录,更新表记录,删除表记录
insert into user values(30,"bob","x"),("31,"bb","gg");
命令格式3:添加1条记录,给指定字段赋值
insert into 表名(字段名列表)values(“字段值列表”);
命令格式4:添加多条记录,给指定字段赋值
insert into 表名(字段名列表 values (“字段值列表”),(“字段值列表”)
注意事项:
具体如下:
字段值要与字段类型相匹配。
字符类型的字段,要用“”号括起来
依次给所有字段赋值,字段名可以省略。
只给部分字段赋值时,必须明确写出对应的字段名称
没有赋值的字段使用默认或自增长赋值
新纪录追加在末尾
查询表记录:
select 字段1...字段n from 库名.表名;
select 字段1...字段n from 库名.表名 where 条件表达式;
批量更新格式1
update 库名.表名
set 字段名=值,字段名=值,字段名=值;
格式2,满足条件匹配更新
update 库名.表名
set 字段名=值,字段名=值,字段名=值
where 条件表达式;
删除命令格式:
格式1:delete from 库名.表名 where 条件表达式;
格式2:delete from 库名.表名;(慎用)
条件匹配:
基本匹配条件:
数值比较-字段必须时数值类型
类型 比较 例子
= 相等 id=3
> 大于 uid>=3
>= 大于或等于 uid>=3
<= 小于或等于 uid<=3
!= 不等于 uid !=3
select name from user where uid <10;
select * from user where id=1;
字符比较匹配空/非空
字段必须时字符类型
= 相等 name="root"
!= 不相等 name!="root"
is null 空 shell is null
is not null 非空 shell is not null
select name from user where shell = "root";
insert into user(name) values(null),("null"),(""),(NULL);
范围匹配:匹配范围内的任意一个值即可
in(值列表) 在....里...
no in (值列表) 不在.....里....
between 数字 and 数字 在...之间....
select name,uid from user where name in ('muy','ss');
select name,shell from user where shell no in ("/bin/","/va");
select name from user where uid between 15 and 100;
select * from user where id between 10 and 20;
逻辑匹配:多个条件使用
or 逻辑或 条件1 or 条件2 or 条件3
and 逻辑与 条件1 and 条件2 and 条件3
!或 not 逻辑非
select name,uid from user where name ="root" and shell="/bin/";
select name,shell from user where shell != "root";
select name,shell from user where shell not in ("/bin/bash","sbb/bash");
高级匹配条件:
where 字段名 like ’通配符‘
_ 下划线表示1个字符
% 表示0~n个字符
列出name值4个字符的记录
select name from userdb.user where name like "____";
select name from userdb.user where name like "%a%";
正则表达式:
where 字段名 regexp "正则表达式“
正则元字符 ^ $ . [] * |
例子:列出name值以j开头或y结尾的记录
select name from db1.t2 where name regexp "^j|y$";
select name from user where name regexp '[0-9]';
insert into user(name) values ('va'),("dj");
四则运算:数值类型:+ - * / %取余 ()提高优先级
select id,name,uid from user where id <=5;
update user set uid=uid+1 where id <=5;
select name,uid from user where uid % 2 =0;
select name,id ,gid,(uid+gid)/2 pjz from user where name = "halt";
操作查询结果
聚集函数:
avg(字段名) 平均值
sum(字段名) 字段之和
min(字段名) 最小值
max(字段名) 最大值
count(字段名) 字段值个数
select avg(age) from user;
select count(*) from user;
select count(name) from user where name like "__”;
排序,分组,去重显示
排序
sql查询 order by 字段名 [asc| desc];升序|降序
分组
sql 查询 group by 字段名
去重显示
select distinct 字段名 from 表名;
select name,uid from user where uid>=10 order by desc
select shell from user;
select shell from user group by shell;
select shell from user where uid<=500 group gy shell;
select distinct shell from user;
查询结果过滤
having 用法
sql 查询 having 条件表达式;
select name from userdb.user where uid >= 1000 having name="ff"; 效率好
限制查询结果显示行数
sql 查询 limit 数字;
sql 查询 limit 数字1,数字2;
数字1 起始行 0表示第1行
数字2 总行数
select name ,uid ,gid from user limit 3;
select name ,uid,gid from user limit 3,3; 指定范围0开始
MySQL图形管理方式:
自带图形化,web管理
navicat 图形
pypMyAdmin 浏览器 跨平台开源 http://ip
yum -y install httpd php php-mysql
下载phpmyadmin源码包
tar -xf phpmyadmin.tar.gz
mv phpmyadmin /var/www/html/phpmyadmin
cp config.sample.inc.php config.inc.php
vim config.inc.php
$cfg['bowfish_secret']="ssss"
$cfg[server]...=localhost
http://ip:50/php.myadmin
systemctl restart httpd
mysql -hlocalhost -uroot -p1234
http://ip:50/phpmyadmin
用户登录权限:
授权grant :添加用户并设置权限及连接密码
命令格式
grant 权限列表 on 库名 to 用户名@"客户端地址“
identified by "密码”
with grant option ; //新添加用户有授权,可选项
yum -y install mariadb
mysql -h地址 -u用户 -p密码
权限列表:
all 所有权限
usage 无权限
select update insert 个别权限
select update (字段1...字段n) 指定字段
库名:
*.* 所有的库的表
库名.* 一个库
库名.表名 一张表
用户名
授权时自定义要有标识性
存储在MySQL库的user表里
客户端地址
% 所有主机
192.168.1.% 网段内的所有主机
local host 数据库服务器本机
登录用户的使用
select user() ; 显示登录用户及客户短地址
show grants ; 用户显示自身访问权限
show grant for 用户名@”客户端地址“;
管理员查看已有授权用户权限
set password =password("密码“);
授权用户链接后修改链接密码
set password for 用户名@”客户端地址“
=password (密码);
管理员重置授权用户链接密码
drop user 用户名@”客户端地址“
删除授权用户必有管理权限
授权库mysql
user表 记录已有的授权用户及权限
db表 记录已有授权用户对某数据库的访问权限
tables_priv 记录已有授权用户对某个表的访问权限
columns_priv 记录已有授权用户对某个字段的访问权限
查看表记录可以获取用户权限,也可以通过更新表记录,修改用户权限
show database; 查看数据库
select user from mysql.user; 查看MySQL库user表user字段
select user ,host from mysql.user; 查看MySQL库user表user字段host字段
show grants for admin@ip地址; 查看某个用户的授权权限;
select * from mysql.tables_priv; 查看存授权表的字段类型
desc tables_priv; 查看授权表的字段类型
update mysql.tables_priv set Table_priv="select ,create,insert,update" where user="admin" and Host="192.168.1.%"; 修改权限对对应授权表操作,更新字段值前查看表数据类型;
show grants for admin@192.168.1.%; 查看用户的权限信息
show database; 查看有哪些库;
desc mysql.db 查看db表字段类型记录对库的授权信息
select host,user,db from mysql.db; 查看主机用户库
select * from mysql.db where db="db2" \G ;
查看mysql.db表db字段为db2的所有字段信息 ;\G竖直显示
show grants for llk@localhost ;
查看本机登录用户的授权;
update mysql.db set Delete_priv="N" where user="plij";
修改用户对某个库的权限,delete没有开放
flush privileges; 重新加载授权库
show grants for pli@localhost; 查看本机登录用户权限
use userdb; 使用userdb库
show tables; 查看有几张表
desc stuinfo 对stuinfo表查看
desc columns_priv
select * from mysql.columns_priv ; 查看对用户设置表字段的权限信息表
grant select ,update(name,age) on db2.user to admin@l"ocalhost"
identified by "123"; 设置允许本机登录用户admin对db2库下user表查询,和表字段name,age有更新权限;
show grants for admin@localhost 查看用户admin的权限
select * from mysql.columns_priv; 查看用户admin的权限
mysql -uadmin2 -p1234 用户admin登录
show grants; 显示用户的权限
命令撤销权限:
revoke 权限列表 on 库名.表 from 用户名@“客户端”
select host,user from mysql.user; 查看表host,user字段
show grants for admin@192.168.1.%; 查看 admin权限
revoke update,create on db3.user from "admin@192.168.1.%";
撤销db3.user库user表的更新创建的权限;
revoke all on db3.user from "admin@192.168.1.%"; 撤销所有权限;
drop user admin@"localhost" 删掉授权用户
root密码管理:
修改密码:操作系统管理员修改本机数据库管理员密码
mysqladmin -uroot -p[旧密码] password"新密码“ 修改密
恢复线下服务器密码:
具体操作:vim /etc/my.cnf 找到[mysql]下输入
skip-grant-tables
:wq
systemctl restart mysqld
有密码策略前加#注释
mysql
update mysql.user set authentication_string=password("新密码”) where user ="root" and host="localhost"; 更改mysql.user表authentication_string值password("新密码")加密
flush privileges; 刷新
quit;
alter user root@localhost identified by "123"; 修改密码
数据完全备份增量备份
完全备份:备份所有数据(1台服务器上1库的1个表)
增量备份:备份上次备份后,所有新产生的数据
差役备份:备份完全备份后,所有新产生的数据
数据备份方式:
物理备份:cp tar ... 适合整个库备份
cp -r /var/lib/mysql 备份路径目录/mysql.bak
tar -zcvf /root/mysql.tat.gz /var/lib/mysql/*
恢复操作:
cp -r 备份路径目录/mysql.bak /var/lib/mysql/
tar -zxvf /root/mysql.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
逻辑备份:mysqldump //备份sql命令
完全备份:mysqldump -uroot -p密码 库名 > 目录路径/xxx.sql
库名:表示方式
--all-databases 或 -A //所有库
-B 数据库1 数据库2 //多个库
数据库名 //1个库
数据库名 表名 //库的1张表
完全恢复:mysql -uroot -p密码 [库名] < 目录路径/xxx.sql
恢复备份多个库和所有库的sql可以不需加库名恢复;
binlog日志应用
使用binlog日志恢复数据:
二进制日志,mysql服务日志文件的一种,记录除查询之外的所有sql命令
可用于数据备份和恢复
配置mysql主从同步的必要条件
show master status ; 显示当前日志信息默认不启用日志
vim /etc/my.cnf 找到[mysql]在下面插入
log_bin //启用binlog日志
#log_bin=/mysql/lik 自定义目录需创建/mysql目录改权限为mysql所有者所属组权限日志文件存储的自定义目录
server_id=100 //指定id值范围1~255
max_binlog_size=1G //指定日志文件容量,默认1G
:wq
systemctl restart mysqld
show master status; 查看使用的日志
ls /var/lib/mysql 日志文件存储在这里记录非查询sql
手动生成新的日志文件:
systemctl restart mysqld
flush logs;
mysql -uroot -p密码 -e 'flush log'
mysqldump --flush-logs
mysqldump -uroot -p123 --flush-logs 库名 表名 > /root/lik.sql
完全备份数据库到lik.sql,数据库之后产生的数据记录到新日志文件
mysqldump -uroot -p123 --flush-logs -B 库名1 库名2 > /root/two.sql
完全备份数据库到,之后新日志文件名数字后移两位
清理日志:
删除指定编号之前的binlog日志文件
purge master logs to "binlog文件名";
删除所有binlog日志,重新日志
reset master;
使用日志恢复数据
mysqlbinlog 日志文件名 | mysql -uroot -p密码
主机1:mysqlbinlog mysql-bin.000001 | mysql -uroot -p密码
主机1:mysqldump -uroot -p123 db2 user > /root/db2user.sql
主机1:scp /root/db2user.sql root@ip:/root/
主机2:mysql -uroot -p123 db2 < /root/db2user.sql
主机2:select * from db2 user;
主机1:flush logs; 刚完成备份数据刷新日志,产生新日志文件
主机1:insert into db2.user(name,age) values ('ll",'2');
主机1:scp /mylog/lik.000001 root@ip:/root/
主机2:mysqlbinlog /root/lik.000001 | mysql -uroot -p123 日志恢复日志
日志恢复部分数据:
日志格式:
报表格式:statement
行格式:row 默认
混合模式 mixed
show variables like "%binlog_format%" ; //查看日志格式
vim /etc/my.cnf 在[mysql]下插入
binlog_format="mixed“ //修日志格式
:wq
systemctl restart mysqld
show master status ; 查看日志文件状况
update db2.user set age=18 where name like "tom%";
mixed日志如何区分记录sql命令
记录执行时间
记录命令执行的偏移量
mysqlbinlog /root/lik.00001 查看偏移量::要执行sql命令上面最近#at值---sql命令下commit下最近#at值
命令格式:
mysqlbinlog 选项 日志文件名
--start-datetime="yyyy-mm-dd hh:mm:ss" 起始时间
--stop-datetime='yyyy-mm-dd hh:mm:ss" 结束时间
--start-position=数字 起始时间
--stop-position=数字 结束偏移量
mysqlbinlog --start-position=321 --stop-position=342 /root/lik.000001 | mysql -uroot -p123
innobackupex完全备份
物理备份缺点:跨平台差 备份时间长,冗余备份,浪费空间
mysqldump备份缺点:
效率低,备份和还原慢,锁表操作,备份过程,阻塞插入和更新操作
binlog日志缺点:太麻烦
innobackupex工具
一款强大的在线热备份工具:备份过程中不锁表,适合生成环境
由专业组织percona提供(改进mysql分支)
主要有两个组件
xtrabackup:c程序,支持innoDB/XtraDB存储引擎增量备份
innobackupex:以perl脚本封装xtrabackup.,.还支持myisam 完全备份
下载libev-4.15-1.e16.rf.x86_64.rpm
rpm -ivh libev-4.15-1.e16.rf.x86_64.rpm
下载 percona-xtrabackup-24-2.4.7-1.e17.x86_64.rpm
rpm -ivh percona-xtrabackup-24.4.7-1.e17.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.7-1.e17.x86_64.rpm
which innobackupex 命令
完全备份 完全恢复 增量备份 增量恢复
innobackupex <选项>
--hosts 主机名
--user 用户名
--port 端口名
--password 密码
--databases=数据库名
--databases="库1 库2”
--databases="库1.表“
--no-timestamp 不用日期命令备份文件存储的子目录名
--redo-only 日志合并
--apply-log 准备数据恢复
--copy-back 拷贝数据
--incremental 增量备份
--incremental-basedir=目录名 增量备份时,指定上次备份数据存储的目录
--incremental-dir=目录名 准备恢复数据时,指定增量备份数据存储的目录名
--export 导出表信息
import 导入表空间
命令格式:
完全备份
innobackupex --user 用户名 --password 密码 [--databases="数据库名”,不加是全部库] 命令执行自动创建备份的目录名 [--no-timestamp 加上不创建日期目录]
innobackupex --user root -password 123 /allback
完全恢复
rm -rf /var/lib/mysql/*
innobackupex --apply-log 备份目录名 /准备恢复数据
innobackupex --copy-back 备份目录名 //恢复数据
chown -R mysql:mysql /var/lib/mysql/
mysql密码改变
show create table a \G; #engine=innodb 查看存储引擎
恢复单张表
先删除表空间 是表存储数据文件*.ibd记录 .frm为表结构
alter table db4.b discard tablespace;
导出表信息
innobackupex --apply-log --export /opt/allbak/
ls /opt/allback/db4 多出文件为导出
拷贝表信息文件到数据库目录下
cp /opt/allbak/db4/b.{cfg,exp,ibd} /var/lib/mysql/db4/
修改表信息文的所有者及组用户为mysql
chown mysql:mysql /var/lib/mysql/db4/*
导入表空间
alter table db4.b import tablespace;
删除数据库目录下的表信息文件
ls /var/lib/mysql/db4/
rm -rf /var/lib/mysql/db4/b.exp 手动删掉不删影响后期删表
rm -rf /var/lib/mysql/db4/b.cfg 手动删掉不删影响后期删表
查看表记录
增量备份:
增量备份:备份上次备份后,新产生的数据
增量备份时,必须先有一次备份,通常时完全备份
例如:周一完全备份,周二~周日增量备份
完全备份:
innobackupex --user root --password 密码 /fullbak --no-timestamp
增量备份:
innobackupex --user root --password 密码 --incremental /new1dir(系统自建) --incremental-basedir=/fullback(上次备份目录或是完全备份目录) --no-timestamp(不需在目录建日期目录)
ls /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1 数据库事务日志文件
增量:cat /fullback/xtrabackup_checkpoints
from_lsn=上次的to_lsn的数字
to_lsn=数字
last_lsn=数字 //日志序列号和mysql事务日志文件序列号比较,不同就增量备份
innobackupex --user root --password 密码 --incremental /new2dir(系统自建)
--increment-basedir=/new1dir(上次备份目录) --no-timestamp(不需要建时间目录)
拷贝完全备份的数据目录和增量数据目录给新主机实现数据库相同;
备份主机:
systemctl stop mysqld
rm -rf /var/lib/mysqld/*
增量恢复:
innobackupex --apply-log --redo-only /fullbak //先准备恢复后将与增量数据合并数据
innobackupex --apply-log --redo-only /fullbak --incremental-dir=/new1dir //合并数据
innobackupex --apply-log --redo-only /fullbak --incremental-dir=/new2dir //合并数据
innobackupex --copy-back /fullbak //拷贝合并之后数据到数据库
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld