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

MySQL backup and restore 1, data backup 1. Backup mode

1) full backup: a full backup of the database

2) incremental backup: files changed since the last backup

3) differential backup: files modified after the last complete

Suggestion: full amount + increment = full amount in one month, increment in one week

two。 Backup classification

1) physical backup

Cold backup: after data is turned off

Hot backup: when data is running stat

2) logical backup

Back up logical objects (tables, libraries)

Second, full backup 1. Characteristics

1) data integrity

2) the data is relatively large

3) long time and repetitive data

Packaging, cp, mysqldump

Case study:

1. Prepare data

/ / create a hehe database mysql > create database hehe

/ / add table an and format mysql > create table hehe.a to hehe database (user char (4), password char (8), primary key (user))

/ / insert data in the table mysql > insert into hehe.a values ('Zhangs','123')

/ / insert data in the table mysql > insert into hehe.a values ('Lisi','123')

two。 Backup method 1: (cold backup)

/ / close the database / etc/init.d/mysqld stop

/ / backup database tar-zcvf / root/mysql.bak / usr/local/mysql/data/

Method 2: (hot backup)

Mysqldump-u root-p-- all-database > / root/mysql.sql / / Hot backup, backup all databases without stopping

Method 3: (compress lock table hot backup)

/ / use compression to do database hot backup

Mysqldump-h localhost-uroot-p-- opt-- single-transaction library name | gzip > library name.sql.gz

/ / Import the database

Gzip-dc library name. Sql.gz | mysql-uroot-p database password

Note: the above hot backup mode 3 is the safest!

3. Simulated fault

/ / mistakenly delete hehe database mysql > drop database hehe

4. Recovery method 1: (cold recovery)

/ / close the database / etc/init.d/mysqld stop

/ / tar-zxvf / root/mysql.bak-C /

Method 2: (heat recovery)

/ / enter the data execution command mysql > source / root/mysql.sql

Method 3: (heat recovery)

/ / restore data mysql-u root-p

< /root/mysql.sql 三、增量备份 1.Mysql不自带增量备份方式，需通过Binary logs(记录所有更改操作)实现增量 案例： 1）准备数据（同上准备数据） 2）开启二进制日志 //编辑MySQL数据库配置文件 vim /etc/my.cnf //重启MySQL服务 /etc/init.d/mysqld restart 3.完整备份方式一：(冷备份) /etc/init.d/mysqld stop //备份数据库 tar -zcvf /root/mysql.bak /usr/local/mysql/data/ 方式二：(热备份) mysqldump -u root -p --all-database >

/ root/mysql.sql / / Hot backup, database does not need to stop, backup all databases

4. Data increase

/ / View the binary file ls / usr/local/mysql/data/

/ / intercept binaries and prepare to add data mysqladmin-u root-p flush-logs

/ / View the binary file ls / usr/local/mysql/data/

/ / log in to the database to add data mysql > insert into hehe.a values ('wang','123')

/ / generate a new binary file mysql > flush log

/ / exit the database to view ls / usr/local/mysql/data/

5. Incremental backup (all the data to be restored is here in mysql-bin.000032)

/ / copy the added data cp / usr/local/mysql/data/mysql-bin.000032 / root/

/ / View the / root/ directory (for example, the newly added data has been successfully backed up in the following cases)

6. Data deletion

/ / mistakenly delete mysql > delete hehe.a from .a where user='lisi' from login database

7. Incremental restore

/ / restore the deleted new data mysqlbinlog mysql-bin.000018 | mysql- u root-p

/ / View database select * from hehe.a

Note: incremental restore succeeded

8. Verification

/ / Log in to the database to check whether the data is restored to mysql > select * from hehe.a

Note: if you do an incremental data backup, you must intercept the binary data before the increment, and intercept the binary data again when the backup is completed. Remember that once the data is lost, the incremental data that needs to be restored is the binary data intercepted before the increment.

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.