xtrabackup备份还原mysql & 主从

主： 192.168.0.60
从：192.168.0.57

一、安装

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all && yum makecache
yum install  percona-xtrabackup -y

也可以 二进制 部署
wget -c https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/tarball/percona-xtrabackup-2.4.28-Linux-x86_64.glibc2.17.tar.gz
tar -xf percona-xtrabackup-2.4.28-Linux-x86_64.glibc2.17.tar.gz -C /usr/local/
#加入$PATH   /etc/profile
echo "export PATH=\$PATH:/usr/local/percona-xtrabackup-2.4.28-Linux-x86_64.glibc2.17/bin" >>/etc/profile
source /etc/profile

二、备份还原

1、主，全量备份

xtrabackup --backup --target-dir=/tmp/xbackup/  \
--user=root  \
--password=123456  \
--socket=/tmp/mysql.sock

–target-dir=/tmp/xbackup/ 为备份的目录

2、从，全量恢复

xtrabackup --copy-back  \
--target-dir=/tmp/xbackup/  \
--defautls-file=/usr/local/mysql/my.cnf  \
--socket=/tmp/mysql.sock \
--user=root --password=123456 \
--datadir=/data/mysql

注：
1、恢复前停止数据库
2、清空数据目录rm -rf /data/mysql/*
3、恢复后改数据目录权限 chown -R mysql:mysql /data/mysql
4、别忘了查看pos点

# cat /tmp/xbackup/xtrabackup_info |grep pos
binlog_pos = filename 'mysql-bin.000095', position '120'

5、也可以通过innobackupex 以下来备份及恢复

备份：
innobackupex --user=root --password=otoyix --defaults-file=/usr/local/mysql/my.cnf  /tmp/xbackup/
cd /tmp/xbackup/
tar -czf 2021-12-31_17-03-28.tar.gz 2021-12-31_17-03-28
scp 2021-12-31_17-03-28.tar.gz root@192.168.0.57:/tmp/


恢复：
docker cp /tmp/2021-12-31_17-03-28.tar.gz mysql:/tmp/
/etc/init.d/mysqld stop
mv /data/mysql{,.bak}
mkdir -p  /data/mysql && chown -R mysql:mysql /data/mysql
innobackupex --apply-log /tmp/xbackup/2021-12-31_17-03-28
innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --copy-back /tmp/xbackup/2021-12-31_17-03-28
[root@e072470c9dd0 /]# cat /data/mysql/xtrabackup_info |grep pos
binlog_pos = filename 'mysql-bin.000095', position '120'
chown -R mysql:mysql /data/mysql
/etc/init.d/mysqld start

三、xtrabackup备份至 其他主机

xtrabackup也可以直接把备份到其他主机，本地不存文件

#备份
xtrabackup --backup --slave-info  \
-uroot -H 127.0.0.1 -P3306 -p'password'  \
--stream=xbstream \
--target-dir=/usr/src/bakup_`date +"%F_%H_%M_%S"`  | ssh root@192.168.3.202 "cat - > /usr/src/backup.xbstream"


mkdir -p /tmp/xbackup
#解压
xbstream -x --parallel=10 -C /tmp/xbackup < ./backup.xbstream   #解压至/tmp/xbackup
/etc/init.d/mysqld stop ; rm -rf /data/mysql/*    #还原前要先停库 及 删除数据目录，还原后，也要执行chown -R mysql:mysql /data/mysql

#还原
xtrabackup --copy-back  \
--target-dir=/tmp/xbackup/  \
--defautls-file=/usr/local/mysql/my.cnf  \
--socket=/tmp/mysql.sock \
--user=root --password=123456 \
--datadir=/data/mysql

chown -R mysql:mysql /data/mysql #恢复目录权限
/etc/init.d/mysqld start

#恢复主从
cat /tmp/xbackup/xtrabackup_info |grep pos 查看pos点
binlog_pos = filename 'mysql-bin.000194', position '67162256'


> change master to master_host="192.168.1.222",  \
 master_user="tongbu", \
 master_password="123456", \
 master_log_file="mysql-bin.000194", \
 master_log_pos=67162256;

四、主从

1、主：

>grant replication slave on *.* to "tongbu"@"192.168.1.%" identified by "123456";
>FLUSH PRIVILEGES;

2、从

mysql> change master to master_host="192.168.0.60",  \
    -> master_user="tongbu", \
    -> master_password="123456", \
    -> master_log_file="mysql-bin.000095", \
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 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.0.60
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000095
          Read_Master_Log_Pos: 401
               Relay_Log_File: e072470c9dd0-relay-bin.000002
                Relay_Log_Pos: 560
        Relay_Master_Log_File: mysql-bin.000095
             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: 401
              Relay_Log_Space: 736
              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: 50
                  Master_UUID: cb04ea2f-ea66-11e9-9b59-4ccc6ad72153
             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 the slave I/O thread to update it
           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
1 row in set (0.00 sec)

-----------------------------end