mysql主主复制报错_mysql 主主复制老报错，运行不起来。请问是哪里出了问题？...

[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;

展开