mysql主从配置与mysql-router

系统环境

服务器A

  • 操作系统: ubuntu-server 18.0.4
  • ip地址: 10.0.1.180

  • mysql版本: mysql 8.0 (主服务器)

  • mysql-router版本: mysql-router 8.0

服务器B

  • 操作系统: ubuntu-server 18.0.4
  • ip地址: 10.0.1.181
  • mysql版本: mysql-8.0 (从服务器)

ps:从服务器的mysql版本尽量和主服务器一致，或者高于主服务器的版本

宿主机(测试用)

  • 操作系统: ubuntu 16.0.4
  • ip地址: 10.0.1.189
  • mysql版本: 5.7

任务目标

配置虚拟机A的mysql为主服务器，虚拟机B的mysql为从服务器，然后使用mysql-router对当前的主从配置服务器进行管理,最后模拟客户端链接测试

软件安装

  • 安装mysql-server和mysql-router

获取mysql的apt完整包

wget https://dev.mysql.com/get/mysql-apt-config_0.8.11-1_all.deb

安装

sudo dpkg -i mysql-apt-config_0.8.11-1_all.deb
 sudo apt update

安装mysql8.0和mysql-router8.0

sudo apt install mysql-server
 sudo apt install mysql-router

虚拟机B安装同上

主从服务器配置

  • 服务器A(主服务器)

编辑/etc/mysql/mysql.conf.d/mysqld.cnf ,配置server-id 并开启二进制日志

log_bin         = mysql-bin
server-id       = 1

重启mysql服务

sudo service mysql restart

登录mysql终端,然后创建从服务器账户,并授权

mysql> CREATE USER 'slave'@'10.0.1.181' IDENTIFIED BY '123456'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.1.181';
mysql> flush privileges;

查看主服务器状态

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 1901
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
  • 同步服务器A的初态到服务器B

导出服务器A的数据 并拷贝到服务器B的home目录下

# 服务器A 
mysqldump -uroot -p --all-databases > db.sql
scp db.sql crisen@10.0.1.181:~/

导入服务器A的数据

# 服务器B
mysql > source db.sql;
  • 服务器B (从服务器)

编辑/etc/mysql/mysql.conf.d/mysqld.cnf ,配置server-id等从服务器配置

log_bin           = mysql-bin # 可选
server-id         = 2
relay_log         = /var/log/mysql/mysql-relay-log
log_slave_updates = 1
read_only         = 1

配置主服务器

mysql > change master to master_host='10.0.1.180',
     -> master_user='slave',
     ->  master_password='123456',
     ->  master_log_file='mysql-bin.000002',
     ->   master_log_pos=1901;

启动复制

mysql > start slave;

查看从服务器状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.180
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1901
               Relay_Log_File: mysql-relay-log.000002
                Relay_Log_Pos: 1777
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ........ 省略 ..........
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Slave_IO_Running和Slave_SQL_Running同时为Yes的时候 就说明主从配置是成功 如果没有配置成功 可以通过查看Slave_SQL_Running_State的状态来检查错误原因

mysql router 配置

虽然很多的框架提供了让你可以把读写操作配置到不同的数据库上，但那是比较简单的情况下，更加复杂的情况当然是选择mysql的中间件来完成，使用专业的工具来做专业的事情，这也是为什么会有mysql中间件，而mysql router就是mysql官方开发的一款mysql中间件 尽管它不是最好的 但是足够让我们用来理解mysql中间件的运行方式了

  • 配置mysql router
[DEFAULT]
logging_folder = /var/log/mysqlrouter/
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

### 读写策略  写操作只允许主服务器
[routing:read_write]
bind_address           = 10.0.1.180
bind_port              = 9468
destinations           = 10.0.1.180
mode                   =  read-write
max_connections        = 65535
max_connect_errors     = 100
client_connect_timeout = 9

### 只读策略   读操作可以在主服务器和从服务器之间进行负载均衡方案
[routing:read_only]
bind_address           =  10.0.1.180
bind_port              =  9469
destinations           =  10.0.1.180,10.0.1.181
mode                   =  read-only
max_connections        = 65535
max_connect_errors     = 100
client_connect_timeout = 9

[keepalive]
interval = 60

详细的mysql-router配置 请自行查看官方文档 这里只做我需要的基本配置

如果在配置的过程中 发生了错误导致mysqlrouter进程没有启动 需要分析/var/log/mysqlrouter/mysqlrouter.log 来找出错误的原因

测试mysql router

配置了mysql router之后 我们所有的mysql服务器就相当于对客户端不可见了

根据上面的配置 我们得到了读写策略和只读策略的地址 分别如下

# 读写服务器
地址:10.0.1.180 
端口:9468
#  只读服务器
地址:10.0.1.181
端口:9468

创建一个对测试机可以链接的用户

mysql> CREATE USER 'crisen'@'10.0.1.189' IDENTIFIED BY '123456'; 
mysql> GRANT ALL ON *.* TO 'crisen'@'10.0.1.189';
mysql> flush privileges; ## 因为已经配置了主从同步 所以只需要在主服务器上创建就可以了  但是从服务器也上需要运行flush privileges命令来刷新权限

在宿主机上使用命令测试链接情况

  1. 写操作
mysql -h10.0.1.180  -ucrisen -P9468 -p123456 -e "select @@hostname;"
+------------+
| @@hostname |
+------------+
| ubuntu180  |
+------------+
  1. 读操作
mysql -h10.0.1.180  -ucrisen -P9469 -p123456 -e "select @@hostname;"
+------------+
| @@hostname |
+------------+
| ubuntu181  |
+------------+
mysql -h10.0.1.180  -ucrisen -P9469 -p123456 -e "select @@hostname;"
+------------+
| @@hostname |
+------------+
| ubuntu180  |
+------------+

写操作返回的是主服务器，如果是读操作，那么返回的服务器名称会自动的进行负载均衡方案，如果选择其中一个服务器down的话， 那么mysql router会自动的剔除这个操作

尽管主从复制可以提高数据库的负载，但是主从同步的这个过程中，数据的一致性是没有办法百分之百保证的，需要其他的解决方案,不过这是主从复制的缺点，并不是mysql-router的缺点

发表评论