MySQL 复制配置

环境

步骤

1. 在每台服务器上创建复制账号
2. 配置主库和备库
3. 通知备库连接到主库并从主库复制数据

创建复制账号

在每台服务器上执行如下命令：

create user repl@'192.168.31.%' identified by 'replp4ssword';
grant replication slave, replication client on *.* to repl@'192.168.31.%';

配置主库和备库

主库：

在主库上开启二进制日志并指定一个服务器ID，其 my.cnf 文件中配置如下内容：

log_bin=mysql-bin
server_id=150

重新启动 MySQL，然后执行如下 SQL：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

该命令显示二进制日志文件已经在主库上创建。

备库：

备库的 my.cnf 增加的配置如下：

log_bin=mysql-bin
server_id=160
relay_log=/u01/mysql/data/mysql-relay-bin
log_slave_updates=1
read_only=1

配置选项：

relay_log - 指定中继日志的位置和命名

log_slave_updates - 允许备库将其重放的事件记录到自身的二进制日志

read_only - 会阻止任何没有特权权限的线程修改数据，对于需要在备库上建表的应用程序不建议设置该选项

启动复制

执行如下 SQL 语句：

change master to
master_host='192.168.31.150',
master_user='repl',
master_password='replp4ssword',
master_log_file='mysql-bin.000001',
master_log_pos=0;

然后执行 show slave status SQL 语句来查看状态，我们可以看到 Slave_IO_State、Slave_IO_Running、Slave_SQL_Running 三列显示当前备库复制尚未运行，此时还没有开始复制，运行如下 SQL 命令：

start slave;

再次执行 show slave status SQL 语句来查看状态，可以看出 IO 线程和 SQL 线程都开始运行，Seconds_Behind_Master 的值也不再为 Null。

在主库上查看由备库 IO 线程向主库发起的连接：

show processlist;

在备库上查看其 IO 线程和 SQL 线程：

show processlist;

测试

在主库上执行如下 SQL 语句：

create database testdb;

use testdb;

create table test_table(
  test_id varchar(64) primary key,
  test_name varchar(32),
  test_number int
);

insert into test_table(test_id, test_name, test_number) values ('X1', '张三', 100);
insert into test_table(test_id, test_name, test_number) values ('X2', '李四', 100);
insert into test_table(test_id, test_name, test_number) values ('X3', '赵六', 100);

在备库上执行如下 SQL 语句：

use testdb;
select * from test_table;

可以看到数据已经复制到了备库。

负载均衡

负载均衡采用 Nginx，nginx 在版本1.9.0以后支持tcp的负载均衡，具体可以参照官网关于模块 ngx_stream_core_module的叙述，链接地址为：http://nginx.org/en/docs/stream/ngx_stream_core_module.html#tcp_nodelay。

nginx从1.9.0后引入模块ngx_stream_core_module，模块是没有编译的，需要用到编译，编译时需添加–with-stream配置参数，stream负载均衡官方配置样例如下所示。

worker_processes auto;
error_log /var/log/nginx/error.log info;

events {
    worker_connections  1024;
}

stream {
    upstream backend {
        hash $remote_addr consistent;

        server backend1.example.com:12345 weight=5;
        server 127.0.0.1:12345            max_fails=3 fail_timeout=30s;
        server unix:/tmp/backend3;
    }

    upstream dns {
       server 192.168.0.1:53535;
       server dns.example.com:53;
    }

    server {
        listen 12345;
        proxy_connect_timeout 1s;
        proxy_timeout 3s;
        proxy_pass backend;
    }

    server {
        listen 127.0.0.1:53 udp;
        proxy_responses 1;
        proxy_timeout 20s;
        proxy_pass dns;
    }
    
    server {
        listen [::1]:12345;
        proxy_pass unix:/tmp/stream.socket;
    }
}

nginx 编译命令：

./configure --prefix=/usr/local/nginx --with-stream --with-pcre=/usr/local/pcre-8.43 --with-zlib=/usr/local/zlib-1.2.11 --with-openssl=/usr/local/openssl-1.1.1d --with-http_ssl_module
make
make install

nginx 配置文件内容：

worker_processes auto;
error_log /u01/nginx/error.log;
error_log /u01/nginx/error.log notice;
error_log /u01/nginx/error.log info;
pid       /u01/nginx/nginx.pid;

events {
    worker_connections  1024;
}

stream {
    upstream mysql_read {
        server 192.168.31.160:3306 weight=1;
        server 192.168.31.170:3306 weight=1;
    }
    
    upstream mysql_write {
        server 192.168.31.150:3306 weight=1;
    }
    
    server{
        listen 3106;
        proxy_pass mysql_read;
    }
    
    server{
        listen 3206;
        proxy_pass mysql_write;
    }
}

nginx 启动脚本：

#!/bin/sh
source /etc/profile
/usr/local/nginx/sbin/nginx -c /u01/nginx/nginx_mysql.conf