一,完全备份和差异备份与恢复
开启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)