最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
CentOS 6.7上编译+YUM两种方法配置MariaDB Galera多主集群实现HA
时间：2016-01-22 编辑：简简单单 来源：一聚教程网
- 同步复制
- 真正的multi-master，即所有节点可以同时读写数据库
- 自动的节点成员控制，失效节点自动被清除
- 新节点加入数据自动复制
- 真正的并行复制，行级
- 用户可以直接连接集群，使用感受上与MySQL完全一致
- 因为是多主，所以不存在Slavelag(延迟)
- 不存在丢失事务的情况
- 同时具有读和写的扩展能力
- 更小的客户端延迟
- 节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的
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@{}
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
###下载cmake源码包 [root@vps197119 ~]# wget http://cmake.org/files/v3.3/cmake-3.3.2.tar.gz ###下载mariadb-galera源码包 [root@vps197119 ~]# wget http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/source/mariadb-galera-10.0.21.tar.gz ###下载kytea源码包 [root@vps197119 ~]# wget http://www.phontron.com/kytea/download/kytea-0.4.7.tar.gz ###将cmake、mariadb-galera、kytea源码包传到其他节点上 [root@vps197119 ~]# 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 [root@vps197119 ~]# tar xf cmake-3.3.2.tar.gz [root@vps197119 ~]# cd cmake-3.3.2/ [root@vps197119 ~/cmake-3.3.2]# ./bootstrap [root@vps197119 ~/cmake-3.3.2]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ../ && which cmake ###开始编译kytea [root@vps197119 ~]# tar xf kytea-0.4.7.tar.gz [root@vps197119 ~]# cd kytea-0.4.7/ [root@vps197119 ~/kytea-0.4.7]# ./configure [root@vps197119 ~/kytea-0.4.7]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###安装Galera [root@vps197119 ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB [root@vps197119 ~]# 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 [root@vps197119 ~]# 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; } [root@vps197119 ~]# findUidGid mysql [root@vps197119 ~]# mkdir -p /data/mariadb-galera-10.0.21 [root@vps197119 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21 [root@vps197119 ~]# tar xf mariadb-galera-10.0.21.tar.gz [root@vps197119 ~]# cd mariadb-10.0.21/ [root@vps197119 ~/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 ###这两项很重要不能少 [root@vps197119 ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. [root@vps197119 ~]#
###编译安装cmake [root@vps197120 ~]# tar xf cmake-3.3.2.tar.gz [root@vps197120 ~]# cd cmake-3.3.2/ [root@vps197120 ~/cmake-3.3.2]# ./bootstrap [root@vps197120 ~/cmake-3.3.2]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ../ ###编译安装kytea [root@vps197120 ~]# tar xf kytea-0.4.7.tar.gz [root@vps197120 ~]# cd kytea-0.4.7/ [root@vps197120 ~/kytea-0.4.7]# ./configure && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###安装Galera [root@vps197120 ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB [root@vps197121 ~]# 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 [root@vps197120 ~]# mkdir -p /data/mariadb-galera-10.0.21 [root@vps197120 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21 [root@vps197120 ~]# tar xf mariadb-galera-10.0.21.tar.gz [root@vps197120 ~]# cd mariadb-10.0.21/ [root@vps197120 ~/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 [root@vps197120 ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. [root@vps197120 ~]#
###编译安装cmake [root@vps197121 ~]# tar xf cmake-3.3.2.tar.gz [root@vps197121 ~]# cd cmake-3.3.2/ [root@vps197121 ~/cmake-3.3.2]# ./bootstrap && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###编译安装kytea [root@vps197121 ~]# tar xf kytea-0.4.7.tar.gz [root@vps197121 ~]# cd kytea-0.4.7/ [root@vps197121 ~/kytea-0.4.7]# ./configure && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. ###安装Galera [root@vps197121 ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB [root@vps197121 ~]# 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 [root@vps197121 ~]# mkdir -p /data/mariadb-galera-10.0.21 [root@vps197121 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21 [root@vps197121 ~]# tar xf mariadb-galera-10.0.21.tar.gz [root@vps197121 ~]# cd mariadb-10.0.21/ [root@vps197121 ~/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 [root@vps197121 ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd .. [root@vps197121 ~]#
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
[root@vps197119 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/ [root@vps197119 /usr/local/mariadb]# cd && service mysqld start --wsrep-new-cluster #第一个节点的启动参数需要加上--wsrep-new-cluster Starting MySQL.. SUCCESS! [root@vps197119 ~]# ss -tnl | grep :3306 LISTEN 0 128 :::3306 :::* [root@vps197119 ~]# 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 % [root@vps197119 ~]#
[root@vps197119 ~]# service mysqld stop Shutting down MySQL... SUCCESS! [root@vps197119 ~]# chkconfig mysqld on [root@vps197119 ~]# sed -i '/binlog_format/d' /etc/my.cnf [root@vps197119 ~]# sed -i '/log_bin/d' /etc/my.cnf [root@vps197119 ~]# 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 [root@vps197119 ~]# service mysqld start Starting MySQL.. SUCCESS! [root@vps197119 ~]# ss -tnl | grep -E ':(3306|4567)' LISTEN 0 128 :::3306 :::* LISTEN 0 128 *:4567 *:*
###我测试时候的规则，参见高亮行 [root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p udp -m udp --dport 4567 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT [root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p udp -m udp --dport 4567 -j ACCEPT [root@vps197121 ~]# service iptables save [root@vps197121 ~]# 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 [root@vps197121 ~]#
[root@vps197120 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/ [root@vps197120 /usr/local/mariadb]# cd && service mysqld start Starting MySQL.. SUCCESS! [root@vps197120 ~]# 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;" [root@vps197120 ~]# service mysqld stop Shutting down MySQL.. SUCCESS! [root@vps197120 ~]# chkconfig mysqld on [root@vps197120 ~]# sed -i '/binlog_format/d' /etc/my.cnf [root@vps197120 ~]# sed -i '/log.bin/d' /etc/my.cnf [root@vps197120 ~]# sed -ri 's/^(server-id).*/\1 = 2/' /etc/my.cnf [root@vps197120 ~]# 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 [root@vps197120 ~]# service mysqld start Starting MySQL... SUCCESS! [root@vps197120 ~]# ss -tnl | grep -E ':(3306|4567)' LISTEN 0 128 :::3306 :::* LISTEN 0 128 *:4567 *:* [root@vps197120 ~]#
[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
[root@vps197121 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/ [root@vps197121 /usr/local/mariadb]# cd && service mysqld start Starting MySQL.. SUCCESS! [root@vps197121 ~]# 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 % [root@vps197121 ~]# service mysqld stop Shutting down MySQL... SUCCESS! [root@vps197121 ~]# chkconfig mysqld on [root@vps197121 ~]# sed -i '/binlog_format/d' /etc/my.cnf [root@vps197121 ~]# sed -i '/log.bin/d' /etc/my.cnf [root@vps197121 ~]# sed -ri 's/^(server-id).*/\1 = 3/' /etc/my.cnf [root@vps197121 ~]# 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 [root@vps197121 ~]# service mysqld start Starting MySQL..SST in progress, setting sleep higher.. SUCCESS! [root@vps197121 ~]# ss -tnl | grep -E ':(3306|4567)' LISTEN 0 128 :::3306 :::* LISTEN 0 128 *:4567 *:* [root@vps197121 ~]#
[root@vps197119 ~]# sed -ri 's/^(wsrep_cluster_address).*/\1 = "gcomm:\/\/node1,node2,node3"/' /etc/my.cnf [root@vps197119 ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL... SUCCESS! [root@vps197119 ~]#
[root@vps197119 ~]# 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 [root@vps197119 ~]#
[root@vps197120 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue [root@vps197120 ~]# 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 [root@vps197120 ~]#
[root@vps197121 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 2 slide 2 blue 4 swing 10 yellow [root@vps197121 ~]# 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 [root@vps197121 ~]#
[root@vps197119 ~]# 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 [root@vps197119 ~]#
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@{}
yum install MariaDB-Galera-server MariaDB-client galera -y
[root@vps197119 ~]# service mysql start Starting MySQL.. SUCCESS! [root@vps197119 ~]# 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! [root@vps197119 ~]#
[root@vps197120 ~]# service mysql start Starting MySQL.. SUCCESS! [root@vps197120 ~]# 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! [root@vps197120 ~]#
[root@vps197121 ~]# service mysql start Starting MySQL.. SUCCESS! [root@vps197121 ~]# 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! [root@vps197121 ~]#
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
####node1#### [root@vps197119 ~]# 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#### [root@vps197120 ~]# 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#### [root@vps197121 ~]# 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
[root@vps197119 ~]# 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
[root@vps197120 ~]# 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
[root@vps197121 ~]# 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
[root@vps197119 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;" id type quant color 3 slide 2 blue 4 swing 10 yellow [root@vps197119 ~]#
相关文章
- win11内核隔离和内存完整性介绍 10-31
- win10全屏缩放设置教程 10-31
- win10系统备份出错解决教程 10-31
- win10打开软件每次都要询问解决教程 10-31
- win10更新驱动后设备出现异常解决教程 10-31
- win10一直提示找到可能不需要的应用解决教程 10-31