Shulou(Shulou.com)06/01 Report--

Data planning:

Haproxy cluster

Haproxy01 node127 192.168.31.127haproxy02 node128 192.168.31.128

Mycat cluster

Mycat01 node119 192.168.31.119mycat02 node118 192.168.31.118

MySQL master-slave replication cluster

Mysqlm1 node115 192.168.31.115mysqlm2 node116 192.168.31.116mysqls1 node117 192.168.31.117

MySQL galera Cluster

Pxc1 node123 192.168.31.123pxc2 node124 192.168.31.125pxc3 node126 192.168.31.126 deploys mysql mutual master-slave, multi-source replication

Edit node115 profile

Node115# cat / etc/my.cnf datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=/tmp/node115 log-bin-index=/tmp/node115 server-id=115 innodb_file_per_table=1

Edit node116 profile

Node116# cat / etc/my.cnflog-bin=/tmp/node116log-bin-index=/tmp/node116server-id=116innodb_file_per_table=1

Edit node117 profile

Configuration on node117

Master_info_repository=TABLErelay_log_info_repository=TABLEserver-id=117slave_skip_errors = 1062

Master_info_repository

After enabling the MTS feature, be sure to set the parameter master_info_repostitory to TABL, so that the performance can be improved by 50% to 80%. This is because when parallel replication is enabled, the update of the meta-master.info file will be greatly increased, and the competition for resources will also increase. In previous versions of InnoSQL, parameters were added to control how often the master.info file was refreshed, even without refreshing the file. Because it is not necessary to refresh this file, that is, the recovery based on the master-info.log file itself is unreliable. In MySQL 5. 7, it is recommended that master_info_repository be set to TABLE to reduce this part of the overhead.

Relay_log_info_repository is the same.

Perform authorization operations on node115 and node116

Mysql > grant replication slave on *. * to 'rep'@'192.168.31.%' identified by' Mirror-12345'

To obtain master file and Pos before configuring synchronization, configure it according to the actual situation.

Mysql > show master status\ gateway * 1. Row * * File: node115.000002 Position: 35291277

Configuration on node116:

Mysql > change master to-> master_host='192.168.31.115',-> master_user='rep',-> master_password='Mirror-12345',-> master_port=3306,-> master_log_file='node115.000002',-> master_log_pos=154

Configuration on node115:

Mysql > change master to-> master_host='192.168.31.116',-> master_user='rep',-> master_password='Mirror-12345',-> master_port=3306,-> master_log_file='node116.000002',-> master_log_pos=154

Configuration on node117

Mysql > set global read_only=1; # read-only mode change master tomaster_host='192.168.31.115',master_user='rep',master_password='Mirror-12345',master_port=3306,master_log_file='node115.000002',master_log_pos=154 for channel 'node115' # distinguishing source change master tomaster_host='192.168.31.116',master_user='rep',master_password='Mirror-12345',master_port=3306,master_log_file='node116.000001',master_log_pos=447 for channel 'node116' by channel

Make sure replication is running properly

Mysql > show slave status\ G [for channel chanelname] Slave_IO_Running: Yes Slave_SQL_Running: Yes

Use stored procedures on node115 or node116 to determine synchronization

# create a table

CREATE TABLE `t3` (`id` INT (11) NOT NULL AUTO_INCREMENT, `user_ id` VARCHAR (20) NOT NULL, `vote_ id` INT (11) NOT NULL, `group_ id` INT (11) NOT NULL, `create_ time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `index_user_ id` (`user_ id`) USING HASH) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

# create a stored procedure

DELIMITER / / CREATE PROCEDURE `t5` (IN n int) BEGINDECLARE i INT DEFAULT 1 position where (i select count (*) from T3; deploy galera cluster [root@node123 ~] # yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm# yum install Percona-XtraDB-Cluster-57# systemctl start mysql# mysql-uroot-pmysql > grant all on *. * to sstuser@'192.168.31.%' identified by 'Mirror-12345' # authorized sst user # systemctl stop mysql

Modify wsrep configuration file

[root@node123 ~] # cat / etc/percona-xtradb-cluster.conf.d/wsrep.cnf | grep-v'#'[mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.31.123192.168.31.125192.168.31.126binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrepairs logically located automatically incinerated lockdown modeboxes 2wsrepairs nodeboxes addressmakers 192.168.31.123wsrepairs clusterbirds clusterboxes nameplate names pxcafe clusterwsrepairs noname names node123pxcregions restricted modecodes ENFORCINGwsrepi Sst_method=xtrabackup-v2wsrep_sst_auth= "sstuser:Mirror-12345"

Modify the configuration files of the other two nodes, which are exactly the same as the first one, except for the following two parameters

Wsrep_node_name=node125wsrep_node_address=192.168.31.125wsrep_node_name=node126wsrep_node_address=192.168.31.126

Start the first node

[root@node123 ~] # systemctl start mysql@bootstrap.servicemysql > show status like 'wsrep%'; # Please note the following parameters | wsrep_cluster_size | 1

Start the second node

[root@node125 ~] # systemctl start mysql@bootstrap.servicemysql > show status like 'wsrep%'; # Please note the following parameters | wsrep_cluster_size | 2

Start the third node

[root@node126 ~] # systemctl start mysql@bootstrap.servicemysql > show status like 'wsrep%'; # Please note the following parameters | wsrep_cluster_size | 3

Use the previously provided stored procedures to verify rep functionality.

It is important to note that this is the test environment, including the previous mysql master and slave, as well as the current galera cluster, so there is no full backup of this step, in the production environment, you need to pay attention.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.