Shulou(Shulou.com)05/31 Report--

This article is about MySQL 5.5 how to use Xtrabackup to build replication environment online. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

--Edit Master's profile

[root@localhost install]# vim /etc/my.cnf

# Log

server-id = 100

log-bin = /log/binlog/mysql-bin

--Install MySQL software in Slave, please refer to source code installation article for installation process

http://blog.itpub.net/26506993/viewspace-2072859/

--On the Master node, create a full backup using Xtrabackup

For Xtrabackup, please refer to

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

--Copy backup to Slave node

[root@localhost backup]# scp /backup/xtra/xtra_fullbackup_20160501.tar.gz root@192.168.78.137:/backup/20160501

Extract backup to data file directory

[root@localhost 5505]# tar xivfz xtra_fullbackup_20160501.tar.gz -C /mysql_data/5505

Xtraback needs to be installed on the Slave node

--Use Xtrabackup to prepare data, apply logs, and make data files consistent

[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!

--Create replication dedicated account on Master database

mysql> grant replication slave on *.* to 'repl'@'192.168.78.% ' identified by 'Mysql#2015';

Query OK, 0 rows affected (0.04 sec)

--Configure Slave profiles

[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

--Start Mysql service of Slave node

[root@localhost bin]# /data/bin/mysqld_safe --defaults-file=/mysql_data/cnf/my.cnf &

--Configure Slave node replication environment

Query Slave Node Binaries and Location of Connection Master Node

This information is saved in the xtrabackup_binlog_info file when using Xtrabackup backups

[root@localhost 5505]# more xtrabackup_binlog_info

mysql-bin.000012 414

--Execute CHANGE MASTER statement on Slave node

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)

--Start application thread

mysql> start slave;

--View synchronization status

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

Thank you for reading! About "MySQL 5.5 how to use Xtrabackup online build replication environment" This article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.