使用mysqldump實現mysql備份_Mysql

注意：備份檔案和二進位記錄檔不能與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/hellodb_inno.sql[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 < /root/binlog.sql

8、查看恢複情況

[root@node2 ~]# mysqlMariaDB [(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 |+-------+---------------+-----+--------+---------+-----------+