mysql进阶

最新推荐文章于 2024-09-04 20:48:11 发布

wssswsss

最新推荐文章于 2024-09-04 20:48:11 发布

阅读量87

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_58281305/article/details/119940150

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一，完全备份和差异备份与恢复

开启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 -p1 --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)

mysql差异备份恢复

模拟误删数据

mysql> drop database lry;
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 -p1 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 -p1 < all-20210826.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 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 |
| sys                |
| lry                |
+--------------------+
[root@localhost ~]# mysql -uroot -p1 -e "show tables from lry;" 
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_lry |
+---------------+
| student       |
| teacher       |
+---------------+
[root@localhost ~]# mysql -uroot -p1 -e "select * from lry.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 -p1
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 (lry.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 (lry.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 lry                     |
| 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 -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |F
+----+-------------+------+
|  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)

二，多表查询

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
group by 多个条件分析

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

mysql> use lry;
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)

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)

wssswsss

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql进阶

一，完全备份和差异备份与恢复开启MySQL服务器的二进制日志功能[root@localhost ~]# vim /etc/my.cnf [mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306user = mysqlpid-file = /tmp/mysql.pidskip-name-resolveserver-id = 10 //设置服务器标识符
复制链接

扫一扫

专栏目录