此种方式安装不能与yum方式安装共存（须恢复快照）
官网
将包传到/usr/src下面 此时该目录下就有一个包 [root@localhost ~]# cd /usr/src/ [root@localhost src]# ls debug mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz kernels [root@localhost src]# du -sh mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz 读取该包大小 360M mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz 创建系统用户和组 该用户不能登陆 [root@localhost src]# useradd -r -M -s /sbin/nologin mysql [root@localhost src]# id mysql uid=994(mysql) gid=991(mysql) groups=991(mysql) 解压软件至/usr/local/ [root@localhost src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost ~]# ls /usr/local/ bin lib sbin etc lib64 share games libexec src include mysql-5.7.31-linux-glibc2.12-x86_64 做一个软连接（方便以后用mysql进入） [root@localhost local]# ln -s mysql-5.7.31-linux-glibc2.12-x86_64/ mysql [root@localhost local]# ll total 0 drwxr-xr-x. 2 root root 6 Aug 12 2018 bin drwxr-xr-x. 2 root root 6 Aug 12 2018 etc drwxr-xr-x. 2 root root 6 Aug 12 2018 games drwxr-xr-x. 2 root root 6 Aug 12 2018 include drwxr-xr-x. 2 root root 6 Aug 12 2018 lib drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64 drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec lrwxrwxrwx. 1 root root 36 Oct 22 17:16 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 7161 31415 129 Jun 2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin drwxr-xr-x. 5 root root 49 Aug 26 19:16 share drwxr-xr-x. 2 root root 6 Aug 12 2018 src 将所有者和所有组改成mysql（之前是root） [root@localhost local]# chown -R mysql.mysql mysql* [root@localhost local]# ll total 0 drwxr-xr-x. 2 root root 6 Aug 12 2018 bin drwxr-xr-x. 2 root root 6 Aug 12 2018 etc drwxr-xr-x. 2 root root 6 Aug 12 2018 games drwxr-xr-x. 2 root root 6 Aug 12 2018 include drwxr-xr-x. 2 root root 6 Aug 12 2018 lib drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64 drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec lrwxrwxrwx. 1 mysql mysql 36 Oct 22 17:16 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 mysql mysql 129 Jun 2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin drwxr-xr-x. 5 root root 49 Aug 26 19:16 share drwxr-xr-x. 2 root root 6 Aug 12 2018 src 添加一个环境变量 [root@localhost local]# vi /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH [root@localhost local]# source /etc/profile.d/mysql.sh 读取 [root@localhost local]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@localhost local]# which mysql 此时mysql可以tab出来（没做此步之前tab不出来） /usr/local/mysql/bin/mysql 建立数据存放目录 [root@localhost ~]# mkdir /opt/data [root@localhost ~]# chown -R mysql.mysql /opt/data [root@localhost ~]# ll /opt/ total 0 drwxr-xr-x. 2 mysql mysql 6 Oct 22 17:27 data [root@localhost ~]# ls /opt/data/ [root@localhost ~]# 此时该目录下没有任何东西 初始化数据库 初始化之后最后面有个临时密码，这个密码是随机的每个人都不一样，要记下来，一会儿登陆要用 [root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/ 2020-10-22T09:33:02.959591Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-10-22T09:33:03.151087Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-10-22T09:33:03.188005Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-10-22T09:33:03.242721Z 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: 95267a2f-1449-11eb-9a41-000c291636de. 2020-10-22T09:33:03.243368Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-10-22T09:33:03.869523Z 0 [Warning] CA certificate ca.pem is self signed. 2020-10-22T09:33:04.056379Z 1 [Note] A temporary password is generated for root@localhost: jbn=rgQ4k96S 把密码写到pass里取，方便记忆 [root@localhost ~]# echo 'jbn=rgQ4k96S' > pass [root@localhost ~]# ls anaconda-ks.cfg pass [root@localhost ~]# cat pass jbn=rgQ4k96S 生成配置文件 配置文件里添加以下内容 [root@localhost ~]# vim /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 mysql]# cd support-files/ [root@localhost support-files]# ls magic mysql-log-rotate mysqld_multi.server mysql.server [root@localhost support-files]# cp mysql.server /etc/init.d/mysqld [root@localhost ~]# ll /etc/init.d/mysqld -rwxr-xr-x. 1 root root 10576 Oct 22 18:29 /etc/init.d/mysqld [root@localhost ~]# vim /etc/init.d/mysqld basedir=/usr/local/mysql 添加这两行 datadir=/opt/data10 启动服务
[root@localhost ~]# service mysqld start Starting MySQL. SUCCESS! [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* 设置开机自动启动 [root@localhost ~]# 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]'. [root@localhost ~]# chkconfig --add mysqld [root@localhost ~]# 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 ~]# ps -ef|grep mysql root 10501 1 0 18:40 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid mysql 10691 10501 0 18:40 pts/1 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 10751 1405 0 18:47 pts/1 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 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* 修改密码 使用whatprovides来查看安装什么包 [root@localhost ~]# yum whatprovides libncurses.so.5 Updating Subscription Management repositories. Unable to read consumer identity This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. Last metadata expiration check: 0:37:05 ago on Thu 22 Oct 2020 06:22:54 PM CST. ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses ...: compatibility libraries Repo : BaseOS Matched from: Provide : libncurses.so.5 安装 ncurses-devel [root@localhost ~]# yum -y -q install ncurses-devel [root@localhost ~]# rpm -qa|grep ncurses ncurses-base-6.1-7.20180224.el8.noarch ncurses-c++-libs-6.1-7.20180224.el8.x86_64 ncurses-devel-6.1-7.20180224.el8.x86_64 ncurses-libs-6.1-7.20180224.el8.x86_64 ncurses-6.1-7.20180224.el8.x86_64 安装ncurses-compat-libs [root@localhost ~]# yum -y install ncurses-compat-libs [root@localhost ~]# rpm -qa|grep ncurses-compat-libs ncurses-compat-libs-6.1-7.20180224.el8.x86_64 登陆 [root@localhost ~]# mysql -uroot -p'jbn=rgQ4k96S' 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.31 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('maqiang123'); Query OK, 0 rows affected, 1 warning (0.00 sec) 用新密码可以连接进来 [root@localhost ~]# mysql -uroot -p'maqiang123' 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.31 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>配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf设置免密登陆
在家目录里写一个这样的文件 [root@localhost ~]# vim .my.cnf [client] user = root password = maqiang123 之前设置的密码 直接免密登陆 [root@localhost ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.31 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>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将无法正常处理连接请求数据库备份方案：
全量备份增量备份差异备份 备份方案特点全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长增量备份增量备份是指在一次全量备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行差异备份备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。语法：
mysqldump [OPTIONS] database [tables …]mysqldump [OPTIONS] --all-databases [OPTIONS]mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…] 恢复表（在里面恢复） //常用的OPTIONS： -uUSERNAME //指定数据库用户名 -hHOST //指定服务器主机，请使用ip地址 -pPASSWORD //指定数据库用户的密码 -P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307 备份整个数据库(全备) 1. 把student数据写到student.sql文件里 [root@localhost ~]# mysqldump -uroot -pmaqiang123 maqiang student > student.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# ls anaconda-ks.cfg pass student.sql 2. 进入数据库删除student [root@localhost ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.31 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 maqiang; 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.00 sec) mysql> show tables; +-------------------+ | Tables_in_maqiang | +-------------------+ | student1 | +-------------------+ 1 row in set (0.00 sec) 3. 读取student.sql mysql> source student.sql; 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) 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, 1 warning (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) Query OK, 0 rows affected (0.01 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, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 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, 1 warning (0.01 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) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) 4. 此时该表已被恢复 mysql> show tables; +-------------------+ | Tables_in_maqiang | +-------------------+ | student | | student1 | +-------------------+ 2 rows in set (0.00 sec) 5. 该表的内容已被恢复 mysql> select * from student; +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | tom | 20 | | 2 | jerry | 80 | | 3 | zs | 60 | | 4 | lisi | 90 | +----+-------+-------+ 4 rows in set (0.00 sec) 备份两张表 备份runtime和linux20200727这两个数据库，且会备份其中的表 [root@localhost ~]# mysqldump --databases runtime linux20200727 > multi_database-20201023.sql [root@localhost ~]# ls anaconda-ks.cfg student-202010230940.sql multi_database-20201023.sql student-202010230942.sql pass student.sql 恢复表（在外面恢复） 查看表 mysql> show tables from maqiang; +-------------------+ | Tables_in_maqiang | +-------------------+ | student | | student1 | +-------------------+ 2 rows in set (0.00 sec) 删除两张表 mysql> drop table maqiang.student; Query OK, 0 rows affected (0.00 sec) mysql> drop table maqiang.student1; Query OK, 0 rows affected (0.01 sec) mysql> show tables from maqiang; Empty set (0.00 sec) mysql> quit Bye 将这两张表恢复到maqiang数据库里 [root@localhost ~]# mysql maqiang < student-202010230942.sql 恢复表时需要指定恢复到哪个库里 此时两张表已恢复 [root@localhost ~]# mysql -e 'show tables from maqiang;' +-------------------+ | Tables_in_maqiang | +-------------------+ | student | | student1 | +-------------------+ 两张表的内容也恢复了 [root@localhost ~]# mysql -e 'select * from maqiang.student;' +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | tom | 20 | | 2 | jerry | 80 | | 3 | zs | 60 | | 4 | lisi | 90 | +----+-------+-------+ 恢复库 先删除这两个库 [root@localhost ~]# mysql -e 'drop database runtime;' [root@localhost ~]# mysql -e 'drop database linux20200727;' [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | maqiang | | mysql | | performance_schema | | sys | +--------------------+ 恢复库时不需要指定恢复到哪儿，但是恢复表时就需要指定恢复到哪个库里 [root@localhost ~]# mysql < multi_database-20201023.sql 此时查看就有这两个库了 [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | linux20200727 | | maqiang | | mysql | | performance_schema | | runtime | | sys | +--------------------+开启MySQL服务器的二进制日志功能
[root@localhost ~]# vim /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 = 10 设置服务器标识符 log-bin=mysql_bin 开启二进制日志功能 重启 [root@localhost ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:对数据库进行完全备份
查看数据库 [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | linux20200727 | | maqiang | | mysql | | performance_schema | | runtime | | sys | +--------------------+ 进行完全备份 [root@localhost ~]# mysqldump -uroot -pmaqiang123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-2020.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# ls all-2020.sql anaconda-ks.cfg 增 mysql> select * from student; +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | tom | 20 | | 2 | jerry | 100 | | 3 | zs | 60 | | 4 | lisi | 90 | +----+-------+-------+ 4 rows in set (0.00 sec) mysql> insert student(name,score) values('wnagwu',87),(('qianliu',67); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+---------+-------+ | id | name | score | +----+---------+-------+ | 1 | tom | 20 | | 2 | jerry | 100 | | 3 | zs | 60 | | 4 | lisi | 90 | | 5 | wnagwu | 87 | | 6 | qianliu | 67 | +----+---------+-------+ 6 rows in set (0.00 sec) 改 mysql> update student set score = 98 where name = 'jerry'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+---------+-------+ | id | name | score | +----+---------+-------+ | 1 | tom | 20 | | 2 | jerry | 98 | | 3 | zs | 60 | | 4 | lisi | 90 | | 5 | wnagwu | 87 | | 6 | qianliu | 67 | +----+---------+-------+ 6 rows in set (0.00 sec)模拟误删数据
删除maqiang这个数据库 [root@localhost ~]# mysql -e 'drop database maqiang;' [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | linux20200727 | | mysql | | performance_schema | | runtime | | sys | +--------------------+刷新创建新的二进制日志
此时查看日志文件，记录了前面的所有操作 [root@localhost ~]# ll /opt/data total 122992 -rw-r-----. 1 mysql mysql 56 Oct 22 17:33 auto.cnf -rw-------. 1 mysql mysql 1676 Oct 22 17:33 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 22 17:33 ca.pem -rw-r--r--. 1 mysql mysql 1112 Oct 22 17:33 client-cert.pem -rw-------. 1 mysql mysql 1676 Oct 22 17:33 client-key.pem -rw-r-----. 1 mysql mysql 1355 Oct 24 14:55 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Oct 24 15:15 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Oct 24 15:15 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Oct 22 17:33 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Oct 24 15:03 ibtmp1 drwxr-x---. 2 mysql mysql 58 Oct 24 14:40 linux20200727 -rw-r-----. 1 mysql mysql 32236 Oct 24 14:55 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 Oct 24 14:40 mysql -rw-r-----. 1 mysql mysql 912 Oct 24 15:13 mysql_bin.000002 -rw-r-----. 1 mysql mysql 19 Oct 24 15:03 mysql_bin.index -rw-r-----. 1 mysql mysql 5 Oct 24 14:55 mysql.pid drwxr-x---. 2 mysql mysql 8192 Oct 22 17:33 performance_schema -rw-------. 1 mysql mysql 1680 Oct 22 17:33 private_key.pem -rw-r--r--. 1 mysql mysql 452 Oct 22 17:33 public_key.pem drwxr-x---. 2 mysql mysql 52 Oct 24 14:40 runtime -rw-r--r--. 1 mysql mysql 1112 Oct 22 17:33 server-cert.pem -rw-------. 1 mysql mysql 1676 Oct 22 17:33 server-key.pem drwxr-x---. 2 mysql mysql 8192 Oct 22 17:33 sys [root@localhost ~]# cat /opt/data/mysql_bin.index 这个mysql_bin.000002里面记录到了前面操作的删除maqiang这一步骤 ./mysql_bin.000002 刷新创建新的二进制日志 [root@localhost ~]# mysqladmin -uroot -pmaqiang123 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# ll /opt/data total 122996 -rw-r-----. 1 mysql mysql 56 Oct 22 17:33 auto.cnf -rw-------. 1 mysql mysql 1676 Oct 22 17:33 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 22 17:33 ca.pem -rw-r--r--. 1 mysql mysql 1112 Oct 22 17:33 client-cert.pem -rw-------. 1 mysql mysql 1676 Oct 22 17:33 client-key.pem -rw-r-----. 1 mysql mysql 1355 Oct 24 14:55 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Oct 24 15:15 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Oct 24 15:15 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Oct 22 17:33 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Oct 24 15:03 ibtmp1 drwxr-x---. 2 mysql mysql 58 Oct 24 14:40 linux20200727 -rw-r-----. 1 mysql mysql 32236 Oct 24 14:55 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 Oct 24 14:40 mysql -rw-r-----. 1 mysql mysql 959 Oct 24 15:21 mysql_bin.000002 -rw-r-----. 1 mysql mysql 154 Oct 24 15:21 mysql_bin.000003 -rw-r-----. 1 mysql mysql 38 Oct 24 15:21 mysql_bin.index -rw-r-----. 1 mysql mysql 5 Oct 24 14:55 mysql.pid drwxr-x---. 2 mysql mysql 8192 Oct 22 17:33 performance_schema -rw-------. 1 mysql mysql 1680 Oct 22 17:33 private_key.pem -rw-r--r--. 1 mysql mysql 452 Oct 22 17:33 public_key.pem drwxr-x---. 2 mysql mysql 52 Oct 24 14:40 runtime -rw-r--r--. 1 mysql mysql 1112 Oct 22 17:33 server-cert.pem -rw-------. 1 mysql mysql 1676 Oct 22 17:33 server-key.pem drwxr-x---. 2 mysql mysql 8192 Oct 22 17:33 sys [root@localhost ~]# cat /opt/data/mysql_bin.index ./mysql_bin.000002 ./mysql_bin.000003 此时生成了一个mysql_bin.000003,里面将记录后面的操作恢复完全备份
恢复 [root@localhost ~]# mysql -uroot -pmaqiang123 < all-2020.sql mysql: [Warning] Using a password on the command line interface can be insecure. 查看数据库 [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | linux20200727 | | maqiang | | mysql | | performance_schema | | runtime | | sys | +--------------------+ 查看表的内容（此时改表里并没有之前增加和修改的内容，是因为我是全量备份之后再进行增加和修改的，所以我恢复是恢复到增改之前的内容。此时若想要恢复到增改之后的内容就要用差量备份恢复，需要用到mysql_bin.000002，这里记录到了增改的操作） [root@localhost ~]# mysql -e 'select * from maqiang.student;' +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | tom | 20 | | 2 | jerry | 100 | | 3 | zs | 60 | | 4 | lisi | 90 | +----+-------+-------+恢复差异备份
检查误删数据库的位置在什么地方(此时我能知道删除maqiang数据库之前是811,删除之后是912) 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 | 10 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 | | mysql_bin.000002 | 123 | Previous_gtids | 10 | 154 | | | mysql_bin.000002 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 219 | Query | 10 | 294 | BEGIN | | mysql_bin.000002 | 294 | Table_map | 10 | 352 | table_id: 124 (maqiang.student) | | mysql_bin.000002 | 352 | Write_rows | 10 | 420 | table_id: 124 flags: STMT_END_F | | mysql_bin.000002 | 420 | Xid | 10 | 451 | COMMIT /* xid=992 */ | | mysql_bin.000002 | 451 | Anonymous_Gtid | 10 | 516 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 516 | Query | 10 | 591 | BEGIN | | mysql_bin.000002 | 591 | Table_map | 10 | 649 | table_id: 124 (maqiang.student) | | mysql_bin.000002 | 649 | Update_rows | 10 | 715 | table_id: 124 flags: STMT_END_F | | mysql_bin.000002 | 715 | Xid | 10 | 746 | COMMIT /* xid=995 */ | | mysql_bin.000002 | 746 | Anonymous_Gtid | 10 | 811 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 811 | Query | 10 | 912 | drop database maqiang | | mysql_bin.000002 | 912 | Rotate | 10 | 959 | mysql_bin.000003;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 15 rows in set (0.00 sec) 使用mysqlbinlog恢复差异备份(此时恢复到删除之前的811,在mysql_bin.000002文件里) [root@localhost ~]# mysqlbinlog --stop-position=811 /opt/data/mysql_bin.000002 |mysql -uroot -pmaqiang123 mysql: [Warning] Using a password on the command line interface can be insecure. 恢复之后就能查看到maqiang数据库，也能查看到增改的内容 [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | linux20200727 | | maqiang | | mysql | | performance_schema | | runtime | | sys | +--------------------+ [root@localhost ~]# mysql -e 'select * from maqiang.student;' +----+---------+-------+ | id | name | score | +----+---------+-------+ | 1 | tom | 20 | | 2 | jerry | 98 | | 3 | zs | 60 | | 4 | lisi | 90 | | 5 | wnagwu | 87 | | 6 | qianliu | 67 | +----+---------+-------+