ZGGSONG WIKI

Mysql主从复制

数据库主从复制

主数据库:增删改

从数据库:查

创建主数据库容器

docker run -d \                                                                                                      
--name mariadb-master \
-p 3308:3306 \
-v  /Users/song/docker/sqldata/mariadb-master/log:/var/log/mysql \
-v  /Users/song/docker/sqldata/mariadb-master/data:/var/lib/mysql \
-v  /Users/song/docker/sqldata/mariadb-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=jiaobaba \
mariadb

主服务器配置

vim /Users/song/docker/sqldata/mariadb-master/conf/my.cnf

[mysqld]
server_id=101
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062

docker restart mariadb-master

创建从数据库容器

docker run -d \                                                                                                      
--name mariadb-slave \
-p 3308:3306 \
-v  /Users/song/docker/sqldata/mariadb-slave/log:/var/log/mysql \
-v  /Users/song/docker/sqldata/mariadb-slave/data:/var/lib/mysql \
-v  /Users/song/docker/sqldata/mariadb-slave/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=jiaobaba \
mariadb

从服务器配置

vim /Users/song/docker/sqldata/mariadb-slave/conf/my.cnf

[mysqld]
server_id=102
binlog-ignore-db=mysql
log-bin=mall-mysql-slave1-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=mall-mysql-relay-bin
log_slave_updates=1
read_only=1

docker restart mariadb-slave

创建数据同步用户

create user 'slave'@'%' identified by 'jiaobaba';

授权数据同步用户

grant replication slave, replication client on *.* to 'slave'@'%';

在主数据库查看配置信息

show master status \G;

获取master_log_posmaster_log_file

提前测试连通性mysql -uroot -h${ip} -P3307 -p

在从数据库中配置主从复制

change master to master_host='172.20.10.10', master_user='slave', master_password='jiaobaba', master_port=3307, master_log_file='mall-mysql-bin.000001', master_log_pos=672, master_connect_retry=30;

在从数据库中查看主从同步状态

show slave status \G;

在从数据库中开启主从同步

start slave;
Copyright © 2022 ZGGSONG