溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》
您好，登錄后才能下訂單哦！
下文內(nèi)容主要給大家?guī)?lái)MySQL數(shù)據(jù)庫(kù)備份的安全與重要性，所講到的知識(shí)，與書(shū)籍略有不同，都是億速云專(zhuān)業(yè)技術(shù)人員在與用戶(hù)接觸過(guò)程中，總結(jié)出來(lái)的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來(lái)幫助。
mysqldump -u root -p auth > /backup/auth.sqlmysqldump -u 用戶(hù)名 -p [密碼] [選項(xiàng)] --databases 庫(kù)名 1 [庫(kù)名2] ... > /備份路徑/備份文件名mysqldump -u root -p --databases autth mysql > /backup/databases-auth-mysql.sqlmysqldump -u 用戶(hù)名 -p [密碼] [選項(xiàng)] --all-databases > /備份路徑/備份文件名mysqldump -u root -p --opt --all-databases > /backup/all-data.sqlmysqldump -u 用戶(hù)名 -p [密碼] [選項(xiàng)] 數(shù)據(jù)庫(kù)名 表命 > /備份路徑/備份文件名mysqldump -u root -p mysql user > /backup/mysql-user.sqlMySQL[(none)]>source /backup/all-data.sqlmysql -u 用戶(hù)名 -p [密碼] < 庫(kù)備份腳本的路徑mysql -u root -p < /backup/all-data.sqlmysq| -u 用戶(hù)名 -p [密碼] < 表備份腳本的路徑mysql -u root -P mysq| < /backup/mysql-user.sql一般恢復(fù)
基于時(shí)間點(diǎn)的恢復(fù)
mysqlbinlog [--no-defaults] 增量備份文件 | mysql -u 用戶(hù)名 -pmysqlbinlog --stop-position='操作 id' 二進(jìn)制日志 | mysql -u 用戶(hù)名 -p 密碼mysqlbinlog --start-position='操作 id' 二進(jìn)制日志 | mysql -u 用戶(hù)名 -p 密碼mysqlbinlog [-no-defaults] --stop-datetime='年-月-日 小時(shí):分鐘:秒' 二進(jìn)制日志 | mysql -u 用戶(hù)名-p 密碼mysqlbinlog [-no-defaults] --start-datetime='年-月-日 小時(shí):分鐘:秒' 二進(jìn)制日志 | mysql -u 用戶(hù)名 -p 密碼mysqlbinlog [-no-defaults] --start-datetime='年-月-日 小時(shí):分鐘:秒' --stop-datetime='年-月-日 小時(shí):分鐘:秒' 二進(jìn)制日志 | mysql -u 用戶(hù)名 -p 密碼[root@master2 ~]# mysql -uroot -p //進(jìn)入數(shù)據(jù)庫(kù)
Enter password:
mysql> create database school; //創(chuàng)建數(shù)據(jù)庫(kù)
Query OK, 1 row affected (0.01 sec)
mysql> use school; //使用數(shù)據(jù)庫(kù)
Database changed
mysql> create table info( //創(chuàng)建表
-> id int(3) not null primary key auto_increment,
-> name varchar(10) not null,
-> score decimal(4,1) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc info; //查看表結(jié)構(gòu)
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(4,1) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into info (name,score) values ('stu01',88),('stu02',77); //插入表數(shù)據(jù)
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info; //查看表內(nèi)容
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.01 sec)
mysql> select * from info limit 1; //只顯示表中的前1行
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
+----+-------+-------+
1 row in set (0.00 sec)[root@master2 ~]# cd /usr/local/mysql/data/ //切換到數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄下
[root@master2 data]# ls
auto.cnf ibdata1 ib_logfile1 mysql school test
ib_buffer_pool ib_logfile0 ibtmp1 performance_schema sys
[root@master2 data]# cd school/
[root@master2 school]# ls //數(shù)據(jù)中的文件
db.opt info.frm info.ibd
[root@master2 school]# cd ..
[root@master2 data]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/ //用xz格式壓縮
[root@master2 data]# cd /opt/
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz mysql-5.7.20 rh[root@master2 opt]# mysqldump -uroot -p school > /opt/school.sql //邏輯備份單個(gè)數(shù)據(jù)庫(kù)
Enter password:
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql
[root@master2 opt]# vim school.sql //查看備份數(shù)據(jù)庫(kù)腳本
...
CREATE TABLE `info` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`score` decimal(4,1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
...
LOCK TABLES `info` WRITE;
/*!40000 ALTER TABLE `info` DISABLE KEYS */;
INSERT INTO `info` VALUES (1,'stu01',88.0),(2,'stu02',77.0);
...[root@master2 opt]# mysqldump -uroot -p --databases school mysql > /opt/db_school_mysql.sql //備份多個(gè)數(shù)據(jù)庫(kù)
Enter password:
[root@master2 opt]# ls
db_school_mysql.sql mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql[root@master2 opt]# mysqldump -uroot -p --opt --all-databases > /opt/all.sql //完全備份
Enter password:
[root@master2 opt]# ls
all.sql mysql-2019-11-26.tar.xz rh
db_school_mysql.sql mysql-5.7.20 school.sql[root@master2 opt]# mysqldump -uroot -p school info > /opt/school_info.sql //對(duì)數(shù)據(jù)庫(kù)中的表進(jìn)行備份
Enter password:
[root@master2 opt]# ls
all.sql mysql-2019-11-26.tar.xz rh school.sql
db_school_mysql.sql mysql-5.7.20 school_info.sql[root@master2 opt]# mysqldump -uroot -p -d school info > /opt/school_info_desc.sql //對(duì)表結(jié)構(gòu)進(jìn)行備份
Enter password:
[root@master2 opt]# ls
all.sql mysql-5.7.20 school_info.sql
db_school_mysql.sql rh school.sql
mysql-2019-11-26.tar.xz school_info_desc.sql[root@master2 opt]# mysql -uroot -p //進(jìn)入數(shù)據(jù)庫(kù)
Enter password:
mysql> show databases; //查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use school; //使用數(shù)據(jù)庫(kù)
Database changed
mysql> show tables; //查看表
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> drop table info; //刪除表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; //查看表
Empty set (0.00 sec)
mysql> source /opt/school.sql //恢復(fù)數(shù)據(jù)庫(kù)腳本文件
mysql> show tables; //查看表
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)mysql> drop table info; //刪除表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; //查看表
Empty set (0.00 sec)
mysql> quit //退出
Bye
[root@master2 opt]# mysql -uroot -p123123 school < /opt/school.sql //利用mysql命令進(jìn)行恢復(fù)
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123 //進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; //使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; //查看表
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)[root@master2 opt]# vim /etc/my.cnf //開(kāi)啟二進(jìn)制日志文件
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
log-bin=mysql-bin //開(kāi)啟二進(jìn)制日志文件
server-id = 1
[root@master2 opt]# systemctl restart mysqld.service //重啟mysql服務(wù)
[root@master2 opt]# cd /usr/local/mysql/data/ //切換到mysql站點(diǎn)
[root@master2 data]# ls //查看二進(jìn)制日志文件
auto.cnf ib_logfile0 mysql performance_schema test
ib_buffer_pool ib_logfile1 mysql-bin.000001 school
ibdata1 ibtmp1 mysql-bin.index sys[root@master2 data]# mysqldump -uroot -p123123 school > /opt/school.sql //一次完全備份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# ls
auto.cnf ib_logfile0 mysql performance_schema test
ib_buffer_pool ib_logfile1 mysql-bin.000001 school
ibdata1 ibtmp1 mysql-bin.index sys
[root@master2 data]# mysqladmin -uroot -p123123 flush-logs //刷新二進(jìn)制日志文件
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# ls //生成新的二進(jìn)制日志文件，接下來(lái)的操作會(huì)保存在mysql-bin.000002中
auto.cnf ib_logfile0 mysql mysql-bin.index sys
ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema test
ibdata1 ibtmp1 mysql-bin.000002 school[root@master2 data]# mysql -uroot -p123123 ##進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; ##使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from info; //查看表
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> insert into info (name,score) values ('by01',66); //正確操作
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> delete from info where name='st01'; //錯(cuò)誤操作
Query OK, 1 row affected (0.00 sec)
mysql> insert into info (name,score) values ('by02',99); //正確操作
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
| 4 | by02 | 99.0 |
+----+------+-------+
3 rows in set (0.00 sec)
[root@master2 data]# mysqladmin -uroot -p123123 flush-logs //刷新二進(jìn)制日志文件
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt //用64位解碼器查看二進(jìn)制日志文件，并生成一個(gè)文件
[root@master2 data]# cd /opt/
[root@master2 opt]# ls
bak.txt mysql-5.7.20 rh school.sql
[root@master2 opt]# vim bak.txt //查看二進(jìn)制日志文件
# at 1084
#191127 20:14:01 server id 1 end_log_pos 1132 CRC32 0xdcc90eb5 Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `school`.`info` //第一次正確操作的時(shí)間和位置
### SET
### @1=3
### @2='by01'
### @3=66.0
...
# at 1302 //停止位置點(diǎn)
#191127 20:14:46 server id 1 end_log_pos 1357 CRC32 0x6648509a Table_map: `school`.`info` mapped to number 221
# at 1357
#191127 20:14:46 server id 1 end_log_pos 1405 CRC32 0x1eeb752b Delete_rows: table id 221 flags: STMT_END_F
### DELETE FROM `school`.`info` //第二次執(zhí)行錯(cuò)誤操作的時(shí)間和位置191127 20:14:46
### WHERE
### @1=1
### @2='st01'
### @3=88.0
# at 1405 //開(kāi)始位置點(diǎn)
#191127 20:14:46 server id 1 end_log_pos 1436 CRC32 0xf1c8d903 Xid = 54
...
# at 1630
#191127 20:15:16 server id 1 end_log_pos 1678 CRC32 0x08d9b0f4 Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `school`.`info` //第二次正確操作的時(shí)間和位置191127 20:15:16
### SET
### @1=4
### @2='by02'
### @3=99.0[root@master2 opt]# mysql -uroot -p123123 //進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; //進(jìn)入庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table info; //刪除數(shù)據(jù)庫(kù)
Query OK, 0 rows affected (0.01 sec)
mysql> select * from info; //查看表
ERROR 1146 (42S02): Table 'school.info' doesn't exist
mysql> source /opt/school.sql //恢復(fù)完全備份數(shù)據(jù)庫(kù)腳本
...
mysql> show tables; //查看表
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info; //查看表數(shù)據(jù)
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
+----+------+-------+
2 rows in set (0.00 sec)
[root@master2 opt]# mysqlbinlog --no-defaults --stop-datetime='2019-11-27 20:14:46' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //恢復(fù)bin.000002中前一個(gè)正確的執(zhí)行語(yǔ)句（從第二個(gè)錯(cuò)誤語(yǔ)句時(shí)間點(diǎn)停止）
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123 //進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; //使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from info; //查看表數(shù)據(jù)，恢復(fù)了第一次正確操作
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
+----+------+-------+
3 rows in set (0.00 sec)
[root@master2 opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-27 20:15:16' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //跳過(guò)錯(cuò)誤節(jié)點(diǎn)，恢復(fù)最后一個(gè)正確的操作（從最后一個(gè)正確的操作時(shí)間點(diǎn)開(kāi)始）
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123 //進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; //使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from info; //查看表數(shù)據(jù)，恢復(fù)了第二次正確操作，跳過(guò)了錯(cuò)誤的操作
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
| 4 | by02 | 99.0 |
+----+------+-------+
4 rows in set (0.00 sec)mysql> delete from info where name='by01'; //為實(shí)驗(yàn)方便直接刪除
Query OK, 1 row affected (0.01 sec)
mysql> delete from info where name='by02'; //刪除
Query OK, 1 row affected (0.00 sec)
mysql> select * from info; //完全備份的初始狀態(tài)
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> quit
Bye
[root@master2 opt]# mysqlbinlog --no-defaults --stop-position='1302' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //跳過(guò)錯(cuò)誤操作的位置點(diǎn)從上一個(gè)位置點(diǎn)開(kāi)始
[root@master2 opt]# mysql -uroot -p123123 //進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; //使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from info; //查看表數(shù)據(jù)，恢復(fù)了第一次正確的操作
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> quit
Bye
[root@master2 opt]# mysqlbinlog --no-defaults --start-position='1405' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //從錯(cuò)誤的位置后一個(gè)位置點(diǎn)開(kāi)始，跳過(guò)錯(cuò)誤操作的位置點(diǎn)
[root@master2 opt]# mysql -uroot -p123123 //進(jìn)入數(shù)據(jù)庫(kù)
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school; ##使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from info; //查看表數(shù)據(jù)，跳過(guò)錯(cuò)誤操作，恢復(fù)第二次正確操作數(shù)據(jù)
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
| 4 | by02 | 99.0 |
+----+------+-------+
4 rows in set (0.00 sec)[root@master2 opt]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //全部增量恢復(fù)
對(duì)于以上關(guān)于MySQL數(shù)據(jù)庫(kù)備份的安全與重要性，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專(zhuān)業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)?lái)一定的知識(shí)更新。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼