Mysql二進制安裝與備份的全過程記錄

更新時間：2022年01月27日 10:18:53 作者：??

這篇文章主要給大家介紹了關(guān)于Mysql二進制安裝與備份的相關(guān)資料,文中通過實例代碼介紹的非常詳細,對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下

Mysql的二進制安裝

下載安裝包

[root@localhost mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
--2021-05-04 20:34:21--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz [following]
--2021-05-04 20:34:23--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 223.119.236.209
Connecting to cdn.mysql.com (cdn.mysql.com)|223.119.236.209|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 661718255 (631M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz'
mysql-5.7.33-linux- 100%[================>] 631.06M  11.3MB/s    in 57s     
2021-05-04 20:35:21 (11.0 MB/s) - ‘mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz' saved [661718255/661718255]

創(chuàng)建用戶

[root@localhost mysql]# useradd -r -M -s /sbin/nologin mysql

解壓至指定目錄

[root@localhost mysql]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local

創(chuàng)建軟鏈接或者修改目錄名

[root@localhost local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ls
apache  apr-util  etc    include  lib64    mysql  share
apr     bin       games  lib      libexec  sbin   src

修改屬主和屬組

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost local]# ll
drwxr-xr-x.  9 mysql mysql 129 五月    4 20:40 mysql

設(shè)置環(huán)境變量（因為不是用yum裝的，找不到mysql程序）

[root@localhost mysql]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost mysql]# . /etc/profile.d/mysql.sh
[root@localhost mysql]# which mysql
/usr/local/mysql/bin/mysql

創(chuàng)建存放數(shù)據(jù)的目錄并修改屬主屬組

需要一個空間大的目錄，或者將目錄創(chuàng)建后將硬盤設(shè)備掛載在此目錄上

[root@localhost mysql]# mkdir /opt/mysql_data
[root@localhost mysql]# chown -R mysql.mysql /opt/mysql_data/
[root@localhost mysql]# ll /opt
drwxr-xr-x. 2 mysql mysql 6 五月    4 20:58 mysql_data

初始化并保存密碼

[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql_data/        //初始化    控制mysql的用戶        數(shù)據(jù)存放目錄
2021-05-04T13:01:07.403961Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-04T13:01:07.683107Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-04T13:01:07.739366Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-04T13:01:07.746720Z 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: caa21b8a-acd8-11eb-b7ab-000c294bb269.
2021-05-04T13:01:07.747895Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-04T13:01:09.096727Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-04T13:01:09.485357Z 1 [Note] A temporary password is generated for root@localhost: q_UG8?3sa/l%
[root@localhost mysql]# vim password
q_UG8?3sa/l%

寫配置文件

[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql            //程序位置
datadir = /opt/mysql_data            //數(shù)據(jù)存放位置
socket = /tmp/mysql.sock            //文件套接字位置
port = 3306                                    //端口
pid-file = /opt/mysql_data/mysql.pid        //進程文件位置
user = mysql                                //用戶
skip-name-resolve                        //跳過域名解析，即直接在內(nèi)網(wǎng)使用ip連接數(shù)據(jù)庫

配置啟動腳本和開機自啟

[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/mysql_data#g' /etc/init.d/mysqld
[root@localhost mysql]# head -47 /etc/init.d/mysqld |tail -2
basedir=/usr/local/mysql
datadir=/opt/mysql_data
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost mysql]# chkconfig  mysqld on
[root@localhost mysql]# chkconfig --list
Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.
      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.
mysqld             0:off    1:off    2:on    3:on    4:on    5:on    6:off
[root@localhost mysql]# ss -anlt
State   Recv-Q  Send-Q    Local Address:Port     Peer Address:Port  Process  
LISTEN  0       128             0.0.0.0:22            0.0.0.0:*              
LISTEN  0       128                   *:443                 *:*              
LISTEN  0       80                    *:3306                *:*              
LISTEN  0       128                   *:80                  *:*              
LISTEN  0       128                [::]:22               [::]:*

頭文件和庫文件配置

[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql                        [root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost mysql]# ldconfig            //重新讀取配置文件

啟動并設(shè)置密碼

[root@localhost local]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory        //缺少包
[root@localhost local]# dnf provides libncurses.so.5                        //查看包所依賴的類庫
Warning: failed loading '/etc/yum.repos.d/mssql-server.repo', skipping.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5
[root@localhost local]# dnf -y install ncurses-compat-libs
[root@localhost local]# cat password
/sdjtceDy7F7
[root@localhost local]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33
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>

Mysql的配置文件

mysql的配置文件為/etc/my.cnf

配置文件查找順序：若在多個配置文件中均有設(shè)定，則最后找到的配置文件中的配置生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

參數(shù)	說明
port = 3306	設(shè)置監(jiān)聽端口，默認3306
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安裝路徑
datadir = /data/mysql	指定MySQL的數(shù)據(jù)存放路徑
pid-file = /data/mysql/mysql.pid	指定進程ID文件存放路徑
user = mysql	指定MySQL以什么用戶的身份提供服務(wù)
skip-name-resolve	禁止MySQL對外部連接進行DNS解析使用這一選項可以消除MySQL進行DNS解析的時間若開啟該選項，則所有遠程主機連接授權(quán)都要使用IP地址方式否則MySQL將無法正常處理連接請求

備份與恢復(fù)

數(shù)據(jù)庫常用備份方案

數(shù)據(jù)庫備份方案：

全量備份
增量備份
差異備份

備份方案	特點
全量備份	全量備份就是指對某一個時間點上的所有數(shù)據(jù)或應(yīng)用進行的一個完全拷貝數(shù)據(jù)恢復(fù)快，備份時間長
增量備份	增量備份是指在一次全備份或上一次增量備份后，以后每次的備份只需備份與前一次相比增加和者被修改的文件。這就意味著，第一次增量備份的對象是進行全備后所產(chǎn)生的增加和修改的文件第二次增量備份的對象是進行第一次增量備份后所產(chǎn)生的增加和修改的文件，如此類推沒有重復(fù)的備份數(shù)據(jù) 備份時間短恢復(fù)數(shù)據(jù)時必須按一定的順序進行
差異備份	備份上一次的完全備份后發(fā)生變化的所有文件差異備份是指在一次全備份后到進行差異備份的這段時間內(nèi) 對那些增加或者修改文件的備份。在進行恢復(fù)時我們只需對第一次全量備份和最后一次差異備份進行恢復(fù)

mysql備份工具mysqldump

mysqldump 語法：

??? ?mysqldump [OPTIONS] database [tables ...]?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?//備份數(shù)據(jù)表
? ? mysqldump [OPTIONS] --all-databases [OPTIONS]?? ??? ??? ??? ??? ??? ??? ??? ??? ?//備份全部數(shù)據(jù)庫
? ? mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]?? ??? ??? ?//備份某個數(shù)據(jù)庫
?? ?-uUSERNAME ? ? ?//指定數(shù)據(jù)庫用戶名
? ? -hHOST ? ? ? ? ?//指定服務(wù)器主機，請使用ip地址
? ? -pPASSWORD ? ? ?//指定數(shù)據(jù)庫用戶的密碼
? ? -P# ? ? ? ? ? ? //指定數(shù)據(jù)庫監(jiān)聽的端口，這里的#需用實際的端口號代替

備份所有數(shù)據(jù)庫

[root@localhost ~]# mysqldump -uroot -p  --all-databases > /mysql_dump/all_20210504.sql
Enter password: 
[root@localhost ~]# ls /mysql_dump/
all_20210504.sql

備份某個數(shù)據(jù)庫

[root@localhost ~]# mysqldump -uroot -p  --databases hanao > /mysql_dump/hanao_db_20210504.sql
Enter password: 
[root@localhost ~]# ls /mysql_dump/
all_20210504.sql  hanao_db_20210504.sql

備份某個數(shù)據(jù)表

[root@localhost ~]# mysqldump -uroot -p hanao student > /mysql_dump/student_table_20210504.sql
Enter password: 
[root@localhost ~]# ls /mysql_dump/
all_20210504.sql  hanao_db_20210504.sql  student_table_20210504.sql

mysql全備數(shù)據(jù)恢復(fù)

恢復(fù)數(shù)據(jù)表

mysql> use hanao
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> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

[root@localhost ~]# cd /mysql_dump/
[root@localhost mysql_dump]# mysql -uroot -pHa153624.
mysql> use hanao;
Database changed
mysql> source student_table_20210504.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_hanao |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

恢復(fù)數(shù)據(jù)庫

mysql> drop database hanao;
Query OK, 1 row affected (0.00 sec)

[root@localhost mysql_dump]# mysql -uroot -pHa153624. < hanao_db_20210504.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.


[root@localhost mysql_dump]# mysql -uroot -pHa153624. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ha                 |
| hanao              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

恢復(fù)所有數(shù)據(jù)庫

mysql> drop database ha;
Query OK, 1 row affected (0.01 sec)

[root@localhost mysql_dump]# mysql -uroot -pHa153624. < all_20210504.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -pHa153624. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ha                 |
| hanao              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql差備（事務(wù)日志備份/binlog備份）

備份

開啟mysql服務(wù)二進制日志功能

root@localhost mysql_data]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
server-id=1						//設(shè)置服務(wù)器標識符
log-bin=mysql_bin			//開啟二進制日志功能

重啟服務(wù)

[root@localhost mysql_data]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

先對數(shù)據(jù)庫進行全備

查看數(shù)據(jù)庫中的表和數(shù)據(jù)

[root@localhost /]# mysql -uroot -pHa153624...
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 4
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 |
| hanao              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hanao;
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;
+-----------------+
| Tables_in_hanao |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 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        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

對數(shù)據(jù)進行全備

[root@localhost mysql_dump]# mysqldump -uroot -pHa153624... --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# ll
total 1720
-rw-r--r--. 1 root root 874762 五月    4 22:48 all_20210504.sql
-rw-r--r--. 1 root root 873608 五月    6 18:19 all-20210506.sql
-rw-r--r--. 1 root root   2250 五月    4 22:51 hanao_db_20210504.sql
-rw-r--r--. 1 root root   2108 五月    4 22:52 student_table_20210504.sql

插入新數(shù)據(jù)

mysql> use hanao;
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> insert into student(name,age) value('hanao',22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,age) value('hanao',21);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,age) value('tengjia',21);
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        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | hanao       |   22 |
| 13 | hanao       |   21 |
| 14 | tengjia     |   21 |
+----+-------------+------+
14 rows in set (0.00 sec)

恢復(fù)備份

模擬誤刪數(shù)據(jù)

mysql> drop database hanao;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新二進制日志文件

[root@localhost mysql_dump]# ll /opt/mysql_data/
total 123020
-rw-r-----. 1 mysql mysql       56 五月    4 22:01 auto.cnf
-rw-------. 1 mysql mysql     1680 五月    4 22:01 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 五月    4 22:01 ca.pem
-rw-r--r--. 1 mysql mysql     1112 五月    4 22:01 client-cert.pem
-rw-------. 1 mysql mysql     1680 五月    4 22:01 client-key.pem
-rw-r-----. 1 mysql mysql      562 五月    6 18:06 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 五月    6 18:24 ibdata1
-rw-r-----. 1 mysql mysql 50331648 五月    6 18:24 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 五月    4 22:01 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 五月    6 18:19 ibtmp1
-rw-r-----. 1 mysql mysql    58417 五月    6 18:06 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 五月    4 23:03 mysql
-rw-r-----. 1 mysql mysql     1129 五月    6 18:23 mysql_bin.000010
-rw-r-----. 1 mysql mysql       19 五月    6 18:19 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 五月    6 18:06 mysql.pid
drwxr-x---. 2 mysql mysql     8192 五月    4 22:01 performance_schema
-rw-------. 1 mysql mysql     1680 五月    4 22:01 private_key.pem
-rw-r--r--. 1 mysql mysql      452 五月    4 22:01 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 五月    4 22:01 server-cert.pem
-rw-------. 1 mysql mysql     1676 五月    4 22:01 server-key.pem
drwxr-x---. 2 mysql mysql     8192 五月    4 22:01 sys
[root@localhost mysql_dump]# mysqladmin -uroot -pHa153624... flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# ll /opt/mysql_data/
total 123024
-rw-r-----. 1 mysql mysql       56 五月    4 22:01 auto.cnf
-rw-------. 1 mysql mysql     1680 五月    4 22:01 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 五月    4 22:01 ca.pem
-rw-r--r--. 1 mysql mysql     1112 五月    4 22:01 client-cert.pem
-rw-------. 1 mysql mysql     1680 五月    4 22:01 client-key.pem
-rw-r-----. 1 mysql mysql      562 五月    6 18:06 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 五月    6 18:24 ibdata1
-rw-r-----. 1 mysql mysql 50331648 五月    6 18:24 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 五月    4 22:01 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 五月    6 18:19 ibtmp1
-rw-r-----. 1 mysql mysql    58417 五月    6 18:06 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 五月    4 23:03 mysql
-rw-r-----. 1 mysql mysql     1176 五月    6 18:24 mysql_bin.000010
-rw-r-----. 1 mysql mysql      154 五月    6 18:24 mysql_bin.000011
-rw-r-----. 1 mysql mysql       38 五月    6 18:24 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 五月    6 18:06 mysql.pid
drwxr-x---. 2 mysql mysql     8192 五月    4 22:01 performance_schema
-rw-------. 1 mysql mysql     1680 五月    4 22:01 private_key.pem
-rw-r--r--. 1 mysql mysql      452 五月    4 22:01 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 五月    4 22:01 server-cert.pem
-rw-------. 1 mysql mysql     1676 五月    4 22:01 server-key.pem
drwxr-x---. 2 mysql mysql     8192 五月    4 22:01 sys
[root@localhost mysql_dump]# cat /opt/mysql_data/mysql_bin.index
./mysql_bin.000010
./mysql_bin.000011

查看恢復(fù)的全備的數(shù)據(jù)

數(shù)據(jù)中沒有后面新增的數(shù)據(jù)

[root@localhost mysql_dump]# mysql -uroot -pHa153624... < all-20210506.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -pHa153624... -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hanao              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql_dump]# mysql -uroot -pHa153624... -e 'select * from hanao.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        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

恢復(fù)差異備份

查看誤刪數(shù)據(jù)庫的操作在什么地方

可以看到drop命令開始之前的Pos是1034，所以我們用1034恢復(fù)到數(shù)據(jù)刪除之前

[root@localhost mysql_dump]# mysql -uroot -pHa153624...
mysql> show binlog events in 'mysql_bin.000010';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000010 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql_bin.000010 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000010 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000010 |  219 | Query          |         1 |         292 | BEGIN                                 |
| mysql_bin.000010 |  292 | Table_map      |         1 |         347 | table_id: 133 (hanao.student)         |
| mysql_bin.000010 |  347 | Write_rows     |         1 |         394 | table_id: 133 flags: STMT_END_F       |
| mysql_bin.000010 |  394 | Xid            |         1 |         425 | COMMIT /* xid=518 */                  |
| mysql_bin.000010 |  425 | Anonymous_Gtid |         1 |         490 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000010 |  490 | Query          |         1 |         563 | BEGIN                                 |
| mysql_bin.000010 |  563 | Table_map      |         1 |         618 | table_id: 133 (hanao.student)         |
| mysql_bin.000010 |  618 | Write_rows     |         1 |         665 | table_id: 133 flags: STMT_END_F       |
| mysql_bin.000010 |  665 | Xid            |         1 |         696 | COMMIT /* xid=519 */                  |
| mysql_bin.000010 |  696 | Anonymous_Gtid |         1 |         761 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000010 |  761 | Query          |         1 |         834 | BEGIN                                 |
| mysql_bin.000010 |  834 | Table_map      |         1 |         889 | table_id: 133 (hanao.student)         |
| mysql_bin.000010 |  889 | Write_rows     |         1 |         938 | table_id: 133 flags: STMT_END_F       |
| mysql_bin.000010 |  938 | Xid            |         1 |         969 | COMMIT /* xid=520 */                  |
| mysql_bin.000010 |  969 | Anonymous_Gtid |         1 |        1034 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000010 | 1034 | Query          |         1 |        1129 | drop database hanao                   |
| mysql_bin.000010 | 1129 | Rotate         |         1 |        1176 | mysql_bin.000011;pos=4                |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
20 rows in set (0.00 sec)

使用mysqlbinlog恢復(fù)差異備份

可以看到之前新增的數(shù)據(jù)也恢復(fù)回來了

[root@localhost mysql_dump]# mysqlbinlog --stop-position=1034 /opt/mysql_data/mysql_bin.000010 |mysql -uroot -pHa153624...
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -pHa153624... -e 'select * from hanao.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        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | hanao       |   22 |
| 13 | hanao       |   21 |
| 14 | tengjia     |   21 |
+----+-------------+------+