CentOS6.9搭建Mariadb-Galera集群 · Linux笔记

[TOC] # 安装MariaDB MariaDB-Galera、MariaDB-client、MariaDB-common、MariaDB-compat、galera、lsof、rsync、perl、perl-DBI、boost-program-options： https://mirrors.aliyun.com/mariadb/yum/10.0-galera/centos6-amd64/rpms/MariaDB-Galera-10.0.38-centos6-x86_64-server.rpm https://mirrors.aliyun.com/mariadb/yum/10.0-galera/centos6-amd64/rpms/MariaDB-10.0.38-centos6-x86_64-client.rpm https://mirrors.aliyun.com/mariadb/yum/10.0-galera/centos6-amd64/rpms/MariaDB-10.0.38-centos6-x86_64-common.rpm https://mirrors.aliyun.com/mariadb/yum/10.0-galera/centos6-amd64/rpms/MariaDB-10.0.38-centos6-x86_64-compat.rpm https://mirrors.aliyun.com/mariadb/yum/10.0-galera/centos6-amd64/rpms/galera-25.3.25-1.rhel6.el6.x86_64.rpm https://mirrors.aliyun.com/centos/6/os/x86_64/Packages/lsof-4.82-5.el6.x86_64.rpm https://mirrors.aliyun.com/centos/6/os/x86_64/Packages/rsync-3.0.6-12.el6.x86_64.rpm https://mirrors.aliyun.com/centos/6/os/x86_64/Packages/perl-5.10.1-144.el6.x86_64.rpm https://mirrors.aliyun.com/centos/6/os/x86_64/Packages/perl-DBI-1.609-4.el6.x86_64.rpm https://mirrors.aliyun.com/centos/6/os/x86_64/Packages/boost-program-options-1.41.0-28.el6.x86_64.rpm **1.上传包到/root/mariadb目录下** **2.安装MariaDB** ``` yum install *.rpm -y ``` # 安装XtraBackup **3.安装Percona XtraBackup热备份工具** 下载地址 https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.6/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm 依赖包地址： ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm https://mirrors.aliyun.com/centos/6/os/x86_64/Packages/perl-DBD-MySQL-4.013-3.el6.x86_64.rpm 上传包到/root/xtrabackup目录下 ``` 安装：yum install -y *.rpm ``` 安装socat `yum install -y socat` # 安装jemalloc **4.安装jemalloc内存分配器** 下载地址 https://mirrors.aliyun.com/mariadb/yum/10.0-galera/centos6-amd64/rpms/jemalloc-3.6.0-1.el6.x86_64.rpm 上传包到/root/jemalloc目录下 ``` 安装：yum install -y *.rpm ``` 加载jemalloc，vi /etc/profile ``` 末尾添加一行 export LD_PRELOAD=/usr/lib64/libjemalloc.so.1 ``` **5.启动mysql服务** ``` service mysql start ``` ***** # 配置mariadb 下面我们开始配置MariaDB Galera Cluster，分别修改MariaDB Galera集群的每个节点上的/etc/my.cnf.d/server.cnf文件，具体每个节点的内容如下： ## node1节点 ### 配置 /etc/my.cnf.d/server.cnf文件内容： ``` [client] socket = /data/var/lock/mysql.sock default-character-set = utf8mb4 [mysqld_safe] socket = /data/var/lock/mysql.sock nice = 0 [mysql] prompt="MySQL [\d]> " no-auto-rehash default-character-set=utf8mb4 [mysqld] port = 13306 socket = /data/var/lock/mysql.sock datadir = /data/mariadb-galera/ pid-file = /data/var/run/mysql.pid user = ewhine bind-address = 0.0.0.0 server-id = 1 #innodb_print_all_deadlocks = 1 #innodb_status_output = 1 #innodb_status_output_locks = 1 init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci skip-name-resolve #skip-networking back_log = 300 max_connections = 2000 max_connect_errors = 6000 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 read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 256M thread_cache_size = 64 query_cache_type = 1 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = ROW expire_logs_days = 7 log_error = /data/var/log/mysql-error.log performance_schema = 0 skip-external-locking default_storage_engine = InnoDB innodb_file_per_table = 1 #每个表一个ibdata文件 innodb_file_format = Barracuda innodb_large_prefix innodb_open_files = 500 innodb_buffer_pool_size = 1024M 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 = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://mnode1,mnode2,mnode3,mnode4" wsrep_cluster_name='MariadbGaleraCluster1' wsrep_node_address='mnode1' wsrep_node_name='node1' #wsrep_sst_method=rsync wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=user_xxx:passwd_xxx thread_concurrency = 8 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 general-log=0 general_log_file=/data/var/log/general.log [mysqldump] quick quote-names max_allowed_packet = 500M default-character-set=utf8mb4 [myisamchk] key_buffer_size = 256M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M [mysqlhotcopy] interactive-timeout ``` \# 上面配置使用的是rsync方式同步数据，如果要使用xtrabackup方式（建议使用），需要设置： **wsrep\_sst\_auth=galera:123456** **wsrep\_sst\_method=xtrabackup-v2** #默认是rsync全量拷贝，但是需要在donor节点上执行全局读锁(flushtables with read lock)，建议采用xtrabackup热备份方式，只有在备份.frm表结构文件才会锁表 ### 创建应用用户以及mysql依赖目录 ``` [root@mysqlnode1 home]# useradd ewhine [root@mysqlnode1 /]# mkdir -p /data/var/lock/ [root@mysqlnode1 /]# mkdir -p /data/mariadb-galera/ [root@mysqlnode1 /]# mkdir -p /data/var/log [root@mysqlnode1 /]# mkdir -p /data/var/run/ [root@mysqlnode1 /]# chown ewhine:ewhine /data -R ``` ### 初始化Mariadb ``` mysql_install_db --defaults-file=/etc/my.cnf.d/server.cnf --user=ewhine ``` 如果不执行初始化，会报错： ``` 200409 10:46:20 [Note] InnoDB: 128 rollback segment(s) are active. 200409 10:46:20 [Note] InnoDB: Waiting for purge to start 200409 10:46:20 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 1600785 200409 10:46:20 [Note] Plugin 'FEEDBACK' is disabled. 200409 10:46:20 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 200409 10:46:20 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 200409 10:46:20 [Warning] Failed to create a socket for IPv6 '::': errno: 97. 200409 10:46:20 [Note] Server socket created on IP: '0.0.0.0'. 200409 10:46:20 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 200409 10:46:20 mysqld_safe mysqld from pid file /home/app/mysql//localhost.pid ended ``` 如果执行初始化报错： ``` Neither host 'localhost' nor 'localhost' could be looked up with '/usr/sbin/resolveip' Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option The latest information about mysql_install_db is available at https://mariadb.com/kb/en/installing-system-tables-mysql_install_db ``` 执行hostname查看主机名 ``` hostname ``` 如果不是localhost或者主机名，则执行"hostname 主机名"进行修改，永久修改，编辑/etc/sysconfig/network，修改"HOSTNAME" ``` [root@localhost mysql]# cat /etc/sysconfig/network NETWORKING=yes HOSTNAME=mysqlnode1 NETWORKING_IPV6=no PEERNTP=no ``` /etc/hosts增加一行 ``` [root@localhost mysql]# vi /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 127.0.0.1 mysqlnode1 ``` ### 启动节点（第一次启动一定要使用--wsrep-new-cluster，再次启动就不需要） `/etc/init.d/mysql start --wsrep-new-cluster` ### 创建XtraBackup的备份用户名和密码以及修改root口令（初始化之后，之前的数据都没有了） ``` grant all on *.* to 'user_xxx'@'localhost' identified by 'passwd_xxx'; use mysql; update user set password=password('root_xxx') where user='root'; flush privileges; ``` ### 检查mysql ``` #进程是否启动 ps -ef|grep mysql #监听端口是否正确 netstat -tnlp|grep mysql #wsrep全同步复制通过4567端口通信，mysql监听端口是13306 #内存分配器是否为jemalloc lsof | grep libjemalloc ``` ### 报错 \[ERROR\] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe\_to\_bootstrap to 1 . 无法从当前节点实现安全引导。原因是当前节点不是集群中最后离开的节点，也就是说当前节点可能未能包含所有的更新。首先，尝试从其他节点启动。如果所有节点启动都报这个错误，就只能强制启动当前节点，需要修改data/grastate.dat文件将safe\_to\_bootstrap的值置为1。