Mysql主从同步配置

这篇文章为自己动手后的操作记录，以下是我这次主从配置的过程，请大家多多指教：

环境：

 

     1. 两台windows7笔记本。

     2. 安装数据库版本，使用 select version(); 

          看到主库上是：

          mysql>      select version();

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

| version()               |

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

| 5.0.45-community-nt-log |

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

1 row in set

          同样，从库上是：

           mysql> select version();

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

| version()           |

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

| 5.0.45-community-nt |

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

1 row in set

    

配置步骤：

 

1. 修改主库机器上的my.ini文件：

     末尾增加：

          #主库的serverId 还不清楚怎么使用。但不能和从库相同

server-id=559  

#设置bin.log文件地址，但是我本地生成的不是log-bin.log而是log-bin.000001 ，log-bin.000002 等等文件

log-bin = D:\log-bin.log

#设置一个错误日志，和bin.log一样会在启动的时候更新

log-error=D:\log-err.log

 

2. 在主库上增加从库的用户信息:

     grant replication slave on *.* to 'slave_test'@'10.28.164.123' identified by 'slave_test';

     

 

3. 修复从库上的my.ini文件：

          #从库的serverId

server-id=558

#配置主库的ip地址

master-host=10.28.162.35

#从库连接主库的用户名

master-user=slave_test

#密码

master-password=slave_test 

#尝试次数

master-connect-retry=30

#主库的库名

replicate-do-db=master_db

 

 

配置好后，各自重启mysql服务：

主库上查看状态

mysql> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000003 |    13484 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set

 

从库上查看状态：

 

+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State       | Master_Host  | Master_User  | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File                 | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Connecting to master | 10.28.162.35 | slave_test   |        3306 |            30 |                 |                   4 | slave-relay-bin.000003         |            98 |                       | No               | Yes               | cms             |                     |                    |                        |                         |                             |          0 |            |            0 |                   0 |              98 | None            |                |             0 | No                 |                    |                    |                 |                   |                | NULL                  |
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set

 

注意：

Slave_IO_Running | Slave_SQL_Running  

          No              |           Yes                

 

正常情况这里应该是两个Yes,并且Master_Log_File 字段为空的（这里需要配置）

网上说的情况有两种：

1. 从库的用户没有权限。

2. 主库的binlog文件不存在。

 

我检查后发现没有这两种情况，于是 我重启slave 一次执行：

1. slave stop ;命令

2. change master to Master_Log_File='log-bin.000003',Master_Log_Pos=98;  #将日志文件重新设置一次，对应于 show master status 看到的File字段。

3. slave  start;

 

还是不行。于是我重新修改了master的日志文件：

mysql> change master to Master_Log_File='log-bin.000004',Master_Log_Pos=98;

Query OK, 0 rows affected

mysql> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000004 |     8774 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set

 

 

然后再修改从库的 Master_Log_File：

1. slave stop ;命令

2. change master to Master_Log_File='log-bin.000004',Master_Log_Pos=98;  #将日志文件重新设置一次

3. slave  start;

 

再看slave状态：

mysql> show slave status;
+----------------------------------+--------------+-------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host  | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File                 | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+--------------+-------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 10.28.162.29 | root        |        3306 |            30 | log-bin.000004  |                8774 | CDYJY-ZHANGSB-relay-bin.000002 |          8909 | log-bin.000004        | Yes              | Yes               | cms             |                     |                    |                        |                         |                             |          0 |            |            0 |                8774 |            8909 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+--------------+-------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set

 

全部是Yes，状态正常了。

这个时候，主库插入数据，从库就可以正常同步啦~~~！

 

 

 

值得注意的几个问题：

 

 

可以看到 log-err.log后有这么一句话：

130801 15:12:02 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=D:\Program Files\Database\MySQL\Data\xxxx-relay-bin' to avoid this problem.

 

解释如下：

master(binlog)->relay log(slave io线程)->db data(salve sql线程)

所以这些日志就放到relay log 

然后从服务器慢慢执行master的二进制日志  传递给从服务器执行

从服务器需要一个缓冲吧

 

其中relay log是放在主库本地的。

 

 

 

这次只是简单的配置了主从同步，对于Mysql的大部分日志文件的用处还不是非常理解。需要在深入学习下。