mysql进阶
1.二进制格式mysql安装
1.下载mysql的二进制tar.gz压缩包并解压到/usr/src下面
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# ls
apache.sh a.sh certificate.sh haha httpds.sh mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz sf yum.sh
[root@localhost ~]# tar -xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/lcoal
2.创建系统用户mysql
[root@localhost local]# groupadd -r -g 306 mysql
[root@localhost local]# useradd -r -M -s /sbin/nologin -u 306 -g 306 mysql
[root@localhost ~]# id mysql
uid=1001(mysql) gid=306(mysql) 组=306(mysql)
3.给解压缩的mysql文件创建软连接并将其宿主和宿组改为mysql
[root@localhost ~]# cd /usr/local
[root@localhost local]# ln -s mysql-5.7.22-linux-glibc2.12-x86_64 mysql
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 6 root root 58 11月 13 10:33 apr
drwxr-xr-x. 5 root root 43 11月 13 10:33 apr-util
drwxr-xr-x. 2 root root 6 3月 10 2016 bin
drwxr-xr-x. 2 root root 6 3月 10 2016 etc
drwxr-xr-x. 2 root root 6 3月 10 2016 games
drwxr-xr-x. 13 root root 152 11月 13 10:51 httpd
drwxr-xr-x. 2 root root 6 3月 10 2016 include
drwxr-xr-x. 2 root root 6 3月 10 2016 lib
drwxr-xr-x. 2 root root 6 3月 10 2016 lib64
drwxr-xr-x. 2 root root 6 3月 10 2016 libexec
lrwxrwxrwx. 1 mysql mysql 35 12月 13 09:15 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 12月 13 09:14 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 3月 10 2016 sbin
drwxr-xr-x. 5 root root 49 10月 13 23:26 share
drwxr-xr-x. 2 root root 6 3月 10 2016 src```
4.设置环境变量,让其用mysql就可以执行、usr/src/mysql/bin里的命令
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/src/mysql/bin:/usr/local/httpd/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
5.创建数据存放目录/opt/data
[root@localhost ~]# mkdir /opt/data
6.初始化数据库,并将密码暂时放在/opt/pass下面
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2019-12-12T10:36:20.062467Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-12-12T10:36:20.062617Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2019-12-12T10:36:20.471107Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-12-12T10:36:20.563699Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-12-12T10:36:20.774205Z 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: 3c888bf4-1ccb-11ea-90ee-000c2931840c.
2019-12-12T10:36:20.798243Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-12-12T10:36:20.822238Z 1 [Note] A temporary password is generated for root@localhost: Tr.cEZh+I81u
[root@localhost ~]# echo 'Tr.cEZh+I81u' > /opt/pass
7.生成配置文件,先将原来/etc/my.cnf备份,然后重新写个my.cnf配置文件,并创建pid-file的存放目录
[root@localhost ~]# mv /etc/my.cnf{,-origin}
[root@localhost ~]# vim /etc/my.cnf
[root@localhost src]# 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-resolv
[root@localhost support-files]# cd /opt/data
[root@localhost data]# mkdir mysql.pid
[root@localhost data]# ls
auto.cnf ibdata1 ib_logfile1 mysql performance_schema
ib_buffer_pool ib_logfile0 localhost.localdomain.err mysql.pid sys
8.复制启动服务脚本到/etc/init.d/mysqld下面并将其下面的basedir=/usr/src/mysql
datadir=/opt/data(mysql安装位置以及数据库存放位置)
[root@localhost ~]# cd /usr/local/mysql/support-files/
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
# If you install MySQL on some other places than /usr/local/mysql, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=<path-to-mysql-installation-directory>
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
# below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.
basedir=/usr/local/mysql
datadir=/opt/data
9.启动mysql服务,3306端口已经开启
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 25 *:514 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::80 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 25 :::514 :::*
LISTEN 0 80 :::3306 :::*
10.输入初始化密码然后进入数据库修改密码
[root@localhost ~]# cat pass
y#y2U3B5dBgr
[root@localhost ~]# mysql -uroot -p'y#y2U3B5dBgr'
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.22
Copyright (c) 2000, 2018, 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> set password=password(123456);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123456)' at line 1
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
2.mysql配置文件
1.免密码登录,将用户密码写到.my.cnf文件里
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=123456
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> quit
Bye
2.破解密码,在/etc/my.cnf加上skip-grant-tables
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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
skip-grant-tables
3.然后就可以免密码登录了,然后修改密码为123
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 mysql;
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> update user set authentication_string=password('123') where User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> quit
Bye
4.然后在my.cnf的配置文件将skip-grant-tables删除,然后重启服务就可以用新密码进去
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot -p123
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.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> quit
Bye
3.修改免密登录密码改为修改后的密码。
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=123
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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>
4.创建数据库和表格
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50) not null,age tinyi,score vamysql> create table student(id int not null primary key auto_increment,name varchar(50) not null,age tinyint,score varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| score | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert student(name,age,score) values('tom',20,30),('mafei',10,60),('haha',11,33);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------+------+-------+
| id | name | age | score |
+----+-------+------+-------+
| 1 | tom | 20 | 30 |
| 2 | mafei | 10 | 60 |
| 3 | haha | 11 | 33 |
+----+-------+------+-------+
3 rows in set (0.00 sec)
mysql> quit
Bye
4.将school的所有表格备份到root下面的school.sql,将shcool下面的student表格备份
[root@localhost ~]# mysqldump -uroot -p123 school>school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
ocalhost ~]# mysqldump -uroot -p123 school student>student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
apache.sh school.sql student.sql
5.将整个数据库备份到root下面,将数据库school备份到root下面
[root@localhost ~]# mysqldump -uroot -p123 --all-databases > all-20191213.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20191213.sql apache.sh school.sql student.sql
[root@localhost ~]# mysqldump -uroot -p123 --databases school > school-20191213.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
6.删除数据库school然后备份
发现数据库school已经删除
[root@localhost ~]# mysql -uroot -p123 -e 'drop database school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
然后备份数据库,查看数据库school和数据库里的表格和内容都在已经恢复了
[root@localhost ~]# mysql -uroot -p123 < school-20191213.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -p123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
[root@localhost ~]# mysql -uroot -p123 -e 'select * from school.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+-------+
| id | name | age | score |
+----+-------+------+-------+
| 1 | tom | 20 | 30 |
| 2 | mafei | 10 | 60 |
| 3 | haha | 11 | 33 |
+----+-------+------+-------+
7.差异备份
1.先开启MySQL服务器的二进制日志功能,在/etc/my.cnf下面加上二进制日志功能,然后/opt/data就会多出日志功能
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# ls /opt/data
auto.cnf ibdata1 ib_logfile1 localhost.localdomain.err mysql_bin.000001 mysql.pid school
ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql_bin.index performance_schema sys
2.然后全量备份一次,然后/opt/data下面的mysql_bin.000001就会变成2,日志记录已经更新
[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20191213.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/data
auto.cnf ibdata1 ib_logfile1 localhost.localdomain.err mysql_bin.000002 mysql.pid school
ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql_bin.index performance_schema sys
3.进入表格修改表格内容,然后将数据库误删
[root@localhost ~]# mysql -uroot -p123
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.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> 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 | score |
+----+-------+------+-------+
| 1 | tom | 20 | 30 |
| 2 | mafei | 10 | 60 |
| 3 | haha | 11 | 33 |
+----+-------+------+-------+
3 rows in set (0.00 sec)
mysql> insert student(name,age,score) values('zhangfei',9,20);
Query OK, 1 row affected (0.00 sec)
mysql> update student set age =20 where name = 'mafei';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+----------+------+-------+
| id | name | age | score |
+----+----------+------+-------+
| 1 | tom | 20 | 30 |
| 2 | mafei | 20 | 60 |
| 3 | haha | 11 | 33 |
| 4 | zhangfei | 9 | 20 |
+----+----------+------+-------+
4 rows in set (0.00 sec)
mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
- 然后刷新日志功能,就会多出一个日志文件
[root@localhost ~]# ll /opt/data
总用量 122956
-rw-r-----. 1 mysql mysql 56 12月 13 10:19 auto.cnf
-rw-r-----. 1 mysql mysql 725 12月 13 11:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:10 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 13 12:10 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 13 10:19 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:02 ibtmp1
-rw-r-----. 1 mysql mysql 28158 12月 13 11:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 12月 13 11:45 mysql
-rw-r-----. 1 mysql mysql 894 12月 13 12:10 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 12月 13 12:02 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 12月 13 11:57 mysql.pid
drwxr-x---. 2 mysql mysql 8192 12月 13 10:19 performance_schema
drwxr-x---. 2 mysql mysql 8192 12月 13 10:19 sys
[root@localhost ~]# mysqladmin -uroot -p123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data
总用量 122960
-rw-r-----. 1 mysql mysql 56 12月 13 10:19 auto.cnf
-rw-r-----. 1 mysql mysql 725 12月 13 11:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:10 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 13 12:10 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 13 10:19 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:02 ibtmp1
-rw-r-----. 1 mysql mysql 28158 12月 13 11:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 12月 13 11:45 mysql
-rw-r-----. 1 mysql mysql 941 12月 13 12:29 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 12月 13 12:29 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 12月 13 12:29 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 12月 13 11:57 mysql.pid
drwxr-x---. 2 mysql mysql 8192 12月 13 10:19 performance_schema
drwxr-x---. 2 mysql mysql 8192 12月 13 10:19 sys
5.然后首先全量备份
[root@localhost ~]# mysql -uroot < all-20191213.sql
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@localhost ~]# mysql -e 'select * from school.student;'
+----+-------+------+-------+
| id | name | age | score |
+----+-------+------+-------+
| 1 | tom | 20 | 30 |
| 2 | mafei | 10 | 60 |
| 3 | haha | 11 | 33 |
+----+-------+------+-------+
6.然后进数据库查看日志信息,找出删除数据库的前一步是什么
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 293 | BEGIN |
| mysql_bin.000002 | 293 | Table_map | 1 | 352 | table_id: 139 (school.student) |
| mysql_bin.000002 | 352 | Write_rows | 1 | 405 | table_id: 139 flags: STMT_END_F |
| mysql_bin.000002 | 405 | Xid | 1 | 436 | COMMIT /* xid=454 */ |
| mysql_bin.000002 | 436 | Anonymous_Gtid | 1 | 501 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 501 | Query | 1 | 575 | BEGIN |
| mysql_bin.000002 | 575 | Table_map | 1 | 634 | table_id: 139 (school.student) |
| mysql_bin.000002 | 634 | Update_rows | 1 | 700 | table_id: 139 flags: STMT_END_F |
| mysql_bin.000002 | 700 | Xid | 1 | 731 | COMMIT /* xid=456 */ |
| mysql_bin.000002 | 731 | Anonymous_Gtid | 1 | 796 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 796 | Query | 1 | 894 | drop database school |
| mysql_bin.000002 | 894 | Rotate | 1 | 941 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql> show binlog events in 'mysql_bin.000002'\G;
*************************** 1. row ***************************
Log_name: mysql_bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.22-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql_bin.000002
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: mysql_bin.000002
Pos: 154
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: mysql_bin.000002
Pos: 219
Event_type: Query
Server_id: 1
End_log_pos: 293
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql_bin.000002
Pos: 293
Event_type: Table_map
Server_id: 1
End_log_pos: 352
Info: table_id: 139 (school.student)
*************************** 6. row ***************************
Log_name: mysql_bin.000002
Pos: 352
Event_type: Write_rows
Server_id: 1
End_log_pos: 405
Info: table_id: 139 flags: STMT_END_F
*************************** 7. row ***************************
Log_name: mysql_bin.000002
Pos: 405
Event_type: Xid
Server_id: 1
End_log_pos: 436
Info: COMMIT /* xid=454 */
*************************** 8. row ***************************
Log_name: mysql_bin.000002
Pos: 436
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 501
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 9. row ***************************
Log_name: mysql_bin.000002
Pos: 501
Event_type: Query
Server_id: 1
End_log_pos: 575
Info: BEGIN
*************************** 10. row ***************************
Log_name: mysql_bin.000002
Pos: 575
Event_type: Table_map
Server_id: 1
End_log_pos: 634
Info: table_id: 139 (school.student)
*************************** 11. row ***************************
Log_name: mysql_bin.000002
Pos: 634
Event_type: Update_rows
Server_id: 1
End_log_pos: 700
Info: table_id: 139 flags: STMT_END_F
*************************** 12. row ***************************
Log_name: mysql_bin.000002
Pos: 700
Event_type: Xid
Server_id: 1
End_log_pos: 731
Info: COMMIT /* xid=456 */
*************************** 13. row ***************************
Log_name: mysql_bin.000002
Pos: 731
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 796
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 14. row ***************************
Log_name: mysql_bin.000002
Pos: 796
Event_type: Query
Server_id: 1
End_log_pos: 894
Info: drop database school
*************************** 15. row ***************************
Log_name: mysql_bin.000002
Pos: 894
Event_type: Rotate
Server_id: 1
End_log_pos: 941
Info: mysql_bin.000003;pos=4
15 rows in set (0.00 sec)
ERROR:
No query specified
mysql> quit
Bye
7.然后差量备份,数据库已经恢复到删除数据库之前的内容
[root@localhost ~]# mysqlbinlog --stop-position=796 /opt/data/mysql_bin.000002|mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 | score |
+----+----------+------+-------+
| 1 | tom | 20 | 30 |
| 2 | mafei | 20 | 60 |
| 3 | haha | 11 | 33 |
| 4 | zhangfei | 9 | 20 |
+----+----------+------+-------+
4 rows in set (0.00 sec)