mysql 一主多从

1. MySQL主从配置原理

MySQL主服务器数据库的每次操作都会记录在其二进制文件mysql-bin.xxx（该文件可以在mysql目录下的data目录中看到）中，从服务器的I/O线程使用专用账号登录到主服务器中读取该二进制文件，并将文件内容写入到自己本地的中继日志relay-log文件中，然后从服务器的SQL线程会根据中继日志中的内容执行SQL语句。主从复制具体来说就是三个线程的协作：

· 主库binlog输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建下文的两个线程进行处理。

· 从库I/O线程：当START SLAVE语句在从库开始执行之后，从库会创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

· 从库的SQL线程：从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

对于每一个主从复制的连接，都会存在这三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。下图是MySQL主从配置原理图例。

2. MySQL主从配置作用

· 数据的热备，作为后备数据库。主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

· 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

· 读写分离，使数据库能够支撑更大的并发。这一点在报表中尤其重要。部分报表SQL语句执行会非常的慢，有可能导致锁表，进而影响到前台服务。如果前台使用master，报表使用slave，那么报表SQL将不会造成前台锁，保证前台响应速度。

3. MySQL主从配置步骤

下文以一主二从配置多个MySQL数据库为例进行说明。

机器环境

主服务器：10.38.100.1，已有数据库test1、test2且库中有表、函数以及存储过程。
从服务器：10.38.100.98，已安装数据库，除此之外未做任何配置。
从服务器：10.38.100.99，已安装数据库，除此之外未做任何配置。
准备工作

主从服务器需要有完全相同的初始状态：

1、将主服务器要同步的数据库加锁，避免同步时数据发生改变。

mysql> use test1;
mysql> flush tables with read lock;
mysql> use test2;
mysql> flush tables with read lock;

2、将主服务器数据库中数据导出。

mysqldump -uroot -pxxxx --databases test1 test2 > /home/test.sql;

3、备份完成后，解锁主服务器数据库。

mysql> use test1;
mysql> unlock tables;
mysql> use test2;
mysql> unlock tables;

4、将初始数据导入从服务器数据库（从主服务器上传到从服务器相同目录下）。

mysql> source /homt/test.sql;

主服务器配置

1、 修改

/etc/my.cnf

配置文件，在my.cnf配置文件中[mysqld]下添加：

#主数据库端ID号

server_id=1

#开启二进制日志

log-bin=mysql-bin

#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

binlog-do-db=test1
binlog-do-db=test2

#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中

log-slave-updates

#将函数复制到slave

log_bin_trust_function_creators=1

2、重启主服务器上的MySQL后，添加允许从服务器同步数据的账户

添加slave账号root，假设密码是123456

mysql> grant replication slave on *.* to 'root'@'10.38.100.98' identified by '123456';
mysql> grant replication slave on *.* to 'root'@'10.38.100.99' identified by '123456';

更新数据库权限

mysql> flush privileges;

3、 查看主服务器状态

mysql> show master status \G;
***************** 1. row ****************

File: mysql-bin.000001 #当前记录的日志
Position: 600 #日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:

主服务器配置完毕，此刻开始不要再操作主服务器数据库，防止其状态值发生变化。

从服务器配置

1、修改/etc/my.cnf配置文件，在my.cnf配置文件[mysqld]中添加：

server_id=2
log-bin=mysql-bin
log-slave-updates
sync_binlog=0

#指定slave要复制哪个库，多个库就重复多遍

replicate-do-db=test1
replicate-do-db=test2

#MySQL主从复制的时候，Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据

slave-net-timeout=60
log_bin_trust_function_creators=1

2、 重启从服务器上MySQL后执行主从同步命令

执行同步命令，设置主服务器ip，同步账号密码，同步位置，两台机器分别执行

mysql> change master to master_host='10.38.100.1',
master_user='root', master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=600;

开启同步功能

mysql> start slave;

3、查看从服务器状态

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.38.100.98
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 600
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 700
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:

…

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.38.100.99
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 600
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 700
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:

…
Slave_IO_Running及Slave_SQL_Running进程必须是正常运行，即Yes状态，否则说明同步失败，若失败则需查看MySQL错误日志中具体报错详情来进行问题定位。
主从服务器配置完毕之后，在主服务器对应数据库上任意选择一条记录，进行修改，确认修改会自动同步到从服务器则说明配置完全成功。