一.误删除root用户如何恢复?
方法一:
1.停止数据库
[root@db01 ~]# /etc/init.d/mysqld stop
2.跳过授权表启动mysql
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
3.进入mysql
[root@db01 ~]# mysql
4.插入root用户
mysql> use mysql
mysql> insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');
方法二：
1.停止数据库
[root@db01 ~]# /etc/init.d/mysqld stop
2.跳过授权表启动mysql
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
3.刷新
[root@db01 ~]# mysql
mysql> flush privileges;
4.授权超级用户
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;
mysql> grant all on *.* to root@'127.0.0.1' identified by '123' with grant option;
二.mysqld 的程序结构
1.连接层
1)检验用户的合法性
2)提供两种连接方式
a)TCP/IP
[root@db02 ~]# mysql -uroot -p123 -h10.0.0.51
[root@db01 ~]# mysql -uroot -p123 -h127.0.0.1
b)socket
[root@db01 ~]# mysql -uroot -p123 -S /usr/local/mysql/tmp/mysql.sock
[root@db01 ~]# mysql -uroot -p123 -hlocalhost
[root@db01 ~]# mysql -uroot -p123
使用下面方法连接成功以后
mysql> status;
Connection : 连接方式
本地连接,不需要建立三次握手,socket连接效率高,默认使用socket连接
但在以后程序连接,用户连接,第三方工具连接都要使用TCP/IP连接,因为数据库和网站不会搭建在一起,数据库是独立存在的
3)建立一个与SQL层交互的线程
2.SQL层
1)接收连接层传来的SQL语句
2)检查语法
3)检查语义(检查它属于哪种SQL语句:DDL(数据定义语言),DML(数据操作语言),DCL(数据控制语言),DQL(数据查询语言))
4)解析器:解析SQL语句,生成多种执行计划
5)优化器:接收解析器传来的多种执行计划,选择最优化的一条方式去执行
6)执行器:执行优化器传来的最优方式的SQL语句
a)建立一个与存储引擎层交互的线程
b)接收存储引擎层,返回的结构化成表的数据
7)写缓存
8)记录日志
3.存储引擎层
1)接收SQL层传来的SQL语句
2)与磁盘交互,找到数据并结构化成表的形式,返回给SQL层
3)建立一个与SQL层交互的线程
三.什么是实例?
1.MySQL的后台进程+线程+预分配的内存结构。
2.MySQL在启动的过程中会启动后台守护进程，并生成工作线程，预分配内存结构供MySQL处理数据使用。
多实例:
1)多个进程
2)多个线程
3)多个预分配的内存结构
多套配置文件:
多个端口
多套数据目录(--datadir=/usr/local/mysql/data)
多个socket文件
四.mysql的多实例
1.创建多个配置文件目录
[root@db01 ~]# mkdir -p /data/330{7..9}
[root@db01 ~]# tree /data/
/data/
├── 3307
├── 3308
└── 3309
[root@db01 ~]# ll /data/
total 0
drwxr-xr-x. 2 root root 6 May 9 09:59 3307
drwxr-xr-x. 2 root root 6 May 9 09:59 3308
drwxr-xr-x. 2 root root 6 May 9 09:59 3309
2.编辑多个配置文件
[root@db01 ~]# vim /data/3307/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
port=3307
socket=/data/3307/mysql.sock
server-id=7
log_error=/data/3307/data/mysql.err
pid=/data/3307/data/mysql.pid
========================================
[root@db01 ~]# vim /data/3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
server-id=8
log_error=/data/3308/data/mysql.err
pid=/data/3308/data/mysql.pid
=========================================
[root@db01 ~]# vim /data/3309/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
port=3309
socket=/data/3309/mysql.sock
server-id=9
log_error=/data/3309/data/mysql.err
pid=/data/3309/data/mysql.pid
注意:在配置文件下输入 :%s#7#8#g 就可以将配置文件中的所有7换成8
2.进入初始化目录
[root@db01 ~]# cd /usr/local/mysql/scripts/
3.初始化多套数据目录
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data
[root@db01 scripts]# ll /data/330*
/data/3307:
total 4
drwx------. 5 mysql mysql 127 May 9 10:11 data
-rw-r--r--. 1 root root 175 May 9 10:05 my.cnf
/data/3308:
total 4
drwx------. 5 mysql mysql 127 May 9 10:12 data
-rw-r--r--. 1 root root 175 May 9 10:06 my.cnf
/data/3309:
total 4
drwx------. 5 mysql mysql 127 May 9 10:12 data
-rw-r--r--. 1 root root 175 May 9 10:07 my.cnf
4.授权
[root@db01 scripts]# chown -R mysql.mysql /data/330*
5.启动mysql
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf &
6.检查端口
[root@db01 scripts]# netstat -lntup|grep 330
tcp6 0 0 :::3306 :::* LISTEN 41108/mysqld
tcp6 0 0 :::3307 :::* LISTEN 42206/mysqld
tcp6 0 0 :::3308 :::* LISTEN 42378/mysqld
tcp6 0 0 :::3309 :::* LISTEN 42550/mysqld
7.检查进程
[root@db01 scripts]# ps -ef|grep mysqld
root 40999 1 0 08:54 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.6.40/bin/mysqld_safe --datadir=/usr/local/mysql-5.6.40/data --pid-file=/usr/local/mysql-5.6.40/data/db01.pid
mysql 41108 40999 0 08:54 pts/0 00:00:03 /usr/local/mysql-5.6.40/bin/mysqld --basedir=/usr/local/mysql-5.6.40 --datadir=/usr/local/mysql-5.6.40/data --plugin-dir=/usr/local/mysql-5.6.40/lib/plugin --user=mysql --log-error=db01.err --pid-file=/usr/local/mysql-5.6.40/data/db01.pid
root 42056 7747 0 10:18 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf
mysql 42206 42056 0 10:18 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf --basedir=/usr/local/mysql --datadir=/data/3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/3307/data/mysql.err --pid-file=db01.pid --socket=/data/3307/mysql.sock --port=3307
root 42228 7747 0 10:18 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf
mysql 42378 42228 4 10:18 pts/0 00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf --basedir=/usr/local/mysql --datadir=/data/3308/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/3308/data/mysql.err --pid-file=db01.pid --socket=/data/3308/mysql.sock --port=3308
root 42379 7747 0 10:18 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3309/my.cnf
mysql 42550 42379 2 10:18 pts/0 00:00:02 /usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf --basedir=/usr/local/mysql --datadir=/data/3309/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/3309/data/mysql.err --pid-file=db01.pid --socket=/data/3309/mysql.sock --port=3309
root 42577 7747 0 10:20 pts/0 00:00:00 grep --color=auto mysqld
8.多实例设置密码
[root@db01 ~]# mysqladmin -uroot -p -S /data/3307/mysql.sock password 3307
[root@db01 ~]# mysqladmin -uroot -p -S /data/3308/mysql.sock password 3308
[root@db01 ~]# mysqladmin -uroot -p -S /data/3309/mysql.sock password 3309
9.多实例连接
[root@db01 ~]# mysql -uroot -p3307 -S /data/3307/mysql.sock
[root@db01 ~]# mysql -uroot -p3308 -S /data/3308/mysql.sock
[root@db01 ~]# mysql -uroot -p3309 -S /data/3309/mysql.sock
10.连接技巧(可以直接使用mysql3307,mysql3308,mysql3309连接)
[root@db01 ~]# vim /usr/local/mysql/bin/mysql3307
mysql -uroot -p3307 -S /data/3307/mysql.sock
[root@db01 ~]# vim /usr/local/mysql/bin/mysql3308
mysql -uroot -p3308 -S /data/3308/mysql.sock
[root@db01 ~]# vim /usr/local/mysql/bin/mysql3309
mysql -uroot -p3309 -S /data/3309/mysql.sock
#给执行权限
[root@db01 ~]# chmod +x /usr/local/mysql/bin/mysql330*
11.停止mysql多实例
[root@db01 ~]# mysqladmin -uroot -p3307 -S /data/3307/mysql.sock shutdown
[root@db01 ~]# mysqladmin -uroot -p3308 -S /data/3308/mysql.sock shutdown
[root@db01 ~]# mysqladmin -uroot -p3309 -S /data/3309/mysql.sock shutdown
五.mysql的结构
1.物理结构
[root@db01 ~]# ll /usr/local/mysql/data/mysql/
最底层的数据文件
2.逻辑结构
数据库管理员操作的 对象
库
表=元数据+真实的数据行
元数据=列(字段)+其他的属性(表的大小,行数...)
列=列名字+约束(数据类型,是否为空,主键,默认值...)
六.mysql 段区页
段:一个段=一张表,一个段是由多个区构成的
区:一个区是由多个页构成的的,64k为一个区(4个页为一个区)
页:mysql最小单位,一个页为16k
七.mysql客户端命令
架构:C/S
mysql 常用option(选项):
1. -u:指定用户
2. -p:指定密码
3. -S:指定socket文件
4. -P:指定端口
5. -h:指定主机域
6. -e:指定SQL语句
mysqladmin 常用的option(选项)
1. -u:指定用户
2. -p:指定密码
3. -S:指定socket文件
4. -P:指定端口
5. -h:指定主机域
6. password:指定新密码
7. shutdown:停止mysql服务
9. create oldboy:在库外创建数据库
10.[root@db01 data]# mysqladmin -uroot -p1 drop oldboy :在库外删除数据库
八.SQL语句的种类
1.DDL:数据定义语言(库和表的增删改)
1)针对数据库
增:
正规的建库语句:
mysql> create database IF NOT EXISTS oldboy CHARACTER SET=utf8 COLLATE=utf8_general_ci;
CHARACTER:字符集
COLLATE:校对规则 ci大小写不敏感 cs或bin大小写敏感
删:
drop database oldboy;
改(就是改字符集,除了字符集没有其他需要修改的):
mysql> alter database oldboy CHARACTER SET=gbk;
2)针对表的
int:整数类型最多只能输入10位
enum:枚举类型
tinyint:整数-128~128
unsigned:只能是正数
tinyint unsigned:0~255
comment:注释
增:
create table stu(
id int not null primary auto_increment comment ‘学号’,
sname varchar(20) noe null comment ‘学生姓名’,
sage tinyint unsigned comment '学生年龄',
sgender enum('m','f') noe null default ‘m’ comment ‘学生性别’,
cometime datetime noe null comment ‘入学时间’);
删:
mysql> drop table stu;
改:
往第一行添加
mysql> alter table stu add yyf varchar(20) first;
往某一行后面添加
mysql> alter table stu add dengyifan varchar(20) after egon;
往结尾添加
mysql> alter table stu add yuanhu varchar(10);
删除某个字段
mysql> alter table stu drop egon;
改表名
mysql> alter table stu rename stu1;
添加多个字段
mysql> alter table stu add test varchar(20),add qq int;
2.DCL:数据控制语言(grant,revoke)
授权权限
grant
#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
给host为%的用户权限方式:
①grant all on *.* to root@'%';
所有库,所有表
②grant all on mysql.* to root@'%';
mysql中的所有表:单库级别
③grant all on mysql.user to root@'%';
mysql库的user表:单表级别
④脱敏:单列级别
grant select(name,age,sex) on mysql.user to dev@'%';
运维或者DBA给开发人员开数据库用户:
grant select,insert,update on *.* to dev@'%' identified by '123';
收回权限
revoke
mysql> revoke select on *.* from root@'%';
mysql的all权限:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
3.DML:数据操作语言(insert,update,delete)
insert:
insert into stu values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);
insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);
insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),
('linux02',2,NOW(),'zhangsi',21,'f',NOW(),111,1234567);
update:
企业中 规范用法:where条件
修改id=1
mysql> update oldboy set id=10 where id=1;
修改整列
mysql> update oldboy set id=12 where 1=1;
delete:
规范删除(危险)
mysql> delete from student where id=3;
企业中用update代替delete做伪删除:
1)给表,添加一个状态列
mysql> alter table oldboy add status enum('1','0') default '1';
2)删除数据
mysql> update oldboy set status='0' where id=2;
3)查询数据
mysql> select * from oldboy where status='1';
4.DQL:数据查询语言(select)
select:
mysql> select * from city;
mysql> select * from city where countrycode='CHN';
mysql> select * from city where countrycode='CHN' limit 10;
mysql> select * from city limit 10,10;(刨去十个看下面十个)
mysql> select name,population from city where countrycode='CHN' and district='heilongjiang';
#世界上小于100人的人口城市是哪个国家的？
国家名, 城市名, 人口数量
country.name city.name city.population
1.传统连接
select country.name,city.name,city.population from city,country where city.countrycode=country.code and city.population<100;
2.自连接:两张表中,有相同的列名字
城市名 国家简称 语言 城市人口
SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
FROM city NATURAL JOIN countrylanguage
WHERE population > 1000000
ORDER BY population limit 10;
3.内连接:小表在前,大表在后
城市名字 国家名 城市人口
select city.name,country.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
范式： 减少数据冗余，防止产生一致性问题，把一个表作为一个原子，把一张表拆到不能再拆为止。（开发阶段设计规范）
