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

I. use a single tablespace per table

This way the ibdata1 file will not be too large.

Xtrabackup can use partial backup, which can only be used in the case of one table and one file.

1. Before using xtrabackup, we need to see if our current mysql uses a single tablespace per table, and if not, we must modify it to a separate tablespace per table:

one

Mysql > show global variables like'% innodb_file_p%'

two

+-+ +

three

| | Variable_name | Value |

four

+-+ +

five

| | innodb_file_per_table | OFF |

six

+-+ +

seven

1 row in set (0.00 sec)

Here is the off state, so we need to start this feature. In the future, if you want to use xtrabackup or various advanced features, it is recommended that you directly write the default configuration into the configuration file when you install mysql, and it will be very troublesome to change it halfway.

two。 Re-export the imported data and modify it to one file per table:

one

[root@test2] # mysqldump-uroot-- lock-all-tables-- all-databases-- master-data=2-- events >. / 1.sql

two

Shut down the database

three

[root@test2 ~] # / etc/init.d/mysqld stop

four

Shutting down MySQL.. SUCCESS!

five

Edit the configuration file to add the following parameters

six

[root@test2 ~] # vim/etc/my.cnf

seven

Innodb_file_per_table= 1

eight

Then delete the data file and reinitialize it

nine

[root@test2 data] # pwd

ten

/ mydata/data

eleven

Delete all data files

twelve

[root@test2 data] # rm-fr *

thirteen

Initialize mysql

fourteen

[root@test mysql] # pwd

fifteen

/ usr/local/mysql

sixteen

[root@test mysql] # scripts/mysql_install_db-user=mysql--datadir=/mydata/data/-basedir=/usr/local/mysql/

seventeen

[root@test mysql] # / etc/init.d/mysqld start

eighteen

Check again to see if each table is a separate tablespace

nineteen

Mysql > showglobal variables like'% innodb_file_per%'

twenty

+-+ +

twenty-one

| | Variable_name | Value |

twenty-two

+-+ +

twenty-three

| | innodb_file_per_table | ON |

twenty-four

+-+ +

twenty-five

1 row in set (0.00sec)

twenty-six

Recover data

twenty-seven

Mysql > source~/1.sql

twenty-eight

[root@test2 data] # ll wpdb/

twenty-nine

Total 220

thirty

-rw-r--r--. 1 mysqlmysql 61 Apr 6 11:05 db.opt

thirty-one

-rw-r--r--.1 mysql mysql 8646 Apr 6 11:05 students.frm

thirty-two

-rw-r--r--.1 mysql mysql 98304 Apr 6 11:05students.ibd

thirty-three

-rw-r--r--. 1 mysql mysql 8556 Apr 6 11:05 tb4.frm

thirty-four

-rw-r--r--. 1 mysql mysql 98304 Apr 6 11:05 tb4.ibd

thirty-five

As shown above, each table has a separate tablespace.

thirty-six

3. Specify datadir:

one

Vim / etc/my.cnf

two

Add a line:

three

Datadir = / data/server/mysql/data

Second, install percona-xtrabackup

one

Resolve dependencies

two

[root@test ~] # yum-y install libaio perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL

Install percona-xtrabackup:

1.rpm

[root@test2 tools] # rpm-ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

Download the rpm package using wget, and then install it through the rpm package

Reference: http://www.cnblogs.com/cosiray/archive/2012/03/02/2376595.html

Reference: http://blog.163.com/ji_1006/blog/static/10612341201382355716623/

2.yum

one

Automatic

two

$rpm-Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

three

Then you will see:

four

Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

five

Preparing... # [100%]

six

1:percona-release # # [100%]

seven

Manual

eight

[percona]

nine

Name = CentOS $releasever-Percona

ten

Baseurl= http://repo.percona.com/centos/$releasever/os/$basearch/

eleven

Enabled = 1

twelve

Gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

thirteen

Gpgcheck = 1

fourteen

Test installation library

fifteen

Use yum list | grep percona to ensure installation

sixteen

Yum list | grep percona

Third, use percona-xtrabackup

Innobackupex is the encapsulation and function extension of xtrabackup by perl script.

1. Configure permissions

one

Grant RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO 'back'@'localhost'identified by' back'

two。 Create a complete set

one

Innobackupex-user=back-password=back-no-timestamp / tmp/mysqlback/

Internal mechanism: during backup, innobackupex will call xtrabackup to back up innodb tables and copy all table definitions, tables from other engines (MyISAM,MERGE,CSV,ARCHIVE)

-- no-timestamp. If you specify this option, the backup will be backed up directly in BACKUP-DIR, and no timestamp folder will be created.

-- default-file, which specifies the configuration file used to configure the line selection of innobackupex.

3. Use innobackupex to prepare everything (back it up, don't do this yet, just save it. Do this only when you need to restore it directly)

one

Innobackupex-apply-lop mysqlback/

-- user-memory: specify the memory that can be used in the preparatory phase. The more memory, the faster the speed. The default is 10MB.

one

Innobackupex-apply-log-use-memory=4G / path/to/BACKUP-DIR

4. Full reduction

1) stop the service:

Service mysqld stop

2) Delete datadir directory data

Rm-rf data/*

3) use innobackupex-- copy-back to restore backups, and all backups will be copied to datadir according to my.cnf:

one

Innobackupex-copy-back mysqlback/

Note: datadir must be empty, innobackupex-copy-back will not overwrite existing files, and note that the service needs to be turned off during restore. If the service is started, it cannot be restored to datadir.

4) modify the owner and permissions of the data

one

Chown-R mysql:mysql data/

5) start the service

Service mysqld start

5. Create an incremental backup

You need a complete backup before you create an incremental backup, otherwise an incremental backup is meaningless.

one

Innobackupex / data/backups

Create the first incremental backup

one

Innobackupex-incremental / data/backups-incremental-basedir=BASEDIR

6. Restore incremental backup

In full, use-redo-only to do only committed transactions and do not roll back uncommitted transactions

one

Innobackupex-apply-log-redo-only BASE-DIR

Apply the first incremental backup

one

Innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Roll back uncommitted transactions

one

Innobackupex-- apply-log BASE-DIR restores incremental backups. Restoring incremental backups is the same as restoring complete backups.

one

Innobackupex-copy-back BASE-DIR

6. Single table backup:

Backup order table:

one

Innobackupex-user=root-password=simlinux.com-defaults-file=/etc/my.cnf-include='se.searchaccount'-slave-info-safe-slave-backup-stream=tar / data/backup > / data/backup/searchaccount.tar.gz

Multiple tables:-- include='test.* | pms.*'

After copying to another server, extract:

one

Tar-ixf searchaccount.tar.gz-C / data/databak/

Innobackupex preparation:

one

Innobackupex-apply-log-export / data/databak

7. Single table restore:

To define a table, first create a table. The table structure needs to be the same as the one being restored (if the table already exists, it does not need to be created):

one

CREATE TABLE `searchmaker '...

Then discard the tablespace (copy the file after discard finishes the tablespace, and then import it, so that there is no need to restart)

one

Mysql > ALTER TABLE se.searchaccount DISCARD TABLESPACE

Copy the files to the corresponding directory of databdir (note the owner of the file and file permissions). Mysql needs .ibd and .cfg files. After mysql 5.6, you can do import,XtraDB without using cfg. Ibd and .exp. Mariadb 10.0 can be directly through ibd and frm files import

one

Cp / data/databak/se/ {searchaccount.ibd,searchaccount.cfg} / usr/local/mysql/data/se/

one

Chown mysql.mysql / usr/local/mysql/data/se/

Then import tablespace

one

Mysql > ALTER TABLE se.searchaccount IMPORT TABLESPACE

8. Back up using xbstream streams

one

Innobackupex-- stream=xbstream / root/backup/ > / root/backup/backup.xbstream

9. Backing up with tar

one

Innobackupex-stream=tar. / > / root/backup/out.tar

10. Using tar streams and backing up to another server

one

Ssh-keygen

two

Ssh-copy-id-I mysql@127.0.0.1

one

Innobackupex-- stream=tar. / | gzip | ssh user@destination "cat-> / data/backup.tar"

Add password

one

Innobackupex-- stream=tar. / | gzip | sshpass-p 'root123' ssh user@destination "cat-> / data/backup.tar"

11. Compressed flow

one

Innobackupex-- stream=tar. / | gzip-> backup.tar.gz

twelve。 To extract tar stream, you need to add I parameter

one

Tar-xizf backup.tar.gz

Reference:

Innobackupex-- user=root-- password=root-- stream=tar. / | gzip | sshpass-p 'root123' ssh root@10.6.32.28 "cat-> / usr/programs/xtraback/backup.tar.gz"

Innobackupex-defaults-file=/etc/my.cnf-user=root-password='123'-host=localhost-rsync-compress--compress-threads=5-compress-chunk-size=64K-slave-info / root

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.