mysq的备份

Mysql备份【数据要防止丢失】
引⾔
为什么需要备份？
1、现代的多数服务多数系统⾼可⽤，数据⽆价，丢失会带来难以承担的损失。
2、⼀套完整的备份机制可以使得系统遇到不可抗⼒的情况时将数据的修复代价降到最低甚⾄零损失。
3、对于任何项⽬都应该具备定期备份数据的好习惯，⽆论是否为⽣产项⽬。

备份形式
1、物理备份：⽐如我们使⽤硬盘拷⻉⾃⼰的重要数据，灵活性⼀般，安全性较⾼。
2、云服务器备份：将数据传到第三⽅的云数据库进⾏保管，维护成本⼀般，安全性取决于第三⽅维护商家的质量。
3、⾃建服务器备份：开销⽐较⼤，但是数据安全性和稳定性都是最⾼的，也可以即时进⾏物理备份，可操作性强。

备份有三种⽅式：

1、热备：正常运⾏备份。此时数据库可读可写。
2、冷备：停机备份。数据库⽆法进⾏任何操作。
3、温备：数据库只读。数据库可⽤性弱于热备，备份期间，数据库只能进⾏读操作，不能进⾏写操作

逻辑备份：输出或者 SQL 语句，可以供技术⼈员阅读
物理备份（裸⽂件）：备份数据库底层⽂件，但是不可阅读
完全备份：备份完整数据
增量备份：备份数据差异
⽇志备份：binlog 备份
mysqldump：逻辑备份，热备份，全量Mysql备份【完成60%左右】 - 2
⼩结：
备份的基本形式：从备份的形式来看，可以使⽤物理磁盘备份，也可以依赖于三⽅服务商的服务器或者⾃建的服务器进⾏备份，⽽从备
份数据状态来看，可以存在热备，冷备和温备，这⾥需要⼩⼼温备这个概念。
备份⼯具⽐较常⽤的有两种：mysqldump 和 xtrabackup，这两种⼯具都需要重点掌握基础的操作使⽤，实践多余理论。
OUTFILE 命令备份
怎么来？
使⽤前提
特点：
缺陷
如何使⽤？
xtrabackup：物理，热，全量 + 增量备份
mysql ⾃带的命令，也是 mysql 的关键字，可以说是最原始的逻辑备份⽅式。
要知道⽹站的绝对路径，可以通过报错信息、phpinfo 界⾯、404 界⾯等⼀些⽅式知道
要有 file 的读写权限，建议给相关⽂件夹执⾏ chmod -R /xxx/xxx
1.
2.
3. 对⽬录要有写权限，⼀般 image 之类的存放图⽚的⽬录有写权限
还要注意的是：写的⽂件名⼀定是在⽹站中不存在的，不然也会不成功！
简单的导出 SQL 结果，主要⽤于临时需要⼀些
Mysql 原⽣命令⽀持的导出⽅式，效率⾼
命令简单，操作⽅便，可以导出⼀致性视图
1.
2.
3.
导出的格式较为简陋，通常需要对于数据进⾏⼆次处理才能正常使⽤。
只能导出 SQL 执⾏结果，没有办法将导出后的数据⽤于还原
通过上⾯的介绍可以看出 OUTFILE 这个命令只能⽤于⽇常开发的场景下需要测试数据可以临时使⽤导出，不能作为热备的主要⼯具，
但是这个命令对于 MysqlDump 来说是启发性的。
前提条件：在具体的导出之前，我们需要了解 Mysql 导出的具体路径，使⽤下⾯的语句检查⼀下当前的安全⽂件导出前缀，注意结果
为 NULL 在 Mysql5.7 是存在影响的，和 Mysql5.6 的结果不同，还需要注意的⼀点是，个⼈使⽤的 Mac 系统，⽂件管理系统和 Linux ⼤
体⼀致，但是其实有很多权限等等细节问题。
SQL
show variables like ‘%secure%’
– secure_file_priv NULL
为什么说使⽤ secure_file_priv 为 NULL 是存在影响的？
解答：
Mysql5.7 的版本中，在 Mysql 启动的时候，如果使⽤了这个参数的配置则会 限制你可以使⽤ LOAD DATA INFILE 加载⽂件的范
围，意味着如果想要导出必须是在这个配置指定的⽬录下⾯，下⾯是此配置对应的变化：

1. secure_file_priv 为 NULL 时，表示限制 mysqld 不允许导⼊或导出。
2. secure_file_priv 为 /tmp 时，表示限制 mysqld 只能在/tmp ⽬录中执⾏导⼊导出，其他⽬录不能执⾏。
3. secure_file_priv 没有值时，表示不限制 mysqld 在任意⽬录的导⼊导出。Mysql备份【完成60%左右】 - 3
   上⾯啰嗦⼀⼤堆之后，下⾯是最终导出的结果，可以看到默认只使⽤了空格分隔，并且格式⽐较乱：
   完成环境的准备⼯作之后，我们需要搭建基本的操作环境，⽐如新建数据库或者表，这⾥依然使⽤了 sakira 数据库，我们可以使⽤下⾯的
   命令进⾏尝试导出，⽐如下⾯的语句中我们将。
   SQL
   注：Sakila 数据库在 Mysql 官⽅的 example 中可以直接下载，导⼊的过程这⾥直接忽略了。
   select * from payment into outfile ‘/Users/xxx/xxx/a.csv’
   但是实际执⾏过程中会出现如下的报错，从报错信息可以看到这⾥是因为 secure_file_priv 为 NULL 的问题：
   SQL
   由于个⼈使⽤的是 macos 系统，设置起来⽐较麻烦，这⾥也不啰嗦具体细节了，主要讲⼀下处理思路：
   设置⼀个⾃定义的配置 my.ini ⽂件并且放到 /etc 的⽬录下⾯，在⽂件结尾设置此参数： secure_file_priv=/Users/xxxx/xxx/
   然后 ：x 保存（注意⽤ sudo vim my.ini ），导出路径建议选的当前 /User/xxx 家⽬录，⽅便导出之后⽴⻢打开。（/路径不太安
   全）
   重启 Mysql 或者重启电脑，连接继续执⾏上述命令后发现： PermissionError: [Errno 13] Permission denied ，明显是 macOs
   的权限问题，通过命令 chmod 777 导出文件夹/* 可以给整个⽂件夹开放权限（根⽬录千万不要这样做）。
   如果出现重名⽂件，使⽤命令⼀样报错，并且提示导出⽂件已经存在，切记每次执⾏前删除重名⽂件。
   1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, Time: 0.00400
   MacOs 使⽤ brew 安装会发现没有 my.ini ⽂件，于是个⼈从⽹上翻了份能⽤的，可以直接在下⾯链接提供的⽂件尾部添加
   secure_file_priv=/Users/xxxx/xxx/ 即可，省去⼤伙的时间，当然是针对我这种蛋疼的 MacOs 系统来说的。其他操作系统应该可以
   直接找到相关配置⽂件。
   链接: https://pan.baidu.com/s/1bM3cQtaXMl3ZGNgQRzhEMA
   提取码: phkg
   插曲：Maxos 使⽤ homebrew 安装版本的启动和关闭：
   关闭： sudo pkill -9 mysql
   启动： cd /usr/local/mysql/support-file/mysql.server start（stop关闭）Mysql备份【完成60%左右】 - 4
   可以看到 outfile 使⽤也是⽐较好记的，同时下⾯是 outfile 的⼀些使⽤参数，通过这些参数可以⾃由配置：
   SQL
   简单的使⽤案例：
   SQL
   从结果可以看出 outfile 只能⽤作⼀些简单的场景的导出操作：
   SELECT … INTO OUTFILE ‘file_name’
   [CHARACTER SET charset_name]
   [export_options]
   export_options:
   [{FIELDS | COLUMNS}
   [TERMINATED BY ‘string’]
   [[OPTIONALLY] ENCLOSED BY ‘char’]
   [ESCAPED BY ‘char’]
   ]
   [LINES
   [STARTING BY ‘string’]
   [TERMINATED BY ‘string’]
   ]
   select * from payment into outfile ‘/Users/xxx/xxx/a.csv’ FIELDS terminated by ','Mysql备份【完成60%左右】 - 5
   MysqlDump 命令使⽤
   「知识点」
   outfile 的痛点
   mysqldump 特点
   到此我们只需要简单了解这个命令即可，为下⾯了解 MysqlDump 打下简单的原理基础。
   mysqldump 的命令可以看作是 outfile 命令的扩展，作为⼀个⼗分重要的
   outfile 的痛点，或者说 mysqldump 改进点
   mysqldump 特点
   mysqldump 的操作（实战案例）
7. mysqldump 的增量备份如何实现（原理）
   binlog 忠实记录 mysql 变化
   mysqldump 只能全量备份，binlog 作为增量备份
   关键：mysqldump 备份，切换新的 binlog ⽂件。
   采⽤从零开始还原

只要简单操作⼀下 outfile 命令就会发现 outfile 有下⾯这⼏个明显的缺点：
只能导出数据，很难把数据再次导⼊
只能导出数据，⽆法做逻辑备份，也就是备份 SQL 逻辑
导出形式单⼀，通常只能导出 excel。
所以其实可以看到 mysqldump 其实就是解决了 outfile 的很多现实问题，并且在此基础上改进让它更加简单好⽤。
Mysql 官⽅内置命令，内置实现可以避开很多没有必要的问题。
⽀持远程备份，可以⽣成多种格式的⽂件
与存储引擎⽆关，可以在多种存储引擎下进⾏备份恢复，对 innodb 引擎⽀持热备，对 MyISAM 引擎⽀持温备（施加表锁）Mysql备份【完成60%左右】 - 6
如何学习？
备份所需权限：
如果需要备份数据⾄少需要 SELECT 权限
备份视图需要 SHOW VIEW 权限
备份触发器需要 TRIGGER 权限
如果不使⽤参数 ，需要 LOCK TABLE 权限进⾏锁表
（从 MySQL 8.0.21 开始）如果不使⽤ --no-tablespaces 选项则需要 PROCESS 权限。
如果需要导⼊备份数据，则需要包含执⾏语句的所有权限，⽐如 CREATE、ALTER、DELETE 权限
实践：备份 Sakila-db
免费
官⽅开发的当然是官⽅学习最好啦，注意链接提供的是 Mysql8.0 的版本，需要根据⾃⼰当前使⽤的版本阅读，另外不需要去记忆，在需
要的时候翻出来看看然后看看官⽅⽂档即可：
MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup…
dev.mysql.com
任何⼯具类的东⻄适合使⽤的适合查阅，死记硬背是没有意义的，最后会发现只需要记住常⽤的⽅式即可。
–single-transaction
我们可以尝试备份⼀下官⽅提供的 example ⽐如 sakila：
备份⼀个数据库
SQL
备份多个数据库到⼀个 sql ⽂件
SQL
将数据从⼀个服务器备份到另⼀个服务器（常⽤）
SQL
如果使⽤ InnoDB 的存储引擎，Mysql 有⼀种在线备份的⽅法：
SQL
– 第一种备份方法
./mysqldump -uroot -pxxxxxx sakila > /Users/xxx/xxx/xxx/xxxx/backup-file.sql
– mysqldump: [Warning] Using a password on the command line interface can be insecure.
./mysqldump --databases sakila sakila-db -uroot -xxx > /Users/xxx/xx/xxxx/xxx/backup-file_bk2.sql
– 未能成功实验
mysqldump --opt db_name | mysql --host=remote_host -C db_name
– 参数解释
– --all-databases 所有数据库
– --single-transaction RR级别的备份，也就是确保一致性的视图(Innodb存储引擎)
– --master-data 将二进制日志文件的名称和位置写到输出端
– 如果不是InnoDB,需要使用下面的参数：
– 1. --lock-all-tables 使用FTWRL锁锁住所有表（MyISAM）
– 2. --lock-tables 使用READ LOCAL锁住当前库的表（MyISAM）
mysqldump -uroot -pxx --all-databases --master-data --single-transaction > /Users/xxx/xxx/all_databases.sqlMysql备份【完成60%左右】 - 7
还原数据库
SQL

./mysqldump -uroot -pxxxxxx sakila < /Users/xxx/xxx/xxx/xxxx/backup-file.sql
– 第二种还原备份方法
– 1. 使用具备相关权限的用户名和密码登陆连接到mysql服务器 mysql -uroot -proot
– 2. source /xxx路径/xx.sql文件 source xxx.sql