（mysql主从复制）

浏览数：37 / 时间：2015年06月12日

【实验环境】

master rhel6.3 （最小化）

slave rhel6.3 （最小化）

IP分配：

master 192.168.30.110

slave 192.168.30.106

一、配置Master

1、Master 上为slave添加一个用户，并授权

mysql> grant replication slave on *.* to ‘rep‘@‘192.168.30.106‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.05 sec)

2、编辑my.cnf

[root@master ~]# vim /etc/my.cnf

server-id=1 // 指定ID，这个ID必须唯一

log-bin=mysql-bin //开启log- bin二进制日志文件

注: [mysqld]里面默认有server-id=1，需要去掉这行，否则在实验过程中，经常会因为忽略了这一行的存在而报server-id 相同的错误。

3、重启mysql

[root@master ~]# service mysqld restart

4、记录下二进制日志文件名以及位置

这里我们将模拟实际生产环境来进行实验，在AB同步之前需要先将主服务器备份给从服务器，以确保两台服务器的数据是一样的。

到这一步，我们需要记录下二进制日志文件的文件名以及位置，这样可以告诉从服务器同步的时候从哪个文件哪个位置开始同步，所以必须保证这个位置不变。但是备份的操作也会改变其位置，所以在做备份之前需要先锁表。

(1)锁表

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

(2)记录二进制日志文件及日志

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 107 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

// 需要记录下二进制文件的名字和位置

(3)备份

[root@master ~]# mysqldump -uroot -p rep > /usr/local/mysql/rep.sql

(4)解锁表

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

二、拷贝数据

在同步之前，需要保证两台服务器上的数据库是一致的，如果数据库都是新装的，则可以跳过这一步

(1)将主服务器上的备份文件拷贝到从服务器上

[root@master ~]# scp /usr/local/mysql/rep.sql [email protected]:/usr/local/mysql/

[email protected]‘s password:

rep.sql 100% 1259 1.2KB/s 00:00

(2)复制

[root@slave ~]# mysql < /usr/local/mysql/rep.sql

如果备份的时候遇到这样的问题：

[root@master ~]# mysqldump -uroot -p --all-databases > /usr/local/mysql/rep1.sql

Enter password:

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘root‘@‘localhost‘ for table ‘cond_instances‘ when using LOCK TABLES

解决办法:

[root@master ~]# mysqldump -uroot -p --all-databases --skip-lock-tables > /usr/local/mysql/rep1.sql

三、配置slave

1、配置my.cnf

[root@slave ~]# vim /etc/my.cnf

server-id=2 // 设置server-id，不可重复

log-bin=mysql-bin // 开启二进制日志，用于链式复制的情况下，即这台服务器如果需要作为其他从服务器的主服务器，则需要开启这个选项

read_only = 1 // 设置为只读

relay_log = mysql-relay-bin //配置中继日志

log_slave_updates = 1 // 表示slave 将复制事件写进自己的二进制日志

注：my.cnf 中默认有server-id=1这一行，可直接对这行修改或者去掉该行，自行添加一个唯一的id

2、重启mysql

[root@slave ~]# service mysqld restart

Shutting MySQL... SUCCESS!

Starting MySQL... SUCCESS!

3、对从数据库进行相应的设置

mysql> change master to

-> master_host=‘192.168.30.110‘,

-> master_user=‘rep‘,

-> master_password=‘123456‘,

-> master_log_file=‘mysql-bin.000005‘,

-> master_log_pos=107;

Query OK, 0 rows affected (0.06 sec)

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

执行show processlist 查看进程

mysql> show processlist\G

*************************** 3. row ***************************

Id: 7

User: system user

Host:

db: NULL

Command: Connect

Time: 664

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

看到以上信息，表示slave 已经连接到master，并开始接受并执行日志。

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.30.110

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 267

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 413

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 267

Relay_Log_Space: 569

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

验证是否正常工作：

mysql> show slave status\G;

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

看到上面两个YES说明，IO以及SQL线程都已正常工作。

四、测试

在master上面创建一个新的表

mysql> use test;

mysql> create table user(id int);

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| user |

+----------------+

在slave上查看同步情况，可以看到

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| user |

+----------------+

到此，mysql 的主从同步完成！

========================

实验可能遇到的问题

1、在slave上，使用命令show slave status 看到如下报错

The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be

different for replication to work (or the --replicate-same-server-id option must be used on slave but

this does not always make sense; please check the manual before using it)

解决办法：

从上面的输出可以看到发送错误的原因是server-id重复了。

检查主从上的server-id是否唯一（注意，my.cnf配置中默认已经有server-id=1）

2、手动执行同步的时候，查看slave status 时，sql线程为no

可能因为之前已经同步，再次执行会报错，因为之前的数据库可能已经存在，再次执行创建的sql语句，则会有错

解决办法：

之前的数据库数据，可以通过锁表，备份并恢复到从数据库上，然后重新定义新的二进制日志文件名以及位置，再次开启同步即可。（即跳过之前执行过的操作）

3 查看master status 状态看到io线程没有正常工作，显示connecting

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Last_IO_Error: error reconnecting to master ‘[email protected]:3306‘ - retry-time: 60 retries: 86400

从上面的信息可以看到IO线程连接超时，

解决办法：

(1) 查看防火墙是否放行3306

(2) 注意mysql 修改了配置之后是否有重启，还有网络状态如何
(3) 看看master主机IP地址是否写错

(4)主服务器上授权grant 语句有没写错（比如登陆的IP，用户名或者密码）

4 MySQL主从复制时遇到个ERROR 1201 (HY000): Could not initialize master info structure .

出现这个问题的原因是之前曾做过主从复制！

解决方案是：运行命令 stop slave;

成功执行后继续运行 reset slave;

然后进行运行GRANT命令重新设置主从复制。

具体过程如下：

Command代码

mysql> change master to master_host=‘127.0.0.1‘, master_user=‘user‘, master_pass

word=‘user‘, master_log_file=‘mysql-bin-000202‘, master_log_pos=553;

ERROR 1201 (HY000): Could not initialize master info structure; more error messa

ges can be found in the MySQL error log

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host=‘127.0.0.1‘, master_user=‘user‘, master_pass

word=‘user‘, master_log_file=‘mysql-bin-000202‘, master_log_pos=553;

Query OK, 0 rows affected (0.11 sec)

========================

其他

1、主从服务器同步维护

由于各种原因，导致主从数据不一致，在负载低的时候，进行手动同步.

在主服务器上执行

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 355 | | |

+------------------+----------+--------------+------------------+

在从服务器上执行

先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步

mysql> select master_pos_wait(‘mysql-bin.000005‘,‘355‘);

+-------------------------------------------+

| master_pos_wait(‘mysql-bin.000005‘,‘355‘) |

+-------------------------------------------+

| 0 |

+-------------------------------------------+

同步完成后，回到主服务器上，解锁

mysql> unlock tables;

=====================

注意：

read-only = 1 这个选项对于root用户无效，root用户照样可以执行插入更新的操作。

mysql> insert into test1.test values (‘2‘);

Query OK, 1 row affected (0.06 sec)

mysql> select * from test1.test;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

这个选项只对普通用户生效:

新添加一个用户并授权

mysql> grant all privileges on test1.test to ‘dbuser‘@localhost identified by ‘123456‘;

退出mysql后，使用dbuser这个刚刚新建的用户测试

mysql> use test1;
Database changed
mysql> insert into test values(‘3‘);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

可以看到，提示mysql 设置了read-only的选项，不允许插入操作。

本文出自 “→おyǒňɡ無祉境℃” 博客，请务必保留此出处http://lxxxker.blog.51cto.com/4244111/1387394

（mysql主从复制）,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

（mysql主从复制）