原创

Mysql_innobackupex全备份+binlog进行恢复(可用)



准备备份文件:全备份+binlog进行恢复:
配置好MySQL
解压MySQL的安装文件,把安装文件的bin目录配置到环境变量文件里面
把/usr/local/mysql/bin添加到PATH路径下。
[root@dzfptest ~]# cat /root/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
export PATH
执行生效配置文件:
source  /root/.bash_profile

查看备份文件
[root@dzfptest 102]# ll
-rw-r-----  1 root  root  72087554560 Jun 15 10:18 all.tar
drwxr-xr-x 11 mysql mysql        4096 Jul  3 13:09 base_all
-rw-r-----  1 root  root     40555266 Jun 15 10:06 master-bin.000318
-rw-r-----  1 root  root        83286 Jun 15 10:06 master-bin.000319
-rw-r-----  1 root  root        83286 Jun 15 10:06 master-bin.000320
-rw-r-----  1 root  root      1884620 Jun 15 10:06 master-bin.000321
-rw-r-----  1 root  root       598372 Jun 15 10:06 master-bin.000322
-rw-r-----  1 root  root       492040 Jun 15 10:06 master-bin.000323
-rw-r-----  1 root  root      6725783 Jun 15 10:06 master-bin.000324

解压全备份:
[root@dzfptest 102]#tar -xvf  /data/102/all.tar  -C  /data/102/base_all

复制生产环境的配置文件,一定要复制,不然有可能会报各种错误。特别是数据文件目录要和生产环境一致。
[root@dzfptest 102]# cat /etc/my.cnf
[client]
port = 3306
socket =/data/mysql/data/mysql.sock
#default-character-set=gbk

[mysql]
no-auto-rehash

[mysqld]
## enable autocommit
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
character_set_server = gbk
auto_increment_increment= 2
auto_increment_offset   = 1
lower_case_table_names=1
# system
basedir=/usr/local/mysql
datadir=/data/mysql/data/
max_allowed_packet=134217728
max_connections=8192
max_user_connections=8000
open_files_limit=65535
pid_file=/data/mysql/data/mysqld.pid
port=3306
server_id=128
skip_name_resolve=ON
socket=/data/mysql/data/mysql.sock
tmpdir=/data/mysql/tmp

# binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=15
log-bin=/data/mysql/log/binlog/master-bin
log-bin-index=/data/mysql/log/binlog/master-bin.index
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#relay
#relay-log=/data/mysql/log/relaylog/slave-relay-bin
#relay-log-index=/data/mysql/log/relaylog/slave-relay-bin.index


# LOGGING #
log_error                      = /data/mysql/log/alert.log
log_queries_not_using_indexes  = 1
slow_query_log                = 1
slow_query_log_file            = /data/mysql/log/slow.log
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#slave#
slave_skip_errors=OFF
log_slave_updates=ON

# innodb #
innodb_log_group_home_dir=/data/mysql/log/iblog
innodb_data_home_dir=/data/mysql/log/iblog
innodb_adaptive_flushing=1
#innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=8
innodb_change_buffering=inserts
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 4
innodb_log_file_size          = 100M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 5G
#innodb_file_format=Barracuda
#innodb_file_io_threads=4
innodb_flush_neighbors=0
innodb_io_capacity=200
innodb_lock_wait_timeout=5
innodb_log_buffer_size=64M
innodb_lru_scan_depth=2048
innodb_max_dirty_pages_pct=60
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=200M
innodb_open_files=200
innodb_print_all_deadlocks=1
innodb_purge_threads=4
innodb_read_ahead_threshold=0
innodb_read_io_threads=8
innodb_rollback_on_timeout=0
innodb_sort_buffer_size=2M
innodb_spin_wait_delay=6
innodb_stats_on_metadata=0
innodb_strict_mode=1
innodb_sync_array_size=256
innodb_sync_spin_loops=30
innodb_thread_concurrency=64
innodb_use_native_aio=0
innodb_write_io_threads=8
innodb_support_xa=1


给配置文件准备好目录和权限
[root@dzfptest 102]#mkdir  -p  /data/mysql/data/
[root@dzfptest 102]# mkdir -p /data/mysql/tmp
[root@dzfptest 102]# mkdir -p /data/mysql/log/binlog
[root@dzfptest 102]# mkdir -p /data/mysql/log/iblog
[root@dzfptest 102]# chown -R mysql:mysql /data/mysql/data/
[root@dzfptest 102]# chown -R mysql:mysql /data/mysql/tmp
[root@dzfptest 102]# chown -R mysql:mysql /data/mysql/log/binlog
[root@dzfptest 102]# chown -R mysql:mysql /data/mysql/log/iblog
[root@dzfptest 102]# touch /data/mysql/log/alert.log
[root@dzfptest 102]# chown mysql:mysql  /data/mysql/log/alert.log

 
准备(prepare)一个完全备份, --apply-log  , /data/102/base_all2为全备份的目录
[root@dzfptest 102]#innobackupex --defaults-file=/etc/my.cnf  --apply-log?  /data/102/base_all

拷贝文件到/etc/my.cnf配置文件的数据目录下:
 [root@dzfptest base_all]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/102/base_all/

查看binlog的信息
[root@dzfptest base_all]# cat xtrabackup_binlog_info
master-bin.000318       37811760

启动MySQL
[root@dzfptest 102]# chown -R mysql:mysql /data/mysql/data/
[root@dzfptest base_all]#/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

查看MySQL的进程是否存在
[root@dzfptest base_all]#ps -ef|grep mysql

恢复binlog日志数据:
[root@dzfptest base_all]# mysqlbinlog /data/102/master-bin.000318 --start-position='37811760' |mysql -uroot -p
[root@dzfptest base_all]#mysqlbinlog /data/102/master-bin.000319 | mysql -uroot -p -P3306
[root@dzfptest base_all]# mysqlbinlog /data/102/master-bin.000321 |mysql -uroot -p  -P3306
[root@dzfptest base_all]# mysqlbinlog /data/102/master-bin.000321 |mysql -uroot -p  -P3306
[root@dzfptest base_all]# mysqlbinlog /data/102/master-bin.000322 |mysql -uroot -p  -P3306
[root@dzfptest base_all]# mysqlbinlog /data/102/master-bin.000323 |mysql -uroot -p  -P3306

正文到此结束
Loading...