Galera篇-galera3 mysql5.7 集群安装部署

最新推荐文章于 2023-09-01 19:23:37 发布

鬼刺

最新推荐文章于 2023-09-01 19:23:37 发布

阅读量1.1k

点赞数

分类专栏： DATABASE 文章标签： mysql galera galera mysql galera集群 mysql集群 galera3 mysql集群

本文链接：https://blog.csdn.net/qq_35550345/article/details/109252447

版权

DATABASE 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

1 带着问题去操作

集群健康状况如何判断
master故障后，slave能否自动切换
故障切换效率：切换一次平均耗费多久
slave切换成功之后，能否正常读写
master恢复后，能否自动加入集群变成slave
数据同步效率：同步一定量的数据需要耗费多久

2 操作手册

环境：

node1 172.16.212.31,
node2 172.16.212.32,
node3 172.16.212.33

2.1 准备工作

方案一：在所有节点操作

# 下载需要的rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-client-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-common-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-devel-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-libs-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-libs-compat-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-server-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64/mysql-wsrep-test-5.7-5.7.27-25.19.el7.x86_64.rpm
wget https://releases.galeracluster.com/galera-3.28/centos/7/x86_64/galera-3-25.3.28-1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm
 
# 安装依赖
yum -y install epel-release
yum -y install perl perl-devel perl-Time-HiRes perl-DBD-MySQL.x86_64 perl-Digest-MD5 libaio libaio-devel
 
# 安装galera组件
rpm -ivh ./mysql*
rpm -ivh galera-3-25.3.28-1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm

方案二：在所有节点操作

# 1.准备工作
cat > /etc/yum.repos.d/galera.repo <<-END
[galera]
name = Galera
baseurl = https://releases.galeracluster.com/galera-3.28/centos/7/x86_64
gpgkey = https://releases.galeracluster.com/galera-3.28/GPG-KEY-galeracluster.com
gpgcheck = 1
  
[mysql-wsrep]
name = MySQL-wsrep
baseurl = https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64
gpgkey = https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/GPG-KEY-galeracluster.com
gpgcheck = 1
END
 
# 2.安装galera-3与mysql-wsrep-5.7
yum install -y galera-3 mysql-wsrep-5.7
 
# 3.安装xtrabackup
#wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
#rpm -ivh libev-4.04-2.el6.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm

2.2 配置

编辑配置文件：vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1
binlog_format=row
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_name='galera'
wsrep_cluster_address='gcomm://172.16.212.21,172.16.212.22,172.16.212.23'
wsrep_node_name='node1'
wsrep_node_address='172.16.212.21'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=root:Password

在某一个节点启动mysql：

/usr/bin/mysqld_bootstrap –wsrep-new-cluster
grep -i 'temporary password' /var/log/mysqld.log
mysqladmin -uroot -p password 'Password'
mysql -uroot -p
create user sync identified by 'Password';
grant all on *.* to sync with grant option;

在另外两个节点上，root用户启动mysql：

systemctl start mysqld

3 集群功能测试

# 集群状态
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)
 
# node1上插入数据，查看同步情况
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
 
mysql> use test;
Database changed
mysql> create table t1(a int);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)
 
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

在这里插入图片描述

# node2上插入数据，查看同步情况
mysql> create table t2(a int);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.02 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)
 
mysql> select * from t2;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

在这里插入图片描述

# node3插入数据，查看同步情况
mysql> create table t3(a int);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t3 values(3);
Query OK, 1 row affected (0.03 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)
 
mysql> select * from t3;
+------+
| a    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

在这里插入图片描述

4 故障模拟

1）节点故障，是否能被自动踢出集群

# node3
systemctl stop mysqld

日志：
在这里插入图片描述

# node2上查看集群状态
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
1 row in set (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)
 
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
mysql> insert into t3 values(4);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t3;
+------+
| a    |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)
 
# node1上查看
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)
 
mysql> select * from t3;
+------+
| a    |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)
mysql> insert into t2 values(5);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t2;
+------+
| a    |
+------+
|    2 |
|    5 |
+------+
2 rows in set (0.00 sec)

集群读写正常，故障节点自动被踢出！

2）节点恢复，是否能自动加入集群并正常同步数据

# node3:
systemctl start mysqld

在这里插入图片描述

[root@node3 galera]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27 MySQL Community Server - (GPL), wsrep_25.19
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)
 
mysql> select * from t2;
+------+
| a    |
+------+
|    2 |
|    5 |
+------+
2 rows in set (0.00 sec)
 
mysql> select * from t3;
+------+
| a    |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)
 
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> insert into t1 values(6);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    6 |
+------+
2 rows in set (0.00 sec)

节点自动加入集群，数据同步成功，且读写正常！

3）故障切换效率
在这里插入图片描述

5 性能测试

主要针对故障恢复后数据同步性能

# 安装压测工具
yum -y install sysbench
 
# 创建sbtest1-10表
CREATE TABLE `sbtest1` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `k` int(10) unsigned NOT NULL default '0',
    `c` char(120) NOT NULL default '',
    `pad` char(60) NOT NULL default '',
    PRIMARY KEY  (`id`),
    KEY `k` (`k`));
 
# 生成数据
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-user=root --mysql-password=P@sswo2d --mysql-db=test --oltp-tables-count=10 --oltp-table-size=100000 --report-interval=10 --max-time=60 run
 
# sbtest1-10都产生了100000条数据
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest         |
| sbtest1        |
| sbtest10       |
| sbtest2        |
| sbtest3        |
| sbtest4        |
| sbtest5        |
| sbtest6        |
| sbtest7        |
| sbtest8        |
| sbtest9        |
| t1             |
| t2             |
| t3             |
+----------------+
14 rows in set (0.00 sec)

在这里插入图片描述
启动node3的数据库，看能否在自动加入集群后同步所有数据！

#node3启动之后的日志：
2020-08-31T13:28:11.687472Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-31T13:28:11.691037Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27) starting as process 3007 ...
2020-08-31T13:28:11.696225Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-08-31T13:28:11.696242Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-31T13:28:11.696245Z 0 [Note] InnoDB: Uses event mutexes
2020-08-31T13:28:11.696247Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-08-31T13:28:11.696249Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-08-31T13:28:11.696250Z 0 [Note] InnoDB: Using Linux native AIO
2020-08-31T13:28:11.696621Z 0 [Note] InnoDB: Number of pools: 1
2020-08-31T13:28:11.696883Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-08-31T13:28:11.701209Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-08-31T13:28:11.716747Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-31T13:28:11.721520Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-08-31T13:28:11.738961Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-31T13:28:11.752012Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-08-31T13:28:11.752057Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-08-31T13:28:11.771702Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-08-31T13:28:11.772256Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-08-31T13:28:11.772298Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-08-31T13:28:11.775083Z 0 [Note] InnoDB: Waiting for purge to start
2020-08-31T13:28:11.825275Z 0 [Note] InnoDB: 5.7.27 started; log sequence number 1333346
2020-08-31T13:28:11.828088Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-08-31T13:28:11.839192Z 0 [Note] WSREP: Recovered position: 2643e295-eb3d-11ea-8299-0e10afe1240e:13
2020-08-31T13:28:11.839240Z 0 [Note] Binlog end
2020-08-31T13:28:11.839298Z 0 [Note] Shutting down plugin 'validate_password'
2020-08-31T13:28:11.839311Z 0 [Note] Shutting down plugin 'ngram'
2020-08-31T13:28:11.839313Z 0 [Note] Shutting down plugin 'partition'
2020-08-31T13:28:11.839314Z 0 [Note] Shutting down plugin 'ARCHIVE'
2020-08-31T13:28:11.839316Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2020-08-31T13:28:11.839318Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-08-31T13:28:11.839353Z 0 [Note] Shutting down plugin 'MyISAM'
2020-08-31T13:28:11.839360Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2020-08-31T13:28:11.839362Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-08-31T13:28:11.839364Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-08-31T13:28:11.839365Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-08-31T13:28:11.839367Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-08-31T13:28:11.839378Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-08-31T13:28:11.839382Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-08-31T13:28:11.839384Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-08-31T13:28:11.839385Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-08-31T13:28:11.839387Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-08-31T13:28:11.839389Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-08-31T13:28:11.839390Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-08-31T13:28:11.839399Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-08-31T13:28:11.839400Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-08-31T13:28:11.839402Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-08-31T13:28:11.839403Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-08-31T13:28:11.839405Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2020-08-31T13:28:11.839406Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2020-08-31T13:28:11.839408Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-08-31T13:28:11.839409Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-08-31T13:28:11.839411Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-08-31T13:28:11.839412Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-08-31T13:28:11.839414Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-08-31T13:28:11.839415Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-08-31T13:28:11.839417Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-08-31T13:28:11.839418Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-08-31T13:28:11.839420Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2020-08-31T13:28:11.839421Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-08-31T13:28:11.839422Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-08-31T13:28:11.839424Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-08-31T13:28:11.839425Z 0 [Note] Shutting down plugin 'InnoDB'
2020-08-31T13:28:11.839830Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-08-31T13:28:11.840188Z 0 [Note] InnoDB: Starting shutdown...
2020-08-31T13:28:13.446896Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1333365
2020-08-31T13:28:13.448245Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-08-31T13:28:13.448262Z 0 [Note] Shutting down plugin 'MEMORY'
2020-08-31T13:28:13.448266Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-08-31T13:28:13.448269Z 0 [Note] Shutting down plugin 'CSV'
2020-08-31T13:28:13.448273Z 0 [Note] Shutting down plugin 'sha256_password'
2020-08-31T13:28:13.448275Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-08-31T13:28:13.448277Z 0 [Note] Shutting down plugin 'wsrep'
2020-08-31T13:28:13.448407Z 0 [Note] Shutting down plugin 'binlog'
2020-08-31T13:28:13.448672Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
 
2020-08-31T13:28:13.653839Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-31T13:28:13.654784Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27) starting as process 3047 ...
2020-08-31T13:28:13.656477Z 0 [Note] WSREP: Read nil XID from storage engines, skipping position init
2020-08-31T13:28:13.656492Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera-3/libgalera_smm.so'
2020-08-31T13:28:13.661448Z 0 [Note] WSREP: wsrep_load(): Galera 3.28(rb3295e6) by Codership Oy <info@codership.com> loaded successfully.
2020-08-31T13:28:13.661496Z 0 [Note] WSREP: CRC-32C: using hardware acceleration.
2020-08-31T13:28:13.662240Z 0 [Note] WSREP: Found saved state: 2643e295-eb3d-11ea-8299-0e10afe1240e:13, safe_to_bootstrap: 0
2020-08-31T13:28:13.663068Z 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 172.16.212.23; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S
2020-08-31T13:28:13.671064Z 0 [Note] WSREP: GCache history reset: 2643e295-eb3d-11ea-8299-0e10afe1240e:0 -> 2643e295-eb3d-11ea-8299-0e10afe1240e:13
2020-08-31T13:28:13.671297Z 0 [Note] WSREP: Assign initial position for certification: 13, protocol version: -1
2020-08-31T13:28:13.671336Z 0 [Note] WSREP: wsrep_sst_grab()
2020-08-31T13:28:13.671341Z 0 [Note] WSREP: Start replication
2020-08-31T13:28:13.671350Z 0 [Note] WSREP: Setting initial position to 2643e295-eb3d-11ea-8299-0e10afe1240e:13
2020-08-31T13:28:13.671427Z 0 [Note] WSREP: protonet asio version 0
2020-08-31T13:28:13.671494Z 0 [Note] WSREP: Using CRC-32C for message checksums.
2020-08-31T13:28:13.671514Z 0 [Note] WSREP: backend: asio
2020-08-31T13:28:13.671573Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0
2020-08-31T13:28:13.671710Z 0 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)
2020-08-31T13:28:13.671717Z 0 [Note] WSREP: restore pc from disk failed
2020-08-31T13:28:13.671884Z 0 [Note] WSREP: GMCast version 0
2020-08-31T13:28:13.672207Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2020-08-31T13:28:13.672215Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2020-08-31T13:28:13.672467Z 0 [Note] WSREP: EVS version 0
2020-08-31T13:28:13.672602Z 0 [Note] WSREP: gcomm: connecting to group 'galera', peer '172.16.212.21:,172.16.212.22:,172.16.212.23:'
2020-08-31T13:28:13.673514Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') Found matching local endpoint for a connection, blacklisting address tcp://172.16.212.23:4567
2020-08-31T13:28:13.674604Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') connection established to 2643917a tcp://172.16.212.21:4567
2020-08-31T13:28:13.674638Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2020-08-31T13:28:13.675130Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') connection established to 7791e976 tcp://172.16.212.22:4567
2020-08-31T13:28:14.175214Z 0 [Note] WSREP: declaring 2643917a at tcp://172.16.212.21:4567 stable
2020-08-31T13:28:14.175243Z 0 [Note] WSREP: declaring 7791e976 at tcp://172.16.212.22:4567 stable
2020-08-31T13:28:14.175941Z 0 [Note] WSREP: Node 2643917a state prim
2020-08-31T13:28:14.176334Z 0 [Note] WSREP: view(view_id(PRIM,2643917a,109) memb {
    2643917a,0
    7791e976,0
    d224141e,0
} joined {
} left {
} partitioned {
})
2020-08-31T13:28:14.176348Z 0 [Note] WSREP: save pc into disk
2020-08-31T13:28:14.673586Z 0 [Note] WSREP: gcomm: connected
2020-08-31T13:28:14.673633Z 0 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
2020-08-31T13:28:14.673675Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
2020-08-31T13:28:14.673679Z 0 [Note] WSREP: Opened channel 'galera'
2020-08-31T13:28:14.673862Z 0 [Note] WSREP: Waiting for SST to complete.
2020-08-31T13:28:14.674229Z 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
2020-08-31T13:28:14.674246Z 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
2020-08-31T13:28:14.674267Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 1788b1d3-eb4a-11ea-a5a6-7a5d20a4d331
2020-08-31T13:28:14.674273Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 1788b1d3-eb4a-11ea-a5a6-7a5d20a4d331 from 0 (node1)
2020-08-31T13:28:14.674279Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 1788b1d3-eb4a-11ea-a5a6-7a5d20a4d331 from 1 (node2)
2020-08-31T13:28:14.675984Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 1788b1d3-eb4a-11ea-a5a6-7a5d20a4d331 from 2 (node3)
2020-08-31T13:28:14.676006Z 0 [Note] WSREP: Quorum results:
    version    = 6,
    component  = PRIMARY,
    conf_id    = 108,
    members    = 2/3 (joined/total),
    act_id     = 24079,
    last_appl. = -1,
    protocols  = 0/9/3 (gcs/repl/appl),
    group UUID = 2643e295-eb3d-11ea-8299-0e10afe1240e
2020-08-31T13:28:14.676020Z 0 [Note] WSREP: Flow-control interval: [28, 28]
2020-08-31T13:28:14.676024Z 0 [Note] WSREP: Trying to continue unpaused monitor
2020-08-31T13:28:14.676028Z 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 24079)
2020-08-31T13:28:14.676356Z 2 [Note] WSREP: State transfer required:
    Group state: 2643e295-eb3d-11ea-8299-0e10afe1240e:24079
    Local state: 2643e295-eb3d-11ea-8299-0e10afe1240e:13
2020-08-31T13:28:14.676385Z 2 [Note] WSREP: New cluster view: global state: 2643e295-eb3d-11ea-8299-0e10afe1240e:24079, view# 109: Primary, number of nodes: 3, my index: 2, protocol version 3
2020-08-31T13:28:14.676389Z 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
2020-08-31T13:28:14.676763Z 0 [Note] WSREP: Running: 'wsrep_sst_xtrabackup --role 'joiner' --address '172.16.212.23' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '3047'  '' '
WSREP_SST: [INFO] Streaming with tar (20200831 21:28:14.778)
WSREP_SST: [INFO] Using socat as streamer (20200831 21:28:14.780)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | tar xfi - --recursive-unlink -h; RC=( ${PIPESTATUS[@]} ) (20200831 21:28:14.794)
2020-08-31T13:28:14.998084Z 2 [Note] WSREP: Prepared SST request: xtrabackup|172.16.212.23:4444/xtrabackup_sst
2020-08-31T13:28:14.998110Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-08-31T13:28:14.998127Z 2 [Note] WSREP: REPL Protocols: 9 (4, 2)
2020-08-31T13:28:14.998132Z 2 [Note] WSREP: Assign initial position for certification: 24079, protocol version: 4
2020-08-31T13:28:14.998185Z 0 [Note] WSREP: Service thread queue flushed.
2020-08-31T13:28:14.998504Z 2 [Note] WSREP: IST receiver addr using tcp://172.16.212.23:4568
2020-08-31T13:28:14.998717Z 2 [Note] WSREP: Prepared IST receiver, listening at: tcp://172.16.212.23:4568
2020-08-31T13:28:15.000484Z 0 [Note] WSREP: Member 2.0 (node3) requested state transfer from '*any*'. Selected 0.0 (node1)(SYNCED) as donor.
2020-08-31T13:28:15.000498Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 24079)
2020-08-31T13:28:15.000631Z 2 [Note] WSREP: Requesting state transfer: success, donor: 0
2020-08-31T13:28:15.000648Z 2 [Note] WSREP: GCache history reset: 2643e295-eb3d-11ea-8299-0e10afe1240e:0 -> 2643e295-eb3d-11ea-8299-0e10afe1240e:24079
2020-08-31T13:28:15.136897Z 0 [Note] WSREP: 0.0 (node1): State transfer to 2.0 (node3) complete.
2020-08-31T13:28:15.137180Z 0 [Note] WSREP: Member 0.0 (node1) synced with group.
WSREP_SST: [INFO] xtrabackup_ist received from donor: Running IST (20200831 21:28:15.138)
WSREP_SST: [INFO] Total time on joiner: 0 seconds (20200831 21:28:15.141)
WSREP_SST: [INFO] Removing the sst_in_progress file (20200831 21:28:15.143)
2020-08-31T13:28:15.145145Z 0 [Note] WSREP: SST complete, seqno: 13
2020-08-31T13:28:15.146015Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-08-31T13:28:15.146030Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-31T13:28:15.146033Z 0 [Note] InnoDB: Uses event mutexes
2020-08-31T13:28:15.146035Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-08-31T13:28:15.146039Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-08-31T13:28:15.146041Z 0 [Note] InnoDB: Using Linux native AIO
2020-08-31T13:28:15.146198Z 0 [Note] InnoDB: Number of pools: 1
2020-08-31T13:28:15.146266Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-08-31T13:28:15.147176Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-08-31T13:28:15.151678Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-31T13:28:15.153189Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-08-31T13:28:15.164486Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-31T13:28:15.169688Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-08-31T13:28:15.169741Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-08-31T13:28:15.183753Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-08-31T13:28:15.184314Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-08-31T13:28:15.184324Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-08-31T13:28:15.184916Z 0 [Note] InnoDB: Waiting for purge to start
2020-08-31T13:28:15.235152Z 0 [Note] InnoDB: 5.7.27 started; log sequence number 1333365
2020-08-31T13:28:15.235931Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-08-31T13:28:15.250388Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-08-31T13:28:15.250774Z 0 [Warning] CA certificate ca.pem is self signed.
2020-08-31T13:28:15.251954Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2020-08-31T13:28:15.252113Z 0 [Note] IPv6 is available.
2020-08-31T13:28:15.252191Z 0 [Note]   - '::' resolves to '::';
2020-08-31T13:28:15.252237Z 0 [Note] Server socket created on IP: '::'.
2020-08-31T13:28:15.252733Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-08-31T13:28:15.254710Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200831 21:28:15
2020-08-31T13:28:15.264697Z 0 [Note] Event Scheduler: Loaded 0 events
2020-08-31T13:28:15.264725Z 0 [Note] WSREP: Signalling provider to continue.
2020-08-31T13:28:15.264738Z 0 [Note] WSREP: Initialized wsrep sidno 2
2020-08-31T13:28:15.264754Z 0 [Note] WSREP: SST received: 2643e295-eb3d-11ea-8299-0e10afe1240e:13
2020-08-31T13:28:15.265336Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.27'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - (GPL), wsrep_25.19
2020-08-31T13:28:15.265564Z 2 [Note] WSREP: Receiving IST: 24066 writesets, seqnos 13-24079
2020-08-31T13:28:15.266869Z 0 [Note] WSREP: Receiving IST...  0.0% (    0/24066 events) complete.
2020-08-31T13:28:16.678093Z 0 [Note] WSREP: (d224141e, 'tcp://0.0.0.0:4567') turning message relay requesting off
2020-08-31T13:28:22.316024Z 0 [Note] WSREP: Receiving IST...100.0% (24066/24066 events) complete.
2020-08-31T13:28:22.316628Z 2 [Note] WSREP: IST received: 2643e295-eb3d-11ea-8299-0e10afe1240e:24079
2020-08-31T13:28:22.317739Z 0 [Note] WSREP: 2.0 (node3): State transfer from 0.0 (node1) complete.
2020-08-31T13:28:22.317770Z 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 24079)
2020-08-31T13:28:22.318414Z 0 [Note] WSREP: Member 2.0 (node3) synced with group.
2020-08-31T13:28:22.318435Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 24079)
2020-08-31T13:28:22.318559Z 2 [Note] WSREP: Synchronized with group, ready for connections
2020-08-31T13:28:22.318567Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

100000条数据在11秒内同步完成～

1000000条数据在9秒内同步完成～

10000000条数据在9秒内同步完成～

鬼刺

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
Galera篇-galera3 mysql5.7 集群安装部署

1 带着问题去操作集群健康状况如何判断master故障后，slave能否自动切换故障切换效率：切换一次平均耗费多久slave切换成功之后，能否正常读写master恢复后，能否自动加入集群变成slave数据同步效率：同步一定量的数据需要耗费多久2 操作手册环境：node1 172.16.212.31,node2 172.16.212.32,node3 172.16.212.332.1 准备工作方案一：在所有节点操作# 下载需要的rpmwget https://rele
复制链接

扫一扫