innobackupex数据库备份
3 min read

innobackupex数据库备份

参考文档:https://cloud.tencent.com/developer/article/1119183

参考文档:https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/innobackupex_option_reference.html

参考文档:https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

参考文档:https://www.cnblogs.com/linuxk/p/9372990.html

参考文档:https://blog.csdn.net/shaochenshuo/article/details/50151271

xtrabackup这个备份工具只可以备份innodb引擎的数据库;但是我们也需要备份myisam引擎的数据库,然后就出来了一个工具:innobackupex,也就是我们今天的主角。

前提:在从库搭建一个nfs服务,将nfs服务挂载至主库,导出的数据直接落在nfs上,防止后续还要手动传输一遍。
master操作:

mount -t nfs 10.0.0.18:/data/inn /inn
innobackupex /inn/ --parallel=8   --user=root --password='AxJLNeHC1559XXX'

输出:

innobackupex: Backup created in directory '/path/to/BACKUP-DIR/2013-03-25_00-00-09'
innobackupex: MySQL binlog position: filename 'mysql-bin.012331', position 4878255
111225 00:00:53  innobackupex: completed OK!

位置信息也可以通过以下方式查看:

# cat xtrabackup_info
uuid = a34229bc-3323-11eb-8ea2-af165e97d875
name =
tool_name = innobackupex
tool_command = /inn/ --parallel=8 --user=root --password=...
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 5.5.59-0ubuntu0.14.04.1-log
start_time = 2020-11-30 21:53:01
end_time = 2020-11-30 23:49:32
lock_time = 0
binlog_pos = filename 'mysql-bin.012331', position '4878255'
innodb_from_lsn = 0
innodb_to_lsn = 8608972774059
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

创建replication用户

> grant replication client,replication slave on *.* TO 'rep'@'%' identified by 'replication';
> flush privileges;

slave操作:

# innobackupex --apply-log /data/inn/2020-12-01_11-28-58/    #合并数据,使数据文件处于一致性的状态
# mv /data/mysql/data /data/mysql/data_bak     # 备份原有的数据,导入之前需保证数据目录是空目录
# mkdir -pv /data/mysql/data
# innobackupex --move-back /data/inn/2020-12-01_11-28-58/    #恢复数据,由于数据库数据文件高达4T,磁盘容量有限,就没有使用--copy-back,直接使用了--move-back。
# chown -R mysql:mysql /data/mysql

遇到的问题:

201201 18:42:23 [Note] Plugin 'FEDERATED' is disabled.
201201 18:42:23 InnoDB: The InnoDB memory heap is disabled
201201 18:42:23 InnoDB: Mutexes and rw_locks use GCC atomic builtins
201201 18:42:23 InnoDB: Compressed tables use zlib 1.2.3
201201 18:42:23 InnoDB: Using Linux native AIO
201201 18:42:23 InnoDB: Initializing buffer pool, size = 128.0M
201201 18:42:23 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
201201 18:42:23 [ERROR] Plugin 'InnoDB' init function returned error.
201201 18:42:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
201201 18:42:23 [ERROR] Unknown/unsupported storage engine: InnoDB
201201 18:42:23 [ERROR] Aborting

201201 18:42:23 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

解决方法:

# cd /data/mysql/data
# mv ib_logfile0 ib_logfile0_bak
# mv ib_logfile1 ib_logfile1_bak
# /etc/init.d/mysql start

执行change master to语句

> stop slave;
> reset slave all;
> change master to master_host='10.0.0.19',master_port=3306,master_user='rep',master_password='replication',MASTER_LOG_FILE='mysql-bin.012344', MASTER_LOG_POS=49944553;
> start slave;
> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.19
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.012346
          Read_Master_Log_Pos: 81322669
               Relay_Log_File: mysql-relay-bin.000006
                Relay_Log_Pos: 81322815
        Relay_Master_Log_File: mysql-bin.012346
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 81322669
              Relay_Log_Space: 81323014
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 18
1 row in set (0.00 sec)
京ICP备19055754号