一、搭建数据库

1.下载数据库的yum源

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

2.使用yum下载安装数据库

yum -y install Percona-Server-server-56     Percona-Server-shared-56     percona-xtrabackup     percona-zabbix-templates     Percona-Server-devel-56     percona-toolkit     qpress

3.修改/etc/my.cnf文件

[client]
port            = 3306
socket          = /data/mysql/mysql.sock

[mysql]
prompt="\\u@\\h:\p \\R:\\m:\\s [\\d]> "
no_auto_rehash

[mysqld]
#misc
## skip_grant_tables
gtid_mode=on
enforce_gtid_consistency = 1
user = mysql
default_storage_engine = InnoDB
datadir = /data/mysql/data
##bind_address = 10.1.8.120
port = 3306
socket = /data/mysql/mysql.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/tmp

#timeout
interactive_timeout = 300
wait_timeout = 86400

#character set
character_set_server = utf8

open_files_limit = 65535
max_connections = 1000
max_connect_errors = 100

skip_name_resolve = 1
#logs
log_output = file
slow_query_log = 1
slow_query_log_file = /var/log/mysqld/mysql-slow.log
log_error = /var/log/mysqld/mysql-error.log
log_warnings = 2
pid-file = /var/run/mysqld/mysql.pid
long_query_time = 1
#log_slow_admin_statements = 1
#log_queries_not_using_indexes = 1
log_slow_slave_statements = 1


#binlog
binlog_format = row
server-id = 1203306
log_bin = /data/mysql/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 1
master_info_repository = table
expire_logs_days = 5

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates

explicit_defaults_for_timestamp = 0
#buffers & cache
table_open_cache = 2048
table_definition_cache = 4096
max_heap_table_size = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 32M
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP
max_allowed_packet = 16M

#innodb
innodb_buffer_pool_size = 1500M
innodb_buffer_pool_instances = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_purge_threads = 8
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_status_file = 1
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

innodb_checksum_algorithm = innodb
innodb_data_file_path = ibdata1:2G:autoextend
innodb_log_files_in_group = 2
innodb_log_file_size = 524288000
innodb_page_size = 16384
innodb_undo_tablespaces = 0

4.初始化数据库

useradd mysql
mkdir -pv /data/mysql/{data,logs,tmp} /var/log/mysqld/ /var/run/mysqld/
chown -R mysql:mysql /data /var/log/mysqld/ /var/run/mysqld/
mysql_install_db --defaults-file=/etc/my.cnf

5.启动mysql

systemctl start mysql

问题记录

Error-1

[ERROR] Can't start server : Bind on unix socket: Permission denied
[ERROR] Do you already have another mysqld server running on socket: /data/mysql/mysql.sock ?
mysql用户的权限不足,mysql.sock已被另一个程序运行
chown -R mysql:mysql /data/
systemctl stop mysql

Error-2

[ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 41472 pages (rounded down to MB) than specified in the .cnf file: initial 131072 pages, max 0 (relevant if non-zero) pages!
2019-11-20 14:33:11 2496 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
查看数据目录下的ibdata1文件的大小
把  innodb_data_file_path = ibdata1:2G:autoextend
改为innodb_data_file_path = ibdata1:12367974:autoextend

二、恢复数据

在从跳板机通过ssh传输数据包的过程和解压数据包的过程都要花费很长时间，而ssh终端在一段时间不动的情况下会断开连接，导致失败，即使放在后台也是一样会被kill掉，这是因为通过ssh终端创建的进程都是sshd的子进程，当终端断开连接也就是sshd进程被kill掉，子进程也会被kill掉，可以通过给命令加入()内，使该进程的父进程是init。

1.解压备份包

mkdir data
(tar -xf hins8306839_data_20191114032126.tar -C data &)

2.恢复备份日志

systemctl stop mysql
innobackupex --defaults-file=/etc/my.cnf --user=mysql --apply-log data/

3.恢复数据

innobackupex --defaults-file=/etc/my.cnf --user=mysql --copy-back data

4.验证

chown -R mysql.mysql /data
systemctl start mysql

问题记录

Error-1

InnoDB: Log file ./ib_logfile1 is of different size 1572864000 bytes than other log files 4538368 bytes!
xtrabackup: innodb_init(): Error occured.
把  innodb_log_file_size=1572864000
改为innodb_log_file_size=4538368

Error-2

Original data directory /data/mysql/data is not empty!
数据目录不为空
rm -rf /data/mysql/data/*

Error-3

Can't find file: './base/finance_refund_record.frm' (errno: 13 - Permission denied)
数据的权限不足
chown -R mysql.mysql /data

Error-4

[ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 41472 pages (rounded down to MB) than specified in the .cnf file: initial 131072 pages, max 0 (relevant if non-zero) pages!
2019-11-20 14:33:11 2496 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB

把  innodb_data_file_path = ibdata1:2G:autoextend
改为innodb_data_file_path = ibdata1:200M:autoextend

Error-5

[ERROR] InnoDB: Could not find a valid tablespace file for 'interactive/interactive_activity'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
[ERROR] InnoDB: Tablespace open failed for '"interactive"."interactive_activity"', ignored.
2019-11-20 14:43:12 7f39f871a880  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
一些数据库和表丢失，因为终端断开连接导致恢复不完全。
rm -rf /data/mysql/data/*
innobackupex --defaults-file=/etc/my.cnf --user=mysql --copy-back data

Error-6

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
[ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
[ERROR] InnoDB: The system tablespace must be writable!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
systemctl stop mysql
systemctl start mysql