目錄
一、使用 bin log 來恢復(fù)數(shù)據(jù)
一、bin log的三種格式
1、statement：基于SQL語句的復(fù)制（statement-based replication，SBR）
2、row：基于行的復(fù)制（row-based replication，RBR）
3、mixed：混合模式復(fù)制（mixed-based replication，MBR）
4、查看模式和更改模式
二、配置bin log策略
三、獲取bin log文件列表
四、生成新的bin log文件
五、查看日志中的內(nèi)容
1、在mysql中使用show binlog events查看
2、在shell中使用mysqlbinlog來查看
六、利用bin log 來恢復(fù)數(shù)據(jù)
1、通過pos來恢復(fù)
2、通過時間來恢復(fù)
二、邏輯備份和恢復(fù)
一、mysqldump工具實(shí)現(xiàn)邏輯備份
二、邏輯恢復(fù)
三、物理備份和恢復(fù)
一、物理備份
二、物理恢復(fù)
四、數(shù)據(jù)庫的導(dǎo)出和導(dǎo)入
一、導(dǎo)出
1、通過INTO OUTFILE導(dǎo)出
2、使用mysqldump導(dǎo)出
3、使用mysql命令導(dǎo)出
二、導(dǎo)入
?五、數(shù)據(jù)庫誤刪除恢復(fù)步驟
一、使用 bin log 來恢復(fù)數(shù)據(jù)
一、bin log的三種格式
1、statement：基于SQL語句的復(fù)制（statement-based replication，SBR）
- 每一條會修改數(shù)據(jù)的sql都會記錄在binlog中。
- 優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。但是注意statement相比于row能節(jié)約多少性能與日志量，取決于應(yīng)用的SQL情況。正常同一條記錄修改或者插入row格式所產(chǎn)生的日志量還小于Statement產(chǎn)生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產(chǎn)生大量日志，因此在考慮是否使用ROW格式日志時應(yīng)該跟據(jù)應(yīng)用的實(shí)際情況，其所產(chǎn)生的日志量會增加多少，以及帶來的IO性能問題。
- 缺點(diǎn)：由于記錄的只是執(zhí)行語句，為了這些語句在slave上正確運(yùn)行，我們還必須記錄每條語句在執(zhí)行時候的一些相關(guān)信息，以保證所有語句能在slave得到和在master端執(zhí)行時相同的結(jié)果。另外，一些特定的函數(shù)功能如果要在slave和master上保持一致會有很多相關(guān)問題。
2、row：基于行的復(fù)制（row-based replication，RBR）
- 5.1.5版本的MySQL才開始支持row level的復(fù)制，它不記錄sql語句上下文相關(guān)信息，僅保存哪條記錄被修改。
- 優(yōu)點(diǎn)：binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以row level的日志會非常清楚的記下每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會出現(xiàn)某些特定情況下的存儲過程，或function，以及trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。
- 缺點(diǎn)：所有的執(zhí)行的語句當(dāng)記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產(chǎn)生大量的日志內(nèi)容。但是新版本的MySQL對row level模式進(jìn)行了優(yōu)化，并不是所有的修改都會以row level來記錄，像遇到表結(jié)構(gòu)變更的時候就會以statement模式來記錄，如果sql語句確實(shí)就是update或者delete等修改數(shù)據(jù)的語句，那么還是會記錄所有行的變更。
3、mixed：混合模式復(fù)制（mixed-based replication，MBR）
- 從5.1.8版本開始，MySQL提供了Mixed格式，實(shí)際上就是Statement與Row的結(jié)合。
- 在Mixed模式下，一般的語句修改使用statment格式保存binlog，如果一些函數(shù)，statement無法完成主從復(fù)制的操作，則采用row格式保存binlog，MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。
4、查看模式和更改模式
mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> set binlog_format=mixed; Query OK, 0 rows affected (0.00 sec)
二、配置bin log策略
在配置文件中添加
[mysqld] # 指定 binary log 的路徑和名稱 log-bin="/var/lib/mysql/binlog" # 存活時間 binlog_expire_logs_seconds=60000 # 單個 binlog 文件的最大大小 max_binlog_size=100M # binlog的日志策略 binlog_format=mixed;
三、獲取bin log文件列表
mysql> show binary logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | IU077-bin.000038 | 157 | No | | IU077-bin.000039 | 1400 | No | | IU077-bin.000040 | 157 | No | | IU077-bin.000041 | 333 | No | | IU077-bin.000042 | 157 | No | | IU077-bin.000043 | 157 | No | | IU077-bin.000044 | 157 | No | | IU077-bin.000045 | 157 | No | | IU077-bin.000046 | 157 | No | | IU077-bin.000047 | 157 | No | | IU077-bin.000048 | 180 | No | | IU077-bin.000049 | 180 | No | | IU077-bin.000050 | 157 | No | | IU077-bin.000051 | 157 | No | +------------------+-----------+-----------+
四、生成新的bin log文件
以下三種情況均可生成新的bin log
- 每當(dāng)我們停止或重啟服務(wù)器時，服務(wù)器會把日志文件記入下一個日志文件，MySQL會在重啟時生成一個新的日志文件，文件序號遞增。
- 如果日志文件超過max_binlog_size（默認(rèn)值1G）系統(tǒng)變量配置的上限時，也會生成新的日志文件（在這里需要注意的是，如果你正使用大的事務(wù)，二進(jìn)制日志還會超過max_binlog_size，不會生成新的日志文件，事務(wù)全寫入一個二進(jìn)制日志中,這種情況主要是為了保證事務(wù)的完整性）
- 手動的flush logs刷新日志，會生成一個新的日志文件;
mysql> flush logs; Query OK, 0 rows affected (0.07 sec)
五、查看日志中的內(nèi)容
1、在mysql中使用show binlog events查看
查看具體某個日志中的內(nèi)容：
mysql> show binlog events in 'IU077-bin.000052'; +------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+ | IU077-bin.000052 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 | | IU077-bin.000052 | 126 | Previous_gtids | 1 | 157 | | | IU077-bin.000052 | 157 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | IU077-bin.000052 | 234 | Query | 1 | 345 | create database db_16 /* xid=20 */ | | IU077-bin.000052 | 345 | Anonymous_Gtid | 1 | 422 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | IU077-bin.000052 | 422 | Query | 1 | 555 | use `db_16`; create table tb1(id int, lname varchar(20)) /* xid=24 */ | | IU077-bin.000052 | 555 | Anonymous_Gtid | 1 | 634 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | IU077-bin.000052 | 634 | Query | 1 | 710 | BEGIN | | IU077-bin.000052 | 710 | Table_map | 1 | 768 | table_id: 92 (db_16.tb1) | | IU077-bin.000052 | 768 | Write_rows | 1 | 821 | table_id: 92 flags: STMT_END_F | | IU077-bin.000052 | 821 | Xid | 1 | 852 | COMMIT /* xid=26 */ | | IU077-bin.000052 | 852 | Rotate | 1 | 899 | IU077-bin.000053;pos=4 | +------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+ 12 rows in set (0.00 sec)指定從某個pos開始查看
mysql> show binlog events in 'IU077-bin.000052' from 710; +------------------+-----+------------+-----------+-------------+--------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+------------+-----------+-------------+--------------------------------+ | IU077-bin.000052 | 710 | Table_map | 1 | 768 | table_id: 92 (db_16.tb1) | | IU077-bin.000052 | 768 | Write_rows | 1 | 821 | table_id: 92 flags: STMT_END_F | | IU077-bin.000052 | 821 | Xid | 1 | 852 | COMMIT /* xid=26 */ | | IU077-bin.000052 | 852 | Rotate | 1 | 899 | IU077-bin.000053;pos=4 | +------------------+-----+------------+-----------+-------------+--------------------------------+ 4 rows in set (0.00 sec)查看當(dāng)前正在寫入的日志狀態(tài)：
mysql> show master status\G *************************** 1. row *************************** File: IU077-bin.000053 Position: 157 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)2、在shell中使用mysqlbinlog來查看
mysqlbinlog -v 日志文件的路徑 例如： mysqlbinlog -v /usr/local/mysql/data/binlog.000010
六、利用bin log 來恢復(fù)數(shù)據(jù)
注意：
? ? ? ? bin log不是通過回滾來恢復(fù)數(shù)據(jù)的，而是重新執(zhí)行SQL語句來恢復(fù)的。
1、通過pos來恢復(fù)
方式一：在shell中執(zhí)行mysql登錄和切換
mysqlbinlog --start-position=100 --stop-position=300 --database=數(shù)據(jù)庫名 binlog文件 | mysql -uroot -p密碼 -v 數(shù)據(jù)庫名方式二：生成.sql后綴的腳本，然后在mysql中執(zhí)行該腳本
# 第一步：先生成sql腳本 mysqlbinlog --start-position=100 --stop-position=300 --database=數(shù)據(jù)庫名 binlog文件 > resume.sql # 第二步：在sql中執(zhí)行該腳本 mysql> source 腳本路徑/腳本名稱2、通過時間來恢復(fù)
把position換成datetime或timestramp
二、邏輯備份和恢復(fù)
一、mysqldump工具實(shí)現(xiàn)邏輯備份
基礎(chǔ)操作：
# 備份單個數(shù)據(jù)庫 mysqldump -uroot -p密碼 database_1 > database_1.sql # 備份全部數(shù)據(jù)庫 mysqldump -uroot -p密碼 --all-databases # 或者 mysqldump -uroot -p密碼 --A更細(xì)致的操作：
參數(shù) 作用 --databases 或 -B 備份部分?jǐn)?shù)據(jù)庫 數(shù)據(jù)庫名稱后面跟表名 備份部分表 --where="id < 10" 備份表中的部分?jǐn)?shù)據(jù) --ignore-table=數(shù)據(jù)庫.表名 不備份這個表 --no-create-info 不備份結(jié)構(gòu)，僅備份數(shù)據(jù) --no_data 不備份數(shù)據(jù)
二、邏輯恢復(fù)
1、在shell中恢復(fù)
mysql -uroot -p密碼 數(shù)據(jù)庫名 < .sql腳本2、在mysql中恢復(fù)
mysql> source 腳本路徑/腳本名稱.sql
三、物理備份和恢復(fù)
一、物理備份
1、先給數(shù)據(jù)庫上鎖，防止備份過程中數(shù)據(jù)庫被修改
mysql> flush tables with read lock;2、物理拷貝備份
cp -r database_1 /backup/database_1_bak3、數(shù)據(jù)庫解鎖（一定不能忘記解鎖）
mysql> unlock tables;二、物理恢復(fù)
1、物理移動
cp 數(shù)據(jù)庫 路徑/名稱2、重啟mysql服務(wù)
systemctl restart mysql3、給mysql用戶賦予權(quán)限
chown -R mysql.mysql /var/lib/mysql/數(shù)據(jù)庫名
四、數(shù)據(jù)庫的導(dǎo)出和導(dǎo)入
一、導(dǎo)出
1、通過INTO OUTFILE導(dǎo)出
1、先查看數(shù)據(jù)庫是否可以導(dǎo)出
mysql> show variables like '%secure%'; +--------------------------+------------------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------------------+ | require_secure_transport | OFF | | secure_file_priv | D:\Program Files (x86)\MySQL\Data 8.0\Uploads\ | +--------------------------+------------------------------------------------+2、開始導(dǎo)出
mysql> select * from tb1 INTO OUTFILE "D:\Program Files (x86)\MySQL\Data 8.0\Uploads\tb1.txt";2、使用mysqldump導(dǎo)出
同時生成txt文件和sql腳本
mysqldump -uroot -p密碼 -T "路徑" 數(shù)據(jù)庫名 表名;3、使用mysql命令導(dǎo)出
把查詢內(nèi)容導(dǎo)出內(nèi)容到txt文件
mysql -uroot -p密碼 --execute="select * from tb1;" 數(shù)據(jù)庫名 > 名字.txt;二、導(dǎo)入
1、LOAD DATA INFILE 導(dǎo)入文章來源：http://www.zghlxwxcb.cn/news/detail-831369.html
mysql> LOAD DATA INFILE 'txt文本文件' INTO TABLE dbname.tbname;
?五、數(shù)據(jù)庫誤刪除恢復(fù)步驟
- 取最近一次的全量備份。
- 用全量備份恢復(fù)出一個臨時庫。
- 取出全量備份之后的bin log日志。
- 剔除日志中的誤操作SQL，把其他語句都應(yīng)用到臨時庫。
- 回復(fù)完成之后，把臨時庫恢復(fù)到主庫。
?文章來源地址http://www.zghlxwxcb.cn/news/detail-831369.html
到了這里，關(guān)于MySQL數(shù)據(jù)庫的備份、恢復(fù)、導(dǎo)出、導(dǎo)入（bin log和mydump）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
