基于GTID的无损复制 包含percola-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz 使用

这个实验：是对现有的单机数据库，做一个无损的主从复制

数据库环境准备 

mysql -uroot -proot < /soft/itpuxdb.sql 

主备库都新建 复制用户

mysql -uroot -proot

create user 'repuser'@'%' identified by 'repuser123';

grant replication slave on *.* to 'repuser'@'%';

flush privileges;

select user,host from mysql.user;

exit;

## 192.168.1.50(master)

vi /mysql/data/3306/my.cnf

#master modify parameter

bind-address=0.0.0.0

server_id=503306

#master add parameter

log_bin=/mysql/log/3306/binlog/mysqldb-binlog

log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

 

skip_name_resolve = on

expire_logs_days = 7

innodb_support_xa =1

binlog_cache_size = 1M

max_binlog_size = 2048M

log_bin_trust_function_creators = 1

innodb_flush_log_at_trx_commit =1

sync_binlog = 1

transaction-isolation = read-committed

gtid_mode = on

enforce_gtid_consistency = 1

log-slave-updates = 1

binlog_gtid_simple_recovery=1

#--------------------------

#slave parameter

#--------------------------

#relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log

#read_only=1

#slave-parallel-type=LOGICAL_CLOCK

#slave-parallel-workers=4

#master_info_repository=TABLE

#relay_log_info_repository=TABLE

#relay_log_recovery=1

#slave_skip_errors = ddl_exist_errors

#slave_preserve_commit_order=1

#------------------------------------

#semi sync replication settings

#------------------------------------

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

## 192.168.0.51(slave)

vi /mysql/data/3306/my.cnf

#slave modify parameter

bind-address=192.168.1.51

server_id=513306

#slave add parameter

log_bin=/mysql/log/3306/binlog/mysqldb-binlog

log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

 

skip_name_resolve = on

expire_logs_days = 7

innodb_support_xa =1

binlog_cache_size = 1M

max_binlog_size = 2048M

log_bin_trust_function_creators = 1

innodb_flush_log_at_trx_commit =1

sync_binlog = 1

transaction-isolation = read-committed

gtid_mode = on

enforce_gtid_consistency = 1

log-slave-updates = 1

binlog_gtid_simple_recovery=1

#------------------------------------

#slave parameter

#------------------------------------

relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log

log-slave-updates = 1

read_only=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=4

master_info_repository=table

relay_log_info_repository=table

relay_log_recovery=1

slave_skip_errors = ddl_exist_errors

slave_preserve_commit_order=1

#------------------------------------

#semi sync replication settings

#------------------------------------

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

systemctl restart mysqld

mysql -uroot -proot

create database itpuxdb1;

create table itpuxdb1.itpuxbak11 (id int,name varchar(40));

insert into itpuxdb1.itpuxbak11 values(1,'itpux111'),(2,'itpux112'),(3,'itpux113'),(4,'itpux114'),(5,'itpux115');

commit;

select * from itpuxdb1.itpuxbak11;

 

scp -r /soft/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz 192.168.1.52:/soft

scp /soft/mysql-connector-python-2.1.7-1.el7.x86_64.rpm 192.168.1.52:/soft

scp /soft/rsync-3.1.2.tar.gz 192.168.1.52:/soft

scp -r /soft/ 192.168.1.52:/soft

cd /mysql/app

tar zxvf /soft/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz

ln -sf percona-xtrabackup-2.4.11-Linux-x86_64 xtrabackup

echo"PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin">> /etc/profile    # 不性的话，直接把路径复制进去就好了

source /etc/profile

xtrabackup --version

# | gzip 是通过gzip压缩

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup --stream=tar --parallel=2 --target-dir=/mysql/backup/fullback180520 | gzip >/mysql/backup/fullback180520.tar.gz          

#报错的话，加一参数   --socket=/tmp/mysql.sock

scp /mysql/backup/fullback180520.tar.gz 192.168.0.51:/mysql/backup/

到备库执行

cd /mysql/backup

mkdir -p /mysql/backup/fullback180520

tar zxvf /mysql/backup/fullback180520.tar.gz -C /mysql/backup/fullback180520

准备，保证数据一致

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --target-dir=/mysql/backup/fullback180520 --parallel=2

 

systemctl stop mysqld

清空掉数据，数据库重置

cd /mysql/data/3306/

rm -rf data_bak

mv data data_bak

mkdir data

chown -R mysql:mysql data

rsync -avrP /mysql/backup/fullback180520/* --exclude='xtrabackup_*' /mysql/data/3306/data/

rsync 同步

 

####################################    #########################

场景：主要是用来发布文件。

 

一、rsync服务器端架设

 

1、安装

 

wget https://download.samba.org/pub/rsync/src/rsync-3.0.6.tar.gz

tar -zxvf rsync-3.1.2.tar.gz

cd rsync-3.1.2

./configure --prefix=/usr/local/rsync

make

make install

2、配置  

 

2.1、配置rsyncd.conf 不存在就手动创建 touch /usr/local/rsync/rsyncd.conf

 

配置内容如下：

 

uid = root

gid = root

use chroot = yes

strict modes = false

pid file = /var/run/rsyncd.pid

lock file = /var/run/rsync.lock

####

log file = /var/log/rsyncd.log

transfer logging = true

log format= "%o [%a] %m (%u) %f %l %b %c"

##

reverse lookup = no

ignore errors = true

read only = false

hosts allow = 192.168.0.13

hosts deny = 0.0.0.0/0.0.0.0

strict modes = yes

list = false

max connections = 100

timeout = 1800

[www]

path = /data/www/

auth users = www

secrets file = /usr/local/rsync/rsyncd.secrets

 

2.2、配置rsyncd.secrets 不存在就手动创建 touch /usr/local/rsync/rsyncd.secrets

配置内容：

 

www:123456789

注意：此文件权限必须设置：600 命令如下

 

chmod 600 /usr/local/rsync/rsyncd.secrets

2.3、配置rsyncd.motd 不存在就手动创建 touch /usr/local/rsync/rsyncd.motd 该步骤可以省略

配置内容：

welcome use rsync service

 

2.4、启动rsyncd

 

查看rsyncd进程 命令如下：

 

ps aux | grep rsyncd

启动命令如下：

 

/usr/local/rsync/bin/rsync --daemon --config=/usr/local/rsync/rsyncd.conf

2.5、配置rsync开机启动

 

vi /etc/rc.local

在末尾加上 /usr/local/rsync/bin/rsync --daemon --config=/usr/local/rsync/rsyncd.conf

 

然后保存即可。

 

二、client端安装跟配置

 

安装rsync

 

yum -y install rsync

客户端建立密码文件

touch /etc/rsyncd.passwd 没有的话就自己创建

将其修改成12345678

 

注意:1、这里的密码，与服务端设置的密码相同，就是通过这个密码连接服务端的

     2、然后切记修改改密码文件的权限（密码文件权限属性要设得只有属主可读）

修改命令如下：

 

chmod 600 /etc/rsyncd.passwd

 

##################################################################

chown -R mysql:mysql data

systemctl start mysqld

mysql -uroot -proot

show databases;

select * from itpuxdb.itpux12 limit 2;

mysql -uroot -proot

mysql> show slave status \G

Empty set (0.00 sec)

#设置掉 GTID，看fullback文件夹里的 xtrabackup_info   binlog_pos参数

set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

set @@SESSION.SQL_LOG_BIN= 0;

set @@GLOBAL.GTID_PURGED='ff3df5b0-83c8-11eb-8074-000c296b41c7:1-3';

set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

change master to

master_host='192.168.0.50',

master_port=3306,

master_user='repuser',

master_password='repuser123',

master_auto_position=1;

start slave;

show slave status \G

create database itpuxdb2;

create table itpuxdb2.itpuxbak21 (id int,name varchar(40));

insert into itpuxdb2.itpuxbak21 values(1,'itpux211'),(2,'itpux212'),(3,'itpux213'),(4,'itpux214'),(5,'itpux215');

commit;

select * from itpuxdb2.itpuxbak21;

备库看下

select * from itpuxdb2.itpuxbak21;

mysql -uroot -proot

show slave status \G

show processlist;