Mysql的二进制安装
下载安装包
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[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.gzResolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected.HTTP request sent, awaiting response... 302 FoundLocation: 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.gzResolving cdn.mysql.com (cdn.mysql.com)... 223.119.236.209Connecting to cdn.mysql.com (cdn.mysql.com)|223.119.236.209|:443... connected.HTTP request sent, awaiting response... 200 OKLength: 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] |
创建用户
|
1
|
[root@localhost mysql]# useradd -r -M -s /sbin/nologin mysql |
解压至指定目录
|
1
|
[root@localhost mysql]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local |
创建软链接或者修改目录名
|
1
2
3
4
|
[root@localhost local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql[root@localhost local]# lsapache apr-util etc include lib64 mysql shareapr bin games lib libexec sbin src |
修改属主和属组
|
1
2
3
|
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/[root@localhost local]# lldrwxr-xr-x. 9 mysql mysql 129 五月 4 20:40 mysql |
设置环境变量（因为不是用yum装的，找不到mysql程序）
|
1
2
3
4
5
|
[root@localhost mysql]# vim /etc/profile.d/mysql.shexport PATH=/usr/local/mysql/bin:$PATH[root@localhost mysql]# . /etc/profile.d/mysql.sh[root@localhost mysql]# which mysql/usr/local/mysql/bin/mysql |
创建存放数据的目录并修改属主属组
需要一个空间大的目录，或者将目录创建后将硬盘设备挂载在此目录上
|
1
2
3
4
|
[root@localhost mysql]# mkdir /opt/mysql_data[root@localhost mysql]# chown -R mysql.mysql /opt/mysql_data/[root@localhost mysql]# ll /optdrwxr-xr-x. 2 mysql mysql 6 五月 4 20:58 mysql_data |
初始化并保存密码
|
1
2
3
4
5
6
7
8
9
10
|
[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql_data/ //初始化 控制mysql的用户 数据存放目录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=457902021-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 passwordq_UG8?3sa/l% |
写配置文件
|
1
2
3
4
5
6
7
8
9
|
[root@localhost mysql]# 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 //跳过域名解析，即直接在内网使用ip连接数据库 |
配置启动脚本和开机自启
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
[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 -2basedir=/usr/local/mysqldatadir=/opt/mysql_data[root@localhost mysql]# service mysqld startStarting MySQL.Logging to '/opt/data/localhost.localdomain.err'. SUCCESS! [root@localhost mysql]# chkconfig mysqld on[root@localhost mysql]# chkconfig --listNote: 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 -anltState 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 [::]:* |
头文件和库文件配置
|
1
2
3
|
[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 //重新读取配置文件 |
启动并设置密码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@localhost local]# mysql -uroot -pmysql: 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 librariesRepo : baseosMatched 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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.33Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> |
Mysql的配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找顺序：若在多个配置文件中均有设定，则最后找到的配置文件中的配置生效
|
1
|
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf |
| 参数 | 说明 |
|---|---|
| port = 3306 | 设置监听端口，默认3306 |
| socket = /tmp/mysql.sock | 指定套接字文件位置 |
| basedir = /usr/local/mysql | 指定MySQL的安装路径 |
| datadir = /data/mysql | 指定MySQL的数据存放路径 |
| pid-file = /data/mysql/mysql.pid | 指定进程ID文件存放路径 |
| user = mysql | 指定MySQL以什么用户的身份提供服务 |
| skip-name-resolve |
禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求 |
备份与恢复
数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 |
全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝 数据恢复快，备份时间长 |
| 增量备份 |
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件 第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 |
备份上一次的完全备份后发生变化的所有文件 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时 我们只需对第一次全量备份和最后一次差异备份进行恢复 |
mysql备份工具mysqldump
mysqldump 语法：
|
1
2
3
4
5
6
7
|
mysqldump [OPTIONS] database [tables ...] //备份数据表 mysqldump [OPTIONS] --all-databases [OPTIONS] //备份全部数据库 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //备份某个数据库 -uUSERNAME //指定数据库用户名 -hHOST //指定服务器主机，请使用ip地址 -pPASSWORD //指定数据库用户的密码 -P# //指定数据库监听的端口，这里的#需用实际的端口号代替 |
备份所有数据库
|
1
2
3
4
|
[root@localhost ~]# mysqldump -uroot -p --all-databases > /mysql_dump/all_20210504.sqlEnter password: [root@localhost ~]# ls /mysql_dump/all_20210504.sql |
备份某个数据库
|
1
2
3
4
|
[root@localhost ~]# mysqldump -uroot -p --databases hanao > /mysql_dump/hanao_db_20210504.sqlEnter password: [root@localhost ~]# ls /mysql_dump/all_20210504.sql hanao_db_20210504.sql |
备份某个数据表
|
1
2
3
4
|
[root@localhost ~]# mysqldump -uroot -p hanao student > /mysql_dump/student_table_20210504.sqlEnter password: [root@localhost ~]# ls /mysql_dump/all_20210504.sql hanao_db_20210504.sql student_table_20210504.sql |
mysql全备数据恢复
恢复数据表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
mysql> use hanaoReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 changedmysql> source student_table_20210504.sqlQuery 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) |
恢复数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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 |+--------------------+ |
恢复所有数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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差备（事务日志备份/binlog备份）
备份
开启mysql服务二进制日志功能
|
1
2
3
4
5
6
7
8
9
10
11
|
root@localhost mysql_data]# vim /etc/my.cnf [mysqld]basedir = /usr/local/mysqldatadir = /opt/mysql_datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/mysql_data/mysql.piduser = mysqlskip-name-resolveserver-id=1 //设置服务器标识符log-bin=mysql_bin //开启二进制日志功能 |
重启服务
|
1
2
3
|
[root@localhost mysql_data]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! |
先对数据库进行全备
查看数据库中的表和数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
[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 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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) |
对数据进行全备
|
1
2
3
4
5
6
7
8
|
[root@localhost mysql_dump]# mysqldump -uroot -pHa153624... --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210506.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost mysql_dump]# lltotal 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 |
插入新数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> use hanao;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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) |
恢复备份
模拟误删数据
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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) |
刷新二进制日志文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
[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.errdrwxr-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.piddrwxr-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.pemdrwxr-x---. 2 mysql mysql 8192 五月 4 22:01 sys[root@localhost mysql_dump]# mysqladmin -uroot -pHa153624... flush-logsmysqladmin: [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.errdrwxr-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.piddrwxr-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.pemdrwxr-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 |
查看恢复的全备的数据
数据中没有后面新增的数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
[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 |+----+-------------+------+ |
恢复差异备份
查看误删数据库的操作在什么地方
可以看到drop命令开始之前的Pos是1034，所以我们用1034恢复到数据删除之前
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
[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恢复差异备份
可以看到之前新增的数据也恢复回来了
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[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 |+----+-------------+------+ |
总结
到此这篇关于Mysql二进制安装与备份的文章就介绍到这了,更多相关Mysql二进制安装与备份内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/h1228322088/article/details/116404767
