兜兜    2021-06-16 20:11:18    2021-09-02 14:07:16   

mysql sqlserver SymmetricDS
##### 1.下载SymmetricDS [http://www.symmetricds.org/](https://www.symmetricds.org/) #### 2.安装配置 ##### 2.1解压安装包到E盘重命名为sym-38 ##### 2.2配置主从配置 拷贝安装包下的samples下的corp-000.properties和store-001.properties到目录engines 配置sqlserver(主) ```properties engine.name=corp-000 db.driver=net.sourceforge.jtds.jdbc.Driver db.url=jdbc:jtds:sqlserver://172.16.13.3:1433/dingding_source;sendStringParametersAsUnicode=false; useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 db.user=sa db.password=xxxxxxxx registration.url= sync.url=http://172.16.13.3:8050/sync/corp-000 group.id=corp external.id=000 job.purge.period.time.ms=7200000 job.routing.period.time.ms=5000 job.push.period.time.ms=10000 job.pull.period.time.ms=10000 auto.registration=true initial.load.create.first=true ``` 配置mysql(从) ```properties engine.name=store-001 db.driver=com.mysql.jdbc.Driver db.url=jdbc:mysql://172.16.13.23/shudoon-dingding-source?tinyInt1isBit=false&useSSL=false db.user=root db.password=xxxxxx registration.url=http://172.16.13.3:8050/sync/corp-000 group.id=store external.id=001 job.routing.period.time.ms=5000 job.push.period.time.ms=10000 job.pull.period.time.ms=10000 ``` #### 3.初始化sym系统表 ```bat symadmin --engine corp-000 create-sym-tables ``` #### 4.重置sym系统表数据 ```bat dbimport --engine corp-000 E:\sym-38\samples\insert_sample.sql ``` #### 5.创建业务表 ```bat dbimport --engine store-001 --format XML E:\sym-38\samples\create_sample.xml ``` #### 6.启动master端 ```bat sym --engine corp-000 --port 8050 ``` #### 7.启动slave端 ```bat sym --engine store-001 --port 8010 ``` #### 8.初始化item表结构 ```bat symadmin --engine corp-000 send-schema item --node 001 ``` #### 8.初始化item表数据 ```bat symadmin --engine corp-000 reload-table item --node 001 ``` #### 9.新增表tCommunicationRecords同步 主库执行 ```sql insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('c_tCommunicationRecords', 3, 100000, 1, ''); insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('corp_tCommunicationRecords_2_store', 'corp', 'store', 'default',current_timestamp, current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('tCommunicationRecords','tCommunicationRecords','c_tCommunicationRecords',current_timestamp,current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('tCommunicationRecords','corp_tCommunicationRecords_2_store', 200, current_timestamp, current_timestamp); ``` 命令行执行 ```bat symadmin --engine corp-000 send-schema tCommunicationRecords --node 001 symadmin --engine corp-000 reload-table tCommunicationRecords --node 001 ``` #### 常见问题: `1.处理sqlserver联合自增主键的表结构无法同步到mysql的问题?` 解决方法: 1.去掉建表语句中联合主键中非自增列,拷贝SQL语句手动在mysql建表。 2.修改sym_outgoing_batch表中batch_id对应数据的状态为OK ```sql update sym_outgoing_batch set status='OK' where batch_id='XXXXXX'; ``` 3.删除sym_data_event中对应batch_id和data_id的事件数据 ```sql delete from sym_data_event where batch_id='XXXXXX' and data_id='YYYYYY'; ```
阅读 201 评论 0 收藏 0
阅读 201
评论 0
收藏 0

兜兜    2018-08-12 15:22:41    2019-11-14 14:32:31   

mysql ProxySQL
### 一、环境准备 系统: `CentOS7` 数据库: `ProxySQL:1.4.14` 服务器: `master`: `172.16.0.100/db1` `slave`: `172.16.0.101/db2` `slave/ProxySQL`: `172.16.0.102/db3`     ### 二、准备工作 `a.数据库搭建MySQL主从` `b.从库开启`**`read_only=on`**     ### 三、ProxySQL的安装 #### 安装依赖的软件包 `db3` ```bash yum -y install perl-DBD-MySQL perl-DBI perl-Time-HiRes perl-IO-Socket-SSL ```   #### ProxySQL软件包的两个下载地址 GitHub官网:https://github.com/sysown/proxysql/releases percona官网:https://www.percona.com/downloads/proxysql/   #### 安装ProxySQL `db3` ```bash yum install -y https://www.percona.com/downloads/proxysql/proxysql-1.4.14/binary/redhat/7/x86_64/proxysql-1.4.14-1.1.el7.x86_64.rpm ```   #### 启动ProxySQL `db3` ```bash systemctl start proxysql ```   #### 查看启动信息 `db3` ```bash netstat -tunlp |grep proxysql ``` ``` tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 13331/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 13331/proxysql ``` `6032为管理端口,6033为对外服务的端口号`   #### 登录ProxySQL `db3` ```bash mysql -uadmin -padmin -h 127.0.0.1 -P 6032 ```   #### 查看proxysql信息 `db3` ```sql mysql> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ #可见有四个库:main、disk、stats和monitor。分别说明一下这四个库的作用。 #main:内存配置数据库,即MEMORY,表里存放后端db实例、用户验证、路由规则等信息。 mysql> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ #库下的主要表: #mysql_servers—后端可以连接MySQL服务器的列表。 #mysql_users—配置后端数据库的账号和监控的账号。 #mysql_query_rules—指定Query路由到后端不同服务器的规则列表。 #注:表名以runtime_开头的表示ProxySQL当前运行的配置内容,不能通过DML语句修改。只能修改对应的不以 runtime开头的表,然后“LOAD”使#其生效,“SAVE”使其存到硬盘以供下次重启加载。 #disk库—持久化磁盘的配置。 #stats库—统计信息的汇总。 #monitor库—一些监控的收集信息,包括数据库的健康状态等。 ```     ### 四、配置ProxySQL监控 #### Master上创建ProxySQL的监控账户和对外访问账户并赋予权限 `db1` ```sql mysql> create user 'monitor'@'172.16.0.%' identified by 'monitor'; mysql> grant all privileges on *.* to 'monitor'@'172.16.0.%' with grant option; mysql> create user 'proxysql'@'172.16.0.%' identified by 'proxysql'; mysql> grant all privileges on *.* to 'proxysql'@'172.16.0.%' with grant option; mysql> flush privileges; ```   #### ProxySQL添加主从服务器列表 `db3` ```sql mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.100',3306); mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.101',3306); mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.102',3306); ```   #### ProxySQL查看服务器列表 `db3` ```sql mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ ```   #### ProxySQL配置监控账号 `db3` ```sql mysql> set mysql-monitor_username='monitor'; mysql> set mysql-monitor_password='monitor'; mysql> load mysql variables to runtime; mysql> save mysql variables to disk; ```   #### ProxySQL验证监控信息 `db3` ```sql mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6; +--------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +--------------+------+------------------+-------------------------+---------------+ | 172.16.0.100 | 3306 | 1565593172754235 | 2118 | NULL | | 172.16.0.101 | 3306 | 1565593172137991 | 2729 | NULL | | 172.16.0.102 | 3306 | 1565593171521858 | 773 | NULL | | 172.16.0.100 | 3306 | 1565593113076006 | 2163 | NULL | | 172.16.0.101 | 3306 | 1565593112298748 | 2377 | NULL | | 172.16.0.102 | 3306 | 1565593111521583 | 628 | NULL | +--------------+------+------------------+-------------------------+---------------+ ```     ### 五、配置ProxySQL主从分组信息 #### ProxySQL插入读写分离组 `db3` ```sql mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values (10,20,'proxy'); mysql> load mysql servers to runtime; mysql> save mysql servers to disk; # writer_hostgroup是写入组的编号,reader_hostgroup是读取组的编号。实验中使用10作为写入组,20作为读取组编号。 mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ # ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server,master被分到编号为10的写组,read_only=1的server,slave则被分到编号为20的读组。 ```   #### ProxySQL配置对外访问账号(`默认指定主库,并对该用户开启事务持久化保护`) `db3` ```sql insert into mysql_users(username,password,default_hostgroup) values ('proxysql','proxysql',10); update mysql_users set transaction_persistent=1 where username='proxysql'; #设置为1,避免发生脏读、幻读等现象 load mysql users to runtime; save mysql users to disk; ```   #### ProxySQL验证登录的服务器 `db3` ```bash mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033 ``` ```sql mysql> select @@hostname; +------------+ | @@hostname | +------------+ | db1 | +------------+ # 验证登入的服务器默认为主库(db1) ```     ### 六、ProxySQL配置读写分离策略 #### ProxySQL配置读写分离 `db3` ```sql mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1); mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',20,1); mysql> load mysql query rules to runtime; mysql> save mysql query rules to disk; ```   #### ProxySQL配置服务器的权重 `db3` ```sql mysql> update mysql_servers set weight=10 where hostname='172.16.0.101'; mysql> load mysql servers to runtime; mysql> save mysql servers to disk; # 172.16.0.101的权重被修改为10,172.16。0.102的默认权重为1,则请求比例为10:1 ```     ### 七、测试读写分离 #### 执行select查询 `db3` ```sql mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033 ``` ```sql mysql> select node_name from percona.example where node_id=1; ```   #### 执行select for update查询 `db3` ```sql mysql> select node_name from percona.example where node_id=1 for update; ```   #### 执行update语句 `db3` ```sql mysql> update percona.example set node_name='test' where node_id=1; ```   #### proxySQL查看统计信息 `db3` ```bash mysql -uadmin -padmin -h 127.0.0.1 -P 6032 ``` ```sql mysql> select hostgroup,digest_text from stats_mysql_query_digest; +-----------+--------------------------------------------------------------------------------------------------+ | hostgroup | digest_text | +-----------+--------------------------------------------------------------------------------------------------+ | 10 | update percona.example set node_name=? where node_id=? | | 10 | select node_name,@@hostname from percona.example where node_id=? for update | | 20 | select node_name,@@hostname from percona.example where node_id=? | +-----------+--------------------------------------------------------------------------------------------------+ ``` `从上面的输出信息可以发现读写分离成功了` 参考: https://blog.51cto.com/sumongodb/2130453
阅读 148 评论 0 收藏 0
阅读 148
评论 0
收藏 0

兜兜    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王者晋级之路/张甦
阅读 167 评论 0 收藏 0
阅读 167
评论 0
收藏 0

兜兜    2018-08-08 14:46:59    2019-11-14 14:33:01   

mysql GTID
### 环境准备 系统: `CentOS7` 数据库: `MySQL5.7` Master节点: `172.16.0.100(node1)` Slave节点: `172.16.0.101(node2)` #### GTID复制切换成传统复制 查看复制信息 `Slave` ```sql mysql> show slave status\G ``` ``` Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.100 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: on.000001 Read_Master_Log_Pos: 7227 Relay_Log_File: db3-relay-bin.000002 Relay_Log_Pos: 2374 Relay_Master_Log_File: on.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 7227 ... ``` 停止Slave,配置MASTER_AUTO_POSITION=0 `Slave` ```sql mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.100', MASTER_PORT=3306,MASTER_USER='replica', MASTER_PASSWORD='xxxxx',MASTER_AUTO_POSITION=0,MASTER_LOG_FILE='on.000001',MASTER_LOG_POS=7227; mysql> start slave; ``` 主从同时配置GTID模式为on_permissive `Master/Slave` ```sql mysql> set global gtid_mode=on_permissive ``` 主从同时配置GTID模式为off_permissive `Master/Slave` ```sql mysql> set global gtid_mode=off_permissive ``` 主从同时配置关闭GTID功能 `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=off; mysql> set global gtid_mode=off; ``` 把gtid_mode=off和enforce_gtid_consistency=off写入my.cnf `Master/Slave` ```bash cat /etc/my.cnf ``` ```ini [mysqld] gtid_mode=on enforce_gtid_consistency=on ``` 验证传统复制是否成功 _1.插入数据到Master_ _2.Slave查看Executed_Gtid_Set值是否增加,如果值没变化,数据同步成功,说明传统复制配置成功_ &emsp; #### 传统复制切换GTID复制 主从同时修改参数enforce_gtid_consistency=warn `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=warn; ``` 主从同时修改参数enforce_gtid_consistency=on `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=on; ``` 主从同时配置GTID模式为off_permissive `Master/Slave` ```sql mysql> set global gtid_mode=off_permissive ``` 主从同时配置GTID模式为on_permissive `Master/Slave` ```sql mysql> set global gtid_mode=on_permissive ``` 确认从库的Ongoing_anonymous_transaction_count参数是否为0(为0,意味着没有等待的事务,可以直接进行下一步操作) `Slave` ```sql mysql> show global status like 'Ongoing_anonymous_transaction_count'; ``` ``` +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | Ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ ``` 主从同时配置gtid_mode=on `Master/Slave` ```sql mysql> set global gtid_mode=on; ``` 把传统模式改为GTID复制 `Slave` ```sql mysql> stop slave; mysql> change master to master_auto_position=1; mysql> start slave; ``` 验证GTID复制是否成功 _1.插入数据到Master_ _2.Slave查看Executed_Gtid_Set值是否增加,如果是则说明切换GTID复制成功_
阅读 170 评论 0 收藏 0
阅读 170
评论 0
收藏 0

兜兜    2018-08-05 11:05:07    2019-11-14 14:32:56   

mysql MHA
### 环境准备 系统: `CentOS7` 软件: `MySQL5.7` `MHA 0.56` VIP: `172.16.0.222` 服务器: `node1(Master节点)`: `172.16.0.100` `node2(SLave节点/Master备)`: `172.16.0.101` `node3(SLave节点/MHA manager)`: `172.16.0.102` &emsp; ### 初始化工作 **`a.三台机器SSH免密登陆`** **`b.三台机器的主机名和设置hosts文件解析`** **`c.两台SLAVE节点配置成MASTER节点的MySQL主从`** **`d.MySQL的MHA配置要求`** ```bash vim /etc/my.cnf ``` ```ini [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid bind-address = 0.0.0.0 #配置 server-id = 1 #节点的值唯一 log_bin = mysql-bin #MySQL Master或者备用Master binlog_ignore_db = mysql binlog_ignore_db = infomation_schema binlog_ignore_db = performance_schema #relay_log_purge = 0 #从库需要配置,执行完relay不删除 ``` `提示:MySQL Master或者备用Master需要打开binlog,MHA要求各个数据库节点的复制过滤规则(binlog-do-db, replicate-ignore-db)都一样` **`e. 开启半同步复制`** `所有节点都执行下面命令` ```sql mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步复制插件 mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步复制插件 mysql> set global rpl_semi_sync_slave_enabled=on; #开启Slave半同步复制 mysql> set global rpl_semi_sync_master_enabled=on; #开启Master半同步复制 mysql> stop slave io_thread; mysql> start slave io_thread; mysql> show variables like '%semi%'; #查看半同步复制参数 mysql> show plugins; #查看加载的插件 ``` **`f. MySQL配置GTID复制(可选)`** 参考:https://ynotes.cn/blog/article_detail/203 传统复制和GTID复制转换 &emsp; ### 安装MHA 下载MHA Manager & MHA Node ```bash curl http://www.mysql.gr.jp/frame/modules/bwiki/index.php\?plugin\=attach\&pcmd\=open\&file\=mha4mysql-manager-0.56-0.el6.noarch.rpm\&refer\=matsunobu -o mha4mysql-manager-0.56-0.el6.noarch.rpm curl http://www.mysql.gr.jp/frame/modules/bwiki/index.php\?plugin\=attach\&pcmd\=open\&file\=mha4mysql-node-0.56-0.el6.noarch.rpm\&refer\=matsunobu -o mha4mysql-node-0.56-0.el6.noarch.rpm ``` 安装MHA Node `所有节点` ```bash yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm ``` 安装MHA Manager `node3(MHA manager节点)` ```bash yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm ``` &emsp; ### 配置MHA管理节点 `node3(MHA manager节点)` 创建mha相关目录 ```bash mkdir -p /usr/local/mha mkdir -p /etc/mha ``` 创建mha.conf ```bash vim /etc/mha/mha.conf ``` ```ini [server default] manager_log=/var/log/mha/manager.log master_ip_failover_script=/etc/mha/scripts/master_ip_failover master_ip_online_change_script=/etc/mha/scripts/master_ip_failover # mysql user and password user=mha_admin password=123456 #ssh user ssh_user=root # working directory on the manager manager_workdir=/usr/local/mha # working directory on MySQL servers remote_workdir=/usr/local/mha repl_user=replica repl_password=123456 [server1] hostname=172.16.0.100 [server2] hostname=172.16.0.101 [server3] hostname=172.16.0.102 ``` 创建VIP切换脚本 ```bash vim /etc/mha/scripts/master_ip_failover ``` ```perl #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip = '172.16.0.222/24'; # Virtual IP #设置VIP地址 my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip"; #针对网卡配置,如果使用的是eth0,则eth0:$key my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down"; #针对网卡配置,如果使用的是eth0,则eth0:$key $ssh_user = "root"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; `ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; } ``` 创建复制用户(replica) `node1-2节点(Master节点/Master备)` ```sql mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; ``` 创建管理用户(mha) `所有节点` ```sql mysql> GRANT ALL ON *.* TO 'mha'@'172.16.0.%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; ``` &emsp; ### Master配置VIP(第一次手动添加) `node1节点` ```bash /sbin/ifconfig eth1:1 172.16.0.222 ``` &emsp; ### 测试节点间的SSH登录 `node3(MHA manager节点)` ```bash masterha_check_ssh --conf=/etc/mha/mha.conf ``` ``` Mon Aug 5 03:17:00 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 5 03:17:00 2019 - [info] Reading application default configuration from /etc/mha/mha.conf.. Mon Aug 5 03:17:00 2019 - [info] Reading server configuration from /etc/mha/mha.conf.. Mon Aug 5 03:17:00 2019 - [info] Starting SSH connection tests.. Mon Aug 5 03:17:01 2019 - [debug] Mon Aug 5 03:17:00 2019 - [debug] Connecting via SSH from root@172.16.0.100(172.16.0.100:22) to root@172.16.0.101(172.16.0.101:22).. Mon Aug 5 03:17:00 2019 - [debug] ok. Mon Aug 5 03:17:00 2019 - [debug] Connecting via SSH from root@172.16.0.100(172.16.0.100:22) to root@172.16.0.102(172.16.0.102:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:01 2019 - [debug] Mon Aug 5 03:17:00 2019 - [debug] Connecting via SSH from root@172.16.0.101(172.16.0.101:22) to root@172.16.0.100(172.16.0.100:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:01 2019 - [debug] Connecting via SSH from root@172.16.0.101(172.16.0.101:22) to root@172.16.0.102(172.16.0.102:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:02 2019 - [debug] Mon Aug 5 03:17:01 2019 - [debug] Connecting via SSH from root@172.16.0.102(172.16.0.102:22) to root@172.16.0.100(172.16.0.100:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:01 2019 - [debug] Connecting via SSH from root@172.16.0.102(172.16.0.102:22) to root@172.16.0.101(172.16.0.101:22).. Mon Aug 5 03:17:02 2019 - [debug] ok. Mon Aug 5 03:17:02 2019 - [info] All SSH connection tests passed successfully. ``` `上面显示ssh免密登陆配置成功` ### 检查mha配置 `node3(MHA manager节点)` ```bash masterha_check_repl --conf=/etc/mha/mha.conf ``` ``` Mon Aug 5 03:39:17 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 5 03:39:17 2019 - [info] Reading application default configuration from /etc/mha/mha.conf.. Mon Aug 5 03:39:17 2019 - [info] Reading server configuration from /etc/mha/mha.conf.. Mon Aug 5 03:39:17 2019 - [info] MHA::MasterMonitor version 0.56. Mon Aug 5 03:39:18 2019 - [info] GTID failover mode = 0 Mon Aug 5 03:39:18 2019 - [info] Dead Servers: Mon Aug 5 03:39:18 2019 - [info] Alive Servers: Mon Aug 5 03:39:18 2019 - [info] 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] 172.16.0.101(172.16.0.101:3306) Mon Aug 5 03:39:18 2019 - [info] 172.16.0.102(172.16.0.102:3306) Mon Aug 5 03:39:18 2019 - [info] Alive Slaves: Mon Aug 5 03:39:18 2019 - [info] 172.16.0.101(172.16.0.101:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled Mon Aug 5 03:39:18 2019 - [info] Replicating from 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] 172.16.0.102(172.16.0.102:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled Mon Aug 5 03:39:18 2019 - [info] Replicating from 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] Current Alive Master: 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] Checking slave configurations.. Mon Aug 5 03:39:18 2019 - [info] read_only=1 is not set on slave 172.16.0.101(172.16.0.101:3306). Mon Aug 5 03:39:18 2019 - [warning] relay_log_purge=0 is not set on slave 172.16.0.101(172.16.0.101:3306). Mon Aug 5 03:39:18 2019 - [info] read_only=1 is not set on slave 172.16.0.102(172.16.0.102:3306). Mon Aug 5 03:39:18 2019 - [warning] relay_log_purge=0 is not set on slave 172.16.0.102(172.16.0.102:3306). Mon Aug 5 03:39:18 2019 - [info] Checking replication filtering settings.. Mon Aug 5 03:39:18 2019 - [info] binlog_do_db= , binlog_ignore_db= infomation_schema,mysql,performance_schema Mon Aug 5 03:39:18 2019 - [info] Replication filtering check ok. Mon Aug 5 03:39:18 2019 - [info] GTID (with auto-pos) is not supported Mon Aug 5 03:39:18 2019 - [info] Starting SSH connection tests.. Mon Aug 5 03:39:20 2019 - [info] All SSH connection tests passed successfully. Mon Aug 5 03:39:20 2019 - [info] Checking MHA Node version.. Mon Aug 5 03:39:21 2019 - [info] Version check ok. Mon Aug 5 03:39:21 2019 - [info] Checking SSH publickey authentication settings on the current master.. Mon Aug 5 03:39:21 2019 - [info] HealthCheck: SSH to 172.16.0.100 is reachable. Mon Aug 5 03:39:21 2019 - [info] Master MHA Node version is 0.56. Mon Aug 5 03:39:21 2019 - [info] Checking recovery script configurations on 172.16.0.100(172.16.0.100:3306).. Mon Aug 5 03:39:21 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/usr/local/mha/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000008 Mon Aug 5 03:39:21 2019 - [info] Connecting to root@172.16.0.100(172.16.0.100:22).. Creating /usr/local/mha if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mysql-bin.000008 Mon Aug 5 03:39:22 2019 - [info] Binlog setting check done. Mon Aug 5 03:39:22 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Mon Aug 5 03:39:22 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha_admin' --slave_host=172.16.0.101 --slave_ip=172.16.0.101 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.27-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Mon Aug 5 03:39:22 2019 - [info] Connecting to root@172.16.0.101(172.16.0.101:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to db2-relay-bin.000002 Temporary relay log file is /var/lib/mysql/db2-relay-bin.000002 Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Aug 5 03:39:22 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha_admin' --slave_host=172.16.0.102 --slave_ip=172.16.0.102 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.27-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Mon Aug 5 03:39:22 2019 - [info] Connecting to root@172.16.0.102(172.16.0.102:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to db3-relay-bin.000002 Temporary relay log file is /var/lib/mysql/db3-relay-bin.000002 Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Aug 5 03:39:22 2019 - [info] Slaves settings check done. Mon Aug 5 03:39:22 2019 - [info] 172.16.0.100(172.16.0.100:3306) (current master) +--172.16.0.101(172.16.0.101:3306) +--172.16.0.102(172.16.0.102:3306) Mon Aug 5 03:39:22 2019 - [info] Checking replication health on 172.16.0.101.. Mon Aug 5 03:39:22 2019 - [info] ok. Mon Aug 5 03:39:22 2019 - [info] Checking replication health on 172.16.0.102.. Mon Aug 5 03:39:22 2019 - [info] ok. Mon Aug 5 03:39:22 2019 - [info] Checking master_ip_failover_script status: Mon Aug 5 03:39:22 2019 - [info] /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.0.100 --orig_master_ip=172.16.0.100 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth1:1 down==/sbin/ifconfig eth1:1 172.16.0.222/24=== Checking the Status of the script.. OK ssh: Could not resolve hostname cluster1: Name or service not known Mon Aug 5 03:39:22 2019 - [info] OK. Mon Aug 5 03:39:22 2019 - [warning] shutdown_script is not defined. Mon Aug 5 03:39:22 2019 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. ``` `MySQL Replication Health is OK.显示配置正确!` ### 启动MHA Manager ```bash nohup masterha_manager --conf=/etc/mha/mha.conf & ``` ### 验证是否启动成功 ```bash masterha_check_status --conf=/etc/mha/mha.conf ``` ``` mha (pid:27057) is running(0:PING_OK), master:172.16.0.100 ``` ### 停止MHA Manager(`不执行`) ```bash masterha_stop --conf=/etc/mha/mha.conf ``` &emsp; ### 测试 #### 测试故障转移 **停止node1节点的MySQL** ```bash systemctl stop mysqld ``` **node2查看VIP** ```bash ip a ``` ``` ... 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1450 qdisc pfifo_fast state UP group default qlen 1000 link/ether 5a:00:02:32:0f:46 brd ff:ff:ff:ff:ff:ff inet 172.16.0.101/16 brd 172.16.255.255 scope global eth1 valid_lft forever preferred_lft forever inet 172.16.0.222/24 brd 172.16.0.255 scope global eth1:1 valid_lft forever preferred_lft forever inet6 fe80::5800:2ff:fe32:f46/64 scope link valid_lft forever preferred_lft forever ``` `通过上面的信息可以看到VIP已经转移到node2` **查看是否为slave** ```sql mysql> show slave status\G #没有输出,说明node2已经为非slave节点 Empty set (0.00 sec) ``` **查看node3的slave信息** ```sql mysql> show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.101 Master_User: replica Master_Port: 3306 Connect_Retry: 60 ... ``` `通过上面的信息可以确定故障转移成功,master已经变成node2节点,node3节点已经同步到新的slave` &emsp; #### 旧master恢复的操作 `node1数据库恢复之后,首先设置为node2的slave,之后启动MHA Manager` 启动MySQL `node1` ```bash systemctl start mysqld ``` 配置node1为node2的slave _MHA节点查看同步信息_ `node3` ```bash grep "CHANGE MASTER TO" /var/log/mha/manager.log |tail -1 ``` ```bash Mon Aug 5 03:32:28 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.0.101', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=154, MASTER_USER='replica', MASTER_PASSWORD='xxx'; ``` _node1配置同步node2_ `node1` ```sql mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.101', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=154, MASTER_USER='replica', MASTER_PASSWORD='123456'; mysql> start slave; #启动slave mysql> show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.101 Master_User: replica Master_Port: 3306 Connect_Retry: 60 ... ``` 把 Master切回到原理的node1 `node3` ```bash masterha_master_switch --conf=/etc/mha/mha.conf --master_state=alive --new_master_host=172.16.0.100 --orig_master_is_new_slave ``` 检查mha配置 `node3` ```bash masterha_check_repl --conf=/etc/mha/mha.conf ``` ``` ... MySQL Replication Health is OK. ``` 启动MHA Manager `node3` ```bash nohup masterha_manager --conf=/etc/mha/mha.conf & ``` 参考: http://www.fblinux.com/?p=1018 https://joelhy.github.io/2015/02/06/mysql-mha/
阅读 159 评论 0 收藏 0
阅读 159
评论 0
收藏 0

兜兜    2018-08-01 17:07:51    2019-11-14 14:32:50   

数据库 mysql
### 环境准备 系统: `CentOS7` 数据库: `MySQL5.7` Master1节点: `172.16.0.100(node1)` Master2节点: `172.16.0.101(node2)` VIP: `172.16.0.188` 同步数据库名:`replicatest` &emsp; ### 安装MySQL `Master1/Master2节点` ```bash yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm yum install mysql-community-server ``` 启动数据库 ```bash systemctl enable mysqld systemctl start mysqld ``` 获取数据库临时密码 ```bash grep 'temporary password' /var/log/mysqld.log ``` 对数据库进行加固 ```bash mysql_secure_installation ``` &emsp; ### 配置Master1 修改数据库配置 ```bash vim /etc/my.cnf ``` ```ini bind-address = 172.16.0.100 server-id = 1 log_bin = mysql-bin binlog-do-db = replicatest replicate-do-db = replicatest auto-increment-increment = 2 auto-increment-offset = 1 ``` 重启数据库 ```bash systemctl restart mysqld ``` 创建复制账号 ```sql mysql> CREATE USER 'replica'@'172.16.0.101' IDENTIFIED BY 'strong_password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.101'; mysql> FLUSH PRIVILEGES; ``` 查看二进制日志文件和位置 ```sql mysql> FLUSH TABLES WITH READ LOCK #锁表,备份数据,恢复到master2,再让master2从当前位置去同步数据,保证了数据的一致性 mysql> SHOW MASTER STATUS\G ``` ``` *************************** 1. row *************************** File: mysql-bin.000001 Position: 623 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ``` `注意:文件:mysql-bin.000001,位置:623` 备份replicatest数据 ```bash mysqldump -h127.0.0.1 -uroot -pxxxx --databases replicatest --events --triggers --routines >replicatest.sql ``` ### 拷贝数据到master2 ```bash scp replicatest.sql root@node2:/root ``` &emsp; ### 配置Master2 修改数据库配置 ```bash vim /etc/my.cnf ``` ```ini bind-address = 172.16.0.101 server-id = 2 log_bin = mysql-bin binlog-do-db = replicatest replicate-do-db = replicatest #binlog_ignore_db = mysql #binlog_ignore_db = infomation_schema #binlog_ignore_db = performance_schema auto-increment-increment = 2 auto-increment-offset = 2 ``` 重启数据库 ```bash systemctl restart mysqld ``` 恢复数据库 ```sql mysql>source replicatest.sql ``` 停止slave线程 ```sql mysql> STOP SLAVE; ``` 配置master2复制master1 ```sql mysql> CHANGE MASTER TO mysql> MASTER_HOST='172.16.0.100', mysql> MASTER_USER='replica', mysql> MASTER_PASSWORD='strong_password', mysql> MASTER_LOG_FILE='mysql-bin.000001', mysql> MASTER_LOG_POS=623; ``` 启动slave线程 ```sql mysql> START SLAVE; ``` 查看slave状态 ```sql mysql > show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.100 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 623 Relay_Log_File: node2-relay-bin.000001 Relay_Log_Pos: 323 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 623 Relay_Log_Space: 544 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: f8cc4c10-b429-11e9-9ff9-5600023106ec Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: ``` 创建复制账号 ```bash mysql> CREATE USER 'replica'@'172.16.0.100' IDENTIFIED BY 'strong_password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.100'; mysql> FLUSH PRIVILEGES; ``` 查看当前的二进制日志 ```sql mysql> SHOW MASTER STATUS\G ``` ``` *************************** 1. row *************************** File: mysql-bin.000001 Position: 504 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ``` `注意:文件:mysql-bin.000001,位置:504` &emsp; ### 配置Master1 解锁只读锁 ```sql mysql> UNLOCK TABLES ``` 停止slave线程 ```sql mysql> STOP SLAVE; ``` 配置同步 ```sql mysql> CHANGE MASTER TO mysql> MASTER_HOST='172.16.0.101', mysql> MASTER_USER='replica', mysql> MASTER_PASSWORD='strong_password', mysql> MASTER_LOG_FILE='mysql-bin.000001', mysql> MASTER_LOG_POS=504; ``` 启动slave线程 ```sql mysql> START SLAVE; ``` 查看同步状态 ```sql mysql> show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.101 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 504 Relay_Log_File: node1-relay-bin.000001 Relay_Log_Pos: 245 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 504 Relay_Log_Space: 213 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 1c505063-b42a-11e9-96c3-560002320f46 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: ``` &emsp; ### 安装Keepalived `Master1节点/Master2节点` ```bash yum install keepalived -y ``` ### 配置Keepalived `Master1` ```bash vim /etc/keepalived/keepalived.conf #除了参数priority两台Master设置优先级不一样,其他参数保存一致 ``` ```ini ! Configuration File for keepalived global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script vs_mysql_100 { script "/etc/keepalived/checkmysql.sh" interval 10 } vrrp_instance VI_100 { #集群的名字 state BACKUP #这里两台都配置BACKUP nopreempt #设置为不抢占模式 interface eth1 #VIP绑定的网卡eth1 virtual_router_id 51 #vrid的两台一样 priority 100 #优先级,两台设置不一样,master1设置为100,master2设置为90 advert_int 5 #主备之前同步建成的时间间隔是5秒 authentication { auth_type PASS #验证方式通过密码 auth_pass 9898 #验证密码 } track_script { vs_mysql_100 #执行监控的脚本 } virtual_ipaddress { 172.16.0.188 #虚拟VIP地址 } } ``` #### 创建数据库监控脚本 `Master1/Master2节点` ```bash cat /etc/keepalived/checkmysql.sh ``` ```sh #!/bin/bash mysqlstr=/usr/bin/mysql host=127.0.0.1 user=root password=xxxxxxx port=3306 mysql_status=1 $mysqlstr -h $host -u $user -p$password -P$port -e "show status;" >/dev/null 2>&1 if [ $? -eq 0 ];then echo "mysql_status=1" exit 0 else echo "systemctl stop keepaliaved" systemctl stop keepaliaved fi ``` 添加执行权限 ```bash chmod +x /etc/keepalived/checkmysql.sh ``` ### 启动keepalived `Master1/Master2节点` ```bash systemctl start keepalived ``` &emsp; ### 测试主主 `Master1` 插入测试数据 ```sql mysql> use replicatest; mysql> insert into test(name) values('master1 write'); ``` `Master2` 查看是否同步 ```sql mysql> use replicatest; mysql> select * from test; ``` ``` +----+---------------+ | id | name | +----+---------------+ | 19 | master1 write | +----+---------------+ ``` 插入测试数据 ```sql mysql> insert into test(name) values('master2 write'); ``` `Master1` 查看是否同步 ```sql select * from test; ``` ``` +----+---------------+ | id | name | +----+---------------+ | 19 | master1 write | | 20 | master2 write | +----+---------------+ ``` `通过输出可以看到master1和master2都能同步成功!` &emsp; ### 测试keepalived **停止Master1上的MySQL,查看VIP是否转移到MASTER2** `Master1` ```bash systemcat stop mysqld ``` 查看VIP是否漂移成功 `Master2` ```bash ip a ``` ``` ... 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1450 qdisc pfifo_fast state UP group default qlen 1000 link/ether 5a:00:02:32:0f:46 brd ff:ff:ff:ff:ff:ff inet 172.16.0.101/16 brd 172.16.255.255 scope global eth1 valid_lft forever preferred_lft forever inet 172.16.0.188/32 scope global eth1 valid_lft forever preferred_lft forever ``` `通过测试发现VIP漂移成功`
阅读 157 评论 0 收藏 0
阅读 157
评论 0
收藏 0

兜兜    2018-08-01 15:42:24    2019-11-14 14:33:10   

数据库 mysql
### 环境准备 系统: `CentOS7` 数据库: `MySQL5.7` Master节点: `172.16.0.100(node1)` Slave节点: `172.16.0.101(node2)` &emsp; ### 安装MySQL `Master/Slave节点` ```bash yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm yum install mysql-community-server ``` 启动数据库 ```bash systemctl enable mysqld systemctl start mysqld ``` 获取数据库临时密码 ```bash grep 'temporary password' /var/log/mysqld.log ``` 对数据库进行加固 ```bash mysql_secure_installation ``` &emsp; ### 配置Master 修改数据库配置 ```bash vim /etc/my.cnf ``` ```ini bind-address = 172.16.0.100 server-id = 1 log_bin = mysql-bin ``` 重启数据库 ```bash systemctl restart mysqld ``` 创建复制账号 ```sql mysql> CREATE USER 'replica'@'172.16.0.101' IDENTIFIED BY 'strong_password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.101'; mysql> FLUSH PRIVILEGES; ``` 查看二进制日志文件和位置 ```sql mysql> SHOW MASTER STATUS\G ``` ``` *************************** 1. row *************************** File: mysql-bin.000001 Position: 623 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ``` `注意:文件:mysql-bin.000001,位置:623` &emsp; ### 配置Slave 修改数据库配置 ```bash vim /etc/my.cnf ``` ```ini bind-address = 172.16.0.101 server-id = 2 log_bin = mysql-bin ``` 重启数据库 ```bash systemctl restart mysqld ``` 停止slave线程 ```sql mysql> STOP SLAVE; ``` 配置slave复制master ```sql mysql> CHANGE MASTER TO mysql> MASTER_HOST='172.16.0.100', mysql> MASTER_USER='replica', mysql> MASTER_PASSWORD='strong_password', mysql> MASTER_LOG_FILE='mysql-bin.000001', mysql> MASTER_LOG_POS=623; ``` 启动slave线程 ```sql mysql> START SLAVE; ``` 查看slave状态 ```sql mysql >show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.100 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1576 Relay_Log_File: node2-relay-bin.000002 Relay_Log_Pos: 1273 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 1576 Relay_Log_Space: 1480 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: f8cc4c10-b429-11e9-9ff9-5600023106ec Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: ``` ### 开启半同步复制 安装半同步复制插件 `Master` ```sql mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步复制插件 mysql> set global rpl_semi_sync_master_enabled=on; #开启Master半同步复制 mysql> show variables like '%semi%'; #查看半同步复制参数 mysql> show plugins; #查看加载的插件 ``` ``` ``` `Slave` ```sql mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步复制插件 mysql> set global rpl_semi_sync_slave_enabled=on; #开启Slave半同步复制 mysql> show variables like '%semi%'; #查看半同步复制参数 mysql> show plugins; #查看加载的插件 ``` 重启IO线程 `Slave` ```sql mysql> stop slave io_thread; mysql> start slave io_thread; ``` 查看半同步复制信息 `Master` ```sql mysql> show global status like '%semi%'; ``` ``` +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ ``` `Rpl_semi_sync_master_no_tx`表示没有成功接收slave提交的事务 `Rpl_semi_sync_master_yes_tx`表示成功接收slave事务回复的次数 配置开机自启动半同步复制 `Master` ```bash vim /etc/my.cnf ``` ```ini rpl_semi_sync_master_enabled = on ``` `Slave` ```bash vim /etc/my.cnf ``` ```ini rpl_semi_sync_slave_enabled = on ``` &emsp; ### 测试主从 `Master` ```sql mysql> CREATE DATABASE replicatest; ``` `Slave` ```sql mysql> show databases; ``` ``` +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | replicatest | | sys | +--------------------+ ``` `通过输出可以看到slave已经同步成功了!`
阅读 137 评论 0 收藏 0
阅读 137
评论 0
收藏 0

兜兜    2018-07-29 11:42:38    2019-11-14 14:32:03   

mysql DRBD pcs Pacemaker corosync
### 准备工作 所有节点: - 系统: `CentOS7.6` - 数据库: `MariaDB 5.5.60` - VIP: `172.16.0.200` node1节点: - IP/主机:`172.16.0.100` node2节点: - IP/主机:`172.16.0.101` 网络配置如下图 ![](https://image.ynotes.cn/drbd_pcs2.png) &emsp; ### 安装Pacemaker和Corosync #### 安装Pacemaker,Corosync,pcs `node1和node2执行` ```bash yum -y install corosync pacemaker pcs ``` #### 设置集群用户密码 `node1和node2执行` ```bash echo "passwd" | passwd hacluster --stdin ``` 启动和开启服务 `node1和node2执行` ```bash systemctl start pcsd systemctl enable pcsd pcs cluster enable --all #配置集群服务开机启动 ``` #### 配置Corosync `node1执行` 认证用户hacluster,将授权tokens存储在文件/var/lib/pcsd/tokens中. ```bash pcs cluster auth node1 node2 -u hacluster -p passwd ``` ``` node1: Authorized node2: Authorized ``` #### 生成和同步Corosync配置 `node1执行` ```bash pcs cluster setup --name mysql_cluster node1 node2 ``` #### 在所有节点启动集群 `node1执行` ```bash pcs cluster start --all ``` ### 安装DRBD `node1和node2执行` ```bash rpm --import https://www.elrepo.org/RPM-GPG-KEY-elrepo.org rpm -Uvh http://www.elrepo.org/elrepo-release-7.0-2.el7.elrepo.noarch.rpm yum install -y kmod-drbd84 drbd84-utils ``` #### 配置DRBD `node1和node2执行` ```bash vim /etc/drbd.d/mysql01.res ``` ```ini resource mysql01 { protocol C; meta-disk internal; device /dev/drbd0; disk /dev/vdb; #/dev/vdb为空闲的块设备,可以LVM创建一个逻辑卷 handlers { split-brain "/usr/lib/drbd/notify-split-brain.sh root"; } net { allow-two-primaries no; after-sb-0pri discard-zero-changes; after-sb-1pri discard-secondary; after-sb-2pri disconnect; rr-conflict disconnect; } disk { on-io-error detach; } syncer { verify-alg sha1; } on node1 { address 172.16.0.100:7789; } on node2 { address 172.16.0.101:7789; } } ``` #### 初始化DRBD`(创建DRBD metadata)` `node1和node2执行` ```bash drbdadm create-md mysql01 ``` #### 启动mysql01 `node1和node2执行` ```bash drbdadm up mysql01 ``` #### 指定主节点 `node1执行` ```bash drbdadm primary --force mysql01 ``` #### 查看drbd状态 `node1执行` ```bash cat /proc/drbd ``` ``` version: 8.4.11-1 (api:1/proto:86-101) GIT-hash: 66145a308421e9c124ec391a7848ac20203bb03c build by mockbuild@, 2018-11-03 01:26:55 0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r----- ns:136 nr:288 dw:428 dr:13125 al:5 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0 ``` #### 快速同步 ```bash drbdadm new-current-uuid --clear-bitmap mysql01/0 ``` #### 格式化drbd设备 等待上面主从的块设备同步(UpToDate/UpToDate)之后执行 格式化drbd成ext4格式 `node1执行` ```bash mkfs.ext4 -m 0 -L drbd /dev/drbd0 tune2fs -c 30 -i 180d /dev/drbd0 ``` #### 挂载drbd设备 `node1执行` ```bash mount /dev/drbd0 /mnt ``` &emsp; ### 安装MariaDB `node1和node2执行` ```bash yum install -y mariadb-server mariadb systemctl disable mariadb.service #设置开启不启动,通过pacemaker去管理 ``` `node1执行` ```bash systemctl start mariadb ``` #### 安装数据库 `node1执行` ```bash mysql_install_db --datadir=/mnt --user=mysql ``` #### 执行安全安装 `node1执行` ```bash mysql_secure_installation ``` #### 卸载drbd和停止数据库 `node1执行` ```bash umount /mnt #卸载目录 systemctl stop mariadb #停止数据库 ``` #### 配置mysql `node1和node2执行` ```bash vim /etc/my.cnf ``` ```ini [mysqld] symbolic-links=0 bind_address = 0.0.0.0 datadir = /var/lib/mysql pid_file = /var/run/mariadb/mysqld.pid socket = /var/run/mariadb/mysqld.sock [mysqld_safe] bind_address = 0.0.0.0 datadir = /var/lib/mysql pid_file = /var/run/mariadb/mysqld.pid socket = /var/run/mariadb/mysqld.sock !includedir /etc/my.cnf.d ``` &emsp; ### 配置Pacemaker集群 #### 配置逻辑和顺序如下 ```bash Start: mysql_fs01 -> mysql_service01 -> mysql_VIP01, Stop: mysql_VIP01 -> mysql_service01 -> mysql_fs01. ``` mysql_fs01是文件系统资源,mysql_service01是服务资源,mysql_VIP01是浮动虚拟IP `172.16.0.200` pcs具有的一个方便功能是能够将多个更改排入文件并以原子方式提交这些更改。为此,我们首先使用CIB中的当前原始XML配置填充文件 `node1执行` ```bash pcs cluster cib clust_cfg ``` 关闭STONITH(`注意:依赖具体的环境视情况操作`) `node1执行` ```bash pcs -f clust_cfg property set stonith-enabled=false ``` 设置quorum策略为ignore `node1执行` ```bash pcs -f clust_cfg property set no-quorum-policy=ignore ``` 防止资源在恢复后移动,因为它通常会增加停机时间 `node1执行` ```bash pcs -f clust_cfg resource defaults resource-stickiness=200 ``` 为了达到这个效果,Pacemaker 有一个叫做“资源粘性值”的概念,它能够控制一个服务(资源)有多想呆在它正在运行的节点上。 Pacemaker为了达到最优分布各个资源的目的,默认设置这个值为0。我们可以为每个资源定义不同的粘性值,但一般来说,更改默认粘性值就够了。资源粘性表示资源是否倾向于留在当前节点,如果为正整数,表示倾向,负数则会离开,-inf表示负无穷,inf表示正无穷。 &emsp; 为drbd设备创建名为mysql_data01的集群资源和一个额外的克隆资源MySQLClone01,允许资源同时在两个集群节点上运行 `node1执行` ```bash pcs -f clust_cfg resource create mysql_data01 ocf:linbit:drbd \ drbd_resource=mysql01 \ op monitor interval=30s ``` ```bash pcs -f clust_cfg resource master MySQLClone01 mysql_data01 \ master-max=1 master-node-max=1 \ clone-max=2 clone-node-max=1 \ notify=true ``` master-max: 可以将多少资源副本提升为主状态 master-node-max: 可以在单个节点上将多少个资源副本提升为主状态 clone-max: 要启动多少个资源副本。默认为群集中的节点数 clone-node-max: 可以在单个节点上启动多少个资源副本 notify: 停止或启动克隆副本时,请事先告知所有其他副本以及操作何时成功 &emsp; 为文件系统创建名为mysql_fs01的集群资源,告诉群集克隆资源MySQLClone01必须在与文件系统资源相同的节点上运行,并且必须在文件系统资源之前启动克隆资源。 `node1执行` ```bash pcs -f clust_cfg resource create mysql_fs01 Filesystem \ device="/dev/drbd0" \ directory="/var/lib/mysql" \ fstype="ext4" ``` ```bash pcs -f clust_cfg constraint colocation add mysql_fs01 with MySQLClone01 \ INFINITY with-rsc-role=Master ``` ```bash pcs -f clust_cfg constraint order promote MySQLClone01 then start mysql_fs01 ``` 为MariaDB服务创建名为mysql_service01的集群资源。告诉群集MariaDB服务必须在与mysql_fs01文件系统资源相同的节点上运行,并且必须首先启动文件系统资源。 `node1执行` ```bash pcs -f clust_cfg resource create mysql_service01 ocf:heartbeat:mysql \ binary="/usr/bin/mysqld_safe" \ config="/etc/my.cnf" \ datadir="/var/lib/mysql" \ pid="/var/lib/mysql/mysql.pid" \ socket="/var/lib/mysql/mysql.sock" \ additional_parameters="--bind-address=0.0.0.0" \ op start timeout=60s \ op stop timeout=60s \ op monitor interval=20s timeout=30s ``` ```bash pcs -f clust_cfg constraint colocation add mysql_service01 with mysql_fs01 INFINITY ``` ```bash pcs -f clust_cfg constraint order mysql_fs01 then mysql_service01 ``` 为虚拟IP 172.16.0.200创建名为mysql_VIP01的集群资源 `node1执行` ```bash pcs -f clust_cfg resource create mysql_VIP01 ocf:heartbeat:IPaddr2 \ ip=172.16.0.200 cidr_netmask=32 \ op monitor interval=30s ``` 当然,虚拟IP mysql_VIP01资源必须与MariaDB资源在同一节点上运行,并且必须在最后一个时启动。这是为了确保在连接到虚拟IP之前已经启动了所有其他资源。 `node1执行` ```bash pcs -f clust_cfg constraint colocation add mysql_VIP01 with mysql_service01 INFINITY ``` ```bash pcs -f clust_cfg constraint order mysql_service01 then mysql_VIP01 ``` 检查配置 `node1执行` ```bash pcs -f clust_cfg constraint ``` ``` Location Constraints: Ordering Constraints: promote MySQLClone01 then start mysql_fs01 (kind:Mandatory) start mysql_fs01 then start mysql_service01 (kind:Mandatory) start mysql_service01 then start mysql_VIP01 (kind:Mandatory) Colocation Constraints: mysql_fs01 with MySQLClone01 (score:INFINITY) (with-rsc-role:Master) mysql_service01 with mysql_fs01 (score:INFINITY) mysql_VIP01 with mysql_service01 (score:INFINITY) ``` ```bash pcs -f clust_cfg resource show ``` ``` Master/Slave Set: MySQLClone01 [mysql_data01] Stopped: [ node1 node2 ] mysql_fs01 (ocf::heartbeat:Filesystem): Stopped mysql_service01 (ocf::heartbeat:mysql): Stopped mysql_VIP01 (ocf::heartbeat:IPaddr2): Stopped ``` 提交修改并查看集群状态 `node1执行` ```bash pcs cluster cib-push clust_cfg ``` ```bash pcs status ``` ``` Cluster name: mysql_cluster Stack: corosync Current DC: node1 (version 1.1.19-8.el7_6.4-c3c624ea3d) - partition with quorum Last updated: Mon Jul 29 06:51:22 2019 Last change: Mon Jul 29 02:49:38 2019 by root via cibadmin on node1 2 nodes configured 5 resources configured Online: [ node1 node2 ] Full list of resources: Master/Slave Set: MySQLClone01 [mysql_data01] Masters: [ node1 ] Slaves: [ node2 ] mysql_fs01 (ocf::heartbeat:Filesystem): Started node1 mysql_service01 (ocf::heartbeat:mysql): Started node1 mysql_VIP01 (ocf::heartbeat:IPaddr2): Started node1 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled ``` 一旦配置提交,Pacemaker将会执行以下操作 - 在集群节点启动DRBD - 选择一个节点提升为主节点 - 在同一个节点挂载文件系统,配置集群IP地址,启动MariaDB - 开始监控资源 通过telenet虚拟IP地址和3306端口,测试MariaDB服务 `client执行` ```bash telnet 172.16.0.200 3306 ``` ``` Trying 172.16.0.200... Connected to 172.16.0.200. Escape character is '^]'. GHost '172.16.0.200' is not allowed to connect to this MariaDB serverConnection closed by foreign host. ``` ### 常用命令汇总 查看集群状态: ```bash pcs status ``` 查看集群当前配置: ```bash pcs config ``` 开机后集群自启动: ```bash pcs cluster enable --all ``` 启动集群: ```bash pcs cluster start --all ``` 查看集群资源状态: ```bash pcs resource show ``` 验证集群配置情况: ```bash crm_verify -L -V ``` 测试资源配置: ```bash pcs resource debug-start resource ``` 设置节点为备用状态: ```bash pcs cluster standby node1 ``` 列出集群属性 ```bash pcs property list ``` 测试corosync成员 ```bash corosync-cmapctl | grep members ``` 查看corosync成员 ```bash pcs status corosync ``` 将集群配置保存到文件 ```bash pcs cluster cib filename ``` 创建资源不应用到集群,写入到文件 ```bash pcs -f testfile1 resource create VirtualIP ocf:heartbeat:IPaddr2 ip=192.168.0.120 cidr_netmask=24 op monitor interval=30s ``` 将文件配置应用到集群 ```bash pcs cluster cib-push filename ``` 备份集群配置 ```bash pcs config backup filename ``` 使用恢复集群配置 ```bash pcs config restore [--local] [filename] #--local只还原当前节点,没有执行filename则读取标准输入 ``` 添加集群节点 ```bash pcs cluster node add node ``` 删除集群节点 ```bash pcs cluster node remove node ``` 显示资源的参数 ```bash pcs resource describe standard:provider:type|type #例如:pcs resource describe ocf:heartbeat:IPaddr2 ``` 设置节点进入待机状态 ```bash pcs cluster standby node | --all ``` 设置节点从待机状态移除 ```bash pcs cluster unstandby node | --all ``` 删除集群配置(`警告:这个命令可永久移除已创建的集群配置`) ```bash pcs cluster stop pcs cluster destroy ``` 参考: https://www.lisenet.com/2016/activepassive-mysql-high-availability-pacemaker-cluster-with-drbd-on-centos-7/ https://linux.cn/article-3963-1.html https://www.howtoforge.com/tutorial/how-to-set-up-nginx-high-availability-with-pacemaker-corosync-on-centos-7/ http://www.alexlinux.com/pacemaker-corosync-nginx-cluster/ https://access.redhat.com/documentation/zh-cn/red_hat_enterprise_linux/7/html/high_availability_add-on_reference/ch-clusteradmin-haar
阅读 212 评论 0 收藏 0
阅读 212
评论 0
收藏 0

第 1 页 / 共 2 页
 
第 1 页 / 共 2 页