mysql 主从 Mycat读写分离备份恢复实践 - 宇宙超级无敌无敌小明

公告

M1 my.cnf：

[mysqld]
server-id = 1
log-bin = M1-log-bin
gtid_mode=ON
enforce-gtid-consistency=ON #服务器通过允许仅执行可以使用 GTID 安全记录的语句来实现 GTID 一致性

M2 my.cnf：

[mysqld]
server-id = 2
log-bin = M2-log-bin
gtid_mode=ON
enforce-gtid-consistency=ON

S1 my.cnf：

[mysqld]
server-id = 3
gtid_mode=ON
enforce-gtid-consistency=ON

schema.xml：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="test" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="test01" dataNode="test01"></schema>  <!--逻辑库-->
        <dataNode name="test01" dataHost="mall" database="test"></dataNode>  <!--分片节点-->
        <dataHost name="mall" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  <!--节点主机-->
                <heartbeat>show slave status</heartbeat>
                <writeHost host="M1" url="192.168.3.102:3306" user="root" password="root">
                        <readHost host="S1" url="192.168.3.102:3308" user="root" password="root"></readHost>
                </writeHost>
                <writeHost host="M2" url="192.168.3.102:3307" user="root" password="root"></writeHost>
        </dataHost>
</mycat:schema>

server.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="nonePasswordLogin">0</property>
    <property name="ignoreUnknownCommand">0</property>
    <property name="useHandshakeV10">1</property>
    <property name="removeGraveAccent">1</property>
    <property name="useSqlStat">0</property> 
    <property name="useGlobleTableCheck">0</property> 
        <property name="sqlExecuteTimeout">300</property>
        <property name="sequnceHandlerType">1</property>
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
    <property name="subqueryRelationshipCheck">false</property> 
    <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <property name="processorBufferPoolType">0</property>
    <property name="serverPort">8066</property> 
    <property name="managerPort">9066</property> 
    <property name="bindIp">0.0.0.0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">0</property>
        <property name="memoryPageSize">64k</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
        <property name="strictTxIsolation">false</property>
        <property name="useZKSwitch">true</property>
        <property name="parallExecute">0</property>
    </system>
　　　　<!--上面都是默认配置文件只修改了<user>标签内容-->
    <user name="root" defaultAccount="true"> <!--用户和权限-->
        <property name="password">root</property> <!--密码-->
        <property name="schemas">test</property> <!--可访问的逻辑库-->
        <property name="defaultSchema">test</property><!--默认逻辑库-->
    </user>
</mycat:server>

docker-compose.yml：

version: "3.9"
services:

  mysql-M1:
    image: mysql:5.7
    container_name: mysql-M1
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3306:3306'
    volumes:
      - /db/mysql-M1/data:/var/lib/mysql
      - /db/mysql-M1/conf:/etc/mysql/conf.d

  mysql-M2:
    image: mysql:5.7
    container_name: mysql-M2
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3307:3306'
    volumes:
      - /db/mysql-M2/data:/var/lib/mysql
      - /db/mysql-M2/conf:/etc/mysql/conf.d

  mysql-S1:
    image: mysql:5.7
    container_name: mysql-S1
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3308:3306'
    volumes:
      - /db/mysql-S1/data:/var/lib/mysql
      - /db/mysql-S1/conf:/etc/mysql/conf.d

  mycat-01:
    image: mycat:1.6.7.6
    container_name: mycat-01
    ports:
      - '8066:8066'
      - '9066:9066'
    volumes:
      - /db/mycat-01/log:/usr/local/mycat/logs
      - /db/mycat-01/conf:/usr/local/mycat/conf

  mycat-02:
    image: mycat:1.6.7.6
    container_name: mycat-02
    ports:
      - '8067:8066'
      - '9067:9066'
    volumes:
      - /db/mycat-02/log:/usr/local/mycat/logs
      - /db/mycat-02/conf:/usr/local/mycat/conf

#启动前先放置好mysql和mycat的配置文件启动执行以下命令:

docker-compose up -d

查看：#基于二进制的话就要记下“File”和"Position" 供后续使用

创建用于复制的用户：

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

此案例使用的是下面用户和权限

CREATE USER 'root'@'%' IDENTIFIED BY 'root';

GRANT ALL ON *.* TO 'root'@'%';

在从节点执行更改主声明：

#基于GITD

CHANGE MASTER TO MASTER_HOST='IP',MASTER_USER='账号',MASTER_PASSWORD='密码',MASTER_PORT=端口,MASTER_AUTO_POSITION=1;

#基于二进制

CHANGE MASTER TO MASTER_HOST='IP',MASTER_USER='账号',MASTER_PASSWORD='密码',MASTER_PORT=端口 MASTER_LOG_POS = 位置，MASTER_LOG_FILE = '二进制文件名 ';

检查：双YES即可

分别M1 M2互为主从 S1为M1的从操作

验证：

登录【主服务器】
mysql> CREATE DATABASE test; #创建test数据库
mysql> USE test; #进库

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;  #创建user表

INSERT INTO user (id, name, age) VALUES (2, 'zhangsan', 18); #插数据

SELECT * FROM user; 查看

use test;

SELECT * FROM user;

--------------------------------------------------------------------------------------------

mycat

制作镜像可参考官网

cd /db
tar -zxvf mycat1.6.7.6.tar.gz -C /db/ mycat/conf 
mv mycat mycat-01
cp -r  mycat-01 mycat-02
编辑或替换成启动配置文件
mycat01和mycat-02配置一样的

因记录搭建实践所以还修改了wrapper.conf调低资源消耗
wrapper.java.additional.9=-Xmx512M
wrapper.java.additional.10=-Xms256M

--------------------------------------------------------------------------------------------

keepalived+LVS

主 keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id lvs01
   vrrp_skip_check_adv_addr
}
vrrp_instance API {
    state MASTER
    interface ens32
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.3.200/24
    }
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault  "/etc/keepalived/notify.sh fault"
}
virtual_server 192.168.3.200 8066 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP
    real_server 192.168.3.102 8066 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8066
            }
        }
    real_server 192.168.3.102 8067 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8067
            }
        }
}

备 keepalived.conf：

! Configuration File for keepalived

global_defs {
   router_id lvs02
   vrrp_skip_check_adv_addr
}
vrrp_instance API {
    state BACKUP
    interface ens32
    virtual_router_id 51
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.3.200/24
    }
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault  "/etc/keepalived/notify.sh fault"
}
virtual_server 192.168.3.200 8066 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP
    real_server 192.168.3.102 8066 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8066
            }
        }
    real_server 192.168.3.102 8067 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8067
            }
        }
}

RS上使用的绑定VIP脚本 realserver.sh：

#!/bin/bash
#description: Config realserver

VIP=192.168.3.200

 
case "$1" in
start)
       /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP
       /sbin/route add -host $VIP dev lo:0
       echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
       sysctl -p >/dev/null 2>&1
       echo "RealServer Start OK"
       ;;
stop)
       /sbin/ifconfig lo:0 down
       /sbin/route del $VIP >/dev/null 2>&1
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
       echo "RealServer Stoped"
       ;;
*)
       echo "Usage: $0 {start|stop}"
       exit 1
esac
 
exit 0

主备切换通知邮件脚本 notify.sh: 前提需要配下mail.rc才发的出去

#!/bin/bash
#
contact='502369651@qq.com'
notify() {
local mailsubject="$(hostname) to be $1, vip floating"
local mailbody="$(date +'%F %T'): vrrp transition, $(hostname) changed to be $1"
echo "$mailbody" | mail -s "$mailsubject" $contact
}

case $1 in
master)
        notify master
        ;;
backup)
        notify backup
        ;;
fault)
        notify fault
        ;;
*)
        echo "Usage: $(basename $0) {master|backup|fault}"
        exit 1
        ;;
esac

mail.rc：

#vim /etc/mail.rc #添加如下内容
set from=xxxx@126.com
set smtp=smtp.126.com
set smtp-auth-user=xx@126.com
set smtp-auth-password=xxx
set smtp-auth=login
#---说明
#from：对方收到邮件时显示的发件人
#smtp：指定第三方发邮件的smtp服务器地址
#set smtp-auth-user：第三方发邮件的用户名
#set smtp-auth-password：用户名对应的密码,有些邮箱填的是授权码
#smtp-auth：SMTP的认证方式，默认是login，也可以改成CRAM-MD5或PLAIN方式

看看效果启备再启主；启动命令：systemctl start keepalived.service

还可以通过 ip a 查看VIP在谁那

测试：通过VIP进入

这样整体就完成了mysql+mycat+keepalived+LVS的高可用负载均衡读写分离的方案

--------------------------------------------------------------

主从复制延迟问题可参考这里

数据库备份-恢复：

　　物理备份：

　　　　 xtrabackup 官网所需权限

如何通过Xtrabackup进行备份
1、xtrabackup工具介绍
xtrabackup有两个主要的工具：xtrabackup、innobackupex。
    xtrabackup只能备份InnoDB和XtraDB两种数据表，而且只有ibd文件，frm文件它不管，恢复时就需要DBA提供frm ，同时，xtrabackup做备份的时候不能备份表结构、触发器等等。
    innobackupex是perl脚本对xtrabackup的封装和功能扩展。 innobackupex可以备份和恢复MyISAM、 InnoDB和XtraDB表以及frm文件，所以我们使用innobackupex来备份MySQL数据库。
Xtrabackup以及innobackupex是根据my.cnf配置文件来获取备份的文件，同时，需要连接到数据库和datadir操作权限。
    需要注意的是：innobackupex在备份MyISAM表之前要对全库进行加READ LOCK，会阻塞写操作，若备份是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表备份不会阻塞读写。
2、 xtrabackup备份恢复过程
    在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
    xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。
    Innobackupex会启动xtrabackup，xtrabackup准备复制数据文件前，首选执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来，同时把MyISAM表数据刷到硬盘上，然后开始复制MyISAM数据文件，文件复制完成，最后释放锁。
    上面就是xtrabackup的备份过程。
    接下来是准备（prepare）过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像MySQL刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了。
    备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表数据与InnoDB表数据是同步的。类似Oracle的，InnoDB的prepare过程可以称为recover（恢复），MyISAM的数据复制过程可以称为restore（还原）。
利用xtrabackup进行完全备份
1、innobackupex的常用选项：更多选项详解
    --host：指定数据库服务器地址
    --port：指定连接到数据库服务器的哪个端口
    --socket：连接本地数据库时使用的套接字路径
    --no-timestamp：在使用innobackupex进行备份时，可使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据
    --default-files：可通过此选项指定其它的配置文件；但是使用时必须放于所有选项的最前面
    --incremental：指定创建增量备份
    --incremental-basedir：指定基于哪个备份做增量备份
    --apply-log：应用xtrabackup_logfile文件，重做已提交的事务，回滚未提交的事务
    --redo-only：只重做已提交的事务，不准加滚
    --use-memory：在“准备”阶段可提供多少内存以加速处理，默认是100M
    --copy-back：恢复备份至数据库服务器的数据目录
    --compact：压缩备份
    --stream={tar|xbstream}：对备份的数据流式化处理 
2、 通过innobackupex 进行全备份的过程
    1）创建备份用户
            grant reload,lock tables,replication client,create tablespace,super on *.* to bakuser@'172.16.213.%' identified by '123456';
    2）进行全库备份
             innobackupex --user=DBUSER --host=SERVER --password=DBUSERPASS  --socket=path  /path/to/BACKUP-DIR
      使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间戳命名的目录中。
　　 3）一个全备份例子：
[root@localhost mnt]# innobackupex --defaults-file=/etc/my.cnf --user=root  --password=123456  --socket=/tmp/mysqld.sock      /data/backup/full/ 
 /data/backup/full/是将备份存放的目录。innobackupex或者xtrabackup通过--user和--password连接到数据库服务，
--defaults-file指定mysql配置文件目录。

在备份的同时，innobackupex还会在备份目录中创建如下文件：
xtrabackup_checkpoints：内容如下：      
           backup_type = full-backuped      #备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）
           from_lsn = 0        #备份起始的LSN(日志序列号)，每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
           to_lsn = 119232396    #备份结束的LSN(日志序列号)
xtrabackup_binlog_info：mysql服务器当前正在使用的二进制日志文件及至备份此刻为止二进制日志事件的位置。
xtrabackup_logfile：事务日志记录文件，用于在prepared阶段进行redo重做和日志回滚。
xtrabackup_info：有关此次备份的各种详细信息
backup-my.cnf：备份命令用到的配置选项信息

Innobackupex完全备份Mysql步骤
1 首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql redo的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_logfile中
2 复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方。
3 复制结束后，执行flush table with read lock操作。
4 复制.frm，MYI，MYD文件。
5 并且在这一时刻获得binary log 的位置。
6 将表进行解锁unlock tables。
7 停止xtrabackup_log进程。

通过Innobackupex完全恢复数据库
全库恢复过程：
    这一阶段会启动xtrabackup内嵌的innodb实例，将xtrabackup日志xtrabackup_Log进行回放，将提交的事务信息变更应用到innodb数据或表空间，同时回滚未提交的事务
1、prepare数据库

    在备份完成后，数据尚且不能直接用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务，而且备份过程中可能还有数据的更改动作，此时xtrabackup_logfile就可派上用场了。xtrabackup会解析该文件，对事务已经提交但数据还没有写入的部分，进行redo重做；将已经写到数据文件，但未提交的事务通过undo进行回滚，最终使得数据文件处于一致性状态。 Prepare过程是通过使用innobakupex命令的 --apply-log 选项实现的；
[root@localhost mnt]#  innobackupex --apply-log  /data/backup/full/2018-05-21_12-04-52/
成功则会输出：
111225  1:01:57  InnoDB: Shutdown completed; log sequence number 1609228
111225 01:01:57  innobackupex: completed OK!
成功后，这个完全备份就可以被用来还原数据库了。
prepare的过程，其实是读取备份文件夹中的配置文件，然后innobackupex重做已提交事务，回滚未提交事务，之后数据就被写到了备份的数据文件(innodb文件)中，并重建日志文件。
2、恢复数据库
使用innobackupex --copy-back来还原备份（recovery）
[root@localhost mnt]#  innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/backup/full/2018-05-21_12-04-52/
innobackupex会根据my.cnf的配置，将所有备份数据复制到my.cnf里面指定的datadir路径下。
如果恢复成功，最后会有如下提示：
innobackupex: Finished copying back files.
160422 17:34:51  innobackupex: completed OK!
注：datadir必须是为空的，innobackupex --copy-back不会覆盖已存在的文件，还要注意，还原时需要先关闭mysql服务，如果服务是启动的，那么就不能还原到datadir。
3、修改权限启动数据库
    默认情况下是通过root用户恢复的数据，所以mysql数据文件夹目录是root权限，需要修改文件的所有者和权限：
$ chown -R mysql:mysql /db/data
    最后启动数据库即可。
xtrabackup增量备份
    所谓的增量，一定是在已有全量的基础之上的，不然是没有任何意义的。innobackupex增量备份过程中的"增量"处理，其实主要是相对innodb而言，对myisam和其他存储引擎而言，它仍然是全拷贝(全备份)

    "增量"备份的过程主要是通过拷贝innodb中有变更的"页"（这些变更的数据页指的是"页"的LSN大于xtrabackup_checkpoints中给定的LSN）。增量备份是基于全备的，第一次增备的数据必须要基于上一次的全备，之后的每次增备都是基于上一次的增备，最终达到一致性的增备。增量备份的过程如下，和全备的过程类似，区别仅在第2步。
在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份是基于上一次的增量备份，以此类推。

全备份：
[root@MySQL-01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root  --password=123456 --socket=/tmp/mysqld.sock     /data/backup/full/
第一次增量备份：
[root@MySQL-01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root  --password=123456 --socket=/tmp/mysqld.sock  --incremental /data/backup/incremental/   --incremental-basedir=/data/backup/full/2018-05-21_12-04-52   --parallel=2
第二次增量备份：
[root@MySQL-01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root  --password=123456 --socket=/tmp/mysqld.sock  --incremental /data/backup/incremental/  --incremental-basedir=/data/backup/incremental/2018-05-21_12-11-18/ --parallel=2
xtrabackup增量备份的恢复

增量备份的恢复大体为3个步骤：
（1）恢复完全备份
（2）恢复增量备份到完全备份（开始恢复的增量备份要添加--redo-only参数，到最后一次增量备份去掉--redo-only参数）
（3）对整体的完全备份进行恢复，回滚那些未提交的数据
xtrabackup增量备份的恢复

（1）恢复完全备份（注意这里一定要加--redo-only参数，该参数的意思是只应用xtrabackup日志中已提交的事务数据，不回滚还未提交的数据）一个事务在前一个备份中没有提交，但可能在其后的备份中提交了，这种情况就需要--redo-only参数。
[root@MySQLdb ~]# innobackupex --apply-log --redo-only /data/backup/full/2018-05-21_12-04-52/

（2）将增量备份1应用到完全备份
[root@MySQLdb ~]# innobackupex --apply-log --redo-only /data/backup/full/2018-05-21_12-04-52/ --incremental-dir=/data/backup/incremental/2018-05-21_12-11-18/

（3）将增量备份2应用到完全备份（注意恢复最后一个增量备份时需要去掉--redo-only参数，回滚xtrabackup日志中那些还未提交的数据）
[root@MySQLdb ~]# innobackupex --apply-log /data/backup/full/2018-05-21_12-04-52/ --incremental-dir=/data/backup/incremental/2018-05-21_14-28-40/

（4）把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据：
[root@MySQLdb ~]# innobackupex --apply-log /data/backup/full/2018-05-21_12-04-52/

（5）把恢复完的备份复制到数据库目录文件中，赋权，然后启动mysql数据库，检测数据正确性
[root@MySQLdb ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/backup/full/2018-05-21_12-04-52/
xtrabackup针对海量数据的备份优化
使用流特性，需要指定--stream选项，使用tar备份：（推荐方法）
innobackupex  --defaults-file=/etc/my.cnf --user=root  --password=123456  --socket=/tmp/mysqld.sock  --stream=tar  /data/backup/tgz  --parallel=4 | gzip  > / data/backup/tgz/mysqlbak1.tar.gz

备份到远程主机：
innobackupex  --defaults-file=/etc/my.cnf --user=root  --password=123456  --socket=/tmp/mysqld.sock  --stream=tar  /data/backup/tgz|ssh root@172.16.213.233  "gzip >/data/backup/tgz/mysqlbak1.tar.gz"
可用参数：
--parallel=4：  加速备份，这个选项会指定xtrabackup备份文件的线程数。

提取tar流，需要加i参数
$ tar -xizvf mysqlbak1.tar.gz
通过xtrabackup进行mysql备份恢复例子
1、对mysql的cmsdb库进行备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456  --databases=cmsdb  --stream=tar  /data/back_data/ 2>/data/back_data/cmsdb.log | gzip  >/data/back_data/cmsdb.tar.gz
      
说明：
   --database=cmsdb ：单独对cmsdb数据库做备份 ，若是不添加此参数那就那就是对全库做备份
   2>/data/back_data/cmsdb.log  输出信息写入日志中
   gzip >/data/back_data/cmsdb.tar.gz 打包压缩存储到该文件中
      
2、此处可以写个脚本做备份(backup.sh)
      #!/bin/sh
      echo "开始备份..."`date`
      log=cmsdb_`date +%y%m%d%H%M`.log
      str=cmsdb_`date +%y%m%d%H%M`.tar.gz
      innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --database=cmsdb  --stream=tar /data/back_data/ 2>/data/back_data/$log | gzip  >/data/back_data/$str
      echo "备份完毕..."`date`
3、恢复数据
      1) 先停止数据库：service mysqld stop
      2) 解压 tar -izxvf cmsdb.tar.gz -C /data/back_data/db/   (没有db ,需要mkdir /data/back_data/db/)  
      3) 恢复 innobackupex --defaults-file=/etc/my.cnf --user=root --password  --apply-log /data/back_data/db/  
              innobackupex --defaults-file=/etc/my.cnf --user=root --password  --copy-back /data/back_data/db/
      4) 赋权 chown -R mysql.mysql /var/lib/mysql/*
      5) 重启数据库 service mysqld restart

　　逻辑备份：

　　　　mysqldump　　可参考 ① ②　　　

适用于innodb存储引擎 备份单个库或多个库如下示例 #备份所有库，-B换-A 不加dbname。注意：如果一次备份了多个表，后续只需要恢复其中一个表的时候，其他表也会覆盖原有的数据，谨慎使用！！！　建议单表单备份
mysqldump -u用户 -p密码 -B -F --triggers -R -E --master-data=2 --single-transaction --set-gtid-purged=off dbname|gzip >/路径/to/dbname_$(date +%F).sql.gz

常用选项：
--all-databases（即-A）：备份MySQL服务器上的所有数据库；
--triggers  每个转储表的转储触发器。（默认为开；使用--skip-triggers禁用。）
--routines （即-R）：在输出中包括用于转储数据库的存储例程(过程和函数)
--events （-E） 在输出中包括转储数据库的事件计划程序事件
--no-data （-d）   没有行信息 只导出表结构，不导出表数据
--databases （-B） 指定库
--master-data 在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息如果选项值为 2则更改为主语句被写为 SQL注释，因此仅提供信息；重新加载转储文件时，它无效。如果选项值为 1，则该语句不作为注释写入，并在重新加载转储文件时生效。
如果未指定选项值，则默认值为 1. 该选项自动关闭--lock-tables。除非也指定了--single-transaction，否则它也会打开--lock-all-tables（全局锁）
--single-transaction 对InnoDB的数据表，让整个数据在dump过程中保证数据的一致性。且不会锁表
--set-gtid-purged  ON记录GITD号到备份里，OFF不记录。 如果打算仅使用转储服务器中的某些数据来部署新的复制从属，请使用ON。如果要通过在拓扑中复制 table 来修复 table，请使用OFF。如果要在不相交的复制拓扑之间复制 table 并将保留该 table，请使用OFF
--lock-all-tables （-x）全局锁表，此选项自动关闭--single-transaction和--lock-tables
--flush-logs （-F） 开始转储之前，请刷新 MySQL 服务器日志文件
示例可看参考1 2

　　　备份脚本：

#!/bin/bash
# crontab -l0 1 * * * sh /data/script/mysql_bak.sh >/dev/null 2>&1 #配合crontab周期备份
USER_NAME=root
USER_PASSWD=root
backup=/db/backupdata
date=`date +%Y-%m-%d`
[ ! -d $backup ] && mkdir -p $backup #判断备份目录不存在，为真执行创建，为假不执行
mkdir -p ${backup}/${date}
date1=`date +%Y.%m.%d-%H:%M:%S`
echo "Start time: $date1 Path: ${backup}/${date}" > ${backup}/mysqlbak.log #脚本开始执行时间戳
#iplist="192.168.56.5 192.168.56.6" #IP列表
iplist="192.168.3.102"
port="3306"
for i in $iplist;
do
  for dbname in `/bin/mysql -u $USER_NAME -p$USER_PASSWD -h $i -P $port -e "show databases;"| sed 1d | egrep -v "schema|mysql|sys|*_bak"`
  do
    /bin/mysqldump -u $USER_NAME -p$USER_PASSWD -h $i -P $port -B -F --triggers -R -E --master-data=2 --single-transaction --set-gtid-purged=off $dbname | gzip > ${backup}/${date}/${i}_${dbname}.$date.sql.gz
    date1=`date +%Y.%m.%d-%H:%M:%S`
    echo "$dbname   | size: `du -sh ${backup}/$date/${i}_${dbname}.$date.sql.gz | awk '{print $1}' ` | Completion time: $date1 " >> ${backup}/mysqlbak.log #执行完时间戳和相关信息
  done
done
/bin/mail  -s "mysql备份日志" xxx@qq.com < ${backup}/mysqlbak.log

　　　　恢复：

gzip -cd xxxx.sql.gz >xxx.sql #解压成xxx.sql文件
以上备份的sql文件，恢复时，直接执行"mysql -uroot -p123456 < xxx.sql" 或者登录数据库执行"source xxx.sql" 命令进行即可恢复备份的数据！不需要提前创建要恢复的库！ 可以相对路径或者绝对路径下的xxx.sql
看情况决定要不要  #恢复数据前要关闭binlog记录： set sql_log_bin=0; 在主库里关闭binlog恢复的话，因没有写入binlog就会造测主库有。从库没 所以备份前
恢复完只是恢复完全备，增量部分恢复需要使用二进制日志

　　　　　二进制日志进行增量备份恢复

grep "MASTER_LOG_POS" xxx.sql  #查看备份最后的截止事件二进制文件和位置点
show binlog events in '二进制日志文件名' from 位置点; 找到开始的GTID号，在根据情况选最后的GTID号 截取生产sql文件来恢复即可  
#可看下面关于二进制日志文件的内容

　　备份和恢复策略示例　

--------------------------------------------------------------------

二进制日志记录什么：
记录所有变更类的操作日志：
DDL--------DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接 和约束等初始化工作上，他们大多在建立表时使用
DCL----------是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
DML------它们是SELECT、UPDATE、INSERT、DELETE，这4条命令是用来对数据库里的数据进行操作的语言 

DDL和DCL 以语句的方式，原样记录
DML记录已提交的事务，DML记录格式 SBR（基于语句）、RBR（基于行）、MBR（混合）
SBR和RBR优缺点---------官网很清晰


查看二进制日志事件

show master status; #查看当前正在使用的二进制文件
show binlog events in '二进制日志文件名';    #解析示图如下:

查看二进制内容

mysqlbinlog 二进制日志文件名  #解析示图如下：
mysqlbinlog -d 库名 二进制日志文件  #-d 选项可指定某个库

mysqlbinlog --base64-output=decode-rows -vvv 二进制日志文件名  #行模式记录的操作部分 变的可读性好点

二进制日志截取：

mysqlbinlog --start-position=起始位置 --stop-position=结束位置 二进制日志文件名 >/tmp/xxxx.sql    #位置通过上面“查看二进制日志事件”可看到

恢复binlog

mysql> set sql_log_bin=0;    --->临时关闭恢复时产生的新日志；此变量控制是否为当前会话启用到二进制日志的日志记录(假设二进制日志本身已启用)#主从不需要关闭二进制记录
mysql> source /tmp/xxxx.sql    - - - >加载sql文件
mysql> set sql_log_bin=1; set sql_log_bin=1;    --->改回来

GITD模式下的二进制日志文件： #解析示图如下

对于binlog中的每一个事务,都会生成一个GTID号码，

DDL ,DCL 一个event就是一个事务,就会有一个GTID号；

DML语句来讲,begin到commit,是一个事务,就是一个GTID号；

TID是一个:自增长的数据,从1开始

GTID的幂等性（如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过）

基于GITD的binlog截取: ## 如果打算某些数据来部署新的复制从属 就不要跳过gitd号。如果打算修复某些操作就要跳过GITD号，不然会因幂等性恢复时报错。

mysqlbinlog --skip-gtids --include-gtids='erver_uuid:TID范围' 二进制日志文件名 >/tmp/gtid.sql　　#--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息；--include-gtids='erver_uuid:TID范围' #包含
还有个选项 --exclude-gtids='erver_uuid:TID范围'  #排除  
例如：mysqlbinlog --skip-gtids --include-gtids='6e60f813-7cab-11eb-b5b1-0242ac130002:1-3' M2-log-bin.000001 >/tmp/gtid.sq

基于GITD的binlog恢复:

set sql_log_bin=0;#主从不需要关闭二进制记录
source /tmp/gtid.sql
set sql_log_bin=1;

-----------------------------------

常用命令

mysql命令行内命令-------
START SLAVE;    #启动从
STOP SLAVE;     #停从
reset slave all;   #重置从
SHOW SLAVE STATUS \G  #查看从状态
RESET MASTER; #重置主
show variables like '%log_bin%'; #查看二进制日志位置
show binary logs; #查看所有已存在的二进制日志
show master status; #查看正在使用的二进制日志
flush logs; #滚动二进制日志
SHOW DATABASES; #查看所有库

posted on 2021-03-05 12:24 宇宙超级无敌无敌小明阅读(181) 评论(0) 编辑收藏举报

刷新页面返回顶部

mysqldump 可参考 ① ②

　　　　mysqldump　　可参考 ① ②　　　