mysql进阶
mysql进阶
目录
1. 二进制格式mysql安装
//下载二进制格式的mysql软件包 [ ~]# cd /usr/local //创建用户和组 [ local]# groupadd -r -g 306 mysql [ local]# useradd -r -M -s /sbin/nologin -u 306 -g 306 mysql //解压软件至/usr/local/ [ ~]# cd /usr/local/ [ local]# ls bin include libexec share etc lib mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz src games lib64 sbin [ local]# tar -xf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz [ local]# ls bin include libexec sbin etc lib mysql-5.7.29-linux-glibc2.12-x86_64 share games lib64 mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz src [ local]# ln -s mysql-5.7.29-linux-glibc2.12-x86_64 mysql [ local]# ll 总用量 649172 drwxr-xr-x. 2 root root 6 4月 11 2018 bin drwxr-xr-x. 2 root root 6 4月 11 2018 etc drwxr-xr-x. 2 root root 6 4月 11 2018 games drwxr-xr-x. 2 root root 6 4月 11 2018 include drwxr-xr-x. 2 root root 6 4月 11 2018 lib drwxr-xr-x. 2 root root 6 4月 11 2018 lib64 drwxr-xr-x. 2 root root 6 4月 11 2018 libexec lrwxrwxrwx. 1 root root 35 6月 9 11:11 mysql -> mysql-5.7.29-linux-glibc2.12-x86_64 drwxr-xr-x. 9 root root 129 6月 9 11:09 mysql-5.7.29-linux-glibc2.12-x86_64 -rw-r--r--. 1 root root 664749587 6月 9 11:02 mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz drwxr-xr-x. 2 root root 6 4月 11 2018 sbin drwxr-xr-x. 5 root root 49 4月 8 02:52 share drwxr-xr-x. 2 root root 6 4月 11 2018 src //修改目录/usr/local/mysql的属主属组 [ local]# chown -R mysql.mysql mysql [ local]# ll -d mysql lrwxrwxrwx. 1 mysql mysql 35 6月 9 11:11 mysql -> mysql-5.7.29-linux-glibc2.12-x86_64 //添加环境变量 [ ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh [ ~]# . /etc/profile.d/mysql.sh [ ~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin //建立数据存放目录 [ local]# mkdir /opt/mysqldata [ local]# chown -R mysql.mysql /opt/mysqldata/ [ local]# ll -d /opt/mysqldata/ drwxr-xr-x. 2 mysql mysql 6 6月 9 11:18 /opt/mysqldata/ //初始化数据库 [ ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysqldata/ 2020-06-09T03:19:38.814013Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-06-09T03:19:38.973639Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-06-09T03:19:39.000701Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-06-09T03:19:39.057201Z 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: 0d72d179-aa00-11ea-b236-000c29261efc. 2020-06-09T03:19:39.057845Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened. 2020-06-09T03:19:39.471815Z 0 [Warning] CA certificate ca.pem is self signed. 2020-06-09T03:19:39.669799Z 1 [Note] A temporary password is generated for : 4bnX(WEjtdB6 //随机的临时密码：4bnX(WEjtdB6 //生成配置文件 [ ~]# > /etc/my.cnf [ ~]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/mysqldata socket = /tmp/mysql.sock port = 3306 pid-file = /opt/mysqldata/mysql.pid user = mysql skip-name-resolve //配置服务启动脚本 [ ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [ ~]# vim /etc/init.d/mysqld basedir=/usr/local/mysql datadir=/opt/mysqldata //启动mysql [ ~]# service mysqld status ERROR! MySQL is not running [ ~]# service mysqld start Starting MySQL. SUCCESS! [ ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 *:22 *:* LISTEN 0 100 [::1]:25 [::]:* LISTEN 0 80 [::]:3306 [::]:* LISTEN 0 128 [::]:22 [::]:* [ ~]# service mysqld status SUCCESS! MySQL running (2842) [ ~]# chkconfig mysqld on [ ~]# chkconfig mysqld --list mysqld 0:关 1:关 2:开 3:开 4:开 5:开 6:关 //修改密码 //使用临时密码登录 [ ~]# mysql -uroot -p‘4bnX(WEjtdB6‘ 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 2 Server version: 5.7.29 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> set password = password(‘#$‘); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit Bye [ ~]# mysql -uroot -p‘#$‘ 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 3 Server version: 5.7.29 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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>
2. mysql配置文件
mysql`的配置文件为`/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
[ ~]# mysql -uroot -p‘#$‘ mysql> quit Bye //在家目录下创建.my.cnf [ ~]# vim .my.cnf [client] user=root password=‘#$‘ //登录不用输入密码 [ ~]# mysql -uroot -e ‘show databases;‘ +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
mysql常用配置文件参数：**
| 说明 | 参数 |
|---|---|
| 设置监听端口 | port = 3306 |
| 指定套接字文件位置 | socket = /tmp/mysql.sock |
| 指定MySQL的安装路径 | basedir = /usr/local/mysql |
| 指定MySQL的数据存放路径 | datadir = /data/mysql |
| 指定进程ID文件存放路径 | pid-file = /data/mysql/mysql.pid |
| 指定MySQL以什么用户的身份提供服务 | user = mysql |
| 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求 | skip-name-resolve |
3. mysql数据库备份与恢复
3.1 数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
3.2 mysql备份工具mysqldump
//语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P33073.2.1 备份整个数据库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use school Database changed mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | student | | teacher | +------------------+ 2 rows in set (0.00 sec) //备份整个数据库(全备) [ ~]# mysqldump -uroot -p‘#$‘ --all-databases > all-$(date +%F-%T).sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [ ~]# ll 总用量 836 -rw-r--r--. 1 root root 851541 6月 12 16:19 all-2020-06-12-16:19:49.sql -rw-------. 1 root root 1257 4月 8 02:56 anaconda-ks.cfg [ ~]# file all-2020-06-12-16\:19\:49.sql all-2020-06-12-16:19:49.sql: UTF-8 Unicode text, with very long lines
3.2.2 备份school库的student表和teacher表
[ ~]# mysqldump -uroot -p school student teacher > table-$(date +%F-%T).sql Enter password: [ ~]# [ ~]# ls all-2020-06-12-16:19:49.sql anaconda-ks.cfg table-2020-06-12-16:26:22.sql [ ~]# file table-2020-06-12-16\:26\:22.sql table-2020-06-12-16:26:22.sql: ASCII text
3.2.2 备份school库
[ ~]# mysqldump -uroot -p --databases school > school-$(date +%F-%T).sql Enter password: [ ~]# ls all-2020-06-12-16:19:49.sql school-2020-06-12-16:32:15.sql anaconda-ks.cfg table-2020-06-12-16:26:22.sql [ ~]# file school-2020-06-12-16\:32\:15.sql school-2020-06-12-16:32:15.sql: ASCII text
3.3 mysql数据恢复
//删除school库 mysql> drop database school; Query OK, 2 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) #恢复school库 [ ~]# mysql -uroot -p < school-2020-06-12-16\:32\:15.sql Enter password: [ ~]# mysql -uroot -p -e ‘show databases;‘ Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ //删除schoool库的teacher表和student表 mysql> drop table teacher; Query OK, 0 rows affected (0.00 sec) mysql> drop table student; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec) #恢复teacher表和student表 mysql> use school; Database changed mysql> show tables; Empty set (0.00 sec) mysql> source table-2020-06-12-16:26:22.sql Query OK, 0 rows affected (0.00 sec) ..... Query OK, 0 rows affected (0.00 sec) mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | student | | teacher | +------------------+ 2 rows in set (0.00 sec) //模拟删除整个数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database school; Query OK, 2 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) #恢复整个数据库 [ ~]# mysql -uroot -p < all-2020-06-12-16\:19\:49.sql Enter password: [ ~]# mysql -uroot -p -e ‘show databases;‘ Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+
3.4 差异备份与恢复
3.4.1. mysql差异备份
[mysqld] basedir = /usr/local/mysql datadir = /opt/mysqldata socket = /tmp/mysql.sock port = 3306 pid-file = /opt/mysqldata/mysql.pid user = mysql skip-name-resolve server-id=1 //设置服务器标识符 log-bin=mysql_bin //开启二进制日志功能 [ ~]# systemctl restart mysqld
对数据库进行完全备份
[ ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> show tables from school; +------------------+ | Tables_in_school | +------------------+ | student | | teacher | +------------------+ 2 rows in set (0.00 sec) mysql> select * from school.student; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | +----+-------+------+ 2 rows in set (0.00 sec) mysql> select * from school.teacher; +----+----------+------+-------+ | id | name | age | slary | +----+----------+------+-------+ | 1 | laowang | 30 | 11000 | | 2 | laozhang | 40 | 15000 | +----+----------+------+-------+ 2 rows in set (0.00 sec) #完全备份 [ ~]# mysqldump -uroot -p‘#$‘ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20200612.sql [ ~]# mysqldump -uroot -p‘#$‘ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20200612.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [ ~]# ll 总用量 1676 -rw-r--r--. 1 root root 851541 6月 12 16:19 all-2020-06-12-16:19:49.sql -rw-r--r--. 1 root root 851693 6月 12 17:16 all-20200612.sql //新增内容 [ ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> use school; 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> select * from student; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | +----+-------+------+ 2 rows in set (0.00 sec) mysql> insert student(name,age) value(‘natasha‘,25); Query OK, 1 row affected (0.00 sec) mysql> update student set age=17 where name=‘jerry‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | tom | 20 | | 2 | jerry | 17 | | 3 | natasha | 25 | +----+---------+------+ 3 rows in set (0.00 sec) mysql>
3.4.2. mysql差异备份恢复
模拟误删数据
[ ~]# mysql -uroot -p‘#$‘ -e ‘drop database school;‘ mysql: [Warning] Using a password on the command line interface can be insecure. [ ~]# mysql -uroot -p‘#$‘ -e ‘show databases;;‘ mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
刷新创建新的二进制日志
[ ~]# ll /opt/mysqldata/ 总用量 122992 -rw-r-----. 1 mysql mysql 56 6月 9 11:19 auto.cnf -rw-------. 1 mysql mysql 1680 6月 9 11:19 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 ca.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 client-cert.pem -rw-------. 1 mysql mysql 1680 6月 9 11:19 client-key.pem -rw-r-----. 1 mysql mysql 510 6月 12 16:59 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 6月 12 17:21 ibdata1 -rw-r-----. 1 mysql mysql 50331648 6月 12 17:21 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 6月 9 11:19 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 6月 12 17:16 ibtmp1 -rw-r-----. 1 mysql mysql 30936 6月 12 16:59 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 6月 12 16:55 mysql -rw-r-----. 1 mysql mysql 878 6月 12 17:21 mysql_bin.000004 -rw-r-----. 1 mysql mysql 19 6月 12 17:16 mysql_bin.index -rw-r-----. 1 mysql mysql 5 6月 12 16:59 mysql.pid drwxr-x---. 2 mysql mysql 8192 6月 9 11:19 performance_schema -rw-------. 1 mysql mysql 1676 6月 9 11:19 private_key.pem -rw-r--r--. 1 mysql mysql 452 6月 9 11:19 public_key.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 server-cert.pem -rw-------. 1 mysql mysql 1676 6月 9 11:19 server-key.pem drwxr-x---. 2 mysql mysql 8192 6月 9 11:19 sys //刷新创建新的二进制日志 [ ~]# mysqladmin -uroot -p‘#$‘ flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [ ~]# ll /opt/mysqldata/ 总用量 122996 -rw-r-----. 1 mysql mysql 56 6月 9 11:19 auto.cnf -rw-------. 1 mysql mysql 1680 6月 9 11:19 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 ca.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 client-cert.pem -rw-------. 1 mysql mysql 1680 6月 9 11:19 client-key.pem -rw-r-----. 1 mysql mysql 510 6月 12 16:59 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 6月 12 17:21 ibdata1 -rw-r-----. 1 mysql mysql 50331648 6月 12 17:21 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 6月 9 11:19 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 6月 12 17:16 ibtmp1 -rw-r-----. 1 mysql mysql 30936 6月 12 16:59 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 6月 12 16:55 mysql -rw-r-----. 1 mysql mysql 925 6月 12 17:24 mysql_bin.000004 -rw-r-----. 1 mysql mysql 154 6月 12 17:24 mysql_bin.000005 -rw-r-----. 1 mysql mysql 38 6月 12 17:24 mysql_bin.index -rw-r-----. 1 mysql mysql 5 6月 12 16:59 mysql.pid drwxr-x---. 2 mysql mysql 8192 6月 9 11:19 performance_schema -rw-------. 1 mysql mysql 1676 6月 9 11:19 private_key.pem -rw-r--r--. 1 mysql mysql 452 6月 9 11:19 public_key.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 server-cert.pem -rw-------. 1 mysql mysql 1676 6月 9 11:19 server-key.pem drwxr-x---. 2 mysql mysql 8192 6月 9 11:19 sys
恢复完全备份
[ ~]# mysql -uroot -p‘#$‘ < all-20200612.sql mysql: [Warning] Using a password on the command line interface can be insecure. [ ~]# mysql -uroot -p‘#$‘ -e ‘show databases;‘ mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ [ ~]# mysql -uroot -p‘#$‘ -e ‘select * from school.student;‘ mysql: [Warning] Using a password on the command line interface can be insecure. +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | +----+-------+------+ [ ~]# mysql -uroot -p‘#$‘ -e ‘select * from school.teacher;‘ mysql: [Warning] Using a password on the command line interface can be insecure. +----+----------+------+-------+ | id | name | age | slary | +----+----------+------+-------+ | 1 | laowang | 30 | 11000 | | 2 | laozhang | 40 | 15000 | +----+----------+------+-------+
恢复差异备份
[ ~]# ll /opt/mysqldata/ 总用量 124016 -rw-r-----. 1 mysql mysql 56 6月 9 11:19 auto.cnf -rw-------. 1 mysql mysql 1680 6月 9 11:19 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 ca.pem -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 client-cert.pem -rw-------. 1 mysql mysql 1680 6月 9 11:19 client-key.pem -rw-r-----. 1 mysql mysql 510 6月 12 16:59 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 6月 12 17:26 ibdata1 -rw-r-----. 1 mysql mysql 50331648 6月 12 17:26 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 6月 9 11:19 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 6月 12 17:16 ibtmp1 -rw-r-----. 1 mysql mysql 30936 6月 12 16:59 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 6月 12 17:26 mysql -rw-r-----. 1 mysql mysql 925 6月 12 17:24 mysql_bin.000004 -rw-r-----. 1 mysql mysql 833167 6月 12 17:26 mysql_bin.000005 -rw-r-----. 1 mysql mysql 38 6月 12 17:24 mysql_bin.index -rw-r-----. 1 mysql mysql 5 6月 12 16:59 mysql.pid drwxr-x---. 2 mysql mysql 8192 6月 9 11:19 performance_schema -rw-------. 1 mysql mysql 1676 6月 9 11:19 private_key.pem -rw-r--r--. 1 mysql mysql 452 6月 9 11:19 public_key.pem drwxr-x---. 2 mysql mysql 96 6月 12 17:26 school -rw-r--r--. 1 mysql mysql 1112 6月 9 11:19 server-cert.pem -rw-------. 1 mysql mysql 1676 6月 9 11:19 server-key.pem drwxr-x---. 2 mysql mysql 8192 6月 9 11:19 sys //检查误删数据库的位置在什么地方 [ ~]# mysql -uroot -p‘#$‘ mysql> show binlog events in ‘mysql_bin.000004‘; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql_bin.000004 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 | | mysql_bin.000004 | 123 | Previous_gtids | 1 | 154 | | | mysql_bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘ | | mysql_bin.000004 | 219 | Query | 1 | 293 | BEGIN | | mysql_bin.000004 | 293 | Table_map | 1 | 349 | table_id: 142 (school.student) | | mysql_bin.000004 | 349 | Write_rows | 1 | 398 | table_id: 142 flags: STMT_END_F | | mysql_bin.000004 | 398 | Xid | 1 | 429 | COMMIT /* xid=496 */ | | mysql_bin.000004 | 429 | Anonymous_Gtid | 1 | 494 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘ | | mysql_bin.000004 | 494 | Query | 1 | 568 | BEGIN | | mysql_bin.000004 | 568 | Table_map | 1 | 624 | table_id: 142 (school.student) | | mysql_bin.000004 | 624 | Update_rows | 1 | 684 | table_id: 142 flags: STMT_END_F | | mysql_bin.000004 | 684 | Xid | 1 | 715 | COMMIT /* xid=497 */ | | mysql_bin.000004 | 715 | Anonymous_Gtid | 1 | 780 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘ | | mysql_bin.000004 | 780 | Query | 1 | 878 | drop database school | | mysql_bin.000004 | 878 | Rotate | 1 | 925 | mysql_bin.000005;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 15 rows in set (0.00 sec) //使用mysqlbinlog恢复差异备份 [ ~]# mysqlbinlog --stop-position=780 /opt/mysqldata/mysql_bin.000004 | mysql -uroot -p‘#$‘ mysql: [Warning] Using a password on the command line interface can be insecure. [ ~]# mysql -uroot -p‘#$‘ -e ‘select * from school.student;‘ mysql: [Warning] Using a password on the command line interface can be insecure. +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | tom | 20 | | 2 | jerry | 17 | | 3 | natasha | 25 | +----+---------+------+