首页 > 数据库技术 > 详细

linux-mysql差异备份

时间:2021-05-08 09:55:41      阅读:22      评论:0      收藏:0      [点我收藏+]

开启MySQL服务器的二进制日志功能

[root@zwy1 ~]# 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@zwy1 ~]# service mysqld restart  #重启服务
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!  



完全备份

[root@zwy1 ~]# mysqldump -uroot -p4564949a --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-210508.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zwy1 ~]# ls
all-210508.sql  anaconda-ks.cfg  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz  nginx-1.18.0  nginx-1.18.0.tar.gz  pass

添加新内容

mysql> insert student (name,age)values(lisi,26),(heigou,24);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from student;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  1 | tom       |   20 |  NULL |
|  2 | jerry     |   14 |  NULL |
|  3 | zhangshan |   18 |  NULL |
|  4 | lisi      |   26 |  NULL |
|  5 | heigou    |   24 |  NULL |
+----+-----------+------+-------+
5 rows in set (0.00 sec)

mysql> update student set score =90 where name=lisi;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from student;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  1 | tom       |   20 |  NULL |
|  2 | jerry     |   14 |  NULL |
|  3 | zhangshan |   18 |  NULL |
|  4 | lisi      |   26 |    90 |
|  5 | heigou    |   24 |  NULL |
+----+-----------+------+-------+
5 rows in set (0.00 sec)

模拟误删

[root@zwy1 ~]# mysql -e drop database school;
[root@zwy1 ~]# mysql -e show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

刷新创建新的二进制日志

[root@zwy1 data]# ll
total 122988
-rw-r-----. 1 mysql mysql       56 May  8 01:17 auto.cnf
-rw-------. 1 mysql mysql     1676 May  8 01:17 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 May  8 01:17 ca.pem
-rw-r--r--. 1 mysql mysql     1112 May  8 01:17 client-cert.pem
-rw-------. 1 mysql mysql     1676 May  8 01:17 client-key.pem
-rw-r-----. 1 mysql mysql      405 May  8 02:17 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 May  8 03:39 ibdata1
-rw-r-----. 1 mysql mysql 50331648 May  8 03:39 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 May  8 01:17 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 May  8 02:18 ibtmp1
drwxr-x---. 2 mysql mysql     4096 May  8 01:17 mysql
-rw-r-----. 1 mysql mysql      177 May  8 02:17 mysql_bin.000002
-rw-r-----. 1 mysql mysql      941 May  8 03:39 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 May  8 03:39 mysql_bin.000004
-rw-r-----. 1 mysql mysql       57 May  8 03:39 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 May  8 02:18 mysql.pid
drwxr-x---. 2 mysql mysql     8192 May  8 01:17 performance_schema
-rw-------. 1 mysql mysql     1680 May  8 01:17 private_key.pem
-rw-r--r--. 1 mysql mysql      452 May  8 01:17 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 May  8 01:17 server-cert.pem
-rw-------. 1 mysql mysql     1676 May  8 01:17 server-key.pem
drwxr-x---. 2 mysql mysql     8192 May  8 01:17 sys
-rw-r-----. 1 mysql mysql    19203 May  8 02:18 zwy1.err
[root@zwy1 data]# cat mysql_bin.index 
./mysql_bin.000002
./mysql_bin.000003
./mysql_bin.000004

全量备份恢复

[root@zwy1 ~]# mysql  -uroot < all-210508.sql 
[root@zwy1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from student;
ERROR 1046 (3D000): No database selected
mysql> select * from  school.student;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  1 | tom       |   20 |  NULL |
|  2 | jerry     |   14 |  NULL |
|  3 | zhangshan |   18 |  NULL |
+----+-----------+------+-------+
3 rows in set (0.00 sec)

检查误删数据库的位置在什么地方

mysql> show binlog events in mysql_bin.000003;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= ANONYMOUS  |
| mysql_bin.000003 | 219 | Query          |        10 |         293 | BEGIN                                 |
| mysql_bin.000003 | 293 | Table_map      |        10 |         351 | table_id: 99 (school.student)         |
| mysql_bin.000003 | 351 | Write_rows     |        10 |         410 | table_id: 99 flags: STMT_END_F        |
| mysql_bin.000003 | 410 | Xid            |        10 |         441 | COMMIT /* xid=17 */                   |
| mysql_bin.000003 | 441 | Anonymous_Gtid |        10 |         506 | SET @@SESSION.GTID_NEXT= ANONYMOUS  |
| mysql_bin.000003 | 506 | Query          |        10 |         580 | BEGIN                                 |
| mysql_bin.000003 | 580 | Table_map      |        10 |         638 | table_id: 99 (school.student)         |
| mysql_bin.000003 | 638 | Update_rows    |        10 |         700 | table_id: 99 flags: STMT_END_F        |
| mysql_bin.000003 | 700 | Xid            |        10 |         731 | COMMIT /* xid=19 */                   |
| mysql_bin.000003 | 731 | Anonymous_Gtid |        10 |         796 | SET @@SESSION.GTID_NEXT= ANONYMOUS  |
| mysql_bin.000003 | 796 | Query          |        10 |         894 | drop database school                  |
| mysql_bin.000003 | 894 | Rotate         |        10 |         941 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

使用mysqlbinlog恢复差异备份

[root@zwy1 ~]# mysqlbinlog  --stop-position=796 /opt/data/mysql_bin.000003 |mysql -uroot -p4564949a
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zwy1 ~]# mysql -e select * from school.student;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  1 | tom       |   20 |  NULL |
|  2 | jerry     |   14 |  NULL |
|  3 | zhangshan |   18 |  NULL |
|  4 | lisi      |   26 |    90 |
|  5 | heigou    |   24 |  NULL |
+----+-----------+------+-------+

 

linux-mysql差异备份

原文:https://www.cnblogs.com/Mariko/p/14743384.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!