搭建主(mysqld3307,生产库) --> 从(mysqld3309)
# 备份mysqld3307数据。最好在生产库压力小的时候进行
mysql-3307/install/bin/mysqldump -uroot -p -A --flush-logs --lock-all-tables --master-data=1 > ./bak_3307.sql
# 授权同步账户
mysql-3307/install/bin/mysql -uroot -ppwd -e "GRANT REPLICATION SLAVE on *.* to 'repl'@'%' identified by 'repl@pwd'; FLUSH PRIVILEGES;"
# 搭建mysqld3309实例
参见[url]http://babaoqi.iteye.com/admin/blogs/1954110[/url]
# 关闭mysqld3309服务
service mysqld3309 stop;
# 配置my.cnf
vim /home/bbq/mysql/mysql-3309/cnf/my.cnf
log-bin=mysql-bin
binlog_format=mixed
read_only
[color=orange]# 主库执行show slave hosts显示使用
report_host=localhost #本机地址
report_port=3309 #本实例端口[/color]
log_slave_updates
server-id = 3309
# 启动mysqld3309服务
service mysqld3309 start;
# 设置同步起始位置。bak_3307.sql中已经含有file position信息,不用再设置
mysql-3309/install/bin/mysql -uroot -plm -e "CHANGE MASTER TO master_host='localhost',master_port=3307, master_user='repl',master_password='repl@pwd'"
# 导入生产库备份
mysql-3309/install/bin/mysql -uroot -plm < bak_3307.sql
# 开启同步,查看同步状态 START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G;
mysql-3309/install/bin/mysql -uroot -plm -e "START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G;"
若是SQL线程(Slave_IO_Running)和I/O线程(Slave_SQL_Running)都显示为YES状态,则搭建成功.
# 备份mysqld3307数据。最好在生产库压力小的时候进行
mysql-3307/install/bin/mysqldump -uroot -p -A --flush-logs --lock-all-tables --master-data=1 > ./bak_3307.sql
# 授权同步账户
mysql-3307/install/bin/mysql -uroot -ppwd -e "GRANT REPLICATION SLAVE on *.* to 'repl'@'%' identified by 'repl@pwd'; FLUSH PRIVILEGES;"
# 搭建mysqld3309实例
参见[url]http://babaoqi.iteye.com/admin/blogs/1954110[/url]
# 关闭mysqld3309服务
service mysqld3309 stop;
# 配置my.cnf
vim /home/bbq/mysql/mysql-3309/cnf/my.cnf
log-bin=mysql-bin
binlog_format=mixed
read_only
[color=orange]# 主库执行show slave hosts显示使用
report_host=localhost #本机地址
report_port=3309 #本实例端口[/color]
log_slave_updates
server-id = 3309
# 启动mysqld3309服务
service mysqld3309 start;
# 设置同步起始位置。bak_3307.sql中已经含有file position信息,不用再设置
mysql-3309/install/bin/mysql -uroot -plm -e "CHANGE MASTER TO master_host='localhost',master_port=3307, master_user='repl',master_password='repl@pwd'"
# 导入生产库备份
mysql-3309/install/bin/mysql -uroot -plm < bak_3307.sql
# 开启同步,查看同步状态 START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G;
mysql-3309/install/bin/mysql -uroot -plm -e "START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G;"
若是SQL线程(Slave_IO_Running)和I/O线程(Slave_SQL_Running)都显示为YES状态,则搭建成功.