Use mysqldump to implement Mysql backup _mysql

Note: Backup files and binary log files cannot be placed under the same disk as MySQL

Node 1

1, Node 1 modify the MySQL configuration file, open binary log Save

Here I put the binary log in the/data/mysql/directory,/data/is another LVM disk I created, I wanted to put it directly under/data/, I found that I could not start MySQL, so I recommend it in/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, view the binary log some information

[root@node1 ~]# mysql
  mariadb [none]]> show master logs;
  +------------------+-----------+
  | Log_name     | file_size |
  +------------------+-----------+
  | mysql-bin.000001 |    264 |
  | mysql-bin.000002 |    245 |
  +------------------+-----------+

3. View the storage engine type of the table and back up

MARIADB [hellodb]> Show Table status\g;

If engine is MyISAM, the backup scenario is as follows, and you need to manipulate the lock table

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


If engine is InnoDB, the backup scenario is as follows

[Root@node1 ~]# mysqldump-uroot--single-transaction--master-data=2--flush-logs--databases hellodb >/root/ Hellodb_inno.sql
--single-transaction: Hot standby
--master-data=2: Change Master to statement for note
--flush-logs: Log scrolling

Bulk modify the table's storage Engine "will be the results of a single execution can be modified, not recommended directly in MySQL modify"

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


4, modify the data in the table

MARIADB [(None)]> use Hellodb;
MARIADB [hellodb]> INSERT INTO students (Name,age,gender,classid,teacherid) VALUES (' Caocao ',}, ' M ', 6,8);
MARIADB [hellodb]> Delete from students where stuid=3;

5. Copy backup files to another node

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


Node 2

6, in another node for MySQL recovery

Modify the configuration file for node 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

Restore Backup Files

[Root@node2 ~]# MySQL </root/hellodb_inno.sql
[root@node2 ~]# less Hellodb_inno.sql
  --Change MASTER to Maste R_log_file= ' mysql-bin.000002 ', master_log_pos=245;

According to the table shown in the backup that moment, binary log mysql-bin.000002, operation to 245

7, restore the binary Log on Node 2

Convert binary log files to SQL files on Node 1 after 245

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


Copy to Node 2

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


Use the SQL file you just produced to recover the contents of the backup operation

[root@node2 ~]# mysql < /root/binlog.sql

8, view the recovery situation

[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 |
 +-------+---------------+-----+--------+---------+-----------+