文章目录
MySQL数据备份与恢复
1.数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
1.1mysql备份工具mysqldump
一、全量备份与恢复
//查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xym |
| zz |
+--------------------+
5 rows in set (0.000 sec)
//查看表
MariaDB [xym]> show tables;
+---------------+
| Tables_in_xym |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
//对所有数据库进行备份
[root@localhost ~]# mysqldump --all-databases > all_$(date +'%y%m%d').sql
[root@localhost ~]#
[root@localhost ~]# ls
all_210826.sql anaconda-ks.cfg
//对表进行备份
[root@localhost ~]# mysqldump xym student > student_$(date +'%y%m%d').sql
[root@localhost ~]# ls
all_210826.sql anaconda-ks.cfg student_210826.sql
//对某个数据库进行备份
[root@localhost ~]# mysqldump zz > zz_$(date +'%y%m%d').sql
[root@localhost ~]# ls
all_210826.sql student_210826.sql
anaconda-ks.cfg zz_210826.sql
//删除数据库
MariaDB [xym]> drop database xym;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> drop database zz;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
//恢复
[root@localhost ~]# mysql < all_210826.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xym |
| zz |
+--------------------+
5 rows in set (0.000 sec)
//删除xym数据库里的表
MariaDB [xym]> drop table student;
Query OK, 0 rows affected (0.003 sec)
MariaDB [xym]> show tables;
Empty set (0.000 sec)
//恢复
[root@localhost ~]# mysql xym < student_210826.sql
MariaDB [xym]> show tables;
+---------------+
| Tables_in_xym |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
//删除某个数据库
MariaDB [xym]> drop database zz;
Query OK, 0 rows affected (0.001 sec)
MariaDB [xym]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xym |
+--------------------+
4 rows in set (0.000 sec)
//恢复
[root@localhost ~]# mysql < zz_210826.sql
MariaDB [xym]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xym |
| zz |
+--------------------+
5 rows in set (0.000 sec)
1.2 差异备份与恢复
一、mysql差异备份
开启MySQL服务器的二进制日志功能
//编辑文件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=1
log-bin=mysql_bin
[root@localhost ~]# systemctl restart mysqld
//对数据库进行完全备份
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xym |
| zz |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [xym]> show tables;
+---------------+
| Tables_in_xym |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
MariaDB [xym]> 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 | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.000 sec)
//完全备份
[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20218262140.sql
[root@localhost ~]# ls
all-20218262140.sql anaconda-ks.cfg
//增加新内容
MariaDB [xym]> select * from xxx;+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zzz | 10 |
| 2 | ccc | 30 |
+----+------+------+
2 rows in set (0.000 sec)
MariaDB [xym]> insert into xxx values(3,'hehe',20),(4,'xixi',50);
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [xym]> select * from xxx;+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zzz | 10 |
| 2 | ccc | 30 |
| 3 | hehe | 20 |
| 4 | xixi | 50 |
+----+------+------+
4 rows in set (0.000 sec)
MariaDB [xym]> update xxx set age = 40 where id = 3;Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [xym]> select * from xxx;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zzz | 10 |
| 2 | ccc | 30 |
| 3 | hehe | 40 |
| 4 | xixi | 50 |
+----+------+------+
4 rows in set (0.000 sec)
二、 mysql差异备份恢复
//模拟误删数据
[root@localhost ~]# mysql -uroot -p123 -e 'drop database xym';
[root@localhost ~]# mysql -uroot -p123 -e 'show databases';
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| zz |
+--------------------+
//刷新创建新的二进制日志
[root@localhost mysql]# mysqladmin -uroot -p123 flush-logs
[root@localhost mysql]# ls
aria_log.00000001 mysql
aria_log_control mysql_bin.000003
ib_buffer_pool mysql_bin.000004
ibdata1 mysql_bin.index
ib_logfile0 mysql.sock
ib_logfile1 mysql_upgrade_info
ibtmp1 performance_schema
multi-master.info zz
//恢复完全备份
[root@localhost mysql]# mysql -uroot -p123 < /root/all-20218262145.sql
[root@localhost mysql]# mysql -uroot -p123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xym |
| zz |
+--------------------+
[root@localhost mysql]# mysql -uroot -p123 -e 'show tables from xym;'
+---------------+
| Tables_in_xym |
+---------------+
| student |
| xxx |
+---------------+
[root@localhost mysql]# mysql -uroot -p123 -e 'select * from xym.xxx;'
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zzz | 10 |
| 2 | ccc | 30 |
+----+------+------+
[root@localhost mysql]# mysql -uroot -p123 -e 'select * from xym.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 | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
//恢复差异备份
MariaDB [xym]> show binlog events in 'mysql_bin.000003';
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
| mysql_bin.000003 | 256 | Gtid_list | 1 | 299 | [0-1-6] |
| mysql_bin.000003 | 299 | Binlog_checkpoint | 1 | 342 | mysql_bin.000002 |
| mysql_bin.000003 | 342 | Binlog_checkpoint | 1 | 385 | mysql_bin.000003 |
| mysql_bin.000003 | 385 | Gtid | 1 | 427 | BEGIN GTID 0-1-7 |
| mysql_bin.000003 | 427 | Query | 1 | 542 | use `xym`; insert into xxx values(3,'hehe',20),(4,'xixi',50) |
| mysql_bin.000003 | 542 | Xid | 1 | 573 | COMMIT /* xid=884 */ |
| mysql_bin.000003 | 573 | Gtid | 1 | 615 | BEGIN GTID 0-1-8 |
| mysql_bin.000003 | 615 | Query | 1 | 717 | use `xym`; update xxx set age = 40 where id = 3 |
| mysql_bin.000003 | 717 | Xid | 1 | 748 | COMMIT /* xid=886 */ |
| mysql_bin.000003 | 748 | Gtid | 1 | 790 | GTID 0-1-9 |
| mysql_bin.000003 | 790 | Query | 1 | 873 | drop database xym |
| mysql_bin.000003 | 873 | Rotate | 1 | 920 | mysql_bin.000004;pos=4 |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------+
13 rows in set (0.000 sec)
//使用mysqlbinlog恢复差异备份
[root@localhost mysql]# mysqlbinlog --stop-position=790 /var/lib/mysql/mysql_bin.000003 |mysql -uroot -p123
[root@localhost mysql]# mysql -uroot -p123 -e 'select * from xym.xxx;'
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zzz | 10 |
| 2 | ccc | 30 |
| 3 | hehe | 40 |
| 4 | xixi | 50 |
+----+------+------+
2、 left join、right join、inner join、group by的应用
- left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的行
//dage表
MariaDB [xym]> select *from dage;
+-----+-----------+
| aID | aNum |
+-----+-----------+
| 1 | a20050111 |
| 2 | a20050112 |
| 3 | a20050113 |
| 4 | a20050114 |
| 5 | a20050115 |
+-----+-----------+
//erge表
MariaDB [xym]> select *from erge;
+-----+----------+
| aID | aNum |
+-----+----------+
| 1 | 20050111 |
| 2 | 20050112 |
| 3 | 20050113 |
| 4 | 20050114 |
| 8 | 20050115 |
+-----+----------+
5 rows in set (0.00 sec)
//left join
MariaDB [xym]> select * from dage left join erge on dage.aID = erge.aID;
+-----+-----------+------+----------+
| aID | aNum | aID | aNum |
+-----+-----------+------+----------+
| 1 | a20050111 | 1 | 20050111 |
| 2 | a20050112 | 2 | 20050112 |
| 3 | a20050113 | 3 | 20050113 |
| 4 | a20050114 | 4 | 20050114 |
| 5 | a20050115 | NULL | NULL |
+-----+-----------+------+----------+
//right join
MariaDB [xym]> select * from dage right join erge on dage.aID = erge.aID;
+------+-----------+-----+----------+
| aID | aNum | aID | aNum |
+------+-----------+-----+----------+
| 1 | a20050111 | 1 | 20050111 |
| 2 | a20050112 | 2 | 20050112 |
| 3 | a20050113 | 3 | 20050113 |
| 4 | a20050114 | 4 | 20050114 |
| NULL | NULL | 8 | 20050115 |
+------+-----------+-----+----------+
//inner join
MariaDB [xym]> select *from dage inner join erge on dage.aID = erge.aID;
+-----+-----------+-----+----------+
| aID | aNum | aID | aNum |
+-----+-----------+-----+----------+
| 1 | a20050111 | 1 | 20050111 |
| 2 | a20050112 | 2 | 20050112 |
| 3 | a20050113 | 3 | 20050113 |
| 4 | a20050114 | 4 | 20050114 |
+-----+-----------+-----+----------+
4 rows in set (0.00 sec)
//group by
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
SQL GROUP BY 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
test 表如下
MariaDB [xym]> select *from test;
+----------+------+
| name | age |
+----------+------+
| xiaoming | 10 |
| xiaofang | 20 |
| xiaowu | 25 |
| xiaohu | 14 |
| xiaoming | 11 |
| xiaohu | 15 |
+----------+------+
//计数 count 函数
MariaDB [xym]> select sum(age) from test;
+----------+
| sum(age) |
+----------+
| 95 |
+----------+
1 row in set (0.00 sec)
//创建两个表 yi和er
MariaDB [xym]> select *from yi;
+----+---------------+
| id | name |
+----+---------------+
| 1 | google |
| 2 | taobao |
| 3 | cainiao |
| 4 | weibo |
| 5 | facebook |
| 6 | stackoverflow |
+----+---------------+
6 rows in set (0.00 sec)
MariaDB [xym]> select *from er;
+-----+---------+-------+
| aid | site_id | count |
+-----+---------+-------+
| 1 | 1 | 45 |
| 2 | 3 | 100 |
| 3 | 1 | 230 |
| 4 | 2 | 10 |
| 5 | 5 | 205 |
| 6 | 4 | 13 |
| 7 | 3 | 220 |
| 8 | 5 | 545 |
| 9 | 3 | 201 |
+-----+---------+-------+
9 rows in set (0.00 sec)
//统计 log 各个 site_id 的访问量
MariaDB [xym]> select site_id,sum(er.count) as nums from er group by site_id;
+---------+------+
| site_id | nums |
+---------+------+
| 1 | 275 |
| 2 | 10 |
| 3 | 521 |
| 4 | 13 |
| 5 | 750 |
+---------+------+
5 rows in set (0.00 sec)
//SQL GROUP BY 多表连接
MariaDB [xym]> select yi.name,count(er.aid) as nums from er left join web on er.site_id = yi.id group by yi.name;
+----------+------+
| name | nums |
+----------+------+
| cainiao | 3 |
| facebook | 2 |
| google | 2 |
| taobao | 1 |
| weibo | 1 |
+----------+------+
5 rows in set (0.00 sec)
8066
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
