Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MySQL5.7 Galera Cluster installation and high availability testing". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

-create and configure my.cnf files

# cat / etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

! includedir / etc/my.cnf.d/

-create and configure wsrep.cnf files

# cat / etc/my.cnf.d/wsrep.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the

# * * default location during install, and will be replaced if you

# * * upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128m

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir =.

# datadir =.

# port =.

# server_id =.

# socket =.

# the ID of the MySQL server must be unique, and each node of the cluster is different.

Server-id=111

Explicit_defaults_for_timestamp=true

Basedir=/usr

# # Storage path of MySQL data File

Datadir=/data/mysql/3306

Socket=/data/mysql/3306/mysql.sock

Pid_file=/data/mysql/mysqld.pid

Port=3306

Log_error=/data/mysql/mysql.err

# # galera cluster name must be uniform

Wsrep_cluster_name='tangyun_cluster'

# # wsrep provider must be configured (where the path of the .so file is, it will be configured)

Wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so

# # ID of wsrep nodes must be unique, and different nodes in the cluster

Wsrep_node_name = db01

# # address of other nodes in the cluster, you can use hostname or IP

Wsrep_cluster_address=gcomm://192.168.56.111192.168.56.112192.168.56.113

# Native node address, you can use hostname or IP

Wsrep_node_address='192.168.56.111'

# specify wsrep startup port number

Wsrep_provider_options = "gmcast.listen_addr=tcp://192.168.56.111:4567"

# A comma-separated node string is used as the source of state transfer, such as wsrep_sst_donor=db01,db02. If db01 is available, use db02.

If db02 is not available, use db03, with the final comma indicating that let the provider choose the best one.

Wsrep_sst_donor='db01,db02,db03'

# # Cluster synchronization

Wsrep_sst_method=rsync

# # user name and password for Cluster synchronization

Wsrep_sst_auth=tangyun:tangyun

Slow_query_log=on

[client]

Default-character-set=utf8

Socket=/data/mysql/3306/mysql.sock

[mysql]

Default-character-set=utf8

Socket=/data/mysql/3306/mysql.sock

[mysqldump]

Max_allowed_packet = 512m

[mysqld_safe]

Malloc-lib=/usr/lib64/libjemalloc.so.1

-Log in to the MySQL database

After the installation is completed, we do not seem to know the default password of mysql and cannot log in to the database. In fact, the default password of the database will be prompted in the installation log. You can log in with the default password and change the password, so that you do not need the following tedious operations.

1. Add a skip-grant-tables line to the configuration file / etc/my.cnf to skip password verification.

2. Restart the main process of mysql database # / etc/init.d/mysqld restart (you can also directly stop the MySQL process and restart MySQL using the skip-grant-tables parameter)

3. Log in to the database and change the password.

Mysql > use mysql

Mysql > update user set authentication_string=password ('mysql') where user='root' and host='localhost'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql > exit

The field that needs to be modified here is authentication_string, which is different from the previous version.

4. At this time, if the password you set is too simple, any command executed in the database will report an error similar to the following:

Mysql > show databases

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Mysql > show database

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

Mysql > update user set authentication_string=password ('mysql') where user='root' and host='localhost'

ERROR 1046 (3D000): No database selected

Mysql > use mysql

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

5. Note: if you only want to set a simple password, you need to modify two global parameters:

Mysql > set global validate_password_policy=0

Mysql > set global validate_password_length=1

Mysql > set global validate_password_policy=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set global validate_password_length=1

Query OK, 0 rows affected (0.00 sec)

Mysql > set password=password ("mysql")

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > use mysql

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

-authorize users and passwords for cluster synchronization. The created account will be automatically synchronized to each node of the cluster after the cluster is successfully installed.

# mysql-uroot-pmysql

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.7.17 MySQL Community Server-(GPL), wsrep_25.10.20170117.92194e2

Copyright (c) 2000, 2016, 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 > grant usage on *. * to tangyun@'%' identified by 'tangyun'

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > grant all privileges on *. * to tangyun@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > exit

Bye

Now that the installation and initialization is basically complete, you can try to create the database and test the synchronization.

1. Check cluster synchronization and initialization attempts

Mysql > show global status like 'wsrep%'

+-+

| | Variable_name | Value |

+-+

| | wsrep_local_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |

| | wsrep_protocol_version | 7 | |

| | wsrep_last_committed | 0 | |

| | wsrep_replicated | 0 | |

| | wsrep_replicated_bytes | 0 | |

| | wsrep_repl_keys | 0 | |

| | wsrep_repl_keys_bytes | 0 | |

| | wsrep_repl_data_bytes | 0 | |

| | wsrep_repl_other_bytes | 0 | |

| | wsrep_received | 10 | |

| | wsrep_received_bytes | 752 | |

| | wsrep_local_commits | 0 | |

| | wsrep_local_cert_failures | 0 | |

| | wsrep_local_replays | 0 | |

| | wsrep_local_send_queue | 0 | |

| | wsrep_local_send_queue_max | 1 | |

| | wsrep_local_send_queue_min | 0 | |

| | wsrep_local_send_queue_avg | 0.000000 | |

| | wsrep_local_recv_queue | 0 | |

| | wsrep_local_recv_queue_max | 1 | |

| | wsrep_local_recv_queue_min | 0 | |

| | wsrep_local_recv_queue_avg | 0.000000 | |

| | wsrep_local_cached_downto | 18446744073709551615 | |

| | wsrep_flow_control_paused_ns | 0 | |

| | wsrep_flow_control_paused | 0.000000 | |

| | wsrep_flow_control_sent | 0 | |

| | wsrep_flow_control_recv | 0 | |

| | wsrep_cert_deps_distance | 0.000000 | |

| | wsrep_apply_oooe | 0.000000 | |

| | wsrep_apply_oool | 0.000000 | |

| | wsrep_apply_window | 0.000000 | |

| | wsrep_commit_oooe | 0.000000 | |

| | wsrep_commit_oool | 0.000000 | |

| | wsrep_commit_window | 0.000000 | |

| | wsrep_local_state | 4 |

| | wsrep_local_state_comment | Synced |

| | wsrep_cert_index_size | 0 | |

| | wsrep_causal_reads | 0 | |

| | wsrep_cert_interval | 0.000000 | |

| | wsrep_incoming_addresses | 192.168.56.111 virtual 3306192.168.56.112virtual 3306192.168.56.113purl 3306 |

| | wsrep_desync_count | 0 | |

| | wsrep_evs_delayed |

| | wsrep_evs_evict_list |

| | wsrep_evs_repl_latency | 0 / 0 / 0 | 0 / 0 / 0 |

| | wsrep_evs_state | OPERATIONAL |

| | wsrep_gcomm_uuid | df49c18f-701a-11e7-aaaa-9659aa7ef9f8 |

| | wsrep_cluster_conf_id | 3 | |

| | wsrep_cluster_size | 3 | |

| | wsrep_cluster_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |

| | wsrep_cluster_status | Primary |

| | wsrep_connected | ON |

| | wsrep_local_bf_aborts | 0 | |

| | wsrep_local_index | 0 | |

| | wsrep_provider_name | Galera |

| | wsrep_provider_vendor | Codership Oy |

| | wsrep_provider_version | 3.20 (r7e383f7) |

| | wsrep_ready | ON |

+-+

57 rows in set (0.00 sec)

-create a database and test the high availability of the cluster

Db01 creates the database:

[root@galera01 subsys] # mysql-uroot-pmysql

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 7

Server version: 5.7.17-log MySQL Community Server-(GPL), wsrep_25.10.20170117.92194e2

Copyright (c) 2000, 2016, 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 > create database tangyun default character set utf8 collate utf8_general_ci

Query OK, 1 row affected (0.04 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | tangyun |

+-+

5 rows in set (0.00 sec)

Db02/db03:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | tangyun |

+-+

5 rows in set (0.00 sec)

-close db02, create tables and insert data on db03

[root@galera02 ~] # / etc/init.d/mysqld stop

Stopping mysqld: [OK]

[root@galera03] # mysql-uroot-pmysql

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 8

Server version: 5.7.17 MySQL Community Server-(GPL), wsrep_25.10.20170117.92194e2

Copyright (c) 2000, 2016, 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 tangyun

Database changed

Mysql > create table ty (tid int,tname varchar (20))

Query OK, 0 rows affected (0.08 sec)

Mysql > insert into ty (tid,tname) values (1)

Query OK, 1 row affected (0.04 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql >

-check whether db01 is synchronized, start db02, and check whether it is synchronized.

Db01:

Mysql > select * from tangyun.ty

+-+ +

| | tid | tname |

+-+ +

| | 1 | tangyun |

+-+ +

1 row in set (0.00 sec)

Db02:

[root@galera02 ~] # / etc/init.d/mysqld start

Starting mysqld: [OK]

Mysql > select * from tangyun.ty

+-+ +

| | tid | tname |

+-+ +

| | 1 | tangyun |

+-+ +

1 row in set (0.01 sec)

-synchronization is normal.

-handling problems encountered in installation and initialization

1. Failed to open gcomm backend connection: 110: failed to reach primary view: 110( Connection timed out)

160613 9:43:01 [Note] WSREP: view ((empty))

160613 9:43:01 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110( Connection timed out)

At gcomm/src/pc.cpp:connect (): 162,

160613 9:43:01 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open (): 208: Failed to open backend connection:-110( Connection timed out)

160613 9:43:01 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open (): 1379: Failed to open channel 'galera_cluster'' at' gcomm://192.168.56.111192.168.56.112192.168.56.113':-10 (Connection timed out)

160613 9:43:01 [ERROR] WSREP: gcs connect failed: Connection timed out

160613 9:43:01 [ERROR] WSREP: wsrep::connect (gcomm://192.168.56.111192.168.56.112192.168.56.113) failed: 7

160613 9:43:01 [ERROR] Aborting

160613 9:43:01 [Note] WSREP: Service disconnected.

160613 9:43:02 [Note] WSREP: Some threads may fail to exit.

160613 9:43:02 [Note] / usr/sbin/mysqld: Shutdown complete

Solution:

After eliminating the problem of firewall and network failure

Delete the two cache files in the installation directory of the MySQL files of the node and all nodes in front of the node and the mysqld files in the / var/lock/subsys directory, and then restart:

# cd / var/lock/subsys

# rm-rf mysql*

# cd / data/mysql/3306

Rm-rf galera.cache grastate.dat

# # launch the first node

# / etc/init.d/mysqld start-wsrep-new-cluster

Starting mysqld: [OK]

Other nodes start:

# / etc/init.d/mysqld start

Starting mysqld: [OK]

2. Mysql cannot start normally after abnormal restart of MySQL cluster host-edit the grastate.dat file manually and set safe_to_bootstrap to 1

1. Error trying to restart mysql database

[root@galera01] # / etc/init.d/mysqld start-- wsrep-new-cluster

MySQL Daemon failed to start.

Starting mysqld: [FAILED]

2. View the startup error log

2017-07-24T02:45:41.972508Z 0 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the

Updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1.

2017-07-24T02:45:41.972511Z 0 [ERROR] WSREP: wsrep::connect (gcomm://192.168.56.111192.168.56.112192.168.56.113) failed: 7

2017-07-24T02:45:41.972513Z 0 [ERROR] Aborting

2017-07-24T02:45:41.972516Z 0 [Note] Giving 0 client threads a chance to die gracefully

2017-07-24T02:45:41.972519Z 0 [Note] WSREP: Service disconnected.

2017-07-24T02:45:42.972895Z 0 [Note] WSREP: Some threads may fail to exit.

2017-07-24T02:45:42.972937Z 0 [Note] Binlog end

2017-07-24T02:45:42.973014Z 0 [Note] / usr/sbin/mysqld: Shutdown complete

3. Try to start other nodes first and report the same error

An error message from the error log indicates that the current node is not the last node to leave in the cluster, which means that the current node may not contain all updates.

If you force the current node to start, you need to modify the grastate.dat file to set the value of safe_to_bootstrap to 1.

Try to start the other nodes first and report the same error.

4. Modify grastate.dat file

This file mainly describes the status information maintained by GALERA, according to the guidelines to modify the value of safe_to_bootstrap to 1.

# cat grastate.dat

# GALERA saved state

Version: 2.1

Uuid: df4a1da6-701a-11e7-87fe-e6c3a440d1ec

Seqno:-1

Safe_to_bootstrap: 1-change from 0 to 1 and start again

5. Mysql cluster started successfully

Mysql > show global status like 'wsrep%'

+-+

| | Variable_name | Value |

+-+

| | wsrep_local_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |

| | wsrep_protocol_version | 7 | |

| | wsrep_last_committed | 0 | |

| | wsrep_replicated | 0 | |

| | wsrep_replicated_bytes | 0 | |

| | wsrep_repl_keys | 0 | |

| | wsrep_repl_keys_bytes | 0 | |

| | wsrep_repl_data_bytes | 0 | |

| | wsrep_repl_other_bytes | 0 | |

| | wsrep_received | 10 | |

| | wsrep_received_bytes | 752 | |

| | wsrep_local_commits | 0 | |

| | wsrep_local_cert_failures | 0 | |

| | wsrep_local_replays | 0 | |

| | wsrep_local_send_queue | 0 | |

| | wsrep_local_send_queue_max | 1 | |

| | wsrep_local_send_queue_min | 0 | |

| | wsrep_local_send_queue_avg | 0.000000 | |

| | wsrep_local_recv_queue | 0 | |

| | wsrep_local_recv_queue_max | 1 | |

| | wsrep_local_recv_queue_min | 0 | |

| | wsrep_local_recv_queue_avg | 0.000000 | |

| | wsrep_local_cached_downto | 18446744073709551615 | |

| | wsrep_flow_control_paused_ns | 0 | |

| | wsrep_flow_control_paused | 0.000000 | |

| | wsrep_flow_control_sent | 0 | |

| | wsrep_flow_control_recv | 0 | |

| | wsrep_cert_deps_distance | 0.000000 | |

| | wsrep_apply_oooe | 0.000000 | |

| | wsrep_apply_oool | 0.000000 | |

| | wsrep_apply_window | 0.000000 | |

| | wsrep_commit_oooe | 0.000000 | |

| | wsrep_commit_oool | 0.000000 | |

| | wsrep_commit_window | 0.000000 | |

| | wsrep_local_state | 4 |

| | wsrep_local_state_comment | Synced |

| | wsrep_cert_index_size | 0 | |

| | wsrep_causal_reads | 0 | |

| | wsrep_cert_interval | 0.000000 | |

| | wsrep_incoming_addresses | 192.168.56.111 virtual 3306192.168.56.112virtual 3306192.168.56.113purl 3306 |

| | wsrep_desync_count | 0 | |

| | wsrep_evs_delayed |

| | wsrep_evs_evict_list |

| | wsrep_evs_repl_latency | 0 / 0 / 0 | 0 / 0 / 0 |

| | wsrep_evs_state | OPERATIONAL |

| | wsrep_gcomm_uuid | df49c18f-701a-11e7-aaaa-9659aa7ef9f8 |

| | wsrep_cluster_conf_id | 3 | |

| | wsrep_cluster_size | 3 | |

| | wsrep_cluster_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |

| | wsrep_cluster_status | Primary |

| | wsrep_connected | ON |

| | wsrep_local_bf_aborts | 0 | |

| | wsrep_local_index | 0 | |

| | wsrep_provider_name | Galera |

| | wsrep_provider_vendor | Codership Oy |

| | wsrep_provider_version | 3.20 (r7e383f7) |

| | wsrep_ready | ON |

+-+

57 rows in set (0.00 sec)

-- End By TangYun

This is the end of the content of "installation and High availability testing of MySQL5.7 Galera Cluster". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.