互联网经典架构
LNMP
LAMP
LNMTMySQL 规划和安装
2.0 规划
IP ： 10.0.0.51
hostname： db01
2.1 yum 安装思路
下载yum源
https://dev.mysql.com/downloads/repo/yum/
yum install mysql-server -y
2.2 二进制包的安装过程
2.2.1 上传并解压
mkdir -p /application/
cd /application/
[root@db01 /application]# tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@db01 /application]# mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
2.2.2 历史环境处理
[root@db01 /application]# rpm -qa |grep mariadb
[root@db01 /application]# yum remove mariadb-libs -y
2.2.3 创建数据库用户
[root@db01 /application]# useradd mysql
2.2.4 创建数据目录并授权
[root@db01 /application]# mkdir /data/mysql/data -p
2.2.5 添加环境变量
vim /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@db01 /application/mysql/bin]# source /etc/profile
2.2.6 初始化数据
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
[root@db01 ~]# yum install -y libaio-devel
2.2.7 配置文件准备
vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log_error=/data/mysql/mysql.log
server_id=6
port=3306
log_bin=/data/mysql/mysql-bin
user=mysql
[mysql]
socket=/tmp/mysql.sock
2.2.8 启动脚本
[root@db01 /application/mysql/support-files]# cp mysql.server /etc/init.d/mysqld
[root@db01 /data/mysql]# touch /data/mysql/mysql.log
[root@db01 /data/mysql]# chown -R mysql. /data/mysql/*
[root@db01 /data/mysql]#
[root@db01 /data/mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]#
[root@db01 ~]# netstat -lnp|grep 330
tcp6 0 0 :::3306 :::* LISTEN 2935/mysqld
- MySQL 的基本管理
3.1 用户和权限管理
MySQL中用户的定义
UNAME@白名单
oldboy@'%'
oldboy@'10.0.0.%'
oldboy@'10.0.0.0/255.255.254.0'
oldboy@'10.0.0.5%'
oldboy@'10.0.0.55'
oldboy@'db01'
root@'localhost'
3.2 用户的管理
创建用户，并设置密码
mysql> create user oldboy@'10.0.0.%' identified by '123';
修改密码（设置密码）
mysql> alter user oldboy@'10.0.0.%' identified by '123456';
查询用户信息
mysql> show databases；
mysql> use mysql
mysql> show tables;
mysql> select user,host,authentication_string from mysql.user;
删除用户
3.3 权限管理
grant 权限 on 作用对象 to 用户 identified by '123';
权限：
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
grant SELECT,INSERT, UPDATE, DELETE on
作用对象:
.
oldboy.*
oldboy.t1
wordpress.*
discuz.*
zhihu.*
3.4 授权案例
需求1：开一个用户，允许wordpress用户，通过10网段登录管理wordpress库下的所有表，密码是123.
mysql> grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
需求2：
授权一个应用用户app，能够通过10网段，应用app下所有表，密码123。
mysql> grant select,insert,update on app.* to app@'10.0.0.%' identified by '123';
3.5 连接管理
socket （本地套接字文件）
[root@db01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock
注意：
1. 只允许本地登录使用
2. 登录的用户必须提前创建好xxx@localhost
TCPIP （网络IP+Port）
[root@db01 ~]# mysql -uoldboy -p123 -h 10.0.0.51 -P 3306
3.6 MySQL客户端的功能
mysql命令：
?
Ctrl+L
\q quit exit Ctrl+D
\G
source /root/world.sql
Ctrl+C
use
mysqladmin
[root@db01 ~]# mysqladmin -uroot -p123 password 123456
mysqldump
[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R --triggers -E >/tmp/full.sql
source /tmp/full.sql
- SQL语句介绍
4.1 SQL 标准
SQL 89
SQL 92
SQL 99
4.2 SQL_MODE
5.7 之后采用严格模式
4.3 分类
DDL ： 数据定义语言
DCL ： 数据控制语言
DML ： 数据操作语言
DQL ： 数据查询语言
4.4 认识DDL
4.4.1 库定义
建库
3306 [(none)]>create database zh charset utf8mb4 ;
3306 [(none)]>show databases;
3306 [(none)]>show create database zh;
删库 （不代表生产操作）
drop database aaaa;
revoke drop,delete on . from oldboy@'10.0.0.%';
修改库
alter database zh charset utf8;
4.4.2 表定义
建表
CREATE TABLE stu (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
name varchar(64) CHARACTER SET armscii8 NOT NULL DEFAULT '' COMMENT '姓名',
age tinyint(3) unsigned zerofill NOT NULL COMMENT '年龄',
gender char(4) NOT NULL COMMENT '性别',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;
3306 [wordpress]>desc stu;
3306 [wordpress]>show create table stu;
删表(不代表生产操作)
drop table stu;
修改表
alter table stu add qq varchar(64) unique not null comment 'qq号';
alter table stu add telnum char(11) not null unique after name;
注意：
DDL语句需要锁表，对于大表需要做DDL修改,尽量选择业务不繁忙期间，或者使用以下工具进行修改
了解 pt-osc(online schema change)，8.0以后MySQL自己解决了Online DDL
4.5 认识 DML： （对数据记录操作）
insert
insert into stu(name,telnum,gender,qq,age) values ('ww','110','f','222222',18);
update
update stu set name='ls' where id=2;
delete （不代表生产操作）
delete from stu where id=2 ;
伪删除：
添加状态列，update 替代 delete
3306 [wordpress]>alter table stu add state tinyint not null default 1;
3306 [wordpress]>update stu set state=0 where id=2;
3306 [wordpress]>select * from stu where state=1;
truncate 物理性质删除，针对数据页
delete 逻辑性质删除，针对数据行
4.6 认识 DQL：
4.6.1 select
select 单独使用
函数应用
select database();
参数查询
select @@port;
select @@datadir;from
表，视图，子查询
select * from city ; （不代表生产操作）where
等值
select * from city where countrycode='CHN';
比较判断符
select * from city where population<100;
逻辑连接符
select * from city where countrycode='CHN' and district='shandong';
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode in ('CHN','USA');
改写
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
模糊查询
select * from city where countrycode like 'C%';
- group by 列 配合 聚合函数使用
统计中国，各个省的城市个数
select district,count(id) from city where countrycode='CHN' group by district;
select district,group_concat(name) from city where countrycode='CHN' group by district;
- having
select district,count(id) from city where countrycode='CHN' group by district having count(id)>10;
使用临时表解决having面对结果集较大的情况
- order by limit
select * from city where countrycode='CHN' order by population desc limit 5;
select district,count(id) from city where countrycode='CHN' group by district having count(id)>10;
select district,count(id) from city where countrycode='CHN' group by district having count(id)>10 order by count(id) desc limit 5;
- 其他
between and
in
exist
union all
show
