MySQL cluster-master-slave replication based on Gtid

MySQL cluster uses mha+mycat+mysql master-slave replication +mycat-web monitoring composition due to lengthy release of steps

Here because my computer memory limit does not do mycat high availability can use haporxy+keppalive to achieve the high availability of mycat so there is no single point of failure in the cluster

Brief introduction

Gtid-based replication is MySQL After 5.6 the new replication method.

gtid (global transaction identifier) is the universal transaction ID, which guarantees that each transaction committed on the main library has a unique ID in the cluster.

In the original log-based replication, the library needs to tell the main library which offset to increment from, and if the specified error causes the data to be omitted, resulting in inconsistent data.

1.mysql configuration based on Gtid master-slave replication

Installation script id= ' ifconfig eth0 | awk -f  ' [ :]+ '   ' nr==2 {print $4} '  |  cut -d . -f 4 ' useradd -s /sbin/nologin mysql -mcd / application/tools/tar xf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz ln -s / application/tools/mysql-5.7.21-linux-glibc2.12-x86_64/ /application/mysql && echo  Successcd /applicationmkdir /application/mysql/{log,data,tmp} -pchown -r mysql:mysql  mysqlcd /application/mysqlchown -r mysql:mysql ./* cat << eof  >/etc/my.cnf[client]port = 3306socket = /tmp/mysql.sock[mysqld]server_id= $ Idport = 3306user = mysqlcharacter-set-server = utf8mb4default_storage_engine  = innodblog_timestamps = systemsocket = /tmp/mysql.sockbasedir = / application/mysqldatadir = /aPplication/mysql/datapid-file = /application/mysql/data/mysql.pidmax_connections = 1000max _connect_errors = 1000table_open_cache = 1024max_allowed_packet = 128mopen_files _limit = 65535#####====================================[innodb]==============================innodb_ buffer_pool_size = 1024minnodb_file_per_table = 1innodb_write_io_threads =  4innodb_read_io_threads = 4innodb_purge_threads = 2innodb_flush_log_at_trx_commit =  1innodb_log_file_size = 512minnodb_log_files_in_group = 2innodb_log_buffer_size  = 16minnodb_max_dirty_pages_pct = 80innodb_lock_wait_timeout = 30innodb_data_ file_path=ibdata1:1024m:autoextendinnodb_undo_tablespaces=3######====================================[log]==== ==========================log_error = /application/mysql/log/mysql-error.log slow_query_log  = 1long_query_timE = 1 slow_query_log_file = /application/mysql/log/mysql-slow.logsql_mode=no_engine_ substitution,strict_trans_tableslog-bin=mysql-bin################# #slave设置参数 ################# #relay_log_purge = 0######====================================[gtid]============================gtid_mode=onenforce_gtid_ Consistency=onlog-slave-updates=1eofcd /application/tools/mysql-5.7.21-linux-glibc2.12-x86_64/bin./mysqld  --initialize --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data  --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp && echo  successcp /application/tools/mysql-5.7.21-linux-glibc2.12-x86_64/support-files/mysql.server /etc/ Init.d/mysqlchkconfig --add mysqlchkconfig mysql onservice mysql start cat  << eof >>/etc/profile#for mysql path= $PATH:/application/mysql/ Bineofsource /etc/profile & & echo successpassword= ' Grep -i password /application/mysql/log/mysql-error.log  | awk -F  ' [ :]+ '   ' {print $14} '  | head -1 ' mysqladmin - uroot -p$password password 123456

Create a replication user create a MySQL user in Master1 that can log in from the machine

GRANT REPLICATION SLAVE on * * to ' mysql01 ' @ ' 172.16.1.% ' identified by ' mysql01 '; FLUSH privileges;master2change MASTER tomaster_host= ' 172.16.1.103 ', master_user= ' mysql01 ', master_password= ' Mysql01 ', Master_auto_position=1;slavechange MASTER tomaster_host= ' 172.16.1.103 ', master_user= ' mysql01 ', master_ Password= ' mysql01 ', master_auto_position=1;

Turn on replication

Master2start SLAVE; Slavestart SLAVE;

To see if master-slave replication is configured successfully

master2show slave status\g*************************** 1. row ************************                slave_io_state:  Waiting for master to send event                   Master_Host: 172.16.1.103                   master_user:  mysql01                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           read_master_log_poS: 1200               relay_ log_file: mysql-relay-bin.000002                 relay_log_pos: 472        relay_master_ log_file: mysql-bin.000001              Slave_io_running: yes            slave_sql_ Running: yes            slaveshow slave  STATUS\G*************************** 1. row ***************************                slave_io_state: waiting for  master to send event                   Master_Host: 172.16.1.103                   Master_User: mysql01                   Master_Port: 3306                 connect_retry: 60               Master_Log_File:  mysql-bin.000001          read_master_log_pos: 1200                relay_log_file:  slave-relay-bin.000003                 relay_log_pos: 320        relay_master_log_file :  MYSQL-BIN.000001  &NBsp;          slave_io_running: yes             slave_sql_running: yes

MySQL cluster-master-slave replication based on Gtid