MySQL的安装!
1. 下载安装包!
[root@lzz ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
--2021-05-05 23:42:16-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-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.33-linux-glibc2.12-x86_64.tar.gz [跟随至新的 URL]
--2021-05-05 23:42:17-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 184.26.84.248
正在连接 cdn.mysql.com (cdn.mysql.com)|184.26.84.248|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:661718255 (631M) [application/x-tar-gz]
正在保存至: “mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz”
mysql-5.7.33-linux-gli 100%[==========================>] 631.06M 2.99MB/s 用时 3m 34s
2021-05-05 23:45:52 (2.95 MB/s) - 已保存 “mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz” [661718255/661718255])
2. 用户相关!
[root@lzz ~]# useradd -r -M -s /sbin/nologin mysql (创建用户!)
[root@lzz ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local
(指定解压目录!)
[root@lzz ~]# cd /usr/local
[root@lzz local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.33-linux-glibc2.12-x86_64 share
[root@lzz local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql(将目录名字修改为 简易模式)
[root@lzz local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@lzz local]# chown -R mysql.mysql /usr/local/mysql (修改对应权限!)
[root@lzz local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 5月 19 2020 bin
drwxr-xr-x. 2 root root 6 5月 19 2020 etc
drwxr-xr-x. 2 root root 6 5月 19 2020 games
drwxr-xr-x. 2 root root 6 5月 19 2020 include
drwxr-xr-x. 2 root root 6 5月 19 2020 lib
drwxr-xr-x. 3 root root 17 4月 29 10:14 lib64
drwxr-xr-x. 2 root root 6 5月 19 2020 libexec
drwxr-xr-x. 9 mysql mysql 129 5月 6 00:26 mysql
drwxr-xr-x. 2 root root 6 5月 19 2020 sbin
drwxr-xr-x. 5 root root 49 4月 29 10:14 share
drwxr-xr-x. 2 root root 6 5月 19 2020 src
[root@lzz local]# vim /etc/profile.d/mysql.sh (设置环境变量!)
[root@lzz local]# . /etc/profile.d/mysql.sh
[root@lzz local]# which mysql
/usr/local/mysql/bin/mysql
[root@lzz local]# cat /etc/profile.d/mysql.sh
PATH=/usr/local/mysql/bin:$PATH
[root@lzz local]# mkdir /opt/date (创建数据存放的目录!)
[root@lzz local]# chown -R mysql.mysql /opt/date
[root@lzz local]# ll /opt
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月 6 00:37 date
3. 初始化获取并且保存密码!
[root@lzz local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2021-05-05T20:58:43.600829Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-05T20:58:43.839479Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-05T20:58:43.877654Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-05T20:58:43.935239Z 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: ad7737af-ade4-11eb-9e4c-000c2973034c.
2021-05-05T20:58:43.936374Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-05T20:58:44.696457Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-05T20:58:44.822501Z 1 [Note] A temporary password is generated for root@localhost: TwZp_gu0n=_;
[root@lzz ~]# vim password
[root@lzz ~]# cat password
TwZp_gu0n=_;
4. 输入配置文件!
[root@lzz ~]# vim /etc/my.cnf
[root@lzz ~]# 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
5. 配置启动脚本和开机自启!
[root@lzz ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@lzz ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@lzz ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@lzz ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/lzz.err'.
SUCCESS!
[root@lzz ~]# chkconfig mysqld on
[root@lzz ~]# chkconfig --list
注:该输出结果只显示 SysV 服务,并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。
要列出 systemd 服务,请执行 'systemctl list-unit-files'。
查看在具体 target 启用的服务请执行
'systemctl list-dependencies [target]'。
mysqld 0:关 1:关 2:开 3:开 4:开 5:开 6:关
[root@lzz ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
6. 头库文件配置!
[root@lzz ~]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@lzz ~]# vim /etc/ld.so.conf.d/mysql.conf
[root@lzz ~]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@lzz ~]# ldconfig
7. 启动MySQL 并且配置新密码!
[root@lzz ~]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@lzz ~]# dnf provides libncurses.so.5
上次元数据过期检查:0:26:11 前,执行于 2021年05月05日 星期三 16时48分52秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 :baseos
匹配来源:
提供 : libncurses.so.5
[root@lzz ~]# dnf -y install ncurses-compat-libs
上次元数据过期检查:0:27:13 前,执行于 2021年05月05日 星期三 16时48分52秒。
依赖关系解决。
==========================================================================================
软件包 架构 版本 仓库 大小
==========================================================================================
安装:
ncurses-compat-libs x86_64 6.1-7.20180224.el8 baseos 331 k
事务概要
==========================================================================================
安装 1 软件包
总下载:331 k
安装大小:1.2 M
下载软件包:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm 108 kB/s | 331 kB 00:03
------------------------------------------------------------------------------------------
总计 93 kB/s | 331 kB 00:03
警告:/var/cache/dnf/baseos-055ffcb2ec25a27f/packages/ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID 8483c65d: NOKEY
CentOS Stream 8 - BaseOS 1.6 MB/s | 1.6 kB 00:00
导入 GPG 公钥 0x8483C65D:
Userid: "CentOS (CentOS Official Signing Key) <security@centos.org>"
指纹: 99DB 70FA E1D7 CE22 7FB6 4882 05B5 55B3 8483 C65D
来自: /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
导入公钥成功
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
运行脚本: ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
验证 : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
Installed products updated.
已安装:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
完毕!
[root@lzz ~]# cat password
TwZp_gu0n=_;
[root@lzz ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33
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('123liuZEzheng!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
8. 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将无法正常处理连接请求 |
9. mysql数据库备份与恢复
9.1 数据库常用备份方案
数据库备份方案:
- 全量备份
- 增量备份
- 差异备份
备份方案 | 特点 |
---|---|
全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
增量备份 | 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对象 是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件,如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
9.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
mysql> show databases; (事先数据库里面写入一些数据库 和 表!)
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
7 rows in set (0.00 sec)
mysql> use lzz;
Database changed
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
(备份所有数据!)
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! --all-databases > all_20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql
(备份 数据库lzz!)
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! --databases lzz > lzz_20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql lzz_20210506.sql
(备份 数据库 lzz 中的表 lp!!)
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! lzz lp > lp_20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql lp_20210506.sql lzz_20210506.sql
9.3 数据库的恢复!
mysql> drop database lzz; (删除数据库!)
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
6 rows in set (0.00 sec)
(恢复数据库!!!)
[root@lzz ~]# mysql -uroot -p123liuZEzheng! < lzz_20210506.sql mysql
[Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
Database changed
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table lp; (删除数据库中的表!)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
+---------------+
1 row in set (0.00 sec)
mysql> source lp_20210506.sql; (回复数据库中的表!!)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
mysql> drop database gj; (删除多个数据库!!!!)
Query OK, 0 rows affected (0.00 sec)
mysql> drop database lzz;
Query OK, 2 rows affected (0.00 sec)
mysql> drop database zzh;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
[root@lzz ~]# mysql -uroot -p123liuZEzheng! < all_20210506.sql (恢复整个数据库!)
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
10. mysql差异备份!
10.1 开启MySQL二进制日志功能!
[root@lzz ~]# vim /etc/my.cnf
[root@lzz ~]# 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
server-id=1 (设置服务器标识)
log-bin=mysql_bin (开启二进制功能)
重启服务!!!!!
[root@lzz ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
10.2 查看数据库及其表内现有的内容!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
7 rows in set (0.00 sec)
mysql> use lzz;
Database changed
mysql> show tables;
+---------------+
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
7 rows in set (0.00 sec)
mysql> use lzz;
Database changed
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
mysql> desc lp;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from lp;
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | xieer | 20 |
| 2 | chengling | 15 |
| 3 | wink | 29 |
| 4 | zzh | 30 |
| 5 | ljj | 31 |
+------+-----------+------+
5 rows in set (0.00 sec)
10.3 对数据进行全部备份!
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210507.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql lp_20210506.sql
all-20210507.sql lzz_20210506.sql
[root@lzz ~]# ll /opt/data
总用量 122980
-rw-r-----. 1 mysql mysql 56 5月 5 16:58 auto.cnf
-rw-------. 1 mysql mysql 1680 5月 5 16:58 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 5 16:58 ca.pem
-rw-r--r--. 1 mysql mysql 1112 5月 5 16:58 client-cert.pem
-rw-------. 1 mysql mysql 1676 5月 5 16:58 client-key.pem
drwxr-x---. 2 mysql mysql 20 5月 5 18:25 gj
-rw-r-----. 1 mysql mysql 413 5月 7 02:16 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 7 02:28 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 7 02:28 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 5 16:58 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 7 02:35 ibtmp1
drwxr-x---. 2 mysql mysql 76 5月 7 02:21 lzz
-rw-r-----. 1 mysql mysql 19185 5月 7 02:16 lzz.err
drwxr-x---. 2 mysql mysql 4096 5月 7 02:21 mysql
-rw-r-----. 1 mysql mysql 154 5月 7 02:35 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 5月 7 02:35 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 5月 7 02:16 mysql.pid
drwxr-x---. 2 mysql mysql 8192 5月 5 16:58 performance_schema
-rw-------. 1 mysql mysql 1676 5月 5 16:58 private_key.pem
-rw-r--r--. 1 mysql mysql 452 5月 5 16:58 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 5 16:58 server-cert.pem
-rw-------. 1 mysql mysql 1680 5月 5 16:58 server-key.pem
drwxr-x---. 2 mysql mysql 8192 5月 5 16:58 sys
drwxr-x---. 2 mysql mysql 20 5月 5 18:25 zzh
10.4 插入新的数据用于实验!!
mysql> select * from lp
-> ;
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | xieer | 20 |
| 2 | chengling | 15 |
| 3 | wink | 29 |
| 4 | zzh | 30 |
| 5 | ljj | 31 |
| 6 | zhouxu | 25 |
| 7 | zky | 24 |
| 8 | fuwenduo | 24 |
| 9 | yangyuan | 23 |
| 10 | tangmo | 20 |
+------+-----------+------+
10 rows in set (0.00 sec)
mysql> drop database lzz; (删除数据库!)
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
6 rows in set (0.00 sec)
10.5 刷新二进制日志文件!
[root@lzz ~]# mysqladmin -uroot -p123liuZEzheng! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ll /opt/data/
总用量 122984
-rw-r-----. 1 mysql mysql 56 5月 5 16:58 auto.cnf
-rw-------. 1 mysql mysql 1680 5月 5 16:58 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 5 16:58 ca.pem
-rw-r--r--. 1 mysql mysql 1112 5月 5 16:58 client-cert.pem
-rw-------. 1 mysql mysql 1676 5月 5 16:58 client-key.pem
drwxr-x---. 2 mysql mysql 20 5月 5 18:25 gj
-rw-r-----. 1 mysql mysql 413 5月 7 02:16 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 7 02:50 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 7 02:50 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 5 16:58 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 7 02:35 ibtmp1
-rw-r-----. 1 mysql mysql 19185 5月 7 02:16 lzz.err
drwxr-x---. 2 mysql mysql 4096 5月 7 02:21 mysql
-rw-r-----. 1 mysql mysql 671 5月 7 02:52 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 5月 7 02:52 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 5月 7 02:52 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 5月 7 02:16 mysql.pid
drwxr-x---. 2 mysql mysql 8192 5月 5 16:58 performance_schema
-rw-------. 1 mysql mysql 1676 5月 5 16:58 private_key.pem
-rw-r--r--. 1 mysql mysql 452 5月 5 16:58 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 5 16:58 server-cert.pem
-rw-------. 1 mysql mysql 1680 5月 5 16:58 server-key.pem
drwxr-x---. 2 mysql mysql 8192 5月 5 16:58 sys
drwxr-x---. 2 mysql mysql 20 5月 5 18:25 zzh
[root@lzz ~]# cat /opt/data/mysql_bin.index
./mysql_bin.000002
./mysql_bin.000003
10.6 当我们全部回复备份时,我们是看不到 后面新增加的数据的!
[root@lzz ~]# mysql -uroot -p123liuZEzheng! < all-20210507.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | xieer | 20 |
| 2 | chengling | 15 |
| 3 | wink | 29 |
| 4 | zzh | 30 |
| 5 | ljj | 31 |
+------+-----------+------+
10.7 恢复差异备份!
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 | 1 | 123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 290 | BEGIN |
| mysql_bin.000002 | 290 | Table_map | 1 | 338 | table_id: 174 (lzz.lp) |
| mysql_bin.000002 | 338 | Write_rows | 1 | 439 | table_id: 174 flags: STMT_END_F |
| mysql_bin.000002 | 439 | Xid | 1 | 470 | COMMIT /* xid=868 */ |
| mysql_bin.000002 | 470 | Anonymous_Gtid | 1 | 535 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 535 | Query | 1 | 624 | drop database lzz |
| mysql_bin.000002 | 624 | Rotate | 1 | 671 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)
[root@lzz ~]# mysqlbinlog --stop-position=535 /opt/data/mysql_bin.000002 |mysql -uroot -p123liuZEzheng!
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | xieer | 20 |
| 2 | chengling | 15 |
| 3 | wink | 29 |
| 4 | zzh | 30 |
| 5 | ljj | 31 |
| 6 | zhouxu | 25 |
| 7 | zky | 24 |
| 8 | fuwenduo | 24 |
| 9 | yangyuan | 23 |
| 10 | tangmo | 20 |
+------+-----------+------+ (发现之前添加的数据也恢复了!)
11. 密码破解!
11.1 首先修改配置文件,跳过授权登录,使得不需要密码也可也登录,随后重启服务!
[root@lzz ~]# vim /etc/my.cnf
[root@lzz ~]# 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
server-id=1
log-bin=mysql_bin
skip-name-resolve
skip-grant-tables
[root@lzz ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
11.2 进入 MySQL(此时不需要密码) 修改密码!
[root@lzz ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33-log 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> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user \G; (一共有四个这个模式的表,我只截上来一段!)
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *5ED011771EEEEABDDE479299B1842EADB8EEDF2B (此时这里有乱码,是因为我们原本就有密码,我们修改密码之后,这里就会发生改变!)
password_expired: N
password_last_changed: 2021-05-05 17:18:25
password_lifetime: NULL
account_locked: N
mysql> update user set authentication_string = password('liuZEzheng123!') where User = 'root' and Host = 'localhost'; (修改密码!)
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from user \G;
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *052514F2E5B35A1EBDDDA3D7BCBCD6684BE60BE6 (此时这里已经发生了改变!)
password_expired: N
password_last_changed: 2021-05-05 17:18:25
password_lifetime: NULL
account_locked: N
11.3 删掉配置文件里面的跳过授权,然后用新密码登录!
[root@lzz ~]# vim /etc/my.cnf (修改配置文件!)
[root@lzz ~]# 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
server-id=1
log-bin=mysql_bin
[root@lzz ~]# service mysqld restart (重启服务!)
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@lzz ~]# mysql -uroot -pliuZEzheng123! (用新的密码登录!)
mysql: [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 2
Server version: 5.7.33-log 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>