mysql增加从库方法

#现有环境是一主一从,需要再增加一台从库,具体方法如下:

同步test数据库

主:192.168.3.203
从1:192.168.3.210
从2:192.168.3.211
1.在主服务器203上面为210和211建立账户。
mysql -uroot -p123
grant replication slave on *.* to ‘backup’@’192.168.3.210′ identified by ‘backup';
grant replication slave on *.* to ‘backup’@’192.168.3.211′ identified by ‘backup';
flush privileges;
2.将从1的数据文件拷贝到从2上面
mysql -u root -p123 -S /usr/local/mysql_p/mysql.sock
stop slave;
mysqldump -u root -p123 -S /usr/local/mysql_p/mysql.sock test >test.sql
scp test.sql root@192.168.3.211:/root/
记录主的Read_Master_Log_Pos文件位置
show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.3.203
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 56669406
3.在从2上面恢复从1的备份
mysql -u root -p123 -S /usr/local/mysql_p/mysql.sock
create database test;
use test
source /root/test.sql
stop slave;
change master to
master_host=’192.168.3.203′,master_user=’backup’,master_password=’backup’,master_port=3306,master_log_file=’mysql-bin.000021′,master_log_pos=56669406;
start slave;
4.在从1服务器210上面操作
mysql -u root -p123 -S /usr/local/mysql_p/mysql.sock
start slave;

 

原文地址: http://azhuang.blog.51cto.com/9176790/1440202

 

本条目发布于 来源:azhuang.blog.51cto.com。属于mysql分类,被贴了 标签。作者是