Shulou(Shulou.com)06/01 Report--

Let's talk about the mysql backup tool mysqldump. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe that after reading the mysql backup tool mysqldump to introduce this article, you will certainly benefit.

Node 1

1. Modify the mysql configuration file on node 1 and open the binary log to save it.

Here I put the binary log in the / data/mysql/ directory. / data/ is another lvm disk I created. I originally intended to put it directly under / data/, but found that mysql could not be started, so it is recommended to put 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 some information of the binary log

[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 it up

MariaDB [hellodb] > show table status\ G

If engine is myisam, the backup scheme is as follows, and the operation after locking table is required.

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

If engine is innodb, the backup scheme is as follows

[root@node1 ~] # mysqldump-uroot-- single-transaction-- master-data=2-- flush-logs-- databases hellodb > / root/hellodb_inno.sql--single-transaction: hot backup-master-data=2: CHANGE MASTER TO statement recorded as comments-flush-logs: log scrolling

Storage engine for batch table modification [the result can be modified at one time, and it is not recommended to modify it directly in mysql]

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',99,'M',6,8); MariaDB [hellodb] > delete from students where stuid=3

5. Copy the backup file to another node

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

6. Perform mysql recovery on another node

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

Copy to Node 2

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

Use the sql file you just produced to restore the contents of the operation after the backup

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

Is there anything you don't understand about the introduction of the above mysql backup tool mysqldump? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.