MySQL 5.5使用Xtrabackup在线搭建复制环境

我就是我 2024-04-18 01:52 55阅读 0赞

\--Master IP 192.168.78.139  PORT 3306  
 \--Slave IP 192.168.78.137  PORT 5505  
  
**--编辑Master的配置文件**  
 \[root@localhost install\]\# vim /etc/my.cnf  
 \# Log  
 server-id = 100  
 log-bin = /log/binlog/mysql-bin  
  
 \--在Slave安装好MySQL软件，安装流程可以参考源码安装文章  
 http://blog.itpub.net/26506993/viewspace-2072859/  
  
**--在Master节点，使用Xtrabackup创建完整备份**  
 关于Xtrabackup，可参考  
 http://blog.itpub.net/26506993/viewspace-2087734/  
 http://blog.itpub.net/26506993/viewspace-2088737/  
  
 \[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex  --defaults-file=/etc/my.cnf --stream=tar /tmp --user system --password 'Mysql\#2015' | gzip ->   
 /backup/xtra/xtra\_fullbackup\_20160501.tar.gz  
  
 \--拷贝备份到Slave节点  
 \[root@localhost backup\]\# scp /backup/xtra/xtra\_fullbackup\_20160501.tar.gz  root@192.168.78.137:/backup/20160501  
  
 解压备份到数据文件目录  
 \[root@localhost 5505\]\# tar xivfz xtra\_fullbackup\_20160501.tar.gz -C /mysql\_data/5505  
  
 在Slave节点上面需要安装Xtraback  
**--使用Xtrabackup准备数据、应用日志，使数据文件达到一致性的状态**  
 \[root@localhost bin\]\# ./innobackupex --defaults-file=/mysql\_data/cnf/my.cnf  --apply-log /mysql\_data/5505  
 .....  
 InnoDB: Doing recovery: scanned up to log sequence number 68405269 (0%)  
 InnoDB: Doing recovery: scanned up to log sequence number 68405269 (0%)  
 InnoDB: Database was not shutdown normally!  
 InnoDB: Starting crash recovery.  
 InnoDB: xtrabackup: Last MySQL binlog file position 414, file name /log/binlog/mysql-bin.000012  
 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: 5.7.11 started; log sequence number 68405269  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 68405621  
 160430 23:51:25 completed OK!  
  
 \--在Master数据库上面创建复制专用账户  
 mysql> grant replication slave on \*.\* to 'repl'@'192.168.78.%' identified by 'Mysql\#2015';  
 Query OK, 0 rows affected (0.04 sec)  
  
**--配置Slave的配置文件**  
 \[root@localhost 5505\]\# vim /mysql\_data/cnf/my.cnf  
 \# Log  
 server-id = 200  
 log-bin = /mysql\_log/binlog/mysql-bin  
 relay-log = /mysql\_log/binlog/product-relay-bin  
 relay-log-index = /mysql\_log/binlog/product-relay-index  
 binlog\_cache\_size = 32M  
 max\_binlog\_cache\_size = 512M  
 max\_binlog\_size = 512M  
 binlog\_format = MIXED  
 relay\_log\_recovery = 1  
  
**--启动Slave节点的Mysql服务**  
 \[root@localhost bin\]\# /data/bin/mysqld\_safe --defaults-file=/mysql\_data/cnf/my.cnf &  
  
**--配置Slave节点复制环境**  
 查询Slave节点连接Master节点的二进制文件和位置  
 使用Xtrabackup备份时，在xtrabackup\_binlog\_info文件中会保存这部分信息  
 \[root@localhost 5505\]\# more xtrabackup\_binlog\_info  
 mysql-bin.000012 414  
  
**--在Slave节点执行CHANGE MASTER语句**  
 mysql> change master to  
     -> master\_host='192.168.78.139',  
     -> master\_port=3306,  
     -> master\_user='repl',  
     -> master\_password='Mysql\#2015',  
     -> master\_log\_file='mysql-bin.000012',  
     -> master\_log\_pos=414;  
 Query OK, 0 rows affected (0.13 sec)  
  
 \--启动应用线程  
 mysql> start slave;

\--查看同步状态  
 mysql> show slave status\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Slave\_IO\_State: Waiting for master to send event  
                   Master\_Host: 192.168.78.141  
                   Master\_User: repl  
                   Master\_Port: 3306  
                 Connect\_Retry: 60  
               Master\_Log\_File: mysql-bin.000013  
           Read\_Master\_Log\_Pos: 341  
                Relay\_Log\_File: product-relay-bin.000003  
                 Relay\_Log\_Pos: 487  
         Relay\_Master\_Log\_File: mysql-bin.000013  
              Slave\_IO\_Running: Yes  
             Slave\_SQL\_Running: Yes

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2091601/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2091601/