MySQL主从服务搭建以及MHA的使用

snail
75 阅读11分钟

本次搭建基于Linux服务器

mysql对应的版本以mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar版本为例

主从架构

安装MySQL时的步骤，以及遇到的一些问题

上传下载好的mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

通过rz命令

解压mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

[root@10-9-133-95 /]# tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-test-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm

通过rpm进行安装，由于MySQL的安装是有顺序的，并且检查系统是否安装Mariadb，因为MySQL与其会有冲突，需要先卸载，然后安装MySQL

[root@10-9-133-95 /]# rpm -qa|grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@10-9-133-95 /]# rpm -qa | grep mariadb-libs | xargs rpm -e --nodeps
[root@10-9-133-95 /]# rpm -qa|grep mariadb
[root@10-9-133-95 /]# rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-common-5.7.28-1.e################################# [100%]
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-libs-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-5.7.28-1.el7################################# [100%]
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-compat-5.7.2################################# [100%]
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-client-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-client-5.7.28-1.e################################# [100%]
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-server-5.7.28-1.e################################# [100%]
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-devel-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-devel-5.7.28-1.el################################# [100%]
[root@10-9-133-95 /]#
[root@10-9-133-95 /]# mysqld --initialize --user=mysql
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# cat /var/log/mysqld.log 
2021-01-29T01:37:26.041572Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-29T01:37:27.536073Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-29T01:37:27.739239Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-29T01:37:27.804791Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8b997259-61d2-11eb-ae5d-5254003b8f44.
2021-01-29T01:37:27.805166Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-29T01:37:28.619917Z 0 [Warning] CA certificate ca.pem is self signed.
2021-01-29T01:37:28.953275Z 1 [Note] A temporary password is generated for root@localhost: id)5+Fzuyga-
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# systemctl start mysqld.service
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2021-01-29 09:40:17 CST; 24s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2859 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 2842 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2861 (mysqld)
CGroup: /system.slice/mysqld.service
└─2861 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jan 29 09:40:16 10-9-133-95 systemd[1]: Starting MySQL Server...
Jan 29 09:40:17 10-9-133-95 systemd[1]: Started MySQL Server.
[root@10-9-133-95 /]# 
[root@10-9-133-95 /]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)

关闭防火墙和禁用防火墙

[root@10-9-133-95 /]# systemctl stop firewalld
[root@10-9-133-95 /]# systemctl disable firewalld.service

rpm 安装MySQL遇到的一些问题

[root@10-9-50-70 ~]# rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
libcrypto.so.10()(64bit) is needed by mysql-community-libs-compat-5.7.28-1.el7.x86_64
libcrypto.so.10(libcrypto.so.10)(64bit) is needed by mysql-community-libs-compat-5.7.28-1.el7.x86_64
libssl.so.10()(64bit) is needed by mysql-community-libs-compat-5.7.28-1.el7.x86_64
libssl.so.10(libssl.so.10)(64bit) is needed by mysql-community-libs-compat-5.7.28-1.el7.x86_64

将rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm更改为 rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm --nodeps --force

  • 在使用mysql -uroot -p登陆时

    [root@10-9-50-70 ~]# mysql -u root -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

    解决：执行 yum install libncurses* ，然后执行mysql -uroot -p 就OK了。

Master服务设置：

对 /etc/my.cnf文件添加设置

# log_bin
# 开启binlog
log_bin=mysql-bin
# 设置服务id
server-id=1
# 参数来控制数据库的binlog刷到磁盘上去。
# 默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。


sync-binlog=1
# 忽略同步的库
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
# 指定同步的库
# binlog-do-db=xxxx

  • 进入MySQL，设置授权

    mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000002 | 869 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)

Slave服务设置

对/etc/my.cnf配置文件设置

# log_bin
server-id=2
# 设置中继日志文件名
relay_log=mysql-relay-bin
# 设置只读
read_only=1

进入MySQL终端进行设置

mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to master_host='117.50.23.242',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000019',master_log_pos=1464;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 117.50.23.242
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 869
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
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: 869
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID: 
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: 
1 row in set (0.00 sec)

从库连接主库，一定在从库下能访问主库，否则数据同步失败；

以下是从库无法连接主库，出现的错误；
2021-01-29T02:39:01.029201Z 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000002' at position 869, relay log './mysql-relay-bin.000001' position: 4
2021-01-29T02:40:01.087339Z 3 [ERROR] Slave I/O for channel '': error connecting to master 'root@117.50.23.242:3306' - retry-time: 60 retries: 1, Error_code: 2003
2021-01-29T02:42:01.103326Z 3 [ERROR] Slave I/O for channel '': error connecting to master 'root@117.50.23.242:3306' - retry-time: 60 retries: 2, Error_code: 2003
2021-01-29T02:44:01.159575Z 3 [ERROR] Slave I/O for channel '': error connecting to master 'root@117.50.23.242:3306' - retry-time: 60 retries: 3, Error_code: 2003
2021-01-29T02:46:01.186324Z 3 [ERROR] Slave I/O for channel '': error connecting to master 'root@117.50.23.242:3306' - retry-time: 60 retries: 4, Error_code: 2003
2021-01-29T02:48:01.202331Z 3 [ERROR] Slave I/O for channel '': error connecting to master 'root@117.50.23.242:3306' - retry-time: 60 retries: 5, Error_code: 2003

主从部署必要的条件

  • 从库服务器能连通主库
  • 主库开启binlog日志（设置log-bin参数）
  • 主从server-id不同

设置半同步复制

进入master的MySQL查看是否开启动态加载

mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+

  • 查看插件，是否安装semi相关的插件

    mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)


mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)

进入slave的MySQL同样设置

**注意插件名称不一样**

mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.08 sec)
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

设置并行复制

Master 库相关参数设置

mysql> show variables like '%binlog_group%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.01 sec)
mysql> set global binlog_group_commit_sync_delay=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> set global binlog_group_commit_sync_no_delay_count=100;
Query OK, 0 rows affected (0.00 sec)

Slave库相关参数配置，直接配置到my.cnf中

relay_log_recovery=1
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=8
master_info_repository=TABLE
relay_log_info_repository=TABLE

双主模式

Master 服务配置

  • 在主从模式下的Master服务中，修改/etc/my.cnf配置文件，添加以下参数

    # 配置双主模式，相关参数
relay_log=mysql-relay-bin
log_slave_updates=1
# 配置双主双写，主键使用的参数,id生成规则1、3、5..
#auto_increment_offset=1
#auto_increment_increment=2

  • 重启MySQL服务

    systemctl restart mysqld

 

MHA的搭建与使用

image.png

 

下载mha4mysql-node-0.58和mha4mysql-manager-0.58

安装

然后在所有mysql节点安装mha4mysql-node-0.58

yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

其中一台服务安装Manager节点即mha4mysql-manager-0.58

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes


yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

三台服务之间，服务器相互通信

# 在作为manager服务下进行互信操作
rm -rf /root/.ssh 
ssh-keygen
cd /root/.ssh 
mv id_rsa.pub authorized_keys
# 另外一台服务242
scp  -r  /root/.ssh  10.0.0.242:/root
# 另外一台服务44
scp  -r  /root/.ssh  10.0.0.44:/root

在三台服务都进行互信验证

ssh 10.0.0.74 date
ssh 10.0.0.242 date
ssh 10.0.0.44 date

若不提示需要输入密码，则互信成功

在作为master服务的数据库中创建需要的用户

grant all privileges on *.* to root@'10.0.0.%' identified by 'root';

MHA Manager配置文件创建

  • 创建配置文件目录

    mkdir -p /etc/mha

  • 创建日志目录

    mkdir -p /var/log/mha/app1

  • 创建配置文件

    vi /etc/mha/app1.cnf
将以下内容放入，app1.cnf文件中


[server default]
# MHA的工作日志设置
manager_log=/var/log/mha/app1/manager
# MHA工作目录
manager_workdir=/var/log/mha/app1 
# 主库的binlog目录
master_binlog_dir=/data/binlog 
# 监控用户（监控各节点转态
user=root
# 监控用户密码
password=root
# 心跳检测的间隔时间
ping_interval=2
# 主从复制用户(上文授权的用户)
repl_user=root
# 复制用户密码
repl_password=root
# ssh互信的用户
ssh_user=root


[server1]
hostname=10.0.0.242
port=3306
candidate_master=1 
[server2] 
hostname=10.0.0.44
port=3306
candidate_master=1
[server3]
hostname=10.0.0.237
port=3306
candidate_master=1

状态检查

masterha_check_ssh   --conf=/etc/mha/app1.cnf

image.png

masterha_check_repl  --conf=/etc/mha/app1.cnf

image.png

  • 在进行此操作时遇到的一些问题

    • 服务与服务之间的通信失败；(发现服务外网没有开放3306的端口，导致服务之间通信失败)
    • slave库的my.cnf配置信息不一致；（要求是，除了server-id的值不一样，其余都一样）
    Mon Apr 13 20:02:15 2015 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/ServerManager.pm, ln546] Replication
filtering check failed on dbsrv3(192.168.1.3:3306)! All slaves must have same replication filtering rules.
Check SHOW SLAVE STATUS output and set my.cnf correctly.
Mon Apr 13 20:02:15 2015 - [warning] Bad Binlog/Replication filtering rules:

启动MHA-Manager

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

###############启动参数说明###############
--conf=/etc/mha/app1.cnf #指定MHA程序配置文件，
如需要管理多个该可用集群，可启动多次，指定不同的配置文件
--remove_dead_master_conf #如果主节点宕机，将主节点从配置文件（高可用集群）中踢出
--ignore_last_failover #MHA默认不允许在短时间内多次切换（8h）
如果由此参数，则跳过这个设定

查看MHA监控状态

masterha_check_status --conf=/etc/mha/app1.cnf

image.png

常用的mysql脚本

  • 清空master库的slave库（双主模式，master既是主库又是从库）

    stop slave;
reset slave;
reset slave all
avatar
文章
阅读
粉丝