MYSQL的master/slave数据同步配置

有三个地址必须调试成功，

1  从库连接主库进行测试： mysql -h192.168.200.80 -urepluser -ptestpassword     此处必须连接成功

2  show master status;必须有相关显示

3 show slave status;

 必须为

 

 

 

 

 

 

 

sudo vim /etc/mysql/my.cnf   mysql 配置文件

sudo /etc/init.d/mysql restart  重启 mysql

 

 

 

数据库服务器 Master, 主机名 :DB01, IP: 192.168.1.80

 

数据库服务器 Slave, 主机名 :DB02, IP: 192.168.1.103

 

先配置 Master 的 mysql 环境 .

 

修改 mysql 的配置文件 my.cnf 确保有下面的信息 , 没有加上 . 有的话修改成下面这样 . 其他的可以保持默认配置 .

 

    [mysqld]

    log-bin = mysql-bin

    server-id = 1

       binlog-do-db = test // 要同步的表

 

 

GRANT REPLICATION SLAVE ON *.* TO repluser@192.168.200.103 IDENTIFIED BY 'testpassword';

 

从库连接主库进行测试： mysql -h192.168.200.80 -urepluser -ptestpassword

此处测试应该是通过的，否则 mysql 配置有问题

 

------- 比如 当在另一台机器上登录 mysql 时出现如下错误：

ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (111)

打开 /etc/mysql/my.cnf 文件，找到 bind-address = 127.0.0.1 修改为 bind-address = 0.0.0.0

-----

 

 

 

OK, 简单的 MySQL master 服务器配置到这里 .

重启 mysql 服务 , 登入 , 查看 master[show master status;] 状态 . 如下图 :

 

可通过 show master status 来监控主服务器的状态，内容如下：

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |     1164 |              |                  |

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

##### 其中 File 表示日志文件记录， Position 表示日志文件的位置，这个也是数据库执行复制操作的必须标识，后面两字段表示复制的数据库和不复制的数据库名，也可以在配置文件中你进行配置。

此处若不显示，说明配置也有问题。

 

 

 

再配置 Slave 的 mysql 环境 .

同样修改 my.cnf 配置文件 . 确保下面有如下信息 .

 

     [mysqld]   -- 最好是放在 [mysqld] 下面

    server-id=2

    master-host = 192.168.200.80

    master-user = repluser

    master-password = testpassword

    master-port = 3306

    master-connect-retry = 120

    replicate-do-db = test

保存修改 , 重启 mysql 服务 . 登入数据库 , 可以看到 slave [show slave status;] 状态信息 . 如下图 :

 

mysql> show slave status\G;

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

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.200.80

                Master_User: repluser

                Master_Port: 3306

              Connect_Retry: 120

            Master_Log_File: mysql-bin.000007

        Read_Master_Log_Pos: 1627

             Relay_Log_File: mysqld-relay-bin.000009

              Relay_Log_Pos: 1764

      Relay_Master_Log_File: mysql-bin.000007

            Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: test

        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: 1627

            Relay_Log_Space: 1764

            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

 

 

 

 

show slave status\G  如果发现有：

  Slave_IO_Running: Yes

Slave_SQL_Running: Yes

  就说明已经成功了，如果这两个选项不全是 Yes ，那就说明你钱面的某个配置错了，

  我做的时候没有把主服务器重启，就出现   Slave_IO_Running: NO 。重启后好了

 

 

 

配置正常 .

 

简单的 MySQL 主从配置就 OK 了 . 当然这个是测试环境使用 . 生成环境还需要很多地方的优化配置 .

 

  ========================Slave_SQL_Running: No

 

解决办法一、

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

 

解决办法二、

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

然后到slave服务器上执行手动同步：

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)

 

mysql> show slave status\G
*************************** 1. row ***************************
........
            Master_Log_File: localhost-bin.000094
        Read_Master_Log_Pos: 33768775
             Relay_Log_File: localhost-relay-bin.000537
              Relay_Log_Pos: 1094034
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

手动同步需要停止master的写操作！

 

 

 

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

以下是一些备注，可看可不看。

 

stop slave    reset slave     start slave

   

----------- 今天发现 Mysql 不同步， show slave status 查看同步状态如下：

Slave_IO_Running: No

Slave_SQL_Running: Yes

使用 start slave 也不能同步。后来，先  slave stop 再  slave reset 再  slave start 就正常了。

 

 

====================== 手动同步

解决办法 I ：

1. 首先停掉 Slave 服务： slave stop

到主服务器上查看主机状态：

记录 File 和 Position 对应的值。

 

 

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000020 | 135617781 | | |

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

1 row in set (0.00 sec)

 

3. 到 slave 服务器上执行手动同步：

 

mysql> change master to

> master_host='192.168.200.80',

> master_user='repluser',

> master_password='testpassword',

> master_port=3306,

> master_log_file='mysql-bin.000007',

> master_log_pos=98;

 

 change master to master_host='192.168.12.203', master_user='repluser', master_password='admin@asdf', master_port=3306, master_log_file='binlog.000009', master_log_pos=1253;

 

4  slave start

 ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ end  手动同步

 

 

 

1. 问题：主从服务器表类型的选择

一般的共识是主服务器使用innodb，事务，行锁等功能是myisam所没有的，对修改操作而言，它更高效；从服务器使用myisam，全文检索功能是innodb所没有的，对查询操作而言，它更高效。这样就可以各尽其能。

2. 问题：主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb，因为不涉及查询，所以可以使用varchar等来存储字符串来节省空间，从服务器一般是 myisam，因为涉及查询，所以必须在char和varchar之间仔细权衡，没有varchar, text, blob字段的表是静态表，反之是动态表，静态表的检索效率要比动态表好若干倍，一般来说，所有涉及大结果集的查询都应该尽可能保证在静态表上完成，这里说一个例子：比如说常见的articles表有title(varchar), body(text)等字段，在做文章列表的时候，因为不是静态表，所以查询不会很快，下面开始重构解决方案：把原来的articles表拆分成 subjects表和contents表，title字段设置为一个足够的char类型放在subjects表里，body字段还保持是text类型放到 contents表里，subjects和contents表之间的关系是一对多，这样，顺带着也方便的实现了多页文章的功能，而且更重要的是在查询文章列表的时候，操作都是在subjects静态表里完成，效率肯定会比前一种方案提升很多。

强调：MyISAM里静态表和动态表的区别对性能影响极大，但我敢说很大一部分使用者并没有注意过这一点！如果你就是其中之一，那么我强烈建议你再次体会一下前面说的articles分解为subjects/contents的过程，相信你熟悉了以后，下一个应用的速度会有质的提升。

3. 问题：主从服务器读写分离时读操作失败

先重现一下问题：比如说添加一条新数据，添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务器上完成的，浏览新数据的操作实在从服务器上完成的，不过由于主从服务器间SQL同步存在延迟，所以当使用last_insert_id在从服务器上查询的时候，从服务器很可能还没有还没来得及同步到此记录，所以读操作失败。解决思路也不复杂，在代码里加入一个缓存层（可以使用 memcached），新添加的数据都顺手放到缓存层里一份，新数据的读操作也先查询缓存层，这样就不会再有读操作失败的问题了，当然删除或者更新数据的时候也要顺带着处理好缓存数据，可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录，对于读取列表的记录的情况，则是无效的。

4. 问题：主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离，从服务器上进行读操作，主服务器进行写操作，这样的话，主服务器上仅保留主键，外键，唯一索引等必要的索引即可，以便保持数据合法性，而对于那些原本用于优化SELECT操作的索引，可以全部删除，如此的话主服务器的写操作效率会提升很多。把索引保持在从服务器上还有一个好处就是实际应用里，我们可能时常要通过ALTER TABLE去调整索引，而ALTER TABLE本身数据剧烈运动，会影响在线业务，为了规避影响，我们可以先停止一台从服务器，然后ALTER TABLE建好索引，然后再把从服务器启动，用这样的方法把所有的从服务器都建好索引，从而平滑的完成了ALTER TABLE操作。

 

 

 

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

 

 

 

评论

1 楼 soartju 2011-01-13  

解决方案值得学习：）