Centos7: Mysql备份xtrabackup使用过程记录

最新推荐文章于 2024-10-03 21:33:49 发布

石头-豆豆

最新推荐文章于 2024-10-03 21:33:49 发布

阅读量566

点赞数

分类专栏： Mysql 文章标签： mysql centos 数据库

本文链接：https://blog.csdn.net/xjjj064/article/details/120988084

版权

Mysql 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

本文记录了在MySQL 5.7环境下，由于mysqldump备份MyISAM引擎数据失败，转而采用xtrabackup进行全库备份和恢复的过程。详细步骤包括安装xtrabackup依赖包、执行全库备份、打包备份文件、在从库上恢复数据，以及配置主从复制。在恢复过程中遇到权限问题，通过修改文件属主解决，并最终实现主从同步。

摘要由CSDN通过智能技术生成

Mysql备份xtrabackup使用过程记录

一、背景描述

公司的mysql数据库表有innodb引擎、也有MyISAM引擎，现在需要搭建从数据库使用mysqldump备份，恢复数据库时始终会报语法错误！

/usr/local/mysql/bin/mysqldump -uroot -p  --single-transaction --master-data=1 --no-autocommit --default-character-set=utf8mb4 -B jumpserver risk xxl_job zabbix > jrxz_utf8mb4_20211026.sql

根据度娘得知可能是–single-transaction 参数不支持MyISAM引擎，可能会导致数据备份不一致的情况。
这里考虑使用xtrabackup来进行数据备份恢复，搭建mysql主从。
mysqldump和xtrabackup备份原理实现说明！
参考：https://www.cnblogs.com/zhoujinyi/p/5789465.html

二、xtrabackup 版本选择

xtrabackup 2.4.24 支持Mysql5.7版本
xtrabackup 8.0 以上支持Mysql8.0版本，已移除innobackupex脚本命令。
由于数据库是Mysql5.7 所以还是使用xtrabackup 2.4.24 来进行数据备份

三、xtrabackup安装

注：系统是Centos7

3.1、安装依赖包

[root@jumpserver01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

[root@jumpserver01 ~]# yum -y install rsync perl l perl-Digest-MD5

[root@jumpserver01 ~]# wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
[root@jumpserver01 ~]# rpm -ivh libev-4.04-2.el6.x86_64.rpm

3.2、安装 xtrabackup

[root@mysql-slave-110 ~]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm

[root@mysql-slave-110 ~]# rpm -ivh ercona-release-latest.noarch.rpm
[root@mysql-slave-110 ~]# yum search xtrabackup
Percona Original release/x86_64 YUM repository                                                                                                                                    124 kB/s | 7.3 MB     01:00
Percona Original release/noarch YUM repository                                                                                                                                    3.4 kB/s | 3.6 kB     00:01
Percona Release release/noarch YUM repository                                                                                                                                     1.7 kB/s | 1.8 kB     00:01
======================================================================================= Name & Summary Matched: xtrabackup =======================================================================================
percona-xtrabackup-test-24.x86_64 : Test suite for Percona XtraBackup
percona-xtrabackup-test-80.x86_64 : Test suite for Percona XtraBackup
percona-xtrabackup-24.x86_64 : XtraBackup online backup for MySQL / InnoDB
percona-xtrabackup-80.x86_64 : XtraBackup online backup for MySQL / InnoDB
percona-xtrabackup-24-debugsource.x86_64 : Debug sources for package percona-xtrabackup-24
percona-xtrabackup-80-debugsource.x86_64 : Debug sources for package percona-xtrabackup-80
percona-xtrabackup-24-debuginfo.x86_64 : Debug information for package percona-xtrabackup-24
percona-xtrabackup-80-debuginfo.x86_64 : Debug information for package percona-xtrabackup-80
percona-xtrabackup-test-24-debuginfo.x86_64 : Debug information for package percona-xtrabackup-test-24
percona-xtrabackup-test-80-debuginfo.x86_64 : Debug information for package percona-xtrabackup-test-80
[root@mysql-slave-110 ~]# yum -y install percona-xtrabackup-24.x86_64
[root@mysql-slave-110 ~]# inno
innobackupex  innochecksum

四、完整备份mysql5.7数据库

4.1、全库备份

使用如下命令发现一直报错

innobackupex --defaults-file=/etc/mysql/my.cnf --socket=/tmp/mysql.sock --port=3301 --user=root --password=******* /opt/backup/

修改为：

innobackupex --defaults-file=/etc/mysql/my.cnf -S /tmp/mysql.sock --port=3301 --user=root --password=******* /opt/backup/

经过十来分钟的等待终于备份成功。

11027 11:55:29 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '119846970091'
xtrabackup: Stopping log copying thread.
.211027 11:55:29 >> log scanned up to (119846970100)

211027 11:55:29 Executing UNLOCK TABLES
211027 11:55:29 All tables unlocked
211027 11:55:29 [00] Copying ib_buffer_pool to /opt/backup/2021-10-27_11-47-43/ib_buffer_pool
211027 11:55:29 [00]        ...done
211027 11:55:29 Backup created in directory '/opt/backup/2021-10-27_11-47-43/'
MySQL binlog position: filename 'binlog.000082', position '298'
211027 11:55:29 [00] Writing /opt/backup/2021-10-27_11-47-43/backup-my.cnf
211027 11:55:29 [00]        ...done
211027 11:55:29 [00] Writing /opt/backup/2021-10-27_11-47-43/xtrabackup_info
211027 11:55:29 [00]        ...done
xtrabackup: Transaction log of lsn (119846970091) to (119846970100) was copied.
211027 11:55:30 completed OK!

4.2、打包备份的文件夹scp至从库服务器

[root@localhost backup]# tar -zcvf mysql.tar.gz 2021-10-27_11-47-43/
[root@localhost backup]# scp -P 51022 mysql.tar.gz root@192.168.6.252:/usr/local/src/
root@192.168.6.252's password: 
mysql.tar.gz                                100% 2762MB  34.0MB/s   01:21

4.3、从库服务器恢复数据

注：从库搭建过程省略。
删除data目录下的文件、和log目录下的文件，保证是一台空的mysql服务器能正常启动即可。
停止从数据库

准备恢复

[root@jumpserver01 src]# innobackupex --apply-log 2021-10-27_11-47-43/
InnoDB: xtrabackup: Last MySQL binlog file position 116845, file name binlog.000081
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.35 started; log sequence number 119846970389
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 119846970408

恢复数据

[root@jumpserver01 src]# innobackupex  --defaults-file=/etc/mysql3301.cnf --copy-back --rsync /usr/local/src/2021-10-27_11-47-43/
#恢复报错
181121 17:43:36 innobackupex: Error: extra argument found /allbak

解决办法：

[root@jumpserver01 mysql]# cat /etc/mysql3301.cnf 
[mysqld]
datadir=/data/mysql  #需要加入这行，以前是加载[mysql_safe]下的

加上配置之后恢复成功。

211027 14:54:11 [01]        ...done
211027 14:54:11 [01] Creating directory /data/mysql/2021-10-27_14-20-15
211027 14:54:11 [01] ...done.211027 14:54:11 [01] Creating directory /data/mysql/2021-10-27_14-24-51
211027 14:54:11 [01] ...done.211027 14:54:11 completed OK!

启动从数据库看看！
启动报错：

2021-10-27T06:58:14.853782Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-10-27T06:58:14.853813Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-10-27T06:58:14.853829Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-10-27T06:58:15.454725Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-10-27T06:58:15.454803Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-10-27T06:58:15.454821Z 0 [ERROR] Failed to initialize builtin plugins.
2021-10-27T06:58:15.454832Z 0 [ERROR] Aborting

原因分析及解决办法：

突然回忆起来恢复过来的数据文件是root 然而运行mysql的用户是mysql，所以修改下文件属主和属组就可以了。
[root@jumpserver01 mysql]# chown -R mysql:mysql /data/

五、配置主从复制

5.1、查看binlog文件和pos
备份文件中已经记录了相关信息。

[root@jumpserver01 mysql]# cat xtrabackup_binlog_pos_innodb 
binlog.000081	116845

5.2、创建复制用户并开启复制

mysql> CREATE USER 'testrepl'@'192.168.6.252' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'testrepl'@'192.168.6.252';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

5.3、从库设置change

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.6.51',
    ->     MASTER_PORT=3301,
    ->     MASTER_USER='testrepl',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_LOG_FILE='binlog.000081',
    ->     MASTER_LOG_POS=116845;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.51
                  Master_User: testrepl
                  Master_Port: 3301
                Connect_Retry: 60
              Master_Log_File: binlog.000082
          Read_Master_Log_Pos: 1176
               Relay_Log_File: jumpserver01-relay-bin.000004
                Relay_Log_Pos: 1383
        Relay_Master_Log_File: binlog.000082
             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: 1176
              Relay_Log_Space: 1641
              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: 11
                  Master_UUID: d546dfd5-fb3c-11eb-b3a6-000c2913aa6d
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

5.4、主库查看master状态

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000082
         Position: 1176
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

5.5、主库插入一条数据后再次查看状态

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000082
         Position: 1463
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

5.6、查看从库是否同步了数据

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.51
                  Master_User: testrepl
                  Master_Port: 3301
                Connect_Retry: 60
              Master_Log_File: binlog.000082
          Read_Master_Log_Pos: 1463
               Relay_Log_File: jumpserver01-relay-bin.000004
                Relay_Log_Pos: 1670
        Relay_Master_Log_File: binlog.000082
             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: 1463
              Relay_Log_Space: 1928
              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: 11
                  Master_UUID: d546dfd5-fb3c-11eb-b3a6-000c2913aa6d
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

主从搭建参考：https://blog.csdn.net/xjjj064/article/details/111869542

innobackupex 备份innodb、MyISAM表报错：
mysql.sock如果没有设置默认在 /tmp 下

innobackupex --defaults-file=/etc/mysql/my.cnf --socket=/tmp/mysql.sock --port=3301 --user=test --password=85442791 /opt/backup/

报错一：
211027 11:37:17 innobackupex: Error: extra argument found /opt/backup/