MySQL主从数据库配置

通过两个MySQL数据库实现主从数据库存储，实时库由于数据存储在内存中，具有读写速度快、存储量小、断电丢失数据等特点，适宜存储实时数据，历史库由于数据存储在硬盘中，具有读写速度相对较慢、数据量大、断电不会丢失等特点，适宜存储历史数据。系统定时将实时库中的数据追加到历史库中并清空实时库，完成数据备份。

主从数据库工作原理如下

1. 在主库上把数据更改记录到二进制日志中（Binary Log）中，这些记录称为二进制日志事件。
2. 从库通过IO线程将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 从库通过SQL线程读取中继日志中的事件，将其重放到自己数据上。
   

安装主从数据库

1. 安装主数据库：运行安装程序mysql-5.5.17-win32.msi，安装主数据库到默认位置C:\Program Files(x86)\MySQL，安装过程中需要设置用户名和密码，两者皆设为root，使用 win+r进入services.msc，停止主数据库的mysql服务；
2. 复制并拷贝从数据库：将上面安装好的主数据库拷贝到C:\Program Files\MySQL2\MySQL Server 5.5作为从数据库
3. 修改从数据库的配置文件：在C:\Program Files\MySQL2\MySQL Server 5.5\my.ini中修改从数据库的port端口和文件位置，端口号修改为3307（默认的主数据库端口号为3306），basedir和datadir修改为第二个数据库的对应路径；

[client]
port=3307

[mysql]
default-character-set=gbk

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#

[mysqld]
skip-grant-tables

# The TCP/IP Port the MySQL Server will listen on
port=3307

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL2/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Program Files/MySQL2/MySQL Server 5.5/data/"

4. 创建启动从数据库服务，在从数据库的bin目录C:\Program Files\MySQL2\MySQL Server 5.5\bin启动命令行，输入以下命令并回车：

mysqld install MySQL2 

主从同步

同步原理

A 的更新流程：A在接受一个来自客户端的更新请求之后，首先在undolog 内存中写入，然后存入硬盘，在redolog恢复日志 prepare 完成之后，写入 binlog ，最后再 commit 整个 prepare ，完成 A内部事务的更新逻辑。

B的同步流程：B 和 A 之间维持了一个长链接，在B上设置A的账号信息、日志位置和偏移量。同步时，通过 start slave 命令启动 io_thread 和 sql_thread 两个线程 。从库通过io_thread 向A发送请求，主库 A 接受请求完成检验后发送binlog 给B 。B拿到A 的binlog后写到本地日志relay log 。 sql_thread 解析B上的 relay log 操作从库。

在 Mysql5.6 之后，Mysql开始支持多线程复制，这减少了主备延迟的问题

文件配置

修改主数据库的配置文件C:\Program Files\MySQL\MySQL Server 5.5\my.ini，在其中开启log-bin日志，并指定日志格式

# 开启日志
log-bin=mysql-bin
# 指定日志模式为mixed
binlog_format=mixed
# 同步时用于标识数据库ID
server-id=1

BinLog会记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中，通过这些记录文件从数据库才能进行同步

mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的模式也有三种：STATEMENT，ROW，MIXED。

1. STATEMENT模式（SBR）：每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
2. ROW模式（RBR）：不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。优点是不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
3. MIXED模式（MBR）：以上两种模式的混合使用，MySQL会根据执行的SQL语句选择日志保存方式，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。

同样地，修改从数据库的配置文件

log-bin=mysql-bin
binlog_format=mixed
server-id=2

win+r–>services.msc进入服务列表，重启MySQL和MySQL2服务

在主数据库查询master的状态，可以看到其日志记录在文件mysql-bin.000027中，而且其日志的位置为144058603

show master status;

在从数据库执行同步操作，这时除了需要指定主数据库的位置、用户名、密码之外，还需要设置刚才查询到的主数据库日志文件和位置

change master to master_host='localhost', master_user='root', master_password='root', master_log_file='mysql-bin.000027', master_log_pos=144058603

启动从数据库并查看主从同步状态

start slave;
show slave status；

可以看到Slave_IO_Running及Slave_SQL_Running进程正常运行，即YES状态，说明同步成功。

如果数据库在运行过程中突然断电关闭，会造成主从数据库不同步，这时若Slave_SQL_Running状态为No，通过如下命令进行同步：

STOP SLAVE;                                                      
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
START SLAVE;   

如果slave_io_running为No，则需要按照之前的步骤进行重新同步，先查看master的log file和执行到的位置position，然后执行

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=144058603;