数据库mysql

DBA工作内容*

• 数据管理

  • 增删改查

• 用户管理

  • 权限管理：grant all on . to all@‘%’ identified by ‘123’;
  • 敲完这条命令就可以等着被开除了( • ̀ω•́ )✧
  • root，运维用户ops，程序连接用户(只读用户，读写用户)

• 集群管理

  数据备份、恢复
  逻辑备份（软件备份，mysql自带程序备份）
  物理备份（磁盘一比一备份）
  冷备:文件拷贝（机器关机，需要备份时拉出来备份）
  热备：(读写操作可进行，数据恢复只要追加新增数据列如主从，只能防止硬件上的损坏，程序错误防止不了）
  温备：读操作可进行，写操作不支持,每隔一段时间备份一次
  全备
  增量备份
  差异备份

  监控
  进程，端口
  集群状态
  主从复制 延时情况
  SQL读写速率
  slowlog

什么是数据库管理系统
DBMS（database management system）:组织、存储、获取、维护数据的软件，也就是对数据进行增删改查等操作的软件。

RDBMS
以多张二维表的方式来存储，又给多张表建立了一定的关系（关系型数据库）

NoSQL
nosql 很多以json格式进行存储数据的（mogodb）

RDMS与NoSQL对比

数据库市场

MySQL的市场应用

• 中、大型互联网公司
• 市场空间：互联网领域第一
• 趋势明显
• 同源产品：MariaDB、PerconaDB

类似产品

• 微软：SQLserver
  • 微软和sysbase合作开发的产品，后来自己开发，windows平
  • 三、四线小公司，传统行业在用
• IBM：DB2
  • 市场占有量小
  • 目前只有：国有银行（人行，中国银行，工商银行等）、中国移动应用
• PostgreSQL
• MongoDB
• Redis

MySQL发展史

• 1979年，报表工具Unireg出现。
• 1985年，以瑞典David Axmark为首，成立了一家公司（AB前身），ISAM引擎出现。
• 1990年，提供SQL支持。
• 1999年-2000年，MySQL AB公司成立，并公布源码，开源化。
• 2000年4月BDB引擎出现，支持事务。
• 2008年1月16日 MySQL被Sun公司收购。
• 2009年4月20日Oracle收购Sun公司，MySQL转入Oracle门下

mysql安装

脚本安装

yum install -y wget
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40 /application/mysql
cd /application/mysql/support-files
\cp my-default.cnf /etc/my.cnf
\cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
useradd mysql -s /sbin/nologin -M
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql --data=/application/mysql/data
echo 'export PATH="/application/mysql/bin:$PATH"' >  /etc/profile.d/mysql.sh
source /etc/profile
cat <<EOF > /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://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
cat <<EOF >>  /etc/my.cnf
basedir = /application/mysql/
datadir = /application/mysql/data
EOF
systemctl daemon-reload
/etc/init.d/mysqld start
mysqladmin -uroot password '123456'
mysql -uroot -p123456

rpm、yum安装

• 安装方便、安装快速、无法定制

• 安装mariadb-server

[root@node1 ~]# yum install mariadb mariadb-server -y 

• 初始化

[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# mysql_secure_installation
Enter current password for root (enter for none): 当前root用户密码为空，所以直接
敲回车
OK, successfully used password, moving on...
Set root password? [Y/n] y 设置root密码
New password:
Re-enter new password:
Password updated successfully!
Remove anonymous users? [Y/n] y 删除匿名用户
... Success!
Disallow root login remotely? [Y/n] y 禁止root远程登录
... Success!
Remove test database and access to it? [Y/n] y 删除test数据库
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reload privilege tables now? [Y/n] y 刷新授权表，让初始化生效
... Success!

• 查看并启用数据库状态

[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor
preset: disabled)
 Active: active (running) since 一 2020-09-14 22:38:17 CST; 10s ago
Process: 1634 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID
(code=exited, status=0/SUCCESS)
Process: 1550 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n
(code=exited, status=0/SUCCESS)
Main PID: 1633 (mysqld_safe)

• 登陆数据库

[root@node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
MariaDB [(none)]>

• 配置文件

[root@node1 ~]# vim /etc/my.cnf
[client]  # 客户端基本配置
port     = 3306 #客户端默认连接端口
socket    = /tmp/mysql.sock  #用于本地连接的socket套接字
[mysqld]  # 服务端基本配置
port     = 3306 # mysql监听端口
socket    = /tmp/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接
字文件
user   = mariadb # mysql启动用户
basedir = /usr/local/mariadb # 安装目录
datadir = /data/mysql # 数据库数据文件存放目录
log_error = /data/mysql/mariadb.err #记录错误日志文件
pid-file = /data/mysql/mariadb.pid  #pid所在的目录
skip-external-locking  #不使用系统锁定，要使用myisamchk,必须关闭服务器

基础管理
密码相关设置
管理员密码的设定

[root@localhost ~]# mysqladmin -uroot -p password 1
Enter password: <首次设置直接回车，第二次修改密码时此处输入旧密码>

管理员密码忘记

• 关闭数据库

[root@localhost ~]# systemctl stop mariadb.service

• 跳过授权登录

mysqld_safe --skip-grant-tables --skip-networking & 

• 登陆mysql并修改密码

[root@localhost ~]# mysql
MariaDB [(none)]> use mysql
MariaDB [mysql]> grant all on *.* to root@'localhost' identified by '777';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-
tables option so it cannot execute this statement
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> grant all on *.* to root@'localhost' identified by '777';
Query OK, 0 rows affected (0.00 sec)

• 重启数据库

[root@localhost ~]# pkill mysqld
[root@localhost ~]# systemctl restart mariadb

源码安装

• MySQL版本选择：

  • https://downloads.mysql.com/archives/community/
  • 5.6：GA(稳定版) 6-12个月 小版本是偶数版是稳定版，奇数版本是开发版
  • 5.7：选择5.17版本以上，支持MGR（MySQL自带的高可用）

• 下载源码，并且配置编译环境

[root@localhost ~]#wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40.tar.gz
[root@localhost ~]#tar xzvf mysql-5.6.40.tar.gz
[root@localhost ~]#cd mysql-5.6.40
[root@localhost mysql-5.6.40]# yum install -y ncurses-devel libaio-devel cmake gcc gcc-c++ glibc

• 创建MySQL用户

useradd mysql -s /sbin/nologin -M

• 编译并安装

[root@localhost mysql-5.6.40]# mkdir /application
[root@localhost application]cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.40 -DMYSQL_DATADIR=/application/mysql-5.6.40/data -DMYSQL_UNIX_ADDR=/application/mysql-5.6.40/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0
[root@localhost mysql-5.6.40]# echo $?
0
[root@localhost mysql-5.6.40]#make
[root@localhost mysql-5.6.40]#make install

• 创建配置文件

[root@localhost mysql-5.6.40]ln -s /application/mysql-5.6.38/ /application/mysql
[root@localhost mysql-5.6.40]cd /application/mysql/support-files/
[root@localhost mysql-5.6.40]cp my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y         #系统自带默认配置

• 配置文件

[root@localhost mysql]# ll
总用量 44
drwxr-xr-x  2 root root  4096 3月  12 09:42 bin   #可启动文件
-rw-r--r--  1 root root 17987 2月  26 2018 COPYING
drwxr-xr-x  3 root root    18 3月  12 09:42 data   #数据存放文件
drwxr-xr-x  2 root root    55 3月  12 09:42 docs   
drwxr-xr-x  3 root root  4096 3月  12 09:42 include
drwxr-xr-x  3 root root   291 3月  12 09:42 lib
drwxr-xr-x  4 root root    30 3月  12 09:42 man
drwxr-xr-x 10 root root  4096 3月  12 09:42 mysql-test
-rw-r--r--  1 root root  2496 2月  26 2018 README
drwxr-xr-x  2 root root    30 3月  12 09:42 scripts   #脚本文件
drwxr-xr-x 28 root root  4096 3月  12 09:42 share
drwxr-xr-x  4 root root  4096 3月  12 09:42 sql-bench
drwxr-xr-x  2 root root   136 3月  12 09:42 support-files   #初始化文件

[root@localhost support-files]# ll
总用量 32
-rwxr-xr-x 1 root root  1153 3月  12 09:25 binary-configure
-rw-r--r-- 1 root root   773 2月  26 2018 magic
-rw-r--r-- 1 root root  1126 3月  12 09:25 my-default.cnf  #默认配置文件
-rwxr-xr-x 1 root root  1061 3月  12 09:25 mysqld_multi.server
-rwxr-xr-x 1 root root   939 3月  12 09:25 mysql-log-rotate
-rwxr-xr-x 1 root root 10619 3月  12 09:25 mysql.server

• 创建启动脚本

cp mysql.server /etc/init.d/mysqld   

• 初始化数据库

[root@localhost mysql-5.6.40]cd /application/mysql/scripts/
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/

• 启动数据库

mkdir /application/mysql/tmp
chown -R mysql.mysql /application/mysql*
/etc/init.d/mysqld start

• 配置环境边练

[root@localhost scripts]# cat /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"
[root@localhost scripts]# source /etc/profile

• systemd管理mysql

vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://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

• 设置开机自启动，并开启mysql服务

systemctl start mysqld && systemctl enable mysqld

• 设置mysql密码，并且登陆测试

mysqladmin -uroot password '123456'
mysql -uroot -p123456
mysql> show databases;
mysql> \q
Bye

二进制安装

• 下载二进制包

[root@localhost ~]wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

• 然后的步骤就和编译安装一样

[root@localhost ~]mkdir /application
[root@localhost ~]mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
[root@localhost ~]ln -s /application/mysql-5.6.40 /application/mysql
[root@localhost support-files]cd /application/mysql/support-files
[root@localhost support-files]cp my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
[root@localhost support-files]cp mysql.server /etc/init.d/mysqld
[root@localhost support-files]cd /application/mysql/scripts
[root@localhost scripts]useradd mysql -s /sbin/nologin -M
[root@localhost scripts]yum -y install autoconf
[root@localhost scripts]./mysql_install_db --user=mysql --basedir=/application/mysql --
data=/application/mysql/data
[root@localhost scripts]cat /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"
[root@localhost scripts]source /etc/profile

• 需要注意，官方编译的二进制包默认是在 /usr/local 目录下的，我们需要修改配置文件

sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld
/application/mysql/bin/mysqld_safe

• 创建systemd管理文件，并且测试是否正常使用

[root@localhost scripts]vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://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


vim /etc/my.cnf
basedir = /application/mysql/
datadir = /application/mysql/data
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
mysqladmin -uroot password '123456'
mysql -uroot -p123456

mysql体系结构

客户端与服务器模型

• mysql是一个典型的C/S服务结构

  • mysql自带的客户端程序（/application/mysql/bin）

  • mysql

  • mysqladmin

  • mysqldump

• mysqld一个二进制程序，后台的守护进程

  • 单进程
  • 多线程

• 应用程连接MySQL方式

  • TCP/IP的连接方式
    • mysql -uroot -p123456 -h127.0.0.1

  [root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1
  Warning: Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor. Commands end with ; or \g.
  Your MySQL connection id is 4
  Server version: 5.6.40 MySQL Community Server (GPL)
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  mysql> status
  --------------
  mysql Ver 14.14 Distrib 5.6.40, for linux-glibc2.12 (x86_64) using EditLine
  wrapper
  Connection id:      4
  Current database:
  Current user:      root@localhost
  SSL:          Not in use
  Current pager:     stdout
  Using outfile:      ''
  Using delimiter:    ;
  Server version:     5.6.40 MySQL Community Server (GPL)
  Protocol version:    10
  Connection:       127.0.0.1 via TCP/IP
  Server characterset:  latin1
  Db   characterset:  latin1
  Client characterset:  utf8
  Conn. characterset:  utf8
  TCP port:        3306
  Uptime:         1 hour 55 min 9 sec
  Threads: 2 Questions: 18 Slow queries: 0 Opens: 67 Flush tables: 1 Open
  tables: 60 Queries per second avg: 0.002
  

  • 套接字连接方式
    • mysql -uroot -p123456 -S /tmp/mysql.sock

MySQL服务器构成
mysqld服务结构

• 实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存
  • 公司=老板+经理+员工+办公室

• 连接层

  • 验证用户的合法性(ip,端口,用户名)
  • 提供两种连接方式(socket,TCP/IP)
  • 验证操作权限
  • 提供一个与SQL层交互的专用线程

• SQL层

  • 接受连接层传来的SQL语句

  • 检查语法

  • 检查语义(DDL,DML,DQL,DCL)

  • 解析器，解析SQL语句，生成多种执行计划

  • 优化器，根据多种执行计划，选择最优方式

  • 执行器，执行优化器传来的最优方式SQL

    • 提供与存储引擎交互的线程
    • 接收返回数据，优化成表的形式返回SQL

  • 将数据存入缓存

  • 记录日志，binlog存储引擎

重置密码

[root@localhost support-files]# /etc/init.d/mysqld start
[root@localhost support-files]# ps aux | grep mysql
root      16370  0.0  0.0 113412  1616 pts/0    S    10:08   0:00 /bin/sh /application/mysql-5.6.38/binmysqld_safe --datadir=/application/mysql-5.6.38/data --pid-file=/application/mysql-5.6.38/data/localhost.localdomain.pid
mysql     16478  0.1 22.1 1364640 449040 pts/0  Sl   10:08   0:02 /application/mysql-5.6.38/bin/mysqld --basedir=/application/mysql-5.6.38 --datadir=/application/mysql-5.6.38/data --plugin-dir=/application/mysql-5.6.38/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/application/mysql-5.6.38/data/localhost.localdomain.pid
root      17088  0.0  0.0 112824   988 pts/0    S+   10:39   0:00 grep --color=auto mysql

mysql> select user,host from mysql.user order by host desc,user desc;
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | localhost.localdomain |
|      | localhost.localdomain |
| root | localhost             |
|      | localhost             |
| root | ::1                   |
| root | 127.0.0.1             |
+------+-----------------------+
6 rows in set (0.00 sec)

• 危险操作，清空用户表

mysql> truncate mysql.user;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
Empty set (0.01 sec)
[root@localhost mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@localhost mysql]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

• 抢救措施

[root@localhost mysql]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@localhost mysql]# mysqld_safe --skip-grant-tables &
[root@server1 ~]# mysql -uroot -h192.168.136.135
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 
[root@localhost mysql]# /etc/init.d/mysqld stop
Shutting down MySQL.... SUCCESS! 

[root@localhost mysql]# mysqld_safe --skip-grant-tables --skip-network&
mysql> grant all privileges on *.* to root@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

• 设置管理员

[root@localhost mysql]# /etc/init.d/mysqld stop
Shutting down MySQL.... SUCCESS! 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'192.168.136.%' identified by '123456' with grant option;

Mysql启动关闭流程

• 启动

/etc/init.d/mysqld start ------> mysqld_safe(安全) ------> mysqld 

• 关闭

/etc/init.d/mysqld stop
mysqladmin -uroot -p123456 shutdown
kill -9 pid ?
killall mysqld ?
pkill mysqld ?

mysql逻辑结构

MySQL的逻辑对象：做为管理人员或者开发人员操作的对象

• 库
• 表：元数据+真实数据行
• 元数据：列+其它属性（行数+占用空间大小+权限）
• 列：列名字+数据类型+其他约束（非空、唯一、主键、非负数、自增长、默认值）
• 最直观的数据：二维表，必须用库来存放

mysql逻辑结构与Linux系统对比

mysql	linux
库	目录
show databases;	ls -l /
use mysql	cd /mysql
表	文件
show tables;	ls

mysql的物理结构

• MySQL的最底层的物理结构是数据文件，也就是说，存储引擎层，打交道的文件，是数据文件。

• 存储引擎分为很多种类（Linux中的FS）

• 不同存储引擎的区别：存储方式、安全性、性能

  myisam:

  • mysql自带的表部分就是使用的myisam

  mysql> show create table mysql.user\G
  ...
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global
  privileges'
  

• 查看数据文件

  [root@localhost mysql]# ll data/mysql/
  -rw-rw----. 1 mysql mysql  10684 8月  7 20:54 user.frm
  -rw-rw----. 1 mysql mysql   520 8月  7 20:55 user.MYD  #数据信息
  -rw-rw----. 1 mysql mysql  2048 8月  7 20:55 user.MYI   #索引相关
  

  innodb:

• 自己创建一个表，在编译的时候已经默认指定使用innodb

  mysql> show create table test\G
  *************************** 1. row ***************************
     Table: test
  Create Table: CREATE TABLE `test` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  1 row in set (0.00 sec)
  

• 查看数据文件

  [root@localhost mysql]# ll data/test/
  -rw-rw----. 1 mysql mysql  8586 8月  7 21:27 test.frm  #表解构
  -rw-rw----. 1 mysql mysql 98304 8月  7 21:27 test.ibd  #表空间
  

  段、区、页（块）

• 段：理论上一个表就是一个段，由多个区构成，（分区表是一个分区一个段）

• 区：连续的多个页构成

• 页：最小的数据存储单元，默认是16k

Mysql用户权限管理

Mysql用户基础操作

• Linux用户的作用

  • 登录系统
  • 管理系统文件

• Linux用户管理

  • 创建用户: useradd adduser
  • 删除用户: userdel
  • 修改用户: usermod

• Mysql用户管理

![在这里插入图片描述](https://img-blog.csdnimg.cn/0a2270a0b72e420c9a59b809a60110af.png

• 创建用户: create user

• 删除用户: delete user drop user

• 修改用户: update

• 用户的定义

  • username@‘主机域’

  • 主机域:可以理解为是Mysql登录的白名单

  • 主机域格式：

    • 10.1.1.12
    • 10.1.0.1%
    • 10.1.0.%
    • 10.1.%.%
    • %
    • localhost
    • 192.168.1.1/255.255.255.0

  • 查看用户登录匹配顺序

    • 如果多个用户名都能匹配的上，会根据host排序去匹配，所以有可能给予白名单依旧会登录
      不了

    • select user,host from mysql.user order by host desc,user desc;  #asc升序
      

• 刚装玩MySQL数据库该做的事情

  • 设定初始密码

  mysqladmin -uroot password '123456'
  * 忘记root密码
  /etc/init.d/mysqld stop
  mysqld_safe --skip-grant-tables --skip-networking
  # 修改root密码
  update user set password=PASSWORD('123456') where user='root' and
  host='localhost';
  flush privileges;
  

用户管理

• 创建用户和查看用户

mysql> create user user01;
Query OK, 0 rows affected (0.00 sec)
mysql> create user user01@'192.168.175.%' identified by '123456';
mysql> select user,host from mysql.user; 

• 删除用户

mysql> drop user user01@'192.168.175.%'; 

• 修改密码

# set password=PASSOWRD('123456')
mysql>use mysql
mysql> update user set password=PASSWORD('user01') where user='root' and host='localhost';
mysql> grant all privileges on *.* to user01@'192.168.175.%' identified by
'123456';

用户权限介绍

• 权限

INSERT,SELECT, 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   all privileges   on   *.*   to  user01@'192.168.175.%'
identified by  '123';
       权限        作用对象     归属        密码

作用对象分解

• . [当前MySQL实例中所有库下的所有表]
• wordpress. [当前MySQL实例中wordpress库中所有表（单库级别）]*
• wordpress.user [当前MySQL实例中wordpress库中的user表（单表级别）]

企业中权限的设定

• 开发人员说：给我开一个用户
• 沟通
  • 你需要对哪些库、表进行操作
  • 企业中权限的设定

grant select,update,delete,insert on *.* to 'user01'@'192.168.175.%' identified
by '123456';

实验思考问题

#创建wordpress数据库
create database wordpress;
#使用wordpress库
use wordpress;
#创建t1、t2表
create table t1 (id int);
create table t2 (id int);
#创建blog库
create database blog;
#使用blog库
use blog;
#创建t1表
create table tb1 (id int)

授权

grant select on *.* to wordpress@’10.0.0.5%’ identified by ‘123’;
grant insert,delete,update on wordpress.* to wordpress@’10.0.0.5%’ identified
by ‘123’;
grant all on wordpress.t1 to wordpress@’10.0.0.5%’ identified by ‘123’;

• 一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，
  • 对t1表的管理能力？
  • 对t2表的管理能力？
  • 对tb1表的管理能力？
• 解
  • 同时满足1，2，3，最终权限是1+2+3
  • 同时满足了1和2两个授权，最终权限是1+2
  • 只满足1授权，所以只能select
• 结论
  • 如果在不同级别都包含某个表的管理能力时，权限是相加关系。
  • 但是我们不推荐在多级别定义重复权限。
  • 最常用的权限设定方式是单库级别授权，即：wordpress.*

MySQL连接管理

• MySQL自带的连接工具
  • mysql
    • -u:指定用户
    • -p:指定密码
    • -h:指定主机
    • -P:指定端口
    • -S:指定sock
    • -e:指定SQL
• 第三方连接工具
  • sqlyog
  • navicat
  • phpmyadmi

出现问题：

1. 如果在业务繁忙的情况下，数据库不会释放pid和sock文件
2. 号称可以达到和Oracle一样的安全性，但是并不能100%达到
3. 在业务繁忙的情况下，丢数据（补救措施，高可用）

Mysql实例初始化配置

• 在启动一个实例的时候，必须要知道如下的问题

  • 我不知道我的程序在哪？
  • 我也不知道我将来启动后去哪找数据库？
  • 将来我启动的时候启动信息和错误信息放在哪？
  • 我启动的时候sock文件pid文件放在哪？
  • 我启动，你们给了我多少内存？
    …若干问题
    

• 预编译：cmake去指定，硬编码到程序当中去

• 在命令行设定启动初始化配置

  --skip-grant-tables
  --skip-networking
  --datadir=/application/mysql/data
  --basedir=/application/mysql
  --defaults-file=/etc/my.cnf
  --pid-file=/application/mysql/data/db01.pid
  --socket=/application/mysql/data/mysql.sock
  --user=mysql
  --port=3306
  --log-error=/application/mysql/data/db01.err
  

• 初始化配置文件（/etc/my.cnf）

  • 配置文件读取顺序：
    • /etc/my.cnf
    • /etc/mysql/my.cnf
    • $MYSQL_HOME/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
    • defaults-extra-file （类似include）
    • ~/.my.cnf(文件是以覆盖的方式读取)

• –defaults-file：默认配置文件

  • 如果使用./bin/mysqld_safe 守护进程启动mysql数据库时，使用了 --defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件。

  #cmake：
  socket=/application/mysql/tmp/mysql.sock
  #命令行：
  --socket=/tmp/mysql.sock
  #配置文件：
  /etc/my.cnf中[mysqld]标签下：socket=/opt/mysql.sock
  #default参数：
  --defaults-file=/tmp/a.txt配置文件中[mysqld]标签下：socket=/tmp/test.sock
  

• 优先级结论

  • 命令行
  • defaults-file
  • 配置文件
  • 预编译

• 初始化配置文件功能

  • 影响实例的启动（mysqld）
  • 影响到客户端

• 配置标签分类

  • [client]所有客户端程序
  • [server]所有服务器程序

mysqld_safe --defaults-file=/root/666.txt -server-id=888

MySQL多实例配置

多实例

• 多套后台进程+线程+内存结构
• 多个配置文件
  • 多端口
  • 多socket文件
  • 多个日志文件
  • 多个server_id
• 多套数据

实战配置

#创建数据目录
mkdir -p /data/330{7..9}
#创建配置文件
touch /data/330{7..9}/my.cnf
touch /data/330{7..9}/mysql.log
#编辑3307配置文件
vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
log-bin=/data/3307/mysql-bin
server_id=7
port=3307
[client]
socket=/data/3307/mysql.sock
#编辑3308配置文件
vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
log-bin=/data/3308/mysql-bin
server_id=8
port=3308
[client]
socket=/data/3308/mysql.sock
#编辑3309配置文件
vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
log-bin=/data/3309/mysql-bin
server_id=9
port=3309
[client]
socket=/data/3309/mysql.sock
#初始化3307数据
/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3307/my.cnf \
--basedir=/application/mysql --datadir=/data/3307/data
#初始化3308数据
/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3308/my.cnf \
--basedir=/application/mysql --datadir=/data/3308/data
#初始化3309数据
/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3309/my.cnf \
--basedir=/application/mysql --datadir=/data/3309/data
#修改目录权限
chown -R mysql.mysql /data/330*
#启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
#查看server_id
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
# 进入单独的mysql实例
mysql -S /data/3307/mysql.sock -uroot
# 关闭实例
mysqladmin -S /data/3307/mysql.sock -uroot shutdown
mysqladmin -S /data/3308/mysql.sock -uroot shutdown
mysqladmin -S /data/3309/mysql.sock -uroot shutdown

客户端工具

mysql

• 作用
  • 连接
  • 管理
• 自带的命令

\h 或 help 或？   查看帮助
\G         格式化查看数据（key：value）
\T 或 tee(写入配置文件)      记录日志
\c（5.7可以ctrl+c）  结束命令
\s 或 status     查看状态信息
\. 或 source     导入SQL数据
\u或 use       使用数据库
\q 或 exit 或 quit  退出
\! 或 system     执行shell命令

mysql> select *  from t1 join t2
    -> \c
mysql>
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:       world
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.40 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /application/mysql-5.6.38/tmp/mysql.sock
Uptime:                 2 hours 43 min 36 sec

Threads: 3  Questions: 137  Slow queries: 0  Opens: 95  Flush tables: 1  Open tables: 88  Queries per second avg: 0.013
--------------

mysqladmin

• “强制回应 (Ping)”服务器
• 关闭服务器
• 创建和删除数据库
• 显示服务器和版本信息
• 显示或重置服务器状态变量
• 设置口令
• 重新刷新授权表
• 刷新日志文件和高速缓存
• 启动和停止复制

[root@localhost ~]# mysqladmin -uroot -p1 create hellodb
[root@localhost ~]# mysqladmin -uroot -p1 drop hellodb
[root@localhost ~]# mysqladmin -uroot -p1 ping 检查服务端状态的
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器运行状态
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器状态 --sleep 2 --count 10
# 每两秒钟显示
# ⼀次服务器实时状态⼀共显示10次
[root@localhost ~]# mysqladmin -uroot -p1 extended-status 显示状态变量
[root@localhost ~]# mysqladmin -uroot -p1 variables 显示服务器变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-privileges 数据库重读授权表，等同于
reload
[root@localhost ~]# mysqladmin -uroot -p1 flush-tables 关闭所有已经打开的表
[root@localhost ~]# mysqladmin -uroot -p1 flush-threds 重置线程池缓存
[root@localhost ~]# mysqladmin -uroot -p1 flush-status 重置⼤多数服务器状态变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-logs ⽇志滚动。主要实现⼆进制和中继⽇
志滚动
[root@localhost ~]# mysqladmin -uroot -p1 flush-hosts 清楚主机内部信息
[root@localhost ~]# mysqladmin -uroot -p1 kill 杀死线程
[root@localhost ~]# mysqladmin -uroot -p1 refresh 相当于同时执⾏flush-hosts flush-
logs
[root@localhost ~]# mysqladmin -uroot -p1 shutdown 关闭服务器进程
[root@localhost ~]# mysqladmin -uroot -p1 version 服务器版本以及当前状态信息
[root@localhost ~]# mysqladmin -uroot -p1 start-slave 启动复制，启动从服务器复制线程
[root@localhost ~]# mysqladmin -uroot -p1 stop-slave 关闭复制线程

mysqldump

• 备份数据库和表的内容

  mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db
  # 备份所有数据库
  mysqldump -uroot -p test > /backup/mysqldump/test.db
  # 备份指定数据库
  mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db
  # 备份指定数据库指定表(多个表以空格间隔)
  mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 >
  /backup/mysqldump/test2.db
  # 备份指定数据库排除某些表
  

SQL语句

• SQL是结构化的查询语句
• SQL的种类
  • DDL:数据定义语句
  • DCL:数据控制语言
  • DML:数据操作语言
  • DQL:数据查询语言

DDL:数据操作语言

• 对库或者表进行操作的语句
• 创建数据库

help create database;
# 查看创建数据库语句帮助
create database db01;
# 创建数据库
create database DB01;
# 数据库名区分大小写(注意windows里面不区分)
# show variables like 'lower%'; 可以看到默认是区分大小写的
show databases;
# 查看数据库(DQL)
show create database db01;
# 查看创建数据库语句
create database db02 charset utf8;
# 创建数据库的时候添加属性

• 删除数据库

  drop database db02;
  # 删除数据库db02
  

• 修改定义数据库

  alter database db01 charset utf8;
  show create database db01;
  

• 创建表

  help create table;
  # 查看创表语句的帮助
  create table student(
  sid int,
  sname varchar(20)  #用多少占多少字节，最大分配20字节,
  sage tinyint ,
  sgender enum('m','f'),
  comtime datetime
  );
  # 创建表，并且定义每一列
  

• 数据类型(下面有完整的)

int	整数 -231~230
tinyint	整数 -128~127
varchar	字符类型(可变长)
char	字符类型(定长)
enum	枚举类型
datetime	时间类型 年月日时分秒
not null	不允许是空
primary key	primary key
auto_increment	自增，此列必须是primary key或者unique key
unique key	单独的唯一的
default	默认值
unsigned	unsigned
comment	注释

• 创建表

create table student(
sid int not null primary key auto_increment comment '学号',
sname varchar(20) not null comment '学生姓名',
sgender enum('m','f') not null default 'm' comment '学生性别',
cometime datetime not null comment '入学时间'
)charset utf8 engine innodb;
# 带数据属性创建学生表
show create table student;
# 查看建表语句
show tables;
# 查看表
desc student;
# 查看表中列的定义信息

• 删除表

  drop table student; 
  

• 修改表定义

  alter table student rename stu;
  # 修改表名
  alter table stu add age int;
  # 添加列和列数据类型的定义
  alter table stu add test varchar(20),add qq int;
  # 添加多个列
  alter table stu add classid varchar(20) first;
  # 指定位置进行添加列(表首)
  alter table stu add phone int after age;
  # 指定位置进行添加列（指定列）
  alter table stu drop qq;
  # 删除指定的列及定义
  alter table stu modify sid varchar(20);
  # 修改列及定义（列属性）
  alter table stu change phone telphone char(20);
  # 修改列及定义（列名及属性）
  

DCL数据控制语言

• DCL是针对权限进行控制
• 授权

grant all on *.* to root@'192.168.175.%' identified by '123456'
# 授予root@'192.168.175.%'用户所有权限(非超级管理员)
grant all on *.* to root@'192.168.175.%' identified by '123456' with grant
option;
# 授权一个超级管路员
with
max_queries_per_hour 2：#一个用户每小时可发出的查询数量
max_updates_per_hour 2 ：#一个用户每小时可发出的更新数量
max_connections_per_hour 2：#一个用户每小时可连接到服务器的次数
max_user_connections 2：#允许同时连接数量

• 收回权限

  revoke select on *.* from root@'192.168.175.%';
  # 收回select权限
  show grants for root@'192.168.175.%';
  # 查看权限
  

DML数据操作语言

• 操作表中的数据
• 插入数据

insert into student 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 student set sgender='f';
  # 不规范
  update student set sgender='f' where sid=1;
  # 规范update修改
  update student set sgender='f' where 1=1;
  # 如果非要全表修改
  update mysql.user set password=PASSWORD('123456') where user='root' and
  host='localhost';
  # 修改密码，需要刷新权限flush privileges
  

• 删除数据

  delete from student;
  # 不规范
  delete from student where sid=3;
  # 规范删除（危险）
  truncate table student;
  # DDL清空表中的内容
  

• 使用伪删除

  • 有时候一些重要数据不能直接删除，只能伪删除，因为以后还得使用呢
  • 使用update代替delete，将状态改成删除状态，在查询的时候就可以不显示被标记删除的数据

alter table student add status enum(1,0) default 1;
# 额外添加一个状态列
update student set status='0' where sid=1;
# 使用update
select * from student where status=1;

DQL数据查询语言

• select：基础用法
• 演示用的SQL文件下载：https://download.s21i.faiusr.com/23126342/0/0/ABUIABAAGAAgzcXwhQYozuPv2AE?f=world.sql&v=1622942413

mysql -uroot -p123456 < world.sql
# 常用用法
select countrycode,district from city;
# 常用用法
select countrycode from city;
# 查询单列
select countrycode,district from city limit 2;
select id,countrycode,district from city limit 2,2;
# 行级查询
select name,population from city where countrycode='CHN';
# 条件查询
select name,population from city where countrycode='CHN' and
district='heilongjiang';
# 多条件查询
select name,population,countrycode from city where countrycode like '%H%' limit
10;
# 模糊查询
select id,name,population,countrycode from city order by countrycode limit 10;
# 排序查询（顺序）
select id,name,population,countrycode from city order by countrycode desc limit
10;
# 排序查询（倒序）
select * from city where population>=1410000;
# 范围查询(>,<,>=,<=,<>)
select * from city where countrycode='CHN' or countrycode='USA';
# 范围查询OR语句
select * from city where countrycode in ('CHN','USA');
# 范围查询IN语句
select country.name,city.name,city.population,country.code from city,country
where city.countrycode=country.code and city.population < 100;

字符集定义

• 什么是字符集（Charset）

• 字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标
  点符号、图形符号、数字等。

• MySQL数据库的字符集

  • 字符集（CHARACTER）
  • 校对规则（COLLATION）

• MySQL中常见的字符集

  • UTF8
  • LATIN1
  • GBK

• 常见校对规则

  • ci：大小写不敏感
  • cs或bin：大小写敏感

• 我们可以使用以下命令查看

  show charset;
  show collation;show charset;
  show collation;
  

字符集设置

• 操作系统级别

source /etc/sysconfig/i18n
echo $LANG

• Mysql实例级别

cmake .
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
# 在编译的时候指定
[mysqld]
character-set-server=utf8
# 在配置文件中指定
mysql> create database db01 charset utf8 default collate = utf8_general_ci;
# 建库的时候
mysql> CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
# 建表的时候
mysql> alter database db01 CHARACTER SET utf8 collate utf8_general_ci;
mysql> alter table t1 CHARACTER SET utf8;
# 修改字符集

select的高级用法

• 多表连接查询（连表查询）

create table t1(id int primary key auto_increment,name varchar(20))
ENGINE=InnoDB CHARSET=utf8;

create table t2(id int primary key auto_increment,score int)
ENGINE=InnoDB CHARSET=utf8;
insert into t1(name) values('cs'),('tj'),('lz');
insert into t2(score) values(30),(80),(82);

select * from t1;
select * from t2;

• 传统连接(只能内连接，只能取交集)

select t1.name,t2.score from t1,t2 where t1.id=t2.id and t2.score > 60; 

# 查出及格
#世界上小于100人的人口城市是哪个国家的？
select city.name,city.countrycode,country.name
from city,country
where city.countrycode=country.code
and city.population<100;
# 世界上小于100人的人口城市是哪个国家，说的什么语言？
国家人口数量     城市名     国家名      语言
country.population,  city.name,   country.name,  
countrylanguage.Language

select country.population,city.name,country.name,countrylanguage.Language
from city,country,countrylanguage
where city.countrycode=country.code
and countrylanguage.countrycode=country.code
and country.population<100;

• NATURAL JOIN （自连接的表要有共同的列名字）

SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
FROM city NATURAL  JOIN countrylanguage
WHERE population > 1000000
ORDER BY population;

• 企业中多表连接查询（内连接）

select city.name,city.countrycode,country.name
from city join country on city.countrycode=country.code
where city.population<100;

建议：使用join语句时，小表在前，大表在后。

• 外连接

select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100;

• UNION（合并查询）

mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询OR语句
mysql> select * from city where countrycode in ('CHN','USA');
#范围查询IN语句
替换为：
mysql> select * from city where countrycode='CHN'
union  all
select * from city where countrycode='USA' limit 10;

• union：去重复合并
• union all ：不去重复
• 使用情况：union<union all

MySQL数据类型

数据类型介绍

• 四种主要类别

  • 数值
  • 字符
  • 二进制
  • 时间

• 数据类型的ABC要素

  • Appropriate(适当)
  • Brief(简洁)
  • Complete(完整)

• 数值数据类型

• 使用数值数据类型时的注意事项

  • 数据类型所标识的值的范围
  • 列值所需的空间量
  • 列精度和范围(浮点数和定点数)

• 数值数据类型的类

  • 整数：整数
  • 浮点数：小数
  • 定点数：精确值数值
  • BIT：位字段值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vxpMa0L1-1662431008896)(E:\资料\我整理的笔记\image\image-20220322101905341.png)]

• 文本：真实的非结构化字符串数据类型

• 整数：结构化字符串类型

• 二进制字符串数据类型

  • 字节序列
    • 二进制位按八位分组
    • 存储二进制值
    • 编译的计算机程序和应用程序
    • 图像和声音文件
  • 字符二进制数据类型的类
    • 二进制：固定长度和可变长度的二进制字符串
    • BLOB：二进制数据的可变长度非结构化集合

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ivqv1drz-1662431008897)(E:\资料\我整理的笔记\image\image-20220322102042595.png)]

• 时间数据类型

列属性介绍

• 列属性的类别
  • 数值：适用于数值数据类型（BIT 除外）
  • 字符串：适用于非二进制字符串数据类型
  • 常规：适用于所有数据类型
    

索引介绍

• 索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
• 让获取的数据更有目的性，从而提高数据库检索数据的性能。

索引类型介绍

• BTREE:B+树索引
• HASH：HASH索引
• FULLTEXT：全文索引
• RTREE：R树索引
  • B+树

• B*树

索引管理

• 索引建立在表的列上(字段)的。
• 在where后面的列建立索引才会加快查询速度。
• pages<—索引（属性）<----查数据。
  • 索引分类：
  • 主键索引
  • 普通索引*****
  • 唯一索引
  • 添加索引：
• 添加索引

alter table student add index index_name(name);
#创建索引
create index index_name on student(name);
#创建索引
desc student;
#查看索引
show index from student;
#查看索引
alter table student drop key index_name;
#删除索引
alter table student add unique key uni_xxx(xxx);
#添加主键索引（略）
#添加唯一性索引
select count(*) from city;
#查看表中数据行数
select count(distinct(name)) from city;
#查看去重数据行数

前缀索引和联合索引

前缀索引

• 根据字段的前N个字符建立索引

alter table test add index idx_name(name(10)); 

联合索引

• 多个字段建立一个索引
• 原则：把最常用来做为条件查询的列放在最前面

create table people(id int,name varchar(20),age tinyint,money int ,gender
enum('m','f'));
#创建people表
alter table people add index idx_gam(gender,age,money);
#创建联合索引

explain详解(同desc)

• explain命令使用方法

mysql> explain select name,countrycode from city where id=1; 

• MySQL查询数据的方式

  • 全表扫描（在explain语句结果中type为ALL）

    • 业务确实要获取所有数据

    • 不走索引导致的全表扫描

      • 没索引

      • 索引创建有问题

      • 语句有问题

    • 生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

  • 索引扫描

    • 常见的索引扫描类型
      • index
      • range
      • ref
      • eq_ref
      • const
      • system
      • null
    • 从上到下，性能从最差到最好，我们认为至少要达到range级别

index

• Full Index Scan，index与ALL区别为index类型只遍历索引树

range

• 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有
  between或者where子句里带有<,>查询。

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

ref

• 用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select *
from city where countrycode='USA';

eq_ref

• 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就
  是多表连接中使用primary key或者 unique key作为关联条件A

join B
on A.sid=B.sid

const、system

• 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
• 如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000; 

NULL

mysql> explain select * from city where id=1000000000000000000000000000; 

Extra（扩展）

• Using temporary 临时表
• Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
• Using join buffer
• 如果出现Using filesort（文件排序）请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population; 

• 当order by语句中出现Using filesort（文件排序），那就尽量让排序值在where条件中出现

mysql> explain select * from city where population>30000000 order by
population;
mysql> select * from city where population=2870300 order by population;

• key_len: 越小越好
• 前缀索引去控制，rows: 越小越好

建立索引的原则（规范）

• 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
• 选择唯一性索引
  • 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如:
学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
# 注意：如果重复值较多，可以考虑采用联合索引

• 为经常需要排序、分组和联合操作的字段建立索引

  • 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
  • 如果为其建立索引，可以有效地避免排序操作

• 为常作为查询条件的字段建立索引

  • 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
  • 因此，为这样的字段建立索引，可以提高整个表的查询速度。
  • 如果经常作为条件的列，重复值特别多，可以建立联合索引

• 尽量使用前缀来索引

  • 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检
    索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

• 限制索引的数目

  • 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  • 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

• 删除不再使用或者很少使用的索引

  • 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管
    理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

重点关注：

• 没有查询条件，或者查询条件没有建立索引

select * from table;
select  * from tab where 1=1;
# 全表扫描

• 在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
  • 对用户查看是非常痛苦的。
  • 对服务器来讲毁灭性的。
  • SQL改写成以下语句

# 情况1
select * from table;
#全表扫描
select  * from tab  order by price  limit 10;
#需要在price列上建立索引
# 情况2
select * from table where name='zhangsan';
#name列没有索引
1、换成有索引的列作为查询条件
2、将name列建立索引

• 查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from city where population>3000 order by population;
# 如果业务允许，可以使用limit控制
# 结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到
redis里面

• 索引本身失效，统计数据不真实
  • 索引有自我维护的能力。
  • 对于表内容变化比较频繁的情况下，有可能会出现索引失效。
  • 重建索引就可以解决
• 查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)

错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

• 隐式转换导致索引失效.这一点应当引起重视,也是开发中经常会犯的错误

mysql> create table test (id int ,name varchar(20),telnum varchar(10));
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),
(4,'z4',112);
mysql> explain select * from test where telnum=120;
mysql> alter table test add index idx_tel(telnum);
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum='120';

• explain select * from test where telnum=120;

mysql> select * from test where telnum <> '1555555';
mysql> explain select * from test where telnum <> '1555555';

• 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
• or或in尽量改成union

EXPLAIN  SELECT * FROM test WHERE telnum IN ('110','119');
#改写成
EXPLAIN SELECT * FROM test WHERE telnum='110'
UNION ALL
SELECT * FROM test WHERE telnum='119';

• like “%_” 百分号在最前面不走索引

#走range索引扫描
EXPLAIN SELECT * FROM test WHERE telnum LIKE '31%';
#不走索引
EXPLAIN SELECT * FROM test WHERE telnum LIKE '%110';

• %linux%类的搜索需求，可以使用Elasticsearch -------> ELK
• 单独引用联合索引里非第一位置的索引列

REATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1;
SHOW INDEX FROM t1;
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND
sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m';
#不走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';

MySQL的存储引擎

存储引擎简介

• 文件系统：
  • 操作系统组织和存取数据的一种机制。
  • 文件系统是一种软件。
    文
• 件系统类型：ext2 3 4 ，xfs 数据
  • 不管使用什么文件系统，数据内容不会变化
  • 不同的是，存储空间、大小、速度。
    MySQL引擎：
    可以理解为，MySQL的“文件系统”，只不过功能更加强大。
• MySQL引擎功能：
  • 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能
  • 总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。

MySQL自带的存储引擎类型

• MySQL 提供以下存储引擎:

InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

• 还可以使用第三方存储引擎:

MySQL当中插件式的存储引擎类型
MySQL的两个分支
perconaDB
mariaDB

mysql> show engines;
#查看当前MySQL支持的存储引擎类型
mysql> select table_schema,table_name,engine from information_schema.tables
where engine='innodb';
#查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables
where engine='myisam';
#查看myisam的表有哪些

• innodb和myisam的区别

#进入mysql目录
[root@localhost~l]# cd /application/mysql/data/mysql
#查看所有user的文件
[root@localhost mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 Mar  6  2017 user.frm
-rw-rw---- 1 mysql mysql  960 Aug 14 01:15 user.MYD
-rw-rw---- 1 mysql mysql  2048 Aug 14 01:15 user.MYI
#进入word目录
[root@localhost world]# cd /application/mysql/data/world/
#查看所有city的文件
[root@localhost world]# ll city.*
-rw-rw---- 1 mysql mysql  8710 Aug 14 16:23 city.frm
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd

innodb存储引擎的简介

• 在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
• 优点:
  • 事务安全（遵从 ACID）
  • MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
  • InnoDB 行级别锁定
  • Oracle 样式一致非锁定读取
  • 表数据进行整理来优化基于主键的查询
  • 支持外键引用完整性约束
  • 大型数据卷上的最大性能
  • 将对表的查询与不同存储引擎混合
  • 出现故障后快速自动恢复
  • 用于在内存中缓存数据和索引的缓冲区池
• innodb核心特性
  • MVCC
  • 事务
  • 行级锁
  • 热备份
  • Crash Safe Recovery（自动故障恢复）
• 查看存储引擎
  • 使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;
# 查询默认存储引擎

• 使用 SHOW 确认每个表的存储引擎

SHOW CREATE TABLE city\G
SHOW TABLE STATUS LIKE 'CountryLanguage'\G
# 查看表的存储引擎

• 使用 INFORMATION_SCHEMA 确认每个表的存储引擎

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'city' AND TABLE_SCHEMA = 'world'\G
# 查看表的存储引擎

• 存储引擎的设置
  • 在启动配置文件中设置服务器存储引擎

[mysqld]
default-storage-engine=<Storage Engine>
# 在配置文件的[mysqld]标签下添加

• 使用 SET 命令为当前客户机会话设置

SET @@storage_engine=<Storage Engine>
# 在MySQL命令行中临时设置

• 在 CREATE TABLE 语句指定

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
# 建表的时候指定存储引擎

【实战】存储引擎切换

• 项目背景：
  • 公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。
• 小问题不断：
  • 表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
  • 不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。
• 解决方案：
  • 提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
  • 如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
  • 5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
    *
• 实施过程和注意要素
  • 备份生产库数据（mysqldump）

[root@db01 ~]# mysqldump -uroot -p123456 -A --triggers -R --master-data=2 >/tmp/full.sql

• 准备一个5.6.38版本的新数据库
  • 对备份数据进行处理（将engine字段替换）

[root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/full.sql 

• 将修改后的备份恢复到新库
• 应用测试环境连接新库，测试所有功能
• 停应用，将备份之后的生产库发生的新变化，补偿到新库
• 应用割接到新数据库

表空间介绍

• 5.5版本以后出现共享表空间概念
• 表空间的管理模式的出现是为了数据库的存储更容易扩展
• 5.6版本中默认的是独立表空间
  • 共享表空间

[root@localhost ~]# ll /application/mysql/data/
-rw-rw----. 1 mysql mysql 12582912 6月  8 09:43 ibdata1
# 物理查看
mysql> show variables like '%path%';
innodb_data_file_path = ibdata1:12M:autoextend

• 5.6版本中默认存储
  • 系统数据
  • undo
  • 临时表
  • 5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置
• 共享表空间扩展配置方法

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
Innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

• 独立表空间
  • 对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理

[root@localhost ~]# ll /application/mysql/data/world/
-rw-rw----. 1 mysql mysql 589824 6月  6 10:23 city.ibd
# 物理查看
mysql> show variables like '%per_table%';
innodb_file_per_table = ON

【实战】数据库服务损坏

• 在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。

1. 拷贝库目录到新库中

   [root@db01 ~]# cp -r /application/mysql/data/world/ /data/3307/data/ 
   

2.启动新数据库

[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &    //启用一个实例

3.登陆数据库查看

mysql> show databases; 

4… 查询表中数据

mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

找到以前的表结构在新库中创建表

CREATE TABLE `city_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT '',
 `CountryCode` char(3) NOT NULL DEFAULT '',
 `District` char(20) NOT NULL DEFAULT '',
 `Population` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `CountryCode` (`CountryCode`),
 KEY `idx_city` (`Population`,`CountryCode`)
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country`(`Code`)             //去掉外键 

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

删除表空间文件

mysql> alter table city_new discard tablespace; 

拷贝旧表空间文件

[root@db01 world]# cp /data/3307/data/world/city.ibd
/data/3307/data/world/city_new.ibd

授权

[root@db01 world]# chown -R mysql.mysql * 

1. 导入表空间

mysql> alter table city_new import tablespace; 

事务

• 事务的定义

  • 主要针对DML语句（update，delete，insert）一组数据操作执行步骤，这些步骤被视为一个工作单元
    • 用于对多个语句进行分组
    • 可以在多个客户机并发访问同一个表中的数据时使用
  • 所有步骤都成功或都失败
    • 如果所有步骤正常，则执行
    • 如果步骤出现错误或不完整，则取消交易的概念

• 事务ACID特性

  • Atomic（原子性）
    • 所有语句作为一个单元全部成功执行或全部取消。
  • Consistent（一致性）
    • 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
  • Isolated（隔离性）
    • 事务之间不相互影响。
  • Durable（持久性）
    • 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

• 一个成功事务的生命周期

begin;
sql1
sql2
sql3
...
commit;

• 一个失败事务的生命周期

begin;
sql1
sql2
sql3
...
rollback;

• 自动提交

mysql> show variables like 'autocommit';
#查看自动提交
mysql> set autocommit=0;
#临时关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0
#永久关闭

• 事务隐式提交情况
  • 现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。
  • 有些情况下事务会被隐式提交
    • 在事务运行期间，手工执行begin的时候会自动提交上个事务
    • 在事务运行期间，加入DDL、DCL操作会自动提交上个事务
    • 在事务运行期间，执行锁定语句（lock tables、unlock tables）
    • load data infile
    • select for update
    • 在autocommit=1的时候

事务日志redo

oot@db01 ~]# cp -r /application/mysql/data/world/ /data/3307/data/

2.启动新数据库

```shell
[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &    //启用一个实例

3.登陆数据库查看

mysql> show databases; 

4… 查询表中数据

mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

找到以前的表结构在新库中创建表

CREATE TABLE `city_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT '',
 `CountryCode` char(3) NOT NULL DEFAULT '',
 `District` char(20) NOT NULL DEFAULT '',
 `Population` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `CountryCode` (`CountryCode`),
 KEY `idx_city` (`Population`,`CountryCode`)
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country`(`Code`)             //去掉外键 

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

删除表空间文件

mysql> alter table city_new discard tablespace; 

拷贝旧表空间文件

[root@db01 world]# cp /data/3307/data/world/city.ibd
/data/3307/data/world/city_new.ibd

授权

[root@db01 world]# chown -R mysql.mysql * 

1. 导入表空间

mysql> alter table city_new import tablespace; 

事务

• 事务的定义

  • 主要针对DML语句（update，delete，insert）一组数据操作执行步骤，这些步骤被视为一个工作单元
    • 用于对多个语句进行分组
    • 可以在多个客户机并发访问同一个表中的数据时使用
  • 所有步骤都成功或都失败
    • 如果所有步骤正常，则执行
    • 如果步骤出现错误或不完整，则取消交易的概念

• 事务ACID特性

  • Atomic（原子性）
    • 所有语句作为一个单元全部成功执行或全部取消。
  • Consistent（一致性）
    • 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
  • Isolated（隔离性）
    • 事务之间不相互影响。
  • Durable（持久性）
    • 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

[外链图片转存中…(img-qunf6iOO-1662431008904)]

• 一个成功事务的生命周期

begin;
sql1
sql2
sql3
...
commit;

• 一个失败事务的生命周期

begin;
sql1
sql2
sql3
...
rollback;

• 自动提交

mysql> show variables like 'autocommit';
#查看自动提交
mysql> set autocommit=0;
#临时关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0
#永久关闭

• 事务隐式提交情况
  • 现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。
  • 有些情况下事务会被隐式提交
    • 在事务运行期间，手工执行begin的时候会自动提交上个事务
    • 在事务运行期间，加入DDL、DCL操作会自动提交上个事务
    • 在事务运行期间，执行锁定语句（lock tables、unlock tables）
    • load data infile
    • select for update
    • 在autocommit=1的时候

事务日志redo