MySQL
MySQL 安装:
端口及协议:
TCP 3306
软件安装:
yum:下载rpm包,解决依赖关系
卸载:rpm -e 单独卸载(防止用yum erase 删除时将依赖包删除掉)
编译安装:
./configure
make
make install
bin包安装:绿色版
[root@mysql-bin ~]# lftp 172.16.0.99
lftp 172.16.0.99:~> cd scripts/
lftp 172.16.0.99:/scripts> get mysql-5.7.18-bin.sh
[root@mysql-rpm ~]# lftp 172.16.0.99
lftp 172.16.0.99:~> cd scripts/
lftp 172.16.0.99:/scripts> get mysql-5.7.18.sh
修改初始密码:
1. bin 包安装
[root@mysql-bin ~]# mysql -u root -p # bin包安装之后mysql命令需要设置变量才能使用
-bash: mysql: command not found
[root@mysql-bin ~]# vim /etc/profile
PATH=/usr/local/mysql/bin:$PATH
[root@mysql-bin ~]# . /etc/profile # 重新加载环境变量 使命令生效
[root@mysql-bin ~]# mysql -u root -p # 登录mysql
Enter password: 初始密码
mysql> alter user root@'localhost' identified by '12345'; # 将初始密码做修改
mysql> flush privileges;
mysql> quit
[root@mysql-bin ~]# mysql -u root -p
Enter password: 新密码
如果遗失初始化时的密码:
在 /data/mysql/log/mysql-error.log 日志中查找 localhost 后边的就是密码
sql语句结束,要有 ; !!! ( ; 分号)
2. rpm 包安装
查找初始密码
[root@mysql-rpm ~]# grep "root@localhost" /var/log/mysqld.log
得到初始密码
[root@mysql-rpm ~]# mysql -u root -p
Enter password: 初始密码
mysql> alter user root@'localhost' identified by 'mysql';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
密码太简单,修改失败
要求:大小写字母 数字 符号 >=8位
mysql> alter user root@'localhost' identified by 'ABC123qwe@#';
mysql> flush privileges;
刷新权限列表,让密码修改生效。
mysql> quit
[root@mysql-rpm ~]# mysql -u root -p
Enter password: 新密码
线上生产环境:密码要尽量复杂。
线下测试环境:密码可以简单一点。
严格的密码策略:
mysql> show variables like '%password%';
+---------------------------------------+--------+
|Variable_name | Value |
+---------------------------------------+--------+
|default_password_lifetime | 0 |
|disconnect_on_expired_password | ON |
|log_builtin_as_identified_by_password | OFF |
|mysql_native_password_proxy_users | OFF |
|old_passwords | 0 |
|report_password | |
|sha256_password_proxy_users | OFF |
|validate_password_check_user_name | OFF |
|validate_password_dictionary_file | |
|validate_password_length | 8 | 长度
|validate_password_mixed_case_count | 1 | 最少有1位大写、小写字母
|validate_password_number_count | 1 | 最少有1位数字 |validate_password_policy | MEDIUM | 密码的安全策略级别
|validate_password_special_char_count | 1 | 最少有1位符号
+---------------------------------------+--------+
密码的安全策略: validate_password_policy
0 LOW:最低级,只检查密码长度
1 MEDIUM:中级的,长度,复杂度 2
STRONG:最高级,长度,复杂度,数据字典
修改密码策略:
mysql> set global validate_password_policy=0;
修改密码的最大长度:
mysql> set global validate_password_length=4;
mysql> alter user root@‘localhost’ identified by ‘12345’;
mysql> flush privileges;
mysql> quit
mysql 启动和关闭:
1. bin 包启动以及设置开机启动的方法
[root@mysql-bin ~]# mysql -u root -p # bin包安装之后mysql命令需要设置变量才能使用
-bash: mysql: command not found
[root@mysql-bin ~]# vim /etc/profile
PATH=/usr/local/mysql/bin:$PATH
[root@mysql-bin ~]# . /etc/profile # 重新加载环境变量 使命令生效
[root@mysql-bin ~]# mysql -u root -p # 登录mysql
Enter password: 初始密码
mysql> alter user root@'localhost' identified by '12345'; # 将初始密码做修改
mysql> flush privileges;
mysql> quit
直接启动:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
设置默认开机启动:
- 将配置信息写入 /etc/rc.loacl
echo "/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & " >> /etc/rc.local - 给rc.local执行权限
chmod +x /etc/rc.d/rc.local
结束进程命令
kill killall
2. rpm 包启动以及设置开机启动的方法
systemctl start mysqld
systemctl enable mysqld
查看默认端口 3306/tcp
[root@localhost ~]# netstat -antp | grep mysqld
mysql 连接:
centos7 默认的mariadb 没有初始密码
yum install -y mariadb-server
systemctl start mariadb 之后可以使用mysql命令
- bin
- rpm
DBA 通过使用mysql(客户端工具) --> 管理mysql-server
mysql-进程 mysqld-进程
进程间通信 mysql.sock 套接字文件
[root@mysql-bin ~]# ll /tmp/mysql.sock
进程间通信 mysql.sock 套接字文件
srwxrwxrwx 1 mysql mysql 0 Nov 12 10:02 /tmp/mysql.sock
禁止明文密码的操作登陆数据库
-p 后边直接写密码也可以登录,mysql系统会提示警告信息,禁止这种明文密码的登录操作
[root@mysql-bin ~]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
禁止此操作
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
查看有哪些数据库
映射到文件系统,是目录
查看用户:
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost | 管理员用户
+----------------+
查看当前的用户身份
查看当前在哪个数据库里边
mysql> select database();
+------------+
| database() |
+------------+
| db2 |
+------------+
查看当前在哪个数据库里面
数据库的管理操作:
1. 创建
mysql> create database db1;
mysql> create database db2;
2. 删除
mysql> drop database db2;
3. 查看
mysql> show databases;
数据库的字符集文件:
[root@mysql-bin /data/mysql/data/db1]# cat db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
汉字在latin1语言里,是乱码。
mysql> create database db2 default character set utf8 collate utf8_general_ci;
在建库时,指定字符集和字符编码规则。
mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
[root@mysql-bin /data/mysql/data]# cat db2/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
[root@mysql-bin ~]# vim /etc/my.cnf # 添加字符集必须结束mysql进程在启动才能生效
[mysqld]
添加字符集设置
character-set-server = utf8
collation-server = utf8_general_ci
重启mysqld
[root@mysql-bin ~]# netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 1685/mysqld
[root@mysql-bin ~]# kill 1685
[root@mysql-bin ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
mysql> create database db3;
[root@mysql-bin /data/mysql/data]# cat db3/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
表的操作:
1. 创建
建表,必须在库里。
mysql> use db1 #进入db1库
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
mysql> create table t1 (id int,name char(10));
建t2表,属性为id和名字
2. 删除
mysql> drop table t1; #删除表t1
3. 查看
查看所有表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
查看某个表的属性
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
Field 列名
Type 列的数据类型
Null 是否允许是空值
Key 键值 PRI主键 MUL外键
Default 默认值是什么,没有设置的情况下,是空值
Extra 其他的参数
查看t1表的配置属性
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
ENGINE=InnoDB 表的默认的存储引擎
CHARSET=latin1 表的默认的字符集
[root@mysql-bin /data/mysql/data/db1]# ls
t1.frm t1.ibd
innodb 这种存储引擎
.frm 表的框架
.ibd 表的表空间文件,保存表的数据
列的数据类型:
1. 数值型
(1)整型
- tinyint 很小整数 127 (人的年龄 )
- smallint 小整数 32767 (游戏的等级 )
- mediumint 中型整数 838607
- int 整数 2147483647
- bigint 大整数 2^63 (游戏的经验)
mysql> create table t2 (id1 tinyint,id2 smallint,id3 mediumint,id4 int,id5 bigint);
mysql> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id1 | tinyint(4) | YES | | NULL | |
| id2 | smallint(6) | YES | | NULL | |
| id3 | mediumint(9) | YES | | NULL | |
| id4 | int(11) | YES | | NULL | |
| id5 | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
mysql> insert into t2(id1) values(1234);
ERROR 1264 (22003): Out of range value for column 'id1' at row 1
mysql> insert into t2(id1) values(123);
(2)浮点型
- float(M,D) 单精度浮点型 4byte
- double(M,D) 双精度浮点型 8byte
- M 整个列的数值的位数
- D 小数点后面保留几位
- M=D .是0
- M>D M-D=几 .前面的整数是几位
正数值必须大于后值:
mysql> create table t3 (f1 float(2,3));
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'f1').
建表,属性为浮点数类型:(其中3-2=1,表示浮点数,小数点前边一位,2表示取小数点后保留两位)
mysql> create table t3 (f1 float(3,2));
mysql> insert into t3 values(1.23456); 向表里存入数值
mysql> insert into t3 values(1.5678);
mysql> select * from t3;
+------+
| f1 |
+------+
| 1.23 |
| 1.57 | 四舍五入
+------+
2. 字符型
(1)
char() 等长字符串
身份证号 电话号码 …
char(18) char(12)
varchar(255) 可变长字符串
名字 地址 邮箱
mysql> create table t4(name char(10),name2 varchar(10));
mysql> insert into t4 values("niua","niub");
mysql> select * from t4;
+------+-------+
| name | name2 |
+------+-------+
| niua | niub |
+------+-------+
(2)
- enum( ) 单选可能的枚举类型
- 格式 enum(“元素1”,“元素2”,…) 人的性别 游戏的职业
- set( ) 多选可能的枚举类型
- 格式 set(“元素1”,“元素2”,…)
建表,设置性别属性为M,W
mysql> create table t5 (sex enum("M","W"));
mysql> insert into t5 values("M"); 存入值成功
mysql> insert into t5 values("W");
mysql> insert into t5 values("R"); 当存入其它值时报错!
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
set( ) 多选可能的枚举类型
set(“元素1”,“元素2”,…)
mysql> create table t6(test set("yuwen","shuxue","yingyu","wudao"));
mysql> insert into t6 values("yuwen,shuxue"); # 只能输入已有选项,选项大于等于1个
mysql> insert into t6 values("yingyu,shuxue,wudao");
mysql> select * from t6;
+---------------------+
| test |
+---------------------+
| yuwen,shuxue |
| shuxue,yingyu,wudao |
+---------------------+
(3)
text 保存文本资料
…
3. 时间和日期型
(1) date 日期
1000-01-01 ~ 9999-12-31
账号的注册时间,员工的入职时间,社保的缴纳时间 …
mysql> create table t7 (birthday date);
mysql> insert into t7 values("1990-01-01");
mysql> insert into t7 values("19910101");
(2) time 时间
-838:00:00 ~ 838:59:59
mysql> create table t8 (t1 time);
mysql> insert into t8 values(“16:11:30”);
(3)datetime 日期时间 (使用很少)
(4) year 年
两位数: 0~69 2000~2069
70~99 1970~1999
四位数:1901~2155
mysql> create table t9 (y1 year);
mysql> insert into t9 values('00');
mysql> insert into t9 values('69');
mysql> insert into t9 values('70');
mysql> insert into t9 values('99');
mysql> insert into t9 values('1901');
(5) timestamp 时间戳
1000-01-01 00:00:00 ~ 2038-01-19 11:14:07
mysql DML 语句
建表 建库 DDL 数据定义语言
create 创建
drop 删除
数据类型
数据的插入 insert
mysql> create table t10 (id int,name varchar(30));
mysql> insert into t10 values(1,"niua");
mysql> insert into t10(id) values(2);
mysql> select * from t10;
+------+------+
| id | name |
+------+------+
| 1 | niua |
| 2 | NULL |
+------+------+
数据的修改 update
mysql> update t10 set name="niub" where id=2;
修改name为niub,对应的条件为id=2
mysql> select * from t10;
+------+------+
| id | name |
+------+------+
| 1 | niua |
| 2 | niub |
+------+------+
mysql> update t10 set name="niuc";
如果不指定修改条件,name列就都被修改了
mysql> select * from t10;
+------+------+
| id | name |
+------+------+
| 1 | niuc |
| 2 | niuc |
+------+------+
数据的删除 delete
(delete 是删除数据,不是删除表,如果删除表用drop)
mysql> delete from t10 where id=1;
mysql> select * from t10;
+------+------+
| id | name |
+------+------+
| 2 | niuc |
+------+------+
mysql> delete from t10;
mysql> select * from t10;
Empty set (0.00 sec)
DQL 数据查询语句
准备工作 (文件内可能有空格或其它符号)
[root@mysql-bin ~]# cat shop id name price city street date
1001 .1niu1 5.99 shenyang wuaijie 2013/11/12
1002 .2niu2 6.99 shenyang wuaijie 2013/11/13
1003 .3niu3 9.99 shenyang wuaijie 2013/11/15
1004 4niu4 5.39 beijing zhongguancun 2015/1/5
1005 5niu5 10.9 beijing zhongguancun 2015/1/10
1006 6niu6 20 beijing zhongguancun 2015/1/25
1007 wangqi 10.5 shanghai pudong 2017/10/11
1008 maba 100 shanghai pudong 2017/10/16
1009 zhaojiu 130 shanghai pudong 2018/3/12
1010 zhoushi 200 shenyang sanhaojie 2018/3/15
[root@mysql-bin ~]# mv shop /tmp/
进去 库2
mysql> use db2
创建表shop(表中各个列的属性)
mysql> create table shop (id int,name varchar(30),proce float(5,2),city varchar(255),street varchar(255),date date);
将文件的内容导入表:
mysql> load data local infile "/tmp/shop" into table shop;
mysql> delete from shop where id=0;
用passwd文件操作倒入数据表
mysql> create table passwd (username varchar(30),pass char(1),uid int,gid int,common varchar(255),homedir varchar(255),shell varchar(255));
mysql> load data local infile "/tmp/passwd" into table passwd fields terminated by ':';
导入数据,指定原文件的分隔符
开始查询:
1. 全表查询
mysql> select * from shop;
尽量少用
2. 单列查询
mysql> select id,name from shop;
3. 指定条件查询 where
mysql> select * from shop where id=1001;
mysql> select name,city,street from shop where id=1001;
(1)使用比较运算符
> < >= <= != <> between and
mysql> select * from shop where id between 1003 and 1006;
(2)使用逻辑运算符
and or !
mysql> select * from shop where id>=1003 and id <=1006;
mysql> select * from shop where !(id>=1003 and id <=1006);
mysql> select * from shop where city="shenyang";
字符串条件 用 ""
(3)使用算数运算符
+ - * / %
mysql> select id,proce+1 as new_proce from shop;
price+1 价格列所有数值加1 as 输出列的别名
(4)多值匹配
or
in
not in
mysql> select * from shop where id=1001 or id=1003 or id=1005;
||
mysql> select * from shop where id in (1001,1003,1005);
mysql> select * from shop where id not in (1001,1003,1005);
(5)空值匹配
NULL not NULL
查看名字是空值的hang:
mysql> select * from shop where name is NULL;
查看名字,不要空值的行:
mysql> select * from shop where name is not NULL;
(6) 模糊查询
like
通配符:
_ :表示任意的单个字符
% :表示任意的字符 (可以代表0;最好不要在查询大表的时候放在开头)
mysql> select * from shop where name like "____";
mysql> select * from shop where name like "_____";
mysql> select * from shop where name like ".%";
mysql> select * from shop where name like "%i";
mysql> select * from shop where name like "%i%";
说明:
like比较消耗时间。
不要过度使用;
不要把通配符%放在开始的位置,%i
(7) 正则表达式
. * ^ $ + ? { }
\< ==[[:<:]]
\>==[[:>:]]
转义符号 \\
regexp 正则命令 (可以使用正则)
mysql> select * from shop where name regexp "^\\.";
mysql> select * from shop where name regexp "^[0-9][a-z]{3}[0-9]"; # ^[0-9]表示数字开头 [a-z]{3} 表示中间字母三位; [0-9]表示数字
mysql> select * from shop where name regexp "[a-z]+"; # 表示包含字母的
mysql> select * from shop where name regexp "^[a-z]+"; # 表示开头为字母的
mysql> select * from shop where name regexp "^[0-9][a-z]+"; # 表示数字开头,包含字母的
mysql> select * from shop where name regexp "^[0-9][a-z]+[0-9]$"; #表示数字开头,[a-z]+包含字母,结尾数字的[0-9]$,
4. 对输出结果进行排序
order by 排序
order by 排序
mysql> select * from shop order by proce; 升序
mysql> select * from shop order by proce desc; 降序
mysql> select * from shop where proce is not null order by proce desc;
过滤空值
5. 限制输出结果
limit
mysql> select * from shop limit 0,3;
显示前3行 head -3
需求:输出价格最贵的信息?
按照 价格 降序(desc)从高到低,limit 0,1 取最高的价格。
mysql> select * from shop where proce is not null order by proce desc limit 0,1;
+------+---------+--------+----------+-----------+------------+
| id | name | proce | city | street | date |
+------+---------+--------+----------+-----------+------------+
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie | 2018-03-15 |
按照 价格 升序从低到高,limit 0,1 取最低的价格。
mysql> select * from shop where price is not null order by price limit 0,1;
+------+-------+-------+---------+--------------+------------+
| id | name | price | city | street | date |
+------+-------+-------+---------+--------------+------------+
| 1004 | 4niu4 | 5.39 | beijing | zhongguancun | 2015-01-05 |
+------+-------+-------+---------+--------------+------------+
- 去掉重复输出
distinct (词义:不同的)
去掉重复的值(空值会保留)
mysql> select distinct city from shop ;
+----------+
| city |
+----------+
| shenyang |
| beijing |
| shanghai |
| NULL |
+----------+
添加了 不显示空值
mysql> select distinct city from shop where city is not null ;
7. 分组查询
group by
mysql> create table t1 (id int,name varchar(30),sex enum("M","W"));
mysql> insert into t1 values(101,"niua","W");
mysql> insert into t1 values(102,"niub","M");
mysql> insert into t1 values(103,"niuc","M");
mysql> insert into t1 values(104,"niud","W");
mysql> insert into t1 values(105,"niue","W");
按照性别分组:(看都男的都有谁,学号是多少,女的都有谁,学号是多少):
mysql> select sex,group_concat(id),group_concat(name) from t1 group by sex;
+------+------------------+--------------------+
| sex | group_concat(id) | group_concat(name) |
+------+------------------+--------------------+
| M | 102,103 | niub,niuc |
| W | 101,104,105 | niua,niud,niue |
+------+------------------+--------------------+
按照学号大于102的显示:(注:W列虽然104,105大于102,但是104,105跟101是一组,101小于102所以那组就没了)
mysql> select sex,group_concat(id),group_concat(name) from t1 group by sex having group_concat(id)>102;
Empty set (0.00 sec)
mysql> select sex,group_concat(id),group_concat(name) from t1 group by sex having group_concat(id)>101;
+------+------------------+--------------------+
| sex | group_concat(id) | group_concat(name) |
+------+------------------+--------------------+
| M | 102,103 | niub,niuc |
+------+------------------+--------------------+
需求:输出每个城市商品价格最贵的信息?
mysql> select city,max(proce) from shop where proce is not null group by city;
+----------+------------+
| city | max(proce) |
+----------+------------+
| beijing | 20.00 |
| shanghai | 130.00 |
| shenyang | 200.00 |
+----------+------------+
8. mysql的函数
(1)字符串函数
concat( ) 字符串连接
查看 id, concat(city,"市",street)将城市和街道连接 as address 设置个别名
mysql> select id,concat(city,"市",street) as address from shop;
+------+----------------------+
| id | address |
+------+----------------------+
| 1001 | shenyang市wuaijie |
| 1002 | shenyang市wuaijie |
| 1003 | shenyang市wuaijie |
| 1004 | beijing市zhongguancun |
| 1005 | beijing市zhongguancun |
| 1006 | beijing市zhongguancun |
| 1007 | shanghai市pudong |
| 1008 | shanghai市pudong |
| 1009 | shanghai市pudong |
| 1010 | shenyang市sanhaojie |
| 1011 | NULL |
+------+----------------------+
lower( ) 大写转小写
upper( ) 小写转大写
mysql> select upper(name) from shop;
length() 字符串的长度
mysql> select length(name) from shop;
(2)数据汇总函数
count( ) 统计
用 * 查询的结果是11
mysql> select count(*) from shop
-> ;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
用name查询到的结果是10 因为name列里有空值,空值不计算数
mysql> select count(name) from shop;
+-------------+
| count(name) |
+-------------+
| 10 |
+-------------+
sum( ) 求和,要求是数值型数据
mysql> select sum(proce) from shop;
+------------+
| sum(proce) |
+------------+
| 499.76 |
+------------+
avg( ) 求平均数,要求是数值型数据
mysql> select avg(proce) from shop;
+------------+
| avg(proce) |
+------------+
| 49.976000 |
+------------+
max( ) 求最大值
min( ) 求最小值
mysql> select max(proce) from shop;
+------------+
| max(proce) |
+------------+
| 200.00 |
+------------+
mysql> select min(proce) from shop;
+------------+
| min(proce) |
+------------+
| 5.39 |
+------------+
忽略空值
(3)时间和日期函数
date( ) 返回日期
time( ) 返回时间
year( ) 返回年
month( ) 返回月
day( ) 返回日
mysql> select year(date) from shop;
(4)其他函数
查看当前时间:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-11-13 14:00:30 |
+---------------------+
查看当前MySQL版本:
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.18-log |
+------------+
查看用户:
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost | 管理员用户
+----------------+
查看当前的用户身份
查看当前在哪个数据库里边
mysql> select database();
+------------+
| database() |
+------------+
| db2 |
+------------+
查看当前在哪个数据库里面
=======================================
MySQL 的索引
什么是索引?
索引是创建在表上的特殊的文件,对表中的列进行排序的一种数据结构。
索引的功能?
为了加快查询的速度。
尤其是在大表查询。
什么时候使用索引?
表中的数据比较多,列中的数据比较整齐
大表 int或char(10)
什么时候索引无效?
where条件有 <> != (不等于)
where条件有 汇总函数
模糊查询 %xxx
列中数据有很多重复的
索引的“副作用”?
保存索引需要一定的存储空间
查询索引也需要花费时间
如果表中的数据频繁发生变化,索引也得跟着变化,数据更新会变慢
使用索引的注意事项:
如果一个表中的数据,频繁发生变化,不要建索引;
小表没有必要;
尽量选择经常被查询的列创建索引;
索引的类型:
innodb B树索引 (常用)
memory hash索引 (用的比较少)
创建索引:
1. 普通索引
(1) 在创建表的时候,创建索引
(基本没用,因为生产过程中可能不确定这个表要做什么用)
index 和 key 作用一样
将 id 列创建索引:
mysql> create table t1(id int,name char(10),index(id));
mysql> create table t1(id int,name char(10),key(id));
查看
mysql> show create table t1;
KEY `id` (`id`)
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
创建表 t2 索引是id
mysql> create table t2 (id int,name char(10),index id_index(id));
mysql> show create table t2;
KEY `id_index` (`id`)
(2)在建表之后,再创建索引
表shop,表passwd已经创建
给表shop添加索引
mysql> create index id_index on shop(id);
mysql> desc shop;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
给pass
mysql> alter table passwd add index uid_index(uid);
mysql> desc passwd;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| uid | int(11) | YES | MUL | NULL | |
删除普通索引:
mysql> drop index id on t1;
mysql> alter table t2 drop index id_index;
(2)唯一性索引
unique
约束:列里面的值是唯一的,但是空值无效。
mysql> create table t3 (id int unique,name char(11));
mysql> insert into t3 values(1,"niua");
mysql> insert into t3 values(1,"niub");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> insert into t3(name) values("niub");
mysql> insert into t3(name) values("niuc");
mysql> select * from t3;
+------+------+
| id | name |
+------+------+
| 1 | niua |
| NULL | niub |
| NULL | niuc |
+------+------+
(3) 约束:非空,列里面的值,不允许是空值。
not null 不为空
建表t4 设置id为整数 不能为空
mysql> create table t4 (id int not null,name char(10));
mysql> insert into t4(name) values("niua");
ERROR 1364 (HY000): Field 'id' doesn't have a default value
唯一性约束+非空约束=主键 primary key
mysql> create table t5 (id int unique not null,name char(10));
mysql> desc t5;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> create table t6 (id int primary key,name char(10));
主键:列的数据的唯一标识。
在主键列上的数据,是唯一的并且非空的。
ID号 身份证号
外键:外键列里面的数据,必须是主键列里面的。
构建外键:
要求:
表的存储引擎是innodb
表的主键列的数据类型和外键列的数据类型是一致的。
- 构建2个表:
- 主键表: 公司的部门信息表 部门ID(主键) 部门名称(非空)
- 外键表: 公司的员工信息表 员工ID(主键) 名字 入职时间 所在部门(部门ID,外键)
mysql> create table dept(dept_id int primary key,dept_name varchar(30) not null);
mysql> create table employees(id int primary key,name varchar(30) not null,date date,dept int,foreign key(dept) references dept(dept_id))engine=innodb;
mysql> insert into dept values(101,"admin");
mysql> insert into dept values(102,"hr");
mysql> insert into dept values(103,"edu");
mysql> insert into employees values(1001,"niua","2018-01-10",101);
mysql> insert into employees values(1002,"niub","2018-01-11",101);
mysql> insert into employees values(1003,"niuc","2018-02-11",102);
删除外键:
mysql> show create table employees;
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `dept` (`dept_id`)
mysql> alter table employees drop foreign key employees_ibfk_1;
mysql> insert into employees values(1004,"niud","2018-03-11",104);
mysql> select * from employees;
+------+------+------------+------+
| id | name | date | dept |
+------+------+------------+------+
| 1001 | niua | 2018-01-10 | 101 |
| 1002 | niub | 2018-01-11 | 101 |
| 1003 | niuc | 2018-02-11 | 102 |
| 1004 | niud | 2018-03-11 | 104 |
+------+------+------------+------+
再添加外键:
mysql> alter table employees add foreign key(dept) references dept(dept_id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`#sql-5a3_3`, CONSTRAINT `#sql-5a3_3_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `dept` (`dept_id`))
之前的104 是在删除外键后添加进员工表的,如果不删除的话就没办法操作添加外键,因为主键中没有104,必须主键先有,外键才能有.
这里将104这个部门id删除
mysql> delete from employees where dept=104;
再次添加员工的外键 关联dept 部门id的主键
mysql> alter table employees add foreign key(dept) references dept(dept_id);
级联删除 级联修改:
mysql> alter table employees drop foreign key employees_ibfk_1;
mysql> alter table employees add foreign key(dept) references dept(dept_id) on delete cascade on update cascade;
mysql> update dept set dept_id=112 where dept_name="hr";
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 101 | admin |
| 103 | edu |
| 112 | hr | <--
+---------+-----------+
mysql> select * from employees;
+------+------+------------+------+
| id | name | date | dept |
+------+------+------------+------+
| 1001 | niua | 2018-01-10 | 101 |
| 1002 | niub | 2018-01-11 | 101 |
| 1003 | niuc | 2018-02-11 | 112 | <--
+------+------+------------+------+
mysql> delete from dept where dept_id=112;
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 101 | admin |
| 103 | edu |
+---------+-----------+
mysql> select * from employees;
+------+------+------------+------+
| id | name | date | dept |
+------+------+------------+------+
| 1001 | niua | 2018-01-10 | 101 |
| 1002 | niub | 2018-01-11 | 101 |
+------+------+------------+------+
======================================
测试索引是不是会加快查询速度?
mysql> desc shop;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
mysql> set profiling=1;
开启sql查询的时间统计。
mysql> select * from shop;
mysql> select id from shop;
mysql> select * from shop where id=1003;
mysql> select id from shop where id=1003;
mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00077800 | select * from shop |
| 2 | 0.00066150 | select id from shop |
| 3 | 0.00175275 | select * from shop where id=1003 |
| 4 | 0.00049225 | select id from shop where id=1003 |
+----------+------------+-----------------------------------+
使用优化器 explain
mysql> explain select * from shop \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 100.00
Extra: NULL
mysql> explain select * from shop where id=1003\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop
partitions: NULL
type: ref
possible_keys: id_index
key: id_index
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
如果再type位置,看到了ALL,说明你写的这条SQL语句已经是最烂的了。
自增:
auto_increment
列上设置自增:
数值型数据,最好是整型
列上有唯一性约束或主键
员工ID 实现自动增加
mysql> create table t7 (id int unique auto_increment,name char(10));
mysql> desc t7;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
mysql> insert into t7(name) values("niu1");
mysql> insert into t7(name) values("niu2");
mysql> insert into t7(name) values("niu3");
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | niu1 |
| 2 | niu2 |
| 3 | niu3 |
+----+------+
默认,自增从1开始
mysql> insert into t7(name) values("niua");
mysql> insert into t7(name) values("niub");
mysql> insert into t7(name) values("niuc");
mysql> select * from t7;
+----+-------+
| id | name |
+----+-------+
| 1 | niu1 |
| 2 | niu2 |
| 3 | niu3 |
| 10 | niu10 |
| 11 | niua |
| 12 | niub |
| 13 | niuc |
+----+-------+
出现断档,从最大值开始自增
mysql> delete from t7;
表数据被delete以后,自增仍然从最大值开始
mysql> truncate t7;
清空表数据
mysql> insert into t7(name) values("niua");
mysql> insert into t7(name) values("niub");
mysql> insert into t7(name) values("niuc");
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | niua |
| 2 | niub |
| 3 | niuc |
+----+------+
===============================
默认值:
default
当信息写入,没有具体输入内容,给定一个默认值。
设置名字为默认值 "unknown",设置年龄为默认"30"
mysql> create table t8 (id int,name char(10) default "unknown",age int default 30);
mysql> desc t8;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | unknown | |
| age | int(11) | YES | | 30 | |
+-------+----------+------+-----+---------+-------+
写入数据时,只写入id,其它自动按照默认值填写
mysql> insert into t8(id) values(1);
mysql> select * from t8;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | unknown | 30 |
+------+---------+------+
多表查询:
表1:学员信息
id 主键
name 非空
tel 非空
性别
年龄
表2:学员成绩
id 外键
ule 非空
ula 非空
oracle 非空
表3:学员就业
id 外键
work enum("Y","N")
money
stu1 学生信息 id 为主键 名字不为空 电话不为空 性别不为空 年龄不为空
mysql> create table stu1 (id int primary key,name varchar(30) not null,tel char(11)not null,sex enum("M","W"),age int);
stu2 学生分数 id 各科分数 不为空 id 设为外键关联stu1的主键
mysql> create table stu2 (id int,ule tinyint not null,ula tinyint not null,oracle tinyint not null,foreign key(id) references stu1(id) on delete cascade on update cascade);
stu3 学生工作 id 是否工作 工资 id 设为外键关联stu1的主键
mysql> create table stu3 (id int,work enum("Y","N"),money int,foreign key(id) references stu1(id) on delete cascade on update cascade);
为stu1插入数据信息
mysql> insert into stu1 values(2019092801,"zhangyi",13112345678,"M",21);
mysql> insert into stu1 values(2019092802,"wanger",13122345678,"M",22);
mysql> insert into stu1 values(2019092803,"lisan",13132345678,"M",23);
mysql> insert into stu1 values(2019092804,"liusi",13132345678,"W",25);
mysql> insert into stu1 values(2019092805,"zhouwu",13152345678,"W",29);
mysql> insert into stu1 values(2019092806,"maliu",13162345678,"M",31);
为stu2插入数据信息
mysql> insert into stu2 values(2019092801,66,78,90);
mysql> insert into stu2 values(2019092802,75,68,81);
mysql> insert into stu2 values(2019092803,71,76,79);
mysql> insert into stu2 values(2019092804,80,81,75);
mysql> insert into stu2 values(2019092805,88,85,89);
mysql> insert into stu2 values(2019092806,79,77,80);
为stu2插入数据信息
mysql> insert into stu3 values(2019092801,"Y","5000");
mysql> insert into stu3 values(2019092802,"Y","6000");
mysql> insert into stu3 values(2019092803,"Y","6500");
mysql> insert into stu3 values(2019092804,"N","0");
mysql> insert into stu3 values(2019092805,"Y","7000");
表的关联查询
2个表的关联查询:
1.内部联结
mysql> select id,name,ule from stu1,stu2 where stu1.id=stu2.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql> select stu1.id,name,ule from stu1,stu2 where stu1.id=stu2.id;
||
mysql> select stu1.id,name,ule from stu1 inner join stu2 on stu1.id=stu2.id;
+------------+---------+-----+
| id | name | ule |
+------------+---------+-----+
| 2019092801 | zhangyi | 66 |
| 2019092802 | wanger | 75 |
| 2019092803 | lisan | 71 |
| 2019092804 | liusi | 80 |
| 2019092805 | zhouwu | 88 |
| 2019092806 | maliu | 79 |
+------------+---------+-----+
mysql> select stu1.id,name,ule from stu1,stu2 where stu1.id=stu2.id and ule >=80;
||
mysql> select stu1.id,name,ule from stu1 inner join stu2 on stu1.id=stu2.id where ule >=80;
+------------+--------+-----+
| id | name | ule |
+------------+--------+-----+
| 2019092804 | liusi | 80 |
| 2019092805 | zhouwu | 88 |
+------------+--------+-----+
2. 外部联结
(1)左联结
以左表为基准
mysql> select stu2.id,work from stu3 left outer join stu2 on stu3.id=stu2.id;
+------------+------+
| id | work |
+------------+------+
| 2019092801 | Y |
| 2019092802 | Y |
| 2019092803 | Y |
| 2019092804 | N |
| 2019092805 | Y |
+------------+------+
mysql> select stu2.id,work from stu2 left outer join stu3 on stu3.id=stu2.id;
+------------+------+
| id | work |
+------------+------+
| 2019092801 | Y |
| 2019092802 | Y |
| 2019092803 | Y |
| 2019092804 | N |
| 2019092805 | Y |
| 2019092806 | NULL |
+------------+------+
(2)右联结
以右表为基准
mysql> select stu2.id,work from stu3 right outer join stu2 on stu3.id=stu2.id;
mysql> select stu2.id,work from stu2 right outer join stu3 on stu3.id=stu2.id;
三个表联结:
mysql> select stu1.id,name,ula,work,money
-> from
-> (
-> (stu1 inner join stu2 on stu1.id=stu2.id)
-> inner join stu3 on stu1.id=stu3.id
-> )
-> ;
+------------+---------+-----+------+-------+
| id | name | ula | work | money |
+------------+---------+-----+------+-------+
| 2019092801 | zhangyi | 78 | Y | 5000 |
| 2019092802 | wanger | 68 | Y | 6000 |
| 2019092803 | lisan | 76 | Y | 6500 |
| 2019092804 | liusi | 81 | N | 0 |
| 2019092805 | zhouwu | 85 | Y | 7000 |
+------------+---------+-----+------+-------+
坑:笛卡尔积
没有指定联结条件的查询。
mysql> select stu1.id,ule from stu1,stu2;
子查询:
里层的查询结果,是外层的查询条件。
需求:shop表,输出商品价格最贵的人名?
(1)使用比较运算符
> < = …
条件限制:里层的查询结果输出是1个值。
查询消费最高的人名
mysql> select name from shop where proce=(select max(proce) from shop);
+---------+
| name |
+---------+
| zhoushi |
+---------+
mysql> select name from shop where proce=(select proce from shop where proce>=20);
ERROR 1242 (21000): Subquery returns more than 1 row
(2)使用 IN , not IN 关联子查询的多个值
查询消费20元以上的人名
mysql> select name from shop where proce in (select proce from shop where proce>=20);
+---------+
| name |
+---------+
| 6niu6 |
| maba |
| zhaojiu |
| zhoushi |
+---------+
查询人名,不要消费20元以上的
mysql> select name from shop where proce not in (select proce from shop where proce>=20);
体系结构:
应用接口
- mysql连接池: 默认开150个连接
- sql接口:接收客户端输入的sql语句
- 解析器:解释(翻译)接收到的sql语句
- 优化器:让sql以最快的方式执行
- 缓存:加快执行速度
- 存储引擎:完成sql的具体操作
- 文件系统:存储数据
存储引擎:
调用在文件系统上的数据,然后用sql进行操作。
看到所有的存储引擎
mysql> show engines;
看到所有的存储引擎
DEFAULT 当前mysql默认使用的存储引擎
YES 当前mysql可以使用这种存储引擎
NO 当前mysql无法使用
mysql
5.5.08 以前,默认的存储引擎是 MyISAM
以后,默认的存储引擎是 InnoDB
MyISAM:
适用于读频率远远大于写频率的应用场景。
查询的效率相当高,而且占用内存很小。
表的保存方式:
.frm 表的框架
.MYD 表的数据
.MYI 表的索引
缺点:
不支持事务操作
也不支持外键
InnoDB:
为了处理巨大的数据量,如果表的数据量很大,选择使用innodb。
提供了良好的事务处理,针对数据崩溃有很好的修复机制
缺点:读写的效率差。
支持事务:
多个更新命令组合到一起,就是一个事务。
特性:
A atomic:原子性;对数据的修改,要么执行,要么不执行
C consistency:一致性;事务在开始和结束时,数据必须保证是一致的状态
I isolation:隔离性;事务的操作不允许外界干扰
D duration:持久性;事务要一直执行到最后
表的存储方式:
innodb存储引擎使用表空间存储数据。
独立表空间:每个表都有自己的表空间文件
.frm 表的框架结构
.ibd 表的独立表空间文件
共享表空间:所有的表共享使用同一个表空间文件
.frm 表的框架结构
ibdata1 所有表的共享表空间文件
数据库:保存有组织的数据的容器;
表:保存数据的结构化文件;
行:
列:
数据类型:
表空间:是数据库里最大的逻辑单位,保存表数据
一个表空间可以包含一个或多个数据文件
一个数据文件只能属于一个表空间
例子:
数据库 仓库
表 箱子
数据 保存在箱子里面的“东西”
切换独立表空间和共享表空间
innodb_file_per_table 根据values的状态
ON 表示独立表空间
OFF 表示共享表空间
mysql> show variables like "%per_table%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON | <-- 独立表空间
+-----------------------+-------+
mysql> set global innodb_file_per_table=0;
mysql> show variables like "%per_table%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF | <-- 共享表空间
+-----------------------+-------+
mysql> use db3
mysql> create table t1(id int);
独立表空间的文件是这样显示的:
[root@mysql-bin /data/mysql/data/db3]# ls
db.opt t1.frm
修改表空间状态,在mysql主配置文件中修改:
[root@mysql-bin ~]# vim /etc/my.cnf
[mysqld]
innodb-file-per-table=[01]
0 共享
1 独立,默认的
重启服务生效
事务的提交和回滚:
默认情况下,是自动提交。
commit 是提交
rollback 是回滚
DDL语句,不受事务操作约束:
create 创建
drop 删除
alter 更改
truncate 清除
**关闭自动提交**
mysql> set autocommit=off;
关闭自动提交
mysql> insert into t1 values(1);
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
另一个终端:
mysql> select * from t1;
Empty set (0.00 sec)
mysql> commit; 提交
另一个终端:
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
mysql> update t1 set id=2 where id=1;
mysql> select * from t1;
+------+
| id |
+------+
| 2 |
+------+
另一个终端:
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
mysql> rollback; 回滚
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
3. memory
内存存储引擎
速度快。
缺点:
服务一重启,或虚拟机重启。
数据全部丢失。
mysql> create table t1 (id int,name char(10))engine=memory;
mysql> show create table t1;
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
mysql> insert into t1 values(1,"niua");
[root@mysql-bin ~]# netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 2098/mysqld
[root@mysql-bin ~]# kill 2098
[root@mysql-bin ~]# netstat -antp | grep mysqld
[root@mysql-bin ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[root@mysql-bin ~]# mysql -u root -p
Enter password:
mysql> use db3
mysql> select * from t1;
Empty set (0.00 sec)
锁:
数据库为了保证数据一致性,让访问接口有序的进行数据库操作的一种机制。
分类:
表级锁:直接锁定整个表,在上锁以后,其他用户操作该表会受限。
锁定的粒度大,发生冲突的概率比较高,并发性差。
行级锁:只锁定操作的那行,表中的其他行不受影响。
锁定的粒度小,几乎不会产生冲突,并发性高。
有可能出现死锁。
mysql5.7以后,会自动解开死锁。
mysql> create table t2(a int,b char(11));
mysql> create index a_index on t2(a);
mysql> create index b_index on t2(b);
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | int(11) | YES | MUL | NULL | |
| b | char(11) | YES | MUL | NULL | |
+-------+----------+------+-----+---------+-------+
终端1: 关闭自动提交
mysql> set autocommit=0;
mysql> insert into t2 values(1,"niua");
mysql> insert into t2 values(2,"niub");
mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | niua |
| 2 | niub |
+------+------+
提交
mysql> commit;
终端2:关闭自动提交
mysql> set autocommit=0;
提交
mysql> commit;
mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | niua |
| 2 | niub |
+------+------+
终端1:更改b 名字 对应a1 没提交
mysql> update t2 set b="niu11" where a=1;
终端2:更改b 名字 对应a1 跟终端1操作一样,被锁住 没提交
mysql> update t2 set b="niu22" where a=1;
被锁住
终端1:提交
commit;
终端2:
自动解锁,报错
提交后
commit;
看到终端1的修改
演示死锁
终端1:
mysql> update t2 set b="niu111" where a=1;
终端2:
mysql> update t2 set b="niu22" where a=2;
终端1:
mysql> update t2 set b="niu222" where a=2;
终端2:
mysql> update t2 set b="niu123" where a=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
自动解开死锁
读锁和写锁:★★★★★
读锁又称为共享锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
读锁:当给表加读锁后,所有人对该表只有读权限,不可写。
备份时加读锁。
写锁又称为排他锁,简称X锁,顾名思义,排他锁就是不能与其他所并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改
写锁:当给表加写锁后,只有自己可以对表进行读写,其他用户没有任何权限。
读锁: 所有人对该表都是只有读的权限,包括管理员
mysql> create table t3(id int);
mysql> insert into t3 values(1);
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
+------+
mysql> lock table t3 read;
给表加读锁
mysql> insert into t3 values(2);
ERROR 1099 (HY000): Table 't3' was locked with a READ lock and can't be updated
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
+------+
mysql> unlock tables;
解锁
写锁: 只有自己可以查看和修改,其它用户不可以
mysql> lock table t3 write;
给表加写锁
mysql> insert into t3 values(3);
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
其他用户:
mysql> select * from t3;
mysql> insert into t3 values(4);
被阻塞
mysql> unlock tables;
mysql> show open tables where in_use>0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| db3 | t3 | 1 | 0 |
+----------+-------+--------+-------------+
查看哪个表被加了锁
================================================
用户和权限管理:
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
mysql> use mysql
mysql> show tables;
user 用户权限
db 库权限
tables_priv 表权限
columns_priv 列权限
(1)user
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-
表示一个用户:用户名@主机名(ip)
user1@xxx
user1@172.16.x.x
权限信息:互斥 Y N
Y 该用户拥有全局权限,操控所有的库.所有的表
(2)db
mysql> desc db;
单库权限:用户可以操控库.所有的表
(3)tables_priv
mysql> desc tables_priv;
单表权限:对 库.表 拥有所有的权限
(4)columns_priv
mysql> desc columns_priv;
单列权限:对 库.表 上的某些列拥有权限
创建用户:
mysql> create user user1@‘localhost’ identified by ‘12345’;
mysql> flush privileges;
删除用户:记住
mysql> drop user user1@‘localhost’;
授予权限:可以创建用户
grant 权限列表 on 库.表 to 用户名@'主机名' identified by '密码';
权限列表:
all 所有权限
权限,权限,...
库.表:
*.* 所有的库.所有的表
库名.* 单库.所有的表
库名.表名 单库.单表
用户名@'主机名(IP)'
1. 授予user1全局权限
mysql> grant all on *.* to user1@'localhost' identified by '12345';
mysql> flush privileges;
mysql> show grants for user1@'localhost' ;
+----------------------------------------------------+
| Grants for user1@localhost |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' |
+----------------------------------------------------+
mysql> use mysql
mysql> select * from user where user="user1"\G;
user1只没有授权用户的权限。
给user1添加授权的权限 with grant option
user=root
mysql> grant all on *.* to user@'localhost' identified by '12345' with grant option;
mysql> flush privileges;
2. 单库权限
单库权限
mysql> grant all on db2.* to user2@'localhost' identified by '12345';
mysql> flush privileges;
3. 单表权限
单表权限*
mysql> grant all on db2.shop to user3@'localhost' identified by '12345';
mysql> flush privileges;
4. 单列权限
单列权限
mysql> grant select(id,name),update(proce) on db2.shop to user4@'localhost' identified by '12345';
mysql> flush privileges;
5. 远程用户访问的权限
mysql> grant all on db2.* to 自定义用户名@'伙计的IP' identified by '12345';
mysql> flush privileges;
mysql-server:
mysql> grant all on db2.* to user6@'172.16.0.9' identified by '12345';
mysql> flush privileges;
mysql-client:
mysql -h IP -u -p
[root@centos7-bj ~]# mysql -h 172.16.0.51 -u user6 -p
Enter password:
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
+--------------------+
yum install -y mariadb 安装mysql命令(新安装的虚拟机,如果没有mysql命令时)
收回权限: 删除
revoke (如果为了安全考虑,回收权限不如直接删除这个用户).因为即使回收权限这个用户还是可以登录到mysql的
mysql> show grants for user2@'localhost';
+--------------------------------------------------------+
| Grants for user2@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' | 用户的登录权限
| GRANT ALL PRIVILEGES ON `db2`.* TO 'user2'@'localhost' | 用户拥有的操作数据库的权限
+--------------------------------------------------------+
mysql> revoke all on db2.* from user2@'localhost';
收回权限
mysql> show grants for user2@'localhost';
+-------------------------------------------+
| Grants for user2@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' |
+-------------------------------------------+
会:
授权的同时,创建用户
删除用户
破解root@localhost密码?
操作的时间:
需要和业务确认,具体是时间段。
[root@mysql-bin ~]# vim /etc/my.cnf
[mysqld]
skip_grant_tables
#跳过授权表
#可以不用输入用户密码,就可以连接数据库
[root@mysql-bin ~]# netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 2738/mysqld
[root@mysql-bin ~]# kill 2738
[root@mysql-bin ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[root@mysql-bin ~]# mysql
mysql> use mysql
mysql> update user set authentication_string=password('12345') where user='root' and host='localhost';
[root@mysql-bin ~]# vim /etc/my.cnf
删掉skip_grant_tables
重启
netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 3583/mysqld
[root@mysql-bin ~]# kill 3583
[root@mysql-bin ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[root@mysql-bin ~]# mysql -u root -p
Enter password: 输入新密码
操作的时间:
需要和业务确认,具体是时间段。
MySQL 的备份和恢复
重点 ************
备份的目的?
防止数据损坏、丢失。
造成损坏的:
1. 误操作
2. 硬件故障 raid 硬盘
3. 黑客攻击
raid0 (速度快)
日志 squid的缓存
不怕损失的数据
raid1 (镜像模式,存储安全数据)
镜像
安装操作系统
mysql数据
raid5 (备盘模式 设备到过保修之后 设备需要割接 (重新换新设备) )
性能 <0 >1
备盘
raid10 (跟raid01性能一样,但是比如同时坏一块盘的话,10没有影响,01就不行)
备份的时间?
在用户量 少 的时候。
备份的内容?
全库
单库
备份策略?
完全备份: 将原数据原封不动,备份一份。
好处:恢复方便
缺点:备份时间长,浪费存储空间
适用于数据量小的场景。
差异备份: 以完全备份为基准,只备份不一样的数据
增量备份: 以上一次备份为基准,只备份不一样的数据
数据量大,可以考虑使用增量或差异备份。
mysql的备份:
自带的备份工具-mysqldump
只支持完全备份
第三方备份工具-xtrbackup
支持完全备份和增量备份
使用mysqldump进行备份:
“<” 恢复时 注意 箭头的方向 !!!
1. 全库备份
[root@mysql-bin ~]# mysqldump -u root -p --all-databases > /tmp/all.sql
Enter password:
测试恢复:
[root@mysql-bin ~]# mysql -u root -p < /tmp/all.sql
2. 单库备份
[root@mysql-bin ~]# mysqldump -u root -p db2 > /tmp/db2.sql
Enter password:
恢复:(恢复时需要先建库)
[root@mysql-bin ~]# mysql -u root -p
Enter password:
mysql> create database db2;
[root@mysql-bin ~]# mysql -u root -p db2 < /tmp/db2.sql
Enter password:
3. 单表备份
[root@mysql-bin ~]# mysqldump -u root -p db2 dept > /tmp/db2_dept.sql
Enter password:
恢复:
[root@mysql-bin ~]# mysql -u root -p db2 < /tmp/db2_dept.sql
Enter password:
4. 多库备份
[root@mysql-bin ~]# mysqldump -u root -p -B db2 mysql > /tmp/mysql_db2.sql
Enter password:
恢复:
[root@mysql-bin ~]# mysql -u root -p < /tmp/mysql_db2.sql
Enter password:
5. 多表备份
[root@mysql-bin ~]# mysqldump -u root -p db2 dept employees > /tmp/db2_dept-employees.sql
Enter password:
恢复:
[root@mysql-bin ~]# mysql -u root -p db2 < /tmp/db2_dept-employees.sql
Enter password:
备份的选项:
--lock-all-tables 备份的同时,给表加锁 (防止备份的时候写入数据)
--default-character-set=utf8 指定字符集 (防止乱码)
两个选项可以同时使用
[root@mysql-bin ~]# mysqldump -u root -p --all-databases --lock-all-tables --default-character-set=utf8 > /tmp/all.sql
Enter password:
mysql的复制技术:
重点
功能:
实时备份
同步架构
工作原理:AB复制 主从复制
master 主
slave 从
============================
lamp 单点架构
l linux
a apache 静态web协议
m mysql
p php 动态web协议
apache+php 172.16.14.8
mysql 172.16.14.2
Master(A) 有数据库
Slave (B) 空库
Master准备
安装apache及需要的动态网站需要的工具
> apache+php 172.16.14.8
[root@apache ~]# yum install -y httpd php php-mysql
[root@apache ~]# yum install -y php-gd php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-snmp php-soap php-bcmath curl curl-devel
[root@apache ~]# vim /etc/hosts
172.16.0.29 apache.up.com apache # 添加修改
[root@apache ~]# vim /etc/httpd/conf/httpd.conf
164 DirectoryIndex index.php index.html # 修改,添加index.php
[root@apache ~]# systemctl start httpd
[root@apache ~]# systemctl enable httpd
编辑php的配置文件:
[root@apache ~]# vim /etc/php.ini
211 short_open_tag = On # 修改 On
[root@apache ~]# systemctl restart httpd
写php的测试页:
[root@apache ~]# cd /var/www/html/
[root@apache /var/www/html]# ls
[root@apache /var/www/html]# vim index.php
<?php
phpinfo()
?>
浏览器访问:http://172.16.14.8/
看到测试页,OK!
Master (A)部署应用:
[root@apache /var/www/html]# lftp 172.16.0.99 # 远程的ftp拷贝资源
lftp 172.16.0.99:~> cd tar
cd ok, cwd=/tar
lftp 172.16.0.99:/tar> get qqfarm.zip
[root@apache /var/www/html]# ls
index.php qqfarm.zip
[root@apache /var/www/html]# yum install -y unzip
[root@apache /var/www/html]# unzip qqfarm.zip
[root@apache /var/www/html]# mv upload/ qqfarm
配置数据库 (mysql 172.16.14.2 ):
> mysql 172.16.14.2
[root@apache /var/www/html/qqfarm]# scp qqfarm.sql 172.16.0.51:/tmp/
[root@mysql-bin ~]# mysql -u root -p
Enter password:
mysql> create database qqfarm;
mysql> grant all on qqfarm.* to qquser@'172.16.%' identified by '12345';
mysql> flush privileges;
[root@mysql-bin ~]# mysql -u root -p qqfarm < /tmp/qqfarm.sql
Enter password:
[root@mysql-bin ~]# mysql -u root -p qqfarm
Enter password:
mysql> show tables;
看到导入的表,OK
浏览器访问:http://172.16.14.8/qqfarm/
解决访问页面配置时产生的"黄x"
[root@apache /var/www/html]# chmod -R 777 qqfarm
mysql 基于位置点的AB复制:
两个新库 master 将自己的初始位置告诉 slave
salve 带着初始位置找到master 的二进制日志 ,通过日志找变化的数据,写入到自己的中继日志,然后开启sal线程转换到自己的数据库
环境:A库已经运行一段时间,有若干数据 (qqfarm的mysql服务器)
B库是空库
172.16.14.2 带qqfarm的mysql服务器---> 有数据
[root@mysql-bin ~]# vim /etc/my.cnf
relay-log = /data/mysql/relay-log/slave_relay_bin
中继日志的位置
保存读取过来的sql
relay-log-index = slave_relay_bin.index
索引
log-slave-updates
binlog_format = row
二进制日志的格式,行
log-bin = /data/mysql/binlog/binlog
路径
log-bin-index = binlog.index
索引
innodb_log_buffer_size = 16M
缓存
server-id = 2 # 用ip号命名
172.16.14.9 mysql服务器---> 空库
[root@mysql-bin2 ~]# vim /etc/my.cnf
server-id = 9
重启mysql---> 两个服务器配置完都需要重启
[root@mysql-bin ~]# netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 1465/mysqld
[root@mysql-bin ~]# kill 1465
[root@mysql-bin ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
B同上
坑:
[root@mysql-bin /data/mysql/data]# cat auto.cnf
[auto]
server-uuid=8a13f7ef-04f0-11ea-acb7-000c29c1d40e
server的唯一标识
解决:
删掉这个文件,重启mysql就可以了。
全库备份(A库)
A库,全库备份,备份文件scp给B,B全库恢复
172.16.14.2
mysql> flush tables with read lock; # 加读锁 这个执行完后不能关闭shell窗口,否则加锁失败
给所有的表加读锁
不可以退出
172.16.14.2
[root@mysql-bin ~]# mysqldump -u root -p --all-databases > /tmp/all.sql
Enter password:
[root@mysql-bin ~]# scp /tmp/all.sql 172.16.0.52:/tmp/
全库恢复(B库):
172.16.14.9 空库
[root@mysql-bin2 ~]# mysql -u root -p < /tmp/all.sql
Enter password:
A的数据 == B的数据
测试:
A库的操作:
mysql> unlock tables;
mysql> grant replication slave,reload,super on *.* to slave@'172.16.0.52' identified by '12345';
mysql> flush privileges;
mysql> show master status;
+---------------+----------+
| File | Position |
+---------------+----------+
| binlog.000013 | 1891 |
+---------------+----------+
B库操作:
mysql> change master to
-> master_host='172.16.0.51',
-> master_port=3306,
-> master_user='slave',
-> master_password='12345',
-> master_log_file='binlog.000013',
-> master_log_pos=1891;
mysql> start slave;
启动
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.51
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000013
Read_Master_Log_Pos: 1891
Relay_Log_File: slave_relay_bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000013
Slave_IO_Running: Yes <--
Slave_SQL_Running: Yes <--
两个yes,表示成功
测试:
A库 创建,插入数据
B库 可以看到,OK. 其它操作都不能做,做了就坏了,后果很严重!!!
权限的网址
https://blog.csdn.net/zmx729618/article/details/78026497/
https://www.jb51.net/article/87979.htm
GTID模式
mysql基于GTID模式的AB复制:
什么是GTID?
1、全局唯一,一个事务对应一个GTID
2、替代传统的binlog+pos复制;使用master_auto_position=1自动匹配GTID断点进行复制
3、MySQL5.6开始支持
4、在传统的主从复制中,slave端不用开启binlog;但是在GTID主从复制中,必须开启binlog
5、slave端在接受master的binlog时,会校验GTID值 6、为了保证主从数据的一致性,多线程同时执行一个GTID工作原理:
1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。 2、slave端的i/o
线程将变更的binlog,写入到本地的relay log中。
3、sql线程从relay
log中获取GTID,然后对比slave端的binlog是否有记录。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
6、在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描
准备两个空库
172.16.14.10—> A库
172.16.14.11—> B库
172.16.14.10 空库A
[root@mysqlA ~]# vim /etc/my.cnf
server-id = 71
gtid_mode = on # 双A模式也需要分别设置这两项
开启使用gtid
enforce_gtid_consistency = 1 # 双A模式也需要分别设置这两项
强制使用
172.16.14.11 空库B
[root@mysqlB ~]# vim /etc/my.cnf
server-id = 72
gtid_mode = on # 双A模式也需要分别设置这两项
enforce_gtid_consistency = 1 # 双A模式也需要分别设置这两项
重启mysqld
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
A库操作:
mysql> grant replication slave,reload,super on *.* to slave@'172.16.0.72' identified by '12345';
mysql> flush privileges;
B库操作:
mysql> change master to
-> master_host='172.16.0.71',
-> master_port=3306,
-> master_user='slave',
-> master_password='12345',
-> master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000011
Read_Master_Log_Pos: 655
Relay_Log_File: slave_relay_bin.000004
Relay_Log_Pos: 862
Relay_Master_Log_File: binlog.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
坑:
- 使用gtid模式,mysql的AB两端,必须是空库
架构部署初期。 - 一个事务只有1个ID,约束不允许一条sql写入多个值
gtid模式当A库写入多个值(values)数据之后,备份时会报错,(可以备份但是恢复是有问题的)
[root@mysqlA ~]# mysqldump -u root -p db1 > /tmp/db1.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF.
To make a complete dump, pass --all-databases --triggers --routines --events.
上述情况添加选项--set-gtid-purged=OFF 再次执行就可以了.
--set-gtid-purged=OFF 临时关闭gtid模式进行备份
[root@mysqlA ~]# mysqldump -u root -p --set-gtid-purged=OFF db1 > /tmp/db1.sql
Enter password:
- 如果你使用gtid模式,失败,更改回位置点方式,需要将B库全部清空,重新连接。
位置点
gtid
健壮性!!! —> 多重启两遍 (投入生产环境使用前要做!!!)
A
B
关闭 A B哪个先关闭都可以。
启动 先启动A
[root@mysql-bin ~]# netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 1472/mysqld
tcp6 0 0 172.16.0.51:3306 172.16.0.52:46384 ESTABLISHED 1472/mysqld
[root@mysqlA ~]# netstat -antp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 1447/mysqld
tcp6 0 0 172.16.0.71:3306 172.16.0.72:53950 ESTABLISHED 1447/mysqld
LISTEN 监听
ESTABLISHED 连接完成
字符集
[root@mysql-bin ~]# vim /etc/my.cnf
character-set-server = utf8
collation-server = utf8_general_ci
双A模式:
2 <-> 9
10 <-> 11
单向:
2 --> 9
10–> 11
单向的AB,B是不能有任何写操作!!!
- 基于位置点
9 --> 2
172.16.14.9: (就是按照那个边单项的,反向操作)
mysql> grant replication slave,reload,super on *.* to slave@'172.16.14.2' identified by '12345';
mysql> flush privileges;
mysql> show master status;
+---------------+----------+
| File | Position |
+---------------+----------+
| binlog.000014 | 615 |
+---------------+----------+
2:
172.16.14.2 操作
mysql> change master to
-> master_host='172.16.14.9',
-> master_port=3306,
-> master_user='slave',
-> master_password='12345',
-> master_log_file='binlog.000014',
-> master_log_pos=615;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.52
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000014
Read_Master_Log_Pos: 615
Relay_Log_File: slave_relay_bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
基于gtid的:
172.16.14.11:
mysql> grant replication slave,reload,super on *.* to slave@'172.16.14.10' identified by '12345';
mysql> flush privileges;
172.16.14.10:
mysql> change master to
-> master_host='172.16.14.11',
-> master_port=3306,
-> master_user='slave',
-> master_password='12345',
-> master_auto_position=1;
mysql> start slave;
这样基于gtid的两台设备就实现AA了.
AB的常见模式:
AB
AA ***
ABB…
AABB
ABBAB 跨机房的AB复制
MySQL 的日志:
1. 错误日志
**error-log:记录mysql-server在运行的过程中,出现了哪些问题。**
[root@mysql-bin /data/mysql/log]# ls
mysql-error.log
mysql> show variables like '%log_error%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_error | /data/mysql/log/mysql-error.log | 日志路径
| log_error_verbosity | 3 | 日志级别
+---------------------+---------------------------------+
1 错误信息
2 错误和警告信息
3 错误、警告、通知信息
[root@mysql-bin ~]# vim /etc/my.cnf
log-error = /data/mysql/log/mysql-error.log
2. 通用查询日志
记录客户端连接服务器,以及执行的sql。
默认是没有开启的,因为量太大。
mysql> show variables like '%general%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | OFF | 默认是关闭的
| general_log_file | /data/mysql/log/mysql-access.log | 路径
+------------------+----------------------------------+
mysql> set global general_log=1;
临时开启
mysql> show variables like '%general%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | ON |
| general_log_file | /data/mysql/log/mysql-access.log |
+------------------+----------------------------------+
[root@mysql-bin /data/mysql/log]# ls
mysql-access.log
mysql> set global general_log=0;
关闭
[root@mysql-bin /data/mysql/log]# vim /etc/my.cnf
[mysqld]
general_log=1
3. 慢查询日志
默认关闭
用于优化sql语句。
给定一个基准值,查询时间。
mysql> show variables like '%quer%';
+----------------------------------------+-------------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------------+
| long_query_time | 10.000000 | 默认的基准值
| slow_query_log | OFF | 默认没有开启
| slow_query_log_file | /data/mysql/data/mysql-bin-slow.log | 路径
+----------------------------------------+-------------------------------------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
你有几条sql达到慢查询的要求
[root@mysql-bin /data/mysql/log]# vim /etc/my.cnf
[mysqld]
slow_query_log=1
4. 二进制日志
记录数据更新的信息
[root@mysql-bin /data/mysql/log]# vim /etc/my.cnf
log-bin = /data/mysql/binlog/binlog
log-bin-index = binlog.index
expire_logs_days = 7 保留的天数
max-binlog-size = 10M 单个日志文件的大小
重启一次mysqld,就会生成一个新的二进制日志
轮滚机制,保留 7 个。
5. 中继日志
保存来自master的日志信息
故障排除
问题
Slave_IO_Running:Connecting
Slave_SQL_Running: Yes
1.网络不通
2.账户密码错误
3.防火墙
4.mysql配置文件问题
5.连接服务器时语法
6.主服务器mysql权限
7.MASTER_LOG_POS= 配置错误
中间件–mycat
功能:实现读写分离
mysqlA 172.16.0.51
mysqlB 172.16.0.52
mycat 172.16.0.59
Mycat 设置hosts
[root@mycat ~]# vim /etc/hosts
172.16.0.51 mysqlA.up.com mysqlA
172.16.0.52 mysqlB.up.com mysqlB
172.16.0.59 mycat.up.com mycat
mycat 部署:
[root@mycat ~]# lftp 172.16.0.99
lftp 172.16.0.99:~> cd tar
cd ok, cwd=/tar
lftp 172.16.0.99:/tar> get Jdk-8u131-linux-x64.tar.gz Mycat-server-1.6.tar.gz
1. 安装java环境
[root@mycat ~]# tar zxf Jdk-8u131-linux-x64.tar.gz -C /usr/local/
[root@mycat /usr/local]# ln -s jdk1.8.0_131/ java
[root@mycat /usr/local]# vim /etc/profile
结尾添加
export JAVA_HOME=/usr/local/java
export CLASSPATH=.:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar
export PATH=$JAVA_HOME/bin:$PATH
[root@mycat /usr/local]# . /etc/profile
校验:
[root@mycat /usr/local]# java -version
java version "1.8.0_131"
[root@mycat /usr/local]# ls $JAVA_HOME/lib/tools.jar
/usr/local/java/lib/tools.jar
[root@mycat /usr/local]# ls $JAVA_HOME/lib/dt.jar
/usr/local/java/lib/dt.jar
2. 部署mycat
设置环境变量
[root@mycat ~]# tar zxf Mycat-server-1.6.tar.gz -C /usr/local/
[root@mycat /usr/local/mycat]# vim /etc/profile
export PATH=/usr/local/mycat/bin:$JAVA_HOME/bin:$PATH
[root@mycat /usr/local/mycat]# . /etc/profile
[root@mycat ~]# echo $PATH
/usr/local/mycat/bin:/usr/local/java/bin:/usr/local/java/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mycat /usr/local/mycat]# vim conf/server.xml
80 <user name="root">
在mysql A B 授权的用户名
81 <property name="password">12345</property>
root的密码
82 <property name="schemas">qqfarm</property>
数据库的名字
94 <!--
95 <user name="user">
96 <property name="password">user</property>
97 <property name="schemas">TESTDB</property>
98 <property name="readOnly">true</property>
99 </user>
100 -->
[root@mycat /usr/local/mycat/conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="qqfarm" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
写入数据库的名字,是模式名
</schema>
<dataNode name="dn1" dataHost="localhost1" database="qqfarm" />
读写分离的数据库名
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
检测心跳
<writeHost host="mysqlA" url="172.16.0.51:3306" user="root" password="12345">
执行写操作的mysql
<readHost host="mysqlB" url="172.16.0.52:3306" user="root" password="12345" />
执行读操作的mysql
</writeHost>
</dataHost>
</mycat:schema>
mysql A B 授权用户:
mysql> grant all on qqfarm.* to root@'172.16.%' identified by '12345';
mysql> flush privileges;
启动:
[root@mycat ~]# mycat start
Starting Mycat-server...
[root@mycat ~]# mycat status
Mycat-server is running (1252).
[root@mycat ~]# netstat -antp | grep java
测试:
[root@centos7-bj ~]# mysql -h 172.16.0.59 -P 8066 -u root -p
Enter password:密码是root@'172.16.%'的密码
在mysql A B 开启通用查询日志:
mysql> set global general_log=1;
[root@mysql-bin /data/mysql/log]# tail -0f mysql-access.log
2019-11-20T08:33:23.600805Z 17 Query create table t1 (id int)
2019-11-20T08:33:34.672733Z 8 Query insert into t1 values (1)
[root@mysql-bin2 /data/mysql/log]# tail -0f mysql-access.log
2019-11-20T08:33:23.572137Z 2 Query create table t1 (id int)
2019-11-20T08:33:38.967989Z 9 Query select * from t1
进行读写操作:
MySQL [qqfarm]> create table t1 (id int);
MySQL [qqfarm]> insert into t1 values (1);
MySQL [qqfarm]> select * from t1;
结果:
写操作在 A B上都执行,因为写A的同时,也会写B
读操作只在B上执行
[root@mycat /usr/local/mycat]# vim conf/server.xml
<user name="root">
<property name="password">12345</property>
<property name="schemas">qqfarm,db2</property>
[root@mycat /usr/local/mycat]# vim conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="qqfarm" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<schema name="db2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="qqfarm" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="mysqlA" url="172.16.0.51:3306" user="root" password="12345">
<readHost host="mysqlB" url="172.16.0.52:3306" user="root" password="12345" />
</writeHost>
<writeHost host="mysqlA" url="172.16.0.51:3306" user="root" password="12345">
<readHost host="mysqlB" url="172.16.0.52:3306" user="root" password="12345" />
</writeHost>
</dataHost>
</mycat:schema>
启动 Mycat
[root@mycat ~]# mycat stop
[root@mycat ~]# mycat start
[root@mycat ~]# netstat -antp | grep java
在A,B主机授权
mysql AB 授权:
mysql> grant all on db2.* to root@'172.16.%';
mysql> flush privileges;
mysql> grant all on db2.* to root@'172.16.%';
mysql> flush privileges;
字符集:★★★★★
防止数据库中,数据是 汉字,导致乱码。
mysql> show character set;
查看支持的字符集
设置utf8:
mysql> show character set like "%utf8%";
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 | <--
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+---------+---------------+--------------------+--------+
mysql> show variables like "%coll%";
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
mysql> show variables like "%char%";
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+----------+
[root@mysql-bin ~]# vim /etc/my.cnf
character-set-server = utf8
collation-server = utf8_general_ci
重启mysql
运维:
安装
部署架构 AB AA …
用户和权限
备份和恢复
日志
错误参考
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘;’ at line 1
语法错误,结尾的;打错了
ERROR 1046 (3D000): No database selected
建表的时候没有选择库(没在库里是不能建表的)
mysql> create tables t1 (name varchar(30));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘tables t1 (name varchar(30))’ at line 1
低级错误,table,不应该写s
mysql> insert into t1 values(‘明天会更好’);
ERROR 1366 (HY000): Incorrect string value: ‘\xE6\x98\x8E\xE5\xA4\xA9…’ for column ‘name’ at row 1
插入值得时候提示错误,是因为所在库没有添加中文字符集(具体操作参照上文)
mysql命令无法使用
提示:mysql: command not found
解决方法:alias mysql=/usr/local/mysql/bin/mysql
配置别名之后OK了
mysql提示stop slave
哪边提示就停哪边。
双A模式主从不同步,提示:Slave_IO_Running: No
mysql> stop slave;
mysql> change master to Master_Log_File='binlog.000001',Master_Log_Pos=98;
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
做AB库的时候,设置SLAVE权限密码时,提示错误
mysql> grant replication slave,reload,super on *.* to slave@'172.16.1.52' identified by '12345';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
答:
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave,reload,super on *.* to slave@'172.16.1.52' identified by '12345';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)