MYSQL主从:主(新范例)-主(新实例,只读)

最新推荐文章于 2024-03-28 18:59:06 发布

原创最新推荐文章于 2024-03-28 18:59:06 发布 · 1.3k 阅读

2 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

23 篇文章

订阅专栏

本文介绍如何在同一台机器上配置两个MySQL实例，并实现双主从复制。通过详细步骤指导完成3307为主实例，3308为只读从实例的搭建过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MYSQL主从:主(新实例)--主(新实例,只读)
一、一台机器安装两个实例(3307、3308)
按照http://babaoqi.iteye.com/admin/blogs/1954110方法搭建mysqld3308

二、搭建双主:3307、3308(只读)

# 关闭两个mysql服务
service mysqld3307 stop; service mysqld3308 stop;

# 配置3307 my.cnf
vim /home/bbq/mysql/mysql-3307/cnf/my.cnf
log-bin=mysql-bin
binlog_format=mixed
auto_increment_increment=2
auto_increment_offset=1
# 主库执行show slave hosts显示使用
report_host=localhost #本机地址
report_port=3307      #本实例端口
log_slave_updates
server-id   = 3307

# 配置3308 my.cnf
vim /home/bbq/mysql/mysql-3308/cnf/my.cnf
log-bin=mysql-bin
binlog_format=mixed
auto_increment_increment=2
auto_increment_offset=2
read_only
# 主库执行show slave hosts显示使用
report_host=localhost #本机地址
report_port=3308      #本实例端口
log_slave_updates
server-id   = 3308

# 启动两个mysql服务
service mysqld3307 start; service mysqld3308 start;

# 进入两个实例的根目录
cd /home/bbq/mysql

# 授权同步账户
mysql-3307/install/bin/mysql -uroot -ppwd -e "GRANT REPLICATION SLAVE on *.* to 'repl'@'%' identified by 'repl@pwd'; FLUSH PRIVILEGES;"
mysql-3308/install/bin/mysql -uroot -ppwd -e "GRANT REPLICATION SLAVE on *.* to 'repl'@'%' identified by 'repl@pwd'; FLUSH PRIVILEGES;"

# 生成新的binlog
mysql-3307/install/bin/mysql -uroot -ppwd -e "FLUSH binary logs;"
mysql-3308/install/bin/mysql -uroot -ppwd -e "FLUSH binary logs;"

# 设置同步起始位置。在3307、3308上分别执行'SHOW MASTER STATUS',记录File，Position
# CHANGE MASTER TO master_host='主库IP′,master_port=主库端口, master_user='同步账号名称',master_password='同步账号密码', master_log_file='master-file',master_log_pos=master-Position;
# 此例File=mysql-bin.000003，Position=107
mysql-3307/install/bin/mysql -uroot -ppwd -e "CHANGE MASTER TO master_host='localhost',master_port=3308, master_user='repl',master_password='repl@pwd', master_log_file='mysql-bin.000003',master_log_pos=107;"
mysql-3308/install/bin/mysql -uroot -ppwd -e "CHANGE MASTER TO master_host='localhost',master_port=3307, master_user='repl',master_password='repl@pwd', master_log_file='mysql-bin.000003',master_log_pos=107;"

# 查看同步状态
mysql-3307/install/bin/mysql -uroot -ppwd -e "START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G;"
mysql-3308/install/bin/mysql -uroot -ppwd -e "START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G;"
若是SQL线程(Slave_IO_Running)和I/O线程(Slave_SQL_Running)都显示为YES状态，则搭建成功.