Database基础(五):使用binlog日志、XtraBackup备份工具、MySQL AB复制

一、使用binlog日志

目标:

利用binlog恢复库表,要求如下:

  1. 启用binlog日志
  2. 创建db1库tb1表,插入3条记录
  3. 删除tb1表中刚插入的3条记录
  4. 使用mysqlbinlog恢复删除的3条记录

步骤:

步骤一:启用binlog日志        //记录除了查询之外的SQL命令,默认未启用

1)调整/etc/my.cnf配置,并重启服务

[root@dbsvr1 ~]# vim  /etc/my.cnf
    [mysqld]
    .. ..
    log-bin-index=mysql-bin                              //启用二进制日志,并指定前缀
    server_id=1
    binlog_format=STATEMENT                               //在Mysql5.7中,binlog日志格式默认为ROW,但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT
    .. ..
    [root@dbsvr1 ~]# systemctl  restart mysqld.service

2)确认binlog日志文件

新启用binlog后,每次启动MySQl服务都会新生成一份日志文件:

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
    /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index

其中mysql-bin.index文件记录了当前保持的二进制文件列表:

[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
    ./mysql-bin.000001

重启MySQL服务程序,或者执行SQL操作“FLUSH LOGS;”,会生成一份新的日志:

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
    /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
    /var/lib/mysql/mysql-bin.000002
    [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
    ./mysql-bin.000001
    ./mysql-bin.000002

步骤二:利用binlog日志重做数据库操作

1)执行数据库表添加操作

创建db1·库tb1表,表结构自定义:

mysql> CREATE DATABASE db1;
    Query OK, 1 row affected (0.05 sec)
    mysql> USE db1;
    Database changed
    mysql> CREATE TABLE tb1(
        -> id int(4) NOT NULL,name varchar(24)
        -> );
    Query OK, 0 rows affected (0.28 sec)

插入3条表记录:

mysql> INSERT INTO tb1 VALUES
        -> (1,'Jack'),
        -> (2,'Kenthy'),
        -> (3,'Bob');
    Query OK, 3 rows affected (0.12 sec)
    Records: 3  Duplicates: 0  Warnings: 0

确认插入的表记录数据:

mysql> SELECT * FROM tb1;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | Jack   |
    |  2 | Kenthy |
    |  3 | Bob    |
    +----+--------+
    3 rows in set (0.00 sec)

2)删除前一步添加的3条表记录

执行删除所有表记录操作:

mysql> DELETE FROM tb1;
    Query OK, 3 rows affected (0.09  sec)

确认删除结果:

mysql> SELECT * FROM tb1;
    Empty set (0.00 sec)

步骤三:通过binlog日志恢复表记录

binlog会记录所有的数据库、表更改操作,所以可在必要的时候重新执行以前做过的一部分数据操作,但对于启用binlog之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的3条表记录”的需求,应通过mysqlbinlog工具查看相关日志文件,找到删除这些表记录的时间点,只要恢复此前的SQL操作(主要是插入那3条记录的操作)即可。

1)查看mysql-bin.000002日志内容

[root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #170412 12:05:32 server id 1  end_log_pos 123 CRC32 0x6d8c069c  Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG '
    jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    AZwGjG0=
    '/*!*/;
    # at 123
    #170412 12:05:32 server id 1  end_log_pos 154 CRC32 0x17f50164  Previous-GTIDs
    # [empty]
    # at 154
    #170412 12:05:59 server id 1  end_log_pos 219 CRC32 0x4ba5a976  Anonymous_GTID  last_committed=0        sequence_number=1
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 219
    #170412 12:05:59 server id 1  end_log_pos 310 CRC32 0x5b66ae13  Query   thread_id=3     exec_time=0     error_code=0
    SET TIMESTAMP=1491969959/*!*/;
    SET @@session.pseudo_thread_id=3/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1436549152/*!*/;
    SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    /*!\C utf8 *//*!*/;
    SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    CREATE DATABASE db1
    /*!*/;
    # at 310
    #170412 12:06:23 server id 1  end_log_pos 375 CRC32 0x2967cc28  Anonymous_GTID  last_committed=1        sequence_number=2
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 375
    #170412 12:06:23 server id 1  end_log_pos 502 CRC32 0x5de09aae  Query   thread_id=3     exec_time=0     error_code=0
    use `db1`/*!*/;
    SET TIMESTAMP=1491969983/*!*/;
    CREATE TABLE tb1(
    id int(4) NOT NULL,name varchar(24)
    )
    /*!*/;
    # at 502
    #170412 12:06:55 server id 1  end_log_pos 567 CRC32 0x0b8cd418  Anonymous_GTID  last_committed=2        sequence_number=3
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 567
    #170412 12:06:55 server id 1  end_log_pos 644 CRC32 0x7e8f2fa0  Query   thread_id=3     exec_time=0     error_code=0
    SET TIMESTAMP=1491970015/*!*/;
    BEGIN
    /*!*/;
    # at 644
    #170412 12:06:55 server id 1  end_log_pos 772 CRC32 0x4e3f728e  Query   thread_id=3     exec_time=0     error_code=0                            //插入表记录的起始时间点
    SET TIMESTAMP=1491970015/*!*/;
    INSERT INTO tb1 VALUES(1,'Jack'),(2,'Kenthy'), (3,'Bob')
    /*!*/;
    # at 772
    #170412 12:06:55 server id 1  end_log_pos 803 CRC32 0x6138b21f  Xid = 10
                                                          //确认事务的时间点
    COMMIT/*!*/;
    # at 803
    #170412 12:07:24 server id 1  end_log_pos 868 CRC32 0xbef3f472  Anonymous_GTID  last_committed=3        sequence_number=4
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 868
    #170412 12:07:24 server id 1  end_log_pos 945 CRC32 0x5684e92c  Query   thread_id=3     exec_time=0     error_code=0
    SET TIMESTAMP=1491970044/*!*/;
    BEGIN
    /*!*/;
    # at 945
    #170412 12:07:24 server id 1  end_log_pos 1032 CRC32 0x4c1c75fc         Query   thread_id=3     exec_time=0     error_code=0            //删除表记录的时间点
    SET TIMESTAMP=1491970044/*!*/;
    DELETE FROM tb1
    /*!*/;
    # at 1032
    #170412 12:07:24 server id 1  end_log_pos 1063 CRC32 0xccf549b2         Xid = 12
    COMMIT/*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2) 执行指定Pos节点范围内的sql命令恢复数据

根据上述日志分析,只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出,结合管道交给msyql命令执行导入重做:

[root@dbsvr1 ~]# mysqlbinlog \
        --start-datetime="2017-04-12 12:06:55" \
        --stop-datetime="2017-04-12 12:07:23" \
        /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
    Enter password:                                     //验证口令

3)确认恢复结果

mysql> SELECT * FROM db1.tb1;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | Jack   |
    |  2 | Kenthy |
    |  3 | Bob    |
    +----+--------+
    3 rows in set (0.00 sec)

二、XtraBackup备份工具

目标:

  1. 安装XtraBackup软件包。
  2. 使用XtraBackup执行完整备份、增量备份。
  3. 准备数据恢复目录。

步骤:

步骤一:安装XtraBackup软件包

1)了解软件包描述信息

[root@dbsvr1 pub]# rpm -qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
    Name        : percona-xtrabackup-24
    Version     : 2.4.6
    Release     : 2.el7
    Architecture: x86_64
    Install Date: (not installed)
    Group       : Applications/Databases
    Size        : 32416340
    License     : GPLv2
    Signature   : DSA/SHA1, 2017年02月27日 星期一 20时28分17秒, Key ID 1c4cbdcdcd2efd2a
    Source RPM  : percona-xtrabackup-24-2.4.6-2.el7.src.rpm
    Build Date  : 2017年02月27日 星期一 20时27分21秒
    Build Host  : vps-centos7-x64-01.ci.percona.com
    Relocations : (not relocatable)
    URL         : http://www.percona.com/software/percona-xtrabackup
    Summary     : XtraBackup online backup for MySQL / InnoDB
    Description :
    Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines

2)安装依赖包perl-DBD-MySQL perl-Digest-MD5 libev

使用RHEL 7自带的即可,yum方式安装:

[root@dbsvr1 pub]# yum -y install perl-DBD-MySQL perl-Digest-MD5
    libev使用网上找的rpm包 libev-4.15-1.el6.rf.x86_64.rpm   //该包由讲师提供
    [root@dbsvr1 pub]#rpm –ivh libev-4.15-1.el6.rf.x86_64.rpm

如果未安装这些依赖包,则直接安装percona-xtrabackup时会报错:

[root@dbsvr1 pub]# rpm -ivh percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
        libev.so.4()(64bit) 被 percona-xtrabackup-24-2.4.6-2.el7.x86_64 需要
        perl(DBD::mysql) 被 percona-xtrabackup-24-2.4.6-2.el7.x86_64 需要
        perl(Digest::MD5) 被 percona-xtrabackup-24-2.4.6-2.el7.x86_64 需要

3)安装percona-xtrabackup

[root@dbsvr1 pub]#rpm -ivh percona-xtrabackup-*.rpm
    警告:percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID cd2efd2a: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:percona-xtrabackup-24-2.4.6-2.el7################################# [ 33%]
       2:percona-xtrabackup-test-24-2.4.6-################################# [ 67%]
       3:percona-xtrabackup-24-debuginfo-2################################# [100%]

4)确认安装的主要程序/脚本

[root@dbsvr1 pub]# rpm -ql percona-xtrabackup-24-2.4.6-2.el7.x86_64
    /usr/bin/innobackupex
    /usr/bin/xbcloud
    /usr/bin/xbcloud_osenv
    /usr/bin/xbcrypt
    /usr/bin/xbstream
    /usr/bin/xtrabackup
    /usr/share/doc/percona-xtrabackup-24-2.4.6
    /usr/share/doc/percona-xtrabackup-24-2.4.6/COPYING
    /usr/share/man/man1/innobackupex.1.gz
    /usr/share/man/man1/xbcrypt.1.gz
    /usr/share/man/man1/xbstream.1.gz
    /usr/share/man/man1/xtrabackup.1.gz

步骤二:使用XtraBackup执行数据库备

--host 主机名

--port 3306

--user 用户名

--password 密码

--databases="库名"

--databases="库1 库2"

--databases="库.表"

--no-timestamp 不用日期命名备份文件存储的子目录,使用备份的数据库名做备份目录名

--no-timestmap 不使用日期命名备份目录名

1)做一个完整备份

默认情况下,备份文件存储的子目录会用日期命名,

innobackupex作为客户端工具,以mysql协议连入mysqld,将数据备份到/backup文件夹:

[root@dbsvr1 ~]# innobackupex --user=root --password=123456 /backup/mysql –no-timestamp
    170425 11:05:44 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!".
    Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
    170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    Using server version 5.7.17
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    InnoDB: Number of pools: 1
    170425 11:05:45 >> log scanned up to (2543893)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
    170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1
    170425 11:05:45 [01]        ...done
    170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd
    170425 11:05:46 [01]        ...done
    170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd
    170425 11:05:46 [01]        ...done
    170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd
    170425 11:05:46 [01]        ...done
    170425 11:05:46 >> log scanned up to (2543893)
    .. ..
    170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm
    170425 11:06:00 [01]        ...done
    170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm
    170425 11:06:00 [01]        ...done
    170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt
    170425 11:06:00 [01]        ...done
    170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm
    170425 11:06:00 [01]        ...done
    170425 11:06:00 Finished backing up non-InnoDB tables and files
    170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '2543884'
    xtrabackup: Stopping log copying thread.
    .170425 11:06:00 >> log scanned up to (2543893)
    170425 11:06:00 Executing UNLOCK TABLES
    170425 11:06:00 All tables unlocked
    170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
    170425 11:06:00 [00]        ...done
    170425 11:06:00 Backup created in directory '/backup/'
    170425 11:06:00 [00] Writing backup-my.cnf
    170425 11:06:00 [00]        ...done
    170425 11:06:00 [00] Writing xtrabackup_info
    170425 11:06:00 [00]        ...done
    xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.
    170425 11:06:01 completed OK

确认备份好的文件数据:

[root@dbsvr1 ~]#ls /backup/mysql
    backup-my.cnf  ib_buffer_pool  mysql      sys                   xtrabackup_info
    db1  ibdata1      performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

2)做一个增量备份(基于前一步的完整备份)

随意做一些新增或更改库表的操作,比如在db1库中新建一个mytb的表:

mysql> USE db1;
    Database changed
    mysql> CREATE TABLE mytb(id int(4), name varchar(24));
    Query OK, 0 rows affected (0.38 sec)
    mysql> INSERT INTO tb1 VALUES
        -> (1,'bon'),
        -> (2,'bo'),
    Query OK, 2 rows affected (0.12 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> SELECT * FROM tb1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | bob  |
    |    2 | bo   |
    +------+------+
    2 rows in set (0.00 sec)

以前一次保存到/backup的完整备份为基础,做一个增量备份,保存到/incr01/,指定增量备份参照的基本目录(完整备份目录)需要用到选项--incremental-basedir。相关操作如下:

[root@dbsvr1 ~]# innobackupex  --user=root --password=123456 --incremental /incr01 --incremental-basedir=/backup/mysql --no-timestamp
    170425 11:30:14 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!".
    Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
    170425 11:30:14 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    Using server version 5.7.17
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    incremental backup from 2543884 is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    InnoDB: Number of pools: 1
    170425 11:30:14 >> log scanned up to (2549933)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
    xtrabackup: using the full scan for incremental backup
    170425 11:30:15 [01] Copying ./ibdata1 to /incr01/ibdata1.delta
    170425 11:30:15 [01]        ...done
    170425 11:30:15 >> log scanned up to (2549933)
    170425 11:30:15 [01] Copying ./mysql/plugin.ibd to /incr01/mysql/plugin.ibd.delta
    170425 11:30:15 [01]        ...done
    ... ...
    170425 11:30:35 Executing UNLOCK TABLES
    170425 11:30:35 All tables unlocked
    170425 11:30:35 [00] Copying ib_buffer_pool to /incr01/ib_buffer_pool
    170425 11:30:35 [00]        ...done
    170425 11:30:35 Backup created in directory '/incr01/'
    170425 11:30:35 [00] Writing backup-my.cnf
    170425 11:30:35 [00]        ...done
    170425 11:30:35 [00] Writing xtrabackup_info
    170425 11:30:35 [00]        ...done
    xtrabackup: Transaction log of lsn (2549924) to (2549933) was copied.
    170425 11:30:35 completed OK!

确认备份好的文件数据:

[root@dbsvr1 ~]# ls /incr01/
    backup-my.cnf  ib_buffer_pool  ibdata1.meta  performance_schema  xtrabackup_checkpoints  xtrabackup_logfile
    db1            ibdata1.delta   mysql         sys

对比完整备份、增量备份的大小:

[root@dbsvr1 ~]# du -sh /backup/mysql /incr01/
    142M    /backup/mysql                              //完整备份的大小
    3.5M    /incr01/                              //增量备份的大小

步骤三:准备用于恢复的数据库目录

通过XtraBackup工具备份的数据库目录,若要恢复到另一个MySQL服务器,需要先做一个“--apply-log --redo-only ”的准备操作。

1)准备恢复“完整备份”

完成准备以后,最终/backup可用来重建MySQL服务器。这种情况下,需要先做一个“--apply-log --redo-only ”的准备操作,以确保数据一致性:

[root@dbsvr1 ~]#innobackupex  --user=root --password=123456 --apply-log  --redo-only /backup/mysql      //对备份数据库的位置做日志回读操作
    170425 11:42:19 innobackupex: Starting the apply-log operation
    IMPORTANT: Please check that the apply-log run completes successfully.
               At the end of a successful apply-log run innobackupex
               prints "completed OK!".
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: cd to /backup/mysql
    xtrabackup: This target seems to be already prepared.
    InnoDB: Number of pools: 1
    xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = .
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = .
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.7
    InnoDB: Number of pools: 1
    InnoDB: Not using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Highest supported file format is Barracuda.
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 2544177
    InnoDB: Number of pools: 1
    170425 11:42:20 completed OK!

准备恢复“增量备份”

[root@dbsvr1 ~]#innobackupex  --user=root --password=123456 --apply-log  --redo-only /backup/mysql  --incremental-dir=/incr01
    170425 11:42:55 innobackupex: Starting the apply-log operation
    IMPORTANT: Please check that the apply-log run completes successfully.
               At the end of a successful apply-log run innobackupex
               prints "completed OK!".
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    incremental backup from 2543884 is enabled.
    xtrabackup: cd to /backup/
    xtrabackup: This target seems to be already prepared with --apply-log-only.
    InnoDB: Number of pools: 1
    xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2549924)
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = /incr01/
    xtrabackup:   innodb_log_files_in_group = 1
    xtrabackup:   innodb_log_file_size = 8388608
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
    xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytes
    Applying /incr01//ibdata1.delta to ./ibdata1...
    ... ...
    170425 11:43:09 [01] Copying /incr01/performance_schema/global_status.frm to ./performance_schema/global_status.frm
    170425 11:43:09 [01]        ...done
    170425 11:43:09 [01] Copying /incr01/performance_schema/session_status.frm to ./performance_schema/session_status.frm
    170425 11:43:09 [01]        ...done
    170425 11:43:09 [00] Copying /incr01//xtrabackup_info to ./xtrabackup_info
    170425 11:43:09 [00]        ...done
    170425 11:43:10 completed OK!

2)关闭mysql服务,并将/var/lib/mysql/下的文件删除,假设数据被删除。

[root@dbsvr1 ~]#systemctl  stop  mysqld
    [root@dbsvr1 ~]#rm -rf /var/lib/mysql

3)恢复“完整备份+增量备份”

完成准备以后,最终仍然是/backup用来重建MySQL服务器,但这种情况下需提前合并相关增量备份的数据

[root@dbsvr1 ~]# mkdir /var/lib/mysql       //注意恢复数据库前,重建/var/lib/mysql数据库目录,因为刚才为了做假设将目录删掉了;后面测试发现这个目录在innobackupex命令下会自动创建,所以这步骤可以没有
    [root@dbsvr1 ~]# chown -R mysql:mysql /var/lib/mysql     //创建的目录需要归档为mysql用户和mysql组,否则启动会报错,保证mysql用户对此目录的权限;如果上面没创建目录这步骤可以不要,后面等innobackupex恢复命令执行后(自动创建这个目录),再归档

[root@dbsvr1 ~]# innobackupex --user=root --password=123456 --copy-back  /backup/mysql     //将backup/mysql里面的数据恢复回来
    ... ...
    170425 11:51:39 [01] Copying ./performance_schema/global_status.frm to /var/lib/mysql/performance_schema/glo.frm
    170425 11:51:39 [01]        ...done
    170425 11:51:39 [01] Copying ./performance_schema/session_status.frm to /var/lib/mysql/performance_schema/seus.frm
    170425 11:51:39 [01]        ...done
    170425 11:51:39 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
    170425 11:51:39 [01]        ...done
    170425 11:51:39 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
    170425 11:51:39 [01]        ...done
    170425 11:51:39 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
    170425 11:51:39 [01]        ...done
    170425 11:51:39 completed OK!

4)修改/var/lib/mysql/下文件属主与属组,查看数据:

恢复后,/var/lib/mysql下文件属组与属主皆为root,需要更改为mysql

[root@dbsvr1 ~]#chown -R mysql:mysql /var/lib/mysql      //chown -R 递归归属文件属性,这步骤最好放在第三步骤操作
    [root@dbsvr1 ~]#systemctl  start mysqld.service
    [root@dbsvr1 ~]#mysql -uroot -p12345678 -e "select * from db1.tb1"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | bob  |
    |    2 | bo   |
    +------+------+

三、MySQL AB复制

目标:

  1. 配置2台MySQL服务器,实现 主-->从 同步。
  2. 其中Master服务器允许SQL查询、写入,Slave服务器只允许SQL查询

方案:

使用2台RHEL 6虚拟机,如下图所示。其中192.168.4.10是MySQL主服务器,负责提供同步源;另一台192.168.4.20作为MySQL从服务器, 通过调取主服务器上的binlog日志,在本地重做对应的库、表,实现与主服务器的AB复制(同步)。

Database基础(五):使用binlog日志、XtraBackup备份工具、MySQL AB复制

提前为两台MySQL服务器安装好MySQL-server、MySQL-Client软件包,并为数据库用户root修改密码;Linux客户机上则只需安装MySQL-Client软件包即可。

步骤:

步骤一:初始化现有库

为了在启用binlog日志及同步之前保持主、从库的一致性,建议进行初始化——备份主服务器上现有的库,然后导入到从服务器上。

当现有库、表都采用MyISAM引擎时,可执行离线备份、恢复,这样更有效率;否则,可通过mysqldump等工具来实现库的导出、导入。

1)备份MySQL Master(192.168.4.10)上现有的库

如果服务器已经启用binlog,建议对日志做一次重置,否则可忽略:

[root@dbsvr1 ~]# mysql -u root -p       //可以这里-p后面紧跟密码,即可直接进入数据库
    Enter password:                                  //以数据库用户root登入
    .. ..
    mysql> RESET MASTER;                              //重置binlog日志
    Query OK, 0 rows affected (0.06 sec)
    mysql> quit                                      //退出mysql> 环境
    Bye

以备份mysql库、sys库为例,导出操作如下:

[root@dbsvr1 ~]# mysqldump -u root -p --all-databases  > /root/mytest.sql     //可以这里-p后面紧跟密码
    Enter password:                                      //验证口令
    [root@dbsvr1 ~]# ls -lh /root/mytest.sql              //确认备份结果
    -rw-r--r--. 1 root root 777172 4月  23 12:21 /root/mytest.sql

2)在MySQL Slave(192.168.4.20)上导入备份的库

先清理目标库,避免导入时冲突。主要是采用InnoDB引擎的库,授权库mysql多采用MyISAM引擎,可不做清理。

[root@dbsvr2 ~]# mysql -u root -p
    Enter password:                                  //以数据库用户root登入
    .. ..
    mysql> DROP DATABASE test;                          //删除test库
    Query OK, 0 rows affected (0.03 sec)
    mysql> quit                                      //退出mysql> 环境
    Bye

使用scp工具下载备份文件:

[root@dbsvr1 ~]# scp /root/mytest.sql  root@192.168.4.20:/
    root@dbsvr1's password:                          //验证对方系统用户root的口令
    mytest.sql                                  100%  759KB 759.0KB/s   00:00
    [root@dbsvr1 ~]# ls -lh mytest.sql              //确认下载结果
    -rw-r--r--. 1 root root 759K 4月  23 12:22 /mytest.sql

执行导入操作:

[root@dbsvr2 ~]# mysql -u root -p < /mytest.sql
    Enter password:                                  //验证口令

导入成功后,可重新登入 mysql> 环境,确认清理的目标库已恢复:    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                 |                          
    +--------------------+
    4 rows in set (0.00 sec)

步骤二:配置MySQL Master(主服务器,192.168.4.10)

1)修改/etc/my.cnf配置,重新启动MySQL服务程序

指定服务器ID号、允许日志同步:

[root@dbsvr1 mysql]# vim /etc/my.cnf
    [mysqld]
    log_bin=dbsvr1-bin                      //启用binlog日志,并指定文件名前缀
    server_id = 10                          //指定服务器ID号
    ......

重启mysql服务:

[root@dbsvr1 ~]# systemctl  restart mysqld.service

2)新建一个备份用户,授予复制权限

需要的权限为REPLICATION SLAVE,允许其从Slave服务器访问:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicater'@'192.168.4.%' IDENTIFIED BY 'pwd123';
    Query OK, 0 rows affected, 1 warning (0.09 sec)

3)检查Master服务器的同步状态

在已经初始化现有库的情况下,查看MASTER状态,记录下当前的日志文件名、偏移的位置(下面SLAVE发起复制时需要用到):

mysql> SHOW MASTER STATUS\G
    *************************** 1. row ***************************
                 File: dbsvr1-bin.000001              //记住当前的日志文件名
             Position: 154                             //记住当前的位置
         Binlog_Do_DB:
     Binlog_Ignore_DB:
    Executed_Gtid_Set:
    1 row in set (0.00 sec)

步骤三:配置MySQL Slave(从服务器,192.168.4.20)

1)修改/etc/my.cnf配置,重新启动MySQL服务程序

指定服务器ID号、允许日志同步:    [root@dbsvr2 ~]# vim /etc/my.cnf
    [mysqld]
    log_bin=dbsvr2-bin                      //启动SQL日志,并指定文件名前缀
    server_id = 20                          //指定服务器ID号,不要与Master的相同
    .. ..

在生产环境中,还可以根据需要设置更详细的同步选项。比如,指定当主、从网络中断时的重试超时时间(slave-net-timeout=60 )等,具体可参考MySQL手册。

配置完成后,重启mysql服务:

[root@dbsvr2 ~]# systemctl  restart mysqld.service

通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置(参考MASTER上的状态输出):

mysql> CHANGE MASTER TO MASTER_HOST='192.168.4.10',
        -> MASTER_USER='replicater',
        -> MASTER_PASSWORD='pwd123',
        -> MASTER_LOG_FILE='dbsvr1-bin.000002',      //对应Master的日志文件
        -> MASTER_LOG_POS=334;                          //对应Master的日志偏移位置
    Query OK, 0 rows affected, 2 warnings (0.12 sec)

然后执行START SLAVE(较早版本中为SLAVE START)启动复制:

mysql> START SLAVE;                              //启动复制
    Query OK, 0 rows affected (0.00 sec)

注意:一旦启用SLAVE复制,当需要修改MASTER信息时,应先执行STOP SLAVE停止复制,然后重新修改、启动复制。

通过上述连接操作,MASTER服务器的设置信息自动存为master.info文件,以后每次MySQL服务程序时会自动调用并更新,无需重复设置。查看master.info文件的开头部分内容,可验证相关设置:

[root@dbsvr2 ~]# ls -lh /var/lib/mysql/master.info
    -rw-r-----. 1 mysql mysql 132 4月  23 12:06 /var/lib/mysql/master.info
    [root@dbsvr2 ~]# head /var/lib/mysql/master.info     //注意这个文件是保存在slave服务器的
    25
    dbsvr1-bin.000001
    154
    192.168.4.10
    replicater
    pwd123
    3306
    60
    0

2)检查Slave服务器的同步状态

通过SHOW SLAVE STATUS语句可查看从服务器状态,确认其中的IO线程、SQL线程正常运行,才能成功同步:

mysql> SHOW SLAVE STATUS\G
    Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.4.1
                      Master_User: replicater
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: dbsvr1-bin.000001
              Read_Master_Log_Pos: 154
                   Relay_Log_File: db2-relay-bin.000003
                    Relay_Log_Pos: 321
            Relay_Master_Log_File: dbsvr1-bin.000001
                 Slave_IO_Running: Yes             //IO线程应该已运行
                Slave_SQL_Running: Yes             //SQL线程应该已运行
                  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: 154
                  Relay_Log_Space: 2490
                  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: 10
                      Master_UUID: 2d4d8a11-27b7-11e7-ae78-52540055c180
                 Master_Info_File: /var/lib/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)

若START SLAVE直接报错失败,请检查CHANGE MASTER相关设置是否有误,纠正后再重试;若IO线程或SQL线程有一个为“No”,则应检查服务器的错误日志,分析并排除故障后重启主从复制。

步骤四:测试主从同步效果

1)在Master上操作数据库、表、表记录

新建newdb库、newtable表,随意插入几条表记录:

mysql> CREATE DATABASE newdb;                          //新建库newdb
    Query OK, 1 row affected (0.17 sec)
    mysql> USE newdb;                                      //切换到newdb库
    Database changed
    mysql> CREATE TABLE newtable(id int(4));              //新建newtable表
    Query OK, 0 rows affected (0.46 sec)
    mysql> INSERT INTO newtable VALUES(1234),(5678);      //插入2条表记录
    Query OK, 2 rows affected (0.24 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> SELECT * FROM newtable;                         //确认表数据
    +------+
    | id   |
    +------+
    | 1234 |
    | 5678 |
    +------+
    2 rows in set (0.00 sec)

2)在Slave上确认自动同步的结果

直接切换到newdb库,并查询newtable表的记录,应该与Master上的一样,这才说明主从同步已经成功生效:

mysql> USE newdb;                                      //直接切换到newdb库
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed
    mysql> SELECT * FROM newtable;                      //输出表记录
    +------+
    | id   |
    +------+
    | 1234 |
    | 5678 |
    +------+
    2 rows in set (0.02 sec)

3)在Master服务器上可查看Slave主机的信息

mysql> SHOW SLAVE HOSTS;
    +-----------+------+------+-----------+--------------------------------------+
    | Server_id | Host | Port | Master_id | Slave_UUID                           |
    +-----------+------+------+-----------+--------------------------------------+
    |         2 |      | 3306 |        10 | 512cf7c1-27c4-11e7-8f4b-5254007b030b |
    +-----------+------+------+-----------+--------------------------------------+
    1 row in set (0.00 sec)

步骤五:将Slave服务器设为只读

一般来说,为了避免写入冲突,采用主、从复制结构时,不应该允许用户从Slave执行数据库写入操作,这样会导致双方数据的不一致性。

正因为如此,我们可以把Slave数据库限制为只读模式,这种情况下有SUPER权限的用户和SLAVE同步线程才能写入。相关验证操作及效果可参考以下过程。

1)新建一个测试用户rwuser(不能用root测试)

在Master上建立即可,会自动同步到Slave上:

mysql> GRANT all ON newdb.* TO rwuser@localhost IDENTIFIED BY '1234567';
    Query OK, 0 rows affected, 1 warning (0.09 sec)

2)未启用只读前,验证从Slave写入

在Slave上以rwuser登入(不要用root哦):

[root@dbsvr2 ~]# mysql -u rwuser -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 30
    Server version: 5.7.17-log MySQL Community Server (GPL)
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    mysql>

然后向newdb库中新建一个booker表:

mysql> USE newdb;                                  //切换到newdb库
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed
    mysql> CREATE TABLE booker(id int(12));          //成功创建booker表
    Query OK, 0 rows affected (0.64 sec)

在Slave上可看到新建的booker表:

mysql> SHOW TABLES;
    +-----------------+
    | Tables_in_newdb |
    +-----------------+
    | booker          |
    | newtable        |
    +-----------------+
    2 rows in set (0.00 sec)

但是在Master上却看不到,导致主、从上的newdb出现不一致:

mysql> USE newdb;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed
    mysql> SHOW TABLES;                      //看不到Slave上新建的表
    +-----------------+
    | Tables_in_newdb |
    +-----------------+
    | newtable        |
    +-----------------+
    1 row in set (0.00 sec)

完成上述验证后,在Slave上删除booker表,确保双方一致:

mysql> DROP TABLE booker;
    Query OK, 0 rows affected (0.27 sec)

3)修改/etc/my.cnf文件,重载配置

[root@dbsvr2 ~]# vim /etc/my.cnf
    [mysqld]
    .. ..
    read_only=1                                      //启动只读模式
    [root@dbsvr2 ~]# systemctl restart mysqld.service          //重启服务

4)再次在Slave上验证数据库写入操作

仍然是以rwuser登入(不要用root哦)来验证,当尝试创建新表时会被拒绝:

mysql> USE newdb;                                      //切换到newdb库
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed
    mysql> CREATE TABLE booker(id int(12));     //新建表的写入操作失败
    ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
    mysql> DROP TABLE mytable;                      //删除表的写入操作一样会失败
    ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

附加:

一、设置binlog日志,修改配置文件后,启动mysqld服务报错,排错思路

1> 检查SELinux是否设置为Permissing,如果没有就改为Permissing,可以直接设置setenforce 0【临时Permissive】,也可以修改/etc/selinux/config配置文件,设置selinux永久Permissive

2> 检查/etc/my.cnf配置文件里binlog日志文件log_bin=/tmp/logdir/plj的修改路径/tmp/logdir/plj有没有创建

3> 创建了路径目录/tmp/logdir/plj后,需要chown mysql /tmp/logdir  将文件目录归属给mysql用户,注意:备份的日志文件都在/tmp/logdir/下面,而不是/tmp/logdir/plj下面

4> 检查httpd服务是否开启,有授权给网段用户可以登录的话,必须启动httpd才能正常启动mysqld

二、mysql数据备份恢复,启动mysqld服务报错,排错思路

1> mysql在设置备份服务后,备份完成后,重启mysqld服务失败,检查/var/lib/mysql目录是否归档为mysql用户和mysql组。

全备份和增量备份思路:

备份:
# innobackupex --user=root --password=123456 /backup/mysql –no-timestamp     //全备份
# innobackupex  --user=root --password=123456 --incremental /incr01 --incremental-basedir=/backup/mysql --no-timestamp    //增量备份
# du -sh /backup/ /incr01/     //查看全备份和增量备份的文件大小

恢复:
# innobackupex  --user=root --password=123456 --apply-log  --redo-only /backup/mysql    //全备份的恢复
# innobackupex  --user=root --password=123456 --apply-log  --redo-only /backup/mysql  --incremental-dir=/incr01    //增量备份的恢复

模拟数据库误删除操作:
# systemctl  stop  mysqld    
# rm -rf /var/lib/mysql

重建数据库目录并归档:
# mkdir /var/lib/mysql     //重建/var/lib/mysql数据库目录,这步骤可以不要,下面innobackupex会自动创建
# chown -R mysql:mysql /var/lib/mysql    //保证mysql用户对此目录的权限,上面若没有创建目录,这步骤可以放到下面

# innobackupex --user=root --password=123456 --copy-back  /backup/mysql     //将backup/mysql里面的数据恢复回来
# chown -R mysql:mysql /var/lib/mysql    //保证mysql用户对此目录的权限,上面若有这步骤可省略
# systemctl  start mysqld.service   //重启数据库服务,登录验证

  附加:

摘自:

Database基础(五):使用binlog日志、XtraBackup备份工具、MySQL AB复制       http://www.linuxidc.com/Linux/2014-02/96945.htm

MySQL主从同步故障-Slave_SQL_Running: No

故障现象:
进入slave服务器,运行:
mysql> show slave status\G
        .......
            Relay_Log_File: localhost-relay-bin.000535
            Relay_Log_Pos: 21795072
    Relay_Master_Log_File: localhost-bin.000094
Slave_IO_Running: Yes
Slave_SQL_Running: No
          Replicate_Do_DB:
      Replicate_Ignore_DB:
      ......
解决办法一、
Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.

一般是事务回滚造成的:
解决办法:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
解决办法二、
首先停掉Slave服务:slave stop
到主服务器上查看主机状态:
记录File和Position对应的值
进入master
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
然后到slave服务器上执行手动同步:

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
........
          Master_Log_File: localhost-bin.000094
      Read_Master_Log_Pos: 33768775
            Relay_Log_File: localhost-relay-bin.000537
            Relay_Log_Pos: 1094034
    Relay_Master_Log_File: localhost-bin.000094
          Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
手动同步需要停止master的写操作!
查看mysql主从配置的状态及修正 slave不启动问题
1、查看master的状态
show master status;  //Position不应该为0
show processlist; 
//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated
2、查看slave状态
show slave status;
//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes
show processlist;
//应该有两行state值为:
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

3、错误日志
MySQL安装目录 /usr/local/mysql
MySQL日志目录 /usr/local/mysql/data/  形如,Hostname.err

4、Change master to
如果从库的Slave未启动,Slave_IO_Running为NO。
可能是主库是的master的信息有变化,
查看主库show master status;
记录下File,Position字段,假设为‘mysql-bin.000004’,98;
在从库执行:

mysql>stop slave;
mysql>change master to master_log_file='mysql-bin.000004',master_log_pos=98;
mysql>start slave;

5、SET global sql_slave_skip_counter=n;
如果从库的slave_sql_running为NO。
Err文件中记录:
Slave:Error "Duplicate entry '1' for key 1" on query.....
可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突可以在从库上执行
set global sql_slave_skip_counter=n;
跳过几步。再restart slave就可以了。

6、同步错误处理
发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错,整个同步也会因此而卡在那,最初的办法只是手动去出错的机器执行下面三条SQL语句,跳过错误即可。

mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;

PS:本人多次遇到从数据库的同步进程自动停掉的问题,有时简单通过slave stop,slave start即可解决。有时slave start启动后又会自动停掉,这时使用 change master重设主数据库信息的方式解决了问题。

说明:
Slave_IO_Running:连接到主库,并读取主库的日志到本地,生成本地日志文件
Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令。

上一篇:Struts2 源码分析——配置管理之PackageProvider接口


下一篇:MySQL 逻辑备份工具