2020-09-24
邏輯備份
備份的是建庫、建表、插入等操作所執(zhí)行的SQL語句（DML，DDL，DCL），適用于中小型數(shù)據(jù)庫，效率相對(duì)較低
保證數(shù)據(jù)一致性：在備份的時(shí)候會(huì)自動(dòng)進(jìn)行鎖表
工具安裝
①mysqldump在安裝MySQL數(shù)據(jù)庫時(shí)自帶，在/安裝路徑/bin/mysqldump
②若不想安裝mysql數(shù)據(jù)庫，只單獨(dú)安裝mysqldump，使用命令下載：yum -y install holland-mysqldump.noarch
#遠(yuǎn)程備份語法(后綴名隨意，能區(qū)分開就行)
mysqldump -u 用戶名 -p 密碼 -h 服務(wù)器 -P 端口 需要備份的數(shù)據(jù)庫 > 備份文件名.sql
#本地備份語法
mysqldump -u 用戶名 -p 密碼 數(shù)據(jù)庫名 > 備份文件.sql
#常用備份選項(xiàng)
-A （或 --all-databases） #備份所有庫
-B （或 --databases web mysql db2） #備份多個(gè)庫
-d （或 --no-data） #不導(dǎo)出任何數(shù)據(jù)，指導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)
備份表
[root@localhost ~]# mkdir /home/bakups
[root@localhost ~]# mysqldump -uroot -p'123' db2 table2 > /home/backups/db2-table2-`date +%F`.sql #指定備份某庫中的某個(gè)表
[root@localhost ~]# mysqldump -uroot -p'123' db2 table2 table3> /home/backups/db2-table2-table3-`date #備份某庫的多個(gè)表
備份庫
[root@localhost ~]# mysqldump -uroot -p'123' db1 > /home/backups/db1-`date +%F`.sql #備份單個(gè)庫
[root@localhost ~]# mysqldump -uroot -p'123' -B db1 db2 > /home/backups/db1-db2-`date +%F`.sql #備份多個(gè)庫
[root@localhost ~]# mysqldump -uroot -p'123' -A > /home/backups/allbase-`date +%F`.sql #備份所有的庫
恢復(fù)數(shù)據(jù)庫和表
為保證數(shù)據(jù)一致性，應(yīng)在數(shù)據(jù)恢復(fù)前停止數(shù)據(jù)庫的對(duì)外服務(wù)，停止binlog日志，因?yàn)楫?dāng)使用binlog日志恢復(fù)數(shù)據(jù)時(shí)也會(huì)產(chǎn)生binlog日志
(1)恢復(fù)庫
mysql> create database company;
[root@localhost ~]# mysql -uroot -p'123' company < /home/backups/company.sql
(2)恢復(fù)表
方式一：
mysql> set sql_log_bin=0; #停止binlog日志（可選）
mysql > use company;
mysql> source /home/back/company.employee.sql;
方式二：
[root@localhost ~]# mysql -uroot -p'123' company < /home/back/company.employee.sql
備份和恢復(fù)表結(jié)構(gòu)
#備份表結(jié)構(gòu)
[root@localhost ~]# mysqldump -uroot -p'123' -d db1 table1 > /home/backups/table1.sql
#恢復(fù)表結(jié)構(gòu)
mysql > create database db3; #創(chuàng)建新庫（或者直接使用已存在的庫）
導(dǎo)入導(dǎo)出數(shù)據(jù)
直接在庫里導(dǎo)入導(dǎo)出數(shù)據(jù)（沒有表結(jié)構(gòu)，只有數(shù)據(jù)）
在恢復(fù)數(shù)據(jù)的時(shí)候先恢復(fù)表結(jié)構(gòu)，再導(dǎo)入數(shù)據(jù)
備份表結(jié)構(gòu)用mysqldump，導(dǎo)出數(shù)據(jù)SQL語句(select * from table2 into outfile '/home/sql/table2.bak';)
mysql> show variables like "secure_file_priv"; #查詢導(dǎo)入導(dǎo)出數(shù)據(jù)的目錄（保證數(shù)據(jù)安全做共享）
#修改安全文件目錄（secure_file_priv）
[root@localhost home]# cd /home/
[root@localhost home]# mkdir sql
[root@localhost home]# chown mysql.mysql sql/
[root@localhost home]# vim /etc/my.cnf
#在[mysqld]里追加
secure_file_priv=/home/sql
[root@localhost home]# systemctl restart mysqld
#導(dǎo)出數(shù)據(jù)
mysql > show databases;
mysql > use db2;
mysql > show tables;
mysql > select * from t2 into outfile '/home/sql/db2-t2.bak';
mysql > select * from t2 into outfile '/home/sql/db2-t2.bak' fields terminated by ',' lines terminated by '\n'; #(fields terminated by ',' ：字段以逗號(hào)分割) (lines terminated by '\n'：結(jié)尾換行)
#注意：添加了修飾導(dǎo)出的數(shù)據(jù)不是表格形式，導(dǎo)入的時(shí)候會(huì)報(bào)錯(cuò)
#導(dǎo)入數(shù)據(jù)
mysql > delete * from t2; #刪除舊數(shù)據(jù)
mysql > load data infile '/home/sql/db2-t2.bak' into table t2; #如果表結(jié)構(gòu)改變則數(shù)據(jù)導(dǎo)入失敗
使用binlog日志恢復(fù)數(shù)據(jù)
前提：必須開啟有binlog日志功能
#開啟binlog
[root@localhost ~]# vim /etc/my.cnf
##確保以下兩條配置存在##
#指定binlog的存放位置和名稱（自定義）
log-bin = /usr/local/mysqld/mysql/mysql-bin.log
#主從復(fù)制需要server_id，開啟binlog也需要
server_id = 11
[root@localhost ~]# systemctl restart mysqld #重啟使配置生效
[root@localhost ~]# cd /usr/local/mysqld/mysql/ && ls #重啟之后就能在指定目錄下看到binlog日志文件
[root@localhost ~]# mysql -uroot -p'123'
mysql > flush logs; #刷新日志，使后面的SQL操作都記錄再另一個(gè)binlog日志中
mysql > create table db2.t3(id int);
[root@localhost ~]# mysqlbinlog /usr/local/mysqld/mysql/mysql-bin.000002 #查看binlog日志內(nèi)容
ff1.png
#恢復(fù)測試
mysql>drop table testdb.t2(id int); #刪除剛創(chuàng)建的表格 注意：增刪改都會(huì)記錄到binlog日志中
[root@localhost ~]# mysqlbinlog --start-position 219 --stop-position 321 mysql-bin.000002 |mysql -uroot -p123 #恢復(fù)語句，根據(jù)起始位置和結(jié)束為止進(jìn)行恢復(fù)（binlog文件不能帶路徑，會(huì)報(bào)錯(cuò)）
