zp blog

MySQL的主从同步是一个很成熟的架构，优点为：

①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力；

②在从主服务器进行备份，避免备份期间影响主服务器服务；

③当主服务器出现问题时，可以切换到从服务器。

所以我在项目部署和实施中经常会采用这种方案.

my.cnf配置文件     /etc/my.cnf

主数据库：192.168.2.11

从数据库：192.168.2.22

一、设置主库

1、修改主库my.cnf,主要是设置个不一样的id和logbin（#这部可依具体环境而定，压力大的化可采用huge.cnf）

[root@localhost etc]#vi /etc/my.cnf

X 记住这部分一定要配置在[mysqld]后面，否则无法找到从节点，各个配置项的含义可自己查阅文档

    [mysqld]
        log-bin=mysql-bin
        server-id=1
        binlog-ignore-db=information_schema
        binlog-ignore-db=mysql
        binlog-do-db=test

2、启动主库生效

    [root@localhost etc]service mysqld restart

3、登陆主库

    [root@localhost etc]mysql -u root -p

4、赋予从库权限帐号，允许用户在主库上读取日志

    mysql> grant replication slave on *.* TO '账号'@'%' identified by '密码';

5、检查创建是否成功

    select user,host from mysql.user;

6、显示主库信息

记录File和Position，从库设置将会用到

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

X 如果执行这个步骤始终为Empty set(0.00 sec)，那说明前面的my.cnf没配置对。

二、设置从库

1、修改从库my.cnf（位置一样）

    [root@localhost etc]vi my.cnf

X 记住这部分一定要配置在[mysqld]后面，否则无法找到从节点，各个配置项的含义可自己查阅文档

[mysqld]
        log-bin=mysql-bin
        server-id=2
        binlog-ignore-db=information_schema
        binlog-ignore-db=mysql
        replicate-do-db=test
        replicate-ignore-db=mysql,information_schema
        log-slave-updates
        slave-skip-errors=all
        slave-net-timeout=60

4、在从库上验证连接主库

    [root@localhost etc]mysql -h 192.168.2.11 -u 账号 -p

5、在从库从库上设置同步

X 设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position

X 注意下面第二条命令语句中的master_log_file='mysql-bin.000001', master_log_pos=98;对应为前面在主库中执行的show master status;结果

    mysql> stop slave;
    mysql> change master to master_host='192.168.2.11',master_user='root',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=98;
    mysql> start slave;

6、启动从库服务

    mysql> start slave;

7、进行测试

在主库上的test库上建立名为myTest的表

    mysql> CREATE TABLE `myTest` (
    `id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `username` VARCHAR( 20 ) NOT NULL ,
    `password` CHAR( 32 ) NOT NULL ,
    `last_update` DATETIME NOT NULL ,
    `number` FLOAT( 10 ) NOT NULL ,
    `content` TEXT NOT NULL ,
    PRIMARY KEY ( `id` ) 
    ) ENGINE = MYISAM ;

在从表中马上看到了效果，主从同步成功了；

为了更进一步验证在从库上输入show slave status\G;

    mysql> show slave status\G;
    Slave_IO_Running: Yes(网络正常)；
    Slave_SQL_Running: Yes(表结构正常)

故障及解决:

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
....
Seconds_Behind_Master:NULL

原因：

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

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

解决办法I：

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

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

记录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='master_ip',
    > master_user='user', 
    > master_password='pwd', 
    > master_port=3307, 
    > master_log_file='mysql-bin.000020', 
    > master_log_pos=135617781;
    1 row in set (0.00 sec)
    
    mysql> start slave;
    1 row in set (0.00 sec)

再次查看slave状态发现：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

解决办法II：

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