Setup a slave for replication with Percona XtraBackup

使用 master 搭建一个 新 slave

STEP 1:Make a backup on TheMaster and prepare it

1
TheMaster$ xtrabackup --defaults-file=/etc/my_3761.cnf --host=theMasterIP --user=dba --password="2018" --port=3761 --backup --parallel=4   --target-dir=/path/to/backupdir
1
TheMaster$ xtrabackup --defaults-file=/etc/my_3761.cnf --host=theMasterIP --user=dba --password="dba@2020" --port=3761 --prepare --parallel=4   --target-dir=path/to/backupdir

STEP 2: Copy backed up data to TheSlave

1
TheMaster$ rsync -avpP -e ssh /path/to/backupdir TheSlave:/path/to/mysql/  
1
TheSlave$ chown mysql:mysql /path/to/mysql/datadir

STEP 3: Configure The Master’s MySQL server

1
2
TheMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'$slaveip'
IDENTIFIED BY '$slavepass';

STEP 4: Configure The Slave’s MySQL server

1
TheSlave$ scp user@TheMaster:/etc/mysql/my.cnf /etc/mysql/my.cnf

修改 server-id。

STEP 5: Start the replication

1
TheSlave$ cat xtrabackup_binlog_info  
1
2
3
4
5
6
TheSlave|mysql> CHANGE MASTER TO
MASTER_HOST='$masterip',
MASTER_USER='repl',
MASTER_PASSWORD='$slavepass',
MASTER_LOG_FILE='TheMaster-bin.000001',
MASTER_LOG_POS=481;  
1
TheSlave|mysql> START SLAVE;

如果启动失败可能需要手动创建错误日志文件。

Adding more slaves to The Master

在已有的主从基础上，增加更多的 slave 节点。

在已有的 slave 上创建一个全备

1
TheSlave$ xtrabackup --user=yourDBuser --password=MaGiCiGaM --backup --slave-info --target-dir=/path/to/backupdir

使用 –slave-info 会创建一个 xtrabackup_slave_info 文件。接着 Apply the logs ，

1
TheSlave$ xtrabackup --prepare --use-memory=2G --target-dir=/path/to/backupdir/

将备份传输到新 slave 上

1
rsync -avprP -e ssh /path/to/backupdir TheNewSlave:/path/to/mysql/datadir

从已有的 slave 上拷一份配置文件

1
TheNEWSlave$ scp user@TheSlave:/etc/mysql/my.cnf /etc/mysql/my.cnf

增加或修改如下参数

1
2
skip-slave-start
server-id=3  
1
2
3
4
5
6
7
8
9
10
cat xtrabackup_slave_info  

TheNEWSlave|mysql> CHANGE MASTER TO
MASTER_HOST='$masterip',
MASTER_USER='repl',
MASTER_PASSWORD='$slavepass',
MASTER_LOG_FILE='TheMaster-bin.000001',
MASTER_LOG_POS=481;  

TheNEWSlave|mysql> START SLAVE;

总结：和基于 master 的搭建差不多，只不过备份的时候需要加 –slave-info 参数。

Create a new (or repair a broken) GTID based slave

STEP 1: Take a backup from any server on the replication environment, master or slave

在任意节点全备：

1
innobackupex /data/backups/  
1
2
$ cat xtrabackup_binlog_info
mysql-bin.000002 1232 c777888a-b6df-11e2-a604-080027635ef5:1-4

STEP 2: Prepare the backup

1
TheMaster$ innobackupex --apply-log /data/backups/$TIMESTAMP/

STEP 3: Move the backup to the destination server

1
TheMaster$ rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP NewSlave:/path/to/mysql/  
1
NewSlave$ chown mysql:mysql /path/to/mysql/datadir

STEP 4: Configure and start replication

1
2
3
4
5
6
NewSlave > SET GLOBAL gtid_purged="c777888a-b6df-11e2-a604-080027635ef5:1-4";
NewSlave > CHANGE MASTER TO
MASTER_HOST="$masterip",
MASTER_USER="repl",
MASTER_PASSWORD="$slavepass",
MASTER_AUTO_POSITION = 1;