mysql5.7gtid主从同步部署

master: 192.128.232.131

slave: 192.128.232.130

#安装mysql多版本脚本

[root@mysql-131 ~]# cat /usr/local/src/install_mysql.sh
#!/bin/bash
Mysql_Pid=`ps -ef |grep mysqld|grep -v grep|awk '{print $2}'|wc -l`
if [ ${Mysql_Pid} != 0 ];then
  echo -e "\033[31m mysql server exists,please check this mysql server \033[0m"
  echo -n "Enter you  continue or exit to choice:"
  read choice
  if [ $choice == "continue" ];then
     ps -ef |grep mysqld|grep -v grep|awk '{print $2}'|xargs kill -9
     unlink /usr/local/mysql
     rm /usr/local/mysql-* -rf
     rm -rf /data/mysql_data
  elif [ $choice == "exit" ];then
     exit 1
  fi
fi
rpm -q libaio >/dev/null || yum -y install libaio > /dev/null
if id -g mysql >/dev/null 2>&1
then
   echo "mysql group exists"
else
   groupadd mysql >/dev/null 2>&1 && echo "creating mysql group success"
fi

if id -u mysql >/dev/null 2>&1;then
   echo "mysql user exists"
else
   useradd -r -g mysql -s /sbin/nologin mysql >/dev/null 2>&1 && echo "creating mysql user success"
fi

src_binary_dir=/usr/local/src
install_dir=/usr/local
mydatadir=/data/mysql_data

[ -d $mydatadir ] || mkdir -p $mydatadir
echo -n "Enter you version of Mysql:"
read version
cd ${src_binary_dir}
if [ ${version} == "8.0" ];then
   tar -Jxvf $(/usr/bin/ls mysql-${version}.*.tar.xz) -C ${install_dir}
elif [ ${version} == "5.6" ];then
   tar -zxf $(/usr/bin/ls mysql-${version}.*.tar.gz) -C ${install_dir}
elif [ ${version} == "5.7" ];then
   tar -zxf $(/usr/bin/ls mysql-${version}.*.tar.gz) -C ${install_dir}
else
   echo -e "\033[31m please Enter 5.6 or 5.7 or 8.0 string to install \033[0m"
   exit 1
fi

cd ${install_dir}
ln -s $(ls -d mysql-${version}.*) mysql

echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql_path.sh
source /etc/profile.d/mysql_path.sh

cat >/etc/my.cnf <<EOF
[mysql]
prompt=(\\u@\\h) [\\d]\\_

[mysqld]
skip-name-resolve
port = 3306
basedir=/usr/local/mysql
datadir= /data/mysql_data/data
log_error = /data/mysql_data/logs/error.log
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
EOF

cd mysql
case $version in
5.7)
  mkdir -p /data/mysql_data/{data,logbin,logs}
  chown -R  mysql.mysql /usr/local/mysql*
  chown -R  mysql.mysql /data/mysql_data
  ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql_data/data > /dev/null 2>&1
  [ $? -eq 0 ] && echo "Installing mysql server success"
  \cp -rf ./support-files/mysql.server /etc/rc.d/init.d/mysqld
  chmod +x /etc/rc.d/init.d/mysqld
  /etc/init.d/mysqld start
  [ $? -eq 0 ] && echo "Starting mysql server success"
  cat /data/mysql_data/error.log |grep "password"|awk -F':' '{print $NF}' > /tmp/mysql_password.txt
  sed -i 's/^[ \t]*//g' /tmp/mysql_password.txt
;;

5.6)
  yum install autoconf perl perl-devel perl-Data-Dumper -y >/dev/null 2>&1
  chown -R  mysql.mysql /usr/local/mysql*
  ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql_data/data > /dev/null 2>&1
  [ $? -eq 0 ] && echo "Installing mysql server success"
  \cp -rf ./support-files/mysql.server /etc/rc.d/init.d/mysqld
  chmod +x /etc/rc.d/init.d/mysqld
  /etc/init.d/mysqld start
  [ $? -eq 0 ] && echo "Startting mysql server success"
;;

8.0)
  mkdir -p /data/mysql_data/{data,logbin,logs}
  chown -R  mysql.mysql /usr/local/mysql*
  chown -R  mysql.mysql /data/mysql_data
  ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql_data/data > /dev/null 2>&1
  [ $? -eq 0 ] && echo "Installing mysql server success"
  \cp -rf ./support-files/mysql.server /etc/rc.d/init.d/mysqld
  chmod +x /etc/rc.d/init.d/mysqld
  /etc/init.d/mysqld start
  [ $? -eq 0 ] && echo "Startting mysql server success"
  cat /data/mysql_data/logs/error.log |grep "root@localhost"|awk -F':' '{print $NF}' > /tmp/mysql_password.txt
  sed -i 's/^[ \t]*//g' /tmp/mysql_password.txt

;;

esac

二：master节点配置文件

[root@mysql-131 ~]# cat /etc/my.cnf
[client]
user=root
password=1111aaA_          #几个实例的密码一定要设置这个。否则无法停止。

[mysql]
prompt=(\u@\h) [\d]\_
no-auto-rehash

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/mysqld_multi.log

[mysqld56]
skip-name-resolve
port = 3306
server-id = 131
basedir = /usr/local/mysql
datadir = /data/mysql_data/data
socket = /tmp/mysql.sock
log_error = /data/mysql_data/logs/error.log
event_scheduler = 1
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
binlog_format = row
transaction-isolation = READ-COMMITTED
log_bin=/data/mysql_data/logbin/mysql-binlog
binlog_rows_query_log_events = 1

#skip-grant-tables

#slow log
slow_query_log = 1
slow_query_log_file=/data/mysql_data/logs/mysql-slow.log
long_query_time=2
log_queries_not_using_indexes = 1

#session memory
sort_buffer_size = 64M
tmp_table_size = 64M

#replication
slave_parallel_workers = 16
slave_parallel_type = LOGICAL_CLOCK
read_only=1

#rpl_semi
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=100000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC

#innodb
innodb_log_file_size = 4G
innodb_log_buffer_size = 16M
innodb_log_files_in_group=2
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 16
innodb_online_alter_log_max_size = 512M
innodb_write_io_threads = 16
innodb_page_size = 16384
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
innodb_page_cleaners = 4
innodb_fast_shutdown = 1
innodb_flush_neighbors = 0
innodb_purge_threads = 8
innodb_lock_wait_timeout = 300
innodb_autoinc_lock_mode = 2
innodb_status_output_locks = 1
innodb_print_all_deadlocks = 1

#relay
relay_log_recovery = 1
relay_log_info_repository = table
master_info_repository = table
log_slave_updates

#gtid
gtid-mode = ON
enforce-gtid-consistency = ON

三。slave节点配置

[root@mysql-130 data]# cat /etc/my.cnf
[client]
user=root
password=1111aaA_          #几个实例的密码一定要设置这个。否则无法停止。

[mysql]
prompt=(\u@\h) [\d]\_
no-auto-rehash

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/mysqld_multi.log

[mysqld56]
skip-name-resolve
port = 3306
server-id = 130
basedir = /usr/local/mysql
datadir = /data/mysql_data/data
socket = /tmp/mysql.sock
log_error = /data/mysql_data/logs/error.log
event_scheduler = 1
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
binlog_format = row
transaction-isolation = READ-COMMITTED
log_bin=/data/mysql_data/logbin/mysql-binlog
binlog_rows_query_log_events = 1

#skip-grant-tables

#slow log
slow_query_log = 1
slow_query_log_file=/data/mysql_data/logs/mysql-slow.log
long_query_time=2
log_queries_not_using_indexes = 1

#session memory
sort_buffer_size = 64M
tmp_table_size = 64M

#replication
slave_parallel_workers = 16
slave_parallel_type = LOGICAL_CLOCK
read_only=1

super_read_only = 1

#rpl_semi
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=100000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC

#innodb
innodb_log_file_size = 4G
innodb_log_buffer_size = 16M
innodb_log_files_in_group=2
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 16
innodb_online_alter_log_max_size = 512M
innodb_write_io_threads = 16
innodb_page_size = 16384
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
innodb_page_cleaners = 4
innodb_fast_shutdown = 1
innodb_flush_neighbors = 0
innodb_purge_threads = 8
innodb_lock_wait_timeout = 300
innodb_autoinc_lock_mode = 2
innodb_status_output_locks = 1
innodb_print_all_deadlocks = 1

#relay
relay_log_recovery = 1
relay_log_info_repository = table
master_info_repository = table
log_slave_updates

#gtid
gtid-mode = ON
enforce-gtid-consistency = ON

四：gtid主从同步配置

1：master节点

    [root@mysql-131 ~]# mysql -uroot -p'1111aaA_' -e "grant replication slave on *.* to 'repl'@'192.128.232.%' identified by 'repl123QWE'; flush privileges;"

[root@mysql-131 ~]# mysqldump -uroot -p'1111aaA_' -A -R -E --triggers --master-data=2  --single-transaction --max_allowed_packet=128M --set-gtid-purged=OFF > /tmp/full.sql
[root@mysql-131 ~]# scp /tmp/full.sql 192.128.232.130:/tmp/                                                                         

2：slave节点

[root@mysql-130 data]# mysql

(root@localhost) [mysql] source /tmp/full.sql;
 

(root@localhost) [(none)] CHANGE MASTER TO MASTER_HOST='192.128.232.131',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl123QWE',MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

(root@localhost) [(none)] start slave;
Query OK, 0 rows affected (0.67 sec)

(root@localhost) [(none)] show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.128.232.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000004
          Read_Master_Log_Pos: 885
               Relay_Log_File: mysql-130-relay-bin.000006
                Relay_Log_Pos: 1104
        Relay_Master_Log_File: mysql-binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3，master节点，创建一个测试haha库，看slave节点是否可以同步

(root@localhost) [(none)] create database haha;
Query OK, 1 row affected (0.00 sec)
 

4，slave节点，查看haha库

[root@mysql-130 tmp]# mysql -e "show databases"|grep "haha"
haha