MySQL数据库操作

一、简介

Mysql是什么？

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
MySQL是将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
由于其社区版的性能卓越，搭配 PHP 和 Apache可组成良好的开发环境

数据库简介：

　　是数据结构化的结合。电子化的文件柜，数据库是指以一定的方式存储在一起的数据的集合。

　　数据库的组成：

　　　　数据库由表组成，数据库映射到系统中是一个目录
　　　　表是由相关数据组成，表映射到系统中是文件
　　　　记录是由一定数量不同属性的字段组成的

　　数据库管理系统是为了管理数据库而设计的计算机软件，一般情况下有存储、截取、维护及备份等功能。

数据库的分类：

　　关系型数据库

　　　　是建立在关系模型基础上的数据库

　　　　常见的关系型数据库：
　　　　　　MySQL：开放源代码
　　　　　　MariaDB：Mysql的替代品
　　　　　　Oracle：闭源
　　　　　　SQL Server：微软的
　　　　　　DB2：IBM
　　　　　　Access：微软公司

　　　　非关系型数据库：
　　　　　　与关系型数据库最明显的区别：不再使用SQL作为查询语言
　　　　　　MongoDB
　　　　　　hadoop

Mysql的结构:

　　C/S结构：客户端/服务器结构客户端和服务器端都的装软件

　　CentOS7:
　　　　Server：mariadb-server
　　　　Client ：mariadb

　　RedHat6：
　　　　Server：mysql-server
　　　　Client ：mysql

二、登录、帮助

　　MySQL默认情况下支持匿名登陆

匿名登陆

# mysql

给root用户设置密码

# mysqladmin -u root password
New password: 
Confirm new password:

本地登陆：

# mysql -u root -p123456
注意：-p后面一定没有空格

远程登陆：

# mysql -u root -h 192.168.1.2 -p123456

帮助：

　　　官方网站：http://www.mysql.com
　　　　　　　　 http://dev.mysql.com/doc

MySQL获取客户端帮助

MariaDB [(none)]>help; 或者 ？ 或者 \h 
MariaDB [(none)]> create databases
-> \c 清除当前输入的sql语句，注意一定要在没敲分号的时候使用
MariaDB [(none)]> exit 退出 quit \q 

MariaDB [(none)]> use farm; 切换数据库
MariaDB [(none)]> \u test

MariaDB [(farm)]> source qqfarm.sql; 执行sql脚本
MariaDB [test]> \. qqfarm.sql

MariaDB [(none)]> system ls; 调用系统命令
MariaDB [(none)]> \! ls;

忘记MySQL密码

修改配置文件

#vim /etc/my.cnf

[mysqld]
skip-grant-table //添加 跳过权限表
datadir=/var/lib/mysql

重启服务

# systemctl restart mariadb.service

登陆mysql

# mysql

MariaDB [(none)]> select user,password,host from mysql.user; //查看mysql库中user表的user、password、host三个字段
+------+-------------------------------------------+-----------------+
| user | password | host |
+------+-------------------------------------------+-----------------+
| root | *23AE809DDACAF96A | localhost |
| root | | ula.example.com |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | ula.example.com |
+------+-------------------------------------------+--------------------+

MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost"; //更新密码
MariaDB [(none)]> exit
Bye

将配置文件中跳过权限表的选项删除或注释

#vim /etc/my.cnf

[mysqld]
#skip-grant-table 
datadir=/var/lib/mysql

重启服务

# systemctl restart mariadb.service

三、基本SQL语句

基本的sql语句：

　　SQL语句中的大小写问题：
　　　　1）关键字、函数名、列名和索引名不区分大小写
　　　　2）数据库的名字、表名、别名区分大小写的(因为linux是区分大小写的)

库的操作

#查看所有库
show databases;

#创建库
create database 库名;

#创建库并指定字符集
create database 库名 DEFAULT CHARACTER SET utf8;

#库的字符集
alter database databasename DEFAULT CHARACTER SET utf8;

#查看库的属性　　　　　 
show create database 库名;

#切换库
use apple;

#删除库
drop database apple;

#查看现在使用的库
select database();

表的操作

#创建表
create table t1(id int,name char(10));

#查看表的结构
desc t1;

#查看当前库中所以表
show tables;  

#查看表的属性
show create table t1\G;

#添加字段
alter table 表名 操作 字段名 数据类型 [约束];

#追加
alter table t1 add TEL int(11);

#在name字段后面插入sex字段
alter table t1 add sex char(10) after name;

#添加字段在第一列
alter table t1 add UID int(10) first;

#修改字段名及属性
alter table t1 change id ID int(5);

#仅修改字段属性
alter table t1 modify ID int(10);

#删除字段
alter table t1 drop UID;  

#修改表名
alter table t1 rename to t2;  

#删除表
drop table nametable;

记录的操作

#添加记录
insert into t1 set id=2,name='jim';
insert into t1 (id,name) values(3,'tom');

#添加多条记录
insert into t1 (id,name) values(8,'harry'),(9,'kitty');
insert into t1 values (5,'kiki'),(6,'jack'),(7,'banana');

#所有字段都要有数据
insert into t1 values (4,'apple');

#更新记录
update t2 set age=18 where name is NULL;

#满足where后面的条件做什么事
update info_name set TEL="456789123" where NUM=2 ;

#多条件定位，&&=and 两个条件都满足
update info_name set SEX="m" where NUM=2 && AGE=18 ; 

#||=or  任意满足其一条件
update info_name set SEX="m" where NUM=3 || AGE=18;

#删除记录
delete from info_name where NAME='tom' and SEX='m';    
delete from info_name where SEX is null;

#清空表记录
delete from info_name;

四、存储引擎

mysql的常用存储引擎

innodb：在事务上具有优势，即支持提交、回滚及崩溃恢复能力等事务特性，所以比myisam存储引擎占用更多的磁盘空间。因此当需要频繁更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。

　　myisam：不支持事务，也不支持外键，所以访问速度快。当对事务的完整性没有要求并以访问为主的应用适合使用该存储引擎。

　　　　　　myisam存储引擎的目录结构

1. 1. .frm 存储的是表结构
  2. .MYD 存储的是表数据
  3. .MYI 存储的是表的索引
  4. db.opt 该文件主要用于存储当前数据库的默认字符集和字符集规则。

NDB存储引擎：memory存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障，适合用于需要快速访问的临时表。

重启服务之后，数据丢失，一般用于临时表。

查看当前数据库软件支持的所有存储引擎

MariaDB [(none)]> show engines\G;

查看默认使用存储引擎

MariaDB [(none)]> show variables like 'storage_engine%';

设置表的存储引擎

在建表时指定存储引擎

MariaDB [(none)]> create table test.t2(id int ,name char(10)) engine=myisam;

永久修改默认存储引擎

修改配置文件

# vim /etc/my.cnf

[mysqld]

default-storage-engine=MYISAM

重启服务生效

 # systemctl restart mariadb

对于已经创建的表，修改存储引擎

alter table test.t3 engine=innodb;

五、函数

基本函数

#database()     查看当前所在的库
select database(); 

#user()     查看当前登陆的用户
select user();

聚合函数

#sum()    求和函数
select sum(age) from t1;

#avg()    平均值函数
select avg(age) from t1;

#max()    最大值函数
select max(age) from t1;

#min()    最小值函数
select min(age) from t1;

#count()    统计sex字段每种有多少条记录
select sex,count(sex) from t1 group by sex;


#统计一下mysql成绩在80分以上的人数
select mysql,count(*) from score where mysql>80;

#统计表中有多少条非空的记录
select count(*) from pass;

时间函数

#查看当前时间
select curtime();

#查看当前日期
select curdate();

#查看系统时间
select now();
select sysdate();

其他函数

#concat()    连接函数
select concat(username,uid) from pass;
select concat(username,' ',uid) from pass;
select concat(username,':',uid) from pass;
select concat(username,':',uid) as 'username-uid' from pass;

#lower()    转换成小写
update pass set username='ROOT' where username='root';
select lower(username) from pass;

#upper()    转换成大写
select upper(username) from pass;

#length()    求长度 
select username,length(username) from pass;

六、记录与表的复制

　　要求：有两张表，但是字段数不同

准备表

MariaDB [test]> create table t1(id int primary key,name varchar(20));

MariaDB [test]> insert into t1 values (1,'jim'),(2,'tom'),(1000,'jack');

记录的复制

#复制全部记录
MariaDB [test]> insert into user1(id,name) select * from user;

#复制部分记录
insert into user2(id,name) select * from user where id<1000;

复制表

复制部分表（不可以复制主键、外键和索引）

MariaDB [test]> create table user3 select * from user;

完全复制表（可以复制表的结构）

MariaDB [test]> create table user4 select name from user;

只复制表的结构

　　方法一：不复制主键、外键、索引

MariaDB [test]> create table user5 select * from user where 1<0;

　　方法二：完全复制

MariaDB [test]> create table user6 like user;
MariaDB [test]> select * from user6;

七、数据导入和导出

导入数据

　　语法：load data infile '文件名' into table 表名;（分割符为空格后者制表符）

load data infile "/opt/test.txt" into table test;

　　注意：1）文件不要放在/tmp和/root目录下，MySQL读取不到。

　　　　　2）mysql用户对导入的文件只要需要读的权限

　　　　　3）文件用引号引起来

　　语法：load data infile '文件名' into table 表名 fields terminated by '分隔符'；（任何分隔符）

load data infile '/opt/pass.txt' into table pass fields terminated by ':';

导出数据

select *from passwd into outfile '/test/pass' fields terminated by ':';

　　注意：1）导出前文件名一定不存在。

　　　　　2）mysql用户对目录要有读写权限

　　　　　3）导出文件默认以|t为字段分隔符，\n为行分隔符

八、约束

约束也叫完整性约束

　　约束：为了保证数据的正确性和相容性，对关系模型提出的某些约束条件或者规则
　　完整性：是指数据库中存放的数据是有意义的、正确的

　　　　注意：约束一般用于字段上

约束有哪些？

　　非空、唯一、默认值、主键、外键、自增
　　语法：字段名数据类型[宽度|not null|unique|default 默认值|auto_increment]

默认值

MariaDB [test]> create table t1(name varchar(10),sex char(10)default 'male' );

　　默认值：当用户向表中插入数据时，指定了该字段的值，那么就插入该值，否则就插入默认值

修改默认值

#两种方法
MariaDB [test]> alter table t1 modity sex varchar(10) default 'jim';
MariaDB [test]> alter table t1 alter sex set default 'tom';

非空 not null

MariaDB [test]> create table t2 (id int not null ,name char(10));
MariaDB [test]> insert into t2 values();
MariaDB [test]> select * from t2; //会将不允许为空的id字段转换成0
MariaDB [test]> select * from t1 where name is null; //查询name字段为null的行
MariaDB [test]> select * from t1 where name is not null; //查询name字段为不为null的行

#字段类型为字符串型，非空约束会将空值转换成空字符串
MariaDB [test]> alter table t2 modify name char(10) not null;

唯一

MariaDB [test]> create table t3 (id int unique,name char (10));

自增 auto_increment

要求：

该字段必须是数值型
字段上要有唯一或者主键

MariaDB [test]> create table t3 (id int unique,name char (10));

　　总结：

　　　　当自增字段发生断档时，值会从最大值继续自增
　　　　当delete删除最大值时，下一个值任然从删除之前的最大值继续自增
　　　　当truncate表时，值从1开始重新计算（truncate table test5）
　　

主键

主键是表中的特殊字段，这个字段能够唯一标识表中的每一条记录。
一张表只能有一个主键
主键的用途：快速定位数据
主键要满足的条件：非空且唯一
功能:primary key == not null + unique

使用单个字段做主键

#方法一、在字段后直接指定主键约束(列级约束，默认值为null)
MariaDB [test]> create table t6 (id int primary key,age int , name char(10));

#方法二、整张表的所有字段都定义完成之后再来指定主键(表级约束，默认值是0)
MariaDB [test]> create table t8 ( id int,name char(10),primary key (id));

多个字段做联合主键

　　注意：联合主键只能在所有字段都定义完成之后，才能定义主键。

例：

MariaDB [test]> create table t8 ( HOST char(15),NAME char(10),PASSWORD char(50) ,primary key (HOST,NAME));

外键 foreign key

　　外键：一个表的数据依赖另一张表的主键列的数据，如果在主键列没有出现的值，是不能够出现在外键字段的。

　　如：A表 a字段 int ------> B表主键字段

创建外键的条件：

存储引擎必须是innodb
相关联字段数据类型要一致
最好在外键列上建索引(目的是为了减少扫描范围，不创建也可以，值是影响性能)

MariaDB [test]> show create table score; 查看属性;
-----> ENGINE=InnoDB 存储引擎

例：
　　class 班级表
　　student 学生表

  class 父表                      student  子表
                               CLASS_ID   NAME   AGE 
  ID    CLASS                     1       jim    18
  1     0215                      1       tom    19
  2     0308                      2       xiao   20
  3     0316                      2       ming   21
  4     0411                      5       hong   19

创建父表

MariaDB [test]> create table class (ID int primary key,class char(20));

创建子表

MariaDB [test]> create table student (CLASS_ID int,NAME char(20),AGE int,foreign key(CLASS_ID) references class(ID));

　　总结：

　　　　子表中的关联数据依赖于父表，不能向子表中插入父表中不存在的值
　　　　不能删除父表中被子表所依赖的记录

没有级联操作的情况下:

插入: 先父表,再子表
删除: 先子表,再父表
更新: 先子表,再父表

删除父表中被依赖的行的方法：

删除外键约束
指定级联操作的选项

#on delete cascade 级联删除
#on update cascade 级联更新

MariaDB [test]> drop table student;
MariaDB [test]> create table student (CLASS_ID int ,NAME char(20),AGE int,foreign key(CLASS_ID) references class (ID) on delete cascade on update cascade);

　　总结：有了级联删除和级联更新选项，父表中的数据发生删除或者更新时，子表中相关数据也会发生相应的变化。

删除外键

　　语法：alter table 表名 drop foreign key 外键的名字

查看外键的名字

mysql> show create table student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `CLASS_ID` int(11) DEFAULT NULL,
  `NAME` char(20) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `CLASS_ID` (`CLASS_ID`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`)  //student_ibfk_1
为外键名字
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified

多表查询

　　语法：select 表1.字段,表2.字段,表2.字段 from 表1,表2 where 表1.关联字段=表2.关联字段 ;

左链接查询:

　　语法: select 表1.字段,表2.字段,表2.字段 from 表1 left join 表2 on 表1.关联字段=表2.关联字段;

#以左边的表做主,显示全部内容,右边没有对应关系,记录为NULL.

select stu1.sid,stu1.sname,class.id from stu1 left join class on stu1.sclass=class.id;

右链接查询:

　　语法: select 表1.字段,表2.字段,表2.字段 from 表1 right join 表2 on 表1.关联字段=表2.关联字段;

#以右边的表做主,显示全部内容,左边没有对应关系,记录为NULL.
select stu1.sid,stu1.sname,class.id from stu1 right join class on stu1.sclass=class.id;

九、索引

索引：类似于书的目录，字典中的音序表
功能：加快数据检索的速度，提高效率
缺点：

1. 创建和维护索引都需要消耗时间，消耗的时间长短取决于表中数据量的多少
2. 会占用磁盘空间
3. 更新数据库中的数据时，索引也会更新

注意：对于数据频繁更新的表不适合创建索。

索引的分类

　　单列索引、多列索引、唯一性索引

创建索引

在创建表时直接创建索引
　　语法：create table 表名 (字段名类型,...,index[索引名](索引字段列表));

MariaDB [test]> create table index1 (
-> id int,
-> name char(10),
-> index (id));

mysql> show create table index1\G;
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL,
  KEY `id` (`id`)   //索引相关的行，KEY 索引名 （索引字段）
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

　　注：创建索引时不指定名字，那么默认会创建和字段名同名的索引。

对于已经存在的表，添加索引

第一种方法：
#使用create index 命令创建索引
#语法：create index 索引名 on 表名 (字段名);
create index ind_name1 on index1(name);

#第二种方法：
#使用alter table 命令添加索引
#语法：alter table 表名 add index [索引名](索引字段);

#唯一性索引：unique index [索引名](索引字段)
alter table index2 add unique index user_name(user);

查看索引

show create table index1\G;
show index from index1\G;

删除索引

　　语法：drop index 索引名 on 表名;

drop index user_name on index2;

　　语法：alter table 表名 drop index 索引名;

alter table index1 drop index id;

十、用户权限管理

查看用户

MariaDB [(none)]> select user,host,password from mysql.user;

删除用户

　　语法：drop mysql.user 用户名@'主机';

MariaDB [(none)]> drop user root@'ula.example.com';
MariaDB [(none)]> delete from mysql.user where user!='root';

创建用户

　　创建无限制无密码无ip限制用户

MariaDB [(none)]> create user 'sonfer';

　　创建本地用户并设置密码

create user sonfer@localhost identified by 'sonfer123';

　　创建远程登陆的用户

　　　　语法：create user 用户名@'ip' identified by 'sonfer123';

create user sonfer@'192.168.1.71' identified by 'sonfer123';

　　注意：允许所有IP登陆不是'%'，应该写'%.%.%.%'，可能是MariaDB的一个BUG。

设置密码

使用mysqladmin命令

[root@ULA mysql]# mysqladmin -u root -p123 password '456'

使用set password命令

MariaDB [(none)]> set password for 'root'@'localhost'=password('123');

更新表记录

MariaDB [(none)]> update mysql.user set password=password('2') where user='root' and host='localhost';
MariaDB [(none)]> flush privileges;

客户端登陆服务器

　　语法：mysql -u用户名 -p密码 -h服务器IP

mysql -usonfer-h 192.168.1.1 -psonfer123

权限

全局层级

　　　　全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。

数据库层级

　　　　数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。

表层级

　　　　表权限适用于一个给定的表中的所有列。这些权限存储在mysql.tables_priv表中。

列层级

　　　　列权限适用于一个给定的表中的单一列。这些权限存储在mysql.colummns_priv表中。

授权

　　语法：grant 权限 on 库名.表名 to 用户名@'主机名' identified by '密码'; 　　（修改密码，可创建用户）

　　权限列表　　　

alter
create
delete
drop
index
insert
select
update
file　　　读或写服务器上的文件
all　　　　所有权限
show databases

授予全部权限

MariaDB [(none)]> grant all on *.* to user1@192.168.1.71 identified by 'test';
MariaDB [(none)]> flush privileges;  //刷新权限

授予部分权限

MariaDB [(none)]> grant select,insert,update on test.* to sonfer@'192.168.1.%' identified by '123';

查看当前用户权限

MariaDB [(none)]> show grants;

查看其他用户权限

MariaDB [(none)]> show grants for sonfer;

授权经验原则（权限控制主要出于安全考虑，因此需要遵循一个几个经验原则）

只授予能满足需要的最小权限，防止用户干坏事。比如说用户只是需要查询，那就只给select权限就可以了。不要给用户赋予update，insert或者delete。
创建用户的时候限制用户的登陆主机
初始化数据库的时候删除没有密码的用户。
为每个用户设置满足密码复杂度的密码。
定期清理不需要的用户。

回收权限

　　语法：revoke 权限 on 库名.表名 from 用户@'主机';

MariaDB [(none)]> revoke all on *.*  from  root_test@'%';      //回收全部权限
MariaDB [(none)]> revoke insert on *.*  from  root_test@'%';   //回收部分权限

十一、备份

备份的分类

　类型

　　　根据服务是否在线继续分为热备份、温备份和冷备份
　　　　　热备份：读写均不受影响
　　　　　温备份：仅可以执行读操作
　　　　　冷备份：也称为离线备份，读写操作均终止。

　　　根据备份时是否之间复制数据文件分为物理备份和逻辑备份
　　　　　物理备份：直接复制数据文件，速度快。
　　　　　逻辑备份：通常是将数据导出至文本文件中。速度慢、丢失浮点精度；方便使用文本根据直接进行处理、可移植能力强。

　　　根据备份数据内容分为完全备份、增量备份和差异备份
　　　　　完全备份：备份全部数据。
　　　　　增量备份：仅备份上次完全备份或增量备份以后变化的数据。
　　　　　差异备份：仅备份上次完全备份以来变化的数据

　　 备份时间的选择：数据库访问量较小的时候做备份

使用mysqldump备份

备份单个数据库

　　语法：mysqldump -u root -p2 库名 > /tmp/name.sql

[root@ULA mysql]# mysqldump -u root -p2 data1 > /tmp/data1.sql

备份单个表
　　语法：# mysqldump -u root -p2 库名表名 > /tmp/name.sql

备份多个表
　　语法：mysqldump -u root -p2 库名表名表名 > /tmp/name.sql

多库备份 -B,--databases
　　语法：mysqldump -u root -p2 -B 库名库名 > /tmp/name.sql

全库备份 -A, --all-databases
　　语法：mysqldump -u root -p2 -A > /tmp/name.sql

恢复

单库恢复

MariaDB [(none)]> drop database data1;   //删库
MariaDB [(none)]> create database data1; //再建库

#第一种恢复方式：
[root@ULA mysql]# mysql -u root -p2 data1 < /tmp/data1.sql 

#第二种恢复方式：
[root@ULA mysql]# mysql -u root -p2
MariaDB [(none)]> use school
MariaDB [school]> source /tmp/data1.sql

表恢复

[root@ULA mysql]# mysql -u root -p2 school < /tmp/table.sql

多库恢复(恢复之前是不需要手动创建数据库的)

[root@ULA mysql]# mysql -u root -p2 < /tmp/ddata.sql

全库恢复

[root@ULA mysql]# mysql -u root -p2 < /tmp/all.sql

十二、日志管理

四种日志

日志存放的位置：数据目录下
/var/lib/mysql/ rpm

　　错误日志：记录mysql服务器的启动、运行和关闭过程。

/var/log/mariadb/mariadb.log

MariaDB [(none)]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value | 
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.03 sec)

　　二进制日志

　　　　记录用户对数据库所做的所有的ddl和dml操作，不会记录查询语句，对数据的灾难恢复有至关重要的作用。
　　　　ddl：create、drop、alter
　　　　dml：insert、update、delete
　　　　二进制日志作用：做AB复制、恢复

　　通用查询日志：general_log

　　　　记录所有对数据库的请求信息(包括登陆、查询)，默认不开启
　　　　日志量较大、占用磁盘空间，一般不开启

　　慢查询日志：

　　　　记录的是查询时间超过指定时间的查询语句，一般用于优化查询，默认不开启

开启各种日志，修改配置文件

开启通用查询日志

临时开启，重启服务失效

MariaDB [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+S
1 row in set (0.00 sec)

MariaDB [(none)]> set global general_log=on;

MariaDB [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.00 sec)

通用查询日志/var/lib/mysql/短主机名.log

[root@ULA mysql]# cat /var/lib/mysql/name.log

永久设置

[root@ULA mysql]# vim /etc/my.cnf
log=general_select_log //=号右边的日志文件名可自定义

[root@ULA mysql]# systemctl restart mariadb

[root@ULA mysql]# cat /var/lib/mysql/general_select_log

开启慢查询日志

[root@ULA mysql]# vim /etc/my.cnf
log-slow-queries=slow-log //=号右边的日志文件名可自定义
long_query_time=5 //设置慢查询超时时间 单位是：秒

[root@ULA mysql]# systemctl restart mariadb

[root@ULA mysql]# mysql -u root -p2
MariaDB [(none)]> select sleep(6); //执行一个超过5秒的查询操作

[root@ULA mysql]# cat /var/lib/mysql/slow-log

开启二进制日志

[root@ULA mysql]# vim /etc/my.cnf
log-bin=log_bin.log //=号右边的日志文件名可自定义
log-bin-index=log_bin.index

[root@ULA mysql]# systemctl restart mariadb

二进制日志的查看 mysqlbinlog

[root@ULA mysql]# mysqlbinlog log_bin.000001 
# at 4 //事件发生的位置信息
#170606 14:51:00 //事件发生的时间信息

使用二进制日志进行数据恢复

使用位置点进行恢复

[root@ULA mysql]# mysqlbinlog --start-position=245 --stop-position=534 log_bin.000001 |mysql -u root -p2
--start-position 起始位置点
--stop-position 结束位置点

使用事件发生的时间

# mysqlbinlog --start-datetime='17-06-06 15:25:04' --stop-datetime='17-06-06 15:26:15' log_bin.000002 | mysql -u root -p2
--start-datetime 起始时间
--stop-datetime 结束时间

查看日志信息

MariaDB [(none)]> show master logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| log_bin.000001 | 1079 |
| log_bin.000002 | 1523 |
+----------------+-----------+
2 rows in set (0.00 sec)

查看二进制日志里的操作记录

MariaDB [(none)]> show binlog events;

查看当前使用的二进制日志文件及所处的位置

MariaDB [(none)]> show master status\G;

对日志的管理

删除日志（不要轻易删除）

删除某个日志文件之前的所有日志

MariaDB [(none)]> purge master logs to 'log_bin.000002';
Query OK, 0 rows affected (0.05 sec)

删除多少天以前的日志文件

MariaDB [(none)]> purge master logs before date_sub(current_date,interval 1 day);

重置二进制日志

MariaDB [(none)]> reset master;

手动生成新的二进制日志文件

MariaDB [(none)]> flush logs;

posted @ 2018-04-11 10:40 枯藤老艹树阅读(279) 评论(0) 收藏举报

刷新页面返回顶部

Sonfer

MySQL数据库操作

一、简介

Mysql是什么？

数据库简介：

数据库的分类：

Mysql的结构:

二、登录、帮助

匿名登陆

给root用户设置密码

本地登陆：

远程登陆：

帮助：

MySQL获取客户端帮助

忘记MySQL密码

修改配置文件

重启服务

登陆mysql

将配置文件中跳过权限表的选项删除或注释

重启服务

三、基本SQL语句

分类：

基本的sql语句：

库的操作

表的操作

记录的操作

四、存储引擎

mysql的常用存储引擎

查看当前数据库软件支持的所有存储引擎

查看默认使用存储引擎

设置表的存储引擎

永久修改默认存储引擎

重启服务生效

对于已经创建的表，修改存储引擎

五、函数

基本函数

聚合函数

时间函数

其他函数

六、记录与表的复制

准备表

记录的复制

复制表

复制部分表（不可以复制主键、外键和索引）

完全复制表（可以复制表的结构）

只复制表的结构

七、数据导入和导出

导入数据

导出数据

八、约束

约束也叫完整性约束

约束有哪些？

默认值

修改默认值

非空 not null

唯一

自增 auto_increment

主键

使用单个字段做主键

多个字段做联合主键

外键 foreign key

创建父表

创建子表

没有级联操作的情况下:

删除父表中被依赖的行的方法：

删除外键

查看外键的名字

多表查询

左链接查询:

九、索引

索引的分类

创建索引

查看索引

十、用户权限管理

查看用户

删除用户

创建用户

设置密码

使用mysqladmin命令

使用set password命令

　类型

　　错误日志：记录mysql服务器的启动、运行和关闭过程。

　　二进制日志

　　通用查询日志：general_log

　　慢查询日志：