使用Percona XtraBackup备份工具创建MySQL Slave

使用Percona XtraBackup备份工具创建MySQL Slave

在原来搭建MySQL Slave如果可以停同步停库对应用响应要求不高,直接拷贝数据到新备库,配置好后在启用同步就可以了.但是在一些紧急情况下:比如业务超级繁忙的系统,数据库和同步又不能停去做一个MySQL Slave,这个时候XtraBackup就派上用场了.

下面重点介绍用XtraBackup做MySQL Slave的步骤

一、安装XtraBackup
[mysql@localhost backup]$ ll /etc/yum.repos.d/Percona.repo 
-rw-r--r--. 1 root root 189 Dec 10 10:33 /etc/yum.repos.d/Percona.repo

[mysql@localhost ~]$ sudo yum install xtrabackup

二、用XtraBackup创建一个完全备份

[mysql@localhost ~]$ innobackupex-1.5.1 --user=root --password=root --port=3306 --defaults-file=/etc/my.cnf --slave-info  --no-timestamp /mysql/backup

备份完成后会显示如下信息:

xtrabackup: Creating suspend file '/mysql/backup/xtrabackup_suspended_2' with pid '2963'

140209 11:31:21  innobackupex: Continuing after ibbackup has suspended
140209 11:31:21  innobackupex: Starting to lock all tables...
140209 11:31:21  innobackupex: All tables locked and flushed to disk
140209 11:31:21  innobackupex: Failed to get master binlog coordinates from SHOW SLAVE STATUS
140209 11:31:21  innobackupex: This means that the server is not a replication slave. Ignoring the --slave-info option

140209 11:31:21  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/mysql/data'
innobackupex: Backing up file '/mysql/data/test/heartbeat.frm'
innobackupex: Backing up files '/mysql/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (4879018460)
innobackupex: Backing up file '/mysql/data/percona/test.frm'
innobackupex: Backing up file '/mysql/data/percona/db.opt'
innobackupex: Backing up files '/mysql/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
140209 11:31:21  innobackupex: Finished backing up non-InnoDB tables and files

140209 11:31:21  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '4879018460'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (4879018460)

xtrabackup: Creating suspend file '/mysql/backup/xtrabackup_log_copied' with pid '2963'
xtrabackup: Transaction log of lsn (4879018460) to (4879018460) was copied.
140209 11:31:22  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/mysql/backup'
innobackupex: MySQL binlog position: filename 'mysql-bin.000036', position 2683668
140209 11:31:22  innobackupex: Connection to database server closed
140209 11:31:22  innobackupex: completed OK!

注:--slave-info表示备份完成会产生对应主库的BinLog用于同步保证数据一致性,此参数会在一段时间内全局共享锁不可写,但是比停库影响小.

备份完成后还需要为一些未提交事务做恢复保证数据一致.

[mysql@localhost ~]$ innobackupex-1.5.1 --apply-log /mysql/backup/

完成后会显示如下信息:

InnoDB: Last MySQL binlog file position 0 2683668, file name mysql-bin.000036

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4879020124

140209 11:32:16  innobackupex-1.5.1: Restarting xtrabackup with command: xtrabackup_56  --defaults-file="/mysql/backup/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/mysql/backup --tmpdir=/tmp
for creating ib_logfile*

xtrabackup_56 version 2.1.7 for MySQL server 5.6.15 Linux (x86_64) (revision id: 721)
xtrabackup: cd to /mysql/backup
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=4879020124
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.15 started; log sequence number 4879020556

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 2683668, file name mysql-bin.000036

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4879022252
140209 11:32:18  innobackupex-1.5.1: completed OK!

三、将备份文件拷贝到新Slave Server

[mysql@localhost mysql]$ tar czvf backup.tar.gz backup/*

[mysql@localhost mysql]$ scp backup.tar.gz 192.168.20.130:/home/mysql/

四、新Server安装MySQL数据库

注意:如果服务器不是新安装的而且跑着MySQL需要先关闭数据库在清除文件目录,否则恢复时是不会覆盖的,然后用以下命令数据拷贝恢复.
[mysql@localhost mysql]$ innobackupex-1.5.1 --copy-back /mysql/backup
执行完会显示如下信息:
innobackupex-1.5.1: Copying '/mysql/backup/performance_schema/accounts.frm' to '/mysql/data/performance_schema/accounts.frm'
innobackupex-1.5.1: Creating directory '/mysql/data/test'
innobackupex-1.5.1: Copying '/mysql/backup/test/heartbeat.ibd' to '/mysql/data/test/heartbeat.ibd'
innobackupex-1.5.1: Copying '/mysql/backup/test/heartbeat.frm' to '/mysql/data/test/heartbeat.frm'

innobackupex-1.5.1: Starting to copy InnoDB system tablespace
innobackupex-1.5.1: in '/mysql/backup'
innobackupex-1.5.1: back to original InnoDB data directory '/mysql/data'
innobackupex-1.5.1: Copying '/mysql/backup/ibdata1' to '/mysql/data/ibdata1'

innobackupex-1.5.1: Starting to copy InnoDB undo tablespaces
innobackupex-1.5.1: in '/mysql/backup'
innobackupex-1.5.1: back to '/mysql/data'

innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/mysql/backup'
innobackupex-1.5.1: back to original InnoDB log directory '/mysql/data'
innobackupex-1.5.1: Copying '/mysql/backup/ib_logfile0' to '/mysql/data/ib_logfile0'
innobackupex-1.5.1: Copying '/mysql/backup/ib_logfile1' to '/mysql/data/ib_logfile1'
innobackupex-1.5.1: Finished copying back files.

140209 17:35:02  innobackupex-1.5.1: completed OK!

五、启动数据库
[mysql@localhost data]$ mysqld_safe &
[1] 2266
[mysql@localhost data]$ 140209 17:41:13 mysqld_safe Logging to '/mysql/mysqld.log'.
140209 17:41:13 mysqld_safe Starting mysqld daemon with databases from /mysql/data

六、配置同步查看同步状态
[mysql@localhost data]$ cat xtrabackup_binlog_pos_innodb 
mysql-bin.000036        2683668

mysql> change master to master_host='192.168.40.176',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000036',master_log_pos=2683668,master_connect_retry=10;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.176
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000043
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000011
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000043
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test,percona,mysql
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 504
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> exit

OK,至此完成.^_^