首页 > 代码库 > 使用xtrabackup备份和还原mysql的多实例（基于全备）
使用xtrabackup备份和还原mysql的多实例（基于全备）
1）安装percona-xtrabackup
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份 工具InnoDB Hotbackup的一个很好的替代品 # 下载安装包： # cd /usr/local/src # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/redhat/6/x86_64/percona-xtrabackup-2.3.2-1.el6.x86_64.rpm # 安装依赖库： # yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL # wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm # rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm # rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm # 创建备份的目录： # mkdir -p /databackup/fullbackup # mkdir -p /databackup/increasebackup # 创建备份用户和授权： mysql>grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *.* to backup@‘localhost‘ identified by ‘MANAGER‘;
2) 备份3306实例：
# innobackupex --defaults-file=/data/3306/my.cnf --socket=/data/3306/mysql.sock --user=backup --password=‘MANAGER‘ /mysqlbackup/fullbackup/3306 说明： --defaults-file，指定mysql实例的配置文件 --socket，指定socket的位置 --user，指定备份的用户 --password，指定用户名称 /mysqlbackup/fullbackup/3306，这里我们将实例备份到/mysqlbackup/fullbackup/3306目录下 # du -sh /mysqlbackup/fullbackup/3306 130M/mysqlbackup/fullbackup/3306 # ls -lrt /mysqlbackup/fullbackup/3306/* 总用量 131136 -rw-r----- 1 root root 134217728 10月 12 04:32 ibdata1 drwx------ 2 root root 4096 10月 12 04:32 james drwx------ 2 root root 4096 10月 12 04:32 ems drwx------ 2 root root 4096 10月 12 04:32 mysql drwx------ 2 root root 4096 10月 12 04:32 performance_schema drwx------ 2 root root 4096 10月 12 04:32 wh410 drwx------ 2 root root 4096 10月 12 04:32 www drwx------ 2 root root 4096 10月 12 04:32 blog drwx------ 2 root root 4096 10月 12 04:32 opark drwx------ 2 root root 4096 10月 12 04:32 dawnpro drwx------ 2 root root 4096 10月 12 04:32 eip drwx------ 2 root root 4096 10月 12 04:32 hangzhou_dawnpro -rw-r----- 1 root root 21 10月 12 04:32 xtrabackup_binlog_info -rw-r----- 1 root root 2560 10月 12 04:32 xtrabackup_logfile -rw-r----- 1 root root 113 10月 12 04:32 xtrabackup_checkpoints -rw-r----- 1 root root 386 10月 12 04:32 backup-my.cnf -rw-r----- 1 root root 551 10月 12 04:32 xtrabackup_info
3）模拟3306数据文件丢失
# rm -rf /data/3306/data # lsof -i :3306 说明：此时数据库已经没法正常运行，检查错误日志，发现没有了data目录 161012 04:40:35 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended 161012 04:42:10 mysqld_safe Starting mysqld daemon with databases from /data/3306/data 161012 4:42:10 [Warning] Can‘t create test file /data/3306/data/mysql-master.lower-test 161012 4:42:10 [Warning] Can‘t create test file /data/3306/data/mysql-master.lower-test /application/mysql-5.5.32/bin/mysqld: Can‘t change dir to ‘/data/3306/data/‘ (Errcode: 2) 161012 4:42:10 [ERROR] Aborting 161012 4:42:10 [Note] /application/mysql-5.5.32/bin/mysqld: Shutdown complete 161012 04:42:10 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended
4）mysql全备数据恢复：
# mkdir -p /data/3306/data # 对于一般恢复，都是直接用备份文件还原，如果我们这里也是直接运用该备份文件，则可能会导致 一些意想不到的问题，比如:备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据 文件中的事务。因此，此时数据文件处于不一致的状态，我们现在就是要通过回滚未提交的事务及同步 已经提交的事务至数据文件也使得数据文件处于一致性状态。所以我们要用如下命令进行检查： # innobackupex --apply-log --redo-only /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/ 161012 04:53:07 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0) xtrabackup: cd to /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/ xtrabackup: This target seems to be already prepared with --apply-log-only. 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:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 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: Memory barrier is not used 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. xtrabackup: Last MySQL binlog file position 2442, file name /data/3306/mysql-bin.000008 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1606678 161012 04:53:08 completed OK! # innobackupex --defaults-file=/data/3306/my.cnf --copy-back /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/ 161012 04:56:32 innobackupex: Starting the copy-back operation IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0) 161012 04:56:32 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0 161012 04:56:33 [01] ...done 161012 04:56:33 [01] Copying ib_logfile1 to /data/3306/data/ib_logfile1 161012 04:56:34 [01] ...done 161012 04:56:34 [01] Copying ibdata1 to /data/3306/data/ibdata1 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./james/db.opt to /data/3306/data/james/db.opt 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./ems/db.opt to /data/3306/data/ems/db.opt 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/func.MYD to /data/3306/data/mysql/func.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/time_zone.MYD to /data/3306/data/mysql/time_zone.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/help_topic.MYD to /data/3306/data/mysql/help_topic.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/tables_priv.MYI to /data/3306/data/mysql/tables_priv.MYI 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/db.MYD to /data/3306/data/mysql/db.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/proc.frm to /data/3306/data/mysql/proc.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/help_relation.MYI to /data/3306/data/mysql/help_relation.MYI 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/proxies_priv.MYI to /data/3306/data/mysql/proxies_priv.MYI 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/func.frm to /data/3306/data/mysql/func.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/plugin.MYD to /data/3306/data/mysql/plugin.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/user.frm to /data/3306/data/mysql/user.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/time_zone_transition.MYD to /data/3306/data/mysql/time_zone_transition.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/time_zone_transition_type.MYD to /data/3306/data/mysql/time_zone_transition_type.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/general_log.CSV to /data/3306/data/mysql/general_log.CSV 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/event.MYI to /data/3306/data/mysql/event.MYI 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/time_zone_leap_second.frm to /data/3306/data/mysql/time_zone_leap_second.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/help_relation.frm to /data/3306/data/mysql/help_relation.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/ndb_binlog_index.MYD to /data/3306/data/mysql/ndb_binlog_index.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/procs_priv.frm to /data/3306/data/mysql/procs_priv.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/db.frm to /data/3306/data/mysql/db.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/event.frm to /data/3306/data/mysql/event.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/plugin.MYI to /data/3306/data/mysql/plugin.MYI 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/help_category.frm to /data/3306/data/mysql/help_category.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/time_zone_name.MYD to /data/3306/data/mysql/time_zone_name.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/event.MYD to /data/3306/data/mysql/event.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/proc.MYI to /data/3306/data/mysql/proc.MYI 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/tables_priv.MYD to /data/3306/data/mysql/tables_priv.MYD 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/servers.frm to /data/3306/data/mysql/servers.frm 161012 04:56:36 [01] ...done 161012 04:56:36 [01] Copying ./mysql/time_zone_leap_second.MYI to /data/3306/data/mysql/time_zone_leap_second.MYI 161012 04:56:36 [01] ...done 161012 04:56:37 [01] Copying ./mysql/proxies_priv.MYD to /data/3306/data/mysql/proxies_priv.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/procs_priv.MYI to /data/3306/data/mysql/procs_priv.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone.MYI to /data/3306/data/mysql/time_zone.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/slow_log.CSV to /data/3306/data/mysql/slow_log.CSV 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_transition.frm to /data/3306/data/mysql/time_zone_transition.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/procs_priv.MYD to /data/3306/data/mysql/procs_priv.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_topic.MYI to /data/3306/data/mysql/help_topic.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/user.MYI to /data/3306/data/mysql/user.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_keyword.MYD to /data/3306/data/mysql/help_keyword.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/db.MYI to /data/3306/data/mysql/db.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/func.MYI to /data/3306/data/mysql/func.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/servers.MYI to /data/3306/data/mysql/servers.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_category.MYD to /data/3306/data/mysql/help_category.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/host.MYI to /data/3306/data/mysql/host.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone.frm to /data/3306/data/mysql/time_zone.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_category.MYI to /data/3306/data/mysql/help_category.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/proxies_priv.frm to /data/3306/data/mysql/proxies_priv.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/general_log.frm to /data/3306/data/mysql/general_log.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_keyword.frm to /data/3306/data/mysql/help_keyword.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/proc.MYD to /data/3306/data/mysql/proc.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/columns_priv.MYI to /data/3306/data/mysql/columns_priv.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/slow_log.frm to /data/3306/data/mysql/slow_log.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_topic.frm to /data/3306/data/mysql/help_topic.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_keyword.MYI to /data/3306/data/mysql/help_keyword.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_name.MYI to /data/3306/data/mysql/time_zone_name.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/host.MYD to /data/3306/data/mysql/host.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/columns_priv.MYD to /data/3306/data/mysql/columns_priv.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/user.MYD to /data/3306/data/mysql/user.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/ndb_binlog_index.frm to /data/3306/data/mysql/ndb_binlog_index.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/tables_priv.frm to /data/3306/data/mysql/tables_priv.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/general_log.CSM to /data/3306/data/mysql/general_log.CSM 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_leap_second.MYD to /data/3306/data/mysql/time_zone_leap_second.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/servers.MYD to /data/3306/data/mysql/servers.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/host.frm to /data/3306/data/mysql/host.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_name.frm to /data/3306/data/mysql/time_zone_name.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/help_relation.MYD to /data/3306/data/mysql/help_relation.MYD 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_transition.MYI to /data/3306/data/mysql/time_zone_transition.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/slow_log.CSM to /data/3306/data/mysql/slow_log.CSM 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/ndb_binlog_index.MYI to /data/3306/data/mysql/ndb_binlog_index.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/columns_priv.frm to /data/3306/data/mysql/columns_priv.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_transition_type.MYI to /data/3306/data/mysql/time_zone_transition_type.MYI 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/time_zone_transition_type.frm to /data/3306/data/mysql/time_zone_transition_type.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./mysql/plugin.frm to /data/3306/data/mysql/plugin.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /data/3306/data/performance_schema/events_waits_summary_by_instance.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/file_instances.frm to /data/3306/data/performance_schema/file_instances.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/performance_timers.frm to /data/3306/data/performance_schema/performance_timers.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /data/3306/data/performance_schema/file_summary_by_event_name.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /data/3306/data/performance_schema/events_waits_summary_by_thread_by_event_name.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/events_waits_current.frm to /data/3306/data/performance_schema/events_waits_current.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/setup_instruments.frm to /data/3306/data/performance_schema/setup_instruments.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/threads.frm to /data/3306/data/performance_schema/threads.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/file_summary_by_instance.frm to /data/3306/data/performance_schema/file_summary_by_instance.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/events_waits_history.frm to /data/3306/data/performance_schema/events_waits_history.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/rwlock_instances.frm to /data/3306/data/performance_schema/rwlock_instances.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/setup_consumers.frm to /data/3306/data/performance_schema/setup_consumers.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/setup_timers.frm to /data/3306/data/performance_schema/setup_timers.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /data/3306/data/performance_schema/events_waits_summary_global_by_event_name.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/mutex_instances.frm to /data/3306/data/performance_schema/mutex_instances.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/events_waits_history_long.frm to /data/3306/data/performance_schema/events_waits_history_long.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/cond_instances.frm to /data/3306/data/performance_schema/cond_instances.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./performance_schema/db.opt to /data/3306/data/performance_schema/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./wh410/db.opt to /data/3306/data/wh410/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./www/db.opt to /data/3306/data/www/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./xtrabackup_info to /data/3306/data/xtrabackup_info 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./blog/db.opt to /data/3306/data/blog/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/3306/data/xtrabackup_binlog_pos_innodb 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./opark/person.frm to /data/3306/data/opark/person.frm 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./opark/db.opt to /data/3306/data/opark/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./dawnpro/db.opt to /data/3306/data/dawnpro/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./eip/db.opt to /data/3306/data/eip/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 [01] Copying ./hangzhou_dawnpro/db.opt to /data/3306/data/hangzhou_dawnpro/db.opt 161012 04:56:37 [01] ...done 161012 04:56:37 completed OK! 说明：innobackup的copy-back用于执行恢复操作时，它是通过复制所有数据相关文件至MySQL数据目录，因此，需要清空数据目录。 [root@mysql-master data]# ll /data/3306/data 总用量 229432 drwx------ 2 root root 4096 10月 12 04:56 blog drwx------ 2 root root 4096 10月 12 04:56 dawnpro drwx------ 2 root root 4096 10月 12 04:56 eip drwx------ 2 root root 4096 10月 12 04:56 ems drwx------ 2 root root 4096 10月 12 04:56 hangzhou_dawnpro -rw-r----- 1 root root 134217728 10月 12 04:56 ibdata1 -rw-r----- 1 root root 50331648 10月 12 04:56 ib_logfile0 -rw-r----- 1 root root 50331648 10月 12 04:56 ib_logfile1 drwx------ 2 root root 4096 10月 12 04:56 james drwx------ 2 root root 4096 10月 12 04:56 mysql drwx------ 2 root root 4096 10月 12 04:56 opark drwx------ 2 root root 4096 10月 12 04:56 performance_schema drwx------ 2 root root 4096 10月 12 04:56 wh410 drwx------ 2 root root 4096 10月 12 04:56 www -rw-r----- 1 root root 33 10月 12 04:56 xtrabackup_bin
5）更改数据库目录权限
# chown -R mysql.mysql /data/3306/data 这一步很重要，一定要记得
6) 开启mysql服务
# /data/3306/mysql start 启动mysql发现没法起来，检查日志信息如下（报错信息：InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes）： 161012 5:00:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins 161012 5:00:15 InnoDB: Compressed tables use zlib 1.2.3 161012 5:00:15 InnoDB: Using Linux native AIO 161012 5:00:15 InnoDB: Initializing buffer pool, size = 32.0M 161012 5:00:15 InnoDB: Completed initialization of buffer pool InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes InnoDB: than specified in the .cnf file 0 4194304 bytes! 161012 5:00:15 [ERROR] Plugin ‘InnoDB‘ init function returned error. 161012 5:00:15 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed. 161012 5:00:15 [ERROR] Unknown/unsupported storage engine: InnoDB 161012 5:00:15 [ERROR] Aborting 161012 5:00:15 [Note] /application/mysql-5.5.32/bin/mysqld: Shutdown complete 161012 05:00:15 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended
解决方法：
# rm ib_logfile* -rf 推荐的方法：不删除而直接重命名 # /data/3306/mysql start # netstat -tunlp| grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 29697/mysqld
7) 进入数据库进行验证
# mysql -uroot -predhat12345 -S /data/3306/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | blog | | dawnpro | | eip | | ems | | hangzhou_dawnpro | | james | | mysql | | opark | | performance_schema | | wh410 | | www | +--------------------+ 12 rows in set (0.01 sec)
参考了两篇写的好的文章：
http://www.2cto.com/database/201411/351118.html
http://mp.weixin.qq.com/s?__biz=MzIyMDA1MzgyNw==&mid=2651968730&idx=1&sn=c80ddf66f6248b23775f4f732b017a8c&chksm=8c349cf6bb4315e0b3e18053befcc1d5a8305104d3838aeec68b402e7413c5883da8030beca3&mpshare=1&scene=1&srcid=10136XfRadTOysNpsHivaxiF#rd
感谢作者的分享
本文出自 “冰冻vs西瓜” 博客，请务必保留此出处http://molewan.blog.51cto.com/287340/1861487
使用xtrabackup备份和还原mysql的多实例（基于全备）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。