Galera/mysql Cluster memo _ MySQL

Galeramysql cluster memo Mysql Cluster

BitsCN.com

Features
MySQL/Galera is a multi-master synchronization cluster, but it only uses the MySQL/InnoDB engine and has the following features:
Synchronous replication
Topology of multiple master servers
Read/write on any node
Automatically control members and delete faulty nodes
Automatically join a node
Real row-level concurrent replication
Scheduling customer connection

Advantages
Refer to the solution based on DBMS cluster below
The slave server role does not exist.
Transaction loss does not exist
Read/write operations can be expanded as needed
Less latches

Knowledge Point
The MySQL/Galera Cluster uses the Galera database for replication. corresponding to the Galera replication interface, we need the MySQL server to support the wsrep API interface.
Http://www.codership.com/products/mysql-write-set-replication-project

Can I use MySQL instead of mariadb?
No, because mysql does not support wsrep _ data replication parameters. of course, the code level is also quite different.

Working Principle

Mariadb can be seen as a common database for connecting applications (web, API, etc)
Mariadb alone cannot synchronize data of multiple master servers
Data Synchronization in multiple databases is implemented by the wsrep interface

The final goal is to achieve simultaneous read/write of multiple MySQL databases.

Wsrep API
Wsrep API is a database plug-in interface, similar to an application, mainly for write replication
This program is mainly used to define how the application calls the replication database for write-back.
Wsrep API is dynamically loaded by applications that support database change

Global transaction ID (GTID)
The wsrep API describes the following replication model. an application, such as the current object of the database, is modified by the client. an object change leads to a series of atomic changes in transactions, all nodes in the cluster have the same objects, and the synchronized replication applications have their own nodes, which produce the same changes in the same order to achieve data synchronization.

Finally, wsrep API will assign a global transaction ID. this ID has the following functions:
Change of ID object
Identify the final status of the object's own ID (normally, the ID is continuous)

GTID inclusion
A UUID is used as the object ID and sequence number that has undergone changes. The sequence number changes continuously.
GTID allows you to compare application states, establish the order of object changes, and determine whether to update GTID for object changes.
Generally, GTID records in the following format.
45eec521-2f34-11e0-0800-2a36050b826b: 94530586304

To put it bluntly, we need to compile mariadb-mysql and galera plug-ins.

Galera/mysql compilation steps

Https://downloads.mariadb.org/interstitial/mariadb-galera-5.5.33a/kvm-tarbake-jaunty-x86/mariadb-galera-5.5.33a.tar.gz/from/http://mirrors.scie.in/mariadb

Yum install-y cmake

Tar xf mariadb-galera-5.5.33a.tar.gz
Cd mariadb-5.5.33a/
Cmake-LAH

Refer to the configuration information in the CMakeCache.txt file.

cmake -DINSTALL_MYSQLDATADIR:STRING=/mdb -DINSTALL_UNIX_ADDRDIR:STRING=/var/run/mysqld/mysql5.socketmakemake install

By default, mariadb is installed in/usr/local/mysql

Galera compilation

Https://launchpad.net/galera/2.x/23.2.7/+download/galera-23.2.7-src.tar.gz

Add data source

baseurl=http://mirror.neu.edu.cn/fedora/epel//6Server/x86_64/

Add the following software package

yum erase -y mysql.x86_64 mysql-devel.x86_64 mysql-libs.x86_64  yum install -y boost-devel.x86_64 libodb-boost-devel.x86_64  bzr scons

Decompress the galera-23.2.7-src.tar.gz and compile it

cd /usr/srctar xf galera-23.2.7-src.tar.gzcd galera-23.2.7-srcscons

Libgalera_smm.so can be generated after compilation.

Copy the compiled library to the following location:/usr/local/galera/lib/libgalera_smm.so

mkdir /usr/local/galera/lib -pcp /usr/src/galera-23.2.7-src/libgalera_smm.so /usr/local/galera/lib/libgalera_smm.so

Copy the boot script/usr/src/galera-23.2.7-src/scripts/mysql-galera to/usr/local

cp  /usr/src/galera-23.2.7-src/scripts/mysql/mysql-galera /usr/local

Create/usr/local/mysql/etc/my. cnf

[Mysqld] basedir =/usr/local/mysqlbig-tablesbind-address = 0.0.0.0character-set-server = utf8datadir =/mdblog-error =/var/log/mysqld/mysql5-error.logsocket =/var /run/mysqld/mysql5.socketpid-file =/var/run/mysqld/mysql5.pidport = 3306 user = mysqlbinlog_format = signature = utf8collation_server = utf8_general_cidefault-storage-engine = signature = 1innodb_file_per_table = 1innodb_flush_log_at_trx_commit = 2server-id = 1max_connections = 1000net_buffer_length = 8Kopen-files-limit = 65535wsrep_cluster_address = 'gcomm: // 192.168.200.163, 192.168.200.171, 172.18.8.49, 172.18.8.50 'wsrep _ provider =/usr/local/galera/lib/secure = 0wsrep_sst_method = rsyncwsrep_provider_options = "gcache. size = 256 m; gcache. page_size = 256 m "wsrep_slave_threads = 16wsrep_cluster_name = 'My _ cluster' wsrep _ node_name = 'db5' wsrep _ sst_auth = tt: tt123maridb start the test

Initialize database

mkdir /mdbcd /usr/local/mysql./scripts/install_mysql_db --datadir=/mdb

Start the script/etc/rc. d/init. d/mysql5 to ensure that the file is executable.

#!/bin/sh# chkconfig: 2345 64 36# description: A very fast and reliable SQL database engine.

Basedir =/usr/local/mysql datadir =/mdb

# Default value, in seconds, afterwhich the script shocould timeout waiting # for server start. # Value here is overriden by value in my. cnf. #0 means don't wait at all # Negative numbers mean to wait indefinitely service_startup_timeout = 900 startup_sleep = 1

# Lock directory for RedHat/SuSE. lockdir = '/var/lock/subsys' lock_file_path = "$ lockdir/mysql"

# The following variables are only set for lew.mysql. server find things.

# Set some defaults mysqld_pid_file_path =/var/run/mysqld/mysql5.pid if test-z "$ basedir" then basedir =/usr/local/mysql bindir =/usr/local/mysql/ bin if test-z "$ datadir" then datadir =/usr/local/mysql/data fi sbindir =/usr/local/mysql/bin libexecdir =/usr/local/mysql/ bin

Else bindir = "$ basedir/bin" if test-z "$ datadir" then datadir = "$ basedir/data" fi sbindir = "$ basedir/sbin" if test-f" $ basedir/bin/mysqld "then libexecdir =" $ basedir/bin "else libexecdir =" $ basedir/libexec "fi

# Datadir_set is used to determine if datadir was set (and so shoshould be # ** not * set inside of the -- basedir = handler.) datadir_set =

# Use LSB init script functions for printing messages, if possible # lsb_functions = "/lib/lsb/init-functions" if test-f $ lsb_functions; then. $ lsb_functions else log_success_msg () {echo "SUCCESS! $ @ "} Log_failure_msg () {echo" ERROR! $ @ "} Fi

PATH = "/sbin:/usr/sbin:/bin:/usr/bin: $ basedir/bin"

Export PATH

Mode = $1 # start or stop

[$ #-Ge 1] & shift

Other_args = "$ *" # uncommon, but needed when called from an RPM upgrade action # Expected: "-- skip-networking -- skip-grant-tables" # They are not checked here, intentionally, as it is the resposibility # of the "spec" file author to give correct arguments only.

Case 'echo "testing/c" ', 'echo-n testing' in * c *,-n *) echo_n = echo_c =; * c *,*) echo_n =-n echo_c =; *) echo_n = echo_c = '/C'; esac

Parse_server_arguments () {for arg do case "$ arg" in -- basedir = *) basedir = 'echo "$ arg" | sed-e's/^ [^ =] * = // ''bindir = "$ basedir/bin" if test-z "$ datadir_set "; then datadir = "$ basedir/data" fi sbindir = "$ basedir/sbin" if test-f "$ basedir/bin/mysqld" then libexecdir = "$ basedir/bin" else libexecdir = "$ basedir/libexec" fi libexecdir = "$ basedir/libexec ";; -- datadir = *) datadir = 'echo "$ arg" | sed-e's/^ [^ =] * = // ''datadir_set = 1

; -- Pid-file = *) mysqld_pid_file_path = 'echo "$ arg" | sed-e's/^ [^ =] * = //'';; -- service-startup-timeout = *) service_startup_timeout = 'echo "$ arg" | sed-e's/^ [^ =] * = // ''; esac done}

Wait_for_pid () {verb = "$1" # created | removed pid = "$2" # process ID of the program operating on the pid-file pid_file_path = "$3" # path to the PID file.

Sst_progress_file = $ datadir/sst_in_progress I = 0 avoid_race_condition = "by checking again"

While test $ I-ne $ service_startup_timeout; do

Case "$ verb" in 'created ') # wait for a PID-file to pop into existence. test-s "$ pid_file_path" & I = ''& break; 'removed') # wait for this PID-file to disappear test! -S "$ pid_file_path" & I = ''& break; *) echo" wait_for_pid () usage: wait_for_pid created | removed pid pid_file_path "exit 1; esac

# If server isn' t running, then pid-file will never be updated if test-n "$ pid "; then if kill-0 "$ pid" 2>/dev/null; then: # the server still runs

Else # The server may have exited between the last pid-file check and now. if test-n "$ avoid_race_condition"; then avoid_race_condition = "" continue # Check again. fi

# There's nothing that will affect the file. log_failure_msg "The server quit without updating PID file ($ pid_file_path)." return 1 # not waiting any more. fi

If test-e $ sst_progress_file & [$ startup_sleep-ne 100]; then echo $ echo_n "SST in progress, setting sleep higher" startup_sleep = 100 fi

Echo $ echo_n ". $ echo_c" I = 'expr $ I + 1' sleep $ startup_sleep

Done

If test-z "$ I"; then log_success_msg return 0 else log_failure_msg return 1 fi}

# Get arguments from the my. cnf file, # the only group, which is read from now on is [mysqld] if test-x./bin/my_print_defaults then

Print_defaults = ". /bin/my_print_defaults "elif test-x $ bindir/my_print_defaults then print_defaults =" $ bindir/my_print_defaults "elif test-x $ bindir/mysql_print_defaults then print_defaults =" $ bindir/else "# Try to find basedir in/etc/my. cnf conf =/usr/local/mysql/etc/my. cnf print_defaults = if test-r $ conf then subpat = '^ [^ =] * basedir [^ =] * = /(. */) $ 'dirs = 'SED-e "/$ subpat /! D "-e's // 1/'$ Conf' for d in $ dirs do d = 'echo $ d | sed-e's/[] // g' 'If test-x "$ d/bin/my_print_defaults" then print_defaults = "$ d/bin/my_print_defaults" break fi if test-x "$ d/bin/mysql_print_defaults" then print_defaults = "$ d/bin/mysql_print_defaults" break fi done fi

# Hope it's in the PATH... but I doubt it test-z "$ print_defaults" & print_defaults = "my_print_defaults" fi

# Read defaults file from 'basedir'. If there is no defaults file there

# Check if it's in the old (depricated) place (datadir) and read it from there #

Extra_args = "" if test-r "$ basedir/my. cnf "then extra_args ="-e $ basedir/my. cnf "else if test-r" $ datadir/my. cnf "then extra_args ="-e $ datadir/my. cnf "fi

Parse_server_arguments '$ print_defaults $ extra_args mysqld server mysql_server mysql. server'

# Set pid file if not given # if test-z "$ mysqld_pid_file_path" then mysqld_pid_file_path = $ datadir/'hostname '. pid else case "$ mysqld_pid_file_path" in/*); *) mysqld_pid_file_path = "$ datadir/$ mysqld_pid_file_path"; esac fi

Case "$ mode" in 'start') # start daemon

# Safeguard (relative paths, core dumps...) cd $ basedir

Echo $ echo_n "Starting MySQL"

If test-x $ bindir/mysqld_safe then # Give extra arguments to mysqld with the my. cnf file. this script # may be overwritten at next upgrade. $ bindir/mysqld_safe -- datadir = "$ datadir" -- pid-file = "$ mysqld_pid_file_path" $ other_args>/dev/null 2> & 1 & wait_for_pid created "$! "" $ Mysqld_pid_file_path "; return_value = $?

# Make lock for RedHat/SuSE if test-w "$ lockdir" then touch "$ lock_file_path" fi

Exit $ return_value else log_failure_msg "Couldn't find MySQL server ($ bindir/mysqld_safe)" fi ;;

'Stop') # stop daemon. We use a signal here to avoid having to know the # root password.

If test-s "$ mysqld_pid_file_path" then mysqld_pid = 'cat "$ mysqld_pid_file_path "'

If (kill-0 $ mysqld_pid 2>/dev/null) then echo $ echo_n "Shutting down MySQL" kill $ mysqld_pid # mysqld shold remove the pid file when it exits, so wait for it. wait_for_pid removed "$ mysqld_pid" "$ mysqld_pid_file_path"; return_value =$? Else log_failure_msg "MySQL server process # $ mysqld_pid is not running! "Rm" $ mysqld_pid_file_path "fi

# Delete lock for RedHat/SuSE if test-f "$ lock_file_path" then rm-f "$ lock_file_path" fi exit $ return_value else log_failure_msg "MySQL server PID file cocould not be found! "Fi ;;

'Restart') # Stop the service and regardless of whether it was # running or not, start it again. if $0 stop $ other_args; then $0 start $ other_args else log_failure_msg "Failed to stop running server, so refusing to try to start. "exit 1 fi ;;

'Reload' | 'force-reload') if test-s "$ mysqld_pid_file_path "; then read mysqld_pid <"$ alarm" kill-HUP $ mysqld_pid & log_success_msg "Reloading service MySQL" touch "$ alarm" else log_failure_msg "MySQL PID file cocould not be found! "Exit 1 fi; 'status') # First, check to see if pid file exists if test-s" $ mysqld_pid_file_path "; then read mysqld_pid <" $ mysqld_pid_file_path"

If kill-0 $ mysqld_pid 2>/dev/null; then log_success_msg "MySQL running ($ mysqld_pid)" exit 0 else log_failure_msg "MySQL is not running, but PID file exists "exit 1 fi else # Try to find appropriate mysqld process mysqld_pid = 'pidof $ libexecdir/mysqld'

# Test if multiple pids exist pid_count = 'echo $ mysqld_pid | wc-W' if test $ pid_count-gt 1; then log_failure_msg "Multiple MySQL running but PID file cocould not be found ($ mysqld_pid)" exit 5 elif test-z $ mysqld_pid; then if test-f "$ lock_file_path "; then log_failure_msg "MySQL is not running, but lock file ($ lock_file_path) exists" exit 2 fi log_failure_msg "MySQL is not running" exit 3 else Log_failure_msg "MySQL is running but PID file cocould not be found" exit 4 fi; 'configtest') # Safeguard (relative paths, core dumps ..) cd $ basedir echo $ echo_n "Testing MySQL configuration syntax" daemon = $ bindir/mysqld if test-x $ libexecdir/mysqld then daemon = $ libexecdir/mysqld elif test-x $ sbindir/ mysqld then daemon = $ sbindir/mysqld elif test-x 'which mysqld' then daemon = 'which mysqld' els E log_failure_msg "Unable to locate the mysqld binary! "Exit 1 fi help_out = '$ daemon -- help 2> & 1'; r = $? If test "$ r "! = 0; then log_failure_msg "$ help_out" log_failure_msg "There are syntax errors in the server configuration. Please fix them! "Else log_success_msg" Syntax OK "fi exit $ r; 'bootstrap ') # bootstrap the cluster, start the first node # that initiate the cluster echo $ echo_n "Bootstrapping the cluster" $0 start $ other_args -- wsrep-new-cluster ;;*) # usage basename = 'basename "$0" 'echo "Usage: $ basename {start | stop | restart | reload | force-reload | status | configtest | bootstrap} [MySQL server options] "exit 1; esac

Exit 0

Start each database

service mysql5 start

Create the following users in each database for sst authentication

GRANT USAGE ON *.* to tt@'%' IDENTIFIED BY 'tt123';GRANT ALL PRIVILEGES on *.* to tt@'%';GRANT USAGE ON *.* to tt@'localhost' IDENTIFIED BY 'tt123';GRANT ALL PRIVILEGES on *.* to tt@'localhost';flush privileges;

Close all databases

service mysql5 stop

Create and join a cluster

Start the first node in the cluster (192.168.200.163)
Create a soft link and start the cluster. mariadb is automatically started when the cluster is started.

ln -s /usr/local/mysql/bin/ /usr/local/mysql/sbincd /usr/local/./mysql-galera -g  gcomm://  start

Test whether the method is enabled successfully and check whether Port 4567 is automatically started.

[root@db2 local]# netstat -ntlActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address               Foreign Address             Statetcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTENtcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTENtcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN

After logging on to mysql, check whether the galery plug-in is enabled.

MariaDB [(none)]> show status like 'wsrep_ready';+---------------+-------+| Variable_name | Value |+---------------+-------+| wsrep_ready   | ON    |+---------------+-------+1 row in set (0.00 sec)

Note: on is in the enabled plug-in status.

Close method

cd /usr/local./mysql-galera stop

How to add other nodes to a cluster

The first (192.168.200.163) node has been started successfully.
The second server (192.168.200.171) needs to be added to the cluster.

cd /usr/local/./mysql-galera -g  gcomm://192.168.200.163 start

You can start the cluster test using the preceding method, or follow the following method to check whether two server addresses are added to the cluster address.

MariaDB [(none)]> show status like 'wsrep_incoming_addresses';+--------------------------+-------------------------------------------+| Variable_name            | Value                                     |+--------------------------+-------------------------------------------+| wsrep_incoming_addresses | 192.168.200.171:3306,192.168.200.163:3306 |+--------------------------+-------------------------------------------+1 row in set (0.00 sec)

The third server (172.18.8.49) needs to be added to the cluster.

cd /usr/local/./mysql-galera -g  gcomm://192.168.200.163,192.168.200.171 start

The fourth server (172.18.8.50) needs to be added to the cluster.

cd /usr/local/./mysql-galera -g  gcomm://192.168.200.163,192.168.200.171,172.18.8.49 start

Note: Each time the cluster is started, the data synchronization mechanism will be enabled, so that data synchronization in each cluster is assumed that Node 3 (172.18.8.49) is out of the cluster during the cluster's work and restarts, when a fault occurs and is offline, nodes 1, 2, and 4 can continue to work. when Node 3 is online again, before joining the cluster, the method for automatically synchronizing data and going online again will be consistent with the method for adding nodes above. common wsrep parameter comments

MariaDB [terry]> show status like 'wsrep % '; + statement + | Variable_name | Value | + dimensions + | wsrep_local_state_uuid | bb5b9e17-66c8-11e3-86ba-96854521d205 | unique uuid cluster tag | wsrep_protocol_version | 4 | wsrep_last_committed | 16 | SQL submit record | 4 | number of times that data is replicated | wsrep_replicated_bytes | 692 | number of bytes that data is replicated | wsrep_received Ed | 18 | number of data copies received | wsrep_received_bytes | 3070 | number of bytes received by data replication | wsrep_local_comm | 4 | locally executed SQL | wsrep_local_cert_failures | 0 | local transaction failed | 0 | interrupted locally during transaction execution | wsrep_local_replays | 0 | wsrep_local_send_queue | 0 | locally sent queue | average | 0.142857 | average queue interval | average | 0 | local receiving queue | average | 0.000000 | local receiving interval | wsrep_flow_control_paused | 0.000000 | wsrep_flow_control_sent | 0 | wsrep_flow_control_recv | 0 | concurrent | 0.000000 | concurrent Count | concurrent | 0.000000 | concurrent | 0.000000 | wsrep_apply_window | 1.000000 | wsrep_commit_oooe | 0.000000 | wsrep_commit_oool | 0.000000 | wsrep_commit_window | 1.000000 | wsrep_local_state | 4 | wsrep_local_state_comment | Synced | wsrep_cert_index_size | 0 | wsrep_causal_reads | 0 | wsrep_incoming_addresses | 172.18.8.50: 3306,172.18 .8.49: 3306 | connected database | wsrep_cluster_conf_id | 18 | wsrep_cluster_size | 2 | number of cluster members | Primary | bb5b9e17-66c8-11e3-86ba-96854521d205 | cluster ID | wsrep_cluster_status | Primary | master server | wsrep_connected | ON | whether to connect | wsrep_local_index | 1 | wsrep_provider_name | Galera | wsrep_provider_vendor | Codership Oy
 
  
| Wsrep_provider_version | 2.7 (rXXXX) | wsrep_ready | ON | whether the plug-in is in application + -------------------------------------------------------- + 40 rows in set (0.05 sec)
 

There is no time for stress testing or comparison between galera and Percona XtraDB Cluster.

In addition, it is very convenient to use rpm. There are many tutorials on the Internet, which are not described in detail.

BitsCN.com