兜兜    2018-08-09 15:01:14    2019-11-14 14:32:44   

mysql PXC
### 一、环境准备 系统: `CentOS7` 数据库: `Percona-XtraDB-Cluster-57` 服务器: `master1`: `172.16.0.100/db1` `master2`: `172.16.0.101/db2` `master2`: `172.16.0.102/db3` &emsp; &emsp; ### 二、准备工作 `a.删除mysql-community` ```bash yum remove mysql-community-client mysql-community-server -y #仅安装了mysql-community需要执行 ``` `b.关闭防火墙` `c.关闭Selinux` &emsp; &emsp; ### 三、安装PXC集群 `所有节点` 安装Percona库 ```bash yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y ``` 安装Percona-XtraDB-Cluster-57 ```bash yum install Percona-XtraDB-Cluster-57 -y ``` 启动MySQL ```bash systemctl start mysql.service ``` 获取初始密码 ```bash grep 'temporary password' /var/log/mysqld.log ``` ``` 2019-08-09T03:22:26.358453Z 1 [Note] A temporary password is generated for root@localhost: so*WrNqjm3(e ``` 修改root密码 ```sql mysql -u root -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass'; mysql> exit ``` 停止MySQL ```bash systemctl stop mysql.service ``` &emsp; &emsp; ### 四、引导第一个节点 修改配置 `Master1` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```ini [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm:// #配置wsrep_cluster_address=gcomm:// 为了初始化集群,添加其他节点之后再修改回正常的配置,再重启节点即可 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test #集群名字 wsrep_node_name=db1 #节点名 wsrep_node_address=172.16.0.100 #当前节点IP pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 #SST传输方法 wsrep_sst_auth=sstuser:passw0rd #SST账号 ``` bootstrap启动节点 ```bash systemctl start mysql@bootstrap.service ``` 查看集群信息 ```sql mysql> show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_incoming_addresses | 172.16.0.100:3306 | | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+ ``` `上面信息显示集群几点为1,节点状态为Synced` 创建SST用户 ```sql mysql CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql> FLUSH PRIVILEGES; ``` &emsp; &emsp; ### 五、添加节点到集群 #### 添加Master2到集群 修改配置 `Master2` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```bash [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test wsrep_node_name=db2 wsrep_node_address=172.16.0.101 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd ``` 启动节点 ```bash systemctl start mysql ``` 查看集群状态 ```sql mysql> show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_incoming_addresses | 172.16.0.101:3306,172.16.0.100:3306 | | wsrep_cluster_size | 2 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+ ``` `上面信息显示集群现在为2个节点,172.16.0.101:3306,172.16.0.100:3306` #### 添加Master3到集群 修改配置 `Master3` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```bash [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test wsrep_node_name=db3 wsrep_node_address=172.16.0.102 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd ``` 启动节点 ```bash systemctl start mysql ``` 查看集群状态 ```sql mysql> show status like 'wsrep%'; +----------------------------+-------------------------------------------------------+ | Variable_name | Value | +----------------------------+-------------------------------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_incoming_addresses | 172.16.0.102:3306,172.16.0.101:3306,172.16.0.100:3306 | | wsrep_cluster_size | 3 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+-------------------------------------------------------+ ``` `上面信息显示集群现在为3个节点,172.16.0.102:3306,172.16.0.101:3306,172.16.0.100:3306` 重新添加Master1到集群 停止MySQL `Master1` ```bash systemctl stop mysql@bootstrap.service ``` 修改配置 `Master1` ```sql vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```ini ... #wsrep_cluster_address=gcomm:// 修改前 wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 #修改后 ... ``` 启动数据库 `Master1` ```bash systemctl start mysql ``` &emsp; &emsp; ### 六、验证集群 在Master2节点插入测试数据 `Master2` ```sql mysql> CREATE DATABASE percona; mysql> USE percona; mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30)); mysql> INSERT INTO example VALUES (1, 'percona1'); ``` 所有节点查询数据 `所有节点` ```sql mysql> SELECT * FROM percona.example; +---------+-----------+ | node_id | node_name | +---------+-----------+ | 1 | percona1 | +---------+-----------+ ``` `测试发现所有数据都已经同步,说明集群搭建成功!` &emsp; &emsp; ### 七、从节点在线转换成PXC节点 #### **`说明:测试使用db1做主节点,db3做从节点`** #### 重置db3 `a.清理旧的数据并初始化数据库` ```bash systemctl stop mysql mv /var/lib/mysql/ /var/lib/mysql_bak/ mkdir /var/lib/mysql mv /etc/percona-xtradb-cluster.conf.d/wsrep.cnf /tmp #移除PXC相关配置 chown -R mysql.mysql /var/lib/mysql systemctl start mysql #启动并初始化数据库 ``` `b.获取初始密码` ```bash grep 'temporary password' /var/log/mysqld.log ``` ``` 2019-08-09T03:22:26.358453Z 1 [Note] A temporary password is generated for root@localhost: so*WrNqjm3(e ``` `c.修改root密码` ```sql mysql -u root -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass'; mysql> exit ``` &emsp; #### 创建复制账号 `db1` ```sql mysql> create user 'bak'@'172.16.0.%' identified by '123456'; mysql> grant replication slave on *.* to 'bak'@'172.16.0.%'; mysql> flush privileges; ``` #### 备份数据库并发送到db3 `db1` ```bash mysqldump -hlocalhost -uroot -pxxxxxx --single-transaction -A --master-data=2 > all.sql scp all.sql db3:/root ``` #### 从节点执行数据库恢复 `db3` ```bash mysql -hlocalhost -uroot -prootPass </root/all.sql ``` #### 配置从节点同步主节点 `db3` ```sql mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.100',MASTER_USER='bak',MASTER_PASSWORD='test123',MASTER_LOG_FILE='db1-bin.000004', MASTER_LOG_POS=88754; mysql> start slave; ``` #### 停止从节点获取同步位置 `db3` ```sql mysql> stop slave; #停止slave mysql> show slave status\G #查看同步位置 *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.0.100 Master_User: bak Master_Port: 3306 Connect_Retry: 60 Master_Log_File: db1-bin.000004 Read_Master_Log_Pos: 190214 Relay_Log_File: db3-relay-bin.000003 Relay_Log_Pos: 101778 Relay_Master_Log_File: db1-bin.000004 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 190214 ``` `上面的输出显示同步文件:db1-bin.000004,位置为:190214` #### 从节点重置slave配置(`下次启动直接使用PXC去同步`) `db3` ```sql mysql> reset slave all; ``` #### 从节点停止MySQL `db3` ```bash systemctl stop mysql ``` #### 主节点查看同步的Position对应Xid `db1` ```bash mysqlbinlog db1-bin.000004 |grep Xid|grep 190214 ``` ``` #190809 8:57:53 server id 1 end_log_pos 190214 CRC32 0x4cfb3e3b Xid = 675 ``` `确认Postion 190214对应的Xid为675` #### 主节点拷贝grastate.dat文件到从节点 `db1` ```bash scp grastate.dat db3:/var/lib/mysql ``` #### 从节点修改grastate.dat `db3` ```bash cat grastate.dat ``` ```ini # GALERA saved state version: 2.1 uuid: ee7be278-ba54-11e9-9621-8ee7979a72d4 seqno: 675 #-1修改为上面的Xid safe_to_bootstrap: 0 ``` ```bash chown mysql.mysql /var/lib/mysql/grastate.dat ``` #### 加入PXC相关配置 `db3` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```ini [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test wsrep_node_name=db3 wsrep_node_address=172.16.0.102 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd ``` #### 启动数据库 `db3` ```bash systemctl start mysql ``` #### 查看集群信息 `db3` ```sql mysql> show status like 'wsrep%'; +----------------------------------+-------------------------------------------------------+ | Variable_name | Value | +----------------------------------+-------------------------------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | wsrep_protocol_version | 9 | | wsrep_last_applied | 1431 | | wsrep_last_committed | 1431 | | 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 | 247 | | wsrep_received_bytes | 77425 | | 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 | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.004049 | | wsrep_local_cached_downto | 1191 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 173, 173 ] | | wsrep_flow_control_interval_low | 173 | | wsrep_flow_control_interval_high | 173 | | wsrep_flow_control_status | OFF | | wsrep_cert_deps_distance | 75.099585 | | wsrep_apply_oooe | 0.654762 | | wsrep_apply_oool | 0.009259 | | wsrep_apply_window | 5.033069 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 2.513228 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 116 | | wsrep_cert_bucket_count | 210 | | wsrep_gcache_pool_size | 88288 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 172.16.0.101:3306,172.16.0.100:3306,172.16.0.102:3306 | +----------------------------------+-------------------------------------------------------+ ``` 参考:https://www.percona.com/doc/percona-xtradb-cluster/5.7/index.html#introduction 参考书籍: MySQL王者晋级之路/张甦
阅读 176 评论 0 收藏 0
阅读 176
评论 0
收藏 0