Percona galera cluster配置（mysql集群cluster同步）

Dear 丶 2022-07-14 12:08 31阅读 0赞

1.准备

node #1
    hostname: percona1
    IP: 192.168.0.18
    
    node #2
    hostname: percona2
    IP: 192.168.0.19

Firewall has been disabled for convenience
       Selinux is disabled
      需要关闭firewalld和selinux

2.安装percona 5.6

yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm yum -y install Percona-XtraDB-Cluster-56 Percona-XtraDB-Cluster-client-56

3.配置第一台mysql node /etc/my.cnf

[mysqld]
    init_connect='SET collation_connection = utf8_general_ci'
    init_connect='SET NAMES utf8'
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    
    log_bin
    
    
    binlog_format                  = ROW
    innodb_buffer_pool_size        = 100M
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 20M
    innodb_file_per_table          = 1
    datadir                        = /var/lib/mysql
    
    
    wsrep_cluster_address          = gcomm://192.168.0.18,192.168.0.19
    wsrep_provider                 = /usr/lib64/galera3/libgalera_smm.so
    
    
    wsrep_slave_threads            = 8
    wsrep_cluster_name             = Cluster
    wsrep_node_name                = mysql02
    wsrep_node_address             = 192.168.0.18
    
    
    wsrep_sst_method               = xtrabackup-v2
    wsrep_sst_auth                 = sst:PASSWORD
    
    
    innodb_locks_unsafe_for_binlog = 1
    innodb_autoinc_lock_mode       = 2
    
    [mysqld_safe]
    pid-file = /run/mysqld/mysql.pid
    syslog

systemctl start mysql@bootstrap.service

配置root用户以及cluster用户（需与配置文件对应）

mysql -uroot <<EOF
    DELETE FROM mysql.user WHERE user='';
    GRANT ALL privileges ON *.* TO 'root'@'%' IDENTIFIED BY 'PASS_CLUSTER';
    GRANT ALL privileges ON *.* TO 'root'@'localhost' IDENTIFIED BY 'PASS_CLUSTER';
    FLUSH PRIVILEGES; 
    
    
    
    
     
     4.配置第二台Mysql node /etc/my.cnf
     [mysqld]
    init_connect='SET collation_connection = utf8_general_ci'
    init_connect='SET NAMES utf8'
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    log_bin
    
    binlog_format                  = ROW
    innodb_buffer_pool_size        = 100M
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 20M
    innodb_file_per_table          = 1
    datadir                        = /var/lib/mysql
    
    wsrep_cluster_address          = gcomm://192.168.0.18,192.168.0.19
    wsrep_provider                 = /usr/lib64/galera3/libgalera_smm.so
    
    wsrep_slave_threads            = 8
    wsrep_cluster_name             = Cluster
    wsrep_node_name                = mysql02
    wsrep_node_address             = 192.168.0.19
    
    wsrep_sst_method               = xtrabackup-v2
    wsrep_sst_auth                 = sst:PASSWORD
    
    innodb_locks_unsafe_for_binlog = 1
    innodb_autoinc_lock_mode       = 2
    
    
    
    [mysqld_safe]
    pid-file = /run/mysqld/mysql.pid
    syslog
    
    
    EOF
    
    mysql -uroot -pPASS_CLUSTER <<EOF
    CREATE USER 'sst'@'localhost' IDENTIFIED BY 'PASSWORD';
    CREATE USER 'sst'@'%' IDENTIFIED BY 'PASSWORD;
    GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost';
    FLUSH PRIVILEGES;
    EOF
    
    
     systemctl start mysql.service
    
     mysql> show status like 'wsrep%';
    +------------------------------+---------------------------------------------------+
    | Variable_name                | Value                                             |
    +------------------------------+---------------------------------------------------+
    | wsrep_local_state_uuid       | 76a53f80-1d8a-11e6-9bc3-2a7a67e390ad              |
    ...
    | wsrep_local_state            | 4                                                 |
    | wsrep_local_state_comment    | Synced                                            |
    ...
    | wsrep_cluster_size           | 2                                                 |
    | wsrep_cluster_state_uuid     | 76a53f80-1d8a-11e6-9bc3-2a7a67e390ad              |
    | wsrep_cluster_status         | Primary                                           |
    | wsrep_connected              | ON                                                |
    ...
    | wsrep_ready                  | ON                                                |
    +------------------------------+---------------------------------------------------+
    
    
     5.测试是否配置成功
     mysql -uroot -pPASS_CLUSTER <<EOF
    CREATE DATABASE PASS_CLUSTER;
    EOF
    
    
    mysql>show  databases;