MySQL Cluster in practice: use MariaDB-Galera integrated version _ MySQL under CentOS6

MySQL Cluster: use MariaDB-Galera integrated version of MariaDBCentOSMysql cluster under CentOS6

BitsCN.com

Speaking of mysql clusters, it is estimated that many people will first think of mysql's built-in replication or mysql-mmm. Mysql-mmm is actually based on the built-in replication of mysql, but the encapsulation is better, but it is still difficult to configure, and it is powerless to dynamically increase or decrease the master node.

By chance, I learned that there is a mysql-based cluster galera. apart from InnoDB only, there are basically no disadvantages. Let's take a look at what the official saying is:

FeaturesMySQL/Galera is synchronous multi-master cluster for MySQL/InnoDB database, having features like:    Synchronous replication    Active-active multi-master topology    Read and write to any cluster node    Automatic membership control, failed nodes drop from the cluster    Automatic node joining    True parallel replication, on row level    Direct client connections, native MySQL look & feelBenefitsThese features yield un-seen benefits for a DBMS clustering solution:    No slave lag    No lost transactions    Both read and write scalability    Smaller client latencies

To put it bluntly, start testing immediately. the OS used for testing is 64-bit CentOS 6. First, add the software repository of MariaDB and create the File "/etc/yum. repos. d/MariaDB. repo ".

# MariaDB 5.5 CentOS repository list - created 2013-11-05 06:30 UTC# http://mariadb.org/mariadb/repositories/[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/5.5/centos6-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1

Then install MariaDB-Galera integrated version

# yum -y install MariaDB-Galera-server.x86_64 MariaDB-client.x86_64 galera.x86_64

# Cp/usr/share/mysql/wsrep. cnf/etc/my. cnf. d/

By default, Galera uses port 4567 to synchronize data. you need to modify the firewall. in this case, the firewall is disabled directly.

# /etc/init.d/iptables stop

MariaDB-Galera does not seem to provide you with the supporting selinux configuration. for convenience, selinux is directly disabled.

# setenforce 0

Since it is a cluster, of course, there cannot be only one machine

Machine	192.168.56.103
Machine B	192.168.56.104
Machine c	192.168.56.105

Modify "/etc/my. cnf. d/wsrep. cnf" of machine.

# Full path to wsrep provider library or 'none'wsrep_provider=/usr/lib64/galera/libgalera_smm.so# Group communication system handlewsrep_cluster_address="gcomm://"# Address which donor should send State Snapshot to.# Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!!# (SST method dependent. Defaults to the first IP of the first interface)wsrep_sst_receive_address=192.168.56.103

Because machine a is the first node, you can enter "gcomm: //" in wsrep_cluster_address. To join a cluster, enter the ip address of any node in the cluster, for example, "gcomm: // 192.168.56.103: 4567 ". Wsrep_sst_receive_address is the ip address used by the local machine to receive synchronization data. by default, it is the first ip address found in the machine network configuration. if the machine has multiple ip addresses, you 'd better specify them, for example, "192.168.56.103 ". Start mysql database service

# /etc/init.d/mysql start

Now we need to add Machine B to the cluster and modify the configuration file "/etc/my. cnf. d/wsrep. cnf ".

# Full path to wsrep provider library or 'none'wsrep_provider=/usr/lib64/galera/libgalera_smm.so# Group communication system handlewsrep_cluster_address="gcomm://192.168.56.103:4567"# Address which donor should send State Snapshot to.# Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!!# (SST method dependent. Defaults to the first IP of the first interface)wsrep_sst_receive_address=192.168.56.104

Start the mysql service of Machine B.

Now the cluster has been set up, and now the cluster is officially tested. First, connect to machine a's mysql service and create a database and a table. The table contains an AUTO_INCREMENT field that makes mysql-mmm troublesome and troublesome, by the way, add a user to access this database.

[root@centos6 ~]# mysql -u rootWelcome to the MariaDB monitor.  Commands end with ; or /g.Your MariaDB connection id is 3Server version: 5.5.33a-MariaDB MariaDB Server, wsrep_23.7.6.rXXXXCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.MariaDB [(none)]> create database asdf;Query OK, 1 row affected (0.03 sec)MariaDB [(none)]> grant all on asdf.* to 'aauu'@'localhost' identified by '123456';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> use asdf;Database changedMariaDB [asdf]> create table aatt (aa int primary key auto_increment);Query OK, 0 rows affected (0.14 sec)MariaDB [asdf]> insert into aatt values (null);Query OK, 1 row affected (0.01 sec)MariaDB [asdf]> insert into aatt values (null);Query OK, 1 row affected (0.00 sec)MariaDB [asdf]> select * from aatt;+----+| aa |+----+|  2 ||  4 |+----+2 rows in set (0.00 sec)

The step of auto_increment is automatically changed to 2. is it intelligent? Now we go to Machine B and use the new user name to log on to the mysql service and perform similar operations.

[root@centos6 ~]# mysql -u aauu -pEnter password: Welcome to the MariaDB monitor.  Commands end with ; or /g.Your MariaDB connection id is 4Server version: 5.5.33a-MariaDB MariaDB Server, wsrep_23.7.6.rXXXXCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.MariaDB [(none)]> use asdf;Database changedMariaDB [asdf]> insert into aatt values (null);Query OK, 1 row affected (0.01 sec)MariaDB [asdf]> insert into aatt values (null);Query OK, 1 row affected (0.04 sec)MariaDB [asdf]> select * from aatt;+----+| aa |+----+|  2 ||  4 ||  5 ||  7 |+----+4 rows in set (0.00 sec)

No? There is nothing worth mentioning about the data inserted just now. What's amazing is that the new user on machine a can be used here! Now let's dynamically add Machine c to the cluster.

# Full path to wsrep provider library or 'none'wsrep_provider=/usr/lib64/galera/libgalera_smm.so# # Group communication system handlewsrep_cluster_address="gcomm://192.168.56.104:4567"# # Address which donor should send State Snapshot to.# # Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!!# # (SST method dependent. Defaults to the first IP of the first interface)wsrep_sst_receive_address=192.168.56.105

As mentioned before, you can add the ip addresses of any node in the cluster. Start the mysql service of Machine c and perform similar operations

[root@centos6 ~]# mysql -u rootWelcome to the MariaDB monitor.  Commands end with ; or /g.Your MariaDB connection id is 3Server version: 5.5.33a-MariaDB MariaDB Server, wsrep_23.7.6.rXXXXCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.MariaDB [(none)]> use asdf;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [asdf]> insert into aatt values (null);Query OK, 1 row affected (0.00 sec)MariaDB [asdf]> insert into aatt values (null);Query OK, 1 row affected (0.00 sec)MariaDB [asdf]> select * from aatt;+----+| aa |+----+|  2 ||  4 ||  5 ||  7 ||  9 || 12 |+----+6 rows in set (0.00 sec)

The step of auto_increment is automatically changed to 3. do you see it?

Here I just briefly list a use case of Galera, and there are many advanced aspects of Galera that I did not mention here. you can check it on its official website.

Reference URL:

1. Http://www.codership.com/
2. Https://launchpad.net/wsrep
3. Http://blog.sina.com.cn/s/blog_704836f40101lixp.html

BitsCN.com