mysql 主从设置

最新推荐文章于 2024-09-13 16:04:09 发布

后天星期一

最新推荐文章于 2024-09-13 16:04:09 发布

阅读量95

点赞数

分类专栏： docker 数据库

本文链接：https://blog.csdn.net/zhang168/article/details/115406183

版权

docker 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在Docker环境下配置MySQL主从复制，包括主库my.cnf配置、从库my.cnf配置、权限设置、主从同步以及错误排查。重点讨论了账号授权、日志文件定位、错误处理和同步状态检查等关键步骤，旨在帮助读者解决实际操作中的问题。

摘要由CSDN通过智能技术生成

docker run -p 3310:3306 --name mysql-master  -v /data/mysql2/master/log:/var/log/mysql -v /data/mysql2/master/data:/var/lib/mysql/ -v /data/mysql2/master/conf:/etc/mysql/conf.d -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_PASSWORD=root.123 -d mysql:5.7

docker run -p 3311:3306 --name mysql-slaver-01  -v /data/mysql2/slaver1/log:/var/log/mysql -v /data/mysql2/slaver1/data:/var/lib/mysql/ -v /data/mysql2/slaver1/conf:/etc/mysql/conf.d -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_PASSWORD=root.123 -d mysql:5.7

###1、主Mysql的my.cnf


[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

server_id=1
log-bin=mysql_bin
read-only=0
###要同步的数据库
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3
binlog-do-db=db4
binlog-do-db=db5

###不同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

###2、从mysql的my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

server_id=2
#log-bin=mysql_bin
read-only=0

###3、主Mysql配置一个账号给从数据库同步用

GRANT ALL PRIVILEGES ON *.* TO 'backup'@'192.168.%'IDENTIFIED BY 'com.123' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

###4、配置从mysql的主mysql信息

CHANGE MASTER to MASTER_HOST='192.168.3.91', MASTER_USER='backup', MASTER_PASSWORD='com.123', MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=567, master_port=3310;

###5、开始同步，停止同步
start slave

stop slave

###查看连接状态
show slave status;

###如果Slave_IO_Running和Slave_SQL_Running 都是Yes,说明连接成功，可以同步了

###如果Slave_IO_Running是connecting, 说明账号连接未成功。

###[ERROR] Slave I/O for channel '': error connecting to master 'backup@192.168.3.91:3310' - retry-time: 60  retries: 7, Error_code: 1045

###一是检查账号密码是否正确.如果都没问题，那可能就是授权时ip段的问题，修改授权语句

###由于我是用docker搭建，所以可能存在ip段的问题

GRANT ALL PRIVILEGES ON *.* TO 'backup'@'%'IDENTIFIED BY 'com.123' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

### 同步错误
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql_bin.000001' position 1013.


### 清空主从信息，重新设置
STOP SLAVE IO_THREAD FOR CHANNEL '';
RESET SLAVE ALL;