Mysql常用运维命令
权限操作
1.创建用户，使之管理数据库
grant all (privileges) on *.* to root@localhost identified by ‘123456’;
说明:
- grant 与 on 之间是各种权限，例如:insert,select,update 等
- on 之后是数据库名和表名,第一个表示所有的数据库，第二个表示所有的表
- root 可以改成你的用户名，@后可以跟域名或 IP 地址，identified by 后面的是登录用的密码，可以省略，即缺省密码或者叫空密码
方法2：
create user 'suixin'@'10.%' identified by '123123
2.查看已创建的用户有哪些权限
show grants for suixin@'localhost'\G
3.删除用户授权
revoke "移除的权限" on 数据库.表 from 用户@host;
4.添加/修改用户权限
grant select,insert,update,alter on suixin.* to 'suixin'@'192.168.%'
5.更改用户密码
方法一：mysqladmin –u 用户名 –p 旧密码 password 新密码
方法二：update mysql.user set password=password('suixin') where user='root';
6.删除用户
方法一：deleted from user where user=”root” and host=’localhost’;
方法二：drop user ''@'localhost';
7.查看数据库有哪些用户
select user,host from mysql.user;
8.创建一个用户 suixin 在特定客户端 it363.com 登录，可访问特定数据库fangchandb
grant select, insert, update, delete, create,drop on fangchandb.* to suixin@it363.com identified by ' passwd';
9.重命名用户
语法：rename user oldname to newname；
例子：rename user zsy@localhost to suixin@localhost;
10.忘记mysql用户密码
a.停止mysql进程
b.mysqld_safe --skip-grant-tables &
c.mysql -uroot
d.update mysql.user set password=password('newpassword') where user='suixin' and host="locahost";
e.flush privileges;
11.mysql严格模式
# 如何查看严格模式
show variables like "%mode";
模糊匹配/查询
关键字 like
%:匹配任意多个字符
_:匹配任意单个字符
# 修改严格模式
set session 只在当前窗口有效
set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';
修改完之后 重新进入服务端即可
mysql 系统命令
1.显示当前用户
select user();
2.显示当前日期
select now();
3.查看数据库版本
select version();
4.查看 mysql 数据库字符集
show variables like '%char%';
5.设置默认字符集为 utf8
在配置文件找到[mysqld] 添加 default-character-set=utf8
6.查看可用字符集
show character set;
7.修改字符集
alter database testdb character set utf8;
8.查看排序方式
show collation;
库操作
1.显示数据库
show databases;
2.创建数据库
create database name;
扩展：如果不存在数据库则创建数据库
create database if not exists testdb;
3.选择数据库
use databasename;
4.查看当前使用的数据库
select database();
5.删除数据库前，有提示
mysqladmin drop databasename;
6.直接删除数据库，不提醒
drop database databasename;
7.查看数据库版本和当前日期
select version(),current_date;
8.查看已建库的完整语句
show create database databasename;
9.修改数据库字符集
alter database name character set utf8;
10.创建 GBK 字符集的数据库
create database databasename character set gbk collate gbk_chinese_ci;
11.查看数据库的授权
select * from mysql.db where DB='查看的数据库'
表操作
表的修改
1.表创建的集中常见方式
表一： 创建一个innodb引擎字符集为utf8的表suixin，并设置主键
create table suixin(
id int(4) not null,
name varchar(16) not null,
primary key(id)
) ENGINE=innodb default charset=utf8;
表二：创建联合主键
create table test1
(
id int(11),
name varchar(45) not null,
primary key(id,name)
);
表三：设置外键
create table test2(
id int primary key,
name varchar(100) not null,
tid int,
foreign key(tid) references test1(id)
);
表四：创建索引
mysql> create table test3(
-> id int(11),
-> name varchar(50),
-> primary key(id),
-> index ind_name(name));
或
mysql> create table test3(
-> id int(11),
-> name varchar(50),
-> primary key(id),
-> key ind_name(name));
扩展
字段类型
1.INT[(M)] 型： 正常大小整数类型
2.DOUBLE[(M,D)] [ZEROFILL] 型： 正常大小(双精密)浮点数字类型
3.DATE 日期类型：支持的范围是 1000-01-01 到 9999-12-31。MySQL 以 YYYY-MM-DD格式来显示 DATE 值，但是允许你使用字符串或数字把值赋给 DATE 列
4.CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度
5.BLOB TEXT 类型，最大长度为 65535(2^16-1)个字符。
6.VARCHAR 型：变长字符串类型
2.删除表
drop table tablename;
3.删除表中数据
delete 是逻辑删除表中的数据，一列一列的删除表中数据，速度比较慢
mysql> delete from suixin;
truncate 是物理删除表中的数据，一次性全部都给清空表中数据，速度很快
mysql> truncate table suixin;
4.插入数据
insert into suixin values(1,'Tom',),(2,'Joan');
5.重命名表
alter table t1 rename t2;
6.更改表类型
alter table table_name engine innodb|myisam|memory;
7.修改表中数据
update suixin set name=’xiaoluo’where id=1;
8.删除表 suixin 中编号为 1 的记录
delete from suixin where id=1;
9.改变表的编码
Alter TABLE suixin CHARACTER SET gb2312;
或者
alter table 表名 convert to character set gbk; 修改表的字符集
10.把 id 列设置为主键
alter table suixin add primary key(id);
11.为表添加字段，同时添加相应的约束
alter table test5 add age int not null default 0;
12.删除字段
alter table test5 drop age;
13.修改字段名
alter table test5 change id ido int(11);
14.修改字段类型
alter table testtable modify age int; #这种方法，如果这个字段上面有索引，索引使用了字段的前10个字符，则这个字段的数据类型长度不能修改（缩短）到10一下
或
alter table testtable change age age char(10); #这种方法不能用于char类型改为int类型
15.设置表字段为非空约束
alter table test5 modify name varchar(100) not null;
16.删除非空约束
alter table test5 modify name varchar(100) null;
17.设置表字段为自动增长
alter table test5 modify id int auto_increment;
18.删除自动增长
alter table test5 modify id int;
19.主键约束
alter table testtb add primary key(id);
20.删除主键约束
alter table testtb drop primary key;
21.唯一键约束
alter table testtb add unique key(uid);
22.创建唯一键，设置唯一键的约束为uni_test
alter table testtb add unique key uni_test(test);
23.删除唯一键uni_test
alter table testtb drop index uni_test;
24.添加一个索引，索引名字为ind_name
第一种：alter table testtb add index ind_name(name);
第二种：create index ind_name on testtb(name(20));
25.重建索引--mariadb中的innode存储引擎不支持重建索引
repair table table_name quick;
26.删除索引
alter table test2 drop index uu_ttt;
表的查询
1.查看索引
show index from testtb;
2.查看库中的表
show tables;
3.查看表中所有数据
select * from suixin;
4.查看表 suixin 中前两行数据
select * from suixin limit 0,2;
5.指定条件查询语句
select name,age from test5 where age = 25;
6.在范围内查询
select * from tb1 where age > 25 or age < 28;
7.模糊查询
select * from tb1 where name like 'j%';
8.支持正则表达式查询
select * from tb1 where name rlike '^t.*';
9.in指定关键字查询(也可以使用not in)
select * from tb1 where age in (21,23,24,25);
10.对数据库进行排序(asc表示升序，可省略)
升序
select * from tb1 order by age asc;
倒序
select * from tb1 order by age desc;
11.对tb1表中所有数据按照age从大到小降序排列，如果多行之间age字段相同，这些行根据name字段进行升序排序
select * from tb1 order by age desc,name asc;
12.去重查询
select distinct age from tb1;
13.查询时给字段添加别名，以便显示为我们指定的列名
select name as StuName,age from tb1;
14..显示具体的表结构，查看字段
desc(describe) tablename;
或
show columns from test;
15.显示建表语句
show create table table_name;
16.显示详细信息，包括字符集编码
show full columns from tablename;
17．查看表 suixin 的字符集
show table status from databasename like '%suixin%'\G;
18.查看约束
select * from information_schema.key_column_usage where table_name='test5'\G;
19.查看test1表有哪些的外键
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME \
from information_schema.KEY_COLUMN_USAGE \
where TABLE_NAME = 'test1' and REFERENCED_TABLE_NAME is not null;
20.查看test2被哪些表引用成外键
select REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME \
from information_schema.KEY_COLUMN_USAGE \
where REFERENCED_TABLE_NAME = 'test5';
21.添加外键约束
在testtb表创建一个新的字段，tid，并添加一个外键，外键名叫testtb_tid_fk，外键中，testtb表中的tid字段引用了表testtb2表中的id字段
alter table testtb add column tid int default 0 not null;
alter table testtb add constraint testtb_tid_fk foregin key(tid) references testtb2(id);
22.删除外键约束
查出test4有哪些外键，然后进行删除
alter table test4 drop foreign key test_tid_fk;
23.查看事件、函数、存储过程
show enevts； 查看事件
show function status\G 查看函数
show procedure stauts\G 查看存储过程
特殊查询
利用group by对表进行分组查询
已经存在的student表如下
mysql> select * from student;
+----+---------+------+--------+---------+-----------+
| id | name | age | gender | classid | teacherid |
+----+---------+------+--------+---------+-----------+
| 2 | luobinn | 22 | M | NULL | NULL |
| 3 | baby | 44 | M | NULL | NULL |
| 4 | aydy | 30 | M | NULL | NULL |
| 5 | bingy | 30 | M | NULL | NULL |
| 6 | ding | 100 | F | NULL | NULL |
| 7 | ading | 18 | F | NULL | NULL |
| 8 | lading | 28 | F | NULL | NULL |
+----+---------+------+--------+---------+-----------+
1.根据性别进行分组，默认显示该性别的第一个人
mysql> select * from student group by gender;
+----+---------+------+--------+---------+-----------+
| id | name | age | gender | classid | teacherid |
+----+---------+------+--------+---------+-----------+
| 6 | ding | 100 | F | NULL | NULL |
| 2 | luobinn | 22 | M | NULL | NULL |
+----+---------+------+--------+---------+-----------+
2.查出男性和女性分别有多少人
mysql> select count(id),gender from student group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
| 3 | F |
| 4 | M |
+-----------+--------+
count是一种聚合函数
3.算出男生女生的平均年龄
mysql> select avg(age),gender from student group by gender;
+----------+--------+
| avg(age) | gender |
+----------+--------+
| 48.6667 | F |
| 31.5000 | M |
+----------+--------+
mysql常用聚合函数有：
min(col) 返回指定列的最小值
max(col) 返回指定列的最大值
avg(col) 返回指定列的平均值
count(col) 返回指定列中非null值的个数
sum(col) 返回指定列的所有值之和
group_concat(col) 返回指定列的值，但会分组显示
4.根据性别对每个人进行分组
mysql> select gender,group_concat(name) from student group by gender;
+--------+-------------------------+
| gender | group_concat(name) |
+--------+-------------------------+
| F | ding,ading,lading |
| M | luobinn,baby,aydy,bingy |
+--------+-------------------------+
对分组后的信息再次过滤
mysql> select avg(age) as avgage,gender from student group by gender having avgage > 33;
+---------+--------+
| avgage | gender |
+---------+--------+
| 48.6667 | F |
+---------+--------+
如果对分组后的信息再次过滤，可使用having关键字
5.查询student表，以性别字段gender分组，显示各组中年龄大于19的学员的年龄的总和
mysql> select sum(age),gender from student where age > 19 group by gender;
+----------+--------+
| sum(age) | gender |
+----------+--------+
| 128 | F |
| 126 | M |
+----------+--------+
mysql多表查询
有下面两张表
mysql> select * from t2;
+------+-------+
| t2id | t2str |
+------+-------+
| 2 | a |
| 3 | b |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+------+-------+
| t1id | t1str |
+------+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+-------+
1.交叉链接
select * from t1,t2 <--查询结果显示t1的每一项分别对应t2的每一项，重复太多，没有太大实用意义
官方推荐语句
mysql> select * from t1 cross join t2;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
| 1 | 1 | 2 | a |
| 1 | 1 | 3 | b |
| 2 | 2 | 2 | a |
| 2 | 2 | 3 | b |
| 3 | 3 | 2 | a |
| 3 | 3 | 3 | b |
+------+-------+------+-------+
2.内链接
查询t1和t2表中id号相同的记录
mysql> select * from t1,t2 where t1.t1id=t2.t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
| 2 | 2 | 2 | a |
| 3 | 3 | 3 | b |
+------+-------+------+-------+
查询结果和上面一样，但官方推荐语句写法
mysql> select * from t1 inner join t2 on t1.t1id=t2.t2id;
此时交叉链接查询语句也可以达到上述结果
mysql> select * from t1 cross join t2 on t1.t1id=t2.t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
| 2 | 2 | 2 | a |
| 3 | 3 | 3 | b |
+------+-------+------+-------+
3.联合查询，默认union会去掉重复的，可使用union all即可
mysql> select * from t1 union select * from t2;
+------+-------+
| t1id | t1str |
+------+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 2 | a |
| 3 | b |
+------+-------+
4.由于mysql中没有全连接，可使用左链接和右链接和union搭配实现全连接
mysql> select * from t1 left join t2 on t1id=t2id
-> union
-> select * from t2 left join t1 on t1id=t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
| 1 | 1 | NULL | NULL |
| 2 | 2 | 2 | a |
| 3 | 3 | 3 | b |
| 2 | a | 2 | 2 |
| 3 | b | 3 | 3 |
+------+-------+------+-------+
查询缓存
将查询的结果缓存下载，如果查询的语句完全相同，则直接返回缓存中的结果
查看当前服务是否开启查询缓存的功能
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |<--表示数据库支持缓存功能
| query_cache_limit | 1048576 |<--表示单条查询缓存的最大值
| query_cache_min_res_unit | 4096 |<--缓存存储于内存的最小单元
| query_cache_size | 0 |<--查询缓存的总大小
| query_cache_type | ON |<--表示开启了查询缓存功能，ON(开启),OFF(j禁用)，DEMAND(按需缓存)，设置在my.cnf
| query_cache_wlock_invalidate | OFF |<--OFF表示即使有写锁，查询也可以获取到缓存的结果，如果为ON，写锁期间，查询语句命中了查询缓存，也不能从缓存获取结果
+------------------------------+---------+
注意：
所谓的查询语句一样才可以缓存，区分大小写，相同的语句大小写不一样，不能从查询缓存返回结果
如果某张表数据变化频繁，在查询这张表的内容时有使用了缓存，那么缓存失效的频率会非常高，我们往往在数据变化不频繁、且又需要重复执行相同查询的场景中使用缓存
1.在开启缓存的时候，指定对应的查询语句不使用缓存
select sql_no_cache name from stu;
2.也可以按需使用缓存时，指定对应的查询语句使用缓存
select sql_cache name from stu;
3.在配置文件设置缓存空间大小为100M
query_cache_type=DEMAND
query_cache_type=100M
4.使用如下查询语句时，明确指定当前语句的查询结果需要缓存
select sql_cache * from students where teacherid is not null;
5.查看缓存命中情况
mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 | <--表示已分配的内存块中空闲块的数量
| Qcache_free_memory |104838200 | <--表示查询缓存的空闲总量大小
| Qcache_hits | 2 | <--被缓存条目的命中次数
| Qcache_inserts | 1 | <--表示在未命中缓存，将查询结果写入缓存的次数
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 | <--没有被缓存的 查询语句数量
| Qcache_queries_in_cache | 1 | <--表示已缓存的SQL语句数量
| Qcache_total_blocks | 4 | <--当前查询缓存占用的内存block数量
+-------------------------+----------+
清理缓存：flush query cache;
清除查询缓存中已经存在的缓存：reset query cache;
这里的可以通过上述统计信息的数值以及缓存设置的相关值，计算出查询缓存的相关指标，从而判断查询缓存是否对我们有帮助
参考：http://www.zsythink.net/archives/1111
存储引擎操作
MYISAM：支持表级锁，不支持行级锁，不支持事务，不支持外键约束，支持全文索引，表空间文件相对小
INNODB：支持表级锁，支持行级锁，支持事务，支持外键约束，不支持全文索引，表空间文件相对较大
1.查看默认的存储引擎
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
2.查看数据库的所有表存储引擎
mysql> show table status\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 8388608
Auto_increment: NULL
Create_time: 2018-01-02 09:26:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 8388608
Auto_increment: NULL
Create_time: 2018-01-02 10:25:04
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row ***************************
Name: t2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 8388608
Auto_increment: NULL
Create_time: 2018-01-02 10:29:45
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
3 rows in set (0.00 sec)
3.查看单一表的存储引擎
mysql> show table status like '%student%'\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 8388608
Auto_increment: NULL
Create_time: 2018-01-02 09:26:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
4.修改表的存储引擎
alter table t1 engine=myisam;
5.修改数据库的默认存储引擎
a. stop数据库，修改配置文件
b. default-storage-engine=innodb 设置默认存储引擎
c. innodb_file_per_table=on 每个表使用单独的表空间
d. start数据库即可
6.innodb和myisam的数据文件
从配置文件看出，表空间数据文件存放路径如下
datadir=/opt/mysql/data
innodb的数据文件默认存在/opt/mysql/data/ibdata1 文件里面
所有innodb的共享表空间数据文件，表的数据信息与索引信息都存储在ibdata1中。
但是如果配置文件中打开了 innodb_file_per_table=on 此时，每个innodb表使用单独的表空间数据文件
查看数据库目录下的表
[root@test zhudb]# ll /opt/mysql/data/zhudb
total 40
-rw-rw---- 1 mysql mysql 8724 Jan 2 09:26 student.frm
-rw-rw---- 1 mysql mysql 8724 Jan 2 09:26 student.idb
-rw-rw---- 1 mysql mysql 8592 Jan 2 10:25 t1.frm
-rw-rw---- 1 mysql mysql 8592 Jan 2 10:25 t1.ibd
总结：
Inoodb表类型的存储引擎：
from后缀的文件存储表结构信息
ibd后缀的文件存放表的数据信息与索引信息
如果没有开启innodb_file_per_table，则不会有ibd结尾的文件，而是共用ibdata1
myisam表类型的存储引擎，每张表都有三种后缀名的文件，分别是.frm后缀、.MYD后缀、.MYI后缀。
.frm后缀的文件中存放表结构信息
.MYD后缀的文件中存放数据信息
.MYI后缀的文件存放索引信息
事务
1.事务相关概念
事务可以理解成一组sql语句，要么全部执行成功，要么全部执行失败，事务内的sql语句被当做一个整体。
redo log 事务中的sql语句涉及到所有数据操作先记录到redo log中，然后再同步到对应数据文件中
redo log由两部分组成：redo log buffer(重做日志缓冲) 和redo log file(重做日志文件)
redo log buffer存在内存中，容易丢失，redo log file是持久的，存在磁盘上
undo log 修改前的备份，如果事务中有一条sql没有执行成功，数据库可以根据undo log进行撤销
log group 重做日志组，一个重做日志组中有多个重做日志文件(redo log file),当日志组中第一个logfile被写满，则开始将redo log写入日志组中下一个重做日志文件。全备写满后，会覆盖第一个日志文件
1.当使用innodb引擎时，使用如下语句查询日志相关配置参数
mysql> show global variables like '%innodb%log%';
+--------------------------------+---------+
| Variable_name | Value |
+--------------------------------+---------+
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 | <--redo log file的大小
| innodb_log_files_in_group | 2 | <--表示每个重做日志组中有几个redo log file
| innodb_log_group_home_dir | ./ | <--重做日志组的路径
| innodb_mirrored_log_groups | 1 | <--表示一共几组日志组
+--------------------------------+---------+
innodb_flush_log_at_trx_commit表示当事务提交后，是否立即将redo log从内存刷写到redo log file中
值可以设置为0、1、2
设置0，事务提交时不会将redo log从log buffer刷写到redo log file，但会每秒自动刷写一次，如果数据库崩溃，会丢失一秒钟的redo log
设置1 表示事务提交时必须将redo log从log buffer中刷写到redo log file中
设置为2 表示事务提交时，只会将redo log写入文件系统内存中，但不会立即写入到redo log file中，而是每秒钟从文件系统缓存中将数据刷写到redo log file中一次，此种情况mysql宕了，操作系统没有宕机，则数据不会丢失，如果操作系统宕机，重启数据库后，则会丢失为从文件系统内存刷写到redo log file中的那部分事务。
理论上说，此值设置为1，安全性最高，性能最低，设置为0，性能最高，安全性最低，设置为2，性能较高，安全性较低
2.事务控制语句
首先查看是否开启了自动提交
show global variables like 'autocommit';
show session variables like 'autocommit';
有如下一张表
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
2.1 最简单的事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(5,5);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+------+------+
2.2 事务回滚操作
rollback对未做提交的操作进行了撤销
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t3 where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+------+------+
4 rows in set (0.00 sec)
2.3 事务保存点
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t3 where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+------+------+
3 rows in set (0.00 sec)
mysql> savepoint del1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(7,7);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 7 | 7 |
+------+------+
4 rows in set (0.00 sec)
mysql> savepoint add7;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t3 where id = 7;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback to savepoint add7;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | str |
+------+------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 7 | 7 |
+------+------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
我们也可以关闭自动提交功能，在mysql命令行中可以直接敲命令，但是最后要commit才可以提交
mysql> set @@session.autocommit=0
3.事务隔离级别
3.1查看链接当前数据库的线程
mysql> show processlist;
+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-------+------------------+
| 5 | root | localhost | zhudb | Sleep | 445 | | NULL |
| 6 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+-------+---------+------+-------+------------------+
两个窗口分别链接数据库，分别开始自己的事务操作
窗口1对表进行了修改，窗口2查询时看不到窗口1修改后的数据的，查看到的还是修改前的数据
窗口1提交修改操作，窗口2查看还是之前的数据
当窗口2提交数据后，再次查看，可看到窗口1对表的修改后的数据
这是因为mysql的隔离级别影响的
READ-UNCOMMITTED:读未提交
READ-COMMITTED：读已提交或读提交
REPEATABLE-READ：可重复读或可重读
SERIALIZABLE：串行化
3.1 查看mysql的隔离级别
show variables like 'tx_isolation';
3.2 如果要修改配置文件，可通过如下参数配置mysql的事务隔离级别
transaction_isolation=REPEATABLE-READ
mysql日志
1.错误日志
执行过程中的错误日志会记录以下信息：
mysql执行过程中的错误信息
mysql执行过程中的警告信息
event scheduler运行时所产生的信息
mysql启动和停止过程中输出信息，未必是错误信息
主从复制结构中，从服务器IO复制线程的启动信息
mysql中，错误日志使用log_error以及log_warnings等参数定义
log_warnings用于标识警告信息是否一并记录到错误日志中
log_warnings值为0，表示不记录警告信息
log_warnings值为1，表示警告信息一并记录到错误日志中
log_warnings值大于1，表示'失败的链接'的信息和创建新链接时'拒绝访问'类的错误信息也会被记录到错误日志中
查看mysql的错误日志的路径
show global variables like '%log_error%';
查看警告标识信息
show global variables like '%log_warnings%';
或写到配置文件中
log_warnings=2
2.查询日志
查看查询日志是否开启
mysql> show variables where variable_name like "%general_log%" or variable_name="log_output";
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF | <--表示查询日志是否开启
| general_log_file | /opt/mysql/data/test.log | <--查询日志以文件的方式保存路径
| log_output | FILE | <--表示以何种方式保存
+------------------+--------------------------+
log_output 有四种值：TABLE\FILE\FILE,TABLE\NONE
以表方式存放存放在mysql库的general_log表中
3.慢查询日志
某些sql语句执行完毕所花费时间特别长，我们将这种响应比较慢的语句记录在慢查询日志
log_slow_queries: 表示是否开启慢查询日志，5.6以后的版本使用slow_query_log取代此参数
log_output: 慢查询日志开启后，以哪种方式存放，可以设4种值，TABLE\FILE\FILE,TABLE\NONE
slow_query_log: 5.6以后的版本使用此参数代替log_slow_queries
slow_query_log_file: 表示慢查询日志保存到哪个日志文件中
long_query_time: 表示'多长时间的查询'被认定为'慢查询'，此值默认10s
log_queries_not_using_indexes: 表示如果运行的sql语句没有使用到索引，是否也被当作慢查询语句记录到慢查询日志中
log_throttle_queries_not_using_indexes:5.6.5版本新引入的参数，设置为ON时，没有使用索引的查询语句也会被当做慢查询语句记录到慢查询日志中
3.1 开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.05 sec)
mysql> show variables where variable_name like "%low_query%" or variable_name="log_output";
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| log_output | FILE |
| slow_query_log | ON |
| slow_query_log_file | /opt/mysql/data/test-slow.log |
+---------------------+-------------------------------+
3.2 设置慢查询的时间界限为3s
set global long_query_time=3
3.3 查看慢查询的时间界限
slect @@global.long_query_time
全局设置后，当前会话的变量仍然没有被改变
slect @@global.long_query_time <--查看当前会话的慢查询时间界限，发现还是10s
当打开一个新的数据库链接，已经全部时3s了
3.4 查看mysql记录多少慢查询语句
show global status like '%slow_queries%';
mysqldumpslow可对慢查询日志进行分析，具体参考：http://www.zsythink.net/archives/1260
二进制日志的查询
1.查看binlog日志是否开启
show global variables like '%log_bin%';
没打开要再配置文件中配置
2.查看二进制文件列表
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
| mysql-bin.000002 | 107 |
| mysql-bin.000003 | 107 |
| mysql-bin.000004 | 126 |
| mysql-bin.000005 | 126 |
+------------------+-----------+
3.查看当前使用的二进制文件
show master status\G
4.查看binlog日志的内容
mysql> show binlog events in 'mysql-bin.000005';
5.指定位置点查看
mysql> show binlog events in 'mysql-bin.000005' from 317;
6.查看binlog日志的前三行
mysql> show binlog events in 'mysql-bin.000005' limit 3;
7.查看binlog日志指定位置点的前三行内容
mysql> show binlog events in 'mysql-bin.000005' from 956 limit 3;
8.使用mysqlbinlog直接查看binlog文件
mysqlbinlog --start-position 956 --stop-position 1025 mysql-bin.000005
mysqlbinlog --start-datetime "2017-12-31 10:40:00" --stop-datetime "2017-12-31 11:40:00" mysql-bin.000005
备份数据库
常用参数
--routines：表示备份，存储过程和存储函数也会备份
--triggers：表示备份时，触发器会被备份
--events：表示备份，事件表会被备份
1.表使用innodb存储引擎常用的备份语句
1.1 如果未开启二进制日志，备份指定的zsythink数据库，可使用如下语句
mysqldump -uroot -h192.168.1.146 --single-transaction --routines --triggers --events --databases zsythink -p > zsythink.sql
1.2 对开启二进制日志，备份指定的zsythink数据库，可使用如下语句
mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --databases zsythink -p > zsythink.sql
1.3 对开启二进制日志，备份全部数据库语句如下
mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --all-databases -p > dbbackup.sql
2.表使用myisam存储引擎常用的备份语句
--lock-tables 对数据库加锁，该选项不能与--single-transaction同时使用
2.1 如果未开启二进制日志，备份指定的zsythink数据库，可使用如下语句
mysqldump -uroot -h192.168.1.146 --routines --triggers --events --lock-tables --databases zsythink -p > zsythink.sql
2.2 对开启二进制日志，备份指定的zsythink数据库，可使用如下语句
mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --routines --triggers --events --lock-tables --databases zsythink -p > zsythink.sql
2.3 对开启二进制日志，备份全部数据库语句如下
mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --routines --triggers --events --lock-all-tables --all-databases -p > dbbackup.sql
mysqldump 部分参数：
--add-drop--database：在每个 CREATE DATABASE 语句前添加 DROP DATABASE 语句。
--all--database，-A： 备份所有数据库中的所有表。与使用---database 选项相同，在命令行中命名所有数据库。
--database，-B： 转储几个数据库。通常情况，mysqldump 将命令行中的第 1 个名字参量看作数据库名，后面的名看作表名。使用该项，它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name 和 USE db_name 语句包含在每个新数据库前的输出中。
--flush-logs，-F： 刷新 binlog 日志
--lock-all-tables，-x： 所有数据库中的所有表加锁。在整体备份过程中通过全局读锁定来实现。
--master-data： 启用二进制日志。如果该选项值等于 1，位置和文件名被写入 CHANGE MASTER 语句形式的转储输出; 如果选项值等于 2，CHANGE MASTER 语句被写成 SQL 注释。
--no-create-db,-n： 禁止生成创建数据库语句；
--no-data，-d： 备份表结构
--no-create-info，-t： 不写重新创建每个转储表的CREATE TABLE语句。
3.实际场景：
a.全备
mysqldump –uroot –p123456 –c –R --master-data=2 –-add-drop-database –-single-transaction -–all-databases |gzip –qc > /tmp/all.zip
b.导出数据库结构
[root@linux mysql]# mysqldump -uroot -p -n -d --database test >/tmp/db.sql
c.导出函数
[root@linux mysql]# mysqldump -uroot -p -n -t -d -R --database test >/tmp/p.sql
d.导出事件
[root@linux mysql]# mysqldump -uroot -p -n -t -d --events --databases test >1.sql
