03 MySQL进阶

1. 二进制格式mysql安装

//下载二进制格式的mysql软件包
[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
--2021-11-04 00:00:48--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
正在解析主机 downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
正在连接 downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz [跟随至新的 URL]
--2021-11-04 00:00:49--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 23.200.204.228
正在连接 cdn.mysql.com (cdn.mysql.com)|23.200.204.228|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：665389778 (635M) [application/x-tar-gz]
正在保存至: “mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz”

mysql-5.7.34-linux-glibc2.1 100%[========================================>] 634.56M  7.09MB/s  用时 1m 47s  

2021-11-04 00:02:38 (5.92 MB/s) - 已保存 “mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz” [665389778/665389778])

[root@localhost src]# 

//创建用户和组
[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
[root@localhost src]# 

//解压软件至/usr/local/
[root@localhost src]# ls
debug  kernels  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.34-linux-glibc2.12-x86_64  sbin  share  src
[root@localhost src]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.34-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.34-linux-glibc2.12-x86_64/'
[root@localhost local]# 

[root@localhost 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 11月  4 00:11 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x  9 root root 129 11月  4 00:10 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 8月  12 2018 sbin
drwxr-xr-x. 5 root root  49 9月  30 09:11 share
drwxr-xr-x. 2 root root   6 8月  12 2018 src
[root@localhost local]# 

//修改目录/usr/local/mysql的属主属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql -d
lrwxrwxrwx 1 mysql mysql 36 11月  4 00:11 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
[root@localhost ~]# 

//添加环境变量
[root@localhost ~]# ls /usr/local/mysql
/usr/local/mysql
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost ~]# 

//建立数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/
总用量 0
drwxr-xr-x 2 mysql mysql 6 11月  4 00:15 data
[root@localhost ~]# 

//初始化数据库
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2021-11-03T16:23:42.502645Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-03T16:23:43.347546Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-11-03T16:23:43.590040Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-11-03T16:23:43.663831Z 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: 69b81bc9-3cc2-11ec-81ae-000c29b91252.
2021-11-03T16:23:43.665807Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-11-03T16:23:44.348631Z 0 [Warning] CA certificate ca.pem is self signed.
2021-11-03T16:23:44.517554Z 1 [Note] A temporary password is generated for root@localhost: h2V8mIhyqQ;N
[root@localhost ~]# 

//请注意，这个命令的最后会生成一个临时密码，此处密码是h2V8mIhyqQ;N
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到

//生成配置文件
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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@localhost ~]# 

//配置服务启动脚本
[root@localhost ~]# vim /usr/local/mysql/support-files/mysql.server 
 46 basedir=/usr/local/mysql
 47 datadir=/opt/data

//启动mysql
[root@localhost ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
. SUCCESS! 
[root@localhost ~]# ps -ef|grep mysql
root        2216       1  0 00:30 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql       2404    2216 16 00:30 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root        2436    1536  0 00:30 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# ss -antl
State         Recv-Q        Send-Q               Local Address:Port                 Peer Address:Port        
LISTEN        0             128                        0.0.0.0:22                        0.0.0.0:*           
LISTEN        0             128                           [::]:22                           [::]:*           
LISTEN        0             80                               *:3306                            *:*           
[root@localhost ~]# 

//修改密码
//使用临时密码登录
[root@localhost ~]# yum -y install ncurses-compat-libs
[root@localhost ~]# mysql -uroot -p
Enter password: h2V8mIhyqQ;N
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> set password = password('1');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# 

2. mysql配置文件

mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

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

//在家目录下告诉它密码，登录则无需密码
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=1
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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常用配置文件参数：

参数	说明
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将无法正常处理连接请求

3. mysql数据库备份与恢复

3.1 数据库常用备份方案

数据库备份方案：

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

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

冷备 xtrabackup
热备 mysqldump

3.2 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

//备份整个数据库(全备)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

//创建一个以school命名的数据库
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.000 sec)

//进入school数据库
MariaDB [(none)]> use school;
Database changed

//创建一个以student命名的表
MariaDB [school]> create table student(id int primary key auto_increment not null,name varchar(50) not null,age tinyint);
Query OK, 0 rows affected (0.004 sec)

//向student表内插入内容
MariaDB [school]> insert student(name,age) values('tom',20),('jerry',15),('zhangshan',18),('lisi',20),('wangwu',23),('qianliu',17);
Query OK, 6 rows affected (0.001 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [school]> select *from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+