mysql備份之mysqldump

標籤：mysqldump   mysql   mysql備份   

mysql備份之mysqldump

注意：備份檔案和二進位記錄檔不能與mysql放在同一磁碟下

節點1

1、節點1上修改mysql設定檔，開起二進位日誌儲存

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

[[email protected] ~]# mkdir -pv /data/mysql/[[email protected] ~]# chown mysql:mysql /data/*[[email protected] mysql]# cd /var/lib/mysql[[email protected] mysql]# cp -a mysql-bin.000001 mysql-bin.000002 mysql-bin.index /data/mysql/[[email protected] ~]# vim /etc/my.cnf.d/server.cnf    [server]    log_bin=/data/mysql/mysql-bin[[email protected] ~]# service mariadb restart

2、查看二進位日誌的一些資訊

[[email protected] ~]# 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則備份方案如下，需要對鎖表後操作

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

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

[[email protected] ~]# 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、複本備份檔案到另一節點

[[email protected] ~]# scp hellodb_inno.sql 192.168.1.114:/root/

節點2

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

修改節點2的設定檔

[[email protected] ~]# mkdir -pv /data/mysql[[email protected] ~]# vim /etc/my.cnf    [mysqld]     log_bin=/data/mysql/mysql-bin[[email protected] ~]# chown mysql:mysql /data/*[[email protected] ~]# chown mysql:mysql /data[[email protected] ~]# service mariadb start

還原備份檔案

[[email protected] ~]# mysql < /root/hellodb_inno.sql[[email protected] ~]# 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檔案

[[email protected] ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000002 > binlog.sql

複製給節點2

[[email protected] ~]# scp binlog.sql 192.168.1.114:/root/

利用剛才生產的sql檔案來恢複備份之後操作的內容

[[email protected] ~]# mysql < /root/binlog.sql

8、查看恢複情況

[[email protected] ~]# 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 |+-------+---------------+-----+--------+---------+-----------+

本文出自 “信風” 部落格，請務必保留此出處http://xsllqs.blog.51cto.com/2308669/1827605

mysql備份之mysqldump