mysql进阶
目录
mysql进阶
1. 二进制格式mysql安装
//下载二进制格式的mysql软件包
[root@localhost ~]#wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
下载过程省略
//创建用户和组
[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
//解压软件
[root@localhost ~]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin etc games include lib lib64 libexec mysql-5.7.22-linux-glibc2.12-x86_64 sbin share src
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"
[root@localhost local]# ll
总用量 0
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. 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 root root 36 4月 29 22:02 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 4月 29 22:00 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 3月 20 03:03 share
drwxr-xr-x. 2 root root 6 3月 10 2016 src
//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 4月 29 22:02 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
//添加环境变量
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# cd
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
//建立数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data/
//初始化数据库
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2019-04-29T14:11:13.244280Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-04-29T14:11:13.484084Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-04-29T14:11:13.533925Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-04-29T14:11:13.643217Z 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: a582fbb5-6a88-11e9-b3d3-000c29282761.
2019-04-29T14:11:13.643718Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-04-29T14:11:13.645074Z 1 [Note] A temporary password is generated for root@localhost: bwR-jS:ux1oB
//请注意，这个命令的最后会生成一个临时密码，此处密码是：bwR-jS:ux1oB
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
//生成配置文件
[root@localhost ~]# cat > /etc/my.cnf <<EOF
> [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
> EOF
[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 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# vi /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
//启动mysql
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ps -ef|grep mysql
root 9007 1 0 22:25 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql 9185 9007 2 22:25 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root 9215 8847 0 22:26 pts/0 00:00:00 grep --color=auto mysql
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
[root@localhost ~]#
//修改密码
//使用临时密码登录
[root@localhost ~]# mysql -uroot -pbwR-jS:ux1oB
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('liucongrong123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
2. mysql配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
mysql常用配置文件参数：
| 参数 | 说明 |
|---|---|
| port = 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不输入密码需要编辑一个隐藏文件.my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=mysql的用户
password=登陆mysql的密码
效果
[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.
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# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
//备份整个数据库(全备)
mysql> create database liucongrong;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| liucongrong |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use liucongrong;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_liucongrong |
+-----------------------+
| student |
| teacher |
+-----------------------+
2 rows in set (0.00 sec)
全量备份
[root@localhost ~]# ls
anaconda-ks.cfg
[root@localhost ~]# mysqldump --all-databases > all_201904291559.sql
[root@localhost ~]# ls
all_201904291559.sql anaconda-ks.cfg
[root@localhost ~]# less all_201904291559.sql
备份某一个指定数据库
[root@localhost ~]# mysqldump --databases liucongrong > lcr_201904291606.sql
[root@localhost ~]# ls
all_201904291559.sql anaconda-ks.cfg lcr_201904291606.sql
备份数据库中的某一张表
[root@localhost ~]# mysqldump liucongrong(数据库名) student(表名) > student_table_201904291612
[root@localhost ~]# ls
all_201904291559.sql anaconda-ks.cfg lcr_201904291606.sql student_table_201904291612
[root@localhost ~]# less student_table_201904291612
//模拟误删liucongrong数据库
mysql> drop database liucongrong;
Query OK, 2 rows affected (0.00 sec)
3.2.1xtrabackup备份命令
- percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
特点：
1.备份还原过程快速、可靠
2.备份过程不会打断正在执行的事务
3.能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
4.开源，免费
Xtrabackup2.2版之前包括4个可执行文件：
- Innobackupex: perl 脚本
- Xtrabackup: C/C++ 编译的二进制
- Xbcrypt:加解密
- Xbstream:支持并发写的流文件格式
xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 mysqld server 没有交互；innobackupex 脚本用来备份非InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 mysqld server 发送命令进行交互，如加读锁（FTWRL）、获取位点（SHOW SLAVESTATUS）等。即innobackupex是在xtrabackup 之上做了一层封装实现的。
xtrabackup的特性：
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
1.xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
2.xtrabackup_binlog_info：mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置
3.xtrabackup_binlog_pos_innodb：二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当position
4.xtrabackup_binary：备份中用到的xtrabackup的可执行文件
5.backup-my.cnf：备份命令用到的配置选项信息在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据
xtrabackup用法
备份：innobackupex [option] BACKUP-ROOT-DIR
选项说明：
--user：该选项表示备份账号
--password：该选项表示备份的密码
--host：该选项表示备份数据库的地址
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置
--incremental：该选项表示创建一个增量备份，需要指定--incremental-
basedir
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用
--incremental-dir：该选项表示增量备份的目录
--include=name：指定表名，格式：databasename.tablename
Prepare：innobackupex --apply-log [option] BACKUP-DIR
选项说明：
--apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置
-export：表示开启可导出单独的表之后再导入其他Mysql中
--redo-only：这个选项在prepare base full backup，往其中merge增量备份时候使用
还原：innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
注意事项
1.datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-backup选项不会覆盖
2..在restore之前,必须shutdown MySQL实例，你不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下你需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户chown -R mysql:mysql /data/mysql以上需要在用户调用innobackupex之前完成--force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败!
3.3 mysql数据恢复
恢复liucongrong数据库
[root@localhost ~]# ls
all_201904291559.sql anaconda-ks.cfg lcr_database_201904291618 student_table_201904291612
[root@localhost ~]# mysql < lcr_database_201904291618
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| liucongrong |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
恢复liucongrong数据库的student表
mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)
mysql> quit
Bye
[root@localhost ~]# mysql liucongrong < student_table_201904291612
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
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 liucongrong;
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_liucongrong |
+-----------------------+
| student |
| teacher |
+-----------------------+
2 rows in set (0.00 sec)
或者使用source,前提是进入数据库中；
mysql> source student_table_201904291612;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.4 差异备份与恢复
3.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@localhost ~]# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL... SUCCESS
对数据库进行完全备份
[root@localhost ~]# ls
anaconda-ks.cfg gjhttpd.sh vhost_conf.sh
[root@localhost ~]# mysqldump --all-databases > all_201904300935.sql
[root@localhost ~]# ls
all_201904300935.sql anaconda-ks.cfg gjhttpd.sh vhost_conf.sh
[root@localhost ~]# ll all_201904300935.sql
-rw-r--r--. 1 root root 803237 4月 30 17:35 all_201904300935.sql
[root@localhost ~]# mysql
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> use liucongrong;
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 |
| 3 | sean | 28 |
+----+-------+------+
3 rows in set (0.00 sec)
新增内容和修改内容
mysql> insert student (name,age) values ('zhangsan',25),('lisi',36);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> quit
Bye
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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 liucongrong;
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_liucongrong |
+-----------------------+
| student |
| teacher |
+-----------------------+
2 rows in set (0.00 sec)
mysql> select * from teacher;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenshuo | NULL |
| 2 | zhangshan | NULL |
| 3 | wangwu | NULL |
+----+-----------+------+
3 rows in set (0.00 sec)
mysql> update teacher set age=20 where name='chenshuo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update teacher set age=30 where name='zhangshan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teacher;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenshuo | 20 |
| 2 | zhangshan | 30 |
| 3 | wangwu | NULL |
+----+-----------+------+
3 rows in set (0.00 sec)
mysql> quit
Bye
3.4.2. mysql差异备份恢复
模拟误删数据
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
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> drop database liucongrong;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> quit
Bye
//数据库已删除
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
总用量 122976
-rw-r-----. 1 mysql mysql 56 4月 29 22:11 auto.cnf
-rw-r-----. 1 mysql mysql 456 4月 30 01:10 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 4月 30 17:44 ibdata1
-rw-r-----. 1 mysql mysql 50331648 4月 30 17:44 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 4月 29 22:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 4月 30 17:35 ibtmp1
-rw-r-----. 1 mysql mysql 46993 4月 30 17:33 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 4月 29 22:11 mysql
-rw-r-----. 1 mysql mysql 1233 4月 30 17:44 mysql_bin.000001
-rw-r-----. 1 mysql mysql 19 4月 30 17:33 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 4月 30 17:33 mysql.pid
drwxr-x---. 2 mysql mysql 8192 4月 29 22:11 performance_schema
drwxr-x---. 2 mysql mysql 8192 4月 29 22:11 sys
//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin flush-logs
[root@localhost ~]# ll /opt/data/
总用量 122980
-rw-r-----. 1 mysql mysql 56 4月 29 22:11 auto.cnf
-rw-r-----. 1 mysql mysql 456 4月 30 01:10 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 4月 30 17:44 ibdata1
-rw-r-----. 1 mysql mysql 50331648 4月 30 17:44 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 4月 29 22:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 4月 30 17:35 ibtmp1
-rw-r-----. 1 mysql mysql 46993 4月 30 17:33 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 4月 29 22:11 mysql
-rw-r-----. 1 mysql mysql 1280 4月 30 17:47 mysql_bin.000001
-rw-r-----. 1 mysql mysql 154 4月 30 17:47 mysql_bin.000002
-rw-r-----. 1 mysql mysql 38 4月 30 17:47 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 4月 30 17:33 mysql.pid
drwxr-x---. 2 mysql mysql 8192 4月 29 22:11 performance_schema
drwxr-x---. 2 mysql mysql 8192 4月 29 22:11 sys
恢复完全备份
[root@localhost ~]# mysql < all_201904300935.sql
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| liucongrong |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]# mysql -e 'select * from liucongrong.student;'
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
+----+-------+------+
恢复差异备份
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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.000001';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 219 | Query | 1 | 298 | BEGIN |
| mysql_bin.000001 | 298 | Table_map | 1 | 359 | table_id: 108 (liucongrong.student) |
| mysql_bin.000001 | 359 | Write_rows | 1 | 420 | table_id: 108 flags: STMT_END_F |
| mysql_bin.000001 | 420 | Xid | 1 | 451 | COMMIT /* xid=430 */ |
| mysql_bin.000001 | 451 | Anonymous_Gtid | 1 | 516 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 516 | Query | 1 | 595 | BEGIN |
| mysql_bin.000001 | 595 | Table_map | 1 | 656 | table_id: 109 (liucongrong.teacher) |
| mysql_bin.000001 | 656 | Update_rows | 1 | 721 | table_id: 109 flags: STMT_END_F |
| mysql_bin.000001 | 721 | Xid | 1 | 752 | COMMIT /* xid=441 */ |
| mysql_bin.000001 | 752 | Anonymous_Gtid | 1 | 817 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 817 | Query | 1 | 896 | BEGIN |
| mysql_bin.000001 | 896 | Table_map | 1 | 957 | table_id: 109 (liucongrong.teacher) |
| mysql_bin.000001 | 957 | Update_rows | 1 | 1024 | table_id: 109 flags: STMT_END_F |
| mysql_bin.000001 | 1024 | Xid | 1 | 1055 | COMMIT /* xid=442 */ |
| mysql_bin.000001 | 1055 | Anonymous_Gtid | 1 | 1120 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 1120 | Query | 1 | 1233 | drop database liucongrong |
| mysql_bin.000001 | 1233 | Rotate | 1 | 1280 | mysql_bin.000002;pos=4 |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
20 rows in set (0.00 sec)
mysql> quit
Bye
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=1120 （这是删除时的开始位置） /opt/data/mysql_bin.000001 |mysql
[root@localhost ~]# mysql -e 'select * from liucongrong.student;'
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | zhangsan | 25 |
| 5 | lisi | 36 |
+----+----------+------+
[root@localhost ~]# mysql -e 'select * from liucongrong.teacher;'
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenshuo | 20 |
| 2 | zhangshan | 30 |
| 3 | wangwu | NULL |
+----+-----------+------+
