mysql主从数据库同步

时分合理
138 阅读5分钟

首先我准备了一台虚拟机 其中安装了docker 进行部署mysql

**> 目前测试了两个相同名称数据库且创建相同一结构的表

1、创建docker-componse文件进行mysql部署

注意一些路径映射

创建/root/docker/mysql文件 mkdir root/docker/mysql

创建/root/docker/mysql/logs文件 mkdir root/docker/mysql/logs

设置logs文件可读可写可执行 chmod 777 logs

version: '3'
services:
  mysql:
    hostname: mysql
    image: mysql:5.7.26
    # network_mode: "host" # 如果需要容器使用宿主机IP(内网IP)，则可以配置此项
    container_name: mysql # 指定容器名称，如果不设置此参数，则由系统自动生成
    restart: unless-stopped # 设置容器自启模式
    command: mysqld
    environment:
      - TZ=Asia/Shanghai # 设置容器时区与宿主机保持一致
      - MYSQL_ROOT_PASSWORD=root # 设置root密码
    ports:
      - 3306:3306
    volumes:
       # 数据挂载目录自行修改哦！
       - /etc/localtime:/etc/localtime:ro # 设置容器时区与宿主机保持一致
       - /data/mysql/data:/var/lib/mysql/data # 映射数据库保存目录到宿主机，防止数据丢失
       - /root/docker/mysql/my.cnf:/etc/mysql/my.cnf # 映射数据库配置文件
       - /root/docker/mysql/logs:/var/log/mysql # 数据库文件
   
  mysql2:
    hostname: mysql2
    image: mysql:5.7.26
    # network_mode: "host" # 如果需要容器使用宿主机IP(内网IP)，则可以配置此项
    container_name: mysql2 # 指定容器名称，如果不设置此参数，则由系统自动生成
    restart: unless-stopped # 设置容器自启模式
    command: mysqld
    environment:
      - TZ=Asia/Shanghai # 设置容器时区与宿主机保持一致
      - MYSQL_ROOT_PASSWORD=root # 设置root密码
    ports:
      - 3366:3306
    volumes:
       # 数据挂载目录自行修改哦！
       - /etc/localtime:/etc/localtime:ro # 设置容器时区与宿主机保持一致
       - /root/docker/mysql2/data:/var/lib/mysql/data # 映射数据库保存目录到宿主机，防止数据丢失
       - /root/docker/mysql2/my.cnf:/etc/mysql/my.cnf # 映射数据库配置文件

2、设置主数据库的my.cnf配置文件

[client]
port = 3306
socket = /var/lib/mysql/data/mysql.sock
[mysqld]
 # 针对5.7版本执行group by字句出错问题解决
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
 # 一般配置选项
basedir = /var/lib/mysql
datadir = /var/lib/mysql/data
port = 3306
server-id=1 #主myslq编号
log_bin=/var/log/mysql/mysqlbin
# 日志的缓存时间,设置5天
expire_logs_days=5
#日志的最大大小，设置5G
max_binlog_size=1G
#同步的数据库名称
binlog_do_db=test
#忽略同步的数据库
#binlog_ignore_db = mysql
lc-messages-dir = /usr/share/mysql # 务必配置此项，否则执行sql出错时，只能显示错误代码而不显示具体错误消息
character-set-server=utf8mb4
back_log = 300
max_connections = 3000
max_connect_errors = 50
table_open_cache = 4096
max_allowed_packet = 32M
#binlog_cache_size = 4M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 64M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
#tx_isolation = READ-COMMITTED
tmp_table_size = 64M
#log-bin=mysql-bin
long_query_time = 6
innodb_buffer_pool_size = 1024M
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
wait_timeout= 31536000
interactive_timeout= 31536000
lower_case_table_names = 1

3、主数据库3306

创建mysql容器

进入docker-componse同级目录下

docker-componse up -d mysql

查看主数据库是否已经开启binlog on是已经开启

show variables like '%log_bin%'

image.png

创建同步账号

登进主数据库

mysql -u root -p root

创建同步账号

CREATE USER 'tbuser'@'%' IDENTIFIED BY '2001213';

赋予权限

GRANT REPLICATION SLAVE ON *.* TO 'tbuser'@'%' IDENTIFIED BY '2001213';

刷新用户权限

flush privileges;

查看主的状态

show master status;

image.png

4、从数据库3366

my.cnf

server-id=2 设置跟主数据库不一样

##同步的数据库名称

replicate_do_db=test

[client]
port = 3366
socket = /var/lib/mysql/data/mysql.sock
[mysqld]
 # 针对5.7版本执行group by字句出错问题解决
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
 # 一般配置选项
basedir = /var/lib/mysql
datadir = /var/lib/mysql/data
port = 3306
socket = /var/lib/mysql/data/mysql.sock
#设置一个serviceid，不允许与其他服务器的重复
server-id=2
##日志的缓存时间,5天
expire_logs_days=5
##日志的最大大小，5G
max_binlog_size=1G
##同步的数据库名称
replicate_do_db=test
##忽略同步的数据库
##replicate_ignore_db = mysql
lc-messages-dir = /usr/share/mysql # 务必配置此项，否则执行sql出错时，只能显示错误代码而不显示具体错误消息
character-set-server=utf8mb4
back_log = 300
max_connections = 3000
max_connect_errors = 50
table_open_cache = 4096
max_allowed_packet = 32M
#binlog_cache_size = 4M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 64M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
#tx_isolation = READ-COMMITTED
tmp_table_size = 64M
#log-bin=mysql-bin
long_query_time = 6
innodb_buffer_pool_size = 1024M
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
wait_timeout= 31536000
interactive_timeout= 31536000
lower_case_table_names = 1

重启从库mysql

docker restart mysql2

登录从mysql

mysql -uroot -proot

设置主数据库

change master to master_host='192.168.0.102', master_user='tbuser', master_password='2001213', master_port=3306, master_log_file='mysqlbin.000004', master_log_pos=688;

master_log_file='mysqlbin.000004', master_log_pos=688; 这两个值位于主数据库的

image.png

启动同步

start slave;

附上停止同步的命令：

stop slave;

查看同步状态

show slave status\G;

image.png Slave_IO_Running和Slave_SQL_Running两个字段为Yes就代表目前正常同步

同步效果主添加一条数据 从数据库会自动同步

从库

image.png 主库

image.png

主mysql报错 Initializing database

2023-11-28T06:44:50.826008Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

mysqld: File '/var/log/mysql/mysqlbin.index' not found (Errcode: 13 - Permission denied)

2023-11-28T06:44:50.827435Z 0 [ERROR] Aborting

将log目录权限打开

  • /root/docker/mysql/logs:/var/log/mysql # 数据库文件

chmod 777 /root/docker/mysql/logs

avatar
文章
阅读
粉丝