MySQL主从数据库同步_

MySQL主从数据库同步

(2009-09-22 10:08:32)

转载

标签： 数据库服务器 mysql log 主从 show 杂谈

分类： 服务器

概述

    在做web应用系统中，如果数据库出现了性能瓶颈，而你又是使用的MySQL数据库，那么就可以考虑采用数据库集群的方式来实现查询负载了。因为一般来讲一个系统中数据库的查询操作比更新操作要多的多，因此通过多台查询服务器将数据库的查询分担到不同的查询数据库从而提高数据库的查询效率。

    MySQL数据库支持数据库的主从复制功能，使用主数据库进行数据的插入、删除与更新操作，而从数据库则专门用来进行数据库查询，从数据库还可以建立多台，通过负载均衡来实现查询，这样就可以将更新操作与查询操作分离到不同的数据库上，从而提高查询的效率，降低系统的负载。

配置

一、主数据库配置

    修改mysql的配置文件（对于windows就是MySQL安装目录下的my.ini文件，对于linux通常就

是 /etc/my.cnf文件）,在配置文件中加入（或修改）

    server-id = 1
    log-bin=mysql-bin

    binlog-do-db=dbname --要同步的数据库名称

    binlog-ignore-db=dbname --不要同步的数据库名称

    注：MySQL进行主从复制是通过二进制的日志文件来进行，所以我们必须开启MySQL的日志功能，即我们上面的log-bin，同时每一台数据库服务器都需要指定一个唯一的server-id，通常主数据库服务器我们指定为1。

    给主数据库授予一个可以进行复制的用户，如下：

    grant replication slave on *.* to repuser@'host' identified by '123456';

    replication slave——MySQL数据库中表示复制的权限名称

    repuser——从数据库服务器登陆到主数据库服务器时用到的用户名称

    host——从数据库IP地址

    123456——登陆密码

    如果上述权限设置后，无法同步，可将其改为

    grant select,insert,update,delete,replication slave on *.* to repuser@'10.20.20.167' identified by '123456';

    配置完上述步骤后，就可以启动主数据库了。

 

二、从数据库配置

    修改配置文件

     server-id=2
     log-bin=mysql-bin     --在从服务器上启动日志记录，不是必须，但是官方建议
     master-host=主机      --主数据库服务器的IP地址
     master-user=用户名    --执行复制的用户名称，就是grant的用户，即repuser
     master-password=密码  --复制用户的密码，就是grant的用户密码，即123456
     master-port=端口      --主数据库服务器的端口，默认是3306

     replicate-do-db=dbname  --需要同步的数据库

     #replicate-ignore-db=dbname --不需要同步的数据库

     现在可以重启从数据库，启动后，还需要启动复制线程

     slave start

     查看复制线程的状态

     show slave status;

     我们还可以在从数据库服务器上动态的改变对主数据库的配置信息，通过如下命令来进行：
     CHANGE MASTER TO MASTER_HOST='主数据库服务器的IP地址',MASTER_PORT=3306,MASTER_USER='主数据库上的复制账号',MASTER_PASSWORD='密码';

 

三、启动与监控

    1、监控主数据库服务器

        show master status; --运行于主数据库

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |   370558 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

       其中File是表示日志记录的文件，而Position则是表示当前日志在文件中的位置，这个也是从数据库服务器上执行复制操作必须的标识，后面的两个字段分别表示要记录的数据库名称和不需要记录的数据库名称，我们也可以在配置文件中进行配置。

     2、监控从数据库服务器
        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.20.20.194 | repuser     |        3306 |            60 | mysql-bin.000003 |              370558 | WEB2-relay-bin.000206 |         12251 | mysql-bin.000003      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |              370558 |           12251 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)
       红色的部分，分别表示的是Master_Log_File和Read_Master_Log_Pos，即主数据库服务器上的日志文件和要读取的主数据库服务器上的日志的位置，通常这个Read_Master_Log_Pos是和主数据库服务器上的Position是一致的（同步以后），如果从数据库服务器还没有同步完毕，那么这个值通常比主数据库服务器上的要小。

       注：如果从数据库服务器在同步的过程中出现了问题，那么我们可以通过reset slave来重置从数据库服务器的复制线程。

       从数据库常用命令：

Slave start; --启动复制线程
Slave stop; --停止复制线程
Reset slave; --重置复制线程
Show slave status; --显示复制线程的状态

Show slave status\G; --显示复制线程的状态(分行显示)

Show master status\G; --显示主数据库的状态(分行显示)

Show master logs --显示主数据库日志，需在主数据库上运行
Change master to; --动态改变到主数据库的配置

Show processlist --显示有哪些线程在运行

 

四、常见问题与解答

    1、现象：修改完配置后，重启mysql，提示MySQL manager or server PID file could not be found
       解决：查找进程中的mysql，kill掉
             > service mysql restart
             > ps -aux|grep myslq
             > kill 进程号
             # service mysql start

         注：mysql.sock文件在mysql启动后才会生成，位置为/tmp下或/var/lib/mysql/下

    2、现象：从数据库无法同步，show slave status显示Slave_SQL_Running为No,Seconds_Behind_Master为null

       原因：a.程序可能在slave上进行了写操作

             b.也可能是slave机器重起后，事务回滚造成的.
       解决：方法一：

                     mysql> slave stop;
                     mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
                     mysql> slave start;

             方法二：slave stop; --停掉slave服务

                     进入master

                     mysql> show master status;
                     +----------------------+----------+--------------+------------------+
                     | File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
                     +----------------------+----------+--------------+------------------+
                     | mysql-bin.000040     | 324      |              |                  |
                     +----------------------+----------+--------------+------------------+
                     然后到slave服务器上执行手动同步

                     mysql> change master to 
                     > master_host='10.20.20.194',
                     > master_user='repuser',
                     > master_password='123456',
                     > master_port=3306,
                     > master_log_file='mysql-bin.000040',
                     > master_log_pos=324;

                  启动slave服务

                     mysql> slave start;

                     通过show slave status查看Slave_SQL_Running为Yes,Seconds_Behind_Master为0，即为正常。

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

    3、现象：从数据库无法同步，show slave status显示Slave_IO_Running为No,Seconds_Behind_Master为null

       解决：重启主数据库

             service mysql restart

             mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+
            mysql> slave stop;
            mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98
            mysql> slave start;