參考文檔：

利用mysqldump+二進制日志實現備份恢復數據庫：http://www.178linux.com/60616

mysql進階篇(三種備份方法總結：lvm, mysqldump, xtrabackup):http://www.178linux.com/9781

注意：備份文件和二進制日志文件不能與mysql放在同一磁盤下

節點1

1、節點1上修改mysql配置文件，開起二進制日志保存

這里我將二進制日志放在/data/mysql/目錄下，/data/是我創建的另外一個lvm磁盤，本來想直接放在/data/下，發現無法啟動mysql，所以建議還是放在/data/mysql中

[root@node1?~]#?mkdir?-pv?/data/mysql/

[root@node1?~]#?chown?mysql:mysql?/data/*

[root@node1?mysql]#?cd?/var/lib/mysql

[root@node1?mysql]#?cp?-a?mysql-bin.000001?mysql-bin.000002?mysql-bin.index?/data/mysql/

[root@node1?~]#?vim?/etc/my.cnf.d/server.cnf

[server]

log_bin=/data/mysql/mysql-bin

[root@node1?~]#?service?mariadb?restart

2、查看二進制日志的一些信息

[root@node1?~]#?mysql

MariaDB?[(none)]>?show?master?logs;

+------------------+-----------+

|?Log_name?????????|?File_size?|

+------------------+-----------+

|?mysql-bin.000001?|???????264?|

|?mysql-bin.000002?|???????245?|

+------------------+-----------+

3、查看表的存儲引擎類型并備份

MariaDB?[hellodb]>?show?table?status\G;

如果engine是myisam則備份方案如下，需要對鎖表后操作

[root@node1?~]#?mysqldump?-uroot?--lock-tables?--master-data=2?--flush-logs?--databases?hellodb?>?/root/hellodb_myis.sql

如果engine是innodb則備份方案如下

[root@node1?~]#?mysqldump?-uroot?--single-transaction?--master-data=2?--flush-logs?--databases?hellodb?>?/root/hellodb_inno.sql

--single-transaction：熱備

--master-data=2：記錄為注釋的CHANGE?MASTER?TO語句

--flush-logs：日志滾動

批量修改表的存儲引擎【將得到的結果一次執行即可修改，不建議直接在mysql中修改】

MariaDB?[hellodb]>?SELECT?CONCAT('ALTER?TABLE?',table_name,'?ENGINE=InnoDB;')?FROM?information_schema.tables?WHERE?table_schema='hellodb'?AND?ENGINE='myisam';

4、修改表內數據

MariaDB?[(none)]>?use?hellodb;

MariaDB?[hellodb]>?insert?into?students?(Name,Age,Gender,ClassID,TeacherID)?values?('caocao',99,'M',6,8);

MariaDB?[hellodb]>?delete?from?students?where?stuid=3;

5、復制備份文件到另一節點

[root@node1?~]#?scp?hellodb_inno.sql?192.168.1.114:/root/

節點2

6、在另一個節點進行mysql恢復

修改節點2的配置文件

[root@node2?~]#?mkdir?-pv?/data/mysql

[root@node2?~]#?vim?/etc/my.cnf

[mysqld]

log_bin=/data/mysql/mysql-bin

[root@node2?~]#?chown?mysql:mysql?/data/*

[root@node2?~]#?chown?mysql:mysql?/data

[root@node2?~]#?service?mariadb?start

還原備份文件

[root@node2?~]#?mysql?

[root@node2?~]#?less?hellodb_inno.sql

--?CHANGE?MASTER?TO?MASTER_LOG_FILE='mysql-bin.000002',?MASTER_LOG_POS=245;

根據表中的顯示，在備份那一刻，二進制日志mysql-bin.000002，操作到了245

7、在節點2上恢復二進制日志

在節點1上將245之后的二進制日志文件轉換為sql文件

[root@node1?~]#?mysqlbinlog?--start-position=245?/var/lib/mysql/mysql-bin.000002?>?binlog.sql

復制給節點2

[root@node1?~]#?scp?binlog.sql?192.168.1.114:/root/

利用剛才生產的sql文件來恢復備份之后操作的內容

[root@node2?~]#?mysql?

8、查看恢復情況

[root@node2?~]#?mysql

MariaDB?[(none)]>?use?hellodb;

MariaDB?[hellodb]>?select?*?from?students;

+-------+---------------+-----+--------+---------+-----------+

|?StuID?|?Name??????????|?Age?|?Gender?|?ClassID?|?TeacherID?|

+-------+---------------+-----+--------+---------+-----------+

|?????1?|?Shi?Zhongyu???|??22?|?M??????|???????2?|?????????3?|

|?????2?|?Shi?Potian????|??22?|?M??????|???????1?|?????????7?|

|?????4?|?Ding?Dian?????|??32?|?M??????|???????4?|?????????4?|

|?????5?|?Yu?Yutong?????|??26?|?M??????|???????3?|?????????1?|

|?????6?|?Shi?Qing??????|??46?|?M??????|???????5?|??????NULL?|

|?????7?|?Xi?Ren????????|??19?|?F??????|???????3?|??????NULL?|

|?????8?|?Lin?Daiyu?????|??17?|?F??????|???????7?|??????NULL?|

|?????9?|?Ren?Yingying??|??20?|?F??????|???????6?|??????NULL?|

|????10?|?Yue?Lingshan??|??19?|?F??????|???????3?|??????NULL?|

|????11?|?Yuan?Chengzhi?|??23?|?M??????|???????6?|??????NULL?|

|????12?|?Wen?Qingqing??|??19?|?F??????|???????1?|??????NULL?|

|????13?|?Tian?Boguang??|??33?|?M??????|???????2?|??????NULL?|

|????14?|?Lu?Wushuang???|??17?|?F??????|???????3?|??????NULL?|

|????15?|?Duan?Yu???????|??19?|?M??????|???????4?|??????NULL?|

|????16?|?Xu?Zhu????????|??21?|?M??????|???????1?|??????NULL?|

|????17?|?Lin?Chong?????|??25?|?M??????|???????4?|??????NULL?|

|????18?|?Hua?Rong??????|??23?|?M??????|???????7?|??????NULL?|

|????19?|?Xue?Baochai???|??18?|?F??????|???????6?|??????NULL?|

|????20?|?Diao?Chan?????|??19?|?F??????|???????7?|??????NULL?|

|????21?|?Huang?Yueying?|??22?|?F??????|???????6?|??????NULL?|

|????22?|?Xiao?Qiao?????|??20?|?F??????|???????1?|??????NULL?|

|????23?|?Ma?Chao???????|??23?|?M??????|???????4?|??????NULL?|

|????24?|?Xu?Xian???????|??27?|?M??????|????NULL?|??????NULL?|

|????25?|?Sun?Dasheng???|?100?|?M??????|????NULL?|??????NULL?|

|????26?|?caocao????????|??99?|?M??????|???????6?|?????????8?|

+-------+---------------+-----+--------+---------+-----------+

總結

以上是生活随笔為你收集整理的mysql dump 查看器_mysql备份之mysqldump工具的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。