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

Database backup

1 Cold standby (cp)

2 LVM snapshot backup

Lvcreate-s-n rhel6node1-L 2G / dev/vg_kvm/rhel6base

3 Hot standby

Mysqldump-hserip-u user name-p password database name > directory name / xxx.sql

The representation of the database name

-- all-databases backup all data on the server

The database name backs up all the data in a library

Database name table name backing up all the data of a table

-B database name 1 database name 2 back up all data from several libraries

Full recovery

Mysql-hlocalhost-uroot-p123 [database name]

< xxx.sql 4 增量备份 1 binlog日志加脚本实现增量备份 1 使用mysql自带binlog日志功能实现对数据的增量备份 2 安装第3方软件提供增量备份工具对数据做增量备份 ------------------------------------------------------------------ 使用binlog日志增量备份/增量恢复 binlog日志的使用 mysql服务有4种日志文件：错误日志、查询日志、慢查询日志 、binlog日志 binlog日志 （二进制日志） 记录客户端连接自己之后，执行的除查询之外的sql命令。 默认没开启binlog日志 service mysql stop vim /etc/my.cnf [mysqld] Log-bin=/logdir/plj :wq mkdir /logdir chown mysql /logdir service mysql start cd /var/lib/mysql/ www-bin.000001 binlog日志文件 500M+ www-bin.000002 www-bin.index 日志的索引文件 查看binlog日志文件内容 mysqlbinlog binlog日志文件名 手动生成新binlog日志文件方式？ mysql>

Flush logs

Mysql-hlocalhost-uroot-P123-e "flush logs"

Service mysql restart

How does binlog record SQL commands?

1 character offset

2 time point

Mysqlbinlog [options] binlog log file name

Time point

-- start-datetime= "2015-12-16 14:20:30" go to the library to find time by yourself.

-- stop-datetime= "2015-12-16 17:20:30" go to the library to find time by yourself.

Character offset

-- start-position= numbers go to the database to find the offset.

-- stop-position= numbers go to the database to find the offset.

Mysqlbinlog-- start-position=385-- stop-position=1134 plj.000010 | mysql-hlocalhost-uroot-p123 [database name]

Vim / shell/newlogfilebak.sh

#! / bin/bash

If [!-e / logbak]; then

Mkdir / logbak

Fi

Mysql-hlocalhost-uroot-P123-e "flush logs" & > / dev/null

Cd / logdir

For file in `sed'$d'plj.index`

Do

Name= `echo $file | awk-F "/"'{print $3}'`

If [!-e / logbak/$name]; then

Cp $name / logbak/

Fi

Done

: wq

00 18 * * 1 / shell/bakadb.sh & > / dev/null

00 19 * 2-7 / shell/newfillogbak.sh

2 three-party plug-in xtrabackup incremental backup

Yum-y install perl-Time-HiRes perl-DBD-MySQL

Rpm-ivh percona-xtrabackup-2.1.6-702.rhel6.x86_64.rpm

Incremental backup / incremental restore command xtrabackup_56

The library table is not locked during backup

Only the table records are backed up during the backup, not the table structure.

When using xtrabackup_56 incremental backups, you must have a full backup so that when you back up again, you will know which data is new.

Xtrabackup:C program that supports InnoDB/XtraDB

Xtrabackup_56

Option

-- backup backup data

-- prepare prepares to recover data

-- target-dir= directory name backup directory

-- Source file backed up by datadir= database directory

-- incremental-basedir= directory name incremental backup specifies the directory where the last backup file was stored

-- when the incremental-dir= directory name is ready to restore data, specify which backup directory to use for recovery

1001 5

Xtrabackup_56-backup-datadir=/var/lib/mysql-target-dir=/onedir

2002 7

Xtrabackup_56-backup-datadir=/var/lib/mysql-target-dir=/new1-incremental-basedir=/onedir

3003 10

Xtrabackup_56-backup-datadir=/var/lib/mysql-target-dir=/new2-incremental-basedir=/new1

Mysql > delete from testdb.a

Drop table testdb.a

Incremental recovery

1. Prepare to recover data

Xtrabackup_56-prepare-datadir=/var/lib/mysql-target-dir=/onedir

Xtrabackup_56-prepare-datadir=/var/lib/mysql-target-dir=/onedir-incremental-dir=/new1

Xtrabackup_56-prepare-datadir=/var/lib/mysql-target-dir=/onedir-incremental-dir=/new2

2. The backed up file is copied back to the corresponding location under the database directory.

Cp / onedir/testdb/a.ibd / var/lib/mysql/testdb/

3. Restart the database server service mysql restart

Xtrabackup working process

Lsn log serial number

/ var/lib/mysql/

Ibdata1

Ib_logfile0

Ib_logfile1

Ibdata1

Ibdata1.delta

Ibdata1.meta

Xtrabackup_checkpoints

Xtrabackup_logfile

A.ibd

A.ibd.delta a.ibd.meta

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.