MySQL数据库备份并添加主从复制

guozhe

2022-03-29

MySQL

背景

由于公司iaas人员在操作虚拟ip时手误，导致应该指向mysql主库的虚拟ip指向了从库。因为业务app使用的是虚拟ip链接的数据库，因此导致所有的业务数据都写入了从库，而主库的数据从此没有任何变化。

为了解决此问题，需要把原来的主库变成从库，从而继续保持mysql数据库的高可用；但是在处理的时候发现由于旧从库配置的bin-log超时时间为：expire-logs-days = 3，这导致了3天前的bin-log已经因为过期而被清除。所以如果直接把旧的主库当作从库来进行主从复制，会导数据不一致。

因此最终需要把旧主库的数据全部清除，然后从旧从库导出所有数据再导入旧主库，最后再添加主从复制来达到此目的。

基本信息

旧主库ip：172.99.0.32

旧从库ip：172.99.0.31

具体操作如下

因为一共有三个数据库，查看数据库的data目录共200G以上的数据量，因此不能使用mysqldump来进行导入和导出备份。最终确定使用Percona XtraBackup工具进行备份和恢复数据。

安装Percona XtraBackup

参考官方文档：

# 1.Install the percona-release configuration tool
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 2.Testing the repository
yum list | grep percona

# 3.Enable the repository:
percona-release enable-only tools release

# 4.Install Percona XtraBackup
yum install percona-xtrabackup-24

错误处理

$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Loaded plugins: fastestmirror
Setting up Install Process
percona-release-latest.noarch.rpm                                                                                                                                                    |  20 kB     00:00
Examining /var/tmp/yum-root-3a5UYo/percona-release-latest.noarch.rpm: percona-release-1.0-27.noarch
Marking /var/tmp/yum-root-3a5UYo/percona-release-latest.noarch.rpm to be installed
Determining fastest mirrors
YumRepo Error: All mirror URLs are not using ftp, http[s] or file.
 Eg. Invalid release/repo/arch combination/
removing mirrorlist with no valid mirrors: /var/cache/yum/x86_64/6/base/mirrorlist.txt
Error: Cannot find a valid baseurl for repo: base

解决方案：

把源替换为清华大学源，参考文档
查看当前系统的版本：more /etc/issue
如果sudo yum makecache报错，先执行yum clean all
如果sudo yum makecache依旧报错（[Errno 14] Peer cert cannot be verified or peer cert invalid），追加sslverify=false导/etc/yum.conf文件

$ sudo yum makecache
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/metalink                                                                                                                                                                        | 4.4 kB     00:00
 * epel: ftp.iij.ad.jp
https://mirrors.tuna.tsinghua.edu.cn/centos-vault/6.8/os/x86_64/repodata/repomd.xml: [Errno 14] Peer cert cannot be verified or peer cert invalid
Trying other mirror.
It was impossible to connect to the Red Hat servers.
This could mean a connectivity issue in your environment, such as the requirement to configure a proxy,
or a transparent proxy that tampers with TLS security, or an incorrect system clock.
Please collect information about the specific failure that occurs in your environment,
using the instructions in: https://access.redhat.com/solutions/1527033 and open a ticket with Red Hat Support.

Error: Cannot retrieve repository metadata (repomd.xml) for repository: base. Please verify its path and try again

使用Percona XtraBackup

参考官方文档

备份master

1	innobackupex --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=127.0.0.1 --port=3306 --user=root --password=123456 /data/backup

查看备份文件：

$ ll
total 8
drwxr-x--- 5 root root 4096 Mar 25 11:34 2022-03-25_10-56-55
drwxr-x--- 7 root root 4096 Mar 25 15:10 2022-03-25_14-49-59


$ ll 2022-03-25_14-49-59
total 2108548
-rw-r----- 1 root root        437 Mar 25 15:10 backup-my.cnf
drwxr-x--- 2 root root      16384 Mar 25 15:10 db1
drwxr-x--- 2 root root     176128 Mar 25 15:10 db2
drwxr-x--- 2 root root       8192 Mar 25 15:10 db3
-rw-r----- 1 root root   10656571 Mar 25 15:10 ib_buffer_pool
-rw-r----- 1 root root 2147483648 Mar 25 14:50 ibdata1
drwxr-x--- 2 root root       4096 Mar 25 15:10 mysql
drwxr-x--- 2 root root       4096 Mar 25 15:10 performance_schema
-rw-r----- 1 root root         46 Mar 25 15:10 xtrabackup_binlog_info
-rw-r----- 1 root root        150 Mar 25 15:10 xtrabackup_checkpoints
-rw-r----- 1 root root        630 Mar 25 15:10 xtrabackup_info
-rw-r----- 1 root root     731648 Mar 25 15:10 xtrabackup_logfile

数据文件一致性

1	innobackupex --apply-log /data/backup/2022-03-25_14-49-59

数据恢复

把备份数据copy导32服务器

1	scp -r /data/backup/2022-03-25_14-49-59/ iaas@172.99.0.32:/data/backup_20220325

停止mysql

1
2
3

sudo su

mysqladmin --port=3306 -uroot -p12345 -h127.0.0.1 shutdown

备份和删除历史数据

1	mv /data/mysql/mysql_3306 /data/mysql/mysql_3306_backup_20220325

创建新的数据文件夹

1	mkdir /data/mysql/mysql_3306

数据恢复

1	innobackupex --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=127.0.0.1 --port=3306 --user=root --password=123456 --copy-back /data/backup_20220325/2022-03-25_14-49-59

授权数据文件夹给mysql用户

1
2
3

cd /data/mysql

chown -R mysql:mysql mysql_3306

启动数据库

1 2	cd /data/mysql/mysql_3306 sh start.sh

添加主从复制配置

查看data/xtrabackup_binlog_pos_innodb获取主从同步的位置。

# 添加主从配置
CHANGE MASTER TO
  MASTER_HOST='172.99.0.31',
  MASTER_USER='rep',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.003637',
  MASTER_LOG_POS=103905709,
  MASTER_CONNECT_RETRY=10;

# 查看主从同步配置
show slave status \G
# 启动主从同步
start slave;

# 验证
show slave status \G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.99.0.31
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.003637
          Read_Master_Log_Pos: 108431943
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 749241
        Relay_Master_Log_File: mysql-bin.003637
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,test,information_schema
           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: 105389363
              Relay_Log_Space: 2847004
              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: 3177
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: 523306
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.02 sec)

错误记录

xtrabackup: Can’t change dir to ‘/var/lib/mysql’ (Errcode: 2 - No such file or directory)

错误场景

在运行备份命令innobackupex --defaults-file=/root/master/config-file.cnf --host=127.0.0.1 --port=3306 --user=root --password=123456 /root/backup时报错

[root@localhost master]# innobackupex --defaults-file=/root/master/config-file.cnf --host=127.0.0.1 --port=3306 --user=root --password=123456 /root/backup
xtrabackup: recognized server arguments: --log_bin --server-id=2481903306
xtrabackup: recognized client arguments:
220324 18:36:59 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

220324 18:36:59  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306' as 'root'  (using password: YES).
220324 18:36:59  version_check Connected to MySQL server
220324 18:36:59  version_check Executing a version check against the server...
220324 18:36:59  version_check Done.
220324 18:36:59 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: 3306, socket: not set
Using server version 10.1.32-MariaDB-1~jessie
innobackupex version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
xtrabackup: uses posix_fadvise().
innobackupex: Can't change dir to '/var/lib/mysql/' (Errcode: 2 - No such file or directory)
xtrabackup: cannot my_setwd /var/lib/mysql/

解决方案

不可以在其他服务器上运行innobackupex命令对mysql进行备份

参考：https://serverfault.com/questions/685279/can-i-run-percona-xtrabackup-on-my-desktop

[ERROR] Can’t start server: Bind on TCP/IP port. Got error: 98: Address already in use

第一次启动数据库报错：

220325 16:16:17 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data
220325 16:16:17 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.28-MariaDB-enterprise) starting as process 19969 ...
220325 16:16:17 [Note] InnoDB: Using mutexes to ref count buffer pool pages
220325 16:16:17 [Note] InnoDB: The InnoDB memory heap is disabled
220325 16:16:17 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
220325 16:16:17 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
220325 16:16:17 [Note] InnoDB: Compressed tables use zlib 1.2.3
220325 16:16:17 [Note] InnoDB: Using Linux native AIO
220325 16:16:17 [Note] InnoDB: Using CPU crc32 instructions
220325 16:16:17 [Note] InnoDB: Initializing buffer pool, size = 50.0G
220325 16:16:19 [Note] InnoDB: Completed initialization of buffer pool
220325 16:16:19 [Note] InnoDB: Highest supported file format is Barracuda.
220325 16:16:20 [Note] InnoDB: 128 rollback segment(s) are active.
220325 16:16:20 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.32-79.0 started; log sequence number 836023388696
2022-03-25 16:16:20 7f6e1bfe8700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
220325 16:16:20 [Note] Server socket created on IP: '::'.
220325 16:16:20 [ERROR] Can't start server: Bind on TCP/IP port. Got error: 98: Address already in use
220325 16:16:20 [ERROR] Do you already have another mysqld server running on port: 3306 ?
220325 16:16:20 [ERROR] Aborting

220325 16:16:20 [Note] unregister_replicator OK
220325 16:16:20 [Note] InnoDB: FTS optimize thread exiting.
220325 16:16:20 [Note] InnoDB: Starting shutdown...
2022-03-25 16:16:20 7f6e1bfe8700 InnoDB: Buffer pool(s) load completed at 220325 16:16:20
2022-03-25 16:16:20 7f6e1bfe8700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2022-03-25 16:16:20 7f6e1bfe8700 InnoDB: Buffer pool(s) dump completed at 220325 16:16:20
220325 16:16:21 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
220325 16:16:23 [Note] InnoDB: Shutdown completed; log sequence number 836023391393
220325 16:16:23 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

220325 16:16:23 mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/172-22-0-32.pid ended

解决方案

查看占用3306端口的进程（lsof -i TCP:3306)，杀掉此进程之后再重新启动。