OS: Ubuntu 16.04
DB: MySQL 5.7.25
规划如下:
192.168.5.92 node1 # mysql master
192.168.5.90 node2 # mysql slave
192.168.5.88 node3 # mysql slave
添加一个新的salve节点 node4
192.168.56.86 node4 # mysql slave
Xtrabackup 备份和还原
在Master上做个全量备份和增量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --backup --datadir=/var/lib/mysql --target-dir="/mnt/mysql_3306/xtrabackup_backup/20190305/ful" --user="root" --password="mysql" --host="localhost" --port=3306 --socket="/var/run/mysqld/mysqld.sock" --no-version-check --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --backup --datadir=/var/lib/mysql --target-dir="/mnt/mysql_3306/xtrabackup_backup/20190305/inc" --incremental-basedir="/mnt/mysql_3306/xtrabackup_backup/20190305/ful" --user="root" --password="mysql" --host="localhost" --port=3306 --socket="/var/run/mysqld/mysqld.sock" --no-version-check --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp
传输到新Slave机器上
# cd /mnt/mysql_3306/xtrabackup_backup
# tar -zcvf ./20190305.tar.gz ./20190305
# scp ./20190305.tar.gz 192.168.5.86:/mnt/mysql_3306/xtrabackup_backup
新Slave执行恢复操作
# cd /mnt/mysql_3306/xtrabackup_backup
# tar -zxvf ./20190305.tar.gz
prepare全量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --prepare --apply-log-only --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful
prepare增量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --prepare --apply-log-only --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful --incremental-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/inc
prepare全量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --prepare --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful
拷贝数据
# /usr/bin/xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mysql
# cd /var/lib/mysql
# cat xtrabackup_info
uuid = 66a4910f-3efb-11e9-95f7-080027c780f8
name =
tool_name = xtrabackup
tool_command = --defaults-file=/etc/mysql/my.cnf --backup --datadir=/var/lib/mysql --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/inc/ --incremental-basedir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful --user=root --password=... --host=localhost --port=3306 --socket=/var/run/mysqld/mysqld.sock --no-version-check --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp
tool_version = 2.4.13
ibbackup_version = 2.4.13
server_version = 5.7.25-log
start_time = 2019-03-05 12:01:47
end_time = 2019-03-05 12:01:49
lock_time = 0
binlog_pos = filename ‘mysql-bin.000002‘, position ‘1284‘, GTID of the last change ‘c74577c8-3e31-11e9-bf54-080027c780f8:1-6‘
innodb_from_lsn = 2524607
innodb_to_lsn = 2524607
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N
Slave节点设置
# mysql -h 127.0.0.1 -P 3306 -u root -p
mysql> show slave status\G
如果是对Master做的备份, 执行show slave status\G的结果就是为空.需要change master to
mysql> stop slave;
mysql> change master to master_host=‘192.168.5.92‘,master_port=3306,master_user=‘repl‘,master_password=‘mysql‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=1284;
mysql> start slave
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
基于GTID的change master to
mysql> change master to master_host=‘192.168.5.92‘,master_port=3306,master_user=‘repl‘,master_password=‘mysql‘,master_auto_position=1;
参考:
https://www.cnblogs.com/zhoujinyi/p/5893333.html
http://www.percona.com/software/percona-xtrabackup
https://www.percona.com/downloads/
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
MySQL初始化
# which mysqld
# /usr/sbin/mysqld --user=mysql --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql --initialize-insecure --ssl --explicit_defaults_for_timestamp
# /usr/bin/mysql_ssl_rsa_setup --user=mysql
# systemctl start mysql
# mysql -h 127.0.0.1 -P 3306 -u root -p
mysql> alter user root@‘localhost‘ identified by ‘mysql‘;