mysql集群

最新推荐文章于 2024-08-31 23:40:08 发布
最新推荐文章于 2024-08-31 23:40:08 发布
阅读量1.1k 收藏 7
点赞数 8
文章标签： mysql adb 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Dream_of_falling/article/details/141752748
版权

一、Mysql集群技术

两个linux7主机，mysql1、2

172.25.254.10、20

[root@mysql1 ~]# vmset.sh eth0 172.25.254.10 mysql1.timinglee.org

[root@mysql1 ~]# yum install lrzsz.x86_64 -y

将包上传进linux中。解压

mysql1、2

[root@mysql1 ~]# tar zxf mysql-boost-5.7.44.tar.gz

[root@mysql1 ~]# cd mysql-5.7.44/

[root@mysql1 mysql-5.7.44]# yum install cmake.x86_64 -y

[root@mysql1 mysql-5.7.44]# yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64

编译

[root@mysql1 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/

[root@mysql1 mysql-5.7.44]# make

[root@mysql1 mysql-5.7.44]# make install

1.1 安装启动配置—所有mysql都一致

[root@mysql1 mysql-5.7.44]# cd /usr/local/mysql/support-files/

[root@mysql1 support-files]# cp mysql.server /etc/init.d/mysqld

[root@mysql1 support-files]# vim ~/.bash_profile

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

[root@mysql1 support-files]# source ~/.bash_profile

[root@mysql1 support-files]# useradd -s /sbin/nologin -M mysql

[root@mysql1 support-files]# mkdir -p /data/mysql

[root@mysql1 support-files]# chown mysql.mysql /data/mysql/

[root@mysql1 support-files]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

[root@mysql1 support-files]# mysqld --initialize --user=mysql

2024-08-24T07:06:08.117366Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-24T07:06:08.335322Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-24T07:06:08.366421Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-24T07:06:08.374487Z 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: 5639d40a-61e7-11ef-9e3d-000c29fd8b36.
2024-08-24T07:06:08.375245Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-24T07:06:08.492622Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-24T07:06:08.492637Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-24T07:06:08.495995Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-24T07:06:08.544314Z 1 [Note] A temporary password is generated for root@localhost: tv6(vU=7vwXg  #此处为主默认密码，30N+>JEKnTtg为从默认密码

[root@mysql1 support-files]# /etc/init.d/mysqld start

Starting MySQL.Logging to '/data/mysql/mysql1.org.err'.
 SUCCESS!

[root@mysql1 support-files]# chkconfig mysqld on

注：此处报错是因为mysql的服务没关

配置mysql的权限表

[root@mysql1 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:tv6(vU=7vwXg     #输入当前密码，开始时输入默认密码，密文

The existing password for the user account root has expired. Please set a new password.

New password:123456         #输入新密码

Re-enter new password:123456            # 重复密码

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: no     #是否启用密码插件
Using existing password for root.

#是否要重置密码
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

#是否删除匿名用户
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

#是否禁止root用户远程登录
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

#删除测试数据库和访问权限
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

#是否重新加载权限表
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

测试

[root@mysql1 ~]# mysql -uroot -p123456

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit

Bye

1.2 mysql主从复制

  • 概念：当主的配置修改时，从mysql也会出现相应的配置，而从mysql修改配置时，主mysql则不会出现从mysql新添加的配置

1.2.1 对于主mysql的配置

[root@mysql1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1

[root@mysql1 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@mysql1 ~]# mysql -p123456

mysql> CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘lee’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON . TO repl@‘%’;
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.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

[root@mysql1 ~]# cd /data/mysql/

[root@mysql1 mysql]# mysqlbinlog mysql-bin.000001 -vv

1.2.2 对于从mysql的配置

[root@mysql2 support-files]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2

[root@mysql2 support-files]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’,MASTER_USER=‘repl’,MASTER_PASSWORD=‘lee’,MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=595;

注意：MASTER_LOG__FILE=‘mysql-bin.000001’,MASTER_LOG_POS=595;要与主服务器的mysql> SHOW MASTER STATUS;这条命令的查询结果一致。

mysql> start slave;

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 595
               Relay_Log_File: mysql2-relay-bin.000004
                Relay_Log_Pos: 320
        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: 595
              Relay_Log_Space: 694
              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: 5639d40a-61e7-11ef-9e3d-000c29fd8b36
             Master_Info_File: /data/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)

测试

进入mysql进行配置

[root@mysql1 ~]# mysql -uroot -p123456

查看是否有lee数据库

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| lee                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

使用lee数据库

mysql> use lee;
Database changed

在lee库中建表，建立id，username，password字段

mysql> create table userlist (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> username varchar(255) NOT NULL,
    -> password varchar(255) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

在主mysql中的lee中的userlist表中插入值

[root@mysql1 ~]# mysql -uroot -p123456 -e “INSERT INTO lee.userlist VALUES(‘1’,‘user3’,‘123’);”

[root@mysql1 ~]# mysql -uroot -p123456 -e ‘select * from lee.userlist;’

mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | user3    | 123      |
+----+----------+----------+

在从mysql中检查看是否出现该配置

[root@mysql2 support-files]# mysql -uroot -p123456 -e ‘select * from lee.userlist;’

mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | user3    | 123      |
+----+----------+----------+

在slave阶段中默认情况下是开启了写功能的，但是建议关闭slave节点的写功能来保证数据一致性

[root@mysql2 support-files]# vim /etc/mysql.cnf

[mysqld]
super_read_only=on

1.2.3 添加一台新的从mysql

[root@mysql3 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=3

[root@mysql3 ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS!

在三台mysql机中都配置互相免密，此处只展示mysql3的免密

[root@mysql3 ~]# ssh-keygen

[root@mysql3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.10

[root@mysql3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.20

建立一个lee数据库，并从master节点备份数据

[root@mysql3 ~]# mysql -uroot -p123456

mysql> CREATE DATABASE lee;

mysql> exit

[root@mysql3 ~]# mysqldump -uroot -p123456(此处为自己的密码) lee > lee.sql(自己得有lee数据库)

在lee库中建立userlist表

[root@mysql3 ~]# mysql -uroot -p123456

mysql> use lee

mysql> create table userlist (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> username varchar(255) NOT NULL,
-> password varchar(255) NOT NULL);

mysql> exit

利用master节点中备份出来的lee.sql在slave2中拉平数据

[root@mysql3 ~]# mysql -uroot -p123456 lee <lee.sql

[root@mysql3 ~]# mysql -uroot -p123456 -e “select * from lee.userlist;”

mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | user3    | 123      |
+----+----------+----------+

在主mysql中查询日志

[root@mysql1 ~]# mysql -uroot -p123456 -e “SHOW MASTER STATUS;”

mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

[root@mysql3 ~]# mysql -uroot -p123456

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’,MASTER_USER=‘repl’,MASTER_PASSWORD=‘lee’,MASTER_LOG_FILE=‘mysql-bin.000002’,MASTER_LOG_POS=154;

mysql> start slave;

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 154
              Relay_Log_Space: 528
              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: 5639d40a-61e7-11ef-9e3d-000c29fd8b36
             Master_Info_File: /data/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)

测试

在主mysql中添加数据

[root@mysql1 ~]# mysql -uroot -p123456 -e ‘select * from lee.userlist;’

在新添加的mysql3中查看

[root@mysql3 ~]# mysql -uroot -p123456 -e ‘select * from lee.userlist;’

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | user3    | 123      |
|  2 | user6    | 1234     |
+----+----------+----------+

1.3 延迟复制

  • 概念：当主mysql修改数据后，从mysql会等待一段时间才复制数据
  • 作用：当主mysql误操作，可以在从mysql进行数据备份

配置

[root@mysql3 ~]# mysql -uroot -p123456

mysql> STOP SLAVE SQL_THREAD;

mysql> CHANGE MASTER TO MASTER_DELAY=60; #配置延迟60s

mysql> START SLAVE SQL_THREAD;

mysql> SHOW SLAVE STATUS\G;

Master_UUID: 5639d40a-61e7-11ef-9e3d-000c29fd8b36
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 60    #此处表示配置成功，延迟60s复制
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400

在这里插入图片描述

1.4 慢查询查询

  • 作用：当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个 SQL语句就是需要优化的，开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

配置

查看

[root@mysql1 ~]# mysql -uroot -p123456

mysql> SHOW variables like “slow%”;

+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | OFF                         |
| slow_query_log_file | /data/mysql/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.01 sec)

开启慢查询日志

mysql> SET GLOBAL slow_query_log=ON;

mysql> SET long_query_time=4;

mysql> SHOW VARIABLES like “long%”;

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES like “slow%”;

+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | ON                          |
| slow_query_log_file | /data/mysql/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> exit

查看慢查询日志文件

[root@mysql1 ~]# cat /data/mysql/mysql1-slow.log

测试

mysql> select sleep (10);

+------------+
| sleep (10) |
+------------+
|          0 |
+------------+
1 row in set (10.00 sec)

mysql> exit

[root@mysql1 ~]# cat /data/mysql/mysql1-slow.log

/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2024-08-25T06:24:42.689095Z
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 10.001840  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1724567082;
select sleep (10);
# Time: 2024-08-25T06:24:56.818017Z
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 10.000315  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1724567096;
select sleep (10);

1.5 mysql的并行复制—从mysql中配置

[root@mysql2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@mysql2 ~]# /etc/init.d/mysqld start

​ Starting MySQL SUCCESS!

二、半同步模式

在这里插入图片描述

配置gtid

主mysql

[root@mysql1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1

gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0

[root@mysql1 ~]# /etc/init.d/mysqld restart

Shutting down MySQL........... SUCCESS! 
Starting MySQL. SUCCESS!

所有从mysql

[root@mysql2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2

log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0

[root@mysql2 ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS!

2.1 从mysql开启与停止gtid

关闭

[root@mysql3 ~]# mysql -p123456

mysql> stop slave;

开启

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’, MASTER_USER=‘repl’,MASTER_PASSWORD=‘lee’, MASTER_AUTO_POSITION=1;

mysql> start slave;

*************************** 1. row ***************************
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1   #表示开启gtid
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

2.2 启用半同步模式

主mysql

[root@mysql1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1

gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
symbolic-links=0

安装半同步插件

[root@mysql1 ~]# mysql -p123456

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;

查看插件安装情况

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; #开启

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| 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> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| 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     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

mysql> show plugins；

从mysql

[root@mysql2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2

log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1   #开启半同步
symbolic-links=0

下载半同步插件，并重启io线程

[root@mysql2 ~]# mysql -p123456

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

检查配置

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

测试

主mysql

mysql> insert into lee.userlist values (‘3’,‘user4’,‘123’);
Query OK, 1 row affected (0.03 sec)

mysql> SHOW STATUS LIKE ‘Rpl_semi_sync%’;

+--------------------------------------------+-------+
| 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             | 1     |
| Rpl_semi_sync_master_no_times              | 0     |

#未同步数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      | 676   |
| Rpl_semi_sync_master_tx_wait_time          | 676   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |

#已同步数1
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+

模拟故障

所有从mysql

mysql> STOP SLAVE IO_THREAD;

主mysql

mysql> insert into lee.userlist values (‘6’,‘user5’,‘555’);

Query OK, 1 row affected (10.01 sec) #10s超时，同步失败

mysql> SHOW STATUS LIKE ‘Rpl_semi%’;

+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     | #一笔数据为同步
#自动转为异步模式，当slave恢复后会自动恢复
| Rpl_semi_sync_master_status                | OFF   | 
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 676   |
| Rpl_semi_sync_master_tx_wait_time          | 676   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

三、mysql高可用之组复制(MGR)

组复制流程

在这里插入图片描述

注：节点数量不能超过9台

3.1 实现mysql组复制

mysql1

[root@mysql1 ~]# rm -fr /data/mysql/ #清空配置，还原环境

[root@mysql1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=1

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON

log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

group_replication_start_on_boot=off
group_replication_local_address="172.25.254.10:33061"
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1

[root@mysql1 ~]# mysqld --user=mysql --initialize

2024-08-25T07:24:10.521301Z 1 [Note] A temporary password is generated for root@localhost: IbZqfA6_p-yo     ptQk#Et)o7=>

[root@mysql1 ~]# /etc/init.d/mysqld start

Starting MySQL.Logging to '/data/mysql/mysql1.org.err'.
.. ERROR! The server quit without updating PID file (/data/mysql/mysql1.org.pid).
#发现启动失败

查看日志查找错误在哪

[root@mysql1 ~]# cat /data/mysql/mysql1.org.err

2024-08-25T07:24:21.077842Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
2024-08-25T07:24:21.077844Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2024-08-25T07:24:21.077849Z 0 [ERROR] Aborting

#表示3306端口被占用

[root@mysql1 ~]# ps aux | grep mysqld

root       2285  0.0  0.0 113412  1592 pts/0    S    Aug24   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql1.org.pid
mysql      2474  0.0  7.2 2155404 203988 pts/0  Sl   Aug24   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mysql1.org.err --pid-file=/data/mysql/mysql1.org.pid --socket=/data/mysql/mysql.sock
root       3084  0.0  0.0 112808   964 pts/0    S+   00:44   0:00 grep --color=auto mysqld

#发现3306端口确实正在使用

[root@mysql1 ~]# kill 2285

[root@mysql1 ~]# kill 2474

[root@mysql1 ~]# /etc/init.d/mysqld start

[root@mysql1 ~]# mysql_secure_installation

​ #配置新密码为123456

[root@mysql1 ~]# mysql -uroot -p123456 -e “alter user root@localhost identified by ‘lee’;”

​ #将密码修改为lee

[root@mysql1 ~]# mysql -plee

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@‘%’ IDENTIFIED BY ‘lee’;

mysql> GRANT REPLICATION SLAVE ON . TO rpl_user@‘%’;

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user’, MASTER_PASSWORD=‘lee’ FOR CHANNEL ‘group_replication_recovery’;

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

mysql> START GROUP_REPLICATION;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 058e9840-62b3-11ef-b7fe-000c29fd8b36 | mysql1.org  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

在复制配置文件到myql-node20和mysql-node30

[root@mysql1 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf

[root@mysql1 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf

mysql2、3

[root@mysql2 ~]# rm -fr /data/mysql/

[root@mysql2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2                           #唯一标识，修改为2，mysql3修改为3

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON

log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

group_replication_start_on_boot=off

group_replication_local_address="172.25.254.20:33061"    #修改为本地主机地址
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1

[root@mysql2 ~]# mysqld --user=mysql --initialize

2024-08-25T08:13:17.665281Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-25T08:13:17.707142Z 1 [Note] A temporary password is generated for root@localhost: ptQk#Et)o7=>

[root@mysql2 ~]# ps aux | grep mysqld

root       2454  0.0  0.0 113412  1596 pts/0    S    Aug24   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql2.org.pid
mysql      2643  0.0  7.2 1933252 204272 pts/0  Sl   Aug24   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mysql2.org.err --pid-file=/data/mysql/mysql2.org.pid --socket=/data/mysql/mysql.sock
root       3275  0.0  0.0 112808   964 pts/0    R+   01:14   0:00 grep --color=auto mysqld

[root@mysql2 ~]# kill 2454

[root@mysql2 ~]# kill 2643

[root@mysql2 ~]# /etc/init.d/mysqld start

[root@mysql2 ~]# mysql_secure_installation

​ #配置新密码为123456

[root@mysql2 ~]# mysql -uroot -p123456 -e “alter user root@localhost identified by ‘lee’;”

[root@mysql2 ~]# mysql -plee

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@‘%’ IDENTIFIED BY ‘lee’;

mysql> GRANT REPLICATION SLAVE ON . TO rpl_user@‘%’;

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user’, MASTER_PASSWORD=‘lee’ FOR CHANNEL ‘group_replication_recovery’;

mysql> START GROUP_REPLICATION;

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 058e9840-62b3-11ef-b7fe-000c29fd8b36 | mysql1.org  |        3306 | ONLINE       |
| group_replication_applier | e227d3cc-62b9-11ef-8094-000c29ba23e8 | mysql2.org  |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

测试

mysql1

[root@mysql1 ~]# mysql -plee

mysql> CREATE DATABASE lee;

建userlist表

mysql> CREATE TABLE lee.userlist(
    -> username VARCHAR(10) PRIMARY KEY NOT NULL,
    -> password VARCHAR(50) NOT NULL);

mysql> INSERT INTO lee.userlist VALUES (‘user1’,‘111’);

mysql> SELECT * FROM lee.userlist;

+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

3.2 mysql路由

在这里插入图片描述

mysql Router

是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路 由。 利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略 来处理连接，使其连接到正确的MySQL数据库服务器

3.2.1 mysql路由配置

再开一台主机mysql-router

在这里插入图片描述

将该包导入linux系统中，并解压

[root@mysql_router ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm

配置mysql路由

[root@mysql_router ~]# vim /etc/mysqlrouter/mysqlrouter.conf

[keepalive]
interval = 60

[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available

[root@mysql_router ~]# systemctl start mysqlrouter.service

测试

建立测试用户

mysql> CREATE USER lee@‘%’ IDENTIFIED BY ‘lee’;

mysql> GRANT ALL ON lee.* TO lee@‘%’;

查看调度效果，其他从mysql类同

[root@mysql-node10 ~]# watch -1 lsof -i :3306

COMMAND  PID   USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mysqld   9879  mysql  22u  IPv6  56697  0t0     TCP *:mysql (LISTEN)

[root@mysql_router ~]# mysql -ulee -plee -h 172.25.254.40 -P 7001

四、mysql高可用之MHA

在这里插入图片描述

MHA的作用：故障切换和主从复制的软件

MHA工作原理

  • 目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群必须最少有3台数据库服务器， 一主二从，即一台充当Master，台充当备用Master，另一台充当从库。
  • MHA Node 运行在每台 MySQL 服务器上
  • MHAManager 会定时探测集群中的master 节点
  • 当master 出现故障时，它可以自动将最新数据的slave 提升为新的master
  • 然后将所有其他的slave 重新指向新的master，VIP自动漂移到新的master。
  • 整个故障转移过程对应用程序完全透明。

4.1 搭建主两从架构

主mysql

先暂停服务，并还原环境

[root@mysql1 ~]# /etc/init.d/mysqld stop

[root@mysql1 ~]# rm -fr /data/mysql/*

[root@mysql1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=1
log_bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON

[root@mysql1 ~]# mysqld --user mysql --initialize

2024-08-27T02:28:17.084996Z 1 [Note] A temporary password is generated for root@localhost: 9/wX=Y0r%_c<

[root@mysql1 ~]# /etc/init.d/mysqld start

Starting MySQL.Logging to '/data/mysql/mysql1.org.err'.
 SUCCESS!

[root@mysql1 ~]# mysql_secure_installation

​ #新密码为123456

[root@mysql1 ~]# mysql -p123456

mysql> CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘lee’;

mysql> GRANT REPLICATION SLAVE ON . TO repl@‘%’;

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

所有从mysql

[root@mysql2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

[root@mysql2 ~]# rm -fr /data/mysql/*

[root@mysql2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2

gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
log_bin=mysql-bin

[root@mysql2 ~]# mysqld --user mysql --initialize

2024-08-27T02:35:45.687903Z 1 [Note] A temporary password is generated for root@localhost: guK1Tyy%!p9F

[root@mysql2 ~]# /etc/init.d/mysqld start

[root@mysql2 ~]# mysql_secure_installation

​ 新密码123456

[root@mysql2 ~]# mysql -p123456

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’, MASTER_USER=‘repl’,MASTER_PASSWORD=‘lee’, MASTER_AUTO_POSITION=1;

mysql> start slave;

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

mysql> SHOW STATUS LIKE ‘Rpl_semi_sync%’;

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

4.2 MHA配置

MHA

新开一台主机MHA：172.25.254.40

在这里插入图片描述

[root@mysql_mha ~]# unzip MHA-7.zip

[root@mysql_mha ~]# cd MHA-7/

在这里插入图片描述

[root@mysql_mha MHA-7]# yum install *.rpm -y

[root@mysql_mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.10:/mnt

The authenticity of host '172.25.254.10 (172.25.254.10)' can't be established.
ECDSA key fingerprint is SHA256:E3qE8JvU9z/9Q6iQNWX4FB2C7Of1r+MKoHRNklKH9ow.
ECDSA key fingerprint is MD5:26:47:a1:e9:a2:08:3a:f9:fa:eb:2d:a8:99:11:bf:6b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.254.10' (ECDSA) to the list of known hosts.
root@172.25.254.10's password: 123456
mha4mysql-node-0.58-0.el7.centos.noarch.rpm                          100%   35KB  18.2MB/s   00:00

[root@mysql_mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.20:/mnt

[root@mysql_mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.30:/mnt

其他MySQL10、20、30

[root@mysql1 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

4.3 配置MHA的管理环境

MHA

查看配置目录和配置文件

[root@mysql_mha ~]# masterha_manager --help

因为当前只有一套主从，所以只需要写一个配置文件即可

生成配置文件

[root@mysql_mha ~]# mkdir /etc/masterha

[root@mysql_mha ~]# cd MHA-7/

[root@mysql_mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz

[root@mysql_mha MHA-7]# cd mha4mysql-manager-0.58/samples/conf/

[root@mysql_mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf

编写配置文件

需要将各个主机之间配置免密

[root@mysql_mha conf]# vim /etc/masterha/app1.cnf

[server default]
user=root
password=lee
ssh_user=root
repl_user=repl
repl_password=lee
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s 172.25.254.11
ping_interval=3

# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
[server default]
manager_workdir=/etc/masterha
manager_log=/var/etc/masterha/manager.log

[server1]
hostname=172.25.254.10
candidate_master=1
check_repl_delay=0

[server2]
hostname=172.25.254.20
candidate_master=1
check_repl_delay=0

[server3]
hostname=172.25.254.30
no_master=1

#详情键Mysql集群技术.pdf文件

检测网络及ssh免密

[root@mysql_mha conf]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

mysql1

[root@mysql_mha conf]# mysql -p123456

mysql> GRANT ALL ON . TO root@‘%’ identified by ‘123456’;

mha

执行检测

[root@mysql_mha conf]# masterha_check_repl --conf=/etc/masterha/app1.cnf

4.4 MHA的故障切换—重看，重做

4.4.1 未发生故障手动切换

MHA

前提：主mysql还在正常运行

[root@mysql_mha ~]# masterha_master_switch \
> --conf=/etc/masterha/app1.cnf \
> --master_state=alive \
> --new_master_host=172.25.254.20 \
> --new_master_port=3306 \
> --orig_master_is_new_slave \
> --running_updates_limit=10000

[root@mysql_mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

[root@mysql_mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

测试

模拟故障

[root@mysql_mha ~]# /etc/init.d/mysqld stop

在mha和主MySQL做故障切换

[root@mysql_mha ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.12 --dead_master_port=3306 --new_master_host=192.168.56.11 --new_master_port=3306 --ignore_last_failover

恢复故障mysql节点

[root@mysql_mha ~]# /etc/init.d/mysqld start

[root@mysql_mha ~]# mysql -p123456

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’, MASTER_USER=‘repl’,MASTER_PASSWORD=‘123456’,MASTER__AUTO_POSITION=1;

mysql> show slave\G

测试主两从是否正常

[root@mysql_mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

4.4.2 自动切换

MHA

[root@mysql_mha ~]# rm -fr app1.failover.complete

[root@mysql_mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf

[root@mysql_mha ~]# cat /etc/masterha/manager.log

mysql2

恢复故障节点

[root@mysql2 mysql-5.7.44]# /etc/init.d/mysqld start

[root@mysql2 mysql-5.7.44]# mysql -p123456

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’, MASTER_USER=‘repl’,MASTER_PASSWORD=‘123456’, MASTER_AUTO_POSITION=1；

[root@mysql_mha ~]# rm -rf app1.failover.complete manager.log

4.5 为MHA添加VIP功能

mha

在这里插入图片描述

[root@mysql_mha ~]# ls

anaconda-ks.cfg     master_ip_online_change  MHA-7.zip     mysql-boost-5.7.44.tar.gz
master_ip_failover  MHA-7                    mysql-5.7.44  original-ks.cfg

[root@mysql_mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/

[root@mysql_mha ~]# chmod +x /usr/local/bin/master_ip_*

[root@mysql_mha ~]# vim /usr/local/bin/master_ip_failover

my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

[root@mysql_mha ~]# vim /usr/local/bin/master_ip_online_change

my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;

[root@mysql_mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf & 启动监控程序

musql1

[root@mysql1 ~]# ip a a 172.25.254.100/24 dev eth0

测试-模拟故障

[root@mysql1 ~]# /etc/init.d/mysqld stop

[root@mysql_mha masterha]# cat manager.log

[root@mysql2 mysql]# /etc/init.d/mysqld start

mysql> CHANGE MASTER TO MASTER_HOST=‘172.25.254.10’, MASTER_USER=‘repl’,MASTER_PASSWORD=‘123456’, MASTER_AUTO_POSITION=1

[root@mysql_mha masterha]# rm -rf app1.failover.complete manager.log

[root@mysql_mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

[root@mysql1 ~]# ip a

Dream_of_falling
关注
  • 8
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql集群部署
zhengzhen1205的博客
10-11 3453
Mysql集群部署
mysql学习之mysql集群
热门推荐
liushangzaibeijing的博客
04-12 3万+
本片博文是笔者学习《高性能mysql》一书的第一篇学习笔记，对应书籍章节为第10章。主要介绍了mysql集群部署方案主从集群部署。集群方案、主从同步原理、复制类型、docker安装mysql集群示例。
部署MySQL集群
m0_61431263的博客
07-05 657
MySQL集群可以通过多种方式实现，包括MySQL Cluster（NDB Cluster）、MySQL Group Replication（MGR）、以及使用第三方中间件如ProxySQL、MaxScale等结合多个MySQL实例进行集群管理。本文将以MySQL Group Replication为例进行说明。MySQL Group Replication（MGR）是MySQL 5.7.17及以上版本引入的一个高可用性和数据一致性解决方案。
MySQL集群方案
fupinghaohao的博客
05-23 8270
文中的【// TODO】都是待完成的任务。 1. 简介 1). 集群的好处 ① 高可用性：故障检测及迁移，多节点备份。 ② 可伸缩性：新增数据库节点便利，方便扩容。 ③ 负载均衡：切换某服务访问某节点，分摊单个节点数据库压力。 2). 高可用架构需要考虑以下几个方面 ① 如果数据库发生了宕机或者意外中断等故障，能尽快恢复数据库的可用性，尽可能的减少停机时间，保证业务不会因为数据库的故障而中断。 ② 用作备份、只读等功能的【非主节点】的数据应该和主节点的数据实时或者最终保持一致。 ③ 当
MySQL 集群
一个写了10年bug的程序员日常笔记。
07-06 739
MySQL 集群有多种类型，每种类型都有其特定的用途和优势。
MySQL集群
发现问题，面对问题，分析问题，解决问题，总结问题
02-25 2103
（一）介绍 原理 MySQL集群是一个无共享的、分布式节点架构的存储方案，其目的是提供容错性和高性能.是MySQL适合于分布式计算环境的高可用、高性能版本，它采用了NDB Cluster存储引擎 MySQL集群主要包括三个组成部分：管理节点、数据节点、SQL节点 管理节点（MGM）：管理MySQL Cluster内的其他节点，提供配置数据，启动并停止节点、运行备份等 数据节点：保存Cluster的...
MySQL集群、Redis集群、RabbitMQ集群
云想衣裳花想容，春风拂槛露华浓
07-15 1361
MySQL-MMM 是 Master-Master Replication Manager for MySQL（mysql 主主复制管理器）的简称。脚本）。MMM 基于 MySQL Replication 做的扩展架构，主要用来监控 mysql 主主复制并做失败转移。其原理是将真实数据库节点的 IP（RIP）映射为虚拟 IP（VIP）集。
mysql集群搭建
HoPE_er的博客
06-29 2992
mysql搭建集群，主从关系、主主关系
Mysql集群
weixin_51779902的博客
05-17 895
本文包括Mysql binlog日志恢复，实现Mysql主从复制，读写分离等技术
Docker部署Mysql集群的实现
01-09
常见MySQL集群方案 　mysql 集群方案介绍，建议使用pxc，因为弱一致性会有问题，比如说a节点数据库显示我购买成功，b 节点数据库显示没有成功，这就麻烦了，pxc 方案是在全部节点都写入成功之后才会告诉你成功，是可...
MYSQL 集群
07-10
MYSQL 集群 MYSQL 集群是大型网站架构的设计方案的核心组件之一，旨在提高数据库的高可用性和可扩展性。在本文中，我们将详细介绍 MYSQL 集群的配置和实现过程。 在开始之前，我们需要了解 MYSQL 集群的基本概念。...
centos7搭建mysql集群
05-15
### CentOS 7 搭建 MySQL 集群详解 #### 一、概述 随着业务规模的扩大，单一数据库服务器往往难以满足高并发、大数据量的需求。为了提高系统的可用性和性能，采用 MySQL 集群成为一种常见的解决方案。本文将详细...
centos7部署mysql集群1
08-08
在本篇中，我们将深入探讨如何在CentOS7环境下部署MySQL集群，具体为1个管理节点加2个数据节点和2个SQL节点的配置。首先，我们从环境清理和准备工作开始，然后逐步进行软件安装、配置以及启动服务。 1. **环境清理...
mysql-day02
m0_64370841的博客
08-29 1675
【代码】mysql-day02。
如何在 MySQL 中使用 Socket 身份验证 ？
xiaochong0302的博客
08-31 309
在 MySQL 和 MariaDB 中，可以使用 socket 身份验证，而不是传统的用户名和密码组合。这种方法既安全又方便，因为它使用的是操作系统的用户凭证。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 885
MySQL——子查询（5）带比较运算符的子查询
【MySQL】索引使用规则——（覆盖索引，单列索引，联合索引，前缀索引，SQL提示，数据分布影响，查询失效情况）
最新发布
YYDsis的博客
08-31 796
一.索引使用规则 ※.验证索引效率提升 1.覆盖索引——查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到2.单列索引＆联合索引3.前缀索引——解决冗长字符串与索引问题【1】前缀索引＆索引选择性的介绍【2】前缀索引创建演示：5.SQL提示——指定某个索引/忽略索引/强制索引 6.数据分布影响——MySQL自我评估7.查询失效的几种情况【1】违背——最左前缀法则(联合索引)【2】范围查询右侧失效【3】用or分割开的条件，or后面没索引，所有索引失效【4】索引列上进行运算操作，索引
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值