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