使用xtrabackup实现MySQL主从复制

    1. 环境描述

    主从环境

     

     

    项目

    Master

    Slave

    OS版本

    SuSE 11sp1 x86_64

    SuSE 11sp1 x86_64

    MySQL版本

    官方版本5.5.37

    官方版本5.5.37

    hostname

    NDMC74

    NDMC75

    IP

    192.168.223.132

    192.168.223.133

    PORT

    3306

    3306

    server-id

    1323306

    1333306

    参数文件

    /etc/my.cnf

    /etc/my.cnf

    DATADIR

    /data/mysql/mysql3306/data

    /data/mysql/mysql3306/data

     

    1. 主从库相关配置确认

    1server-id配置

    Master节点:

    NDMC74:~ # mysql -S /tmp/mysql.sock -e "show global variables like ‘server_id‘;"

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

    | Variable_name | Value   |

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

    | server_id     | 1323306 |

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

     

    Slaver节点:

    NDMC75:~ # mysql -S /tmp/mysql.sock -e "show global variables like ‘server_id‘;"

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

    | Variable_name | Value   |

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

    | server_id     | 1333306 |

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

     

    2)确认binlog是否开启

    NDMC74:~ # mysql -S /tmp/mysql.sock -e "show global variables like ‘log_bin‘;"

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

    | Variable_name | Value |

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

    | log_bin       | ON    |

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

     

    1. Master上使用xtrabackup做全库备份

    NDMC74:~ # innobackupex --defaults-file=/etc/my.cnf /data/backup/innobackupex

     

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

    and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

     

    This software is published under

    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

     

    Get the latest version of Percona XtraBackup, documentation, and help resources:

    http://www.percona.com/xb/p

     

    140731 11:20:44  innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup‘ (using password: NO).

    140731 11:20:44  innobackupex: Connected to MySQL server

    140731 11:20:44  innobackupex: Executing a version check against the server...

    140731 11:20:44  innobackupex: Done.

    IMPORTANT: Please check that the backup run completes successfully.

               At the end of a successful backup run innobackupex

               prints "completed OK!".

     

    innobackupex: Using mysql server version 5.5.37-log

     

    innobackupex: Created backup directory /data/backup/innobackupex/2014-07-31_11-20-44

     

    140731 11:20:44  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/backup/innobackupex/2014-07-31_11-20-44 --tmpdir=/tmp --extra-lsndir=‘/tmp‘

    innobackupex: Waiting for ibbackup (pid=25767) to suspend

    innobackupex: Suspend file ‘/data/backup/innobackupex/2014-07-31_11-20-44/xtrabackup_suspended_2‘

     

    xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

    xtrabackup: uses posix_fadvise().

    xtrabackup: cd to /data/mysql/mysql3306/data

    xtrabackup: open files limit requested 8192, set to 8192

    xtrabackup: using the following InnoDB configuration:

    xtrabackup:   innodb_data_home_dir = ./

    xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend

    xtrabackup:   innodb_log_group_home_dir = ./

    xtrabackup:   innodb_log_files_in_group = 3

    xtrabackup:   innodb_log_file_size = 268435456

    >> log scanned up to (6118588398)

    [01] Copying ./ibdata1 to /data/backup/innobackupex/2014-07-31_11-20-44/ibdata1

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    [01]        ...done

    [01] Copying ./test/tt.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/test/tt.ibd

    [01]        ...done

    [01] Copying ./tp50/warehouse.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/warehouse.ibd

    [01]        ...done

    [01] Copying ./tp50/item.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/item.ibd

    [01]        ...done

    >> log scanned up to (6118588398)

    [01] Copying ./tp50/district.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/district.ibd

    [01]        ...done

    [01] Copying ./tp50/customer.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/customer.ibd

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    [01]        ...done

    [01] Copying ./tp50/history.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/history.ibd

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    [01]        ...done

    [01] Copying ./tp50/new_orders.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/new_orders.ibd

    [01]        ...done

    [01] Copying ./tp50/orders.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/orders.ibd

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    [01]        ...done

    [01] Copying ./tp50/order_line.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/order_line.ibd

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    [01]        ...done

    [01] Copying ./tp50/stock.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/stock.ibd

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    >> log scanned up to (6118588398)

    [01]        ...done

    >> log scanned up to (6118588398)

    xtrabackup: Creating suspend file ‘/data/backup/innobackupex/2014-07-31_11-20-44/xtrabackup_suspended_2‘ with pid ‘25767‘

     

    140731 11:22:07  innobackupex: Continuing after ibbackup has suspended

    140731 11:22:07  innobackupex: Executing FLUSH TABLES WITH READ LOCK...

    140731 11:22:07  innobackupex: All tables locked and flushed to disk

     

    140731 11:22:07  innobackupex: Starting to backup non-InnoDB tables and files

    innobackupex: in subdirectories of ‘/data/mysql/mysql3306/data‘

    innobackupex: Backing up files ‘/data/mysql/mysql3306/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (72 files)

    >> log scanned up to (6118588398)

    innobackupex: Backing up file ‘/data/mysql/mysql3306/data/test/tt.frm‘

    innobackupex: Backing up file ‘/data/mysql/mysql3306/data/test/v1.frm‘

    innobackupex: Backing up file ‘/data/mysql/mysql3306/data/test/v2.frm‘

    innobackupex: Backing up file ‘/data/mysql/mysql3306/data/test/v3.frm‘

    innobackupex: Backing up files ‘/data/mysql/mysql3306/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (18 files)

    innobackupex: Backing up files ‘/data/mysql/mysql3306/data/tp50/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (10 files)

    140731 11:22:08  innobackupex: Finished backing up non-InnoDB tables and files

     

    140731 11:22:08  innobackupex: Executing FLUSH ENGINE LOGS...

    140731 11:22:08  innobackupex: Waiting for log copying to finish

     

    xtrabackup: The latest check point (for incremental): ‘6118588398‘

    xtrabackup: Stopping log copying thread.

    .>> log scanned up to (6118588398)

     

    xtrabackup: Creating suspend file ‘/data/backup/innobackupex/2014-07-31_11-20-44/xtrabackup_log_copied‘ with pid ‘25767‘

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

    140731 11:22:09  innobackupex: All tables unlocked

     

    innobackupex: Backup created in directory ‘/data/backup/innobackupex/2014-07-31_11-20-44‘

    innobackupex: MySQL binlog position: filename ‘mysql-bin.000009‘, position 376

    140731 11:22:09  innobackupex: Connection to database server closed

    140731 11:22:09  innobackupex: completed OK!

    NDMC74:~ #

    NDMC74:/data/backup/innobackupex/2014-07-31_11-20-44 # ls -l

    总计 1048608

    -rw-r--r-- 1 root root        357 07-31 11:20 backup-my.cnf

    -rw-r----- 1 root root 1073741824 07-31 11:20 ibdata1

    drwxr-xr-x 2 root root       4096 07-31 11:22 mysql

    drwxr-xr-x 2 root root       4096 07-31 11:22 performance_schema

    drwx------ 2 root root         71 07-31 11:22 test

    drwx------ 2 root root       4096 07-31 11:22 tp50

    -rw-r--r-- 1 root root         23 07-31 11:22 xtrabackup_binlog_info

    -rw-r----- 1 root root         95 07-31 11:22 xtrabackup_checkpoints

    -rw-r--r-- 1 root root        572 07-31 11:22 xtrabackup_info

    -rw-r----- 1 root root       2560 07-31 11:22 xtrabackup_logfile

     

    为了保证备份集中的数据一致,需要操作:

    NDMC74:~ # innobackupex --apply-log /data/backup/innobackupex/2014-07-31_11-20-44

     

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

    and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

     

    This software is published under

    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

     

    Get the latest version of Percona XtraBackup, documentation, and help resources:

    http://www.percona.com/xb/p

     

    IMPORTANT: Please check that the apply-log run completes successfully.

               At the end of a successful apply-log run innobackupex

               prints "completed OK!".

     

     

     

    140731 11:28:16  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/backup/innobackupex/2014-07-31_11-20-44/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/backup/innobackupex/2014-07-31_11-20-44 --tmpdir=/tmp

     

    xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

    xtrabackup: cd to /data/backup/innobackupex/2014-07-31_11-20-44

    xtrabackup: This target seems to be not prepared yet.

    xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(6118588398)

    xtrabackup: using the following InnoDB configuration for recovery:

    xtrabackup:   innodb_data_home_dir = ./

    xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend

    xtrabackup:   innodb_log_group_home_dir = ./

    xtrabackup:   innodb_log_files_in_group = 1

    xtrabackup:   innodb_log_file_size = 2097152

    xtrabackup: using the following InnoDB configuration for recovery:

    xtrabackup:   innodb_data_home_dir = ./

    xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend

    xtrabackup:   innodb_log_group_home_dir = ./

    xtrabackup:   innodb_log_files_in_group = 1

    xtrabackup:   innodb_log_file_size = 2097152

    xtrabackup: Starting InnoDB instance for recovery.

    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

    InnoDB: Using atomics to ref count buffer pool pages

    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: Using CPU crc32 instructions

    InnoDB: Initializing buffer pool, size = 100.0M

    InnoDB: Completed initialization of buffer pool

    InnoDB: Highest supported file format is Barracuda.

    InnoDB: 128 rollback segment(s) are active.

    InnoDB: Waiting for purge to start

    InnoDB: 5.6.17 started; log sequence number 6118588398

     

    [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 324258362, file name ./mysql-bin.000008

     

    xtrabackup: starting shutdown with innodb_fast_shutdown = 1

    InnoDB: FTS optimize thread exiting.

    InnoDB: Starting shutdown...

    InnoDB: Shutdown completed; log sequence number 6118588408

     

    140731 11:28:18  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/data/backup/innobackupex/2014-07-31_11-20-44/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/backup/innobackupex/2014-07-31_11-20-44 --tmpdir=/tmp

    for creating ib_logfile*

     

    xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

    xtrabackup: cd to /data/backup/innobackupex/2014-07-31_11-20-44

    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:1G:autoextend

    xtrabackup:   innodb_log_group_home_dir = ./

    xtrabackup:   innodb_log_files_in_group = 3

    xtrabackup:   innodb_log_file_size = 268435456

    xtrabackup: using the following InnoDB configuration for recovery:

    xtrabackup:   innodb_data_home_dir = ./

    xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend

    xtrabackup:   innodb_log_group_home_dir = ./

    xtrabackup:   innodb_log_files_in_group = 3

    xtrabackup:   innodb_log_file_size = 268435456

    xtrabackup: Starting InnoDB instance for recovery.

    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

    InnoDB: Using atomics to ref count buffer pool pages

    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: 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 256 MB

    InnoDB: Progress in MB: 100 200

    InnoDB: Setting log file ./ib_logfile1 size to 256 MB

    InnoDB: Progress in MB: 100 200

    InnoDB: Setting log file ./ib_logfile2 size to 256 MB

    InnoDB: Progress in MB: 100 200

    InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

    InnoDB: New log files created, LSN=6118588408

    InnoDB: Highest supported file format is Barracuda.

    InnoDB: 128 rollback segment(s) are active.

    InnoDB: Waiting for purge to start

    InnoDB: 5.6.17 started; log sequence number 6118588428

     

    [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 324258362, file name ./mysql-bin.000008

     

    xtrabackup: starting shutdown with innodb_fast_shutdown = 1

    InnoDB: FTS optimize thread exiting.

    InnoDB: Starting shutdown...

    InnoDB: Shutdown completed; log sequence number 6118588438

    140731 11:28:26  innobackupex: completed OK!

    NDMC74:~ #

     

     

    1. Master上创建同步账号并授权REPLICATION

    mysql> CREATE USER ‘repl‘@‘192.168.223.%‘ IDENTIFIED BY ‘replpass‘;

    Query OK, 0 rows affected (0.02 sec)

     

    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘repl‘@‘192.168.223.%‘;

    Query OK, 0 rows affected (0.02 sec)

     

    然后在Slave上测试,看看能否使用repl用户登录Master服务器:

    NDMC75:~ # mysql -h192.168.223.132 -urepl -preplpass

     

    mysql> show grants\G

    *************************** 1. row ***************************

    Grants for repl@192.168.223.%: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘repl‘@‘192.168.223.%‘ IDENTIFIED BY PASSWORD ‘*D98280F03D0F78162EBDBB9C883FC01395DEA2BF‘

    1 row in set (0.00 sec)

     

     

    1. 将Master的全备scp到Slave主机上

    NDMC74:~ # cd /data/backup/innobackupex/

    NDMC74:/data/backup/innobackupex # scp -r ./2014-07-31_11-20-44 192.168.223.133:/data/backup

     

    Slave上的MySQL停掉,然后将Master的备份文件放到Slavedatadir目录下:

    NDMC75:~ # mv /data/backup/2014-07-31_11-20-44 /data/mysql/mysql3306/data

    NDMC75:~ # cd /data/mysql/mysql3306

    NDMC75:/data/mysql/mysql3306 # chown -R mysql:mysql data

     

    注意:Slavemy.cnf文件和Master的一样,只是修改一下server-id即可。

     

    1. 启动Slave数据库

    NDMC75:~ # /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &

    NDMC75:~ # ps -ef |grep mysqld

    mysql     1293     1  1 11:50 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

    root      3124 19598  0 11:51 pts/0    00:00:00 grep mysqld

    同时查看一下error.log文件,看看有没有错误信息。

     

    1. 在slave上执行change master设置主服务器复制信息

    Master binlog信息从备份文件中可以获得:

    NDMC74:~ # cd /data/backup/innobackupex/2014-07-31_11-20-44

    NDMC74:~ # cat xtrabackup_binlog_info

    mysql-bin.000009        376                

     

    mysql> change master to master_host=‘192.168.223.132‘, master_user=‘repl‘, master_password=‘replpass‘,master_port=3306,master_log_file=‘mysql-bin.000009‘,master_log_pos=376;

    Query OK, 0 rows affected (0.05 sec)

     

    1. 在slave上启动复制

    mysql> start slave;

    Query OK, 0 rows affected (0.00 sec)

     

    1. 检查主从复制是否正常

    mysql> show slave status\G

    *************************** 1. row ***************************

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 192.168.223.132

                      Master_User: repl

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: mysql-bin.000009

              Read_Master_Log_Pos: 452

                   Relay_Log_File: mysql-relay-bin.000002

                    Relay_Log_Pos: 329

            Relay_Master_Log_File: mysql-bin.000009

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

                  Replicate_Do_DB:

              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: 452

                  Relay_Log_Space: 485

                  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: 1323306

    1 row in set (0.00 sec)

    使用xtrabackup实现MySQL主从复制,布布扣,bubuko.com

    使用xtrabackup实现MySQL主从复制

    上一篇:第二篇 -- 写一个登录界面


    下一篇:Nginx配置ssl模块踩坑与过坑案例