MySQL进阶

二进制格式mysql安装

//预先下载好需要的安装包到/usr/src/下
//网址wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@dzc ~]# cd /usr/src/
[root@dzc ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
--2021-08-26 22:39:44--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 184.50.240.231
正在连接 cdn.mysql.com (cdn.mysql.com)|184.50.240.231|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：376537503 (359M) [application/x-tar-gz]
正在保存至: “mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz”

mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz       100%[=======================================================================================================>] 359.09M   509KB/s  用时 5m 47s  

2021-08-26 22:45:35 (1.04 MB/s) - 已保存 “mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz” [376537503/376537503])

//接下来创建需要用到的用户与组
[root@dzc ~]# groupadd -r mysql
[root@dzc ~]# useradd -M -s /sbin/nologin -g mysql mysql

//解压软件至/usr/local/下
[root@dzc src]# ls
debug  kernels  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@dzc src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@dzc src]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  **mysql-5.7.31-linux-glibc2.12-x86_64**  sbin  share  src

//进入到/usr/local/下进行链接
[root@dzc local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/'
[root@dzc local]# ll
总用量 0
drwxr-xr-x. 2 root root    6 8月  12 2018 bin
drwxr-xr-x. 2 root root    6 8月  12 2018 etc
drwxr-xr-x. 2 root root    6 8月  12 2018 games
drwxr-xr-x. 2 root root    6 8月  12 2018 include
drwxr-xr-x. 2 root root    6 8月  12 2018 lib
drwxr-xr-x. 2 root root    6 8月  12 2018 lib64
drwxr-xr-x. 2 root root    6 8月  12 2018 libexec
lrwxrwxrwx. 1 root root   36 8月  26 22:54 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 7161 31415 129 6月   2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root    6 8月  12 2018 sbin
drwxr-xr-x. 5 root root   49 3月   1 14:54 share
drwxr-xr-x. 2 root root    6 8月  12 2018 src

//修改目录/usr/local/mysql的属主属组
[root@dzc local]# chown -R mysql.mysql /usr/local/mysql
[root@dzc local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 8月  26 22:54 /usr/local/mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/

//添加环境变量
//这一步非常重要，虽然二进制安装包是别人配置好的，但是环境变量需要自己设置，如果没有设置，则会导致开启不成功及其他的一些问题发生
[root@dzc local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@dzc local]# . /etc/profile.d/mysql.sh
[root@dzc local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@dzc local]# mkdir /opt/data-mysql
[root@dzc local]# chown -R mysql.mysql /opt/data-mysql/
[root@dzc local]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月  26 23:07 data-mysql

//初始化数据库
[root@dzc local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data-mysql/
2021-08-26T15:08:59.335796Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-26T15:08:59.571083Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-26T15:08:59.604564Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-26T15:08:59.659193Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8a8a8cde-067f-11ec-8fa7-000c29dd789e.
2021-08-26T15:08:59.660633Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-26T15:09:00.250905Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-26T15:09:00.509948Z 1 [Note] A temporary password is generated for root@dzc: hojw:g*lw02N
//初始化完成
注意：此命令最后会随机生成一个密码，当前密码为：hojw:g*lw02N
需要在登陆之后手动去修改一次密码

//生成配置文件
[root@ddzc local]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF

[root@dzc local]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

//配置服务启动脚本
[root@dzc local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@dzc local]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@dzc local]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

//启动MySQL服务
[root@dzc local]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/opt/data-mysql/dzc.localdomain.err'.
 SUCCESS! 
[root@dzc local]# ps -ef|grep mysql
root      199312       1  0 23:17 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data-mysql --pid-file=/opt/data-mysql/mysql.pid
mysql     199500  199312  2 23:17 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data-mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=dzc.localdomain.err --pid-file=/opt/data-mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306
root      200299   14569  0 23:18 pts/0    00:00:00 grep --color=auto mysql
//3306端口已启动

//最后在进行一次修改密码，本次二进制安装就结束了
[root@dzc ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password: #hojw:g*lw02N#		//我的密码是这个，你们的和我不一样
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

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> 

//设置新密码
mysql> set password = password('dzc123!');	//密码建议字母+数字+符号
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL配置文件

MySQL的配置文件地址为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

MySQL常用配置文件参数:

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求

MySQL数据库备份与恢复

数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。特点：数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。特点：没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行备份上一次的完全备份后发生变化的所有文件。
差异备份	差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

MySQL备份工具mysqldump

语法：

    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

常用的OPTIONS

-uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

备份整个数据库(全备)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database dzc;
Query OK, 1 row affected (0.00 sec)

mysql> use dzc;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_dzc       |
+---------------------+
| student             |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | dzc         |   20 |
|  2 | gailun      |   23 |
|  3 | zuoyi       |   25 |
|  4 | delaiesi    |   28 |
|  5 | airuiliya   |   26 |
+----+-------------+------+
11 rows in set (0.00 sec)

[root@dzc ~]# mysqldump -uroot -p1 --all-databases> all-$(date '+%Y%m%d').sql
[root@dzc ~]# ls
all-20210826.sql 
[root@dzc ~]# mysql -uroot -p1 -e 'drop database dzc;' 
[root@dzc ~]# mysql -uroot -p < all-20210826.sql
[root@dzc ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| dzc                |
+--------------------+

//备份czd库
mysqldump -uroot -p --databases yy > yy_database_backup.sql


恢复数据库czd：

[root@dzc ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| dzc                |
+--------------------+

[root@dzc ~]# ls
all-20210826.sql    czd_database_backup.sql
[root@dzc ~]# mysql -uroot -p -hlocalhost < czd_database_backup.sql
[root@dzc ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| dzc                |
| czd                |
+--------------------+

差异备份与恢复

MySQL差异备份

开启MySQL服务器的二进制日志功能

[root@dzc ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

[root@dzc ~]# service mysqld restart	//重置一遍数据库
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
注：mysql中，开启或关闭某项功能都需要重置一遍数据库

对数据库进行完全备份

[root@dzc ~]# mysqldump -uroot -pdzc123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210826.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@dzc ~]# ls
all-20210826.sql

//增加新内容
mysql> use dzc;
Database changed
mysql> insert into student values(3,'lakesi',20),(4,'zelasi',30);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | dzc         |   20 |
|  2 | gailun      |   23 |
|  3 | zuoyi       |   25 |
|  4 | delaiesi    |   28 |
|  5 | airuiliya   |   26 |
|  6 | lakesi      |   20 |
|  7 | zelasi      |   30 |
+----+-------------+------+

mysql差异备份恢复

模拟误删数据

[root@dzc ~]# mysql -uroot -p1 -e 'drop database czd;'
[root@dzc ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| dzc                |
+--------------------+

刷新创建新的二进制日志

[root@dzc ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@dzc ~]# ll /opt/data-mysql/
总用量 122992
-rw-r----- 1 mysql mysql       56 8月  26 08:15 auto.cnf
-rw------- 1 mysql mysql     1680 8月  26 08:15 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 8月  26 08:15 ca.pem
--------------------  省略 ------------------
drwxr-x--- 2 mysql mysql       58 8月  26 09:01 dzc

恢复完全备份

[root@dzc ~]# mysql -uroot -p < all-20210826.sql 
Enter password: 
[root@dzc ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| dzc                |
| czd                |
+--------------------+

恢复差异备份
查找误删数据库的位置在什么地方

mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |        10 |         154 |                                       |
--------------------  省略 ------------------
| mysql_bin.000002 | 860 | Rotate         |        10 |         907 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

使用mysqlbinlog恢复差异备份

[root@dzc ~]# mysqlbinlog --stop-position=865/opt/data/mysql_bin.000002 |mysql -uroot -pdzc123!
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@dzc ~]# mysql -uroot -pdzc123! -e "select * from student;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | dzc         |   20 |
|  2 | gailun      |   23 |
|  3 | zuoyi       |   25 |
|  4 | delaiesi    |   28 |
|  5 | airuiliya   |   26 |
|  6 | lakesi      |   20 |
|  7 | zelasi      |   30 |
+----+-------------+------+

SQL多表查询

order by：排序
group by：计数、算平均值、求和等等
inner join：查询多张表中都存在的数据
left join：显示以左边表为主的内容，右边表有匹配左边表内容时完成显示，无匹配时NULL
right by：显示以右边表为主的内容，左边表有匹配右边表内容时完成显示，无匹配时NULL

group by（聚合函数或分组查询）
group by 一般和聚合函数一起使用才有意义，比如count sum avg等，使用group by的两个要素：

出现在select后面的字段，要么是聚合函数中的，要么就是group by中的。
要筛选结果，可以先使用where再用group by或者先用group by再用having

mysql> create table test(a varchar(20),b varchar(20),c varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert test value(1,'a','wu');
Query OK, 1 row affected (0.01 sec)

mysql> insert test value(1,'a','wu');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'b','la');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'b','la');
Query OK, 1 row affected (0.01 sec)

mysql> insert test value(1,'b','la');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1    | a    | wu   |
| 1    | a    | wu   |
| 1    | b    | la   |
| 1    | b    | la   |
| 1    | b    | la   |
+------+------+------+
5 rows in set (0.00 sec)

mysql> select count(a),b from test group by b; 
+----------+------+
| count(a) | b    |
+----------+------+
|        2 | a    |
|        3 | b    |
+----------+------+
2 rows in set (0.01 sec)

利用group by去计算c组

mysql> select count(1),c from test group by c;
+----------+------+
| count(1) | c    |
+----------+------+
|        2 | wu   |
|        3 | la   |
+----------+------+
2 rows in set (0.00 sec)

求和

mysql>  select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      122 |
+----------+
1 row in set (0.00 sec)

求平均值

mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
|     24.4 |
+----------+
1 row in set (0.00 sec)

inner by（内连接）、left join（左连接）、right by（右连接）

mysql> create table test1(id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2(id int,job int,parent_id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert test1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu'); 
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

mysql> insert test2 values(1,23,1),(2,34,2),(3,34,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+------+------+-----------+
| id   | job  | parent_id |
+------+------+-----------+
|    1 |   23 |         1 |
|    2 |   34 |         2 |
|    3 |   34 |         4 |
+------+------+-----------+
3 rows in set (0.00 sec)

内连接（inner by:查询两张表相同的数据）

mysql> select test1.*,test2.* from test1 inner join test2 on test1.id = test2.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
+------+----------+------+------+-----------+
2 rows in set (0.00 sec)

左连接(left join:以左边为标准,如果右边跟左边不匹配的值则显示空null)

mysql> select test1.*,test2.* from test1 left join test2 on test1.id = test2.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
|    3 | wangwu   | NULL | NULL |      NULL |
+------+----------+------+------+-----------+
3 rows in set (0.00 sec)

右连接(right by:意义与左连接相同)

mysql> select test1.*,test2.* from test1 right join test2 on test1.id = test2.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
| NULL | NULL     |    3 |   34 |         4 |
+------+----------+------+------+-----------+
3 rows in set (0.00 sec)

目录CONTENT

MySQL进阶管理