第1章 MySQL数据库的备份
1.1 备份数据的意义
第一个是保护公司的数据
第二个是网站7*24小时提供服务
1.2 MySQL备份常见两种方式：
1.2.1 逻辑备份
常用工具：mysqldump
就是以SQL语句的形式，将数据导出成文件（SQL语句）
优点：简单、方便、可靠。
导出的数据可以跨平台、跨版本、可以跨软件。
缺点：速度慢、效率不是特别高。
mysqldump是mysql官方自带的逻辑备份工具，还能实现分表分库备份。
企业场景：
1、适合数据量不是特别大的场景，打包前50G以内的数据库数据。
2、跨版本、系统升级时候是用mysqldump迁移数据。
1.2.2 物理备份
简单讲直接对MySQL数据文件拷贝备份，例如：cp，tar。
优点：速度快、效率高
缺点：不容易跨平台、跨版本、跨软件、甚至不能分库分表备份。
如果普通的系统拷贝命令就需要停机备份。
常用工具或方法：
a.linux备份工具（cp，tar），锁表或者停库。
b.开源的热备（基于innodb）工具xtrabackup（课后作业）、不能分表分库。超过50G可选方案
企业应用场景：
1、xtrabackup物理备份工具：全备和增量。
2、超过50G数据量的优选方案。
3、物理备份方案：从库停止SQL线程，cp或打包。
1.2.3 物理备份和逻辑备份的对比
1.3 全量与增量如何结合备份
1.3.1 按天备份
1.3.2 按天备份的特点
1.3.3 按周备份
1.3.4 按周备份的特点
1.3.5 全备
1.3.6 备份最牛逼的层次，就是永远用不上备份
不管是逻辑备份还是物理备份，备份的数据什么时候需要用？
1、恢复数据到测试库的时候。
2、人为通过SQL语句将数据删除的时候。
3、做数据库主从复制的时候。
其他场景几乎都不要使用备份恢复，而是直接使用主从复制功能，将业务切换到从库。
1.4 使用mysqldump进行数据库备份实践
MySQL数据库自带了一个优秀的备份命令，就是mysqldump，它的基本使用如下：
语法：mysqldump -u用户名 -p密码 参数 数据库名 >备份的文件名.sql
特别说明：为了防止密码外泄，我们已经将密码写入配置文件了，具体配置见之前整理的不用密码登录。
1.4.1 不带参数备份单个数据库
不加参数备份名字为oldboy的库
a.查看备份前的数据
mysql> use oldboy
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_oldboy |
+------------------+
| student |
| test |
+------------------+
2 rows in set (0.00 sec)
c.命令行执行备份
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy >/opt/oldboy.sql
Warning: Using a password on the command line interface can be insecure.
b.删除oldboy
mysql> drop database oldboy;
Query OK, 2 rows affected (0.04 sec)
d.恢复
mysql> source /opt/oldboy.sql
mysql> show databases; 可以看到没有回复成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database oldboy; 单独创建oldboy数据库
Query OK, 1 row affected (0.00 sec)
mysql> use oldboy 进入oldboy库
mysql> source /opt/oldboy.sql 恢复数据
mysql> show tables; 成功
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
提示：mysqldump备份的内容就是曾经执行过的sql语句，所不同的是恢复效率，mysqldump把数据写成了一个inster语句，另外多了两行锁表和解锁的操作。
1.4.2 加-B参数备份实践
1.4.2.1 备份时加-B参数
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy >/opt/oldboy_B_.sql
[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/oldboy_B_.sql 恢复时候直接恢复
1.4.2.2 不加-B 恢复
[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/oldboy.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1046 (3D000) at line 22: No database selected
[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database oldboy;"
Warning: Using a password on the command line interface can be insecure.
root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy</opt/oldboy.sql
Warning: Using a password on the command line interface can be insecure.
1.4.2.3 加-B 与不加-B 文件的区别
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy >/opt/oldboy.sql
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy >/opt/oldboy_B_.sql
[root@db02 ~]# vimdiff /opt/oldboy_B_.sql /opt/oldboy.sql
2 files to edit
+ +-- 12 lines: -- MySQL dump 10.13 Distrib 5.6|+ +-- 12 lines: -- MySQL dump 10.13 Distrib 5.
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECK| /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHEC
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN| /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIG
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MOD| /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MO
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_N| /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_
|
-- | --
-- Current Database: `oldboy` | ---------------------------------------------
-- | ---------------------------------------------
| ---------------------------------------------
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldb| ---------------------------------------------
| ---------------------------------------------
USE `oldboy`; | ---------------------------------------------
| ---------------------------------------------
-- | ---------------------------------------------
-- Table structure for table `student` | -- Table structure for table `student`
-- | --
1.4.3 备份多个库
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy mysql >/opt/bak.sql
Warning: Using a password on the command line interface can be insecure. 提示:-B指定多个库
1.4.4 表备份
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy student >/opt/bak.sql
Warning: Using a password on the command line interface can be insecure. 提示：备份oldboy库的student表
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact oldboy student >/opt/bak.sql --compact参数只显示很少的有用输出，适合学习和测试环境调试用
Warning: Using a password on the command line interface can be insecure.
[root@db02 ~]# cat /opt/bak.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT \'0\',
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
1.4.5 多库备份
[root@db02 ~]# vim bak.sql.sh
#!/bin/bash
Name=$(mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e \'show databases;\'|sed 1d)
for name in $Name
do
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B $name|gzip >/opt/bak${name}_$(date +%F).sql.gz
done
1.5 mysqldump里重要参数说明
|
mysqldump重要参数 |
参数说明 |
|
-B，--databases |
会在备份的数据中增加建库（create）及use库的语句，同时可以直接接多个库名，备份多个库 |
|
-A，--all-databases |
备份所有的数据库，同--databases |
|
-d，--no-data |
只备份库表结构（sql语句形式）。没有行数据 |
|
-t，--no-create-info |
只备份表内行数据（sql语句形式）。没有表结构 |
|
-T，--tab=name |
将库表和数据分离成不同的文件，行数据是纯文本，表结构是SQL语句。 |
|
-F，--flush-logs |
刷新binlog日志，生成新的binlog文件，将来增量恢复从这个新binlog文件开始，当备份多个库时，每个库都会刷新一次binlog，如果想只刷一次binlog，可加--lock-all-tables或--master-data参数 |
|
--master-data={1|2} |
在备份结构中增加binlog日志文件名及对应的binlog位置点（即CHANGE MASTER...语句）。值为1时不注释状态，值为2时是注释状态，该参数执行时会打开--lock-all-tables功能，除非有--single-transaction在，使用该参数时会关闭--lock-tables功能。 |
|
-x，--lock-all-tables |
备份时对所有数据库的表执行全局读锁，期间同时禁止--single-transaction和--lock-tables参数功能 |
|
-l，--lock-tables |
锁定所有的表为只读 |
|
--single-transaction 拍快照 |
在备份innodb引擎数据表时，通常用该选项来获取一个一致性的数据快照备份，它的工作原理是设定本次备份会话的隔离级别为REPEATABLE READ，并将整个备份放在 一个事务里，以确保执行本次dump会话时，不会看到其他连接会话已经提交了的数据，即备份开始时刻的数据是什么样，备份出来就是什么样子，也就相当于锁表备份数据，但是这个参数允许备份期间写入数据的，而不是-x锁表后的备份期间无法任何数据写入，启用该参数会关闭--lock-tables |
|
--compact |
只显示很少的有用输出，适合学习和测试环境调试用 |
1.6 binlog 内容
1.6.1 mysql的binlog日志作用是什么
MySQL的binlog日志用来记录mysql内部增删改等对mysql数据库有更新的内容的记录，（对数据库的改动），对数据库查询的语句如show，select开头的语句，不会被binlog日志记录，主要作用是，用于数据库的主从复制以及数据的增量恢复。
1.6.2 binlog是什么？
binlog一个二进制格式的文件，用于记录对数据库的更新日志，例如对数据库库表的更改及表的内容更改的SQL语句会记录到binlog里，但是对库表的查询等不会记录。
1.6.3 binlog对于备份的作用
当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文说的binlog文件。
使用mysqldump备份，一般是对某一时刻的数据的全备，例如：00点进行数据库备份。
假设每天0点对数据库进行备份，那么在两次备份之间就有24小时的数据没有备份，在这期间一定丢失数据，使用mysqldump全量备份恢复也只能恢复到当日00点备份的时刻，但是有了binlog文件，就可以用了两次完整备份之间的数据丢失恢复还原，因为binlog文件里的数据就是写入数据库的数据，使用binlog恢复数据，我们称之为增量数据恢复。
1.6.4 为什么要刷新binlog？
binlog日志刷新（切割）的目的就是确定全备和增量备份的临界点，当全备完成后，全备时刻以前的binlog就无用了（全备这里有这部分数据），但是全备以后到下一次全备前的数据就是十分重要的，因此进行全备时需要找到全备和增量之间的临界点，以方便恢复。
1.6.5 如何开启binlog功能
binlog文件生效需要一个参数：log-bin，加入到my.cnf配置文件中即可。
重启生效
[root@db01 ~]# ll /data/3306/
总用量 808
-rw-rw---- 1 root root 1467 3月 21 19:58 bin.sql
drwxr-xr-x 9 mysql mysql 4096 3月 21 22:52 data
-rw-r--r-- 1 mysql mysql 1884 3月 21 18:46 my.cnf
-rwx------ 1 mysql mysql 1195 2月 27 12:37 mysql
-rw-rw---- 1 mysql mysql 1352 3月 21 18:26 mysql-bin.000001
-rw-rw---- 1 mysql mysql 317 3月 21 18:48 mysql-bin.000002
-rw-rw---- 1 mysql mysql 654800 3月 21 22:57 mysql-bin.000003
-rw-rw---- 1 mysql mysql 84 3月 21 18:48 mysql-bin.index
-rw-rw---- 1 mysql mysql 6 3月 21 18:48 mysqld.pid
-rwx------ 1 mysql mysql 1363 3月 16 09:28 mysql_oldboy
srwxrwxrwx 1 mysql mysql 0 3月 21 18:48 mysql.sock
-rw-r----- 1 mysql mysql 129359 3月 21 22:10 oldboy_3306.err
.index是索引文件
.000001记录更改的SQL文件
1.6.6 如何配置binlog
[root@db02 ~]# mkdir /application/mysql/logs
[root@db02 ~]# chown -R mysql.mysql /application/mysql/logs
开启binlog
编辑/etc/my.cnf
[mysqld]
log_bin = /application/mysql/logs/oldboy-bin
重启:/etc/init.d/mysqld restart
[root@db02 ~]# ll /application/mysql/logs/
total 8
-rw-rw---- 1 mysql mysql 120 Jun 21 12:04 oldboy-bin.000001
-rw-rw---- 1 mysql mysql 42 Jun 21 12:04 oldboy-bin.index
5）使用-F刷新binlog日志
使用-F将从备份后的时刻起重新binlog日志文件，将来增量恢复从这个新日志文件开始即可。
binlog文件切割的条件：
1、数据库重启，自动切割新文件。
2、mysqldump -F
3、binlog文件达到1.1G，自动切割
4、人为配置切割及调整
删除binlog日志方法
1、设置参数自动删除
expire_logs_days = 7 #删除7天前的日志
2、从头删到指定的文件位置
数据库执行
purge binary logs to \'mysql-bin.00004\';
1.7 特殊记录binlog位置的参数
mysqldump里提供了一个参数，允许我们不用刷新binlog，也可以找到全量和增量的临界点，就是--master-data参数，使用这个参数备份后，在备份的文件对应的SQL语句里会添加CHANGE MASTER语句及binlog文件及位置点信息。
当--master-data=1时，备份结果为可执行的“CHANGE MASTER...”语句，当--master-data=2时，备份结果为可执行的“--CHANGE MASTER...”语句，“--”在SQL语句里为注释的意思。--master-data参数除了确定增量恢复和全备之间的临界点外，做主从复制时作用更大。
1.8 生产场景不同引擎mysqldump备份命令
1） innodb引擎企业生产备份可使用命令
2） mysqldump -B -R --triggers --master-data=2 oldboy|gzip >/opt/alL_$(date +%F).sql.gz
mysqldump -A -B --master-data=2 --single-transaction |gzip >/opt/all.sql.gz
提示： --single-transaction是innodb引擎专有备份参数，优势就是备份期间数据依然可以写入数据库。--master-data作用：
基于事务引擎:不用锁表就可以获得一致性的备份.
99% 使用innodb事务引擎.ACID四大特性中的隔离性
--single-transaction
2）适合多引擎混合（例如：myisam与innodb混合）企业生产备份命令
mysqldump -A -B --master-data=2 |gzip >/opt/alL_$(date +%F).sql.gz
提示：这个备份的优势会锁表，备份期间影响数据写入，使用--master-data会自动开启-x锁表参数。
说明：如果数据库有特殊功能，例如：存储过程和函数数据就加上-R，如果有触发器数据就加上--triggers，如果考虑切割binlog日志也可以加-F，如果不想备份所有库，也可以取消-A，指定多个库名备份。
[root@db01 ~]# mysqldump --master-data=2 -B -x oldboy>/opt/oldboy.sql
[root@db01 ~]# mysqldump --master-data=2 -B -x oldboy|gzip>/opt/oldboy.sql.gz
[root@db01 ~]# ls -l /opt/
total 656
-rw-r--r-- 1 root root 2051 Mar 17 14:44 bak.sql
-rw-r--r-- 1 root root 2204 Mar 17 16:36 oldboy.sql
-rw-r--r-- 1 root root 869 Mar 17 16:36 oldboy.sql.gz
drwxr-xr-x. 2 root root 4096 Mar 26 2015 rh
1.8.1 --master-data={1|2} 告诉你备份后时刻的binlog位置
2 注释
1 非注释,要执行(主从复制)
[root@db02 logs]# sed -n \'22p\' /opt/t.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=\'oldboy-bin.000005\', MASTER_LOG_POS=344;
[root@db02 logs]# mysqldump -B --master-data=2 oldboy >/opt/t.sql
1.8.2 解压:
zcat t.sql.gz >t1.sql
gzip -d t.sql.gz #删压缩包
[root@db02 ~]# zcat /opt/oldboy.sql.gz
-- MySQL dump 10.13 Distrib 5.6.34, for Linux (x86_64)
--
-- Host: localhost Database: oldboy
-- ------------------------------------------------------
-- Server version 5.6.34-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=\'+00:00\' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=\'NO_AUTO_VALUE_ON_ZERO\' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE=\'mysql-bin.000004\', MASTER_LOG_POS=120;
表示从mysql-bin.000004的 MASTER_LOG_POS=120;开始切割binlog
查看binlog文件
[root@db02 ~]# mysqlbinlog /data/3306/binlog/mysql-bin.000004
第2章 MySQL数据库恢复
2.1 使用gzip压缩备份数据库实践
指定压缩命令gzip压缩备份MySQL数据
[root@db01 ~]# mysqldump -S /data/3306/mysql.sock -B mysql|gzip >/opt/mysql_gzip_B.sql.gz
[root@db01 ~]# mysqldump -S /data/3306/mysql.sock -B mysql >/opt/mysql_gzip_B.sql
[root@db01 ~]# ll /opt/
总用量 1628
-rw-r--r-- 1 root root 650671 3月 16 16:16 3306.sql
-rw-r--r-- 1 root root 178497 3月 22 10:37 3306.sql.gz
-rw-r--r-- 1 root root 650391 3月 22 11:19 mysql_gzip_B.sql #没有压缩的备份数据
-rw-r--r-- 1 root root 178358 3月 22 11:19 mysql_gzip_B.sql.gz #压缩过的数据备份
drwxr-xr-x. 2 root root 4096 3月 26 2015 rh
提示：同样的数据从没有备份的650391字节，到压缩备份的178358字节，缩小了很多。
2.2 针对压缩的备份数据恢复说明
方法1：使用gzip解压（会删除压缩文件）
[root@db01 ~]# gzip -d /opt/mysql_gzip_B.sql.gz
[root@db01 ~]# mysql -S /data/3306/mysql.sock </opt/mysql_gzip_B.sql
方法2：使用gzip解压（但不会删除压缩文件）
[root@db01 ~]# !mysqldump ##使用此方法会调出最近一次备份的命令
mysqldump -S /data/3306/mysql.sock -B --master-data=2 --single-transaction mysql|gzip >/mnt/mysql.sql.gz
[root@db01 ~]# gzip -cd /mnt/mysql.sql.gz >/mnt/mysql1.sql ##特殊解压方法
[root@db01 ~]# ll /mnt/
总用量 812
-rw-r--r-- 1 root root 650721 3月 22 23:20 mysql1.sql
-rw-r--r-- 1 root root 178465 3月 22 23:18 mysql.sql.gz
[root@db01 ~]# mysql -S /data/3306/mysql.sock </mnt/mysql1.sql
方法3：使用gunzip解压（不会删除压缩包）
[root@db01 ~]# gunzip -cd /mnt/mysql.sql.gz >/mnt/mysql2.sql
[root@db01 ~]# ll /mnt/mysql2.sql
-rw-r--r-- 1 root root 650721 3月 22 23:23 /mnt/mysql2.sql
[root@db01 ~]# mysql -S /data/3306/mysql.sock </mnt/mysql1.sql
或者
gunzip </mnt/mysql.sql.gz |mysql -S /data/3306/mysql.sock
方法4：使用zcat读取压缩包数据
[root@db01 ~]# zcat /mnt/mysql.sql.gz >/mnt/mysql3.sql
[root@db01 ~]# mysql -S /data/3306/mysql.sock </mnt/mysql1.sql
2.3 恢复数据库实践
2.3.1 数据库恢复基本事项
1、数据恢复和字符集关联很大，如果字符集不正确会导致恢复的数据乱码。
2、mysql命令以及source命令恢复数据库原理就是把文件的SQL语句，在数据库里重新执行的过程。
2.3.2 利用source命令恢复数据库
mysql 登录用它
innodb引擎
准备环境
mysqldump -B -R --triggers --master-data=2 oldboy|gzip >/opt/alL_$(date +%F).sql.gz
[root@db02 opt]# mysql -e "drop database oldboy;"
[root@db02 opt]# mysql -e "show databases;;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldgirl |
| performance_schema |
+--------------------+
[root@db02 opt]# mysql </opt/alL_2017-06-22.sql
[root@db02 opt]# mysql -e "show databases;;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldgirl |
| performance_schema |
+--------------------+
1、登录数据库
mysql -S /data/3306/mysql.sock
2、选择想要恢复的数据库
mysql> use mysql
3、使用source命令恢复数据（source 备份文件所在路径）
mysql> source /mnt/mysql1.sql
2.3.3 利用mysql命令恢复数据
mysql -S /data/3306/mysql.sock </mnt/mysql1.sql
不加-B的恶果。
create database oldboy;
mysql oldboy </opt/alL_2017-06-22.sql
2.3.4 利用文本插入数据
假定开发人员让我们插入数据到数据库（可能是邮件发给我们的，内容可能是字符串）
或者下面的文件
sql文件里面没有use db这样的字样时，在导入时就要指定数据库名了
[root@db01 opt]# cat /opt/new.sql
set names utf8;
use management
insert into test(name) values(\'小陶\');
环境创建
mysql> create database oldboy;
Query OK, 1 row affected (0.01 sec)
mysql> use oldboy
Database changed
mysql> CREATE TABLE test (
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> PRIMARY KEY (id)
-> ) ;
Query OK, 0 rows affected (0.10 sec)
[root@db01 ~]# cat /mnt/new.aql
set names utf8;
use oldboy
insert into test(name) values(\'小陶\');
[root@db01 ~]# mysql -S /data/3306/mysql.sock </mnt/new.aql
[root@db01 ~]# mysql -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+--------+
| id | name |
+----+--------+
| 1 | 小陶 |
+----+--------+
提示：如果UTF8数据库，人工编辑的SQL文件，格式建议为“UTF8没有签名”格式
2.4 利用mysql -e参数查看mysql数据
mysql命令提供了一个功能，可以让使用者无需登录数据库，在Linux命令行就可以执行SQL语句。
[root@db01 ~]# mysql -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+--------+
| id | name |
+----+--------+
| 1 | 小陶 |
+----+--------+
2.5 利用mysql -e查看系统运行状态
[root@db01 ~]# mysql -S /data/3306/mysql.sock -e "show global status"|head -5
Variable_name Value
Aborted_clients 0
Aborted_connects 5
Binlog_cache_disk_use 0
Binlog_cache_use 1
[root@db02 ~]# mysql -e "show global status like \'%uptime%\';"
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Uptime | 567 |
| Uptime_since_flush_status | 567 |
+---------------------------+-------+
2.6 利用mysql -e参数不重启数据库修改数据库参数
不重启数据库修改数据库参数还能生效
[root@db01 opt]# mysql -e "show variables;"|grep key_buffer
key_buffer_size 8388608
[root@db01 opt]# mysql -e "set global key_buffer_size = 1024*1024*16;"
[root@db01 opt]# mysql -e "show variables;"|grep key_buffer
key_buffer_size 16777216
不重启数据库更改数据库参数小结：
1、set global key_buffer_size = 1024*1024*16; #及时生效，重启mysql失效。
2、配置文件也要增加或修改，编辑/etc/my.cnf，修改key_buffer_size = 16M
2.7 mysql常用命令小结
show processlist; #查看数据库里正在执行的SQL语句，可能无法看全完整SQL语句。
show full processlist; #查看正在执行的完整SQL语句，完整显示。
set global key_buffer_size = 1024*1024*16; #不重启数据库调整数据库参数，直接生效，重启失效。
show variables; #查看数据库的配置参数信息，例如：my.cnf里参数的生效情况。
show variables like \'%log_bin%\';
kill ID #杀掉SQL线程命令，ID为线程号。
show session status; #查看当前会话的数据库状态信息。
show global status; #查看整个数据库运行状态信息，很重要，要分析并做好监控。
show engine innodb status; #显示innodb引擎的性能状态（早期版本 show innodb status）
计算一天之内；MySQL数据库有多个insert，delete语句有没有好方法？
1、定时任务每天0点，show global status;按天取出对比
[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e \'show global status;\'|egrep \'Com_insert|Com_delete\'
Com_delete 0
Com_delete_multi 0
Com_insert 12
Com_insert_select 0
2、按天分析binlog日志，获取数据库不同语句的频率
2.8 mysqladmin命令相关
mysqladmin password oldboy123 #设密码
mysqladmin -uroot -poldboy123 password oldboy #修改密码
mysqladmin -uroot -poldboy123 status #查看状态，相当于show status
mysqladmin -uroot -poldboy123 -i 1 status #每秒查看一次状态
mysqladmin -uroot -poldboy123 extended-status #等同show global status
mysqladmin -uroot -poldboy123 flush-logs #切割日志
mysqladmin -uroot -poldboy123 processlist #查看执行的SQL语句信息
mysqladmin -uroot -poldboy123 processlist -i 1 #每秒查看一次执行的SQL语句
mysqladmin -uroot -p\'oldboy\' shutdown #关闭mysql服务
mysqladmin -uroot -p\'oldboy\' variables #相当于show variables
修改密码
[root@db02 ~]# mysqladmin -uroot -poldboy1234 password oldboy123 -S /data/3306/mysql.sock
[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
2.9 mysql命令常用参数总结
|
mysql命令常用参数 |
说明 |
|
-u |
指定数据库用户 |
|
-p |
指定数据库密码 |
|
-S |
指定数据库socket文件 |
|
-h |
指定数据库主机，默认localhost |
|
-P |
指定数据库端口，默认3306 |
|
-e |
不登录数据库执行数据库命令 |
更多命令信息，可执行mysql --help查看
2.10 企业案例：mysql sleep线程过多的问题案例
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 89 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> kill 89;
慢查询堵了数据库，mysql> kill 89; 89是ID，kill（insert,update）可能要丢数据。
解决办法：
mysql> show variables like \'%_timeout%\';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.00 sec)
set global wait_timeout = 60;
set global interactive_timeout = 60;
mysql> show global variables like \'interactive_timeout\';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| interactive_timeout | 60 |
+---------------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like \'wait_timeout\';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 60 |
+---------------+-------+
1 row in set (0.00 sec)
企业案例：mysql sleep线程过多的问题案例。
1、配置文件里修改：
[mysqld]
interactive_timeout = 120 此参数设置后wait_timeout自动生效。
wait_timeout = 120
2、其他方法：
1.PHP程序中，不使用持久链接，即使用mysql_connect而不是pconnect（JAVA调整连接池）。
2.PHP程序执行完毕，应该显式调用mysql_close。
3.逐步分析MySQL的SQL查询及慢查询日志，找到查询过慢的SQL,优化之。
2.11 mysqlbinlog增量恢复工具
mysqlbinlog工具的作用是解析mysql的二进制binlog日志内容，把二进制日志解析成可以在MySQL数据库里执行的SQL语句。
2.11.1 MySQL的binlog日志是什么？
mysql数据目录下的如下文件就是mysql的binlog日志
[root@db01 ~]# ll /data/3306|grep mysql-bin*
-rw-rw---- 1 mysql mysql 651797 3月 22 11:50 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1306 3月 22 23:53 mysql-bin.000002
提示：必须要打开log_bin才能生成上述文件。
2.11.2 解析指定库的binlog日志
利用mysqlbinlog -d参数解析指定库的binlog日志
[root@db01 3306]# mysqlbinlog -S /data/3306/mysql.sock -d mysql mysql-bin.000002 -r bin.sql #-d指定库，-r指定生成的文件
[root@db01 3306]# grep -i insert bin.sql #过滤内容，看到了曾经恢复的SQL语句
结论：mysqlbinlog可以指定-d实现分库导出binlog，如果使用-d参数，那更新数据时，必须要有use库名，才能分出指定库的binlog，例如，写入数据库的语句必须是下述写法：
use oldboy;
insert into test values(1,\'oldboy\')
下面的这种写法就不行：
inster into oldboy.test values(2,\'oldgirl\')
分库备份：
mysqlbinlog -S /data/3306/mysql.sock -d kkk mysql-bin.000002 -r a.sql
分表备份：
mysqlbinlog -S /data/3306/mysql.sock -d kkk mysql-bin.000002|grep -w test >b.sql
2.11.3 mysqlbinlog常用参数说明
|
mysqlbinlog命令常用参数 |
参数说明 |
|
-d, --database=name |
根据指定库拆分binlog（拆分单表binlog可通过SQL关键字过滤） |
|
-r, --result-file=name |
指定解析binlog输出SQL语句的文件 |
|
-R, --read-from-remote-server |
从MySQL服务器读取binlog日志，是下面参数的别名。read-from-remote-master=BINLOG-DUMP-DTDS |
|
-j, --start-position=# |
读取binlog的起始位置点，#号是具体的位置点 |
|
--stop-postion=# |
读取binlog的停止位置点，#号是具体的位置点 |
|
--base64-output=decode-rows |
解析ROW级别binlog日志的方法，例如：mysqlbinlog --base64-output=decode-rows -v mysql-bin.000016 |
|
--start-datetime=name |
读取binlog的起始位置点。name是具体的时间格式2017-06-22 11:25:56 |
|
--stop-datetime=name |
读取binlog的停止位置点。name是具体的时间格式2017-06-22 11:25:56 |
2.11.4 按照位置截取binlog内容
1）按照位置截取binlog内容的优点是精确，但是要花费时间寻找位置，例如：
a.截取指定起始点
mysqlbinlog oldboy-bin.000009 --start-position=8826 --stop-position=8975 -r pos.sql
b.指定开始位置，不指定结束位置，不指定的话默认到结束
mysqlbinlog oldboy-bin.000009 --start-position=8826 -r pos.sql
c.指定结束位置，不指定开始，默认找最初
mysqlbinlog oldboy-bin.000009 --stop-position=8975 -r pos.sql
2）按照时间截取binlog内容
mysqlbinlog mysql-bin.000020 --start-datetime=\'2014-10-16 17:14:15\' --stop-datetime=\'2014-10-16 17:15:15\' -r time.sql
具体规则同上
提示：所谓的时间点就是mysqlbinlog解析文件里的不同行行首的“#170323 1:07”标识的数据
第3章 老男孩增量恢复项目实践：
备份最牛逼的层次，永远都用不上备份。
什么时候会使用备份的数据？
1、恢复数据到测试库的时候。
2、人为通过SQL语句将数据删除的时候。*****
3、做主从复制的时候。
其它场景都不要使用备份恢复，而是直接使用主从复制功能，将业务切换到从库。
企业案例：人为删除了数据库，怎么恢复？
1、具备什么条件才能恢复数据库？
a.全量备份（mysqldump）
b.全量备份以后的所有binlog增量日志。
增量恢复企业案例:
条件:
1.具备全量备份（mysqldump）。
2.除全量备份以外，还有全量备份之后产生的的所有binlog增量日志。
drop database oldboy;
CREATE DATABASE oldboy;
USE `oldboy`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES (1,\'oldboy\'),(2,\'oldgirl\'),(3,\'inca\'),(4,\'zuma\'),(5,\'kaka\');
==================================================================
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.01 sec)
==================================================================
1、准备环境：
mkdir /data/backup -p
date -s "2017/06/22"
mysqldump -B --master-data=2 --single-transaction oldboy|gzip>/data/backup/oldboy_$(date +%F).sql.gz
mysql -e "use oldboy;insert into test values(6,\'bingbing\');"
mysql -e "use oldboy;insert into test values(7,\'xiaoting\');"
mysql -e "select * from oldboy.test;"
2、模拟误删数据：
date -s "2017/06/22 11:40"
mysql -e "drop database oldboy;show databases;"
出现问题10分钟后,发现问题,删除了数据库了.
3、开始恢复准备
采用iptables防火墙屏蔽所有应用程序的写入。
[root@oldboy ~]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP #<==非172.16.1.51禁止访问数据库3306端口。
cp -a /application/mysql/logs/oldboy-bin.* /data/backup/
zcat oldboy_2017-06-22.sql.gz >oldboy_2017-06-22.sql
sed -n \'22p\' oldboy_2017-06-22.sql
mysqlbinlog -d oldboy --start-position=339 oldboy-bin.000008 -r bin.sql
需要恢复:
1.oldboy_2017-06-22.sql
2.bin.sql
grep -i drop bin.sql
sed -i \'/^drop.*/d\' bin.sql
4、开始恢复全备。
[root@db02 backup]# mysql <oldboy_2017-06-22.sql
[root@db02 backup]# mysql -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldgirl |
| performance_schema |
+--------------------+
[root@db02 backup]# mysql -e "use oldboy;select * from test;"
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5、开始恢复增量
[root@db02 backup]# mysql oldboy <bin.sql
[root@db02 backup]# mysql -e "use oldboy;select * from test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
| 6 | bingbing |
| 7 | xiaoting |
+----+----------+
恢复完毕。
调整iptables允许用户访问.
