乘风原创程序

  • MySQL5.7热备份-xtrabackup
  • 2020/6/21 23:40:17
  • 热备份
    1 xtrabackup下载

    wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
    [root@Master_test ~]# ll
    total 88192
    -rw-------. 1 root root     1201 Feb 16  2017 anaconda-ks.cfg
    -rw-r--r--. 1 root root     9913 Feb 16  2017 install.log
    -rw-r--r--. 1 root root     3161 Feb 16  2017 install.log.syslog
    -rw-r--r--. 1 root root 90282874 Nov 23  2017 percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz

    2 目录结构

    [root@Master_test bin]# ll
    total 212008
    lrwxrwxrwx. 1 root root        10 Jul  6 23:21 innobackupex -> xtrabackup
    -rwxr-xr-x. 1 root root   5357661 Nov 23  2017 xbcloud
    -rwxr-xr-x. 1 root root      3020 Nov 23  2017 xbcloud_osenv
    -rwxr-xr-x. 1 root root   5270021 Nov 23  2017 xbcrypt
    -rwxr-xr-x. 1 root root   5344286 Nov 23  2017 xbstream
    -rwxr-xr-x. 1 root root 201111701 Nov 23  2017 xtrabackup
    [root@Master_test bin]# pwd
    /root/percona-xtrabackup-2.4.9-Linux-x86_64/bin
    [root@Master_test bin]# 

    说明:
    innobackupex 是要使用的备份工具
    xtrabackup是被封装在innobackupex之中,,innobackupex运行时需要调用它。

    3 创建测试数据库和表

    root@Master 23:59:  [(none)]> create database db1;
    Query OK, 1 row affected (0.08 sec)
    
    root@Master 23:59:  [(none)]> use db1
    Database changed
    root@Master 23:59:  [db1]> 
    root@Master 23:59:  [db1]> create table  t1(id int,name varchar(20));
    Query OK, 0 rows affected (0.08 sec)
    
    root@Master 00:00:  [db1]> desc t1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    
    root@Master 00:00:  [db1]> 
    root@Master 00:00:  [db1]> insert into t1(id, name) values(1,'zhangsan');
    Query OK, 1 row affected (0.00 sec)
    
    root@Master 00:01:  [db1]> insert into t1(id, name) values(2,'lisi');
    Query OK, 1 row affected (0.00 sec)
    
    root@Master 00:01:  [db1]> insert into t1(id, name) values(3,'wangwu');
    Query OK, 1 row affected (0.00 sec)
    
    root@Master 00:01:  [db1]> select * from t1;
    +------+----------+
    | id   | name     |
    +------+----------+
    |    1 | zhangsan |
    |    2 | lisi     |
    |    3 | wangwu   |
    +------+----------+
    3 rows in set (0.00 sec)

    4 全量备份

    1)执行报错

    [root@Master_test bin]# ./innobackupex --user=root --password-root --defaults-file=/etc/my.cnf /bak
    ./innobackupex: /usr/lib64/libstdc++.so.6: version `GLIBCXX_3.4.15' not found (required by ./innobackupex)'

    2)解决错误
    呈现该错误的原因是当前的GCC版本中,没有GLIBCXX_3.4.15,须要安装更高版本

    [root@Master_test bin]# strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX
    GLIBCXX_3.4
    GLIBCXX_3.4.1
    GLIBCXX_3.4.2
    GLIBCXX_3.4.3
    GLIBCXX_3.4.4
    GLIBCXX_3.4.5
    GLIBCXX_3.4.6
    GLIBCXX_3.4.7
    GLIBCXX_3.4.8
    GLIBCXX_3.4.9
    GLIBCXX_3.4.10
    GLIBCXX_3.4.11
    GLIBCXX_3.4.12
    GLIBCXX_3.4.13
    GLIBCXX_FORCE_NEW
    GLIBCXX_DEBUG_MESSAGE_LENGTH

    可见没有GLIBCXX_3.4.15.

    3)下载libstdc
    新版本,地址:
    32bit
    http://ftp.de.debian.org/debian/pool/main/g/gcc-4.7/libstdc++6_4.7.2-5_i386.deb
    64bit
    http://ftp.de.debian.org/debian/pool/main/g/gcc-4.7/libstdc++6_4.7.2-5_amd64.deb

    4)安装libstdc

    [root@Master_test ~]# wget  http://ftp.de.debian.org/debian/pool/main/g/gcc-4.7/libstdc++6_4.7.2-5_amd64.deb
    解压:
    [root@Master_test ~]# ar -x libstdc++6_4.7.2-5_amd64.deb  && tar xvf data.tar.gz
    ./usr/
    ./usr/share/
    ./usr/share/doc/
    ./usr/lib/
    ./usr/lib/x86_64-linux-gnu/
    ./usr/lib/x86_64-linux-gnu/libstdc++.so.6.0.17
    ./usr/share/doc/libstdc++6
    ./usr/lib/x86_64-linux-gnu/libstdc++.so.6

    5)安装步骤
    切到解决后的目录,把libstdc++.so.6.0.17拷贝到系统的/usr/lib64下,然后删除原libstdc++.so.6,把libstdc++.so.6.0.17,软链接为libstdc++.so.6。

    cd /root/usr/lib/x86_64-linux-gnu
    [root@Master_test x86_64-linux-gnu]# ll
    total 972
    lrwxrwxrwx. 1 root root     19 Jul  7 01:18 libstdc++.so.6 -> libstdc++.so.6.0.17
    -rw-r--r--. 1 root root 991600 Jan  7  2013 libstdc++.so.6.0.17
    cp libstdc++.so.6.0.17 /usr/lib64
    cd /usr/lib64
    rm libstdc++.so.6
    ln libstdc++.so.6.0.17 libstdc++.so.6
    检查一下
    [root@Master_test x86_64-linux-gnu]# strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX  
    GLIBCXX_3.4
    GLIBCXX_3.4.1
    GLIBCXX_3.4.2
    GLIBCXX_3.4.3
    GLIBCXX_3.4.4
    GLIBCXX_3.4.5
    GLIBCXX_3.4.6
    GLIBCXX_3.4.7
    GLIBCXX_3.4.8
    GLIBCXX_3.4.9
    GLIBCXX_3.4.10
    GLIBCXX_3.4.11
    GLIBCXX_3.4.12
    GLIBCXX_3.4.13
    GLIBCXX_3.4.14
    GLIBCXX_3.4.15
    GLIBCXX_3.4.16
    GLIBCXX_3.4.17
    GLIBCXX_DEBUG_MESSAGE_LENGTH
    已经有了GLIBCXX_3.4.15。

    5 执行全量备份
    --user:连接数据库的用户名
    --password:连接数据的密码
    --defaults-file:配置文件(必须放在最前面,否则会报错)
    /bak:备份文件的位置

    [root@Master_test bin]# ./innobackupex  --defaults-file=/etc/my.cnf --user=root --password=root /bak
    180707 01:28:04 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!".
    
    180707 01:28:04  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
    Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.
    180707 01:28:04 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
    Using server version 5.7.22-log
    ./innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /data/mysql/mysql3306/data
    xtrabackup: open files limit requested 65535, set to 65535
    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 = 2
    xtrabackup:   innodb_log_file_size = 1073741824
    xtrabackup: using O_DIRECT
    InnoDB: Number of pools: 1
    180707 01:28:05 >> log scanned up to (2597729)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 24 for db1/t1, old maximum was 0
    180707 01:28:05 [01] Copying ./ibdata1 to /bak/2018-07-07_01-28-04/ibdata1
    180707 01:28:06 >> log scanned up to (2597729)
    180707 01:28:07 >> log scanned up to (2597729)
    180707 01:28:08 >> log scanned up to (2597729)
    省略中间信息*****************************************************************
    180707 01:28:45 [01] Copying ./sys/statements_with_sorting.frm to /bak/2018-07-07_01-28-04/sys/statements_with_sorting.frm
    180707 01:28:45 [01]        ...done
    180707 01:28:45 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /bak/2018-07-07_01-28-04/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
    180707 01:28:45 [01]        ...done
    180707 01:28:45 >> log scanned up to (2597729)
    180707 01:28:45 [01] Copying ./sys/x@0024wait_classes_global_by_avg_latency.frm to /bak/2018-07-07_01-28-04/sys/x@0024wait_classes_global_by_avg_latency.frm
    180707 01:28:45 [01]        ...done
    180707 01:28:45 Finished backing up non-InnoDB tables and files
    180707 01:28:45 [00] Writing /bak/2018-07-07_01-28-04/xtrabackup_binlog_info
    180707 01:28:45 [00]        ...done
    180707 01:28:45 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '2597720'
    xtrabackup: Stopping log copying thread.
    .180707 01:28:45 >> log scanned up to (2597729)
    
    180707 01:28:46 Executing UNLOCK TABLES
    180707 01:28:46 All tables unlocked
    180707 01:28:46 [00] Copying ib_buffer_pool to /bak/2018-07-07_01-28-04/ib_buffer_pool
    180707 01:28:46 [00]        ...done
    180707 01:28:46 Backup created in directory '/bak/2018-07-07_01-28-04/'
    MySQL binlog position: filename 'mybinlog.000003', position '1311', GTID of the last change 'fe259816-8122-11e8-ab02-000c29fa3266:1-6'
    180707 01:28:46 [00] Writing /bak/2018-07-07_01-28-04/backup-my.cnf
    180707 01:28:46 [00]        ...done
    180707 01:28:46 [00] Writing /bak/2018-07-07_01-28-04/xtrabackup_info
    180707 01:28:46 [00]        ...done
    xtrabackup: Transaction log of lsn (2597720) to (2597729) was copied.
    180707 01:28:46 completed OK!