[ERROR]SlaveI/Othread:errorconnectingtomaster'server2@172.16.5.163:3306':Error:'LostconnectiontoMySQLserverat'readinginitialcommunicationpacket',systemerror:113'errno:201...
[ERROR] Slave I/O thread: error connecting to master 'server2@172.16.5.163:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 113' errno: 2013 retry-time: 60 retries: 86400
复制过程:
需要两台MySQL主机做服务器:
MySQL的创建方法参见:创建MySQL数据库
Server-1:172.16.5.162
Server-2:172.16.5.163
一、创建并授权用户
在Server-1上:
创建一个充许Server-1来访问的用户server2,密码为:server2
mysql> grant replication slave on *.* to 'server2'@'172.16.5.163' identified by 'server2';
在Server-2上:
创建一个充许Server-1来访问的用户server1,密码为:server1
mysql> grant replication slave on *.* to 'server1'@'172.16.5.162' identified by 'server1';
二、修改MySQL主配置文件
在MySQL的主配置文件中修改/添加如下内容:
Server-1上:
[mysqld]
server-id = 10
log-bin = mysql-bin
replicate-do-db = mydb
auto-increment-increment = 2
auto-increment-offset = 1
# service mysqld restart
Server-2上:
[mysqld]
server-id = 20
log-bin = mysql-bin
replicate-do-db = mydb
auto-increment-increment = 2
auto-increment-offset = 2
# service mysqld restart
注:二都只有server-id不同和 auto-increment- offset不同
三、复制其中一台服务器的数据库到别外一台服务器
以Server-1上数据库为源数据库
备份数据前先锁表,保证数据一致性
mysql> FLUSH TABLES WITH READ LOCK;
> SHOW MASTER STATUS;
|mysql-bin.000006 | 213 | | |
开始备份数据库
# mysqldump –user=root -p mydb > /tmp/mydb.sql
mysqldump -uroot -proot -B mydb --lock-all-tables>/tmp/mydb.sql
备份完毕,现在可以解锁数据库表
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
四、将备份数据导入Server-2
先在Server-2上创建一个与mydb同名的空数据库
# mysql
> CREATE DATABASE mydb;
>\q
# scp 192.168.0.1:/tmp/mydb.sql ./
# mysql -uroot -p mydb < /tmp/mydb.sql
# mysql
> flush TABlES WITH READ LOCK;
> SHOW MASTER STATUS;
|mysql-bin.000001 | 106
> UNLOCK TABLES;
五、互相通告二进制日志位置
在Server-1上:
# mysql
> CHANGE MASTER TO MASTER_HOST=’172.16.5.163′,MASTER_USER=’master1′,MASTER_PASSWORD=’master1′,MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=1778;
change master to master_host='192.116.5.163',master_user='master1',master_password='master1',master_log_file='mysql-bin.000002',master_log_pos=1778;
change master to master_log_file='mysql-bin.000002',master_log_pos=1778;
在Server-2上:
# mysql
> CHANGE MASTER TO
> MASTER_HOST=’192.168.0.1′,
> MASTER_USER=’master2′,
> MASTER_PASSWORD=’master2′,
> MASTER_LOG_FILE=’mysql-bin.000006′,
> MASTER_LOG_POS=213;
change master to master_host='192.116.5.162',master_user='master2',master_password='master2',master_log_file='mysql-bin.000049',master_log_pos=98;
展开