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

This article shows you how to install and configure galera_cluster for mysql. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

This experiment is synchronized with rsync, the third node of xtrabackup cannot get up, and xtrabackup cannot recover when all data files are deleted.

SST: full recovery

IST: incremental recovery

Note:

1. To use Galera, you must patch MySQL-Server with wsrep. You can directly use the patched MySQL installation package provided by the government. If the standard version of MYSQL is installed on the server, you need to uninstall it and then reinstall it. Pay attention to backing up data before uninstalling.

2. MySQL/Galera cluster only supports InnoDB storage engine. If your datasheet uses MyISAM, you need to convert it to InnoDB, otherwise the records will not be copied on multiple computers. You can add the-skip-create-options parameter to the mysqldump command when backing up old data, which removes the declaration information of the table structure and automatically uses the InnoDB engine when it is imported into the cluster. However, this will remove the AUTO_INCREMENT as well, and tables that already have AUTO_INCREMENT columns must be redefined after import.

3. InnoDB engines with MySQL 5.5 and below do not support full-text indexing (FULLTEXT indexes). If you have previously used MyISAM and built a full-text index field, you can only install MySQL 5.6with wsrep patch.

4. All data tables must have a primary key (PRIMARY). If there is no primary key, you can create an AUTO_INCREMENT column.

5. MySQL/Galera cluster does not support the following query: LOCK/UNLOCK TABLES, and the following system variables: character_set_server, utf16, utf32 and ucs2.

6. Database logs do not support saving to tables and can only be exported to files (log_output = FILE). Binlog-do-db and binlog-ignore-db cannot be set.

7. Like other clusters, in order to avoid brain cracks and destroy data, it is recommended to add a minimum of 3 nodes in the Galera cluster.

8. In the case of high concurrency, transaction conflicts may occur when multiple masters write at the same time, when only one transaction request succeeds and all others fail. You can automatically retry and return the result when the write / update fails.

9. The status of each node in the node is equal, there is no primary or secondary, and the effect of reading and writing to any node is the same. It can actually be used with VIP/LVS or HA to achieve high availability.

10. If all the machines in the cluster restart, such as a power outage in the computer room, the first server started must be started with an empty address: mysqld_safe-wsrep_cluster_address=gcomm:// > / dev/null &

Yum install libaio gcc gcc-c++ boost-devel scons check-devel openssl-devel

1 、 # cat / etc/hosts

172.16.100.131 node1

172.16.100.224 node2

172.16.99.161 node3

2. Modify the file descriptor 65535

# vim / etc/security/limits.conf

* soft nofile 65535

* hard nofile 65535

3 、 # vim / etc/sysctl.conf

Fs.file-max=655350

Net.ipv4.ip_local_port_range = 1025 65000

Net.ipv4.tcp_tw_reuse = 1

# sysctl-p

4. Install the percona xtrabackup backup tool

Slightly

Such as binary installation

# tar zxvf percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

# cd percona-xtrabackup-2.3.2-Linux-x86_64/bin/

# cp-a * / usr/bin/

Rpm is slightly

5. Download address

Download address of Codership:

Http://releases.galeracluster.com/binary/mysql-wsrep-5.6.31-25.16-linux-x86_64.tar.gz

# tar xvfz mysql-wsrep-5.6.31-25.16-linux-x86_64.tar.gz

# mv mysql-wsrep-5.6.31-25.16-linux-x86_64 / usr/local/mysql

6. Install the Galera replication plug-in

Https://launchpad.net/galera/3.x/25.3.5/+download/galera-25.3.5-src.tar.gz

Tar zxvf galera-*

Cd galera-*

Scons

Cp garb/garbd / usr/local/mysql/bin/

Cp libgalera_smm.so / usr/local/mysql/lib/plugin/

Note: the downloaded tar package was not found in the scons yum source

# python setup.py install

7 、

# vim / etc/my.cnf

[mysqld]

Server_id=129

Datadir=/data/galera

Socket=/tmp/mysql.sock

User=mysql

Skip-external-locking

Skip-name-resolve

Character-set-server = utf8

# # #

# # Galera Cluster

Wsrep_provider = / usr/local/mysql/lib/plugin/libgalera_smm.so-set up the galera class library

# wsrep_cluster_address= "gcomm://172.16.100.131172.16.100.224172.16.99.161" the ip address of each node

# wsrep_provider_options= "gcache.size=4G"-similar to the size of the cache, which is used for incremental recovery. Default is 128MB.

Wsrep_cluster_name=MariaDB Galera Cluster-Cluster name, whatever you want

Wsrep_sst_auth=admin:123456-user name and password for synchronization, which must be all permission

# wsrep_sst_method=xtrabackup-v2-synchronization, as well as mysqldump and rsync

Wsrep_sst_method=rsync

# wsrep_sst_method=mysqldump

Wsrep_node_name=172.16.100.224-Node name, which can be ip or server name (each node has a different name)

Wsrep_slave_threads=2-start parallel replication threads, set according to the number of CPU cores

Wsrep_on=ON-enable full synchronous replication mode

Binlog_format = ROW must be in row mode

Innodb_flush_log_at_trx_commit = 0-because the node recovery of Galera Cluster is through the remote node, which is set to 0 for performance reasons, that is, transaction commits are flushed every 1 second.

Innodb_autoinc_lock_mode=2-change the primary key self-increment mode to cross mode

Query_cache_size = 0-turn off query caching

8. Initialization

# cd / usr/local/mysql/scripts/

. / mysql_install_db-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/galera

9. The first node starts

. / mysqld_safe-user=mysql-wsrep-cluster-address= "gcomm://" &

Note:-- the parameter-- wsrep-new-cluster should be added when wsrep-new-cluster starts for the first time, and you don't need to add it when you restart it later (not sure whether to add it or not, but you can get up anyway)

Access to the database

Mysql > grant all on *. * to admin@'%' identified by '123456'

Must be all permission

Just start the second node and the third node normally, and just add a few nodes.

. / mysqld_safe-user=mysql-wsrep-cluster-address= "gcomm://172.16.100.131:4567172.16.100.224:4567" &

. / mysqld_safe-user=mysql-wsrep-cluster-address= "gcomm://172.16.100.131:4567172.16.99.161:4567" &

Enter the database to view

Mysql > show status like'% ws%'

Wsrep_cluster_size | 3

Wsrep_cluster_status | Primary

Wsrep_ready | ON

If you see these, there will be no problem.

Check again to see if admin users have automatically created

Note: to use xtrabackup for synchronization, you need to install lsof socat tar, where socat is not found in my yum source.

Download address:

Http://www.dest-unreach.org/socat/download/socat-2.0.0-b9.tar.gz

# tar xvrz socat-2.0.0-b9.tar.gz

# cd socat-2.0.0-b9

#. / configure

# make & & make install

After rebooting the second node and the third node, the second node is up and the third node is not up.

The error is as follows:

2016-07-29 01:09:36 30877 [Note] WSREP: Member 0.0 (172.16.99.161) requested state transfer from'* any*'. Selected 1.0 (172.16.100.131) (SYNCED) as donor.

2016-07-29 01:09:36 30877 [Note] WSREP: Shifting PRIMARY-> JOINER (TO: 4)

2016-07-29 01:09:36 30877 [Note] WSREP: Requesting state transfer: success, donor: 1

2016-07-29 01:09:36 30877 [Warning] WSREP: 1.0 (172.16.100.131): State transfer to 0.0 (172.16.99.161) failed:-2 (No such file or directory)

2016-07-29 01:09:36 30877 [ERROR] WSREP: gcs/src/gcs_group.c:gcs_group_handle_join_msg (): 723: Will never receive state Need to abort.

2016-07-29 01:09:36 30877 [Note] WSREP: gcomm: terminating thread

2016-07-29 01:09:36 30877 [Note] WSREP: gcomm: joining thread

2016-07-29 01:09:36 30877 [Note] WSREP: gcomm: closing backend

2016-07-29 01:09:36 30877 [Note] WSREP: view (view_id (NON_PRIM,0ee11156-54e6-11e6murb16eMura303ff8a662b WSREP 11) memb {

0ee11156-54e6-11e6 talk b16e color a303ff8a662b line 0

} joined {

} left {

} partitioned {

4d838aa2-5633-11e6 color ba70 color fb0ade4193cdline 0

D1533158-6a9f-11e6-bea7-7fa18ade38f5jue 0

})

2016-07-29 01:09:36 30877 [Note] WSREP: view ((empty))

2016-07-29 01:09:36 30877 [Note] WSREP: gcomm: closed

2016-07-29 01:09:36 30877 [Note] WSREP: / usr/local/mysql/bin/mysqld: Terminated.

160729 01:09:36 mysqld_safe mysqld from pid file / data/galera/zabbixserver.pid ended

The specific reason is unknown. Finally, the synchronization method of rsyc is used for synchronization.

10. Testing

I can't use xtrabackup. It's all changed to rsync.

Delete the data file of the third node, specify which node to restore from in the matching file, add wsrep_sst_donor=172.16.100.224 in my.cnf (restore from the second node), restart, no problem. Read the journal.

The above is how to install and configure galera_cluster for mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are 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.