数据库mysql集群主从、高可用MGR、MHA技术详解

strongjcl

已于 2024-08-26 17:42:40 修改

阅读量1k

点赞数 9

分类专栏：云原生文章标签：数据库 mysql

于 2024-08-26 17:36:46 首次发布

本文链接：https://blog.youkuaiyun.com/strongjcl/article/details/141425527

版权

一、安装数据库mysql步骤

环境：红帽7.9系统

安装依赖

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 -y

将下载的MySQL软件包解压并cd到mysql的目录下

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

[root@mysql-node10 ~]# cd /root/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

make -j2 (这里的数字要等于或小于你的虚拟机上的内核数量)

make install

二、部署mysql

切换到mysql软件目录下

[root@pxe ~]# cd /usr/local/mysql/

创建一个数据库的用户

[root@pxe mysql]# useradd -s /sbin/nologin -M mysql

创建mysql的数据目录

[root@pxe mysql]# mkdir /data/mysql -p

给数据目录赋权

[root@pxe mysql]# chown mysql.mysql -R /data/mysql

生成启动脚本

[root@pxe mysql]# cd support-files/

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

编辑配置文件

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

[mysqld]

datadir=/data/mysql   #指定数据目录

socket=/data/mysql/mysql.sock        #指定套接字

symbolic-links=0   #数据只能存放到数据目录中，禁止链接到数据目录

添加环境变量

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

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

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

export PATH

执行脚本

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

数据库初始化建立基本数据

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

localhost: 10：WPQGxQHTh6(D 20：7fL4w3Czsp(T

[root@pxe support-files]# cd

将初始密码保存

[root@pxe ~]# vim passwd

启动mysql

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

Starting MySQL.Logging to '/data/mysql/pxe.err'.

SUCCESS!

通过命令启动数据库指定级别，这里默认启动2345

[root@pxe ~]# chkconfig mysqld on

[root@pxe ~]# chkconfig --list

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

安全初始化

[root@pxe ~]# mysql_secure_installation

Enter password for user root: 输入初始密码passwd里面保存的

New password:设置新密码

Re-enter new password:再输入一遍新密码

Press y|Y for Yes, any other key for No: no

Change the password for root ? ((Press y|Y for Yes, any other key for No) : no

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Success.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Success.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

登录测试

[root@pxe ~]# mysql -uroot -p

Enter password:123

mysql> show databases

    -> ;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

三、mysql的主从复制

3.1无数据添加slave

设置主从id

[root@mysqlnode1 local]# vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

symbolic-links=0

server-id=10

log-bin=mysql-bin

重启数据库

[root@mysql-node1 mysql]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL. SUCCESS!

[root@mysql-node2 mysql]# vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

symbolic-links=0

server-id=20

[root@mysql-node2 mysql]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

配置master

[root@mysql-node1 mysql]# mysql -uroot -p

进入数据库配置用户权限

生成专门用来做复制的用户，用于slave端做认证用

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';

Query OK, 0 rows affected (0.00 sec)

对用户进行授权

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';

Query OK, 0 rows affected (0.00 sec)

查看master的状态

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |     1151 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

             File: mysql-bin.000001

         Position: 1151

     Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

配置slave

mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=1151;——设置master

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

查看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: 1151

               Relay_Log_File: mysql-node2-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

在master上插入数据测试：

mysql> create database jcl;

Query OK, 1 row affected (0.01 sec)

mysql> create table jcl.userlist(

    -> username varchar(10) not null,

    -> password varchar(50) not null

    -> );

Query OK, 0 rows affected (0.00 sec)

mysql> insert into jcl.userlist values ('jjj1','111');

Query OK, 1 row affected (0.02 sec)

mysql> select * from jcl.userlist

    -> ;

+----------+----------+

| username | password |

+----------+----------+

| jjj1     | 111      |

+----------+----------+

1 row in set (0.00 sec)

在20主机上可以看到数据

mysql> select * from jcl.userlist;

+----------+----------+

| username | password |

+----------+----------+

| jjj1     | 111      |

+----------+----------+

1 row in set (0.00 sec)

注：

在主数据库中添加数据后从数据库上会更新，但是从数据库添加数据主数据库这边是不会看到的。

3.2有数据添加slave

将mysql文件同步到node3上

[root@mysql-node1 mysql]# rsync -al /usr/local/mysql root@172.25.254.30:/usr/local

从master节点备份数据

[root@mysql-node1 mysql]# mysqldump -uroot -p jcl > jcl.sql

Enter password:

[root@mysql-node1 mysql]# scp jcl.sql root@172.25.254.30:/mnt

root@172.25.254.30's password:

jcl.sql 100% 1944 3.4MB/s 00:00

在node3上创建用户和数据目录

[root@mysql-node3 local]# useradd -s /sbin/nologin -M mysql

[root@mysql-node3 local]# mkdir -p /data/mysql

[root@mysql-node3 local]# chown -R mysql.mysql /data/mysql/

[root@mysql-node3 local]# vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

symbolic-links=0

server-id=30

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

[root@mysql-node3 local]# vim ~/.bash_profile

具体内容参考以上MySQL的部署

[root@mysql-node3 local]# source ~/.bash_profile

初始化数据库

[root@mysql-node3 local]# mysqld --user=mysql --initialize

[root@mysql-node3 ~]# /etc/init.d/mysqld start

Starting MySQL.Logging to '/data/mysql/mysql-node3.err'.

SUCCESS!

安全初始化

[root@mysql-node3 ~]# mysql_secure_installation

[root@mysql-node3 ~]# cd /mnt

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

[root@mysql-node3 mnt]# mysql -uroot -p123 -e "create database jcl;"

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mysql-node3 mnt]# mysql -uroot -p123 jcl < jcl.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

配置slave2的slave功能

[root@mysql-node3 mnt]# mysql -uroot -p123

mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=1794;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

测试查看master的数据

mysql> select * from jcl.userlist;

+----------+----------+

| username | password |

+----------+----------+

| jjj1 | 111 |

+----------+----------+

1 row in set (0.00 sec)

3.3延迟复制

在slave2上设置延迟复制

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay=60;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

SQL_Delay: 60

在主数据库上删除一条信息

mysql> delete from jcl.userlist where username='jjj1';

Query OK, 1 row affected (0.00 sec)

mysql> select * from jcl.userlist;

+----------+----------+

| username | password |

+----------+----------+

| ccc      | 222      |

+----------+----------+

1 row in set (0.00 sec)

在30从数据库上因为做了延迟，所以还是看的我们删除的信息

mysql> select * from jcl.userlist;

+----------+----------+

| username | password |

+----------+----------+

| jjj1     | 111      |

| ccc      | 222      |

+----------+----------+

2 rows in set (0.01 sec)

3.4慢查询日志

慢查询就是当执行SQL超过long_query_time参数设定的时间阈值的语句。

查看慢查询参数

mysql> SHOW variables like "slow%";

+---------------------+----------------------------------+

| Variable_name       | Value                            |

+---------------------+----------------------------------+

| slow_launch_time    | 2                                |

| slow_query_log      | OFF                              |

| slow_query_log_file | /data/mysql/mysql-node3-slow.log |

+---------------------+----------------------------------+

3 rows in set (0.00 sec)

开启慢查询日志

mysql> SET GLOBAL slow_query_log=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES like "long%";

+-----------------+-----------+

| Variable_name   | Value     |

+-----------------+-----------+

| long_query_time | 10.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/mysql-node3-slow.log |

+---------------------+----------------------------------+

3 rows in set (0.00 sec)

查看慢查询日志

[root@mysql-node3 mnt]# cat /data/mysql/mysql-node3-slow.log

/usr/local/mysql/bin/mysqld, Version: 5.7.44 (Source distribution). started with:

Tcp port: 3306 Unix socket: /data/mysql/mysql.sock

Time                 Id Command    Argument

慢查询测试：

mysql> select sl

最低0.47元/天解锁文章