Mysql二進(jìn)制安裝與備份的全過程記錄

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

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

Mysql的二進(jìn)制安裝

下載安裝包

[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)境變量（因?yàn)椴皇怯脃um裝的，找不到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ù)的目錄并修改屬主屬組

需要一個(gè)空間大的目錄，或者將目錄創(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        //進(jìn)程文件位置
user = mysql                                //用戶
skip-name-resolve                        //跳過域名解析，即直接在內(nèi)網(wǎng)使用ip連接數(shù)據(jù)庫

配置啟動(dòng)腳本和開機(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            //重新讀取配置文件

啟動(dòng)并設(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

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

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

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

備份與恢復(fù)

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

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

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

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

mysql備份工具mysqldump

mysqldump 語法：

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

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

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

備份某個(gè)數(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

備份某個(gè)數(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ù)二進(jìn)制日志功能

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ù)器標(biāo)識(shí)符
log-bin=mysql_bin			//開啟二進(jìn)制日志功能

重啟服務(wù)

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

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

查看數(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ù)進(jìn)行全備

[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)

刷新二進(jìn)制日志文件

[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 |
+----+-------------+------+