[HA] MySQL with Galera

 블로그>[vagabond] | vagab0nd

 http://blog.naver.com/realnaut/220323448188

출처 : http://www.sebastien-han.fr/blog/2012/04/01/mysql-multi-master-replication-with-galera/

http://galeracluster.com/products/

http://docs.openstack.org/high-availability-guide/content/ha-aa-db-mysql-galera.html

MySQL with Galera - OpenStack High Availability Guide  - current

docs.openstack.org

본문으로 이동

​

 MySQL with Galera

Rather than starting with a vanilla version of MySQL, and then adding Galera, you will want to install a version of MySQL patched for wsrep (Write Set REPlication) from https://launchpad.net/codership-mysql. The wsrep API is suitable for configuring MySQL High Availability in OpenStack because it supports synchronous replication.

Note that the installation requirements call for careful attention. Read the guide https://launchpadlibrarian.net/66669857/README-wsrep to ensure you follow all the required steps.

And for any additional information about Galera, you can access this guide: http://galeracluster.com/documentation-webpages/gettingstarted.html

Installing Galera through a MySQL version patched for wsrep:

1. Setup the repository for Ubuntu 14.04 "trusty" (most recent). Install the software properties, the key and the repository.
2. # apt-get install software-properties-common
3. # apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb94

   # add-apt-repository 'deb http://ams2.mirrors.digitalocean.com/mariadb/repo/5.5/ubuntu trusty main'

	Note
	You can change the mirror to one near you on: downloads.mariadb.org

1. Update your system and install the required packages:
2. # apt-get update

   # apt-get install mariadb-galera-server galera

      

	Warning
	If you have mariaDB already installed you need to re-apply all the permissions from the installation guide. It will purge all privileges!

1. Adjust the configuration:

   In the /etc/mysql/my.conf file, make the following changes:

1 2 3 4 5

query_cache_size=0 binlog_format=ROW default_storage_engine=innodb innodb_autoinc_lock_mode=2 innodb_doublewrite=1

1. Create the /etc/mysql/conf.d/wsrep.cnf file.

   Paste the following lines in this file:

1 2 3 4 5 6 7 8

[mysqld] wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="Openstack" wsrep_sst_auth=wsrep_sst:wspass wsrep_cluster_address="gcomm://PRIMARY_NODE_IP,SECONDARY_NODE_IP" wsrep_sst_method=rsync wsrep_node_address="PRIMARY_NODE_IP" wsrep_node_name="NODE_NAME"

Replace PRIMARY_NODE_IP and SECONDARY_NODE_IP with the IP addresses of your primary and secondary servers.

Replace PRIMARY_NODE_IP with the hostname of the server. This is set for logging.

Copy this file to all other databases servers and change the value of wsrep_cluster_address and wsrep_node_nameaccordingly.

1. Start mysql as root and execute the following queries:

   mysql> SET wsrep_on=OFF; GRANT ALL ON *.* TO wsrep_sst@'%' IDENTIFIED BY 'wspass';

   Remove user accounts with empty user names because they cause problems:

   mysql> SET wsrep_on=OFF; DELETE FROM mysql.user WHERE user='';

2. Check that the nodes can access each other through the firewall. Depending on your environment, this might mean adjusting iptables, as in:
3. # iptables --insert RH-Firewall-1-INPUT 1 --proto tcp \
4. --source <my IP>/24 --destination <my IP>/32 --dport 3306 \
5. -j ACCEPT
6. # iptables --insert RH-Firewall-1-INPUT 1 --proto tcp \
7. --source <my IP>/24 --destination <my IP>/32 --dport 4567 \

   -j ACCEPT

   This might also mean configuring any NAT firewall between nodes to allow direct connections. You might need to disable SELinux, or configure it to allow mysqld to listen to sockets at unprivileged ports.

For the next step create a back-up file of the debian.cnf file in /etc/mysql on all database servers. Should something go wrong just copy the backup file back.

# cp debian.cnf debian.cnf.old

Make sure you have SSH root access on the other servers. From the primary database server, copy the debian.cnf to all other servers.

# scp /etc/mysql/debian.cnf root@IP-address:/etc/mysql

After the copy make sure that all files are the same, you can do this by using the following command:

# md5sum debian.cnf

From the debian.cnf get the database password:

# cat /etc/mysql/debian.cnf

The result will look like this:

[client]

host = localhost

user = debian-sys-maint

password = FiKiOY1Lw8Sq46If

socket = /var/run/mysqld/mysqld.sock

[mysql_upgrade]

host = localhost

user = debian-sys-maint

password = FiKiOY1Lw8Sq46If

socket = /var/run/mysqld/mysqld.sock

basedir = /usr

Run the following query on every server except the primary node. This will make sure that you can restart the database again. Do not forget to add the password from the debian.cnf.

mysql> GRANT SHUTDOWN ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<debian.cnf password>';

mysql> GRANT SELECT ON `mysql`.`user` TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<debian.cnf password>';

Stop all the mysql servers and start the first server with the following command:

# service mysql start --wsrep-new-cluster

All other nodes can now be started using:

# service mysql start

Verify the wsrep replication by logging in as root under mysql and running the following command:

mysql> SHOW STATUS LIKE 'wsrep%';

+------------------------------+--------------------------------------+

| Variable_name | Value |

+------------------------------+--------------------------------------+

| wsrep_local_state_uuid | d6a51a3a-b378-11e4-924b-23b6ec126a13 |

| wsrep_protocol_version | 5 |

| wsrep_last_committed | 202 |

| wsrep_replicated | 201 |

| wsrep_replicated_bytes | 89579 |

| wsrep_repl_keys | 865 |

| wsrep_repl_keys_bytes | 11543 |

| wsrep_repl_data_bytes | 65172 |

| wsrep_repl_other_bytes | 0 |

| wsrep_received | 8 |

| wsrep_received_bytes | 853 |

| wsrep_local_commits | 201 |

| wsrep_local_cert_failures | 0 |

| wsrep_local_replays | 0 |

| wsrep_local_send_queue | 0 |

| wsrep_local_send_queue_avg | 0.000000 |

| wsrep_local_recv_queue | 0 |

| wsrep_local_recv_queue_avg | 0.000000 |

| wsrep_local_cached_downto | 1 |

| 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 | 1.029703 |

| wsrep_apply_oooe | 0.024752 |

| wsrep_apply_oool | 0.000000 |

| wsrep_apply_window | 1.024752 |

| 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 | 18 |

| wsrep_causal_reads | 0 |

| wsrep_cert_interval | 0.024752 |

| wsrep_incoming_addresses | <first IP>:3306,<second IP>:3306 |

| wsrep_cluster_conf_id | 2 |

| wsrep_cluster_size | 2 |

| wsrep_cluster_state_uuid | d6a51a3a-b378-11e4-924b-23b6ec126a13 |

| wsrep_cluster_status | Primary |

| wsrep_connected | ON |

| wsrep_local_bf_aborts | 0 |

| wsrep_local_index | 1 |

| wsrep_provider_name | Galera |

| wsrep_provider_vendor | Codership Oy <info@codership.com> |

| wsrep_provider_version | 25.3.5-wheezy(rXXXX) |

| wsrep_ready | ON |

| wsrep_thread_count | 2 |

+------------------------------+--------------------------------------+