MySQL(mariadb)多实例应用与多实例主从复制

发布时间：2020-02-26 13:54:44 来源：网络阅读：3686 作者：孤岛鱼夫栏目：MySQL数据库

MySQL多实例

mysql多实例，简单理解就是在一台服务器上，mysql服务开启多个不同的端口（如3306、3307，3308），运行多个服务进程。这些 mysql 服务进程通过不同的 socket来监听不同的数据端口，进而互不干涉的提供各自的服务。

在同一台服务器上，mysql 多实例会去共用一套 mysql 应用程序，因此在部署 mysql的时候只需要部署一次 mysql程序即可，无需多次部署。但是，mysql多实例之间会各自使用不同的 my.cnf 配置文件、启动程序和数据文件。在提供服务方面，mysql多实例在逻辑上看起来是各自独立，互不干涉的，并且多个实例之间是根据配置文件的设定值，来获取相关服务器的硬件资源。

多实例应用场景

当一个公司业务访问量不太大，又想节俭成本，并且还希望不同业务的数据库服务能够各自尽量独立，提供服务能够互相不受影响。另外还需要应用主从同步等技术来提供数据库备份或读写分离服务，以及方便后期业务量增大时，数据库架构的扩展和迁移。
公司业务访问量不是太大的时候，服务器的资源基本都是过剩状态。此时就很适合 mysql 多实例的应用。如果对 SQL语句优化做的比较好，mysql 多实例是一个很值得去使用的技术。
测试环境，公司中会需要数据库环境来测试业务，在单机上部署多实例也能够节省成本

MySQL多实例实现方式

mysql 多实例常规来讲，有三种方案可以实现，这三种方案各有利弊，如下：

基于多配置文件通过使用多个配置文件来启动不同的进程，以此来实现多实例。

优点：逻辑简单，配置简单

缺点：管理起来不方便

基于mysqld_multi通过官方自带的 mysqld_multi 工具，使用单独配置文件来实现多实例

优点：便于集中管理管理

缺点：不方便针对每个实例配置进行定制

基于IM使用 MySQL 实例管理器（MYSQLMANAGER），这个方法好像比较好不过也有点复杂

优点：便于集中管理

缺点：耦合度高。IM一挂，实例全挂

不方便针对每个实例配置进行定制

本章内容采用第一种方式来是实现，(个人倾向于这种方式)，数据库采用mariadb来代替mysql，过程都一样

系统与安装版本信息
操作系统：Centos6.9
数据库：mariadb-10.2.15.tar.gz
三个实例：3306，3307，3308

MySQL多实例安装

安装mysql

安装依赖包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-devel ncurses-devel libxml2-devel

创建mysql用户组

useradd -r mysql

创建实例数据库目录
各实例的数据库目录，配置文件目录，启动程序目录都存放在单独的目录位置，结构如下：

[root@localhost ~]# mkdir -p /data/mysql/{3306,3307,3308}/{data,etc,log,socket,bin}
[root@localhost ~]# tree /data
/data
├── lost+found
└── mysql
    ├── 3306
    │   ├── bin
    │   ├── data
    │   ├── etc
    │   ├── log
    │   └── socket
    ├── 3307
    │   ├── bin
    │   ├── data
    │   ├── etc
    │   ├── log
    │   └── socket
    ├── 3308
    │   ├── bin
    │   ├── data
    │   ├── etc
    │   ├── log
    │   └── socket
    └── mysqld

解压安装源码包

[root@localhost ~]# tar zxvf mariadb-10.2.15
[root@localhost ~]# cd mariadb-10.2.15

#编译
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DCMAKE-USER=mysql  -DCMAKE-GROUP=mysql -DMYSQL_DATADIR=/data/mysql -DWITHOUT_TOKUDB=1

如果执行cmake提示：CMake Error at storage/tokudb/PerconaFT/cmake_modules/TokuSetupCompiler.cmake:183 (message):
添加编译参数：-DWITHOUT_TOKUDB=1

make  -j  4
make  install

安装成功后先不初始化，把配置文件，启动脚本文件都配置完成后再执行初始化

复制配置文件到各实例的etc目录下

配置文件在源码包解压目录下

[root@localhost support-files]# cp my-huge.cnf /data/mysql/3306/etc/my.cnf
[root@localhost support-files]# cp my-huge.cnf /data/mysql/3307/etc/my.cnf
[root@localhost support-files]# cp my-huge.cnf /data/mysql/3308/etc/my.cnf

修改配置文件内容，3306,3307,3308各实例配置文件修改my.cnf内容，修改端口，socket路径，数据存放目录等

[client]
#password       = your_password
port            = 3306       
socket          = /data/mysql/3306/socket/mysql.sock

[mysqld]
port            = 3306
socket          = /data/mysql/3306/socket/mysql.sock
datadir=/data/mysql/3306

同样，3307，3308实例也要修改相同的配置

编写服务启动脚本

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}

function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
#       ${cmd_path}/mysqladmin -u ${mysql_user} -S ${mysql_sock} shutdown
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}

function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

上面的脚本要准备三份，因为mysql实例是各自独立管理的，将上面的脚本参数port变量修改成3307,3308即可，放在各自实例的bin目录，服务启动是从各自实例的bin目录下执行这脚本

port=3307
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

初始化数据库

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3306/data

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3307/data

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3308/data

修改mysql实例目录属主

chown -R mysql:mysql /data/mysql

启动各实例服务

[root@localhost ~]# /data/mysql/3306/bin/mysqld start
[root@localhost ~]# /data/mysql/3307/bin/mysqld start
[root@localhost ~]# /data/mysql/3308/bin/mysqld start

[root@localhost mysql]# ss -tnl
State      Recv-Q Send-Q                                 Local Address:Port                                   Peer Address:Port 
LISTEN     0      80                                                :::3306                                             :::*     
LISTEN     0      80                                                :::3307                                             :::*     
LISTEN     0      80                                                :::3308                                             :::*     
LISTEN     0      128                                               :::22                                               :::*

测试登录

[root@localhost ~]# mysql -S /data/mysql/3306/socket/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.15-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

至此mysql的多实例安装完成，在这种多实例的环境下，可以单独使用个实例进行数据存储，也可以将各实例配置成主从架构进行使用，下面顺便把mysql的主从也介绍使实现一下，关于mysql的主从架构，以后会专门研究各种功能，这里只简单实现，能够帮助理清主从复制的工作过程和配置过程。

MySQL多实例主从复制

在开始主从配置之前先简单介绍一下mysql的主从复制原理，
mysql复制原理大致如下：
1，mysql主数据库在事物提交时会把数据变更作为事件events记录在二进制日志文件bin-log中，mysql主库上的sync_binlog参数控制bin-log日志刷新到磁盘

2，主库推送二进制日志文件bin-log中的事件到从库的中继日志Relay Log ,之后从库根据中继日志Relay log重写数据操作将数据写入从库，以此达到主库和从库的数据一致

mysql复制过程中通过3个线程来完成复制操作：其中binlog dump线程在主库上，I/O线程和SQL线程在从库上，当在从库上启动复制（START SLAVE）时，首先会I/O线程连接主库，（连接主库用户用主库创建），主库随后创建binlog dump线程读取数据库事件（binlog日志）并发送给I/O线程，I/O获取到binlog日志数据后更新到从库的中继日志Relay log中，从库上的SQL线程读取中继日志Relay log 中数据写入本地数据库，最终完成主从复制。

MySQL(mariadb)多实例应用与多实例主从复制

一主多从的实现

本文实现一主多重的方式，即上面的3个mysql实例，3306实例作为主库，3307和3308作为从库，实现主从复制的最为关键的是日志文件的设置，主库必须要开启二进制日志，从库要关闭二进制，并且开启中继日志relay log，要点如下：

主库创建提供从库的同步用户
主库开启二进制日志
从库开启中继日志relay log，并关闭二进制日志功能
主从之间的server-id不能相同
主从时间同步（由于在同一台主机，步奏略过）

配置主从库实例的配置文件

查看mysql是否开启二进制和中继日志

MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'relay_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| relay_log     |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql安装默认会启用二进制日志，但么有启动relay log，所以主库上无需设置，从库需要开启中继日志：
编辑实例3307,3308的my.cnf关闭bin-log，开启中继日志，修改server-id

3307实例：

#log-bin=mysql-bin
relay-log=relay-log
server-id       = 2

3308实例：

#log-bin=mysql-bin
relay-log=relay-log
server-id       = 3

修改后要重启3307,3308实例

[root@localhost ~]# /data/mysql/3307/bin/mysqld restart
Restarting MySQL...
Stoping MySQL...
Enter password: 
Starting MySQL...

[root@localhost ~]# /data/mysql/3308/bin/mysqld restart
Restarting MySQL...
Stoping MySQL...
Enter password:    #这里提示要输入密码，因为中定义了要输入密码
Starting MySQL...

重启从库实例后检查日志启用情况

MariaDB [(none)]> show variables like 'relay_log';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| relay_log     | relay-log |
+---------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

主库创建同步复制的用户

MariaDB [(none)]> grant replication slave on *.* to yufu@'192.168.%.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看主库的bin-log日志记录位置节点
因为在从库中使用change master to 指令连接主库时要指定连接时主库的bin-log日志记录位置节点。

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      645 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从库连接主库

登录到从库3307实例：

MariaDB [(none)]> change master to master_host='192.168.214.141',master_user='yufu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=645;
Query OK, 0 rows affected (0.11 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

登录到从库3308实例：

MariaDB [(none)]> change master to master_host='192.168.214.141',master_user='yufu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=645;
Query OK, 0 rows affected (0.11 sec)

MariaDB [(none)]>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

启动从库复制功能

启动3307实例并查看复制状态

MariaDB [(none)]> START SLAVE;      #
Query OK, 0 rows affected (0.80 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.214.141
                  Master_User: yufu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 645
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes         #同时为yes才能成功同步数据
            Slave_SQL_Running: Yes

启动3308实例并查看复制状态

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.214.141
                  Master_User: yufu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 645
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

至此，配置启动都没有问题，下面从主库实例3306中写入一些数据看看看看3307,3308实例能不能正常同步数据

测试同步

登录主库3306实例写一些数据：

MariaDB [(none)]> create database yufu;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use yufu;
Database changed
MariaDB [yufu]> create table test(
    -> id varchar(10),
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.84 sec)

MariaDB [yufu]> insert into test values ('2323','yufuname');
Query OK, 1 row affected (0.00 sec)

登录从库3307实例查看数据：

MariaDB [(none)]> select * from yufu.test;
+------+----------+
| id   | name     |
+------+----------+
| 2323 | yufuname |
+------+----------+
1 row in set (0.00 sec)

登录从库3308实例查看数据：

MariaDB [(none)]>  select * from yufu.test;
+------+----------+
| id   | name     |
+------+----------+
| 2323 | yufuname |
+------+----------+
1 row in set (0.01 sec)

经过测试同步功能正常，文章最后再次总结一下mysql主从复制的要点：

1. 主库必须开启bin-log二进制日志
2. 从库必须开启中继日志Relay-log，关闭bin-log
3. 主从的server-id 必须不能相同
4. 主库创建同步用户
5. 确保上面设置后，登录从库执行 change master to 指令
6. 主从时间同步

在后面的文章里，会比较详细介绍使用mysql的主从复制功能

个人博客站点www.gudaoyufu.com

向AI问一下细节

MySQL(mariadb)多实例应用与多实例主从复制

MySQL多实例

多实例应用场景

MySQL多实例实现方式

MySQL多实例安装

安装mysql

MySQL多实例主从复制

一主多从的实现

配置主从库实例的配置文件

主库创建同步复制的用户

从库连接主库

启动从库复制功能

测试同步

猜你喜欢

最新资讯

相关推荐

相关标签