Galera Cluster实战部署

最新推荐文章于 2024-08-05 15:34:36 发布

track sun

最新推荐文章于 2024-08-05 15:34:36 发布

阅读量165

点赞数

文章标签：运维网络 git

原文链接：http://www.cnblogs.com/qianjingchen/articles/9543077.html

版权

　　#官方文档：http://galeracluster.com/products/

　　Galera是一个MySQL(也支持MariaDB，Percona)的同步多主集群软件，目前只支持InnoDB引擎。Galera集群的复制功能基于Galera library实现,为了让MySQL与Galera library通讯，特别针对MySQL开发了wsrep API。

1、准备好rpm包上传至/home/tools目录下

        galera-3-25.3.22-2.el6.x86_64.rpm
        mysql-wsrep-5.6-5.6.39-25.22.el6.x86_64.rpm
        mysql-wsrep-client-5.6-5.6.39-25.22.el6.x86_64.rpm
        mysql-wsrep-devel-5.6-5.6.39-25.22.el6.x86_64.rpm
        mysql-wsrep-libs-compat-5.6-5.6.39-25.22.el6.x86_64.rpm
        mysql-wsrep-server-5.6-5.6.39-25.22.el6.x86_64.rpm
        mysql-wsrep-shared-5.6-5.6.39-25.22.el6.x86_64.rpm
        mysql-wsrep-test-5.6-5.6.39-25.22.el6.x86_64.rpm
百度网盘链接地址：https://pan.baidu.com/s/158Dfxm82hnIK70zflbARlw

2、安装依赖和socat

1.  wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo
2.  yum install libaio gcc gcc-c++ boost-devel scons check-devel openssl-devel
3.  yum -y install perl perl-devel  libaio-devel perl-Time-HiRes perl-DBD-MySQL #安装xtrabackup所需的依赖
4.  wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm #安装Xtrabackup工具
    yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
5.  yum -y install socat

3、开始安装Galera Cluster集群

yum -y install galera-3-25.3.22-2.el6.x86_64.rpm
yum -y install mysql-wsrep-5.6-5.6.39-25.22.el6.x86_64.rpm
yum -y install mysql-wsrep-client-5.6-5.6.39-25.22.el6.x86_64.rpm
yum -y install mysql-wsrep-devel-5.6-5.6.39-25.22.el6.x86_64.rpm
yum -y install mysql-wsrep-libs-compat-5.6-5.6.39-25.22.el6.x86_64.rpm
yum -y install mysql-wsrep-server-5.6-5.6.39-25.22.el6.x86_64.rpm
yum -y install mysql-wsrep-shared-5.6-5.6.39-25.22.el6.x86_64.rpm
yum -y install mysql-wsrep-test-5.6-5.6.39-25.22.el6.x86_64.rpm
安装完成：rpm -qa |grep mysql-wsrep #检查一下

4、启动数据库，修改root用户登录密码

1.  /etc/init.d/mysql start --skip-grant-tables  
2.  use mysql;
    update user set password=PASSWORD("123456") where USER="root";
    flush privileges;
3.  service mysql restart
4.  mysql> show databases;
    ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
    mysql> SET PASSWORD=PASSWORD("123456");
    Query OK, 0 rows affected (0.00 sec)
5.  GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.10.16.%' IDENTIFIED BY 'didi123' WITH GRANT OPTION;  #授权用户和连接主机

5、拷贝my.cnf

cp -r /usr/share/doc/mysql-wsrep-server-5.6-5.6.39/wsrep.cnf /etc/my.cnf

6、修改默认配置文件my.cnf

           [mysqld]
            server-id=118
            binlog_format=ROW
            default-storage-engine=innodb
            innodb_autoinc_lock_mode=2
            innodb_locks_unsafe_for_binlog=1
            query_cache_size=0
            query_cache_type=0
            bind-address=10.10.16.118
            wsrep_provider="/usr/lib64/galera-3/libgalera_smm.so"
            wsrep_cluster_name="my_wsrep_cluster"
            wsrep_cluster_address="gcomm://10.10.16.118,10.10.16.119,10.10.16.151"
            wsrep_node_name="node1"
            wsrep_node_address="10.10.16.118"
            wsrep_slave_threads=1
            wsrep_certify_nonPK=1
            wsrep_max_ws_rows=131072
            wsrep_max_ws_size=1073741824
            wsrep_debug=0
            wsrep_convert_LOCK_to_trx=0
            wsrep_retry_autocommit=1
            wsrep_auto_increment_control=1
            wsrep_drupal_282555_workaround=0
            wsrep_causal_reads=0
            wsrep_notify_cmd=
            wsrep_sst_method=rsync
            wsrep_sst_auth=root:123456

7、启动第一个节点

service mysql start --wsrep-new-cluster

8、修改第二、三个节点

拷贝配置文件到119和151主机
server-id=119 #按实际情况修改
wsrep_node_name="node2" #区分节点
wsrep_node_address="10.10.16.119" #修改该主机的ip

9、启动第二、三个节点

/etc/init.d/mysql start

10、安装负载均衡插件glb

1.  git clone https://github.com/codership/glb
2.  cd glb
     ./bootstrap.sh  #启动glb
     #报错：which: no autoreconf in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
     #解决：yum install autoconf automake libtool
         ./configure
         make && make install
3.   cp files/glbd.sh /etc/init.d/glb
      cp files/glbd.cfg /etc/sysconfig/glbd
4.  vim /etc/sysconfig/glbd 
     LISTEN_ADDR="10.10.16.118:8010"
     CONTROL_ADDR="10.10.16.118:8011"
      DEFAULT_TARGETS="10.10.16.118:3306 10.10.16.119:3306 10.10.16.151:3306"
      OTHER_OPTIONS="--source"
5.  启动： /etc/init.d/glb start

11、补充：可使用pen插件代替glb

负载均衡引入
    pen引入：http://galeracluster.com/documentation-webpages/pen.html#using-pen
    #介绍：PEN是一个可伸缩、高可用性、强大的负载均衡中间件，基于TCP和UDP通信，可以使用它来平衡应用程序和Galera集群之间的连接。默认是以Round robin轮询的方式将所有请求引导到一个循环的ip列表中，根据服务器间的负载均衡性来实现调度
    #安装： yum install -y pen 
    #启动： pen -l pen.log -p pen.pid 10.10.16.118:3307 10.10.16.118:3306 10.10.16.119:3306 10.10.16.151:3306
    #查看日志：tailf pen.log

12、优化后的配置

[root@node1 ~]# cat /etc/my.cnf
[mysql]
# CLIENT #
port=3306
socket=/tmp/mysql.sock
user=root
password=123456

[mysqld]
server-id = 118
user      = mysql
socket    = /tmp/mysql.sock
skip-name-resolve
binlog_format=ROW
default-storage-engine=innodb
character-set-server=utf8
datadir=/home/sqldata/mysql

innodb_flush_log_at_trx_commit=0
#innodb_buffer_pool_size=122M
collation-server=utf8_general_ci
log_bin_trust_function_creators = 1
max_connections = 1000
max_connect_errors = 10000
open_files_limit = 65535
table_open_cache = 1024
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 128M
skip-external-locking
innodb_file_per_table = 1
innodb_strict_mode = 0      ##INSERT或UPDATE不会因为记录对于所选页面大小而言太大而失败
innodb_file_format = Barracuda  #解决error "Row size too large (> 8126)
innodb_open_files = 500
innodb_buffer_pool_size = 3G
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 512M  #ERROR 1118 (42000) at line 1852: Row size too large (> 8126)
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
interactive_timeout = 2880000
wait_timeout = 2880000

innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0

bind-address=10.10.16.118
wsrep_provider= /usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address="gcomm://10.10.16.118,10.10.16.119,10.10.16.151"
wsrep_node_address="10.10.16.118"
wsrep_node_name="node1"
wsrep_slave_threads=1
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_notify_cmd=
#wsrep_sst_method=xtrabackup
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:123456

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
general_log=1
general_log_file=/home/sqldata/mysql/log/mysql.log
slow_query_log=1
slow_query_log_file=/home/sqldata/mysql/log/slowquery.log
#skip-grant-tables