Mysql基于GTID的从库创建

1、创建用户及属主
 userdel lxsjfx.cnmysql
 groupadd -g 501 mysql
 useradd -u 501  -s /bin/bash  -g mysql -m   mysql


2、设置环境变量
export MYSQL_PS1="\h [\d] mysql> "
export MYSQL_HOME=/u01/mysql/base
export PATH=$MYSQL_HOME/bin:$PATH
export LD_LIBRARY_PATH=$MYSQL_HOME/lib

3、创建目录
export PORT=3306
mkdir -p /u01/mysql/$PORT
cd /u01/mysql/$PORT
mkdir data log tmp


4、创建my.cnf (COPY)
vi my.cnf


5、创建实例并启动
export PORT=3306
cd $MYSQL_HOME
/u01/mysql/base/scripts/mysql_install_db --defaults-file=/u01/mysql/$PORT/my.cnf --basedir=/u01/mysql/base --datadir=/u01/mysql/$PORT/data 


cd $MYSQL_HOME
./bin/mysqld_safe --defaults-file=/u01/mysql/$PORT/my.cnf &


mysql -uroot -p --socket=/u01/mysql/$PORT/data/mysql.sock
select user,host,password from mysql.user;
delete from mysql.user where user='';

6、在主库Master创建远程登录用户
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*5B62AE1EEE93C3BFF18471065511D560D5726B86' WITH GRANT OPTION;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'%' IDENTIFIED BY 'rep123456';
flush privileges;


7、在从库 reset master
mysql -uroot -p --socket=/u01/mysql/$PORT/data/mysql.sock
reset master;
stop slave;


8、同步数据
mysqldump -uroot -pxxx -h10.77.1.1 -P3306 --default-character-set=UTF8 --single-transaction --all-databases --triggers --routines --events --master-data=1 --set-gtid-purged=ON | mysql -uroot --socket=/u01/mysql/$PORT/data/mysql.sock -p --default-character-set=UTF8


9、change master并启动Slave
CHANGE MASTER TO
  MASTER_HOST='10.77.1.1',
  MASTER_PORT=3306,
  MASTER_USER='rep',
  MASTER_PASSWORD='rep123456',
  MASTER_AUTO_POSITION=1;
  
10、验证
在主库创建新表并插入数据，确认同步到从库