乐趣区

关于linux:Mysql日常维护不停主master做主从同步

jiezi

咱们目前所用到的在 Mysql master 不停机状况下做同步操作的支流工具都是 XtraBackup 包含阿里云、腾讯都是采纳此工具来备份解压。

MySQL 主从同步原理：

MySQL 主从同步是在 MySQL 主从复制 (Master-Slave Replication) 根底上实现的，通过设置在 Master MySQL 上的 binlog(使其处于关上状态)，Slave MySQL 上通过一个 I / O 线程从 Master MySQL 上读取 binlog，而后传输到 Slave MySQL 的中继日志中，而后 Slave MySQL 的 SQL 线程从中继日志中读取中继日志，而后利用到 Slave MySQL 的数据库中。

XtraBackup 备份原理：

innobackupex 在后盾线程一直追踪 InnoDB 的日志文件，而后复制 InnoDB 的数据文件。数据文件复制实现之后，日志的复制线程也会完结。这样就失去了不在同一时间点的数据正本和开始备份当前的事务日志。实现下面的步骤之后，就能够应用 InnoDB 解体复原代码执行事务日志（redo log），以达到数据的一致性。

备份过程

1 ) backup，备份阶段，追踪事务日志和复制数据文件（物理备份）。

2 ) preparing，重放事务日志，使所有的数据处于同一个工夫点，达到一致性状态。

XtraBackup 的长处：

1）备份速度快，物理备份牢靠

2）备份过程不会打断正在执行的事务（无需锁表）

3）可能基于压缩等性能节约磁盘空间和流量

4）主动备份校验

5）还原速度快

6）能够流传将备份传输到另外一台机器上

7）在不减少服务器负载的状况备份数据

xtrabackup 装置

[root@localhost opt]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev-devel rsync perl-Digest-MD5

Centos 6

[root@localhost opt]# wget https://www.percona.com/downl…

[root@localhost opt]# yum –enablerepo=epel localinstall percona-xtrabackup-2.3.2-1.el6.x86_64.rpm -y

Centos 7

[root@localhost opt]# wget https://downloads.percona.com…

[root@localhost opt]# rpm -ivh percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm -y

[root@localhost opt]# whereis innobackupex

innobackupex: /usr/bin/innobackupex /usr/share/man/man1/innobackupex.1.gz

备份数据库

[root@localhost opt]# innobackupex –defaults-file=/etc/my.cnf –user= 账户 –password= 明码 –socket=/home/mysql/mysql.sock /home/BackupData/”

复原数据库

注：核查 mysql 的版本尽量保障数据库版本一致性。

slave 机器上操作

[root@localhost opt]#mkdir 20210831_00
[root@localhost opt]#ll 20210831_00.tar.gz 
-rw-r--r-- 1 root root 508147921 Aug 31 08:53 20210831_00.tar.gz
[root@localhost opt]#tar -zxf 20210831_00.tar.gz -C ./20210831_00/
[root@localhost opt]# ls -l ./20210831_00
-rw-rw---- 1 root root       484 Aug 31 00:23 backup-my.cnf
-rw-rw---- 1 root root 104857600 Aug 31 00:06 ibdata1
drwxr-xr-x 2 root root      4096 Aug 31 14:25 mysql
drwxr-xr-x 2 root root      4096 Aug 31 14:25 performance_schema
-rw-rw---- 1 root root        75 Aug 31 00:23 xtrabackup_binlog_info
-rw-rw---- 1 root root       147 Aug 31 00:23 xtrabackup_checkpoints
-rw-rw---- 1 root root       641 Aug 31 00:23 xtrabackup_info
-rw-rw---- 1 root root   2747392 Aug 31 00:23 xtrabackup_logfile

在 Mysql 的数据库配置文件中，指定的数据寄存目录创立对应文件夹

如：配置文件配置的 datadir = /home/mysql

[root@localhost home]# mkdir /home/mysql

复原日志文件

[root@localhost home]# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=123123 --apply-log /opt/20210831_00
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=62456861898
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 62456862220
InnoDB: Doing recovery: scanned up to log sequence number 62456862229 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 338527434, file name mysql-bin.000153
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: page_cleaner: 1000ms intended loop took 15111ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.32 started; log sequence number 62456862229
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 62456862248
210831 14:37:52 completed OK!

呈现 completed OK! 才示意胜利解压实现

复原数据文件

[root@localhost home]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=mopon123 --copy-back /opt/20210831_00
210831 14:55:39 [01]        ...done
210831 14:55:39 [01] Copying ./xtrabackup_master_key_id to /home/mysql/xtrabackup_master_key_id
210831 14:55:39 [01]        ...done
210831 14:55:39 [01] Copying ./ibtmp1 to /home/mysql/ibtmp1
210831 14:55:39 [01]        ...done
210831 14:55:39 completed OK!

completed OK! 后果为此提醒示意复原正确！！！


[root@localhost home]# chown -R mysql. mysql/
[root@localhost home]# /etc/init.d/mysqld start
Starting MySQL (Percona Server)............ SUCCESS!

开始同步主库

[root@localhost home]#cat /opt/20210831_00/xtrabackup_binlog_info
mysql-bin.000153    338527434    526b9441-6616-11eb-8737-005056b05ba7:1-77041081
[root@localhost home]#mysql -uroot -p123456
MySQL [(none)]> change master to master_host='192.168.0.1',master_user='slave',master_password='slave',master_log_file='mysql-bin.000153', master_log_pos=338527434;
Query OK, 0 rows affected, 2 warnings (0.78 sec)
MySQL [(none)]> start slave;
MySQL [(none)]> show slave status\G;
.....................
          Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
............................

到此为止数据库在 Mstar 不停机状态下同步实现！

退出移动版