您好,登錄后才能下訂單哦!
如何理解mysqldump備份,很多新手對(duì)此不是很清楚,為了幫助大家解決這個(gè)難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來(lái)學(xué)習(xí)下,希望你能有所收獲。
mysqldump備份
邏輯innodb備份mysql
標(biāo)準(zhǔn)備份
mysqldump -uroot -p123 --default-character-set=utf8 --single-transaction --extended-insert=false --hex-blob --master-data=2 --log-error=/tmp/test.err --routines --triggers --events --quick
--flush-logs --databases test1 t1 > test1.sql
--quick 查詢(xún)出來(lái)不放在buffer,直接輸出
--extended-insert=false 導(dǎo)出insert語(yǔ)句是多行,并非 insert into t values (),();
--lock-all-tables 將myisam表鎖住,保持表的一致性
--single-transaction 將innodb表鎖住,保持表的一致性
--master-data=2 file和position的記錄位置 ,2 代表注釋
--databases 導(dǎo)出創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句
恢復(fù)
mysql -uroot -p1234356 < czb.sql
恢復(fù)其他庫(kù)
1、將備份里的創(chuàng)建數(shù)據(jù)語(yǔ)句刪除
2、use ‘新庫(kù)’
3、在mysql數(shù)據(jù)庫(kù)創(chuàng)建新庫(kù)
4、mysql -uroot -p123456 新庫(kù)名 < czb.sql
binlog追加數(shù)據(jù)
根據(jù)--master-data 參數(shù)備份記錄備份的位置,通過(guò)mysqlbinlog 查看
mysqlbinlog --start-position=3444 -d czb mysql-bin.000003 >a.sql
--start-position=3444 //3444這個(gè)值通過(guò)備份里的--master-data參數(shù)得到的
mysql -uroot -p123456
source a.sql
實(shí)驗(yàn):
[root@mysql_master backup]# mysql -uroot -p123456
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 25
Server version: 5.6.29-log Source distribution
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| czb |
| mysql |
| performance_schema |
| t |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use czb
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_czb |
+--------------------+
| F_ORDERINFO_DETAIL |
| t |
+--------------------+
2 rows in set (0.00 sec)
[root@mysql_master backup]# mysqldump -uroot -p123456 --default-character-set=utf8 --single-transaction --extended-insert=false --hex-blob --master-data=2 --log-error=/tmp/test.err --routines --triggers --events --quick --flush-logs --databases czb t > test1.sql
Warning: Using a password on the command line interface can be insecure.
[root@mysql_master backup]# ls
test1.sql
[root@mysql_master backup]#
以上備份完成
恢復(fù)到其他數(shù)據(jù)庫(kù):
[root@mysql_master backup]# vi test1.sql
#CREATE DATABASE /*!32312 IF NOT EXISTS*/ `czb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `back`;
[root@mysql_master backup]# mysql -uroot -p123456
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 27
Server version: 5.6.29-log Source distribution
Copyright (c) 2000, 2016, 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> create database back;
Query OK, 1 row affected (0.04 sec)
mysql>
[root@mysql_master backup]# mysql -uroot -p123456 back < test1.sql
Warning: Using a password on the command line interface can be insecure.
以上就是將czb庫(kù)中的t表恢復(fù)到back庫(kù)中
追備份后的增加的數(shù)據(jù)(通過(guò)binlog)
假如現(xiàn)在備份的表有新的數(shù)據(jù)插入了。
[root@mysql_master backup]# more test1.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
[root@mysql_master data]# mysqlbinlog --start-position=120 -d czb /usr/local/mysql/data/mysql-bin.000004 >a.sql
[root@mysql_master backup]# mysql -uroot -p123456
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 31
Server version: 5.6.29-log Source distribution
Copyright (c) 2000, 2016, 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> use back
看完上述內(nèi)容是否對(duì)您有幫助呢?如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章,請(qǐng)關(guān)注億速云行業(yè)資訊頻道,感謝您對(duì)億速云的支持。
免責(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)容。