数据库环境准备
三台
-
配置启动主从复制参数
mysql -uroot -proot
create user 'repuser'@'%' identified by 'repuser123';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
## 192.168.1.51(master)
vi /mysql/data/3306/my.cnf
#master modify parameter
bind-address=192.168.1.51
server_id=513306
#master add parameter
log_bin=/mysql/log/3306/binlog/itpuxdb-binlog
log_bin_index=/mysql/log/3306/binlog/itpuxdb-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/itpuxdb-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.1.52/53(slave)
vi /mysql/data/3306/my.cnf
#slave modify parameter
bind-address=192.168.1.52
server_id=523306
#slave add parameter
log_bin=/mysql/log/3306/binlog/itpuxdb-binlog
log_bin_index=/mysql/log/3306/binlog/itpuxdb-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/itpuxdb-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
-
数据备份到51
主从 3 台：
cd /opt
scp MySQL* 192.168.0.51:/opt
scp MySQL* 192.168.0.52:/opt
unzip MySQL*.zip
tar zxvf meb-4.1.1-linux-glibc2.12-x86-64bit.tar.gz
ln -sf meb-4.1.1-linux-glibc2.12-x86-64bit mysqlbackup
cp mysqlbackup/bin/mysqlbackup /mysql/app/mysql/bin/
mysqlbackup --version
mkdir -p /mysql/backup/backup-db
mkdir -p /mysql/backup/backup-tmp
mkdir -p /mysql/backup/restore-tmp
chown -R mysql:mysql /mysql/backup
chmod -R 775 /mysql/backup
set global event_scheduler =1; 存储过程做下
create database itpuxdb;
drop table itpuxdb.itpux12;
create table itpuxdb.itpux12(
id int not null primary key auto_increment,
name varchar(60),
age int);
use itpuxdb;
select count(*) from itpuxdb.itpux12;
主库 51：
mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup-image=/mysql/backup/backup-db/fullbackup`date +%Y%m%d`.mbi --with-timestamp backup-to-image --backup-dir=/mysql/backup/backup-tmp
#验证
mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup20210418.mbi validate
从库 52：
scp 192.168.0.50:/mysql/backup/backup-db/fullbackup20210418.mbi /mysql/backup/backup-db/
systemctl stop mysqld
cd /mysql/data/3306
rm -rf data_bak
mv data data_bak
mkdir data
mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/backup-db/fullbackup20210418.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log
图解：报错，改一条下面的参数
#验证不通过改 my.cnf 参数改成：innodb_checksum_algorithm=crc32
chown -R mysql:mysql /mysql/data/3306/data
systemctl restart mysqld
mysql -uroot -proot
show databases;
select * from itpuxdb.itpux12;
-
51做主从同步
图解：gtid的位置
mysql -uroot -proot
reset master;
reset slave;
set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
set @@SESSION.SQL_LOG_BIN= 0;
set @@GLOBAL.GTID_PURGED='e5cae73c-9e8a-11eb-aa29-000c294d6ea1:1-131';
set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
stop slave;
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
select count(*) from itpuxdb.itpux12;
-
配置从库的从库
图解：想做级联，前提是中间这个库的日志和主库一致才行，read和exec一样 Read_Master_Log_Pos: 1019023，且日志文件一样 Master_Log_File: itpuxdb-binlog.000002
从库192.168.0. 51：
mysql -uroot -proot -e "stop slave IO_THREAD;"
备份：
mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup-image=/mysql/backup/backup-db/fullbackup51`date +%Y%m%d`.mbi --with-timestamp backup-to-image --backup-dir=/mysql/backup/backup-tmp
mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup5120210418.mbi validate
从从库 52：
scp 192.168.0.51:/mysql/backup/backup-db/fullbackup5120210418.mbi /mysql/backup/backup-db/
systemctl stop mysqld
cd /mysql/data/3306
rm -rf data_bak
mv data data_bak
mkdir data
mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/backup-db/fullbackup.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log
#验证 my.cnf 改掉 innodb_checksum_algorithm=crc32
chown -R mysql:mysql /mysql/data/3306/data
systemctl restart mysqld
mysql -uroot -proot
show databases;
select count(*) from itpuxdb.itpux12;
主从同步
mysql -uroot -proot
reset master;
reset slave;
set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
set @@SESSION.SQL_LOG_BIN= 0;
set @@GLOBAL.GTID_PURGED='e5cae73c-9e8a-11eb-aa29-000c294d6ea1:1-3189';
set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
stop slave;
change master to
master_host='192.168.0.51',
master_port=3306,
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
从库 51：
mysql -uroot -proot -e "start slave IO_THREAD;"
从库 52：
mysql -uroot -proot
start slave;
show slave status\G #确认下 Master_Host:
select count(*) from itpuxdb.itpux12;
-
把主库 50 关机，对外的业务指向 51，这个时候，还是主从。
1.关闭主库，
systemctl stop mysqld
52 创建 3 个数据库和表的数据：
create database itpuxdb3;
use itpuxdb3;
create table itpuxdb3.itpuxbak31 (id int,name varchar(40));
create table itpuxdb3.itpuxbak32 (id int,name varchar(40));
insert into itpuxdb3.itpuxbak31 values(1,'itpux311'),(2,'itpux312'),(3,'itpux313'),(4,'itpux314'),(5,'itpux315');
insert into itpuxdb3.itpuxbak32 values(1,'itpux321'),(2,'itpux322'),(3,'itpux323'),(4,'itpux324'),(5,'itpux325');
commit;
select * from itpuxdb3.itpuxbak31;
select * from itpuxdb3.itpuxbak32;
备库 52 检查：
select * from itpuxdb3.itpuxbak31;
select * from itpuxdb3.itpuxbak32;