个人感觉MariaDB Galera做跨机房多主还是很方便的，比直接用MySQL做主从好。跟其他集群一样，为了避免节点出现脑裂而破坏数据，建议Galera集群最低添加3个节点。Galera 集群在高并发的情况下，多主同时写入时可能会发生事务冲突，此时只有一个事务请求会成功，其他的全部失败。可以在写入/更新失败时，自动重试一次，再返回结果。节点中每个节点的地位是平等的，没有主次，向任何一个节点读写效果都是一样的。实际可以配合VIP/LVS或HA使用，实现高可用性。如果集群中的机器全部重启，如机房断电，第一台启动的服务器必须以空地址启动。
Galera Cluster是一套基于同步复制的多主MySQL集群解决方案，使用简单，没有单点故障，可用性高，能很好保证业务不断增长时我们数据的安全和随时的扩展。
Galera集群的复制功能基于Galeralibrary实现,为了让MySQL与Galera library通讯，特别针对MySQL开发了wsrep API。
Galera插件保证集群同步数据，保持数据的一致性，靠的就是可认证的复制，工作原理如下图：
主要功能:
- 同步复制
- 真正的multi-master，即所有节点可以同时读写数据库
- 自动的节点成员控制，失效节点自动被清除
- 新节点加入数据自动复制
- 真正的并行复制，行级
- 用户可以直接连接集群，使用感受上与MySQL完全一致
优势:
- 因为是多主，所以不存在Slavelag(延迟)
- 不存在丢失事务的情况
- 同时具有读和写的扩展能力
- 更小的客户端延迟
- 节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的
一、下面说说编译安装MariaDB Galera 多主集群
下文中，如果命令前面没有PS1就是代表3个节点都需要执行相同的动作，如果有就表示在指定的节点上执行动作
1、Node1 Node2 Node3节点上安装ntpdate设置定时时间同步和ssh互相
yum install ntpdate -y
echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1" >> /var/spool/cron/root
/usr/sbin/ntpdate pool.ntp.org
cat >>/etc/hosts<< EOF
51.254.102.19 vps197119.ovh.net vps197119 node1
51.254.102.190 vps197120.ovh.net vps197120 node2
51.254.102.191 vps197121.ovh.net vps197121 node3
EOF
###下面是生产密钥
ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''
###下面是在每个节点上执行，让本机的公钥放到其他节点上
awk '{if ($0!~/'"$(hostname)"'|localhost/)print $NF}' /etc/hosts |xargs -i ssh-copy-id -i ~/.ssh/id_rsa.pub root@{}
2、安装基础的编译环境
rpm -ivh http://mirrors.aliyun.com/epel/epel-release-latest-6.noarch.rpm rpm --import http://mirrors.dwhd.org/epel/RPM-GPG-KEY-EPEL-6 rpm -ivh http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm rpm --import http://mirrors.dwhd.org/repoforge/RPM-GPG-KEY.dag.txt yum clean all && yum makecache yum groupinstall "Development tools" "Server Platform Development" -y yum install libxml2-devel lz4 lz4-devel libpcap nmap lsof socat -y
3、Node1上编译安装MariaDB Galera
###下载cmake源码包 [[email protected] ~]# wget http://cmake.org/files/v3.3/cmake-3.3.2.tar.gz ###下载mariadb-galera源码包 [[email protected] ~]# wget http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/source/mariadb-galera-10.0.21.tar.gz ###下载kytea源码包 [[email protected] ~]# wget http://www.phontron.com/kytea/download/kytea-0.4.7.tar.gz ###将cmake、mariadb-galera、kytea源码包传到其他节点上 [[email protected] ~]# awk '{if ($0!~/'"$(hostname)"'|localhost/)print $NF}' /etc/hosts |xargs -i scp cmake-3.3.2.tar.gz mariadb-galera-10.0.21.tar.gz kytea-0.4.7.tar.gz root@{}:/root
###开始编译cmake [[email protected] ~]# tar xf cmake-3.3.2.tar.gz [[email protected] ~]# cd cmake-3.3.2/ [[email protected] ~/cmake-3.3.2]# ./bootstrap [[email protected] ~/cmake-3.3.2]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ../ && which cmake ###开始编译kytea [[email protected] ~]# tar xf kytea-0.4.7.tar.gz [[email protected] ~]# cd kytea-0.4.7/ [[email protected] ~/kytea-0.4.7]# ./configure [[email protected] ~/kytea-0.4.7]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###安装Galera [[email protected] ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB [[email protected] ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm ###开始编译mariadb-galera [[email protected] ~]# findUidGid() { for i in `seq 400 500`;do if [ -z "$(awk -F: '{print$3,$4}' /etc/passwd | grep "$i")" -a -z "$(awk -F: '{print$3}' /etc/group | grep "$i")" ]; then ugidNo=$i; break; fi; done; groupadd -g $ugidNo $1 && useradd -M -u $ugidNo -g $ugidNo -s /sbin/nologin $1; } [[email protected] ~]# findUidGid mysql [[email protected] ~]# mkdir -p /data/mariadb-galera-10.0.21 [[email protected] ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21 [[email protected] ~]# tar xf mariadb-galera-10.0.21.tar.gz [[email protected] ~]# cd mariadb-10.0.21/ [[email protected] ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \ -DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \ -DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \ -DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \ -DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \ -DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1 ###这两项很重要不能少 [[email protected] ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. [[email protected] ~]#
4、Node2上编译安装MariaDB Galera
###编译安装cmake [[email protected] ~]# tar xf cmake-3.3.2.tar.gz [[email protected] ~]# cd cmake-3.3.2/ [[email protected] ~/cmake-3.3.2]# ./bootstrap [[email protected] ~/cmake-3.3.2]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ../ ###编译安装kytea [[email protected] ~]# tar xf kytea-0.4.7.tar.gz [[email protected] ~]# cd kytea-0.4.7/ [[email protected] ~/kytea-0.4.7]# ./configure && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###安装Galera [[email protected] ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB [[email protected] ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm ###编译安装MariaDB Galera [[email protected] ~]# mkdir -p /data/mariadb-galera-10.0.21 [[email protected] ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21 [[email protected] ~]# tar xf mariadb-galera-10.0.21.tar.gz [[email protected] ~]# cd mariadb-10.0.21/ [[email protected] ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \ -DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \ -DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \ -DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \ -DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \ -DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1 [[email protected] ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. [[email protected] ~]#
5、Node3上编译安装MariaDB Galera
###编译安装cmake [[email protected] ~]# tar xf cmake-3.3.2.tar.gz [[email protected] ~]# cd cmake-3.3.2/ [[email protected] ~/cmake-3.3.2]# ./bootstrap && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###编译安装kytea [[email protected] ~]# tar xf kytea-0.4.7.tar.gz [[email protected] ~]# cd kytea-0.4.7/ [[email protected] ~/kytea-0.4.7]# ./configure && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###安装Galera [[email protected] ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB [[email protected] ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm ###编译安装MariaDB Galera [[email protected] ~]# mkdir -p /data/mariadb-galera-10.0.21 [[email protected] ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21 [[email protected] ~]# tar xf mariadb-galera-10.0.21.tar.gz [[email protected] ~]# cd mariadb-10.0.21/ [[email protected] ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \ -DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \ -DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \ -DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \ -DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \ -DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1 [[email protected] ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. [[email protected] ~]#
6、配置所有节点上的启动脚本、环境变量
cd /usr/local/mariadb/
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
\cp /usr/local/mariadb/support-files/my-large.cnf /etc/my.cnf
sed -i '/query_cache_size/a datadir = /data/mariadb-galera-10.0.21/' /etc/my.cnf
echo "export PATH=/usr/local/mariadb/bin:\$PATH" > /etc/profile.d/mariadb_galera_10.0.21.sh
source /etc/profile.d/mariadb_galera_10.0.21.sh
sed -i "$(awk '$1=="MANPATH"{i=NR}END{print i}' /etc/man.config)a \MANPATH\tMANPATH /usr/local/mariadb/man" /etc/man.config
7、配置Node1节点
[roo[email protected] /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/ [[email protected] /usr/local/mariadb]# cd && service mysqld start --wsrep-new-cluster #第一个节点的启动参数需要加上--wsrep-new-cluster Starting MySQL.. SUCCESS! [[email protected] ~]# ss -tnl | grep :3306 LISTEN 0 128 :::3306 :::* [[email protected] ~]# mysql -uroot -p <<< "USE mysql; update user set password=PASSWORD('lookback') WHERE USER='root'; DELETE FROM user WHERE User=''; GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION; FLUSH PRIVILEGES; SELECT USER,PASSWORD,HOST FROM user;" Enter password: ####注意这里密码是空，直接回车就好了 USER PASSWORD HOST root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C localhost root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C vps197119.ovh.net root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C 127.0.0.1 root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C ::1 cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C % [[email protected] ~]#
[[email protected] ~]# service mysqld stop Shutting down MySQL... SUCCESS! [[email protected] ~]# chkconfig mysqld on [[email protected] ~]# sed -i '/binlog_format/d' /etc/my.cnf [[email protected] ~]# sed -i '/log_bin/d' /etc/my.cnf [[email protected] ~]# sed -i '/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\ default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\ wsrep_cluster_address = "gcomm://"\nwsrep_cluster_name = LegionMariadbGaleraCluster1\nwsrep_node_address = node1\ wsrep_sst_method = rsync\nwsrep_sst_auth = cluster:lookback' /etc/my.cnf [[email protected] ~]# service mysqld start Starting MySQL.. SUCCESS! [[email protected] ~]# ss -tnl | grep -E ':(3306|4567)' LISTEN 0 128 :::3306 :::* LISTEN 0 128 *:4567 *:*
如果开启了防火墙请设置放行规则
###我测试时候的规则，参见高亮行 [[email protected] ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p udp -m udp --dport 4567 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT [[email protected] ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p udp -m udp --dport 4567 -j ACCEPT [[email protected] ~]# service iptables save [[email protected] ~]# iptables -t filter -L INPUT -n --line-numbers Chain INPUT (policy DROP) num target prot opt source destination 1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 2 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:80 5 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:21 6 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpts:20000:30000 7 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:443 8 ACCEPT tcp -- 158.69.87.84 0.0.0.0/0 state NEW tcp dpt:873 9 ACCEPT tcp -- 167.114.230.243 0.0.0.0/0 state NEW tcp dpt:873 10 ACCEPT tcp -- 195.154.167.106 0.0.0.0/0 state NEW tcp dpt:873 15 ACCEPT tcp -- 158.69.87.84 0.0.0.0/0 state NEW tcp dpt:3306 16 ACCEPT tcp -- 158.69.87.84 0.0.0.0/0 state NEW tcp dpt:4444 17 ACCEPT tcp -- 158.69.87.84 0.0.0.0/0 state NEW tcp dpt:4567 18 ACCEPT udp -- 158.69.87.84 0.0.0.0/0 udp dpt:4567 19 ACCEPT tcp -- 167.114.230.243 0.0.0.0/0 state NEW tcp dpt:3306 20 ACCEPT tcp -- 167.114.230.243 0.0.0.0/0 state NEW tcp dpt:4444 21 ACCEPT tcp -- 167.114.230.243 0.0.0.0/0 state NEW tcp dpt:4567 22 ACCEPT udp -- 167.114.230.243 0.0.0.0/0 udp dpt:4567 23 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 limit: avg 100/sec burst 100 24 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 limit: avg 1/sec burst 10 25 syn-flood tcp -- 0.0.0.0/0 0.0.0.0/0 tcp flags:0x17/0x02 26 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited 27 ACCEPT tcp -- 158.69.87.84 0.0.0.0/0 state NEW tcp dpt:873 [[email protected] ~]#
8、配置Node2节点
[[email protected] /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/ [[email protected] /usr/local/mariadb]# cd && service mysqld start Starting MySQL.. SUCCESS! [[email protected] ~]# mysql -uroot -p <<< "USE mysql; update user set password=PASSWORD('lookback') WHERE USER='root'; DELETE FROM user WHERE User=''; GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION; FLUSH PRIVILEGES; SELECT USER,PASSWORD,HOST FROM user;" [[email protected] ~]# service mysqld stop Shutting down MySQL.. SUCCESS! [[email protected] ~]# chkconfig mysqld on [[email protected] ~]# sed -i '/binlog_format/d' /etc/my.cnf [[email protected] ~]# sed -i '/log.bin/d' /etc/my.cnf [[email protected] ~]# sed -ri 's/^(server-id).*/\1 = 2/' /etc/my.cnf [[email protected] ~]# sed -i '/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\ default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\ wsrep_cluster_address = "gcomm://node1,node2,node3"\nwsrep_cluster_name = LegionMariadbGaleraCluster1\n\ wsrep_node_address = node2\nwsrep_sst_method = rsync\nwsrep_sst_auth = cluster:lookback' /etc/my.cnf [[email protected] ~]# service mysqld start Starting MySQL... SUCCESS! [[email protected] ~]# ss -tnl | grep -E ':(3306|4567)' LISTEN 0 128 :::3306 :::* LISTEN 0 128 *:4567 *:* [[email protected] ~]#
node2节点my.cnf参考
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M query_cache_size=0 log-bin=mysql-bin binlog_format=ROW expire_logs_days = 30 basedir = /usr/local/mariadb-galera datadir = /data/mariadb-galera/ log_error = /data/mariadb-galera/mysql-error.log default_storage_engine=innodb innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191" wsrep_cluster_name='LegionMariadbGaleraCluster1' wsrep_node_address='51.254.102.190' wsrep_node_name='node2' wsrep_sst_method=rsync wsrep_sst_auth=cluster:lookback thread_concurrency = 8 server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
9、配置Node3节点
[[email protected] /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/ [[email protected] /usr/local/mariadb]# cd && service mysqld start Starting MySQL.. SUCCESS! [[email protected] ~]# mysql -uroot -p <<< "USE mysql; > update user set password=PASSWORD('lookback') WHERE USER='root'; > DELETE FROM user WHERE User=''; > GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION; > FLUSH PRIVILEGES; > SELECT USER,PASSWORD,HOST FROM user;" Enter password: USER PASSWORD HOST root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C localhost root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C vps197121.ovh.net root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C 127.0.0.1 root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C ::1 cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C % [ro[email protected] ~]# service mysqld stop Shutting down MySQL... SUCCESS! [[email protected] ~]# chkconfig mysqld on [[email protected] ~]# sed -i '/binlog_format/d' /etc/my.cnf [[email protected] ~]# sed -i '/log.bin/d' /etc/my.cnf [[email protected] ~]# sed -ri 's/^(server-id).*/\1 = 3/' /etc/my.cnf [[email protected] ~]# sed -i '/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\ > default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\ > wsrep_cluster_address = "gcomm://node1,node2,node3"\nwsrep_cluster_name = LegionMariadbGaleraCluster1\n\ > wsrep_node_address = node3\nwsrep_sst_method = rsync\nwsrep_sst_auth = cluster:lookback' /etc/my.cnf [[email protected] ~]# service mysqld start Starting MySQL..SST in progress, setting sleep higher.. SUCCESS! [[email protected] ~]# ss -tnl | grep -E ':(3306|4567)' LISTEN 0 128 :::3306 :::* LISTEN 0 128 *:4567 *:* [[email protected] ~]#
10、修改Node1节点配置
[[email protected] ~]# sed -ri 's/^(wsrep_cluster_address).*/\1 = "gcomm:\/\/node1,node2,node3"/' /etc/my.cnf [[email protected] ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL... SUCCESS! [[email protected] ~]#
11、测试集群多主同步
[[email protected] ~]# mysql -uroot -plookback <<< 'USE mysql; CREATE DATABASE LegionTestDataBase; USE LegionTestDataBase; CREATE TABLE equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id)); INSERT INTO equipment (type, quant, color) VALUES ("slide", 2, "blue"); FLUSH PRIVILEGES; SELECT * FROM LegionTestDataBase.equipment;' id type quant color 2 slide 2 blue [[email protected] ~]#
[[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue [[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; INSERT INTO equipment (type, quant, color) VALUES ('swing', 10, 'yellow'); SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue 4 swing 10 yellow [[email protected] ~]#
[[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue 4 swing 10 yellow [[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; INSERT INTO equipment (type, quant, color) VALUES ('Legion', 20, 'red'); SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue 4 swing 10 yellow 6 Legion 20 red [[email protected] ~]#
[[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; > SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue 4 swing 10 yellow 6 Legion 20 red [[email protected] ~]#
经过上面的一轮测试，多节点的全主MariaDB集群是OK的。。
二、使用yum来配置MariaDB Galera的多主集群
下文中，如果命令前面没有PS1就是代表3个节点都需要执行相同的动作，如果有就表示在指定的节点上执行动作
1、Node1 Node2 Node3节点上安装ntpdate设置定时时间同步和ssh互相
yum install ntpdate -y
echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1" >> /var/spool/cron/root
/usr/sbin/ntpdate pool.ntp.org
cat >>/etc/hosts<< EOF
51.254.102.19 vps197119.ovh.net vps197119 node1
51.254.102.190 vps197120.ovh.net vps197120 node2
51.254.102.191 vps197121.ovh.net vps197121 node3
EOF
###下面是生产密钥
ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''
###下面是在每个节点上执行，让本机的公钥放到其他节点上
awk '{if ($0!~/'"$(hostname)"'|localhost/)print $NF}' /etc/hosts |xargs -i ssh-copy-id -i ~/.ssh/id_rsa.pub root@{}
2、安装MariaDB 和 Galera
yum install MariaDB-Galera-server MariaDB-client galera -y
3、配置MariaDB
[[email protected] ~]# service mysql start Starting MySQL.. SUCCESS! [[email protected] ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [[email protected] ~]#
[[email protected] ~]# service mysql start Starting MySQL.. SUCCESS! [[email protected] ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [[email protected] ~]#
[[email protected] ~]# service mysql start Starting MySQL.. SUCCESS! [[email protected] ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [[email protected] ~]#
4、停止MariaDB和设置开机启动
mysql -u root -plookback <<< "USE mysql; ###这里的lookback是数据库的root密码 GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION; ###这里的lookback是cluster的密码 FLUSH PRIVILEGES;" ###设置完毕来验证下 mysql -u root -plookback <<< "USE mysql; SELECT USER,PASSWORD,HOST FROM user;" USER PASSWORD HOST root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C localhost root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C 127.0.0.1 root *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C ::1 cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C % ###验证通过之后停止MariaDB service mysql stop ###设置MariaDB开机启动 chkconfig mysql on
5、修改3个节点的配置信息
####node1#### [[email protected] ~]# sed -ne '/\[mariadb-10.0\]/,//p' /etc/my.cnf.d/server.cnf [mariadb-10.0] query_cache_size=0 binlog_format=ROW default_storage_engine=innodb innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so ###这里需要注意，如果集群是第一次启动请用当前配置，等其他节点启动起来后将下面两行的注释对换重启本节点就OK wsrep_cluster_address="gcomm://" #wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191" wsrep_cluster_name='cluster1' wsrep_node_address='51.254.102.19' wsrep_node_name='node1' wsrep_sst_method=rsync wsrep_sst_auth=cluster:lookback ####node2#### [[email protected] ~]# sed -ne '/\[mariadb-10.0\]/,//p' /etc/my.cnf.d/server.cnf [mariadb-10.0] query_cache_size=0 binlog_format=ROW default_storage_engine=innodb innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191" wsrep_cluster_name='cluster1' wsrep_node_address='51.254.102.190' wsrep_node_name='node2' wsrep_sst_method=rsync wsrep_sst_auth=cluster:lookback ####node3#### [[email protected] ~]# sed -ne '/\[mariadb-10.0\]/,//p' /etc/my.cnf.d/server.cnf [mariadb-10.0] query_cache_size=0 binlog_format=ROW default_storage_engine=innodb innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191" wsrep_cluster_name='cluster1' wsrep_node_address='51.254.102.191' wsrep_node_name='node3' wsrep_sst_method=rsync wsrep_sst_auth=cluster:lookback
6、测试集群
[[email protected] ~]# mysql -uroot -plookback Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.0.21-MariaDB-wsrep MariaDB Server, wsrep_25.10.r4144 Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> SHOW STATUS LIKE 'wsrep%'; +------------------------------+------------------------------------------------------------+ | Variable_name | Value | +------------------------------+------------------------------------------------------------+ | wsrep_local_state_uuid | 7173605a-6299-11e5-b7a2-bb91b9fd6d7a | | wsrep_protocol_version | 7 | | wsrep_last_committed | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 2 | | wsrep_received_bytes | 284 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 1 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_local_cached_downto | 18446744073709551615 | | 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 | 0.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 0 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_incoming_addresses | 51.254.102.190:3306,51.254.102.191:3306,51.254.102.19:3306 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | bbbec594-6299-11e5-bd04-065febe4bd92 | | wsrep_cluster_conf_id | 5 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 7173605a-6299-11e5-b7a2-bb91b9fd6d7a | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <[email protected]> | | wsrep_provider_version | 25.3.9(r3385) | | wsrep_ready | ON | | wsrep_thread_count | 2 | +------------------------------+------------------------------------------------------------+ 57 rows in set (0.00 sec) MariaDB [mysql]> CREATE DATABASE LegionTestDataBase; Query OK, 1 row affected (0.01 sec) MariaDB [mysql]> USE LegionTestDataBase; Database changed MariaDB [LegionTestDataBase]> CREATE TABLE equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id)); Query OK, 0 rows affected (0.04 sec) MariaDB [LegionTestDataBase]> INSERT INTO equipment (type, quant, color) VALUES ("slide", 2, "blue"); Query OK, 1 row affected (0.01 sec) MariaDB [LegionTestDataBase]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) MariaDB [LegionTestDataBase]> \q Bye [[email protected] ~]#
[[email protected] ~]# mysql -uroot -plookback <<< "SHOW STATUS LIKE 'wsrep%'\g" Variable_name Value wsrep_local_state_uuid 7173605a-6299-11e5-b7a2-bb91b9fd6d7a wsrep_protocol_version 7 wsrep_last_committed 4 wsrep_replicated 0 wsrep_replicated_bytes 0 wsrep_repl_keys 0 wsrep_repl_keys_bytes 0 wsrep_repl_data_bytes 0 wsrep_repl_other_bytes 0 wsrep_received 10 wsrep_received_bytes 2718 wsrep_local_commits 0 wsrep_local_cert_failures 0 wsrep_local_replays 0 wsrep_local_send_queue 0 wsrep_local_send_queue_max 1 wsrep_local_send_queue_min 0 wsrep_local_send_queue_avg 0.000000 wsrep_local_recv_queue 0 wsrep_local_recv_queue_max 1 wsrep_local_recv_queue_min 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.000000 wsrep_apply_oooe 0.000000 wsrep_apply_oool 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 4 wsrep_causal_reads 0 wsrep_cert_interval 0.000000 wsrep_incoming_addresses 51.254.102.190:3306,51.254.102.191:3306,51.254.102.19:3306 wsrep_evs_delayed wsrep_evs_evict_list wsrep_evs_repl_latency 0/0/0/0/0 wsrep_evs_state OPERATIONAL wsrep_gcomm_uuid 773b733f-6299-11e5-ba5d-7b3058f177bc wsrep_cluster_conf_id 5 wsrep_cluster_size 3 wsrep_cluster_state_uuid 7173605a-6299-11e5-b7a2-bb91b9fd6d7a wsrep_cluster_status Primary wsrep_connected ON wsrep_local_bf_aborts 0 wsrep_local_index 0 wsrep_provider_name Galera wsrep_provider_vendor Codership Oy <[email protected]> wsrep_provider_version 25.3.9(r3385) wsrep_ready ON wsrep_thread_count 2 [[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 3 slide 2 blue [[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; INSERT INTO equipment (type, quant, color) VALUES ('swing', 10, 'yellow');" [[email protected] ~]#
[[email protected] ~]# mysql -uroot -plookback <<< "SHOW STATUS LIKE 'wsrep%'\g" Variable_name Value wsrep_local_state_uuid 7173605a-6299-11e5-b7a2-bb91b9fd6d7a wsrep_protocol_version 7 wsrep_last_committed 5 wsrep_replicated 0 wsrep_replicated_bytes 0 wsrep_repl_keys 0 wsrep_repl_keys_bytes 0 wsrep_repl_data_bytes 0 wsrep_repl_other_bytes 0 wsrep_received 10 wsrep_received_bytes 2768 wsrep_local_commits 0 wsrep_local_cert_failures 0 wsrep_local_replays 0 wsrep_local_send_queue 0 wsrep_local_send_queue_max 1 wsrep_local_send_queue_min 0 wsrep_local_send_queue_avg 0.000000 wsrep_local_recv_queue 0 wsrep_local_recv_queue_max 1 wsrep_local_recv_queue_min 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.400000 wsrep_apply_oooe 0.000000 wsrep_apply_oool 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 5 wsrep_causal_reads 0 wsrep_cert_interval 0.000000 wsrep_incoming_addresses 51.254.102.190:3306,51.254.102.191:3306,51.254.102.19:3306 wsrep_evs_delayed wsrep_evs_evict_list wsrep_evs_repl_latency 0/0/0/0/0 wsrep_evs_state OPERATIONAL wsrep_gcomm_uuid 7fd4ab67-6299-11e5-88fb-3fd93ceed5f7 wsrep_cluster_conf_id 5 wsrep_cluster_size 3 wsrep_cluster_state_uuid 7173605a-6299-11e5-b7a2-bb91b9fd6d7a 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 <[email protected]> wsrep_provider_version 25.3.9(r3385) wsrep_ready ON wsrep_thread_count 2 [[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 3 slide 2 blue 4 swing 10 yellow [[email protected] ~]#
[[email protected] ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 3 slide 2 blue 4 swing 10 yellow [[email protected] ~]#
====END========
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏