meteorplan的博客

# MySql

# 单机模式


version: '3'
services:
  mysql:
    restart: always
    image: mysql:5.7.42
    container_name: mysql
    ports:
      - "3306:3306"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_general_ci
      - --explicit_defaults_for_timestamp=true
      - --lower_case_table_names=1
      - --max_allowed_packet=128M
    volumes:
      - ./conf:/etc/mysql/conf.d/mysql.cnf
      - mysql-data:/var/lib/mysql

volumes:
  mysql-data:

# 主从模式

  
version: '3'

services:
  mysql-master:
    restart: always
    image: mysql:5.7.42
    container_name: mysql-master
    ports:
      - "3306:3306"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_general_ci
      - --explicit_defaults_for_timestamp=true
      - --lower_case_table_names=1
      - --max_allowed_packet=128M
      - --server-id=1
      - --log-bin=mysql-bin
      - --binlog-ignore-db=mysql
    volumes:
      - ./conf/master:/etc/mysql/conf.d/mysql.cnf
      - mysql-master-data:/var/lib/mysql

  mysql-slave:
    restart: always
    image: mysql:5.7.42
    container_name: mysql-slave
    ports:
      - "3307:3306"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_ROOT_HOST: '%'
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_general_ci
      - --explicit_defaults_for_timestamp=true
      - --lower_case_table_names=1
      - --max_allowed_packet=128M
      - --server-id=2
      - --relay-log=mysqld-relay-bin
      - --log-bin=mysql-bin
      - --read-only=1
    depends_on:
      - mysql-master
    volumes:
      - ./conf/slave:/etc/mysql/conf.d/mysql.cnf
      - mysql-slave-data:/var/lib/mysql

volumes:
  mysql-master-data:
  mysql-slave-data:

# 集群模式


version: '3'

services:
  mysql1:
    image: mysql:5.7.42
    container_name: mysql1
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_ROOT_HOST: '%'
      MYSQL_DATABASE: testdb
      MYSQL_USER: repl
      MYSQL_PASSWORD: repl_pass
    ports:
      - "3306:3306"
    command: >
      --server-id=1
      --log-bin='mysql-bin-1.log'
      --binlog_format=row
      --gtid_mode=ON
      --enforce-gtid-consistency=ON
      --master-info-repository=TABLE
      --relay-log-info-repository=TABLE
      --transaction-write-set-extraction=XXHASH64
      --loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
      --loose-group_replication_start_on_boot=off
      --loose-group_replication_local_address= "mysql1:33061"
      --loose-group_replication_group_seeds= "mysql1:33061,mysql2:33062,mysql3:33063"
      --loose-group_replication_bootstrap_group=off
      --loose-group_replication_single_primary_mode=off
      --loose-group_replication_enforce_update_everywhere_checks=on
    volumes:
      - ./conf/mysql1:/etc/mysql/conf.d
      - mysql1-data:/var/lib/mysql

  mysql2:
    image: mysql:5.7.42
    container_name: mysql2
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_ROOT_HOST: '%'
      MYSQL_DATABASE: testdb
      MYSQL_USER: repl
      MYSQL_PASSWORD: repl_pass
    ports:
      - "3307:3306"
    command: >
      --server-id=2
      --log-bin='mysql-bin-2.log'
      --binlog_format=row
      --gtid_mode=ON
      --enforce-gtid-consistency=ON
      --master-info-repository=TABLE
      --relay-log-info-repository=TABLE
      --transaction-write-set-extraction=XXHASH64
      --loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
      --loose-group_replication_start_on_boot=off
      --loose-group_replication_local_address= "mysql2:33062"
      --loose-group_replication_group_seeds= "mysql1:33061,mysql2:33062,mysql3:33063"
      --loose-group_replication_bootstrap_group=off
      --loose-group_replication_single_primary_mode=off
      --loose-group_replication_enforce_update_everywhere_checks=on
    volumes:
      - ./conf/mysql2:/etc/mysql/conf.d
      - mysql2-data:/var/lib/mysql

  mysql3:
    image: mysql:5.7.42
    container_name: mysql3
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_ROOT_HOST: '%'
      MYSQL_DATABASE: testdb
      MYSQL_USER: repl
      MYSQL_PASSWORD: repl_pass
    ports:
      - "3308:3306"
    command: >
      --server-id=3
      --log-bin='mysql-bin-3.log'
      --binlog_format=row
      --gtid_mode=ON
      --enforce-gtid-consistency=ON
      --master-info-repository=TABLE
      --relay-log-info-repository=TABLE
      --transaction-write-set-extraction=XXHASH64
      --loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
      --loose-group_replication_start_on_boot=off
      --loose-group_replication_local_address= "mysql3:33063"
      --loose-group_replication_group_seeds= "mysql1:33061,mysql2:33062,mysql3:33063"
      --loose-group_replication_bootstrap_group=off
      --loose-group_replication_single_primary_mode=off
      --loose-group_replication_enforce_update_everywhere_checks=on
    volumes:
      - ./conf/mysql3:/etc/mysql/conf.d
      - mysql3-data:/var/lib/mysql

volumes:
  mysql1-data:
  mysql2-data:
  mysql3-data:

# 配置完主从模式执行命令

-- 主服务器
  ## 远程访问
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
  ## 刷新权限
flush privileges;
  ## 显示主库状态
show master status;

-- 从服务器
  ## 远程访问
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
  ## 远程访问
flush privileges;

  ## 配置要跟从的主库
  ## port是数字类型
use ds0;
change master to master_host='你主库的ip地址',master_port=3307,master_user='root',master_password='123456';

  ## 开启主从
start slave;

  ## 查看主从状态
show slave status\G

CHANGE MASTER TO MASTER_HOST='主服务器IP地址', MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_LOG_FILE='主服务器LOG文件名', MASTER_LOG_POS=主服务器LOG位置;
START SLAVE;


-- 主服务器
SHOW MASTER STATUS;
-- 从服务器
SHOW SLAVE STATUS\G;

# 多个从节点选主

  1. 从节点的健康状况
  2. 每个从节点最后一次接收到的二进制日志的位置：拥有最新数据的从节点将有更高的机会被选为新的主节点。

# 故障切换的基本步骤

  1. 检测主节点失败：每个从节点都会定期检查主节点的状态，如果发现主节点不可达，它会记录错误并尝试连接到其他从节点。
  2. 收集信息：从节点会收集关于其他从节点的信息，包括它们最后一次接收到的二进制日志的位置。
  3. 选择新的主节点：基于上述信息，mysqlfailover工具或类似的工具会选择一个拥有最新数据且健康的从节点作为新的主节点。
  4. 故障切换：选定新的主节点后，其他从节点将停止复制并开始复制到新的主节点。
  5. 通知应用程序：应用程序或管理员可能会收到通知，告知已经完成了故障切换。

# 主从延迟解决方案

  1. 检查网络延迟：主从服务器之间的网络延迟可能导致复制延迟。可以使用ping命令检查网络延迟，并优化网络配置或将主从服务器放在同一局域网内来降低延迟。
  2. 优化从库配置：增加从库的内存、CPU等硬件资源，或调整从库的参数配置，如增加redo log大小、调整binlog格式等，可以提高从库的处理能力，从而减少复制延迟。
  3. 使用并行复制：MySQL 5.6及以上版本支持并行复制，可以在从库上开启并行复制，提高复制效率，减少延迟。
  4. 使用半同步复制：MySQL 5.5及以上版本支持半同步复制，可以在主从服务器之间使用半同步复制，提高数据同步的可靠性和效率。
  5. 强制走主库方案（强一致性）：对于对实时性要求高的系统，可以将从服务器只当备份使用，数据从缓存返回，降低主服务器压力。
  6. 判断主备无延迟方案：例如判断seconds_behind_master参数是否已经等于0、对比位点等。
  7. 优化SQL语句：检查并优化SQL语句，减少锁表等操作，提高SQL执行效率。
  8. 主从切换：如果主从复制延迟过大，可以考虑暂时切换主从角色，使用主服务器作为备份服务器，待延迟问题解决后再切换回来。

# Mysql备份

# 创建mysql备份脚本
vim dbbackup.sh

#!/bin/bash

#c3 为容器名称（原mysql）
docker exec c3 mysqldump -uroot -pOKQnk89LzycG9jq54oy1 lx > /usr/local/docker/mysql/mysqlbackup/lx`date +%Y-%m-%d-%H:%M:%S`.sql

cd /usr/local/docker/mysql/mysqlbackup

rm -rf `find . -name '*.sql' -mtime 15`  #删除15天前的备份文件


# 设置权限
chmod +x dbbackup.sh



# 添加 cron任务
crontab -e

#每天凌晨2点执行
0 2 * * * /usr/local/docker/mysql/dbbackup.sh

# 验证 cron 作业
crontab -l

# ShardingSphere(分库分表策略)

sharding sphere官网 (opens new window)

#项目
lx-alibaba

#参考博客
https://blog.csdn.net/Localtrant/article/details/135980380

#yml

spring:
  shardingsphere:
    datasource:
      names: m1,m2     #指定多个数据库名称
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://39.106.81.39:3306/lx-alibaba?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: OKQnk89LzycG9jq54oy1
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://39.106.81.39:3306/lx-alibaba?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: OKQnk89LzycG9jq54oy1
    #分库分表配置
    sharding:
      tables:
        t_order: #指定一个表名称
#          actual-data-nodes: m$->{1..2}.t_order_$->{1..2}     #数据库.表名
          actual-data-nodes: m1.t_order_$->{1..2}    #数据库.表名
          key-generator: #主键自动生成策略
            column: id
            type: SNOWFLAKE     #使用雪花ID
          table-strategy: # 分表策略
            inline: # inline策略
              sharding-column: id # 分表字段
              algorithm-expression: t_order_$->{id % 2 + 1} # 分表算法，id取模2再加1，保证结果为1或2
#          database-strategy: #分库策略
#            inline: #inline策略
#              sharding-column: id     #分库字段
#              algorithm-expression: m$->{id % 2 + 1}    #分库算法，求模取余算法
    props:
      sql:
        show: true

#测试地址
http://localhost:8082/restful/order/save
# 查看锁
show  processlist;
#当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
#当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
#锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
# 注释
# 方法一
select * from table;

-- 方法二
select * from table;

/*
方法三
*/
select * from table;

# mysql解压版安装

https://blog.csdn.net/m0_74823292/article/details/144300499

mysqldump -h rm-2zesceu69w25bs542.mysql.rds.aliyuncs.com -P 3306 -u kuxiaoxiao -p kxx_xxk_pro pt_order > pt_order.sql

lau12h@d#quaz2z*3n$^lviso

← MySql常用命令 →