• dafa888.com手机版客户端,dafa888.com手机版客户端【微信快捷支付】

  • 发布时间:2016-01-12 23:00 | 作者:yc | 来源:互联网 | 浏览:1200 次
  • dafa888.com手机版客户端,dafa888.com手机版客户端【微信快捷支付】当InnoDB发动时,InnoDB会反省数据文件和营业日志,并实行两个进程:它运用(前滚)现已提交的营业日志到数据文件,并将修正过但没有提交的数据进行回滚操作

    MySQL Study之--MySQL innodb引擎备份器械XtraBackup之二(数据库全备)

    Xtrabackup备份道理:

    在InnoDB内部会保护一个redo日志文件,咱们也能够叫做营业日志文件营业日志会存储每一个InnoDB表数据的纪录修正

    xtrabackup在发动时会记着log sequence number(LSN),而且仿制统统的数据文件仿制进程需求一些时候,以是这时期要是数据文件有篡改,那么将会使数据库处于一个不一样的时候点这时,xtrabackup会运转一个后台进程,用于监督营业日志,并从营业日志仿制最新的修正xtrabackup有需要继承的做这个操作,是由于营业日志是会轮转重复的写入,而且营业日志能够被重用以是xtrabackup自发动起头,就赓续的将营业日志中每个数据文件的修正都纪录下来

    上面等于xtrabackup的备份进程接下来是预备(prepare)进程在这个进程中,xtrabackup运用之前仿制的营业日志,对各个数据文件实行磨难康复(就像MySQL刚发动时要做的一样)当这个进程完毕后,数据库就能够做康复回覆再起了

    以上的进程在xtrabackup的编译二进制法度榜样中完成法度榜样innobackupex能够准许咱们备份MyISAM表和frm文件然后增添了快捷和功用Innobackupex会发动xtrabackup,直到xtrabackup仿制数据文件后,然后实行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上,今后仿制MyISAM数据文件,终极释放锁

    备份MyISAM和InnoDB表终极会处于合营,在预备(prepare)进程完毕后,InnoDB表数据现已前滚到整个备份完毕的点,而不是回滚到xtrabackup刚起头时的点这个时候点与实行FLUSH TABLES WITH READ LOCK的时候点一样,以是MyISAM表数据与InnoDB表数据是同步的相似Oracle的,InnoDB的prepare进程能够称为recover(康复),MyISAM的数据仿制进程能够称为restore(回覆再起)

    xtrabackup和innobackupex这两个器械都提供了很多前文没有说到的功用特色手册上有对各个功用都有详细的先容大略先容下,这些器械提供了如流(streaming)备份,增量(incremental)备份等,颠末仿制数据文件,仿制日志文件和提交日志到数据文件(前滚)完成了各类复合备份措施

    如下图所示:

    650) this.width=650;" src="http://img.blog.csdn.net/?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" style="border:none;" />

    事例剖析:

    一、数据库全备

    1、创立设置设置设备摆设摆设文件

    [root@rh64 ~]# cat /tmp/my.cnf

    [mysqld]

    datadir=/var/lib/mysql

    socket=/var/lib/mysql/mysql.sock

    user=mysql

    # Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links=0

    innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend

    innodb_log_files_in_group=2

    innodb_log_file_size=

    2、创立备份目录

    [root@rh64 ~]# ls -ld /data/mysql/backup/

    drwxrwxrwx. 3 mysql mysql 4096 Oct 15 12:13 /data/mysql/backup/

    3、测验

    mysql> show databases;

    +--------------------+

    | Database|

    +--------------------+

    | information_schema |

    | mysql|

    | performance_schema |

    | prod|

    | test|

    +--------------------+

    5 rows in set (0.06 sec)

    mysql> use prod;

    Reading table information for completion of table and column names

    You can turn off this feature to get a quicker startup with -A

    Database changed

    mysql> show tables;

    +----------------+

    | Tables_in_prod |

    +----------------+

    | t1|

    | t2|

    | t3|

    +----------------+

    3 rows in set (0.00 sec)

    mysql> select count(*) from t1;

    +----------+

    | count(*) |

    +----------+

    |49152 |

    +----------+

    1 row in set (0.13 sec)

    刺进数据:

    mysql> insert into t1 select * from t1;

    Query OK, 49152 rows affected (0.69 sec)

    Records: 49152Duplicates: 0Warnings: 0

    mysql> commit;

    Query OK, 0 rows affected (0.00 sec)

    mysql> select count(*) from t1;

    +----------+

    | count(*) |

    +----------+

    |98304 |

    +----------+

    1 row in set (0.03 sec)

    4、创立备份用户并授权

    mysql> create user 'bkusr'@'%' identified by 'oracle';

    Query OK, 0 rows affected (0.00 sec)

    mysql> grant reload,create tablespace,lock tables ,replication client,super on *.* to 'bakusr'@'%';

    Query OK, 0 rows affected (0.00 sec

    mysql> create user 'bakusr'@localhost identified by 'oracle';

    Query OK, 0 rows affected (0.00 sec)

    5、进行数据库全备

    [root@rh64 ~]# innobackupex --user=bakusr --password='oracle' --socket=/var/lib/mysql/mysql.sock --defaults-file=/tmp/my.cnf /data/mysql/backup/full

    xtrabackup: Error: --defaults-file must be specified first on the command line

    ----提示设置设置设备摆设摆设文件参数有需要放在第一位

    以root用户备份:

    [root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password='oracle'--socket=/var/lib/mysql/mysql.sock/data/mysql/backup/full

    14:18:16 innobackupex: Starting the backup operation

    IMPORTANT: Please check that the backup run completes successfully.

    At the end of a successful backup run innobackupex

    prints "completed OK!".

    14:18:16version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'bakusr'(using password: YES).

    14:18:16version_check Connected to MySQL server

    14:18:16version_check Executing a version check against the server...

    14:18:16version_check Done.

    14:18:16 Connecting to MySQL server host: localhost, user: bakusr, password: set, port: 0, socket: /var/lib/mysql/mysql.sock

    Using server version 5.6.25-73.1

    innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)

    xtrabackup: uses posix_fadvise().

    xtrabackup: cd to /var/lib/mysql

    xtrabackup: open files limit requested 0, set to 1024

    xtrabackup: using the following InnoDB configuration:

    xtrabackup:innodb_data_home_dir = ./

    xtrabackup:innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend

    xtrabackup:innodb_log_group_home_dir = ./

    xtrabackup:innodb_log_files_in_group = 2

    xtrabackup:innodb_log_file_size =

    14:18:16 >> log scanned up to ()

    xtrabackup: Generating a list of tablespaces

    14:18:16 [01] Copying ./ibdata1 to /data/mysql/backup/full/2015-10-28_14-18-16/ibdata1

    14:18:17 [01]...done

    14:18:17 [01] Copying ./ibdata2 to /data/mysql/backup/full/2015-10-28_14-18-16/ibdata2

    14:18:17 >> log scanned up to ()

    14:18:17 [01]...done

    14:18:17 [01] Copying ./prod/t2.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/prod/t2.ibd

    14:18:17 [01]...done

    14:18:17 [01] Copying ./prod/t1.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/prod/t1.ibd

    14:18:18 [01]...done

    14:18:18 >> log scanned up to ()

    14:18:18 [01] Copying ./prod/t3.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/prod/t3.ibd

    14:18:18 [01]...done

    14:18:18 [01] Copying ./mysql/slave_worker_info.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/mysql/slave_worker_info.ibd

    14:18:18 [01]...done

    14:18:18 [01] Copying ./mysql/slave_master_info.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/mysql/slave_master_info.ibd

    14:18:18 [01]...done

    14:18:18 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/mysql/slave_relay_log_info.ibd

    14:18:18 [01]...done

    14:18:18 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/mysql/innodb_index_stats.ibd

    14:18:19 [01]...done

    14:18:19 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/mysql/innodb_table_stats.ibd

    14:18:19 [01]...done

    ......

    14:21:39 Executing UNLOCK BINLOG

    14:21:39 Executing UNLOCK TABLES

    14:21:39 All tables unlocked

    14:21:39 Backup created in directory '/data/mysql/backup/full/2015-10-28_14-21-20'

    14:21:39 [00] Writing backup-my.cnf

    14:21:39 [00]...done

    14:21:39 [00] Writing xtrabackup_info

    14:21:39 [00]...done

    xtrabackup: Transaction log of lsn () to () was copied.

    14:21:39 completed OK!

    反省备份:

    [root@rh64 backup]# ls

    bak.shfullprodt.txt

    [root@rh64 backup]# cd full

    [root@rh64 full]# ls

    2015-10-28_14-23-23

    [root@rh64 full]# cd 2015-10-28_14-23-23/

    [root@rh64 2015-10-28_14-23-23]# ls -lt

    total 22560

    -rw-r----- 1 root root507 Oct 28 14:23 xtrabackup_info

    -rw-r----- 1 root root398 Oct 28 14:23 backup-my.cnf

    -rw-r----- 1 root root115 Oct 28 14:23 xtrabackup_checkpoints

    -rw-r----- 1 root root2560 Oct 28 14:23 xtrabackup_logfile

    drwx------ 2 root root4096 Oct 28 14:23 performance_schema

    drwx------ 2 root root4096 Oct 28 14:23 mysql

    drwx------ 2 root root4096 Oct 28 14:23 test

    drwx------ 2 root root4096 Oct 28 14:23 prod

    -rw-r----- 1 root root Oct 28 14:23 ibdata2

    -rw-r----- 1 root root Oct 28 14:23 ibdata1

    [root@rh64 2015-10-28_14-23-23]#

    运用参数:--no-timestamp

    [root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password='oracle'--socket=/var/lib/mysql/mysql.sock/data/mysql/backup/full --no-timestamp

    则不建马上刻有关的目录:

    [root@rh64 backup]# ls

    bak.shfullprodt.txt

    [root@rh64 backup]# cd full

    [root@rh64 full]# ls

    backup-my.cnfibdata2performance_schematestxtrabackup_info

    ibdata1mysqlprodxtrabackup_checkpointsxtrabackup_logfile

    [root@rh64 full]# ls -l

    total 22560

    -rw-r----- 1 root root398 Oct 28 14:25 backup-my.cnf

    -rw-r----- 1 root root Oct 28 14:25 ibdata1

    -rw-r----- 1 root root Oct 28 14:25 ibdata2

    drwx------ 2 root root4096 Oct 28 14:25 mysql

    drwx------ 2 root root4096 Oct 28 14:25 performance_schema

    drwx------ 2 root root4096 Oct 28 14:25 prod

    drwx------ 2 root root4096 Oct 28 14:25 test

    -rw-r----- 1 root root115 Oct 28 14:25 xtrabackup_checkpoints

    -rw-r----- 1 root root522 Oct 28 14:25 xtrabackup_info

    -rw-r----- 1 root root2560 Oct 28 14:25 xtrabackup_logfile

    运用通俗用户备份:

    [root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'--socket=/var/lib/mysql/mysql.sock/data/mysql/backup/full

    Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

    ----提示:短少reload权限

    反省用户权限:

    mysql> select user,host,Reload_priv from user where user='bakusr';

    +--------+-----------+-------------+

    | user| host| Reload_priv |

    +--------+-----------+-------------+

    | bakusr | %| Y|

    | bakusr | localhost | N|

    +--------+-----------+-------------+

    2 rows in set (0.04 sec)

    授权:

    mysql> grant reload,create tablespace,lock tables ,replication client,super on *.* to 'bakusr'@localhost;

    Query OK, 0 rows affected (0.08 sec)

    mysql> select user,host,Reload_priv from user where user='bakusr';

    +--------+-----------+-------------+

    | user| host| Reload_priv |

    +--------+-----------+-------------+

    | bakusr | %| Y|

    | bakusr | localhost | Y|

    +--------+-----------+-------------+

    2 rows in set (0.15 sec)

    备份:

    [root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'--socket=/var/lib/mysql/mysql.sock/data/mysql/backup/full--no-timestamp

    14:26:45 innobackupex: Starting the backup operation

    IMPORTANT: Please check that the backup run completes successfully.

    At the end of a successful backup run innobackupex

    prints "completed OK!".

    14:26:45version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'usrbak'(using password: YES).

    Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock','usrbak',...) failed: Access denied for user 'usrbak'@'localhost' (using password: YES) at - line 1314

    14:26:45 Connecting to MySQL server host: localhost, user: usrbak, password: set, port: 0, socket: /var/lib/mysql/mysql.sock

    Failed to connect to MySQL server: Access denied for user 'usrbak'@'localhost' (using password: YES).

    [root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'--socket=/var/lib/mysql/mysql.sock/data/mysql/backup/full --no-timestamp

    14:26:59 innobackupex: Starting the backup operation

    IMPORTANT: Please check that the backup run completes successfully.

    At the end of a successful backup run innobackupex

    prints "completed OK!".

    14:26:59version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'bakusr'(using password: YES).

    14:26:59version_check Connected to MySQL server

    14:26:59version_check Executing a version check against the server...

    14:26:59version_check Done.

    14:26:59 Connecting to MySQL server host: localhost, user: bakusr, password: set, port: 0, socket: /var/lib/mysql/mysql.sock

    Using server version 5.6.25-73.1

    innobackupex version 2.3.2 based on MyS