MySQL5.7热备份-xtrabackup
热备份
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.gz2 目录结构
[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.65)安装步骤
切到解决后的目录，把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!分享题目：MySQL5.7热备份-xtrabackup
当前网址：http://myzitong.com/article/pgeipc.html