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
  14. # 主从配置
  15. log-bin=binlog
  16. server-id=121
  17. gtid-mode=on
  18. enforce-gtid-consistency=on
  19. log-slave-updates=on
  20. expire_logs_days=14
  22. # Compatible with versions before 8.0
  23. default_authentication_plugin=mysql_native_password
  24. skip-host-cache
  25. skip-name-resolve
  27. [client]
  28. #设置客户端编码
  29. default-character-set=utf8mb4
  30. [mysql]
  31. # 设置mysql客户端默认编码
  32. default-character-set=utf8mb4
  34. # Custom config should go here
  35. !includedir /etc/mysql/conf.d/
  36. # Custom config should go here
  37. !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
  14. # 主从配置
  15. server-id=122
  16. gtid-mode=on
  17. enforce-gtid-consistency=on
  18. log-slave-updates=on
  19. expire_logs_days=14
  21. # Compatible with versions before 8.0
  22. default_authentication_plugin=mysql_native_password
  23. skip-host-cache
  24. skip-name-resolve
  26. [client]
  27. #设置客户端编码
  28. default-character-set=utf8mb4
  29. [mysql]
  30. # 设置mysql客户端默认编码
  31. default-character-set=utf8mb4
  33. # Custom config should go here
  34. !includedir /etc/mysql/conf.d/
  35. # Custom config should go here
  36. !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;
  12. # 开启主从同步
  13. mysql>  start slave;
  14. # 再查看主从同步状态
  15. 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/   #备份文件存储路径
  9. #备份
  11. /usr/bin/mysqldump -u$DB_USER -p$DB_PASS -h 127.0.0.1 |gzip > ${BACKUP}\/rainbond_${DATE}.sql.gz
  13. #保留最近30天的备份文件
  15. 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