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

Editor to share with you how Mariadb uses MariaDB Galera Cluster to achieve mariadb cluster environment, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Summary of MariaDB Galera Cluster:

1. Brief description:

MariaDB Galera Cluster is a system architecture that implements real-time synchronization of multi-master and data on the mysql innodb storage engine. At the business level, there is no need to separate read and write work, and the database read and write pressure can be distributed to each node according to the established rules. Fully compatible with MariaDB and MySQL in terms of data.

two。 Properties:

(1)。 Replicate Synchronous replication synchronously

(2) Active-active multi-master topological logic

(3)。 It can read and write data to any node in the cluster.

(4)。 Automatic membership control, fault nodes are automatically removed from the cluster

(5)。 Automatic node join

(6)。 True parallel replication, based on row level

(7)。 Direct client connection, native MySQL interface

(8)。 Each node contains a complete copy of the data

(9)。 Data synchronization in multiple databases is realized by wsrep interface.

3. Limitation

(1)。 Current replication only supports the InnoDB storage engine. Any tables written to other engines, including mysql.* tables, will not be replicated, but DDL statements will be replicated, so the creating user will be replicated, but insert into mysql.user... It will not be copied.

Delete operation does not support tables without primary keys. Tables without primary keys will have different order in different nodes. If SELECT is performed, the order of tables will be different. LIMIT... Different result sets will appear.

(3)。 LOCK/UNLOCK TABLES is not supported in multi-host environment, as well as lock functions GET_LOCK (), RELEASE_LOCK ().

(4)。 The query log cannot be saved in a table. If you open the query log, it can only be saved to a file.

(5)。 The maximum transaction size allowed is defined by wsrep_max_ws_rows and wsrep_max_ws_size. Any large operation will be rejected. Such as large LOAD DATA operations.

(6)。 Because the cluster is optimistic about concurrency control, the transaction commit may be aborted at this stage. If two transactions are written and committed to the same line to different nodes in the cluster, the failed node will abort. For cluster-level aborts, the cluster returns a deadlock error code (Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).

(7) .XA transactions are not supported because they may be rolled back on the commit.

(8)。 The write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the entire cluster will be slow. In order to achieve stable and high performance requirements, all nodes should use unified hardware.

(9)。 A minimum of 3 cluster nodes are recommended.

(10)。 If there is a problem with the DDL statement, the cluster will be destroyed.

2. MariaDB Galera Cluster building demonstration

1 Planning

Server1: 192.168.1.56

Server3: 192.168.1.81

Galera SST user:sst

Galera SST password:sstpass123

MySQL root password:ESBecs00

Server2: 192.168.1.6

two。 Configure the yum source for mariadb (installed on all three machines)

[root@client137 ~] # vim / etc/yum.repos.d/mariadb.repo

# yum source is as follows:

[mariadb]

Name = MariaDB

Baseurl = http://yum.mariadb.org/5.5/rhel6-amd64

Enabled = 1

Gpgkey= https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

Gpgcheck=1

[root@client137 ~] # yum makecache

3. Install MariaDB-Galera-server galera MariaDB-client (3 sets)

# start the test

[root@client137 ~] # / etc/init.d/mysql start

Starting MySQL....

[root@client137 ~] # chkconfig mysql on

[root@client137 ~] # / usr/bin/mysql_secure_installation-- set the root password. You can also log in to set password.

# Log in to the database and authorize users and passwords for cluster synchronization

[root@client137] # mysql-uroot-pESBecs00

Mysql > GRANT USAGE ON *. * to sst@'%' IDENTIFIED BY 'sstpass123'

Mysql > GRANT ALL PRIVILEGES on *. * to sst@'%'

Mysql > FLUSH PRIVILEGES

Mysql > quit

# create and configure wsrep.cnf files

[root@client137 ~] # cp / usr/share/mysql/wsrep.cnf / etc/my.cnf.d/

[root@client137 ~] # vim / etc/my.cnf.d/wsrep.cnf

5. Configure the parameters of each machine

[root@client137] # iptables-An INPUT-I eth0-p tcp-- dport 3306-j ACCEPT

[root@client137] # iptables-An INPUT-I eth0-p tcp-- dport 4567-j ACCEPT

# start mariadb to see if ports 3306 and 4567 are listening

[root@client137 ~] # / etc/init.d/mysql restart

[root@client137 ~] # netstat-tulpn | grep-e 4567-e 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 32363/mysqld

Tcp 0 0 0.0.0.0 4567 0.0.0.015 * LISTEN 32363/mysqld

Restart mariadb

7. Configuration of newly added nodes

-d: run in daemon mode

-a: cluster address

-g: cluster name

9. Test whether the cluster is configured with parameters:

Login to the database:

View the following parameters:

# Note on the above detailed parameters:

Monitoring status parameters description:

(1)。 Cluster integrity check:

Wsrep_cluster_state_uuid: the values of all nodes in the cluster should be the same. Nodes with different values indicate that they are not connected to the cluster.

Wsrep_cluster_conf_id: normally this value is the same on all nodes. If the value is different, the node is temporarily partitioned. The same value should be restored when the network connection between nodes is restored.

Wsrep_cluster_size: if this value matches the expected number of nodes, then all cluster nodes are connected.

Wsrep_cluster_status: the state of the cluster composition. If it is not Primary, it means that there is a partition or split-brain condition.

(2)。 Node status check:

Wsrep_ready: if the value is ON, the SQL load is acceptable. If Off, you need to check wsrep_connected.

Wsrep_connected: if the value is Off and the value of wsrep_ready is also Off, the node is not connected to the cluster.

Wsrep_local_state_comment: if wsrep_connected is On, but wsrep_ready is OFF, you can check the reason from this item.

(3)。 Copy the health check:

Wsrep_flow_control_paused: indicates how long replication has been stopped. That is to say, the extent to which the cluster is slow due to Slave latency. The value is 0-1, which is as close to 0 as possible, and a value of 1 means that replication stops completely. The value of wsrep_slave_threads can be optimized to improve.

Wsrep_cert_deps_distance: how many transactions can be processed in parallel. The wsrep _ slave_threads setting should not be too much higher than this value.

Wsrep_flow_control_sent: indicates how many times the node has stopped copying.

Wsrep_local_recv_queue_avg: indicates the average length of the slave transaction queue. A sign of bottleneck.

The slowest node has the highest wsrep_flow_control_sent and wsrep_local_recv_queue_avg values. If these two values are lower, they are relatively better.

(4)。 Detect slow network problems:

Wsrep_local_send_queue_avg: a harbinger of network bottleneck. If this value is high, there may be a cyber bottle.

(5)。 Number of conflicts or deadlocks:

Wsrep_last_committed: number of transactions last committed

Wsrep_local_cert_failures and wsrep_local_bf_aborts: rollback, number of conflicts detected

10. Test data synchronization, consistency and other issues, this test is not demonstrated, an overview of the general idea:

(1)。 Create a database to see if it is synchronized

(2)。 Create a table for the innodb and myisam engines in the database to see if it is synchronized

(3)。 Insert data into these two tables respectively to see if they are synchronized. Except innodb engine data can be synchronized, other engines are out of sync.

(4)。 Insert and delete data at any node to see if it is synchronized

11. After the above galera cluster is built, we can realize the load between mysql database clusters with the help of haproxy and lvs.

There is no demonstration here, you can try your own configuration!

These are all the contents of the article "how Mariadb uses MariaDB Galera Cluster to implement mariadb's cluster environment". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.