mysql备份实战

# mysql备份实战 ##全量备份 全量数据，指的是某一整个数据库（如kings）中所有的表、以及表数据，进行备份。 例如备份所有数据库、以及所有数据，上面也讲了mysqldump的全量备份操作。 “` 备份所有库 mysqldump -uroot -pwww.yuchaoit.cn -S /data/3306/mysql.sock -F -A -B |gzip >/server/backup/mysqlbak_$(date+%F).sql.gz 备份某个库 mysqldump -uroot -pwww.yuchaoit.cn -S /data/3306/mysql.sock -F -B oldboy|gzip >/server/backup/mysqlbak_$(date+%F).sql.gz -F 备份前，刷新binlog日志，用于增量恢复 -B 备份指定的某些数据库 -A 备份所有库、表、数据 “` ##增量备份 ### binlog binlog是mysql一大重点，Binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库库表和更改表内容的SQL语句都会记录到binlog里，但是对库表等内容的查询则不会记录到日志中。 ###binlog的作用 当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文所说的binlog文件。 使用mysqldump备份时，例如我们一般会写crontab，例如夜里0点整，进行数据库备份。 ###使用binlog的背景 问题是，每天只有到0点整才进行备份，那未备份之前，也就是两次数据库备份的间隔是24小时。 一旦在这个期间发生故障，那么数据此时就是丢失的，即使使用mysqldump的备份，也只能找回当日0点的数据。 使用binlog功能，可以解决该问题 使用binlog文件，可以将两次完整备份间隔之间的数据还原 因为binlog文件的数据就是，写入数据库，的数据 因此可以使用binlog来恢复数据，这种方式称之为二进制增量数据恢复 ###切割binlog图解  ##mysqldump的参数 ###-F参数切割binlog日志 -F 参数用于mysqldump全量备份后立即对binlog日志文件切割，生成一个新日志文件，且重新记录binlog日志，用于将来增量恢复，从新的binlog日志文件开始 利用-F能够立即切割出新的binlog文件 “` mysqldump -uroot -pyuchao7777 -F -B kings|gzip > /data/3307/$(date +%F).sql.gz “` ###–master-data参数 刚才的-F参数，是给mysqldump提供的切割binlog，但是这也需要不断的执行，不断的切割。` mysqldump也提供了–master-data参数，能够在备份的SQL文件中，添加CHANGE MASTER语句，以及binlog文件的pos位置，也就是记录数据的写入位置。 “` –master-data[=value] 该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。 如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。 如果选项值等于2，CHANGE MASTER语句被写成SQL注释。 “` ## 解读参数–set-gtid-purged=OFF “` 1. 机器A mysqldump 导出数据，不用该参数，导出的SQL数据，携带当前机器A的 binlog历史记录 且机器B导入该SQL的话，也不会再新记录binlog 2. 机器A mysqldump导出数据携带该参数，导出的只有SQL数据，且不包含GTID信息 这样，新机器B导入该SQL数据，就会重新自己记录binlog 事务记录。 简单总结一下，对当前自己的数据库备份恢复，不需要写这个参数，而远程其他数据的的导入。最好添加 “` ###-X参数 既然是数据备份，比如说淘宝网的数据库要进行全量备份，但是例如在24:00整点，还有人在写入数据，那就无法保证数据的一致性。 因此得在备份时，将表锁住，防止数据写入，得到一个完整的数据备份，也就是24:00停止所有写入操作。 ##2.实战全流程 ###2.1数据创建 “` 1.数据准备 [[email protected] ~]$mysql -uroot -plinux3306 -S /tmp/mysql.sock 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.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> create database ztd_linux charset utf8mb4; Query OK, 1 row affected (0.00 sec) mysql> use ztd_linux; Database changed mysql> mysql> create table t1(id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1),(2),(3); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> commit ; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +——————+———-+————–+——————+——————————————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +——————+———-+————–+——————+——————————————+ | mysql-bin.000004 | 829 | | | c68e5464-0e50-11ed-8726-000c2947d442:1-7 | +——————+———-+————–+——————+——————————————+ 1 row in set (0.00 sec) “`