MySQL backup mysqldump

Source: Internet
Author: User
Tags mysql backup

MySQL backup mysqldump

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 the other LVM disk I created, I would like to put in the/data/, I found that I could not start MySQL, so it is recommended to put in the/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 Mar IADB restart

2. Check the binary log for some information

[[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 "Wind" blog, please be sure to keep this source http://xsllqs.blog.51cto.com/2308669/1827605

MySQL backup mysqldump

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.