MySQL主从配置

此文档描述MySQL主从配置过程;该方案在主数据库出现宕机时并不会自行切换至备数据库,备数据库仅提供备份功能;欢迎参与 Rainbond开源社区 提供数据库高可用自动切换方案。

相关信息

数据库类型版本字符编码
MySQL8.0utf8mb4

主从配置

环境准备

  • 硬件资源

根据 软件和硬件环境要求 准备硬件资源。

  • 获取镜像

在主从服务器中执行以下命令获取镜像:

  1. docker pull mysql
  • 配置文件

容器启动时需要分别挂载主从数据库的配置文件

主数据库

  1. $ vi /var/lib/mysql/my.cnf
  2. [mysqld]
  3. pid-file = /var/run/mysqld/mysqld.pid
  4. socket = /var/run/mysqld/mysqld.sock
  5. datadir = /var/lib/mysql
  6. secure-file-priv= NULL
  7. # Disabling symbolic-links is recommended to prevent assorted security risks
  8. symbolic-links=0
  9. # 服务端默认utf8编码
  10. character-set-server=utf8mb4
  11. # 默认存储引擎
  12. default-storage-engine=INNODB
  13. # 主从配置
  14. log-bin=binlog
  15. server-id=121
  16. gtid-mode=on
  17. enforce-gtid-consistency=on
  18. log-slave-updates=on
  19. expire_logs_days=14
  20. # Compatible with versions before 8.0
  21. default_authentication_plugin=mysql_native_password
  22. skip-host-cache
  23. skip-name-resolve
  24. [client]
  25. #设置客户端编码
  26. default-character-set=utf8mb4
  27. [mysql]
  28. # 设置mysql客户端默认编码
  29. default-character-set=utf8mb4
  30. # Custom config should go here
  31. !includedir /etc/mysql/conf.d/
  32. # Custom config should go here
  33. !includedir /etc/mysql/conf.d/

从数据库

  1. $ vi /var/lib/mysql/my.cnf
  2. [mysqld]
  3. pid-file = /var/run/mysqld/mysqld.pid
  4. socket = /var/run/mysqld/mysqld.sock
  5. datadir = /var/lib/mysql
  6. secure-file-priv= NULL
  7. # Disabling symbolic-links is recommended to prevent assorted security risks
  8. symbolic-links=0
  9. # 服务端默认utf8编码
  10. character-set-server=utf8mb4
  11. # 默认存储引擎
  12. default-storage-engine=INNODB
  13. # 主从配置
  14. server-id=122
  15. gtid-mode=on
  16. enforce-gtid-consistency=on
  17. log-slave-updates=on
  18. expire_logs_days=14
  19. # Compatible with versions before 8.0
  20. default_authentication_plugin=mysql_native_password
  21. skip-host-cache
  22. skip-name-resolve
  23. [client]
  24. #设置客户端编码
  25. default-character-set=utf8mb4
  26. [mysql]
  27. # 设置mysql客户端默认编码
  28. default-character-set=utf8mb4
  29. # Custom config should go here
  30. !includedir /etc/mysql/conf.d/
  31. # Custom config should go here
  32. !includedir /etc/mysql/conf.d/

主数据库

启动数据库

  1. docker run --name mysql_master --restart=always \
  2. -p 3306:3306 \
  3. -v /var/lib/mysql/my.cnf:/etc/mysql/my.cnf \
  4. -v /var/lib/mysql/data:/var/lib/mysql \
  5. -e MYSQL_ROOT_PASSWORD=eed1eu.s0S \
  6. -d mysql

查看数据库字符编码(可选),并创建用户授权

  1. # 进入数据库
  2. docker exec -it mysql_master bash
  3. # 创建用户授权
  4. mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
  5. mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
  6. mysql> flush privileges;

获取主节点当前binary log文件名和位置(position)

  1. mysql> SHOW MASTER STATUS;
  2. +---------------+----------+--------------+------------------+------------------------------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +---------------+----------+--------------+------------------+------------------------------------------+
  5. | binlog.000003 | 868 | | | 1b009ef8-a67f-11ea-8c9a-0242ac110002:1-8 |
  6. +---------------+----------+--------------+------------------+------------------------------------------+
  7. 1 row in set (0.00 sec)

从数据库

启动数据库

  1. docker run --name mysql_slave --restart=always \
  2. -p 3306:3306 \
  3. -v /var/lib/mysql/my.cnf:/etc/mysql/my.cnf \
  4. -v /var/lib/mysql/data:/var/lib/mysql \
  5. -e MYSQL_ROOT_PASSWORD=eed1eu.s0S \
  6. -d mysql

配置主从复制

  1. # 进入数据库
  2. docker exec -it mysql_slave bash
  3. # 主从配置
  4. mysql> CHANGE MASTER TO
  5. mysql> MASTER_HOST='192.168.0.162',
  6. mysql> MASTER_USER='slave',
  7. mysql> MASTER_PASSWORD='slave',
  8. mysql> MASTER_PORT=3306,
  9. mysql> MASTER_LOG_FILE='binlog.000003',
  10. mysql> MASTER_LOG_POS=868;
  11. # 开启主从同步
  12. mysql> start slave;
  13. # 再查看主从同步状态
  14. mysql> show slave status;

这里只要看到两个参数Slave_IO_Running和Slave_SQL_Running都为true且Error字段都为空则代码主从正常复制

建库

在主服务器创建Rainbond部署所需的数据库,查看从服务器是否同步更新了数据

在主库创建库

  1. mysql> create database console;
  2. mysql> create database region;

在从库查看

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | console |
  7. | mysql |
  8. | performance_schema |
  9. | region |
  10. | sys |
  11. +--------------------+
  12. 5 rows in set (0.00 sec)

数据同步成功,则主从复制部署完成

创建用户并授权

在主库创建用户并授权,为Rainbond后续部署做准备

  1. mysql> CREATE USER rainbond;
  2. mysql> ALTER USER 'rainbond'@'%' IDENTIFIED WITH mysql_native_password BY 'Gz1ea3.G';
  3. mysql> GRANT ALL PRIVILEGES ON *.* TO 'rainbond'@'%';

从节点配置备份任务

  • 备份脚本
  1. $ mkdir -p /var/lib/mysql/backup
  2. $ vi /var/lib/mysql/backup/mysql-backup.sh
  3. #!/bin/bash
  4. DATE=`date +%Y%m%d%H%M`
  5. DB_USER=rainbond #数据库用户名
  6. DB_PASS="Gz1ea3.G" #数据库密码
  7. BACKUP=/var/lib/mysql/backup/ #备份文件存储路径
  8. #备份
  9. /usr/bin/mysqldump -u$DB_USER -p$DB_PASS -h 127.0.0.1 |gzip > ${BACKUP}\/rainbond_${DATE}.sql.gz
  10. #保留最近30天的备份文件
  11. find ${BACKUP} -name "rainbond_*.sql.gz" -type f -mtime +30 -exec rm {} \; > /dev/null 2>&1
  • 配置计划任务
  1. $ crontab -e
  2. 0 3 * * * /var/lib/mysql/backup/mysql-backup.sh
  • 赋予执行权限
  1. chmod +x /var/lib/mysql/backup/mysql-backup.sh