数据库的主从模式以及双机热备

主从结构搭建

配置主服务器(云主机4)

编辑主master服务器配置文件/etc/my.cnf

#vim /etc/my.cnf

在[mysqld]节点下加入两句话

server-id=1  当前数据库在集群中的id值

log-bin=mysql-bin        #启用二进制日志；key值的名字不能变,value表示你定义

的二进制文件的名字.可以自定义

完成了二进制文件的开启,就可以将当前数据库重启作为主节点提供主从复制架构的

功能;

重启服务：service mysql restart

多台数据库服务器的安装,如果使用虚拟机常见问题在本笔记的末尾(单独导一份)

登录#mysql –uroot -proot

mysql>flush tables with read lock;   #数据库锁表，不让写数据;这步骤可不做

对于当前环境的mysql无需使用lock命令,因为没有人操作,但是生产环境中必须这样做

mysql>show master status;    #查看MASTER状态（这两个值File和Position）其中的file就是二进制文件,position记录当前操作sql的步骤数(注意一条sql包含多步,所以不是sql语句的条数)

mysql>unlock tables;        #从启动好后，记得要解除锁定

主数据库到此配置完毕

配置从服务器

修改/etc/my.cnf增加一行

#vim /ect/my.cnf

server-id=2

重启服务

service mysql restart

通过mysql命令配置同步日志的指向：

登录到从节点的mysql客户端(linux,sqlyog)

mysql>change master to master_host='10.9.48.69',

master_port=3306,

master_user='root',master_password='root',

master_log_file='mysql-bin.000002',

master_log_pos=120;

master_host        主服务器的IP地址

master_port        主服务器的PORT端口

master_log_file    和主服务器show master status中的File字段值相同

master_log_pos        和主服务器show master status中的Position字段值相同

mysql>start slave;                #开启从节点服务状态;stop slave;停止服务，出错时先停止，再重新配置

mysql>show slave status\G;        #查看SLAVE状态，\G结果纵向显示。必须大写,这个命令无法再sqlyog中使用,linux客户端使用的;

注意：如果出错，可以看后面的错误信息。观察Slave_SQL_Running_State字段，它会记录详细的错误信息

测试同步状态

案例1:在主中创建表格,插入数据

观察从

案例2:主节点中添加表格数据,观察从节点

从节点,mysql允许不允许插入数据?

案例3:将从节点中插入数据,然后在主里继续添加数据

观察主,从状态

案例3中由于非法操作,导致从节点的sql线程发现执行错误停止运行,

mysql不限制从节点的写数据,主从结构如果允许从节点写数据,有可能造成主从的

失效;

这是发现没法同步,调用show slave status 发现已经报错

sql线程已经不工作了

id为3的重复,在从中有数据了

 

重新挂接

错误数据必须清除否则继续主从失效

查看主节点中的二进制文件名称 pos

停止从节点的从状态

mysq>stop slave

mysql>show master status;

在从节点中把查询出来的最新数据放到命令里挂接主节点

启动从节点的slave

start slave

 

 

利用mysql的主从搭建完成双机热备的结构

环境前提,完成了一个主从的搭建; 云4是主,云5是从,云主机5启动主节点的二进制,让云4来挂接

• 开启二进制;配置文件/etc/my.cnf中编写二进制的文件名称
• 重启云主机5的mysql,使二进制文件生效
• 查看二进制内容 文件名称和position(云主机5)
• 在云主机4种挂接主节点

• mysql>change master to master_host='10.9.17.153',

  master_port=3306,

  master_user='root',master_password='root',

  master_log_file='mysql-bin.000001',

  master_log_pos=120;

• 启动云主机4的从服务

• mysql>start slave;

• 开启云主机5的二进制时,重启的mysql服务,查看云主机5作为从节点的状态

• show slave status;

 

可能出现的故障以及解决方法：

进入slave服务器，运行：
mysql> show slave status\G
        .......
            Relay_Log_File: localhost-relay-bin.000535
            Relay_Log_Pos: 21795072
    Relay_Master_Log_File: localhost-bin.000094
Slave_IO_Running: Yes
Slave_SQL_Running: No
          Replicate_Do_DB: 
      Replicate_Ignore_DB: 
      ......

解决办法一、

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.

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

亲测，可行。

解决办法二、
首先停掉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的写操作！
查看mysql主从配置的状态及修正 slave不启动问题
1、查看master的状态
show master status;  //Position不应该为0
show processlist;  
//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated
2、查看slave状态
show slave status;
//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes
show processlist;
//应该有两行state值为：
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

3、错误日志
MySQL安装目录 /usr/local/mysql
MySQL日志目录 /usr/local/mysql/data/  形如，Hostname.err

4、Change master to
如果从库的Slave未启动，Slave_IO_Running为NO。
可能是主库是的master的信息有变化，
查看主库show master status;
记录下File,Position字段，假设为‘mysql-bin.000004’,98;
在从库执行：

mysql>stop slave;
mysql>change master to master_log_file='mysql-bin.000004',master_log_pos=98;
mysql>start slave;
 

5、SET global sql_slave_skip_counter=n;
如果从库的slave_sql_running为NO。
Err文件中记录：
Slave:Error "Duplicate entry '1' for key 1" on query.....
可能是master未向slave同步成功，但slave中已经有了记录。造成的冲突可以在从库上执行
set global sql_slave_skip_counter=n;
跳过几步。再restart slave就可以了。

6、同步错误处理
发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错，整个同步也会因此而卡在那，最初的办法只是手动去出错的机器执行下面三条SQL语句，跳过错误即可。

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

 

PS：本人多次遇到从数据库的同步进程自动停掉的问题，有时简单通过slave stop,slave start即可解决。有时slave start启动后又会自动停掉，这时使用 change master重设主数据库信息的方式解决了问题。

说明：
Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令