本文共 29220 字，大约阅读时间需要 97 分钟。
use 库名; 切换库show databases; 查库select database 库名; 建库drop database 库名; 删库desc 表名; 查表内容show tables; 查表select * from 表名; 查看表记录create database 表名; 建表drop table 表名; 删表delete from 表名; 删除表记录insert into 表名 values(直) 插入数据update 表名 set 字段=直； 修改表记录Ctrl+C Ctrl+D quit exit 断开连接/etc/my.cnf 主配置文件/var/lib/mysql 数据库目录3306 默认端口号
安装部署 MySQL
[root@mysql-50 ~]# tar xf /mysql-5.7.17.tar[root@mysql-51 /]# yum -y install mysql-commun*[root@mysql-50 ~]# systemctl restart mysqld[root@mysql-50 ~]# systemctl enable mysqld第一次启动时,mysql 会自动为 root 账户配置随机密码,我们需要通过日志查看该密码[root@mysql-50 ~]# grep password /var/log/mysqld.log&Cj#y&9JZ+(6[root@mysql-50 ~]# mysql -uroot -p&Cj#y&9JZ+(6[root@mysql-50 ~]# mysql -uroot -pEnter password:&Cj#y&9JZ+(6mysql> set global validate_password_policy=0; 只验证长度mysql> set global validate_password_length=6; 修改密码长度,默认值是 8 个字符mysql> alter user user() identified by "123456"; 修改登陆密码mysql> show databases;连接 MySQL 数据库的命令语法格式[root@mysql50 ~]# mysql [ -h 服务器 IP 或域名 -u 用户名 -p 密码 数据库名称 ][root@mysql-50 ~]# mysql -uroot -p123456 mysqlmysql> show tables;mysql> show databases; 查看数据库mysql> use lsc; 切换数据库mysql> select database(); 查看当前数据库mysql> create database tts character set utf8; 创建数据库这个可以不打character set utf8; 默认不可以打中文mysql> use tts 切换数据库mysql> drop database tts; 删除数据库数据表相关指令练习mysql> use lsc;mysql> show character set; 查看所有可用编码mysql> create table student( 创建数据库 -> 学号 char(20), char字符类型 -> 姓名 char(20), -> 性别 char(5), -> 手机号 int(11), int数字类型 -> 通信地址 char(50)); 创建数据表mysql> desc student; 查看数据表mysql> insert into student values ( -> 'NSD1812', 中文加单引 -> '小肉肉', -> '女', -> 147258, 不是中文不要加 -> '达内');mysql> select * from student; 查看数据update 数据库名称.数据表名称 set 字段=值 [where 条件]mysql> update school.student set 性别='女'; 更新所有数据mysql> select * from student;mysql> update student set 性别='男' where 姓名='小六';单独改性别mysql> select * from student;删除数据mysql> delete from student where 姓名='小肉肉'; 删除表中满足条件的数据mysql> delete from student; 删除表中所有数据mysql> drop table student; 删除整个数据表mysql> select * from student;MySQL 数据类型数字类型姓名char 年龄tinyint(2)0101101 1 100mysql> create table num( -> id tinyint, -> age int(3), -> score float(4,2));mysql> insert into num values (125,30,95.80);mysql> select * from num;提示值超出范围(tinyint 只能存-128~127 或者 0~255 之间的值)mysql> create table mun( 创建数据表 -> id tinyint unsigned, -> age int(3), -> score float(4,2));mysql> insert into mun values( 插入数据内容 -> 128, -> 11, -> 18);mysql> insert into mun values( 插入数据内容 -> 255, -> 11, -> 18);mysql> select * from mun; 查看数据提示值超出范围(tinyint 只能存-128~127 或者 0~255 之间的值mysql> create table info( 创建数据表 -> name char(4), -> email varchar(30));mysql> desc info; 查看数据mysql> insert into info values( 插入数据内容 -> '阿萨斯', -> 'abc@163.com');mysql> select * from info; 查看数据 注意:字符串需要使用引号1.数字类型2.字符类型3.日期时间类型date 日期类型YYYYmmddhhmmss创建学员信息表:姓名，出生日期，入学年份，上课时间、下课时间mysql> create table zx( 创建数据表 -> name char(5), 姓名是字符型的 -> birth datetime, 生日是日期时间型的 -> start year, 入学年份 -> begin time, 上课时间 -> end time); 下课时间mysql> insert into zx values( 插入数据内容 -> 'tom', 姓名 -> 20181010080700, 日期时间 -> 2019, 入学年份 -> 090000, 上课时间 -> 180000); 下课时间mysql> insert into zx values( -> 'lsc', -> '1998-05-18:04:45:00', -> 2001, -> '09:00:00', -> '18:00:00');mysql> select * from zx;枚举类型(选择类型)enum(值 1,值 2,值 3...) 单选项set(值 1,值 2,值 3...) 多选项mysql> create table tea( -> name char(5), 名字 -> gender enum('boy','girl'), 性别 -> interest set('film','book','play','football','nv')); 电影 书 玩 足球 约束条件
1.常用约束条件:null 允许为空,默认设置not null 不允许为空key 索引类型default 设置默认值,缺省为 NULLmysql> show tables; 查看数据表name 姓名gender 性别age 年龄
mysql> create table re( -> name char(5) not null, -> gender enum("male","female") not null default "male", -> age int(3) not null default 21, -> interet set("book","movie","eat"));mysql> desc re;mysql> insert into re(name) values( -> "tom");mysql> select * from re;mysql> insert into re(name,age) values( -> "ha", -> 28)mysql> select * from re; name 不能为空alter table 表名 执行动作;add 添加字段modify 修改字段类型change 修改自定名称drop 删除字段rename 修改表名称alter table 表名 add 字段名称 类型(宽度) 约束条件;
mysql> alter table re -> add -> email varchar(30);mysql> desc re;mysql> insert into re(name) values ("");mysql> select * re;添加新字段 phone,放到 name 字段的后面mysql> alter table re -> add -> phone varchar(30) not null after name;添加新字段 addr,放到所有字段前面mysql> desc re;mysql> select * from re;mysql> alter table re -> add -> addr varchar(30) not null default 'vf' first;mysql> desc re;mysql> select * from re;modify 修改字段alter table 表名 modify 字段名称 类型(宽度) 约束条件;mysql> alter table re -> modify -> addr var -> addr varchar(30) not null after name;mysql> alter table re -> modify -> addr varchar(50) default 'shanghai' after name;mysql> desc re;mysql> select * from re;chage 修改字段名称alter table 表名 change 字段名称 类型(宽度) 约束条件;mysql> alter table re -> change name myname varchar(10);mysql> desc re; drop 修改字段名称alter table 表名 drop 字段名称mysql> alter table re drop interet;删除数据表中的 interet 字段rename 修改字段名称alter table 表名 rename 新的数据表名称mysql> alter table re rename rest;mysql> use lsc;mysql> show tables; 键值的类型INDEX 普通索引UNIQUE 唯一索引FULLTEXT 全文索引PRIMARY KEY 主键FOREIGN KEY 外键1.INDEX 普通索引一个表中可以有多个 INDEX 字段字段的值允许有重复,切可以赋 NULL 值经常把做查询条件的字段设置为 INDEX 字段INDEX 字段的 KEY 标志是 MULmysql> create table fo( -> id int(6) not null, -> name varchar(5), -> sex enum('male','female'), -> age int(3) default 1, -> index(id),index(name));mysql> desc fo; 1.创建索引: create index 索引名称 on 数据表(字段名称)mysql> create index age on fo(age);mysql> create index nianling on fo(age);提示:可以创建多个索引,索引与字段名称也可以不一样2.删除索引:drop index 索引名称 on 数据表mysql> drop index name on fo;mysql> show index from fo\G; 树着mysql> show index from fo; 横着primary key 主键索引一个表中只能有一个 primary key 字段对应的字段值不允许有重复,且不允许赋 NULL 值如果有多个字段都作为 PRIMARY KEY,称为复合主键,必须一起创建主键字段的 KEY 标志是 PRI通常与 AUTO_INCREMENT 连用经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]mysql> create table qw( -> stu_id char(9), -> name char(5), -> primary key(stu_id));mysql> desc qw;主键就是唯一索引mysql> insert into qw values ('0001','tom');mysql> insert into qw values ('0002','tom');创建主键索引的另一种方式mysql> create table qw1( -> id char(9) primary key, -> name char(5));mysql> desc qw1;对已经存在的数据表创建主键索引mysql> create table qw2( -> id char(9), -> name char(5));mysql> desc qw2;mysql> alter table qw2 add primary key(id); 加primary key(id)mysql> desc qw2;删除数据表中的主键索引mysql> alter table qw2 drop primary key;提示:删除主键后,对应的字段数据就可以出现重复的数据了复合主键索引(多个字段做主键不做主键(可能同一个人技能不同创建符合主键索引(姓名和单位,不能重复,但是单独的姓名或单位可以重复)mysql> create table book( -> 姓名 char(20), -> 达内 char(20), -> 技能 char(100), -> primary key(姓名,单位));mysql> insert into book values('小猪猪','哄一哄',0);mysql> insert into book values('小猪猪','吵架',100000);自动添加属性(可以自动将数据自动加 1 auto_incrementmysql> create table de( -> id int(100) auto_increment primary key, -> name char(10));mysql> insert into de(name) values ('tom');mysql> insert into de(name) values ('qw');mysql> select * from de;mysql> insert into de values (10,'as');mysql> insert into de values(20,'qaz');mysql> select * from de;提示:id 自动不写,默认也会自动加 1提示:当同时有自增长和主键时,一定要通过 alter 将自增长删除后才可以删除主键,无法直接删除主键foreign key 外键一个作者信息表一个图书信息表要求图书表中的图书作者必须是作者表中的作者。什么是外键?让当前表字段的值在另一个表中某个字段值的范围内选择。使用外键的条件:表存储引擎必须是 innodb(默认就是)字段类型必须一致被参照字段必须是索引类型中的一种(primary key)创建外键的语法foreign key(表 A 的字段名称)references 表 B(字段名称)on update casecade 同步更新on delete casecade 同步删除创建数据库mysql> create database press character set utf8;创建包含主键的数据表(作者信息表)mysql> use pressmysql> create table xzz( -> 姓名 char(10) primary key, -> 地址 char(10));mysql> insert into xzz values -> ('小猪猪','深圳'), -> ('小内内','胡南');mysql> select * from xzz;创建图书信息表并创建外键mysql> create table book( -> 书名 char(20), -> 作者 char(10), -> foreign key(作者) references xzz(姓名) on update cascade on delete cascade);mysql> desc xzz;mysql> desc book;mysql> insert into book values('青蛙','小内内');mysql> insert into book values('我要吃肉肉','小猪猪');mysql> select * from book;mysql> delete from press.author where 姓名='小内内';#提示:当作者表中的作者被删除后,图书信息表中对应的数据也会被删除mysql> update xzz set 姓名='xzz' where 姓名='小内内';mysql> select * from xzz;mysql> select * from book;mysql> show create table book\G;删除外键mysql> alter table book drop foreign key book_ibfk_1;mysql> show create table book\G;在现有的数据表中创建外键mysql> create table book( -> 书名 char(20), -> 作者 char(10));mysql> alter table book -> add -> foreign key(作者) references xzz(姓名) on update cascade on delete cascade; 1.数据导入导出
1.1数据导入导出默认检索目录[root@mysql-55 ~]# mysql -uroot -p123456 mysql查看默认检索目录名称mysql> show variables like "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+[root@mysql-55 ~]# cd /var/lib/mysql-files/ 这个目录下是没有东西的修改默认检索目录名称[root@mysql-55 mysql-files]# mkdir /mysql[root@mysql-55 mysql-files]# chown mysql /mysql[root@mysql-55 mysql-files]# vim /etc/my.cnf[mysqld]secure_file_priv="/mysql"[root@mysql-55 mysql-files]# systemctl stop mysqld[root@mysql-55 mysql-files]# systemctl restart mysqld[root@mysql-55 mysql-files]# mysql -uroot -p123456mysql> show variables like "secure_file_priv"; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | secure_file_priv | /mysql/ | +------------------+---------+mysql> quiu
1.2数据导入：把系统文件的内容存储到数据库的表里命令格式mysql> load data ifile "目录名/文件名" into table 数据库名.表名 FIELDS TERMINATED BY "分隔符" LINES TERMINATED BY "\n";步骤1.把系统文件拷贝到检索目录下2.根据导入文件的内容创建表3.执行数据导入命令步骤[root@mysql-55 ~]# cp /etc/passwd /mysql[root@mysql-55 ~]# mysql -uroot -p123456mysql> create database usedb;mysql> use usedbmysql> create table user( -> username char(50), 名字 -> password char(1), 密码 -> uid int, 用户 -> gid int, 组 -> comment char(150), -> homedir char(180), 家目录 -> shell char(50));mysql> desc user;mysql> load data infile "/mysql/passwd" into table user -> fields terminated by ":" lines terminated by "\n"; 导入mysql> alter table user add -> id int primary key auto_increment first;修改表结构添加表字段mysql> select * from user;
1.3数据导出：把数据库的表里的记录存放到系统文件命令格式mysql> SQL查询 into outfile "目录名/文件名";mysql> SQL查询 into outfile "目录名/文件名" -> fields terminated by "列分隔符" -> lines terminated by "行分隔符";mysql> select * from user into outfile "/mysql/a1.txt";[root@mysql-55 ~]# cd /mysql/[root@mysql-55 mysql]# lsa1.txt passwd[root@mysql-55 mysql] # vim a1.txtmysql> select username,shell from user into outfile "/mysql/a2.txt";[root@mysql-55 mysql]# lsa1.txt a2.txt passwd[root@mysql-55 mysql]# vim a2.txtmysql> select username,shell from user where id<=3 into outfile "/mysql/a3.txt" -> fields terminated by "#" lines terminated by "???";[root@mysql-55 mysql]# lsa1.txt a2.txt a3.txt passwd[root@mysql-55 mysql]# vim a3.txtmysql> select username,shell from user where id<=3;+----------+---------------+| username | shell |+----------+---------------+| root | /bin/bash || bin | /sbin/nologin || daemon | /sbin/nologin |+----------+---------------+
2.管理表记录
2.1插入表记录insert into 向表中插入1条记录给所有字段赋值mysql> insert into 库名.表名 valuse(字段值列表);mysql> insert into usedb.user values( -> 43,"bob","x",2000,2000,"test user","/home/bob","/bin/bash");mysql> select * from user; 向表中插入1条记录给个别字段赋值mysql> insert into 库名.表名(字段名列表)values(值列表);mysql> insert into user -> (username,shell) values("jack","/sbin/nologin");mysql> desc user;mysql> select * from user; 向表中插入多条记录给所有字段赋值mysql> insert into 库名.表名 valuse (字段值列表),(字段值列表),(字段值列表);mysql> insert into user values -> (443,"lili","x",2000,2000,"test user","/home/bob","/bin/bash"), -> (44,"lucy","x",2000,2000,"test user","/home/bob","/bin/bash"); 向表中插入多条记录给个别字段赋值mysql> insert into 库名.表名(字段名列表) -> values(值列表),(值列表),(值列表);mysql> insert into user(username,shell) -> values -> ("jack","/sbin/nologin"), -> ("qw","/sbin/nologin"), -> ("as","/sbin/nologin");mysql> select * from user; 2.2查询表记录命令格式mysql> select 字段名列表 from 库名.表名 [where 条件];mysql> select * from user;mysql> select * from user where id <=2;
2.3更新表记录 updateupdate 库名.表名 set 字段名=值，字段名=值 [where 条件];mysql> update user set password="a";mysql> select * from user where id <=2;mysql> update user set password="x" where username="root";mysql> select username,password from user;
2.4删除表记录delete from 库名.表名[where 条件];mysql> select * from userdb.user where username="bob";mysql> delete from userdb.user;
3.匹配条件
3.1基本匹配条件 数值比较 > >= < <= = != where 字段名 符号 数字mysql> select * from user where id=10;mysql> select username,uid from user where uid<=10;mysql> select username,uid,gid from user where uid=gid; 字符比较 = != where 字段名 符号 "字符串"；mysql> select username,shell from user where shell!="/bin/bash";mysql> select username from user where username="root"; 匹配空/非空 where 字段名 is null; where 字段名 is not null;mysql> select username,gid from user where gid is null;mysql> select username,gid from user where gid is not null; 逻辑匹配 (多个判断条件) and 逻辑与 多个判断条件必须同时成立 or 逻辑或 多个判断条件某一条件成立即可 ! 或 not 逻辑非 取反mysql> select * from user where username="bin" and uid=3 and shell="/bin/bash";mysql> select * from user where username="root" and uid=0;mysql> select * from user where username="bin" or uid=3 or shell="/bin/bash" 范围内匹配/去重显示 in 在...里 not 不在...里 between ....and... 在...之间 disinct 去重显示mysql> select username from user -> where username in ("adm","sync","root","mysql");mysql> select username,uid from user -> where uid in (11,29,1000,33,7);mysql> select username,shell from user -> where shell not in ("/bin/bash","/sbin/nologin");mysql> select * from user where id between 5 and 10;mysql> select distinct shell from user;mysql> select distinct gid from user; () 提高判断优先级 3.2高级匹配条件 模糊查询 like where 字段名 like '通配符'; _ 匹配单个字符 % 匹配0~N个字符mysql> select username from user where username like '____';mysql> select username from user where username like '%a%';mysql> select username from user where username like '__%__'; 正则表达式 元字符 ^ $ . * [] where 字段名 regexp '正则表达式’;mysql> select username , uid from user where uid regexp '^....$';mysql> select username from user where username regexp 't$';mysql> select username from user where username regexp '^r.*t$';mysql> insert into user(username) -> values("2yaya"),("ya5ya"),("yay8a"),("yaya7");mysql> select username from user where username regexp '[0-9]';mysql> select username from user where username regexp '^r.*t$'; 没有mysql> select username from user where username regexp '7$'; 四则运算 计算符号 + - * / %mysql> select * from user where id <=5;mysql> update user set gid=gid+1 where id <=5;mysql> select * from user where username="root";mysqp> select username,uid+gid from user where username="root";mysql> select username,uid+gid xjz from user where username="root";mysql> alter table user add age tinyint default 29 after username;mysql> select username,age from user;mysql> select username,2019-age csnf from user where username="root"; () 提高执行的优先级mysql> select username,uid,gid,(uid+gid)/2 pjz from user where username="bin"; 操作查询结果 聚集函数(mysql服务软件自带的对数据做统计的命令) avg(字段名) 统计字段平均值 sum(字段名) 统计字段之和 min(字段名) 统计字段最小值 max(字段名) 统计字段最大值 count(字段名) 统计字段值个数mysql> select avg(uid) from user;mysql> select avg(uid) from user where id <=10;mysql> select sum(uid) from user;mysql> select sum(uid) from user where id <=10;mysql> select min(uid) from user where shell!="/bin/bash";mysql> select max(uid) from user where shell!="/bin/bash";mysql> select max(uid) from user;mysql> select username from user where shell!="/bin/bash";mysql> select count(username) from user where shell!="/bin/bash";符合mysql> select count(username) from user;mysql> select count(*) from user; 查询结果排序 order by SQL查询 order by 字段名 [ asc | desc ]; 升询 降询mysql> select username,uid from user where uid between 10 and 1000;mysql> select username,uid from user -> where uid between 10 and 1000 -> order by uid; 升询mysql> select username,uid from user -> where uid between 10 and 1000 -> order by uid desc; 降询 查询结果分组 group by 字段名mysql> select shell from user group by shell;mysql> select shell from user where uid <=1000 group by shell; 查询结果过滤 having SQL查询 having 条件表达式; SQL查询 where 条件 having 条件表达式; SQL查询 group by 字段名 having 条件表达式;mysql> select username from user where shell!="/bin/bash";mysql> select username from user where shell!="/bin/bash" -> having username="rsync";mysql> select username from user where shell!="/bin/bash" -> having username="gdm";mysql> select username from user having username="gdm";mysql> select username from user where username="gdm";mysql> select username from user where uid <=1000 having username="gdm";mysql> select username from user where uid <=1000 and username="gdm";mysql> select shell from user where shell!="/bin/bash";mysql> select shell from user where shell!="/bin/bash" group by shell;mysql> select shell from user where shell!="/bin/bash" group by shell having shell="sync";没有mysql> select shell from user where shell!="/bin/bash" group by shell having shell="/bin/sync"; 限制查询结果显示行数limit SQL查询 limit N; 显示查询结果前N条记录 SQL查询 limit N,M; 显示指定范围内的查询记录mysql> select * from user where uid >=10 and uid<=500;mysql> select * from user where uid >=10 and uid<=500 limit 2;mysql> select id,username,homedir from user where uid >=10 and uid<=500;mysql> select id,username,homedir from user where uid >=10 and uid<=500 limit 0,2;mysql> select id,username,homedir from user where uid >=10 and uid<=500 limit 3,4;mysql> select username,uid,homedir from user -> where -> uid >1000 order by uid desc -> limit 3; 4.MySQL存储引擎
MySQL存储引擎介绍 MySQL体系结构 (8个组件) 管理工具： MySQL自带的命令 装包 连接池： 验证用户连接和检查系统资源 SQL接口： 把sql命令传给mysqld进程处理 分析器： 检查sql命令语法 优化器： 对执行的sql命令优化 查询缓存： 存储查找过的数据(8MB 从物理内存划分出来的) 存储引擎：处理表的处理器，不同的存储引擎有不同的功能和数据存储方式 文件系统 ： 机器的硬盘 查看存储引擎 查看已有表的使用存储引擎mysql> use usedb;mysql> show create table 表名\G;mysql> show create table user\G; 查看数据库服务默认使用的存储引擎mysql> show engines\G; 修改存储引擎 修改数据库服务默认使用的存储引擎[root@mysql-55 ~]# systemctl stop mysqld[root@mysql-55 ~]# vim /etc/my.cnf[mysqld]default-storage-engine=myisam[root@mysql-55 ~]# systemctl restart mysqld[root@mysql-55 ~]# mysql -uroot -p123456mysql> show engines;| MyISAM | DEFAULTmysql> use usedb;mysql> create table t2(id int);mysql> create table t3(id int);mysql> show tables;mysql> show create table t3\G; 修改表使用的存储引擎[root@mysql-55 ~]# cd /var/lib/mysql[root@mysql-55 mysql]# cd usedb/[root@mysql-55 usedb]# mysql -uroot -p123456mysql> show engines;mysql> use usedb;mysql> create table t4(age int);mysql> show tables;mysql> show create table t4;mysql> create table t5(age int);mysql> show tables;mysql> create table t6(age int)engine=innodb;2个mysql> show tables;mysql> create table t7(age int)engine=memory;1个 建表时指定表使用的存储引擎mysql> show tables;mysql> show create table t7\G;mysql> alter table t7 engine=myisam;多了2个文件mysql> show tables;mysql> create table t8(name char(10))engine=innodb;2个mysql> show create table t8\G; 常用存储引擎特点 myisam存储引擎特点 支持表级锁 不支持外键、事务、事务回滚 表文件个数 表名.frm 表名.MYD 表名.MYI 表结构 数据 index信息 innidb存储引擎特点 支持外键 支持行级锁 支持事务和事务回滚 事务特点 表文件个数 表名.frm t6.ibd 表结构 数据+index信息 工作中建表时，如何决定表使用那种存储引擎 术语解释 外键 行级锁:只锁定某一行 表级锁:直接对整张表进行加锁 事务 登陆 数据操作 断开连接 事务回滚 ：在访问过程中，任意一步操作失败，恢复之前的所有操作 事务日志文件：记录对所有innodb存储引擎表的操作[root@mysql-55 ~]# cd /var/lib/mysql[root@mysql-55 mysql]# lsibdata1 ib_logfile0 ib_logfile1没有提交的命令 已经提交的命令 事务特性：ACID 原子性 一致性 隔离性 持久性mysql> show variables like "autocommit"; 查看提交状态mysql> commit; 提交数据mysql> rollback; 数据回滚mysql> set autocommit=off; 关闭自动提交mysql> select * from t6; 终端1mysql> delete from t6;mysql> insert into t6 values(999);mysql> show variables like "autocommit";mysql> set autocommit=off;mysql> select * from t6;mysql> delete from t6;mysql> select * from t6;mysql> use usedb; 终端2mysql> select * from user;mysql> select * from t6; 工作中建表时，如何决定表使用那种存储引擎？ select 操作多的表 ，适合使用myisam存储引擎 优点节省系统资源 insert/update/delete 操作多的表 ,适合使用innodb存储引擎 优点并发访问量大
1.相关概念
数据备份方式 物理备份 直接拷贝库或表对应系统文件 逻辑备份 使用备份命令或软件对数据做备份,生成对应的备份文件 数据备份策略 完全备份： 备份所有数据 （ 1张表 、1个库 、1台数据库服务器） 备份新产生的数据 差异备份： 备份完全备份后，所有新产生的数据 增量备份 : 备份上次备份后，所有新产生的数据 数据物理备份 和 数据物理恢复
mysql> create database db5;mysql> create table db5.a select * from mysql.user;mysql> create table db5.b select * from mysql.user;mysql> create table db5.c select * from mysql.user;mysql> use db5;mysql> show tables;[root@mysql-50 ~]# ls /var/lib/mysql[root@mysql-50 ~]# mkdir /mybak[root@mysql-50 ~]# cp -r /var/lib/mysql /mybak/mysql.bak[root@mysql-50 ~]# ls /mybak/mysql.bak/[root@mysql-50 ~]# tar -zcvf /mybak/mysql.tar.gz /var/lib/mysql/*[root@mysql-55 ~]# systemctl stop mysqld[root@mysql-55 ~]# rm -rf /var/lib/mysql/*[root@mysql-50 ~]# scp -r /mybak/mysql.bak root@192.168.4.55:/[root@mysql-55 ~]# cp -r /mysql.bak/* /var/lib/mysql/[root@mysql-55 ~]# ls /var/lib/mysql[root@mysql-55 ~]# ls -l /var/lib/mysql/[root@mysql-55 ~]# chown -R mysql:mysql /var/lib/mysql[root@mysql-55 ~]# ls -l /var/lib/mysql/[root@mysql-55 ~]# systemctl restart mysqld[root@mysql-55 ~]# mysql -uroot -p123456mysql> show databases;
2.mysqldump 数据完全备份 备份命令用法[root@mysql-50 ~]# which mysqldump/usr/bin/mysqldump[root@mysql-52 ~]# rpm -qf /usr/bin/mysqldumpmysql-community-client-5.7.17-1.el7.x86_64[root@mysql-50 ~]# mysqldump --help[root@mysql-50 ~]# man mysqldump[root@mysql-50 ~]# mysqldump -uroot -p 密码 库名 > 路径/xxx.sql 数据完全恢复命令用法[root@mysql-50 ~]# mysql -uroot -p密码 [库名] < 路径/xxx.sql[root@mysql-50 ~]# mysqldump -uroot -p123456 --all-databases[root@mysql-50 ~]# mysqldump -uroot -p123456 --all-databases > /mysql/mysql-all.sql[root@mysql-50 ~]# mysqldump -uroot -p123456 -A > /mysql/mysql-all.sql[root@mysql-50 ~]# vim /mysql/mysql-all.sql[root@mysql-50 ~]# mysqldump -uroot -p123456 userdb user > /mysql/mysql-all.sql[root@mysql-50 ~]# mysqldump -uroot -p123456 db4 > /mysql/db4.sql[root@mysql-50 ~]# scp /mybak/userdb_user.sql root@192.168.4.52:/root/[root@mysql-50 ~]# scp /mybak/db4.sql root@192.168.4.52:/root/ 备份数据[root@mysql-52 ~]# mysql -uroot -p123456mysql> show databases;[root@mysql-52 ~]# mysqldump -uroot -p123456 > /mybak/mysql-all.sql[root@mysql-52 ~]# mysqldump -uroot -p123456 userdb user > /mybak/userdb_user.sql[root@mysql-52 ~]# mysqldump -uroot -p123456 db4 > /mybak/db4.sql 数据恢复 [root@mysql-52 ~]# mysql -uroot -p123456mysql> create database userdb;mysql> create database db4;[root@mysql-52 ~]# mysql -uroot -p123qqq...A userdb < /root/userdb_user.sql[root@mysql-52 ~]# mysql -uroot -p123qqq...A db4 < /root/db4.sql[root@mysql-52 ~]# mysql -uroot -p123456mysql> select * from userdb.user;mysql> use db4;mysql> show tables; 数据恢复[root@mysql-52 ~]# mysql -uroot -p123qqq...A < /root/twodb.sql[root@mysql-52 ~]# mysql -uroot -p123456mysql> show databases;
使用mysqldump 做数据完全备份与恢复的优点与缺点？实时增量备份/恢复-----使用mysql服务自带的binlog日志文件实现的 mysql服务binlog日志的管理 binlog日志介绍?[root@room9pc01 桌面]# scp percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm libev-4.15-1.el6.rf.x86_64.rpm root@192.168.4.50:/ 启用binlog日志[root@mysql-50 ~]# vim /etc/my.cnf[mysqld]server_id=50log-bin[root@mysql-50 ~]# systemctl restart mysqld[root@mysql-50 ~]# cd /var/lib/mysql/[root@mysql-50 ~]# ls *-bin*mysql50-bin.000001 mysql50-bin.index[root@mysql-50 ~]# vim /etc/my.cnf[mysqld]server_id=50log-bin=/logdir/plj[root@mysql-50 ~]# mkdir /logdir[root@mysql-50 ~]# chown mysql /logdir/[root@mysql-50 ~]# systemctl restart mysqld[root@mysql-50 ~]# ll -d /logdir/ 管理binlog日志文件 手动创建新的日志文件[root@mysql-50 ~]# systemctl restart mysqld[root@mysql-50 ~]# mysql -uroot -p123456 -e "show databases"[root@mysql-50 ~]# mysql -uroot -p123456 -e "flush logs"[root@mysql-50 logdir]# ls 删除已有的日志文件mysql> show master status; 显示正在使用日志文件信息mysql> purge master logs to "plj.000005";删除mysql> reset master;删除mysql> show master status;mysql> flush logs;mysql> show master status; 查看日志文件内容[root@mysql-50 ~]# mysqlbinlog [选项] binlog日志文件名[root@mysql-50 ~]# mysqlbinlog /logdir/plj.000001 常用选项--start-datetime="yyyy-mm-dd hh:mm:ss"--stop-datetime="yyyy-mm-dd hh:mm:ss"--start-position=数字--stop-position=数字查看mysql> show variables like "binlog_format";+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+修改[root@mysql-50 ~]# vim /etc/my.cnf[mysqld]server_id=50log-bin=/logdir/pljbinlog_format="mixed"[root@mysql-50 ~]# systemctl restart mysqld[root@mysql-50 ~]# mysql -uroot -p123456mysql> show variables like "binlog_format";mysql> reset master;mysql> show master status;mysql> show databases;mysql> create database bbsdb;mysql> create table bbsdb.user( -> name char(10), -> password char(6));mysql> desc bbsdb.user;mysql> select * from bbsdb.user;mysql> insert into bbsdb.user values("bob","abc123");mysql> insert into bbsdb.user values("tom","123123");mysql> insert into bbsdb.user values("jim","654321");mysql> select * from bbsdb.user;[root@mysql-50 ~]# mysqlbinlog /logdir/plj.000001 | grep -i create[root@mysql-50 ~]# mysqlbinlog /logdir/plj.000001 | grep -i select[root@mysql-50 ~]# mysqlbinlog /logdir/plj.000001 | grep -i desc[root@mysql-50 ~]# mysqlbinlog /logdir/plj.000001 | grep -i insert 日志记录sql命令方式 时间点：记录命令执行的时间 偏移量：记录命令的长度 使用binlog日志恢复数据 命令用法[root@mysql-50 ~]# mysqlbinlog [选项] 日志文件 | mysql -uroot -p密码[root@mysql-50 ~]# scp plj.000001 root@192.168.4.52:/root/[root@mysql-52 ~]# mysqlbinlog /root/plj.000001 | [root@mysql-52 ~]# mysql -uroot -p123qqq...Amysql -uroot -p123qqq...Amysql> show databases;mysql> select * from bbsdb.user; 读日志指定范围的sql命令， 恢复数据mysql> insert into bbsdb.user values("tom","123123");mysql> insert into bbsdb.user values("jim","654321");mysql> select * from bbsdb.user;mysql> delete from bbsdb.user where name in ("jack","alice");mysql> select * from bbsdb.user;[root@mysql-50 ~]# mysqlbinlog --start-position=1517 --stop-position=1955 /root/plj.000001 | mysql -uroot -p123456mysql> select * from bbsdb.user;3.innobackupex 软件介绍 ？安装软件[root@mysql-50 ~]# yum -y install libev-4.15-1.el6.rf.x86_64.rpm[root@mysql-50 ~]# innobackupex --help[root@mysql-50 ~]# man innobackupex命令用法 [root@mysql-50 ~]# innobackupex <选项> 选项说明？ --user --password --no-timestamp 数据备份与恢复 数据完全备份与恢复mysql> show engines;[root@mysql-50 ~]# vim /etc/my.cnf[mysqld]server_id=50log-bin=/logdir/pljbinlog_format="mixed"#default-storage-engine=myisam[root@mysql-50 ~]# systemctl restart mysqldmysql> show engines;mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+mysql> create database db5;mysql> create table db5.a(id int);mysql> insert into db5.a values(99); X5mysql> select * from db5.a;+------+| id |+------+| 99 || 99 || 99 || 99 || 99 |+------+mysql> show databases; 备份数据[root@mysql-50 ~]# innobackupex --user root --password 123456 /allbak[root@mysql-50 ~]# ls /allbak[root@mysql-50 ~]# ls /allbak/2019-04-12_11-40-04/[root@mysql-50 ~]# innobackupex --user root --password 123456 /allbak --no-timestamp 第1次备份所有的数据 （完全备份）[root@mysql-50 ~]# innobackupex --user root --password 123qqq...A fullbak --no-timestamp[root@mysql-50 ~]# ls /fullbak/mysql> insert into db5.a values(666); 写10条 第2次备份新产生的数据 （增量备份）[root@mysql-50 ~]# innobackupex --user root --password 123qqq...A --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp[root@mysql-50 ~]# ls /new1dir/mysql> insert into db5.a values(555); 写10条 第3次备份新产生的数据 （增量备份）[root@mysql-50 ~]# innobackupex --user root --password 123qqq...A --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp 恢复恢复准备恢复数据把备份文件拷贝到数据库目录下修改目录的所有者和组为mysql启动服务管理员登陆查看数据[root@mysql-50 ~]# systemctl stop mysqld[root@mysql-50 ~]# rm -rf /var/lib/mysql/*[root@mysql-50 ~]# innobackupex --apply-log /allbak[root@mysql-50 ~]# chown -R mysql:mysql /var/lib/mysql[root@mysql-50 ~]# systemctl restart mysqld[root@mysql-50 ~]# mysql -uroot -p123456mysql> show database;mysql> select * from db5.a; 数据增量备份与恢复增量备份[root@mysql-50 ~]# rm -rf /fullbak/ ; rm -rf /new1dir/ ; rm -rf /new2dir/备份db5库[root@mysql-50 ~]# innobackupex --user root --password 123qqq...A --databases="db5" /db5bak --no-timestamp[root@mysql-50 ~]# ls /db5bakmysql> drop table db5.b; 恢复b表 步骤如下： 1 创建表b2 删除b表 表空间文件3 在备份数据里导出表信息4 把导出的表信息文件拷贝到数据库目录下5 修改表信息文件的所有者和组用户为mysql6 导入表信息7 查看表记录
mysql> create table db5.b(name char(10));mysql> alter table db5.b discard tablespace; [root@mysql-50 ~]# innobackupex --apply-log --export /db5bak/mysql> system cp /db5bak/db5/b.{cfg,exp,ibd} /var/lib/mysql/db5/mysql> system chown mysql:mysql /var/lib/mysql/db5/b.*mysql> alter table gamedb.a import tablespace; mysql> select * from db5.b;mysql> select * from db5.a;mysql> system rm -rf /var/lib/mysql/db5/b.cfgmysql> system rm -rf /var/lib/mysql/db5/b.expmysql> insert into db5.b values("tom"); 转载地址：http://glnwi.baihongyu.com/