mysql进阶
1. 二进制格式mysql安装
在网页 https://downloads.mysql.com/archives/community/ 下载mysql软件包
//下载二进制格式的mysql软件包,从官网下载mysql压缩包,再把下载好的压缩包拉进/usr/src下面
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug kernels mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
// 创建用户和组
[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
[root@localhost src]# grep mysql /etc/group
mysql:x:973:
// 解压软件至/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 local]# ls
bin include libexec share
etc lib mysql-5.7.34-linux-glibc2.12-x86_64 src
games lib64 sbin
[root@localhost local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[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 47 8月 25 16:08 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月 25 14:56 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 3月 1 22:59 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql*
[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 mysql mysql 47 8月 25 16:08 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 8月 25 14:56 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 3月 1 22:59 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
[root@localhost local]# ls mysql
bin include LICENSE README support-files
docs lib man share
//添加环境变量
[root@localhost local]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
//建立数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# mysql --initialize-insecure --user mysql --datadir /opt/data //初始化(方法一: 初始化时系统不会给数据库设置密码)
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data //初始化(方法二:初始化时系统随机给数据库设置一个密码) 注:两种方法择一即可
2021-08-25T08:15:27.525540Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-25T08:15:27.753830Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-25T08:15:27.790281Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-25T08:15:27.797047Z 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: 9b1b175b-057c-11ec-9a35-000c294867eb.
2021-08-25T08:15:27.797995Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-25T08:15:28.073418Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-25T08:15:28.329315Z 1 [Note] A temporary password is generated for root@localhost: gtUWdq#jt2hL
//请注意,这个命令的最后会生成一个临时密码,此处密码是gtUWdq#jt2hL
//再次注意,这个密码是随机的,一定要记住这个密码,因为一会登录时会用到
//生成配置文件
[root@localhost ~]# 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 //进程ID
> user = mysql
> skip-name-resolve // 跳过名称解析;名称解析:把主机名或域名解析成ip地址
> EOF
[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 ~]# cd /usr/local/mysql
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@localhost support-files]# vim mysql.server
46 basedir=/usr/local/mysql
47 datadir=/opt/data
[root@localhost ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
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 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 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]# yum whatprovides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:1:09:43 前,执行于 2021年08月25日 星期三 15时21分51秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility
: libraries
仓库 :BaseOS
匹配来源:
提供 : libncurses.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs
//修改密码
//使用临时密码登录
[root@localhost ~]# mysql -uroot -p'gtUWdq#jt2hL'
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.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('wjj'); //一定要改密码,不然退出再登就登不进来了
Query OK, 0 rows affected, 1 warning (0.00 sec)
//开机自启mysql
[root@localhost ~]# cd /usr/lib/systemd/system
[root@localhost system]# ls
[root@localhost system]# cp sshd.service mysqld.service
[root@localhost system]# vim mysqld.service
[root@localhost system]# cat mysqld.service
[Unit]
Description=mysql server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# /usr/local/mysql/support-files/mysql.server stop
[root@localhost ~]# systemctl start mysqld
2. 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将无法正常处理连接请求 |
3. mysql数据库备份与恢复
3.1 数据库常用备份方案
数据库备份方案:
- 全量备份
- 增量备份
- 差异备份
备份方案 | 特点 |
---|---|
全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
增量备份 | 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对象 是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件,如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
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 |
| wjj |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]> use wjj;
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
MariaDB [wjj]> show tables;
+---------------+
| Tables_in_wjj |
+---------------+
| student |
| wj |
+---------------+
2 rows in set (0.000 sec)
[root@localhost ~]# ls
公共 视频 文档 音乐 anaconda-ks.cfg
模板 图片 下载 桌面 initial-setup-ks.cfg
[root@localhost ~]# mysqldump -uroot -p1 --all-databases > all-$(date '+%Y%m%d').sql
[root@localhost ~]# ls
公共 视频 文档 音乐 all-20210826.sql initial-setup-ks.cfg
模板 图片 下载 桌面 anaconda-ks.cfg
//备份wjj库的student表和wj表
[root@localhost ~]# mysqldump -uroot -p1 wjj student wj > tables-$(date '+%Y%m%d').sql
[root@localhost ~]# ls
公共 文档 all-20210826.sql tables-20210826.sql
模板 下载 all-.sql
视频 音乐 anaconda-ks.cfg
图片 桌面 initial-setup-ks.cfg
//备份wjj库
[root@localhost ~]# mysqldump -uroot -p1 --databases wjj > wjj-$(date '+%Y%m%d').sql
//模拟误删wangqingge数据库
MariaDB [(none)]> drop database wjj;
Query OK, 2 rows affected (0.005 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
3.3 mysql数据恢复
//恢复wjj数据库
[root@localhost ~]# ls
公共 文档 all-20210826.sql wjj-20210826.sql
模板 下载 anaconda-ks.cfg
视频 音乐 initial-setup-ks.cfg
图片 桌面 table-20210826.sql
[root@localhost ~]# mysql -uroot -p1 < wjj-20210826.sql
[root@localhost ~]# mysql -uroot -p1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wjj |
+--------------------+
//恢复wjj数据库的student表和wj表
MariaDB [(none)]> use wjj;
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
MariaDB [wjj]> source table-20210826.sql
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
............
MariaDB [wjj]> show tables;
+---------------+
| Tables_in_wjj |
+---------------+
| student |
| wj |
+---------------+
2 rows in set (0.000 sec)
//模拟删除整个数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wjj |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> drop database wjj;
Query OK, 2 rows affected (0.003 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
//恢复整个数据库
[root@localhost ~]# mysql -uroot -p1 < all-20210826.sql
[root@localhost ~]# mysql -uroot -p1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wjj |
+--------------------+
3.4 差异备份与恢复
3.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vim /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 = 10 //设置服务器标识符
log-bin = mysql_bin //开启二进制日志功能
[root@localhost ~]# systemctl restart mysqld.service
对数据库进行完全备份
//完全备份
[root@localhost ~]# mysqldump -uroot -pwjj --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@localhost ~]# ls
公共 视频 文档 音乐 all-20210826.sql initial-setup-ks.cfg
模板 图片 下载 桌面 anaconda-ks.cfg pass
//增加新内容
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql> insert student(name,age) values('xiaozhou',18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | xiaozhou | 18 |
+----+-------------+------+
12 rows in set (0.00 sec)
//修改内容
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | xiaozhou | 18 |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql> update student set age = 17 where id = 9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 17 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | xiaozhou | 18 |
+----+-------------+------+
12 rows in set (0.00 sec)
3.4.2. mysql差异备份恢复
模拟误删数据
mysql> drop database wjj;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
总用量 122980
-rw-r-----. 1 mysql mysql 56 8月 26 14:44 auto.cnf
-rw-------. 1 mysql mysql 1680 8月 26 14:44 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 8月 26 14:44 ca.pem
-rw-r--r--. 1 mysql mysql 1112 8月 26 14:44 client-cert.pem
-rw-------. 1 mysql mysql 1676 8月 26 14:44 client-key.pem
-rw-r-----. 1 mysql mysql 301 8月 26 14:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月 26 15:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 26 15:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月 26 14:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 26 15:26 ibtmp1
-rw-r-----. 1 mysql mysql 19289 8月 26 15:26 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 8月 26 14:44 mysql
-rw-r-----. 1 mysql mysql 860 8月 26 15:35 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 8月 26 15:26 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 8月 26 14:57 mysql.pid
drwxr-x---. 2 mysql mysql 8192 8月 26 14:44 performance_schema
-rw-------. 1 mysql mysql 1680 8月 26 14:44 private_key.pem
-rw-r--r--. 1 mysql mysql 452 8月 26 14:44 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 8月 26 14:44 server-cert.pem
-rw-------. 1 mysql mysql 1680 8月 26 14:44 server-key.pem
drwxr-x---. 2 mysql mysql 8192 8月 26 14:44 sys
//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -pwjj flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
总用量 122984
-rw-r-----. 1 mysql mysql 56 8月 26 14:44 auto.cnf
-rw-------. 1 mysql mysql 1680 8月 26 14:44 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 8月 26 14:44 ca.pem
-rw-r--r--. 1 mysql mysql 1112 8月 26 14:44 client-cert.pem
-rw-------. 1 mysql mysql 1676 8月 26 14:44 client-key.pem
-rw-r-----. 1 mysql mysql 301 8月 26 14:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月 26 15:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 26 15:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月 26 14:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 26 15:26 ibtmp1
-rw-r-----. 1 mysql mysql 19289 8月 26 15:26 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 8月 26 14:44 mysql
-rw-r-----. 1 mysql mysql 907 8月 26 15:38 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 8月 26 15:38 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 8月 26 15:38 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 8月 26 14:57 mysql.pid
drwxr-x---. 2 mysql mysql 8192 8月 26 14:44 performance_schema
-rw-------. 1 mysql mysql 1680 8月 26 14:44 private_key.pem
-rw-r--r--. 1 mysql mysql 452 8月 26 14:44 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 8月 26 14:44 server-cert.pem
-rw-------. 1 mysql mysql 1680 8月 26 14:44 server-key.pem
drwxr-x---. 2 mysql mysql 8192 8月 26 14:44 sys
恢复完全备份
[root@localhost ~]# mysql -uroot -pwjj < all-20210826.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pwjj -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wjj |
+--------------------+
[root@localhost ~]# mysql -uroot -pwjj -e "show tables from wjj;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_wjj |
+---------------+
| student |
| teacher |
+---------------+
[root@localhost ~]# mysql -uroot -pwjj -e "select * from wjj.student;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
恢复差异备份
//检查误删数据库的位置在什么地方
[root@localhost ~]# mysql -uroot -pwjj
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 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 10 | 290 | BEGIN |
| mysql_bin.000002 | 290 | Table_map | 10 | 343 | table_id: 244 (wjj.student) |
| mysql_bin.000002 | 343 | Write_rows | 10 | 393 | table_id: 244 flags: STMT_END_F |
| mysql_bin.000002 | 393 | Xid | 10 | 424 | COMMIT /* xid=642 */ |
| mysql_bin.000002 | 424 | Anonymous_Gtid | 10 | 489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 489 | Query | 10 | 560 | BEGIN |
| mysql_bin.000002 | 560 | Table_map | 10 | 613 | table_id: 244 (wjj.student) |
| mysql_bin.000002 | 613 | Update_rows | 10 | 675 | table_id: 244 flags: STMT_END_F |
| mysql_bin.000002 | 675 | Xid | 10 | 706 | COMMIT /* xid=646 */ |
| mysql_bin.000002 | 706 | Anonymous_Gtid | 10 | 771 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 771 | Query | 10 | 860 | drop database wjj |
| mysql_bin.000002 | 860 | Rotate | 10 | 907 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000002 | mysql -uroot -pwjj
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 17 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | xiaozhou | 18 |
+----+-------------+------+
12 rows in set (0.00 sec)
4. 密码破解
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
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> show tables;
mysql> select * from user\G
破解密码步骤:
1、绕过密码验证
跳过skip-grant-tables加入/etc/my.cnf
[root@localhost ~]# vim /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 = 10
log-bin = mysql_bin
skip_grant-tables
2、重启服务
[root@localhost ~]# systemctl restart mysqld.service
3、修改密码
use mysql;
update mysql.user set authentication_string = ‘123456’ where User = ‘root’ and Host = ‘localhost’;
[root@localhost ~]# 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> show tables;
mysql> update user set authentication_string = password('123456') where User = 'root' and Host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4、恢复密码验证
从/etc/my.cnf文件中删除skip-grant-tables
[root@localhost ~]# vim /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 = 10
log-bin = mysql_bin
5、重启服务
[root@localhost ~]# systemctl restart mysqld.service
5. SQL多表查询(group by、order by、inner by、left by、right by)
- order by:排序
- group by:计数、算平均值、求和等等
- inner join:查询多张表中都存在的数据
- left join:显示以左边表为主的内容,右边表有匹配左边表内容时完成显示,无匹配时NULL
- right by:显示以右边表为主的内容,左边表有匹配右边表内容时完成显示,无匹配时NULL
5.1group by(聚合函数或分组查询)
group by 一般和聚合函数一起使用才有意义,比如count sum avg等,使用group by的两个要素:
- 出现在select后面的字段,要么是聚合函数中的,要么就是group by中的。
- 要筛选结果,可以先使用where再用group by或者先用group by再用having
group by 多个条件分析
mysql> create database wjj;
Query OK, 1 row affected (0.00 sec)
mysql> use wjj;
Database changed
mysql> create table test(a varchar(20),b varchar(20),c varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','yi');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)
//第一次查询
mysql> select * from test;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | yi |
| 1 | b | yi |
| 1 | b | yi |
| 1 | b | yi |
| 1 | b | yi |
+------+------+------+
10 rows in set (0.00 sec)
结果中,按照b列来分:则是6个a,4个b;按照c列来分:则是5个jia5个yi
//第二次查询
mysql> select count(a),b from test group by b; //count后面括号里写什么多可以,可以是数字,*号,但是1的效率比*号要高,所以通常用1
+----------+------+
| count(a) | b |
+----------+------+
| 6 | a |
| 4 | b |
+----------+------+
2 rows in set (0.00 sec)
mysql> select count(1),c from test group by c; //group by c:就是以c为组去计算
+----------+------+
| count(a) | c |
+----------+------+
| 5 | jia |
| 5 | yi |
+----------+------+
2 rows in set (0.00 sec)
//求平均值
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
| 19.0000 |
+----------+
1 row in set (0.00 sec)
//求和
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
| 190 |
+----------+
1 row in set (0.00 sec)
5.2 inner by(内连接)、left join(左连接)、right by(右连接)
//先创建两个表
mysql> create table tablea(id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)
mysql> create table tableb(id int,job int,parent_id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert tablea values(1,'zhangshan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert tableb 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 tablea;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhangshan |
| 2 | lisi |
| 3 | wangwu |
+------+-----------+
3 rows in set (0.00 sec)
mysql> select * from tableb;
+------+------+-----------+
| id | job | parent_id |
+------+------+-----------+
| 1 | 23 | 1 |
| 2 | 34 | 2 |
| 3 | 34 | 4 |
+------+------+-----------+
3 rows in set (0.00 sec)
1)内连接,查询两张表相同的数据
mysql> select tablea.*,tableb.* from tablea inner join tableb on tablea.id = tableb.parent_id;
+------+-----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+-----------+------+------+-----------+
| 1 | zhangshan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
+------+-----------+------+------+-----------+
2 rows in set (0.01 sec)
2)左连接,以左边为标准:在left join左边的就是左,在left join右边的就是右如果右边跟左边不匹配的值则显示空null
mysql> select tablea.*,tableb.* from tablea left join tableb on tablea.id = tableb.parent_id;
+------+-----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+-----------+------+------+-----------+
| 1 | zhangshan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
| 3 | wangwu | NULL | NULL | NULL |
+------+-----------+------+------+-----------+
3 rows in set (0.00 sec)
3)右连接
mysql> select tablea.*,tableb.* from tablea right join tableb on tablea.id = tableb.parent_id;
+------+-----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+-----------+------+------+-----------+
| 1 | zhangshan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
| NULL | NULL | 3 | 34 | 4 |
+------+-----------+------+------+-----------+
3 rows in set (0.00 sec)