欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-24 16:04:26
...

MYSQL主从:主(新实例)--主(新实例,只读) 一、一台机器安装两个实例(3307、3308) 按照http://babaoqi.iteye.com/admin/blogs/1954110方法搭建mysqld3308 二、搭建双主:3307、3308(只读) # 关闭两个mysql服务 service mysqld3307 stop; service mysqld3308 stop

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状态,则搭建成功.