mysql增加库_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;