搭建mysql的主从复制和高可用

一、mysql主从复制

一、环境准备

主 10.0.0.52

从 10.0.0.54

1.设置server_id，主为1，从为2,主库开启二进制日志
vim /etc/my.cnf
server_id=1
log_bin=on
log_bin = /data/mysql/binlog
2.在主节点10.0.0.52上创建远程用户
create user 'user1'@'10.0.0.%' identified with mysql_native_password by '123456';
grant all on *.* to 'user1'@'10.0.0.%';

二、在从库10.0.0.54上备份主库数据信息

mysqldump -uuser1 -h10.0.0.52 -p123456 -P3306 -A --source-data=2 --single-transaction > /tmp/all.sql
​
mysql -uroot -p < /tmp/all.sql

三、创建主从数据同步的用户信息 在主库上10.0.0.52上创建用户

create user repl@'10.0.0.%' identified with mysql_native_password by '123456';
grant replication slave on *.* to repl@'10.0.0.%';

四、配置主从节点数据复制的信息

在从节点10.0.0.54上操作

通过备份⽂件获取同步位置点信息
vim /tmp/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=681;

mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=681,
MASTER_CONNECT_RETRY=10;

开启
start slave;

五、查看主从复制是否成功

在主库上创建数据库
crete databases test_db;
在从库上查看是否同步过来
show databases;

查看IO线程和SQL线程是否开启
show slave status\G;
 * * * * * * * * * * * * 1. row * * * * * * * * * * 
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.30.101
Master_User: repl
Master_Port: 3307
Connect_Retr y: 10
Master_Log_File: binlog.000003
Read_Master_Log_Pos : 869
Relay_Log_File: x iaoQ-01-relay-bin.000002
Relay_Log_Pos : 509
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

二、GTID主从复制

步骤一：主从同步架构环境规划

DB01 主库 10.0.0.53 3306 开启gtid功能 binlog日志开启 DB02 从库 10.0.0.54 3306 开启gtid功能 binlog日志开启 DB03 从库 10.0.0.55 3306 开启gtid功能 binlog日志开启

步骤二：部署数据库实例

mkdir -p /data/3306/data/
mkdir -p /data/3306/log/
chown -R mysql:mysql /data/3306/
mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data/  --basedir=/usr/local/mysql 

DB01: 配置信息

cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/3306/log/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF

DB02: 配置信息

cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=54
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/log/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF

DB03: 配置信息

cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=55
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/log/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF

步骤三：备份主库数据信息并恢复到从库

mysqldump -uroot -A  --source-data=2 --single-transaction >/tmp/all.sql    #主库备份数据
scp -p /tmp/all.sql 10.0.0.54:/tmp/  #在主库上将备份文件传输至从库
scp -p /tmp/all.sql 10.0.0.55:/tmp/
mysql -uroot </tmp/all.sql  #从库恢复数据

步骤四：建立主从关系 主库–创建同步用户

create user repl@'10.0.0.%' identified with mysql_native_password by '123456';
grant replication slave on *.* to repl@'10.0.0.%';

从库-进行主从配置

CHANGE MASTER TO
  MASTER_HOST='10.0.0.53',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  master_auto_position=1,
  MASTER_CONNECT_RETRY=10; 
start slave;

步骤五：检查确认主从状态 在主库上创建测试数据

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 使用测试数据库
USE test_db;
-- 创建测试表
CREATE TABLE IF NOT EXISTS test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO test_table (name, email, age) VALUES
('张三', 'zhangsan@example.com', 25),
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28),
('赵六', 'zhaoliu@example.com', 35),
('钱七', 'qianqi@example.com', 22);
-- 更新一条数据，测试UPDATE语句的复制
UPDATE test_table SET age = 26 WHERE name = '张三';
-- 删除一条数据，测试DELETE语句的复制
DELETE FROM test_table WHERE name = '赵六';
-- 查看表中的数据
SELECT * FROM test_table ORDER BY id;
-- 查看表结构
DESC test_table;
-- 查看数据库
SHOW DATABASES LIKE 'test_db';
commit;  #因为在主库上设置了autocommit=0，所以得手动提交事务

在从库上查看

-- 检查从库复制状态（在从库执行）
SHOW SLAVE STATUS\G
-- 关注以下字段：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 或很小的数字
-- Last_Error: 应该为空
-- 查看从库上的数据库
SHOW DATABASES;
-- 使用test_db数据库
USE test_db;
-- 查看表
SHOW TABLES;
-- 查看表结构
DESC test_table;
-- 查看数据是否与主库一致
SELECT * FROM test_table ORDER BY id;
-- 查看表中的数据数量
SELECT COUNT(*) as total_rows FROM test_table;

三、多源主从复制

主从多源数据同步 （MSR）（数据信息整合）

当数据信息拆分后，可以将并发流量进行均衡分配，但是会产生数据查询问题（数据孤岛） 当需要进行数据信息分析时，需要将分散的数据信息进行整合（数据中台）

多源主从复制部署

步骤一：数据库实例规划

DB01 10.0.0.53 3307 主库 DB02 10.0.0.54 3307 主库 DB03 10.0.0.55 3307 从库

步骤二：创建数据库实例

pkill mysql 
ps -ef|grep mysql 

创建数据库实例脚本

vim mysql_example.sh

#!/bin/bash
set -e
​
### ===== 基础变量 =====
SERVER_ID=53            #更改对应ip的server_id
PORT=3307
MYSQL_BASE=/usr/local/mysql
BASE_DIR=/mysql/${PORT}
​
DATA_DIR=${BASE_DIR}/data
CONF_DIR=${BASE_DIR}/etc
SOCKET_DIR=${BASE_DIR}/socket
LOG_DIR=${BASE_DIR}/log
PID_DIR=${BASE_DIR}/pid
TMP_DIR=${BASE_DIR}/tmp
BIN_DIR=${BASE_DIR}/bin
​
SOCKET=${SOCKET_DIR}/mysql.sock
CONF_FILE=${CONF_DIR}/my.cnf
LOG_ERROR=${LOG_DIR}/mysql_error.log
LOG_BIN=${LOG_DIR}/mysql-bin
binlog_format=row
PID_FILE=${PID_DIR}/mysql.pid
​
MYSQLD=${MYSQL_BASE}/bin/mysqld
MYSQLD_SAFE=${MYSQL_BASE}/bin/mysqld_safe
MYSQLADMIN=${MYSQL_BASE}/bin/mysqladmin
MYSQL=${MYSQL_BASE}/bin/mysql
​
### ===== 创建目录 =====
mkdir -pv ${DATA_DIR} ${CONF_DIR} ${SOCKET_DIR} ${LOG_DIR} ${PID_DIR} ${TMP_DIR} ${BIN_DIR}
chown -R mysql:mysql /mysql
​
### ===== 初始化数据库（仅首次）=====
if [ ! -d "${DATA_DIR}/mysql" ]; then
    echo "Initializing MySQL ${PORT}..."
    ${MYSQLD} \
        --initialize-insecure \
        --user=mysql \
        --basedir=${MYSQL_BASE} \
        --datadir=${DATA_DIR} \
        --log-error=${LOG_ERROR}
fi
​
### ===== 生成配置文件 =====
cat > ${CONF_FILE} <<EOF
[mysqld]
port=${PORT}
basedir=${MYSQL_BASE}
datadir=${DATA_DIR}
socket=${SOCKET}
pid-file=${PID_FILE}
log-error=${LOG_ERROR}
log_bin=${LOG_BIN}
tmpdir=${TMP_DIR}
​
server-id=${SERVER_ID}
​
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
​
max_connections=151
max_connect_errors=100000
​
innodb_buffer_pool_size=128M
innodb_log_file_size=48M
innodb_file_per_table=1
​
[client]
port=${PORT}
socket=${SOCKET}
​
[mysql]
default-character-set=utf8mb4
prompt=db01 [\\\\d]>
EOF
​
### ===== 管理脚本 =====
cat > ${BIN_DIR}/mysqld <<EOF
#!/bin/bash
​
PORT=${PORT}
BASE_DIR=${BASE_DIR}
MYSQL_BASE=${MYSQL_BASE}
​
SOCKET=${SOCKET}
CONF_FILE=${CONF_FILE}
DATA_DIR=${DATA_DIR}
LOG_ERROR=${LOG_ERROR}
PID_FILE=${PID_FILE}
​
start() {
    if [ -S "\${SOCKET}" ]; then
        echo "MySQL \${PORT} already running"
        exit 0
    fi
​
    echo "Starting MySQL \${PORT}..."
    \${MYSQL_BASE}/bin/mysqld_safe \
        --defaults-file=\${CONF_FILE} \
        --user=mysql &
​
    sleep 5
    [ -S "\${SOCKET}" ] && echo "✓ MySQL \${PORT} started" || {
        echo "✗ MySQL start failed"
        tail -20 \${LOG_ERROR}
    }
}
​
stop() {
    if [ ! -S "\${SOCKET}" ]; then
        echo "MySQL \${PORT} not running"
        exit 0
    fi
​
    echo "Stopping MySQL \${PORT}..."
    \${MYSQL_BASE}/bin/mysqladmin -S \${SOCKET} shutdown || true
    sleep 3
    rm -f \${SOCKET}
    echo "✓ MySQL \${PORT} stopped"
}
​
status() {
    if [ -S "\${SOCKET}" ]; then
        echo "MySQL \${PORT} is running"
        \${MYSQL_BASE}/bin/mysqladmin -S \${SOCKET} ping
    else
        echo "MySQL \${PORT} is stopped"
    fi
}
​
case "\$1" in
    start) start ;;
    stop) stop ;;
    restart) stop; start ;;
    status) status ;;
    *)
        echo "Usage: \$0 {start|stop|restart|status}"
        ;;
esac
EOF
​
chmod +x ${BIN_DIR}/mysqld
​
### ===== 输出提示 =====
cat <<INFO
​
=== MySQL ${PORT} 部署完成 ===
​
Data Dir : ${DATA_DIR}
Config   : ${CONF_FILE}
Socket   : ${SOCKET}
ErrorLog : ${LOG_ERROR}
​
启动:
  ${BIN_DIR}/mysqld start
​
连接:
  mysql -S ${SOCKET} -uroot
​
设置 root 密码:
  mysql -S ${SOCKET} -uroot -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';"
​
INFO
​
​

分别在DB01、DB02、DB03上创建实例

bash mysql_example.sh

实例创建完成后，更改下配置文件

vim /mysql/3307/etc/my.cnf

在主库DB01 10.0.0.53添加下面配置

[mysqld]
user=mysql
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3307/tmp
innodb_flush_method=O_DIRECT
​
[mysql]
prompt=db01 [\\d]>

在主库DB02 10.0.0.54添加下面配置

user=mysql
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3307/tmp
innodb_flush_method=O_DIRECT
​
[mysql]
prompt=db02 [\\d]>

在主库DB03 10.0.0.55添加下面配置

user=mysql
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3307/tmp
innodb_flush_method=O_DIRECT
​
[mysql]
prompt=db03 [\\d]>

启动实例

/mysql/3307/bin/mysqld start

步骤三：实现多源主从复制 主节点DB01,DB02创建用户：

mysql -S /mysql/3307/socket/mysql.sock -uroot
​
set sql_log_bin=0;
create user repl@'10.0.0.%' identified with mysql_native_password by '123456';
grant replication slave on *.* to repl@'10.0.0.%';  
set sql_log_bin=1; 

从节点DB03主从配置：

mysql -S /mysql/3307/socket/mysql.sock -uroot

change master to
master_host='10.0.0.53',
MASTER_PORT=3307,
master_user='repl',
master_password='123456',
master_auto_position=1 for channel 'Master_1'; 

change master to
master_host='10.0.0.54',
MASTER_PORT=3307,
master_user='repl',
master_password='123456',
master_auto_position=1 for channel 'Master_2';

start slave for channel 'Master_1';
start slave for channel 'Master_2';
​
show slave status for channel 'Master_1'\G;
show slave status for channel 'Master_2'\G;

步骤四：进行多源复制测试

略

四、利用克隆方式实现主从同步

克隆实现主从作用： 1）可以解决云主机之间的主从搭建问题 2）可以提高数据迁移效率

步骤一：创建好数据库实例 DB01 10.0.0.53 3306 主库 DB02 10.0.0.54 3306 从库

pkill mysql 
ps -ef|grep mysql 
vim mysql_example.sh

#!/bin/bash
set -e
​
### ===== 基础变量 =====
SERVER_ID=53            #更改对应ip的server_id
PORT=3306
MYSQL_BASE=/usr/local/mysql
BASE_DIR=/mysql/${PORT}
​
DATA_DIR=${BASE_DIR}/data
CONF_DIR=${BASE_DIR}/etc
SOCKET_DIR=${BASE_DIR}/socket
LOG_DIR=${BASE_DIR}/log
PID_DIR=${BASE_DIR}/pid
TMP_DIR=${BASE_DIR}/tmp
BIN_DIR=${BASE_DIR}/bin
​
SOCKET=${SOCKET_DIR}/mysql.sock
CONF_FILE=${CONF_DIR}/my.cnf
LOG_ERROR=${LOG_DIR}/mysql_error.log
LOG_BIN=${LOG_DIR}/mysql-bin
PID_FILE=${PID_DIR}/mysql.pid
​
MYSQLD=${MYSQL_BASE}/bin/mysqld
MYSQLD_SAFE=${MYSQL_BASE}/bin/mysqld_safe
MYSQLADMIN=${MYSQL_BASE}/bin/mysqladmin
MYSQL=${MYSQL_BASE}/bin/mysql
​
### ===== 创建目录 =====
mkdir -pv ${DATA_DIR} ${CONF_DIR} ${SOCKET_DIR} ${LOG_DIR} ${PID_DIR} ${TMP_DIR} ${BIN_DIR}
chown -R mysql:mysql /mysql
​
### ===== 初始化数据库（仅首次）=====
if [ ! -d "${DATA_DIR}/mysql" ]; then
    echo "Initializing MySQL ${PORT}..."
    ${MYSQLD} \
        --initialize-insecure \
        --user=mysql \
        --basedir=${MYSQL_BASE} \
        --datadir=${DATA_DIR} \
        --log-error=${LOG_ERROR}
fi
​
### ===== 生成配置文件 =====
cat > ${CONF_FILE} <<EOF
[mysqld]
port=${PORT}
basedir=${MYSQL_BASE}
datadir=${DATA_DIR}
socket=${SOCKET}
pid-file=${PID_FILE}
log-error=${LOG_ERROR}
log_bin=${LOG_BIN}
binlog_format=row
tmpdir=${TMP_DIR}
​
server-id=${SERVER_ID}
​
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
​
max_connections=151
max_connect_errors=100000
​
innodb_buffer_pool_size=128M
innodb_log_file_size=48M
innodb_file_per_table=1
​
[client]
port=${PORT}
socket=${SOCKET}
​
[mysql]
default-character-set=utf8mb4
prompt=db01 [\\\\d]>
EOF
​
### ===== 管理脚本 =====
cat > ${BIN_DIR}/mysqld <<EOF
#!/bin/bash
​
PORT=${PORT}
BASE_DIR=${BASE_DIR}
MYSQL_BASE=${MYSQL_BASE}
​
SOCKET=${SOCKET}
CONF_FILE=${CONF_FILE}
DATA_DIR=${DATA_DIR}
LOG_ERROR=${LOG_ERROR}
PID_FILE=${PID_FILE}
​
start() {
    if [ -S "\${SOCKET}" ]; then
        echo "MySQL \${PORT} already running"
        exit 0
    fi
​
    echo "Starting MySQL \${PORT}..."
    \${MYSQL_BASE}/bin/mysqld_safe \
        --defaults-file=\${CONF_FILE} \
        --user=mysql &
​
    sleep 5
    [ -S "\${SOCKET}" ] && echo "✓ MySQL \${PORT} started" || {
        echo "✗ MySQL start failed"
        tail -20 \${LOG_ERROR}
    }
}
​
stop() {
    if [ ! -S "\${SOCKET}" ]; then
        echo "MySQL \${PORT} not running"
        exit 0
    fi
​
    echo "Stopping MySQL \${PORT}..."
    \${MYSQL_BASE}/bin/mysqladmin -S \${SOCKET} shutdown || true
    sleep 3
    rm -f \${SOCKET}
    echo "✓ MySQL \${PORT} stopped"
}
​
status() {
    if [ -S "\${SOCKET}" ]; then
        echo "MySQL \${PORT} is running"
        \${MYSQL_BASE}/bin/mysqladmin -S \${SOCKET} ping
    else
        echo "MySQL \${PORT} is stopped"
    fi
}
​
case "\$1" in
    start) start ;;
    stop) stop ;;
    restart) stop; start ;;
    status) status ;;
    *)
        echo "Usage: \$0 {start|stop|restart|status}"
        ;;
esac
EOF
​
chmod +x ${BIN_DIR}/mysqld
​
### ===== 输出提示 =====
cat <<INFO
​
=== MySQL ${PORT} 部署完成 ===
​
Data Dir : ${DATA_DIR}
Config   : ${CONF_FILE}
Socket   : ${SOCKET}
ErrorLog : ${LOG_ERROR}
​
启动:
  ${BIN_DIR}/mysqld start
​
连接:
  mysql -S ${SOCKET} -uroot
​
设置 root 密码:
  mysql -S ${SOCKET} -uroot -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';"
​
INFO
​
​

bash mysql_example.sh

在主库和从库的my.cnf文件增加下面配置

gtid_mode=on
enforce_gtid_consistency=on

启动实例

/mysql/3306/bin/mysqld start

步骤二：安装克隆功能插件

在主库DB01进行操作

mysql -S /mysql/3306/socket/mysql.sock -uroot -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123456';grant backup_admin on *.* to 'test'@'%';"

在从库DB02上操作

mysql -S /mysql/3306/socket/mysql.sock -uroot -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123456';grant clone_admin on *.* to 'test'@'%';set global clone_valid_donor_list='10.0.0.53:3306';"
​

步骤三：实现主从克隆数据信息

在从库DB02上操作

mysql -S /mysql/3306/socket/mysql.sock -utest -p123456 -h10.0.0.54 -P3306 -e "clone instance from test@'10.0.0.53':3306 identified by '123456';"

查看进度
mysql -S /mysql/3306/socket/mysql.sock -uroot -e "select stage,state,end_time from performance_schema.clone_progress;"

步骤四：主从关系建立

主库DB01进行配置

mysql -S /mysql/3306/socket/mysql.sock -uroot -e "create user repl@'%' identified with mysql_native_password by '123456';grant replication slave on *.* to 'repl'@'%';"

从库DB02进行配置

mysql -S /mysql/3306/socket/mysql.sock -uroot -e "change master to master_host='10.0.0.53',master_user='repl',master_password='123456',master_auto_position=1;start slave;"

步骤五：查看主从同步状态

mysql -S /mysql/3306/socket/mysql.sock -uroot -e "show slave status\G"|grep "Running:"

五、半同步主从复制

步骤一：搭建主从数据库实例环境 创建实例

DB01 主库 10.0.0.53 3306 开启gtid功能 binlog日志开启 DB02 从库 10.0.0.54 3306 开启gtid功能 binlog日志开启 DB03 从库 10.0.0.55 3306 开启gtid功能 binlog日志开启

bash mysql_example.sh

分别在DB01、DB02、DB03的my.cnf配置文件加入下面内容

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3306/tmp
innodb_flush_method=O_DIRECT

启动实例

/mysql/3306/bin/mysqld start

步骤二：安装半同步复制插件

主库DBO1安装插件信息：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
show plugins;

从库DB02、DB03安装插件信息：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
show plugins;

步骤三：激活半同步复制功能 主库DB01激活半同步功能：

set global rpl_semi_sync_master_enabled =1;
set global rpl_semi_sync_master_wait_point=after_commit;

从库DB02、db03激活半同步功能：

set global rpl_semi_sync_slave_enabled =1;

步骤四：建立主从关系 主库创建用户:

create user repl@'%' identified with mysql_native_password by '123456';grant replication slave on *.* to 'repl'@'%';

从库进行配置:

change master to master_host='10.0.0.53',master_user='repl',master_password='123456',master_auto_position=1;start slave;

步骤五：半同步复制应用确认 方式一：查看配置信息

show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

db01 [(none)]>show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |   -- 建立半同步复制的从库数量
| Rpl_semi_sync_master_status                | ON    |   -- 查看半同步功能是否应用激活
| rpl_semi_sync_master_timeout               | 10000 |   -- 等待ack的超时时间
| rpl_semi_sync_master_trace_level           | 32    |   -- 会将半同步复制建立过程信息记录到错误日志中
| rpl_semi_sync_master_wait_for_slave_count  | 1     |   -- 等待确认的ack数量信息
| rpl_semi_sync_master_wait_no_slave         | on    |   -- 可以将事务提交过程进行优化调整
| rpl_semi_sync_master_wait_point     after_sync     |   -- after_sync 增强半同步  after_commit 半同步
+--------------------------------------------+-------+

注：主从数据库半同步功能永久配置：

在my.cnf配置文件加入配置

主库配置信息：

[mysqld]
plugin_dir=/usr/local/mysql/lib/plugin
plugin-load=rpl_semi_sync_master=semisync_master.so        # -- 加载配置自动安装插件
rpl_semi_sync_master_enabled=on                            # -- 数据库服务重启后自动激活半同步复制功能
rpl_semi_sync_master_timeout=1000                          # -- 半同步复制等待ack超时时间
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_commit          
binlog_group_commit_sync_delay=1                           # -- binlog事务组提交，提高半同步复制事务处理效率
binlog_group_commit_sync_no_delay_count=1000

从库配置信息：

[mysqld]
plugin_dir=/usr/local/mysql/lib/plugin
plugin-load=rpl_semi_sync_slave=semisync_slave.so 
rpl_semi_sync_slave_enabled=on
rpl_semi_sync_slave_trace_level=32

增强半同步复制主从搭建：

ps:增强半同步复制主从搭建，就是在原有半同步复制主从搭建上更改rpl_semi_sync_master_wait_point=after_sync这一项配置

更改my.cnf配置文件

主库配置信息：

[mysqld]
plugin_dir=/usr/local/mysql/lib/plugin
plugin-load=rpl_semi_sync_master=semisync_master.so        # -- 加载配置自动安装插件
rpl_semi_sync_master_enabled=on                            # -- 数据库服务重启后自动激活半同步复制功能
rpl_semi_sync_master_timeout=1000                          # -- 半同步复制等待ack超时时间
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				   #开启增强半同步主从复制          
binlog_group_commit_sync_delay=1                           # -- binlog事务组提交，提高半同步复制事务处理效率
binlog_group_commit_sync_no_delay_count=1000

从库配置信息：

[mysqld]
plugin_dir=/usr/local/mysql/lib/plugin
plugin-load=rpl_semi_sync_slave=semisync_slave.so 
rpl_semi_sync_slave_enabled=on
rpl_semi_sync_slave_trace_level=32

六、主从同步复制数据 （MGR）

MGR实现同步复制数据原理 组复制是一种可用于实现容错系统的技术，复制组是一个通过消息传递实现相互交互的server集群； 复制组由多个server成员组成，如下图master01、master02、master03，所有成员独立完成各自的事务；

1. 当客户端发起一个更新事务时，该事务先在本地执行，执行完成之后就要发起对事务的提交操作；
2. 在还没有真正提交之前，需要将产生的复制写集广播出去，复制到其它所有成员节点； 主库事务提交时，会将事务修改记录相关的信息和事务产生的binlog事件打包生成一个写集，将写集发送给所有节点；
3. 如果冲突检测成功，组内决定该事务可以提交，其它成员可以应用，否则就回滚； 冲突检测成功的标准是：至少半数以上个节点投票通过才能事务提交成功；
4. 最终，所有组内成员以相同的顺序接收同一组事务； 因此，组内成员以相同的顺序应用相同的修改，保证组内数据强一致性（采用了分布式事务特性）

MGR主从架构构建方式： 单主模式：只有一个主节点 写操作会只发送到唯一的主节点 只有主节点出现故障，才会选举新的主节点 应用场景：读多写少的业务场景

单主模式创建过程：

步骤一：创建新的数据库实例环境

DB01 主库 10.0.0.53 3306 开启gtid功能 binlog日志开启 DB02 从库 10.0.0.54 3306 开启gtid功能 binlog日志开启 DB03 从库 10.0.0.55 3306 开启gtid功能 binlog日志开启

bash mysql_example.sh

在主库DB01的my.cnf配置文件加入下面内容

[mysqld]
secure-file-priv=/mysql/3306/tmp
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
report_host=10.0.0.53                 
report_port=3306
default_authentication_plugin=mysql_native_password
mysqlx=off  
binlog_checksum=NONE
​
binlog_transaction_dependency_tracking=WRITESET
#-- 在数据库8.0之后具有的配置，表示写集合配置信息，可以进一步提升SQL线程回放的并发度；（需要表有主键）
#-- 是可以实现跨事务并发执行
transaction_write_set_extraction=XXHASH64
#-- 定义写集合的hash算法信息，也属于数据库8.0之后具有的特性配置
#-- 以上两行参数信息不加上，就表示与5.7版本数据库可以进行兼容，可以理解为是优化参数
​
loose-group_replication_group_name="eb8441e9-8aef-4a86-a4bc-5beea315f04f"
#-- 设置组复制各个节点的统一唯一uuid标识信息，即同一组复制内部的唯一标识；
#-- 一样就表示可以加入同一组复制中，不同就表示不加入到相同的组复制中
loose-group_replication_start_on_boot=OFF
#-- 在组复制过程中也是需要启动相应线程，完成组复制任务的；
#-- 此参数配置表示在服务启动时，不自动运行启动组复制功能，一般都是进行手工启动
#-- 主要是防止数据库意外重启后，对组复制之间关系的影响，不能让重启后数据库自动加入到组复制中
loose-group_replication_local_address="10.0.0.53:33061"
#-- 表示定义本地主机数据库服务的内部通讯地址和端口
loose-group_replication_group_seeds="10.0.0.53:33061,10.0.0.54:33062,10.0.0.55:33063"
#-- 表示定义所有集群主机的内部通讯地址和端口
#-- 以上地址和端口信息，表示组复制集群内部通讯时，应用的地址和端口信息；
#-- 内部通讯需求：心跳检测、复制关系、日志同步、投票、选举...，都是通过内部地址和端口进行的;
loose-group_replication_bootstrap_group=OFF
#-- 表示是否将此节点作为引导节点
#-- 组复制在第一次进行配置时，需要先有引导节点，其他节点做为加入节点(joiner)，不能都是ON，否则会产生争抢问题
#-- 以上参数信息中loose，表示在没有组复制插件时，进行初始化操作只会报警告信息，而不会报错误提示

在从库DB02的my.cnf配置文件加入下面内容

[mysqld]
secure-file-priv=/mysql/3306/tmp
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
report_host=10.0.0.54
report_port=3306        
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
mysqlx=off
binlog_transaction_dependency_tracking=WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="eb8441e9-8aef-4a86-a4bc-5beea315f04f"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.54:33062"
loose-group_replication_group_seeds="10.0.0.53:33061,10.0.0.54:33062,10.0.0.55:33063"
loose-group_replication_bootstrap_group=OFF

在从库DB03的my.cnf配置文件加入下面内容

[mysqld]
secure-file-priv=/mysql/3306/tmp
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
report_host=10.0.0.55
report_port=3306
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
mysqlx=off
binlog_transaction_dependency_tracking=WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="eb8441e9-8aef-4a86-a4bc-5beea315f04f"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.55:33063"
loose-group_replication_group_seeds="10.0.0.53:33061,10.0.0.54:33062,10.0.0.55:33063"
loose-group_replication_bootstrap_group=OFF

启动实例

/mysql/3306/bin/mysqld start

步骤二：创建MGR群组，并加入主节点

所有节点创建管理员用户密码

mysql -S /mysql/3306/socket/mysql.sock -e "alter user 'root'@'localhost' identified with mysql_native_password by '123';"

所有节点安装MGR功能插件

mysql -uroot -p123 -S /mysql/3306/socket/mysql.sock -e "install plugin group_replication SONAME 'group_replication.so';"

所有节点创建主从同步连接用户信息

mysql -S /mysql/3306/socket/mysql.sock -uroot -p123

set sql_log_bin=0;
create user repl@'%' identified by '123';
create user repl@'localhost' identified by '123';
create user repl@'127.0.0.1' identified by '123';
grant replication slave,replication client on *.* to repl@'%';
grant replication slave,replication client on *.* to repl@'localhost';
grant replication slave,replication client on *.* to repl@'127.0.0.1';
flush privileges;
set sql_log_bin=1;
change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';   
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;

查看群组成员信息状态：

select * from performance_schema.replication_group_members;

步骤三：将从节点DB01和DB02加入到MGR群组中

reset master;   #-- 清除当前数据库中GTID信息
change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
start group_replication;

注意：

单主模式知识补充：
构建单主模式失败，如何进行排错重构单主模式：
步骤一：确认报错信息
-- 加入群组命令执行时，是否有报错提示
-- 加入群组之后，发现成员信息异常，需要查看日志内容 （从节点日志 主节点日志） 

步骤二：重新测试加入群组
stop group_replication;
reset master;
reset slave all;
set sql_log_bin=0;
change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
start group_replication;
sh
步骤三：查看详细日志信息

多主模式

多主模式：拥有多个主节点 写操作会均衡发送到多个主节点 没有了选举过程 应用场景：写的交互过程多的场景

一、方式一：由单主模式转换为多主模式

步骤一：激活多主模式功能

stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=1;

步骤二：重新将节点加入群组

在DB01上操作

set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;

分别在DB02、DB03上操作

reset master;
start group_replication;

查看群组成员信息

select * from performance_schema.replication_group_members;

二、方法二：直接构建多主模式 DB01 DB02 DB03 配置文件中统一添加两行配置

[mysqld]
loose-group_replication_single_primary_mode=OFF
loose-group_replication_enforce_update_everywhere_checks=1

ps:

在应用MGR组复制功能时，也存在一些应用的限制条件：
​
- 仅支持innodb存储引擎应用组复制功能；
  MGR集群中只支持innodb存储引擎，能够创建非innodb引擎的表，但是无法写入数据，向非innodb表写入数据直接报错；
- 数据表中必须有主键，或者非null的唯一键；
  MGR集群中只支持innodb存储引擎，并且该表必须有显示的主键，或者非null的唯一键，否则即使能够创建表，也无法向表中写数据
- 组复制存在网络限制，MGR组通信引擎目前仅支持IPv4网络，并且对节点间的网络性能要求较高；
  对于低延迟、高带宽的网络是部署MGR集群的基础；
- 组复制功能会自动忽略表锁和命名锁，在MGR中lock tables、unlock tables、get_lock、release_lock等这些表锁和命名锁将忽略
- MGR多主模式中，默认不支持 SERIALIZABLE 隔离级别，建议使用RC隔离级别；
- 组复制多主模式中，对同一个对象进行并发是有冲突的，ddl和dml操作导致这种冲突在部分成员节点中无法检测到；
  最终可能导致数据不一致
- 组复制多主模式中，不支持级联约束的外键，可能造成有冲突的操作无法检查；
- 组复制功能不支持超大事务同步；
- 组复制多主模式下可能导致死锁，比如select ... for update在不同节点执行，由于多节点锁无法共享，很容易导致死锁；
- 组复制是不支持复制过滤的，如果有节点设置了复制过滤功能，将影响节点间决议的达成；
- 组复制功能最多支持9个节点，当大于9个节点，将拒绝新节点的加入；
- 当事务信息比较多的时候，主从同步效率是比较慢的

七、MHA高可用搭建

环境配置

centos 7 mysql 8.0.27

DB01 主库 10.0.0.53 3306 开启gtid功能 binlog日志开启 DB02 从库 10.0.0.54 3306 开启gtid功能 binlog日志开启 DB03 从库 10.0.0.55 3306 开启gtid功能 binlog日志开启 (兼做管理节点)

步骤一：下载MHA相关软件包 mha4mysql-manager-0.58-0.el7.centos.noarch — mha管理服务器上 mha4mysql-node-0.58-0.el7.centos.noarch — 主从节点上

步骤二：部署数据库实例环境

bash mysql_example.sh

主库DB01 10.0.0.53配置文件编写

vim /mysql/3306/etc/my.cnf
​
autocommit=0
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3306/tmp
innodb_flush_method=O_DIRECT

从库DB02 10.0.0.54配置文件编写

vim /mysql/3306/etc/my.cnf
​
autocommit=0
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3306/tmp
innodb_flush_method=O_DIRECT

从库DB03 10.0.0.55配置文件编写

vim /mysql/3306/etc/my.cnf
​
autocommit=0
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/mysql/3306/tmp
innodb_flush_method=O_DIRECT

启动实例

/mysql/3306/bin/mysqld start

mysql -S /mysql/3306/socket/mysql.sock -uroot

步骤三：构建主从架构

主库进行操作

create user repl@'10.0.0.%' identified with mysql_native_password by '123456';
grant replication slave on *.* to repl@'10.0.0.%';

从库进行操作

change master to
master_host='10.0.0.53',
master_user='repl',
master_password='123456',
master_auto_position=1;
start slave;

步骤四：实现主从之间ssh互信

\rm -rf /root/.ssh   #-- 所有节点主机都要删除.ssh 
ssh-keygen
cd /root/.ssh 
mv id_rsa.pub authorized_keys
scp  -r  /root/.ssh  10.0.0.54:/root 
scp  -r  /root/.ssh  10.0.0.55:/root

ssh 10.0.0.53 date   #-- 每台高可用节点主机都要进行测试
ssh 10.0.0.54 date
ssh 10.0.0.55 date

步骤五：安装部署MHA软件程序

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

安装软件程序 — 所有节点安装Node软件依赖包

#-- 所有节点安装Node软件依赖包
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm 

#-- Manager软件安装（db03）
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 

# 在db01主库中创建mha需要的监控用户
create user mha@'10.0.0.%' identified with mysql_native_password by 'mha';
grant all privileges on *.* to mha@'10.0.0.%';

Manager配置文件准备（DB03）

#-- 创建配置文件目录
mkdir -p /etc/mha
#-- 创建日志目录
mkdir -p /var/log/mha/app1

#-- 编辑mha配置文件
cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager         
manager_workdir=/var/log/mha/app1                   
master_binlog_dir=/mysql/3306/log               
user=mha                                                        
password=mha                                  
ping_interval=2                              
repl_password=123456                             
repl_user=repl                                
ssh_user=root                                
[server1]                                     
hostname=10.0.0.53
port=3306                                  
[server2]            
hostname=10.0.0.54
port=3306
#candidate_master=1
[server3]
hostname=10.0.0.55
port=3306
EOF

检查mha服务运行环境

masterha_check_ssh   --conf=/etc/mha/app1.cnf 
Thu Jul  4 17:56:42 2024 - [info] All SSH connection tests passed successfully.
masterha_check_repl  --conf=/etc/mha/app1.cnf 
MySQL Replication Health is OK.

启动运行mha服务

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

#--remove_dead_master_conf: 当主节点故障后，会自动将主节点从高可用架构中删除
#--ignore_last_failover：   当高可用架构发生故障切换后，特别是多次切换后，可以忽略连续错误，修复高可用环境
#< /dev/null                可以跳过脚本或命令信息的交互过程


masterha_check_status --conf=/etc/mha/app1.cnf

构建数据库高可用架构需要满足哪些功能需求： 01 监控功能 02 选主功能 03 数据补偿 04 应用透明（VIP切换） 05 主从重构 06 报警通知 07 数据补偿（额外补偿）

(1) MHA监控主机节点 利用masterha_master_monitor脚本对主库进行周期性监控（ping_interval=2）

mysql -umha -pmha -h10.0.0.53 -e "select user();"

(2) MHA进行选主过程 步骤一：需要加载所有从节点信息，生成4个数组信息 alive 存活数组 主要用于探测存活的节点状态；当主库宕机后，探测的就是两个从库节点 latest 最新数组 表示获取日志最新的从库信息，即数据量最接近主库的从库（根据GTID信息 或 position信息） pref 备选数组 在数组中具有candidate_master参数判断条件，此参数可以放入配置文件节点中，便于节点优先选择为新主 bad 不选数组 如果设定了参数：no_master=1，表示相应节点不参与竞选主； 如果设定了参数：log_bin=0（二进制日志没开），表示相应节点不参与竞选主； 如何设定了参数：check_slave_delay，检查从库延迟主库100M数据信息日志量，表示节点不参与竞选主

步骤二：根据策略信息，将数组中的节点信息进行过滤筛选

序号	alive数组	latest数组	pref数组	bad数组	选主策略	选择规则
01	满足	满足	满足	不满足	优选选择	按照节点号码顺序选择
02	满足	满足	不满足	不满足	优选选择	按照节点号码顺序选择
03	满足	不满足	满足	不满足	优选选择	按照节点号码顺序选择
04	满足	不满足	不满足	不满足	优选活着节点	按照节点号码顺序选择

(3) MHA数据补偿

• 原主库SSH连接正常： 各个从节点自动调用：save_binary_logs脚本文件，立即保存缺失部分的bin_log，到各节点/var/tmp/目录；
• 原主库SSH连接异常： 各个从节点自动调用：apply_diff_relay_logs脚本文件，进行relay_log日志差异信息补偿；
• 额外特殊数据补充：（利用主库日志冗余机制） MHA提供了binlog_server功能，可以实时拉取主库的binlog日志到备份节点，从而进行数据额外补偿；

(4) MHA应用透明（vip） 步骤一：上传脚本文件app1.cnf master_ip_failover master_ip_online_change mha_check.sh send_report 放在/usr/local/bin/目录

#安装ifconfig
yum install net-tools -y

cd /usr/local/bin/
chmod +x /usr/local/bin/*      
dos2unix /usr/local/bin/*
vim master_ip_failover
my $vip = '10.0.0.50/24';
my $key = '1';
my $if = 'ens33';
my $ssh_start_vip = "/sbin/ifconfig $if:$key $vip";           -- 会在新的主节点上生成vip
my $ssh_stop_vip = "/sbin/ifconfig $if:$key down";            -- 会在原有主节点上关闭vip
my $ssh_Bcast_arp= "/sbin/arping -I ens33 -c 3 -A 10.0.0.50";

步骤二：编写MHA配置文件加载VIP切换脚本

vim /etc/mha/app1.cnf
[server default]
master_ip_failover_script=/usr/local/bin/master_ip_failover

步骤三：重启mha服务加载配置

masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf

步骤四：在现有主库中手工配置VIP地址

/sbin/ifconfig ens33:1 10.0.0.50/24

(5) MHA业务切换 将从节点的主从同步关系进行重置 10.0.0.53 -同步数据-> 10.0.0.54 10.0.0.53 -同步数据-> 10.0.0.55

(6) MHA故障报警 步骤一：编写脚本文件

vim /usr/local/bin/send_report
my $smtp='smtp.qq.com';
my $mail_from='1169497550@qq.com';
my $mail_user='1169497550';
my $mail_pass='lpxhwcwlixpohifa';
my $mail_to='1169497550@qq.com';
​
TLS_allowed => '1',

步骤二：编写MHA配置文件加载VIP切换脚本

vim /etc/mha/app1.cnf 
report_script=/usr/local/bin/send_report

步骤三：重启mha服务加载配置

masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf

(7) MHA数据额外补偿 步骤一：创建备份binlog日志目录（备份日志服务器创建-DB03）

mkdir -p /data/binlog_server/
cd /data/binlog_server/
mysqlbinlog -R --host=10.0.0.55 --user=mha --password=mha --raw --stop-never mysql-bin.000003 &

步骤二：编写配置文件信息

vim /etc/mha/app1.cnf
[binlog1]
no_master=1
#-- 不存于竞选
hostname=10.0.0.55
#-- 将日志额外补偿到哪个主机上
master_binlog_dir=/data/binlog_server/
#-- 日志额外补偿的存储目录
#PS：需要确保所有从节点，和备份服务器之间具有互信机制

步骤三：重启mha服务加载配置

masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf

(8) MHA故障修复过程 步骤一：重新恢复主库服务

/msyql/3306/bin/mysqld/ start
#PS：添加新的节点信息（数据迁移）

步骤二：重新构建主从关系

stop slave;
change master to
master_host='10.0.0.55',
master_user='repl',
master_password='123456',
master_auto_position=1;
start slave;

步骤三：写配置文件（恢复节点信息） 方式一：直接编写配置文件恢复

vim /etc/mha/app1.cnf 
hostname=10.0.0.53
port=3306                                  
[server2]            
hostname=10.0.0.54
port=3306

方式二：利用命令添加节点信息（删除节点信息）

masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=10.0.0.53 --block=server1 --params="port=3306"
masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=10.0.0.54 --block=server2 --params="port=3306"

步骤四：修复高可用额外补偿功能

cd /data/binlog_server
\rm -f mysql-bin.000003
mysqlbinlog -R --host=10.0.0.55 --user=mha --password=mha --raw --stop-never mysql-bin.000003 &

步骤五：重新运行mha服务

masterha_check_ssh   --conf=/etc/mha/app1.cnf 
masterha_check_repl  --conf=/etc/mha/app1.cnf 
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf

(9) 手工还原主节点角色

步骤一：编辑脚本文件（master_ip_online_change） 目的：可以实现手工切换主从关系时，自动调整VIP地址

vim /usr/local/bin/master_ip_online_change
my $vip = "10.0.0.50/24";
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key $vip down";
my $ssh_Bcast_arp= "/sbin/arping -I ens33 -c 3 -A 10.0.0.50";

#需要配置文件加载脚本信息：
vim /etc/mha/app1.cnf
master_ip_online_change_script=/usr/local/bin/master_ip_online_change

步骤二：需要停止MHA高可用服务

masterha_stop --conf=/etc/mha/app1.cnf
masterha_check_status --conf=/etc/mha/app1.cnf

步骤三：执行主从切换命令，完成手工高可用切换

masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=10.0.0.53 --orig_master_is_new_slave --running_updates_limit=10000

--master_state=alive            #-- 表示在主节点运行状态，完成主从切换
--new_master_host               #-- 选择切换后，将指定地址主机切换为主节点
--orig_master_is_new_slave     #-- 将切换前主节点身份变为从节点
--running_updates_limit=10000  #-- 在指定时间内，若未完成切换过程，会终止切换操作

#切换过程中需要执行以下命令，在原主库中
FLUSH NO_WRITE_TO_BINLOG TABLES
#https://blog.csdn.net/a772304419/article/details/139234093

步骤四：验证切换是否成功 1 、查看主从角色信息，以及主从同步

#主节点进行查看
db01 [(none)]>show slave hosts;
+-----------+-----------+------+-----------+--------------------------------------+
| Server_id | Host      | Port | Master_id | Slave_UUID                           |
+-----------+-----------+------+-----------+--------------------------------------+
|        52 | 10.0.0.52 | 3306 |        51 | e9b67524-39e4-11ef-9977-000c29f7e34a |
|        53 |           | 3306 |        51 | 49798a0f-39e5-11ef-98f3-000c29d789e0 |
+-----------+-----------+------+-----------+--------------------------------------+

#从节点进行查看
show slave status\G

2、 确认VIP地址信息是否漂移 查看新主节点是否有VIP地址 查看原主节点是否删除VIP地址

3、 恢复binlog日志备份功能

# 重构binlogserver功能
cd /data/binlog_server/
rm -rf ./*
#杀掉原有mysqlbinlog远程备份进程
mysqlbinlog -R --host=10.0.0.53 --user=mha --password=mha --raw --stop-never mysql-bin.000003 &

步骤五：重新启动MHA服务

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf

八、通过ProxySQL实现mysql读写分离

proxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离：

• proxySQL数据库中间件支持Query路由功能；（读写分类）
• pxoxySQL数据库中间件支持动态指定某个SQL进行缓存；
• proxySQL数据库中间件支持动态加载配置信息（无需重启ProxySQL服务）
• proxySQL数据库中间件支持故障切换和SQL的过滤功能（安全机制）

环境：centos7 mysql8.0.27 准备好三节点数据库+GTID复制环境+MHA环境（ 普通主从环境也可以构建 ）；

DB01 主库 10.0.0.53 3306 开启gtid功能 binlog日志开启 DB02 从库 10.0.0.54 3306 开启gtid功能 binlog日志开启 DB03 从库 10.0.0.55 3306 开启gtid功能 binlog日志开启 (兼做管理节点)

10.0.0.52作为proxysql服务器

在10.0.0.52 proxysql服务器上操作

步骤一：安装部署proxySQL软件

wget https://github.com/sysown/proxysql/releases/download/v2.6.3/proxysql-2.6.3-1-centos7.x86_64.rpm
yum install -y epel-release
yum install -y gnutls perl-DBD-MySQL perl-DBI
rpm -ivh proxysql-2.6.3-1-centos7.x86_64.rpm
systemctl start proxysql

步骤二：对软件进行配置

mysql -uadmin -padmin -h127.0.0.1 -P6032   #访问管理端

proxySQL配置方法： 运行时配置 ：运行时中的配置信息会立即生效 内存配置 ：实现读写分离功能，将配置项信息写入内存 （生效） 磁盘配置 ：将内存配置可以导入磁盘，重启proxysql服务可以加载配置信息

内存配置 -导入-> 运行时配置 load 运行时配置 -导入-> 内存配置 save 内存配置 -导入-> 磁盘配置 save 磁盘配置 -导入-> 内存配置 load

确保从库是只读状态：

set global read_only=1;

01 编写mysql_replication_hostgroups表内容

# 添加配置信息
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
​
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+
​
load mysql servers to runtime;  #配置生效
save mysql servers to disk;     #永久保存

02 编写mysql_servers表内容

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.50',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.54',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.55',3306);
​
load mysql servers to runtime;
save mysql servers to disk;sh

03 设置监控用户信息

#在主数据库中创建监控连接的用户信息
create user monitor@'%' identified with mysql_native_password by '123';
grant replication client on *.* to monitor@'%';

#在10.0.0.52 proxysql服务器上操作
#变量信息修改为方法一
set mysql-monitor_username='monitor';
set mysql-monitor_password='123';

#变量信息修改为方法二
update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
update global_variables set variable_value='123' where variable_name='mysql-monitor_password';


#查看监控用户配置信息：
select @@mysql-monitor_username;
select @@mysql-monitor_password;


load mysql variables to runtime;
save mysql variables to disk;

04 设置业务访问用户

#在主库进行用户创建
create user root@'%' identified with mysql_native_password by '123';
grant all on *.* to root@'%';

#在proxysql中添加数据库节点的业务用户信息
insert into mysql_users(username,password,default_hostgroup) values('root','123',10);

load mysql users to runtime;
save mysql users to disk;

05 配置编写读写规则

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

步骤三：读写分离测试

mysql -uroot -p123 -P6033 -h127.0.0.1 -e "begin;select @@server_id;commit"   # 53
mysql -uroot -p123 -P6033 -h127.0.0.1 -e "select @@server_id"                # 53 54 55

读写分离扩展应用： （1）根据端口实现读写分离

set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034'
# 使监听端⼝配置信息⽣效
save mysql variables to disk;
systemctl restart proxysql
​
# 设定相应读写分离路由规则
delete from mysql_query_rules;      #-- 为了测试效果，先清空已有规则信息
insert into mysql_quer y_rules (rule_id,active,proxy_port,destination_hostgroup,apply) values (1,1,6033,10,1),(2,1,6034,20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
#-- 除了基于端⼝进⾏分离，还可以基于监听地址（修改字段proxy_addr即可），也可以基于客⼾端地址（修改字段client_addr字段即可）；

（2）根据用户实现读写分离

insert into mysql_users (username,pas sword,default_hostgroup) values ('write','123',10),('reader','123',20);
load mysql users to runtime;
save mysql users to disk;
​
delete from mysql_query_rules;  #-- 为了测试效果，先清空已有规则信息
insert into mysql_query_rules (rule_id,active,username,destination_hostgroup,apply) values (1,1,'write',10,1),(2,1,'reader',20,1);
load mysql users to runtime;
save mysql users to disk;

proxysql读写分离配置过程总结

步骤	操作说明	涉及数据表信息	涉及操作信息
01	设置从库只读模式		read_only=1
02	添加主机组信息	mysql_replication_hostgroups
03	添加主机组节点信息	mysql_servers
04	添加用户信息（监控用户、应用用户）	global_variables mysql_users
05	添加读写分离规则	mysql_query_rules

九、数据库分布式存储 （MyCAT）

分布式架构理念：(基于业务逻辑分布式/基于程序逻辑分布式)

• 架构演变过程早期，为了满足主要业务功能需求，可以将所有程序部署在一个服务器节点上；
• 架构演变过程发展，为了满足主要业务压力增长，可以将所有程序拆分部署在不同服务器上；
• 架构演变过程发展，为了满足主要业务安全稳定，可以将数据库设计成主从架构或读写分离；
• 架构演变过程发展，为了满足多个业务数量增加，可以将架构环境根据业务的情况独立拆分；
• 架构演变过程发展，为了满足单独业务数量激增，可以将多个数据表进行拆分到多个节点上；(垂直拆分-MyCAT进行数据表信息整合)
• 架构演变过程发展，为了满足单独业务数量激增，可以将单个数据表进行拆分到多个节点上；(水平拆分-MyCAT进行数据表信息整合)
• 架构演变过程发展，为了满足业务的数据搜索业务需求、缓存业务需求、大数据业务分析需求，还要引入NOSQL或NewSQL数据库；

分布式存储服务部署过程

基础环境：centos7 mysql8.0.27

主机名称	地址信息	端口信息	数据库软件
db01	10.0.0.51	3307~3310（4个多实例-分两组）	MySQL-8.0.27
db02	10.0.0.52	3307~3310（4个多实例-分两组）	MySQL-8.0.27
mycat	10.0.0.53	8066：前端业务连接端口 9066：管理端口	Mycat-server-1.6.7.4

步骤一：数据库实例环境搭建

分别在db01和db02上创建实例3307，3308，3309，3310

bash mysql_example.sh

DB01主机中的实例配置：

#3307配置
vim /mysql/3307/etc/my.cnf
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#3308配置
vim /mysql/3308/etc/my.cnf
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#3309配置
vim /mysql/3309/etc/my.cnf
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#3310配置
vim /mysql/3310/etc/my.cnf
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

DB02主机中的实例配置：

#3307配置
vim /mysql/3307/etc/my.cnf
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#3308配置
vim /mysql/3308/etc/my.cnf
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#3309配置
vim /mysql/3309/etc/my.cnf
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#3310配置
vim /mysql/3310/etc/my.cnf
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

启动实例

/mysql/33{07,08,09,10}/bin/mysqld start

步骤二：建立多实例主从关系

红色部分：

DB01 3307 — DB02 3307 双主

#在DB02数据库进行操作
mysql -S /mysql/3307/socket/mysql.sock -e "create user repl@'10.0.0.%' identified with mysql_native_password by '123';grant replication slave on *.* to repl@'10.0.0.%';"
mysql -S /mysql/3307/socket/mysql.sock -e "create user root@'10.0.0.%' identified with mysql_native_password by '123';grant all on *.* to root@'10.0.0.%';"

#在DB01数据库上进行操作
mysql -S //mysql/3307/socket/mysql.sock -e "change master to master_host='10.0.0.52', master_port=3307,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S //mysql/3307/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3307/socket/mysql.sock -e "show slave status\G;"|grep Running;

#在DB02数据库上进行操作
mysql -S /mysql/3307/socket/mysql.sock -e "change master to master_host='10.0.0.51', master_port=3307,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S //mysql/3307/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3307/socket/mysql.sock -e "show slave status\G;"|grep Running;

DB01 3307（主） — 3309（从） 主从

mysql -S /mysql/3309/socket/mysql.sock -e "change master to master_host='10.0.0.51', master_port=3307,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S /mysql/3309/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3309/socket/mysql.sock -e "show slave status\G;"|grep Running;

DB02 3307（主） — 3309（从） 主从

mysql -S /mysql/3309/socket/mysql.sock -e "change master to master_host='10.0.0.52', master_port=3307,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S /mysql/3309/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3309/socket/mysql.sock -e "show slave status\G;"|grep Running;

蓝色部分：

DB01 3308 — DB02 3308 双主

#在DB02数据库进行操作
mysql -S /mysql/3308/socket/mysql.sock -e "create user repl@'10.0.0.%' identified with mysql_native_password by '123';grant replication slave on *.* to repl@'10.0.0.%';"
mysql -S /mysql/3308/socket/mysql.sock -e "create user root@'10.0.0.%' identified with mysql_native_password by '123';grant all on *.* to root@'10.0.0.%';"

#在DB01数据库上进行操作
mysql -S /mysql/3308/socket/mysql.sock -e "change master to master_host='10.0.0.52', master_port=3308,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S /mysql/3308/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3308/socket/mysql.sock -e "show slave status\G;"|grep Running;

#在DB02数据库上进行操作
mysql -S /mysql/3308/socket/mysql.sock -e "change master to master_host='10.0.0.51', master_port=3308,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S /mysql/3308/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3308/socket/mysql.sock -e "show slave status\G;"|grep Running;

DB01 3308（主）– 3310（从） 主从

mysql -S /mysql/3310/socket/mysql.sock -e "change master to master_host='10.0.0.51', master_port=3308,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S /mysql/3310/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3310/socket/mysql.sock -e "show slave status\G;"|grep Running;

DB02 3308（主）– 3310（从） 主从

mysql -S /mysql/3310/socket/mysql.sock -e "change master to master_host='10.0.0.52', master_port=3308,master_auto_position=1,master_user='repl',master_password='123';"
mysql -S /mysql/3310/socket/mysql.sock -e "start slave;"
mysql -S /mysql/3310/socket/mysql.sock -e "show slave status\G;"|grep Running;

#检查所有实例主从状态
mysql -S /mysql/3307/socket/mysql.sock -e "show slave status\G;"|grep Yes
mysql -S /mysql/3308/socket/mysql.sock -e "show slave status\G;"|grep Yes
mysql -S /mysql/3309/socket/mysql.sock -e "show slave status\G;"|grep Yes
mysql -S /mysql/3310/socket/mysql.sock -e "show slave status\G;"|grep Yes

注意：

#！！！如果主从同步异常，可以进行主从重置
mysql -S /mysql/3307/socket/mysql.sock -e "stop slave;reset slave all;"
mysql -S /mysql/3308/socket/mysql.sock -e "stop slave;reset slave all;"
mysql -S /mysql/3309/socket/mysql.sock -e "stop slave;reset slave all;"
mysql -S /mysql/3310/socket/mysql.sock -e "stop slave;reset slave all;"

步骤三：分布式软件下载安装 下载链接地址：http://dl.mycat.org.cn/

yum install -y java
tar xf Mycat-server-1.6.7.4-release-20200105164103-linux_.tar.gz -C /usr/local/
​
vim /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
source /etc/profile
​
mycat start
mysql -uroot -p123456 -h 127.0.0.1 -P8066

步骤四：编写配置mycat文件信息

vim /usr/local/mycat/conf/schema.xml
#第一个配置区域：定义逻辑库信息/定义数据节点名称
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" DataNode="dn1"></schema>
​
#第二个配置区域：调用数据节点信息，并设置主机名称信息，指定映射的物理库信息
<dataNode name="dn1" dataHost="localhost1" database="tabao" />
​
#第三个配置区域：配置真实的物理节点信息
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="localhost:3306" user="root"
                           password="123456">
        </writeHost>
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

读写分离配置信息：

vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
    <dataNode name="dn1" dataHost="localhost1" database="world" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
      <heartbeat>select user()</heartbeat>
      <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
          <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
      </writeHost>
    </dataHost>
</mycat:schema>

# 在db01-3307主节点进行操作
mysql -S /mysql/3307/socket/mysql.sock -e "create user root@'10.0.0.%' identified by '123';grant all on *.* to root@'10.0.0.%';"
mysql -S /mysql/3307/socket/mysql.sock -e "source /root/world-db/world.sql"
#world.sql是mysql的一个测试数据库文件

# 在db01-3308主节点进行操作
mysql -S /mysql/3308/socket/mysql.sock -e "create user root@'10.0.0.%' identified by '123';grant all on *.* to root@'10.0.0.%';"
mysql -S /mysql/3308/socket/mysql.sock -e "source /root/world-db/world.sql"

#重启mycat
mycat restart

读写分离访问测试：

mysql -uroot -p123456 -h 10.0.0.53 -P8066
#测试读效果
mysql> select @@server_id;
#测试写效果
mysql> begin;select @@server_id;commit;

实现高可用功能配置

vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
</schema>
    <dataNode name="sh1" dataHost="xiaoq1" database="world" />
    <dataHost name="xiaoq1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
      <heartbeat>select user()</heartbeat>
      <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
          <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
      </writeHost>
      <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
          <readHost host="db4" url="10.0.0.52:3309" user="root" password="123" />
      </writeHost>  
    </dataHost>
</mycat:schema>

#重启mycat
mycat restart

#读写分离访问测试：
mysql -uroot -p123456 -h 10.0.0.53 -P8066
#测试读效果
mysql> select @@server_id;
#测试写效果
mysql> begin;select @@server_id;commit;
​
#测试高可用功能
mysql -uroot -p123456 -h 10.0.0.53 -P8066
#测试读效果
mysql> select @@server_id;
#测试写效果
mysql> begin;select @@server_id;commit;

实现分布式存储数据 应用一：垂直分表设置

编写配置文件：schema.xml

vim /usr/local/mycat/conf/schema.xml
​
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
    <table name="user" dataNode="sh1" />
    <table name="order_t" dataNode="sh2" />
</schema>
    <dataNode name="sh1" dataHost="xiaoq1" database="taobao" />
    <dataNode name="sh2" dataHost="xiaoq2" database="taobao" />
    <dataHost name="xiaoq1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
      <heartbeat>select user()</heartbeat>
      <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
          <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
      </writeHost>
      <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
          <readHost host="db4" url="10.0.0.52:3309" user="root" password="123" />
      </writeHost>  
    </dataHost>
    
     <dataHost name="xiaoq2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
      <heartbeat>select user()</heartbeat>
      <writeHost host="db1" url="10.0.0.51:3308" user="root" password="123">
          <readHost host="db2" url="10.0.0.51:3310" user="root" password="123" />
      </writeHost>
      <writeHost host="db3" url="10.0.0.52:3308" user="root" password="123">
          <readHost host="db4" url="10.0.0.52:3310" user="root" password="123" />
      </writeHost>  
    </dataHost>
</mycat:schema>

在主从节点创建数据库和表信息

#奇数实例中，创建淘宝库和用户表
mysql -S /mysql/3307/socket/mysql.sock -e "create database taobao;use taobao;create table user (id int,name char(10));"
#偶数实例中，创建淘宝库和订单表
mysql -S /mysql/3308/socket/mysql.sock -e "create database taobao;use taobao;create table order_t (id int,name char(10));"

mycat restart

测试存储数据/读取数据信息：mycat服务进行的操作

mysql -uroot -p123456 -h 10.0.0.53 -P8066
mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
mysql> 
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
| user             |
+------------------+
2 rows in set (0.01 sec)
​
mysql> insert into user values (1,'boy'),(2,'girl');
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0
​
mysql> insert into order_t values (1,'xiaoA'),(2,'xiaoB');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
​
mysql> select * from user;
+------+---------+
| id   | name    |
+------+---------+
|    1 |   boy   |
|    2 |   girl  |
+------+---------+
2 rows in set (0.03 sec)
​
mysql> select * from order_t;
+------+-------+
| id   | name  |
+------+-------+
|    1 | xiaoA |
|    2 | xiaoB |
+------+-------+
2 rows in set (0.00 sec)

#查看数据存储信息：奇数实例中查看
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| user             |
+------------------+
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;select * from user;"
+------+---------+
| id   | name    |
+------+---------+
|    1 |   boy   |
|    2 |   girl  |
+------+---------+
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
+------------------+
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;select * from order_t;"
+------+-------+
| id   | name  |
+------+-------+
|    1 | xiaoA |
|    2 | xiaoB |
+------+-------+
​

应用二：水平分表设置

• 拆分策略：服务程序提供了很多种方案，其中可以根据最长用的range进行拆分，还有取模、枚举、日期、HASH… 几乎融合经典业务中大部分的分片策略，mycat已经开发了相应算法，非常方便调用
• 拆分条件：可以根据指定索引条件拆分，比如时间信息，订单编号，数据表id信息… 拆分条件就是作为分片条件的列，也称为分片键

分表方式一：拆分方式，基于范围拆分

编写配置文件：schema.xml

vim /usr/local/mycat/conf/schema.xml
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />

编写配置文件：rule.xml

#第一个部分：定义分片键
<tableRule name="auto-sharding-long">
    <rule>
            <columns>id</columns>                #--- 定义分片键
            <algorithm>rang-long</algorithm>     #--- 分片键的算法定义
    </rule>
</tableRule>

#第二个部分：设置分片策略的算法信息
<function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
</function>

vim autopartition-long.txt
0-10=0   #id范围在0-10
11-20=1  #id范围在11-20

mycat restart

分表方式二：基于取模方式拆分

编写配置文件：schema.xml

<table name="t4" dataNode="sh1,sh2" rule="mod-long" />

编写配置文件：rule.xml

<tableRule name="mod-long">
    <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
​
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
         <!-- how many data nodes -->
         <property name="count">2</property>
</function>

mycat restart

进行存储和查询测试

#奇数实例中，创建淘宝库和用户表
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
#偶数实例中，创建淘宝库和订单表
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -uroot -p123456 -h 10.0.0.53 -P8066
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');
insert into t4(id,name) values(6,'x'),(8,'y'),(10,'z');

#0--sh1 -- 奇数实例  3307  values(2,'b');  values(4,'d');   偶数数据 -- 3307 
#1--sh2 -- 偶数实例  3308  values(1,'a');  values(3,'c');   奇数数据 -- 3308
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;select * from t4;"
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  4 | d    |
|  6 | x    |
|  8 | y    |
| 10 | z    |
+----+------+
​
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;select * from t4;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | c    |
+----+------+

分表方式三：基于枚举方式进行分布式存储 编写配置文件：schema.xml

<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />

编写配置文件：rule.xml

<tableRule name="sharding-by-intfile">
    <rule>
            <columns>name</columns>
            <algorithm>hash-int</algorithm>
    </rule>
</tableRule>
​
<function name="hash-int"
                    class="io.mycat.route.function.PartitionByFileMap">
          <property name="mapFile">partition-hash-int.txt</property>
          <property name="type">1</property>
</function>

vim /usr/local/mycat/conf/partition-hash-int.txt
bj=0
sh=1
DEFAULT_NODE=1

mycat restart

进行存储和查询测试

#奇数实例中，创建淘宝库和用户表
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
#偶数实例中，创建淘宝库和订单表
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -uroot -p123456 -h 10.0.0.53 -P8066
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
commit;

#bj=0  -- sh1  3307   只有bj信息  
#sh=1  -- sh2  3308   所有sh信息   还会有其他城市信息
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;select * from t5;"
+----+------+
| id | name |
+----+------+
|  1 | bj   |
|  3 | bj   |
+----+------+
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;select * from t5;"
+----+------+
| id | name |
+----+------+
|  2 | sh   |
|  4 | sh   |
|  5 | tj   |
+----+------+

分布式存储扩展说明：

01 分布式存储全局表创建 编写配置文件：schema.xml

<table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2"  />

mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"

mycat restart

测试存储和查询数据

mysql -uroot -p123456 -h 10.0.0.53 -P8066
insert into t_area(id,name) values(1,'a');
insert into t_area(id,name) values(2,'b');
insert into t_area(id,name) values(3,'c');
insert into t_area(id,name) values(4,'d');
commit;

mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;select * from t_area;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;select * from t_area;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+

02 分布式存储ER表构建

编写配置文件：schema.xml

<table name="a" dataNode="sh1,sh2" rule="mod-long-xiaoQ">
    <childTable name="b" joinKey="aid" parentKey="id" />
</table>

编写配置文件：rule.xml

<tableRule name="mod-long-xiaoQ">
        <rule>
                <columns>id</columns>
                <algorithm>mod-long-xiaoQ</algorithm>
        </rule>
</tableRule>
​
<function name="mod-long-xiaoQ" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
</function>

mycat restart

测试存储和查询数据

mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null,aid int);"
​
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null,aid int);"

mysql -uroot -p123456 -h 10.0.0.53 -P8066
insert into a(id,name) values(1,'a');    #--分片节点1   3308
insert into a(id,name) values(2,'b');    #--分片节点0   3307
insert into a(id,name) values(3,'c');    #--分片节点1   3308
insert into a(id,name) values(4,'d');    #--分片节点0   3307
insert into a(id,name) values(5,'e');    #--分片节点1   3308
​
insert into b(id,addr,aid) values(1001,'bj',1);    #--分片节点1   3308
insert into b(id,addr,aid) values(1002,'sj',3);    #--分片节点1   3308
insert into b(id,addr,aid) values(1003,'sd',4);    #--分片节点0   3307
insert into b(id,addr,aid) values(1004,'we',2);    #--分片节点0   3307
insert into b(id,addr,aid) values(1005,'er',5);    #--分片节点1   3308
commit;

mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;select * from a;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | c    |
|  5 | e    |
+----+------+
mysql -S /mysql/3308/socket/mysql.sock -e "use taobao;select * from b;"
+------+------+------+
| id   | addr | aid  |
+------+------+------+
| 1001 | bj   |    1 |
| 1002 | sj   |    3 |
| 1005 | er   |    5 |
+------+------+------+
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;select * from a;"
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  4 | d    |
+----+------+
mysql -S /mysql/3307/socket/mysql.sock -e "use taobao;select * from b;"
+------+------+------+
| id   | addr | aid  |
+------+------+------+` `
| 1003 | sd   |    4 |
| 1004 | we   |    2 |
+------+------+------+