mysql-备份(二)

前章介绍了MySQL的内部数据结构b+tree，这章讲述mysql的备份

1：环境
ubuntu22.04 LST mysql5.7.42 or win10 mysql5.7.44 (这里图简单直接windows部署)
download:https://downloads.mysql.com/archives/community/

2:install
1> unzip mysql-5.7.44-winx64.zip
eg:D:\soft_install\mysql57\mysql-5.7.44-winx64
2>cd D:\soft_install\mysql57\mysql-5.7.44-winx64\bin
create my.ini
my.ini context

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=D:/soft_install\mysql57\mysql-5.7.44-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:/soft_install\mysql57\mysql_basedir\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODBlog-bin=mysql-bin
#binlog_format 为 binlog 的记录模式，有以下三种
#STATEMENT：每一条会修改数据的 SQL 都会记录到 master 的 binlog 中，slave 在复制的时候，会执行和原来 master 端相同的 SQL。该模式可以减少 binlog 日志量，但是对某些特定的函数进行复制时，slave 端不能正确复制
#ROW：binlog 日志中会记录成每一行数据修改的形式，然后在 slave 端再对相同的数据进行修改。该模式会保证 master 和 slave 的正确复制，但是 binlog 日志量会增加。
#MIXED：前两种模式的结合，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，在 STATEMENT 和 ROW 之间选择一种
binlog_format=mixed
# 给服务器分配一个独一无二的 ID 编号 ; n 的取值范围是 1~2 的 32 次方启用二进制日志功能
server-id = 1
#binlog过期清理时间
expire_logs_days = 10
#单个binlog日志文件大小
max_binlog_size = 200M    
slow_query_log=1
slow-query-log-file=D:\soft_install\mysql57\mysql_basedir\log\mysql-slow.log
long_query_time=3

3>安装 mysql 服务
进入MySQL目录下的bin目录，并执行下面的命令
mysqld install or mysqld.exe --install MySQL57_44 --defaults-file=“D:/soft_install\mysql57\mysql-5.7.44-winx64\bin\my.ini”
执行完显示 Service successfully installed.
4>初始化 MySQL 数据库，执行下面的命令（会生成初始root密码请记住）：
mysqld --initialize --user=mysql --console
执行完显示
[Note] A temporary password is generated for root@localhost: b!M/g4d6W*5a

5>
启动MySQL服务 net start mysql
停止 MySQL服务 net stop mysql

3:进入修改密码
mysql -uroot -p
b!M/g4d6W*5a

修改密码 为123456 自行修改其他密码
mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

4:全量备份与还原
① DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge
② DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：create、alter、drop、truncate、comment、grant、revoke
1>指定数据库开启 binlog
binlog-do-db：指定mysql的binlog日志记录哪个db
（1）binlog_format=STATEMENT时，在用use dbname的格式下，如果dbname没有在binlog-do-db里，DDL和DML语句都不会被记录在binlog里；即使指定具体的DB.table；
（2）在row模式下，在用use dbname的格式下，如果dbname没有在binlog-do-db里，DDL语句都不会被记录在binlog里。即使指定具体的DB.table；DML语句会记录。
（3）在mixed模式下，在用use dbname的格式下，如果dbname没有在binlog-do-db里，DDL、DML语句都不会被记录在binlog里。即使指定具体的DB.table；

binlog_ignore_db 此参数表示不记录指定的数据库的二进制日志

#master配置
[mysqld]
# 指定需要同步的数据库
binlog_do_db = db1
binlog_do_db = db2
binlog_do_db = db3
#不记录指定的数据库的二进制日志
binlog_ignore_db= db11
# 指定不需要同步的数据库(同步到从库去)
replicate-ignore-db = db4
replicate-ignore-db = db5

#参数是在slave上配置
[mysqld]
replicate-do-db = labg1601                   #表示需要复制主服务器上的数据库是labg1601
replicate-ignore-db = mysql                  #表示不从主服务器上复制的数据库mysql（多个可以写多个，不写则以replicate-do-db配置的为准）

2>对所有数据库 完全备份
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql
3>多库备份
mysqldump -u root -p --databases auth mysql > /backup/databases-auth-mysql.sql
4>单库备份
mysqldump -u root -p school > /opt/school.sql
5>备份表(某库下的某张表) mysqldump -u username -ppassword dbname table1 table2…… > backupname.sql

5:恢复
eg: 备份单库 并恢复(testdb)
全备份在 update test1 set num=22 wherw id=1 ;之前执行的，备份时 id=1 num=2

恢复
mysql -uroot -p < d:/temp/testdb.sql

5:增量备份与还原
当前testdb.test1 数据
mysql> select * from test1;
±—±-------±-----+
| id | name | num |
±—±-------±-----+
| 11 | name11 | 11 |
| 12 | name12 | 12 |
| 13 | name13 | 13 |
| 14 | name14 | 14 |
±—±-------±-----+
4 rows in set (0.00 sec)

--master-data=2		表示在备份过程中记录主库的binlog和pos点，并在备份文件中注释掉这一行；
--master-data=1		表示在备份过程中记录主库的binlog和pos点，并在备份文件中不注释掉这一行，即恢复时会执行；
--dump-slave=2		表示在备份过程中，在从库备份，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在备份文件中注释掉这一行；
--dump-slave=1		表示在备份过程中，在从库备份，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在备份文件中不注释掉这一行；
注意:在从库上执行备份时，即--dump-slave=2，这时整个备份过程都是stop io_thread的状态

参考 https://help.aliyun.com/zh/rds/apsaradb-rds-for-mysql/rds-mysql-mysqldump-options

1>查看logs

#查看master binary logs
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)
#查看当前的Binlog日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)
#清理日志
mysql> RESET MASTER;
Query OK, 0 rows affected (0.18 sec)

2>全备份
注意记得 加 --flush-logs 重新生成新的log 便于
D:\soft_install\mysql57\mysql-5.7.44-winx64\bin>mysqldump -u root -p --lock-all-tables --flush-logs --master-data=2 --databases testdb > d:/temp/incremental_backup.sql
Enter password: ******

mysql> show binary logs;
±-----------------±----------+
| Log_name | File_size |
±-----------------±----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 154 |
±-----------------±----------+
2 rows in set (0.00 sec)

#全备完后，插入一条数据 ，更新一条数据 作为增量日志

step2:全量还原
step3:增量还原到420位置
mysql> insert into test1 values(20,‘name20’,20); #还没执行
step4:增量还原到451位置，
mysql> insert into test1 values(20,‘name20’,20); 已经执行完，
mysql> update test1 set num=1212 where id =12; 未开始执行

(1)一般的恢复:备份的二进制日志内容全部恢复
格式:
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码
(2)基于时间点的恢复:便于跳过某个发生错误的时间点实现数据恢复
格式:
从日志开头截止到某个时间点的恢复:
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码
从某个时间点到日志结尾的恢复:
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码
从某个时间点到某个时间点的恢复:
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

(3)基于位置的恢复:可能在同一时间点既有错误的操作也有正确的操作，基于位置 进行恢复更加精准
格式:
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

mysqlbinlog -v /path/mysql-bin.000002 --start-position=’操作 id’ --stop-position=’操作 id’ | mysql -uroot -p

备份与恢复工具
备份方法		备份速度		恢复速度			便捷性						功能		一般用于
cp			快			快			一般、灵活性低						很弱	 	少量数据备份
mysqldump	慢			慢		一般、可无视存储引擎的差异  				一般	   中小型数据量的备份
lvm2快照	快			快		一般、支持几乎热备、速度快				一般	   中小型数据量的备份
xtrabackup	较快		较快 	实现innodb热备、对存储引擎有要求			强大		较大规模的备份

6：如果觉得有用，麻烦点个赞，加个收藏