MySQL Backup mysqldump tool

Reference Documentation:Use the mysqldump+ binary log to implement a backup recovery database:http://www.178linux.com/60616MySQL Advanced article (three methods of backup summary: LVM, mysqldump, xtrabackup): http://www.178linux.com/9781

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

Node 1

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

Here I put the binary log in the/data/mysql/directory,/data/is another LVM disk I created, originally wanted to be placed directly under/data/, found unable to start MySQL, so it is recommended to put in/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, viewing some information for binary logs

[[email protected] ~]# 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 the engine is MyISAM then the backup scheme is as follows and requires operation on the lock table

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

If the engine is InnoDB then the backup scheme is as follows

[Email protected] ~]# 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 recorded as comment--flush-logs: Log scrolling

Bulk Modify table Storage Engine "will get the results of a single execution can be modified, not recommended to modify 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 ', ", ' M ', 6,8); MariaDB [hellodb]> Delete from students where stuid=3;

5. Copy the backup file to another node

[Email protected] ~]# SCP hellodb_inno.sql 192.168.1.114:/root/

Node 2

6. mysql Recovery on another node

Modify the configuration file for node 2

[Email protected] ~]# mkdir-pv/data/mysql[[email protected] ~]# vim/etc/my.cnf [mysqld] Log_bin=/data/mysql/mys  Ql-bin[[email protected] ~]# chown mysql:mysql/data/*[[email protected] ~]# chown mysql:mysql/data[[email protected] ~]# Service MARIADB Start

Restore Backup Files

[[email protected] ~]# MySQL </root/hellodb_inno.sql[[email protected] ~]# less hellodb_inno.sql – change MASTER T O master_log_file= ' mysql-bin.000002 ', master_log_pos=245;

According to the display in the table, at the moment of backup, the binary log mysql-bin.000002, operation to 245

7. Restore the binary Log on Node 2

Convert a binary log file after 245 to a SQL file on Node 1

[Email protected] ~]# Mysqlbinlog--start-position=245/var/lib/mysql/mysql-bin.000002 > Binlog.sql

Copy to Node 2

[Email protected] ~]# SCP binlog.sql 192.168.1.114:/root/

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

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

8. View recovery Status

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

This article is from the "Wang Liming" blog, make sure to keep this source http://afterdawn.blog.51cto.com/7503144/1927509

MySQL Backup mysqldump tool