MySQL复制从库建立-xtracebackup方式

percona xtrabackup工具提供了一种在系统运行时执行mysql数据热备份的方法。

percona xtrabackup在事务系统上执行联机非阻塞，紧密压缩，高度安全的完整备份，因此在计划的维护时段内，应用程序仍保持完全可用。

环境准备：

master:192.168.0.106:3306 
slave:192.168.0.105:3306

datadir: /data/mysql/mysql3306/data

cnfpath: /data/mysql/mysql3306/my3306.cnf

xtrabackup 安装，两台机器都安装：

下载地址：https://www.percona.com/downloads/percona-xtrabackup-latest/

themaster$ wget https://www.percona.com/downloads/percona-xtrabackup-latest/percona-xtrabackup-8.0-7/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
themaster$ yum install -y percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm

1、在master制作一个备份

themaster$ mkdir xtrabackup_backupfiles
themaster$ xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -s /tmp/mysql3306.sock -uroot -p'' --backup --target-dir=./xtrabackup_backupfiles
...
completed ok!

为了让快照是一致的:需要prepare备份数据：

themaster$ xtrabackup -s /tmp/mysql3306.sock -uroot -p --prepare --target-dir=./xtrabackup_backupfiles/
...
completed ok!

undo log目录需要处理下，默认会解压到xtrabackup_backupfiles目录下，在my3306.cnf中定义在/data/mysql/mysql3306/data/undolog/下，所以：

themaster$ cd xtrabackup_backupfiles && mkdir undolog && mv undo_* undolog/

2、复制备份数据到slave上

使用rsync或scp将数据从master复制到slave。如果直接将数据同步到slave的数据目录，建议在这之前先停止mysqld。

themaster$ rsync -avpp -e ssh ./xtrabackup_backupfiles/ 192.168.0.105:/data/mysql/mysql3306/data/

复制数据前，您可以备份原始或先前安装的mysql datadir（注意：在移动mysqld的内容或将快照移动到其datadir之前，请确保mysqld已关闭。）：

theslave$ mv /data/mysql/mysql3306/data /data/mysql/mysql3306/data_bak

复制数据后，确保mysql有权限访问

theslave$ chown -r mysql:mysql /data/mysql/mysql3306/data

3、创建master上配置复制账号

themaster|mysql> grant replication slave on *.*  to 'repl'@'192.168.0.%' identified by 'repl';

测试连接

theslave$ mysql --host=192.168.0.106 --user=repl --password=repl
mysql> show grants;

4、配置slave上的mysql服务器

首先将my3306.cnf从themaster复制到theslave：

theslave$ scp root@192.168.0.106:/data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3306/

theslave$ chown -r mysql:mysql /data/mysql/mysql3306/data/my3306.cnf

修改theslave上的my3306.cnf：

server-id=1053306  # 建议ip最后一位+端口号

启动theslave 上的 mysqld

theslave$ /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

观察error.log是否有错误

theslave$ more /data/mysql/mysql3306/data/error.log 

5、启动复制

theslave$ cat /data/mysql/mysql3306/data/xtrabackup_binlog_info
mysql-bin.000012        195     e8f74dde-ed8e-11e9-8ebb-000c29f5c092:1-3

执行change master语句在mysql命令行：

theslave|mysql> change master to
                master_host='192.168.0.106',
                master_user='repl',
                master_password='repl',
                master_auto_position=1;

开始复制：

theslave|mysql> start slave;

6、检查

theslave|mysql> show slave status \g
         ...
         slave_io_running: yes
         slave_sql_running: yes
         ...
         seconds_behind_master: 0
         ...

看到io和sql线程处于运行中 （yes）表示复制正常。

root@localhost 22:42:41 [(none)]> show slave status\g
error 2006 (hy000): mysql server has gone away
no connection. trying to reconnect...
connection id:    16
current database: *** none ***

*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: 192.168.0.106
                  master_user: repl
                  master_port: 3306
                connect_retry: 60
              master_log_file: mysql-bin.000016
          read_master_log_pos: 383
               relay_log_file: localhost-relay-bin.000002
                relay_log_pos: 557
        relay_master_log_file: mysql-bin.000016
             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: 383
              relay_log_space: 769
              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: 1063306
                  master_uuid: e8f74dde-ed8e-11e9-8ebb-000c29f5c092
             master_info_file: mysql.slave_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: e8f74dde-ed8e-11e9-8ebb-000c29f5c092:4
            executed_gtid_set: e8f74dde-ed8e-11e9-8ebb-000c29f5c092:1-4
                auto_position: 1
         replicate_rewrite_db: 
                 channel_name: 
           master_tls_version: 
       master_public_key_path: 
        get_master_public_key: 0
            network_namespace: 
1 row in set (0.00 sec)