14 搭建主从环境

1 空库无数据 传统 & GTID复制

master
	grant replication slave on *.* to repl@"%" identified by "replpass";

slave 
    1 传统 binlog & pos
	change master to master_host="ip", master_user="repl", master_password="xxx", master_port=3306, 
	master_log_file="mysql-bin.000001" ,  master_log_pos=0;
	
	2 GITD方式_
	change master to master_host="ip", master_user="repl", master_password="xxx", master_port=3306, master_auto_position=1;
	
	start slave;
	show slave status \G;

2 已有数据场景 需要数据导入（mysqldump & xtrabackup）

1 mysqldump + sysbench造数据 + 传统方式/GTID 

master 

1.1 压入数据 + 模拟持续写入业务
	 create database sysbench;
	 grant all on *.* to sysbench@"%" identified by "sysbench"
	 
	 sysbench --db-driver=mysql --time=180 --report-interval=1 --mysql-host=ip --mysql-port=3306 --mysql-user=sysbench --mysql-password=xxx
	 --mysql-db=sysbench --tables=8 --table-size=5000  --db-ps-mode=disable oltp_read_wirte prepare --thread =8 
	 
	 #–-db-ps-mode=STRING SQL是否需要预编译，模式有：auto/disable，默认为disable
	 # --table-size=5000 行
	 
	 grant replication slave on *.* to repl@"%" identified by "replpass";
	 
	 sysbench --db-driver=mysql --time=99999 --report-interval=1 --mysql-host=ip --mysql-port=3306 --mysql-user=sysbench --mysql-password=xxx
	 --mysql-db=sysbench --tables=8 --table-size=5000  --db-ps-mode=disable oltp_read_wirte run --thread =1
	 
	 
 1.2 mysqldump 备份
      #--master-data=2 以注释的形式,保存备份开始时间点的binlog的状态信息
     #--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行热备，实际上是实现快照备份
	mysqldump -h ip -usysbench -pxxx -P3306 --single-transactioin --master-data=2 --triggers --routines --events --all-databases  --set-gtid-purged=ON  --max-allowed-packet= 256M > /data/back_up_`date +%F_%H_%M_%S`.sql
	scp /data/backup_2021-xx-xx_xx_xx_xx.sql ip:/data/

slave 
	1.1 导入数据
	    登录进去 set sql_log_bin=0; source   /data/backup_2021-xx-xx_xx_xx_xx.sql
		不登录： mysql -uroot -pxxxx -P3306  < /data/backup_2021-xx-xx_xx_xx_xx.sql
	
	1.2传统方式
		
	 查看位点信息
		head -100 /data/backup_2021-xx-xx_xx_xx_xx.sql  | grep -i "change master"
		 -- chanage master to  master_log_file="mysql-bin.000002", master_log_pos = 123456789;
		 
	 指定位点
			
		change master to master_host="ip", master_user="repl", master_password="xxx", master_port=3306, 
		master_log_file="mysql-bin.000001" ,  master_log_pos=123456789;
	
	
	1.3 GTID方式
	  查看位点信息：
		head -100 /data/backup_2021-xx-xx_xx_xx_xx.sql  | grep -i "gtid"
		--GTID state at beginning of the backup
		set @@GLOBAL.GTID_PURGED='XXXXXXX: 1-11322'
	  
	  指定位点
	    RESET MASTER;
		  #备份文件中有SET @@GLOBAL.GTID_PURGED='XXXXXXX: 1-11322'; 无需手动执行
		Change master to master_host="ip", master_user="repl", master_password="xxx", master_port=3306, master_auto_position=1;	
			
	1.4	 启动slave： start slave；

2 Xtrabackup

yum install percona-xtrabackup

8.0.20版本，需要使用PXB 8.0.12+以上版本;PXB 8.0 只能备份 MySQL 8.0 ，不能备份低版本 ; 低版本MySQL 使用PXB2.4版本

1.全量备份
innobackupex --defaults-file=/etc/my.cnf --host=ip --user=sysbench --password=xxx --port=3306 --no-timestamp --stream=tar ./ | cat - > /data/back_up_`date +%Y%m%d`.tar.gz  

2 传给slave： scp /data/back_up_20211105.tar.gz  ip:/data/

3 slave  #清空数据目录 和binlog 目录
	mkdir /data/newdata
	mv /data/back_up_20211105.tar.gz /data/newdata/
	tar -xf /data/back_up_20211105.tar.gz /data/newdata/
	cd /data/newdata/
	innobackupex --apply-log --user-memory =1G ./
	innobackupex --defaults-file = /etc/my.cnf --copy-back ./
	
或者使用参数--datadir替换掉参数--defaults-file.  同时会默认读配置文件
xtrabackup --host=ip --user=root --password=xxx --port=3306 --datadir=/data/xxx/ --backup --target-dir=/data/backup/full
xtrabackup (--host=10.0.0.51 --user=root --password=123 --port=3306) --prepare --target-dir=/data/backup/full
xtrabackup (--host=10.0.0.51 --user=root --password=123 --port=3306 --datadir=/数据库目录) --copy-back --target-dir=/data/backup/full

补充
cat xtrabackup_binlog_info
--mysql-bin.000001   XXXXXXX: 1-11322   或者 position号 xxxxxxx
cat xtrabackup_checkpoints
--backup_type = full-backuped from lsn =0 to_lsn=223224  last_lsn=2102332  flushed_lsh = 0
cat xtrabackup_info   # 记录全备相关信息 xtrabackup_binlog_info + xtrabackup_checkpoints


4 传统 & GTID 

	4.1 传统模式
		change master to master_host="ip", master_user="repl", master_password="xxx", master_port=3306, 
		master_log_file="mysql-bin.000001" ,  master_log_pos= XXXXXXX;
	4.2 GTID 模式
		  SET @@GLOBAL.GTID_PURGED='XXXXXXX: 1-11322'; 
		  Change master to master_host="ip", master_user="repl", master_password="xxx", master_port=3306, master_auto_position=1;	

5 启动start slave 

3 GTID复制相关配置
	GTID_MODE =ON
	Log_slave_updates = ON 
	enforce-gtid-consistency = true

4 8.0版本创建用户
	create user "repl"@"%" identified with mysql_native_password by "xxxxxxx"