MySQL主从服务器配置的优势

在MySQL数据库中，支持单项、异步复制。在复制过程中，一个服务器充当主服务器，而另外一台服务器充当从服务器。如下图所示。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时，从服务器会通知主服器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从那个时刻起发生的任何更新，然后锁住并等到主服务器通知新的更新。

这就是MySQL服务器数据库复制原理的基本说明。作为数据库管理员，对于这个原理只要有几个基本的了解即可。

▲其需要关注的是，我们可以从这个复制功能中得到哪些实惠？

好处一：实现服务器负载均衡

通过服务器复制功能，可以在主服务器和从服务器之间实现负载均衡。即可以通过在主服务器和从服务器之间切分处理客户查询的负荷，从而得到更好的客户相应时间。通常情况下，数据库管理员会有两种思路。

一是在主服务器上只实现数据的更新操作。包括数据记录的更新、删除、新建等等作业。而不关心数据的查询作业。数据库管理员将数据的查询请求全部 转发到从服务器中。这在某些应用中会比较有用。如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。此时就可以设置一台主服务器，专门用来数据的更新。同时设置多台从服务器，用来负责用户信息的查询。将数据更新与查询分别放在不同的服务器 上进行，即可以提高数据的安全性，同时也缩短应用程序的响应时间、提高系统的性能。

二是在主服务器上与从服务器切分查询的作业。在这种思路下，主服务器不单单要完成数据的更新、删除、插入等作业，同时也需要负担一部分查询作 业。而从服务器的话，只负责数据的查询。当主服务器比较忙时，部分查询请求会自动发送到从服务器重，以降低主服务器的工作负荷。当然，像修改数据、插入数据、删除数据等语句仍然会发送到主服务器中，以便主服务器和从服务器数据的同步。

要在数据库之间实现负载的均衡，其关键点就是数据同步的时间。如果主服务器与从服务器之间数据的更新时间比较长，此时从主服务器中查询得到的数 据就会同从从服务器中得到的数据有差异。而如果同步的时间比较短，如实现同步复制，对网络带宽、服务器设备等就有比较高的要求。

可见这个同步的时间选择直接关系到其应用的效果。那么这个同步的时间应该选择多少呢?这没有一个固定的答案。主要是看用户的需要。如用户对数据 的及时性要求并不是很高，或者数据更新的频率不是很高，那么这个同步的时间可以稍微长一点。但是如果这个数据的及时性要求很高，如股票的价格等等，此时就 需要能够实现同步更新。所以具体要看企业实际的应用才能够决定采用什么样的同步时间。

在采取这个应用时，需要注意MySQL数据库的复制是单向的。即只能够将数据从主服务器复制到从服务器，而不能够将数据从从服务器发生到主服务 器。这也就是说，数据库管理员不能够在从服务器上更新数据，否则的话，就可能会与主服务器上的数据产生冲突。默认情况下，系统会自动利用主服务器上的数据 来更新从服务器上的数据。即在从服务器上所做的任何更改，到时候都会失效。如果是用户的请求，一般不用担心。系统会自动判断用户的请求是查询请求还是数据 更新请求。并自动根据请求的类型转发到不同的服务器上。主要是数据库管理员，不要手痒痒，手动去更新从服务器上的数据。否则的话，就会导致从服务器与主服 务器之间数据的冲突。

好处二：通过复制实现数据的异地备份

如上图所示，可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

而如果使用复制来实现对数据的备份，就可以在从服务器上对数据进行备份。此时不仅不会干扰主服务气的正常运行，而且在备份过程中主服务器可以继 续处理相关的更新作业。同时在数据复制的同时，也实现了对数据的异地备份。除非主服务器和从服务器的两块硬盘同时损坏了，否则的话数据库管理员就可以在最 短时间内恢复数据，减少企业的由此带来的损失。

在通过这种方式进行异地备份时，主要需要注意如下二个细节：

一是数据更新的时间。如果要采取这种方式进行备份，那么对于数据同步的时间，要小于等于数据备份的周期。只有如此，才能够确保最近更新的数据被正确的备份。此时即使主服务器出现了故障，用户丢失的也只是最近一个备份周期的数据。

二是从服务器上硬盘的空间。根据一般用户的心态，从服务器的配置往往比主服务器要低。但是现在从服务器要承担起数据备份的任务，为此对于硬盘的 空间要求就要比主服务器要高。其不但要能够存储主服务器上的数据，而且还要对其进行备份。为此一般要求，从服务器上的存储空间是主服务器上的两倍。否则的 话，就容易出现磁盘空间不足而导致备份作业失败。

好处三：提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员也会采用两种手段。

一是主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制。如仍然只能够进行数据的查询，而 不能够进行数据的更新、删除等操作。这主要是从数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。这是同样的道 理。

二是从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操 作。为此就需要做好数据的安全性工作。即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患。

Mysql主从方案介绍

mysql主从方案主要作用：

读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

发 扬不同表引擎的优点。目前Myisam表的查询速度比innodb略快，而写入并发innodb比myIsam要好。那么，我们可以使用innodb作为 master，处理高并发写入，使用master作为slave，接受查询。或在myisam slave中建立全文索引，解决innodb无全文索引的弱点。

热备，slave和master的数据“准实时”同步。

准备工作。先分别安装两台MYSQL

配置MASTER。找到my.cnf文件，修改：

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

binlog_do_db = DB_AdidasFootball

binlog_do_db = DB_CodeBuilder

binlog_ignore_db = test

其中，作为主机，server-id必须为1.

binlog_do_db为需要复制的db。 binlog_ignore_db为忽略复制的db。需要增

加DB的话，就增加相应的一行。

重启master数据库，运行检查：

mysql> show master status; #检查是否以master形式启动了。

+------------------+----------+----------------------------------+---

---------------+

| File | Position | Binlog_Do_DB |

Binlog_Ignore_DB |

+------------------+----------+----------------------------------+---

---------------+

| mysql-bin.000002 | 1087 | DB_AdidasFootball,DB_CodeBuilder | test |

+------------------+----------+----------------------------------+---

---------------+

1 row in set (0.00 sec)

mysql> show variables like "%log%";

#需要看到这样的一行，说明binlog已经开启了: log_bin | ON

在master上为slave建立用户

mysql> grant replication slave, reload, super on *.* to 'slave'@'10.*'

identified by '123456';

这样，主机配置完毕。

配置slave

server-id = 2 #随便什么数字，多台slave注意不能为重复就

可以了。

#log_bin = /var/log/mysql/mysql-bin.log #slave的binlog

就没有必要再开启了。注释掉。

完成。

注意事项

从机必须有其需要的数据库，才能够进行同步，否则会忽略。

=============================老王指正=========================== 有几个错误：

1：其中，作为主机，server-id必须为1.

似乎没有这个规定。。

2：binlog_ignore_db为忽略复制的db。

主服务器最好不要使用binlog_ignore_db，否则binlog就不完整了，不能回到过去某个时间点，可以通过从服务器忽略不想复制的库。

3：grant replication slave, reload, super on

安全考虑，不要给super

4：master-host = 192.168.0.3

不用通过配置文件设置主服务器信息，而应该使用change master to这样的sql

///

MySQL 主从复制是其最重要的功能之一。主从复制是一台服务器充当主服务器，另一台或多台服务器充当从服务器，主机自动复制到从机。对于多级复制，数据服务器即可 充当主机，也可充当从机。MySQL 复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。 要做 MySQL 主从关系的设置，需要有两台 MySQL 主机。所以在开始配置之前需要准备两个 MySQL 服务器，可以是在虚拟机里安装，也可以在真实机器上安装。

最好是在主/从服务器上安装相同的 MySQL 版本。或者是 slave 上的 MySQL 版本要高于 Master 上的版本。另外，推荐是选择较新的正式的 MySQL 版本。

MySQL 手册中对配置主从复制架构的提示和建议：

1. We recommend using the most recent MySQL version available because replication capabilities are continually being improved.

2. We also recommend using the same version for both the master and the slave.

3. We recommend upgrading masters and slaves running alpha or beta versions to new (production) versions.

4. Replication from a 5.0.3 master to a 5.0.2 slave will fail; from a 5.0.4 master to a 5.0.3 slave

第6/9页

will also fail.

5. In general, slaves running MySQL 5.0.x may be used with older masters(even those running MySQL 3.23, 4.0, or 4.1), but not the reverse.

6. You cannot replicate from a master that uses a newer binary log format to a slave that uses an older format (for example, from MySQL 5.0 to MySQL 4.1.)

7. The binary log format as implemented in MySQL 5.0 is considerably different from that used in previous versions. Major changes were made in MySQL 5.0.3(for improvements to

handling of character sets and LOAD DATA INFILE) and 5.0.4 (for improvements to handling of time zones).

一、操作环境说明

主机(master)：

Windows Server 2003，IP：10.0.0.101

MySQL版本：mysql-essential-5.1.47-win32.msi

MySQL 安装路径：D:\MySQLServer5.1

从机(slave)：

Windows Server 2003，IP：10.0.0.102

MySQL版本：mysql-essential-5.1.47-win32.msi

MySQL 安装路径：D:\MySQLServer5.1

其他相关软件：

MySQL 管理客户端：

Navicat 8 for MySQL(Windows)

本文的实验环境是在 Vmware workstation 7.0 虚拟机下搭建的。

二、配置 MySQL 主机(master)：

首先打开 MySQL 服务器的配置文件 my.ini(Linux 下是文件 my.cnf)，一般都在

MySQL 安装目录下，本实验中在 D:\MySQLServer5.1 下。记得先备份以下原来的配置文件。然后在 my.ini 文件末尾加入下面的代码：

#Master Config

server-id = 1

log-bin = mysql-bin

binlog-do-db = test

binlog-ignore-db = mysql

注意上面这几项配置，如果在配置文件中已经存在就不用添加了(MySQL5.0较早的版本中有，本实验的5.1.47版本没有)。注意都是小写字母。

上面的这些配置的含义:

- server-id 顾名思义就是服务器标识id号了

- log-bin 指定日志类型

- binlog-do-db 是你需要复制的数据库名称，如果有多个就用逗号“,”分开

- binlog-ignore-db 是不需要复制的数据库名称，如果有多个就用逗号“,”分开

然后添加一个用户，用于从服务器访问：

很多教程使用 MySQL 命令行(CLI)命令来操作，我们这里使用 MySQL 图形界面的管理客户端 Navicat 来操作，结果是一样的。

连接到 10.0.0.101 上的 MySQL 服务器，进入用户管理界面，建立一个用户 slave，密码也是 slave。主机填写“10.%”，意味着只允许该账户从 IP 地址开头为“10”的主机上连接。当然填写“%”也可以。

第7/9页

账户权限：需要为 slave 账户开放“全局许可权”：Reload、Super、Replication Slave、Replication Client。因为配置中是对 test 数据库进行复制，所以还要开放该对数据库 test 的一切权限。

用 MySQL 命令行操作的话，请参考以下形式的语句：

grant usage on *.* to 'slave'@'10.%' identified by 'slave';

该命令就是添加一个用户了。“@”前面的“slave”是用户名，后面的是有效的域，“10.%”也就是说以“10”开头的任意IP都能使用这个帐号访问 这台 MySQL 服务器，也可以设置成固定的 I P比如“10.0.0.102”。by 后面的“slave”是密码。

MySQL 主服务器配置完毕。把 MySQL 服务重新启动一下，在命令行窗口中执行： net stop mysql //关闭MySQL服务

net start mysql //开启MySQL服务

当然也可以在 Windows 服务管理控制台操作 MySQL 服务。

三、配置 MySQL 从机(slave)：

打开从服务器的配置文件 my.ini，同样在末尾加入下面的代码：

# Slave Config

server-id = 2

master-host = 10.0.0.101

master-port = 3306

master-user = slave

master-password = slave

replicate-do-db = test 下面来解释下： Server-id 从服务器标识id号 Master-host 主服务器的 IP 地址 Master-port 主服务器的端口

Master-user 用于和主服务器同步数据的用户名，我们在配置主服务器时已经添加了，即slave

Master-password 就是 Master-user 的密码

Replicate-do-db 要备份的数据库名称

注意，如果 my.ini 文件中原来就有一个 server-id=1 的配置，需要删除掉(MySQL5.0较早的版本中有，本实验的5.1.47版本没有)。配置语句也都是小写字母。

重启从服务器上的 MySQL 服务。

四、测试

登录到从服务器命令行界面，执行以下命令，查看从服务器的配置是否正确： Show slave status;

执行后会显示很多东西, 然后找到下面这两项：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果两项都是 Yes 那么就配置成功了。如果不全是 Yes，则有可能是主服务器上 slave 账户的权限配置问题，需要反复检查。

接着执行命令：

load data from master;

这个命令就是从主服务器载入数据。本命令用于对主服务器进行快照，并拷贝到从属服务器上。它可以更新MASTER_LOG_FILE和 MASTER_LOG_POS的值，这样，从属服务

第8/9页

器就可以从正确的位置开始进行复制。使用--replicate-*-do-*和 --replicate-*-ignore-*选项指定的表和数据库排除规则均被兑现。--

如果命令执行后显示信息“Query OK, 0 rows affected (0.13 sec)”，则说明正确执行了数据复制。

本“load data from master”语句只对 MyISAM 类型的表起作用。如果试图载入一个非MyISAM表，会导致以下错误：

ERROR 1189 (08S01): Net error reading from master

并且当拍摄快照时，会获得对主服务器的全局读取锁定。在载入操作期间，该锁定会阻止对主服务器的更新。

然后，在主服务器建表和增删记录，应该在从服务器中看到对应的变化。说明配置成功了。

五、MySQL 主从复制错误的处理

MySQL 主从复制架构配置成功后，首先应该在主服务器上锁住表，然后备份数据，同步到从服务器上。

flush tables with read lock;

-- copy data files ...

unlock tables;

或者是通过 mysqldump 工具来备份和同步数据：

mysqldump --user=root --password=xxxx --master-data=1 --all-databases > dbsnapshot.sql 然后主从复制开始正确工作。

如果主服务器已经存在一定数量的数据，或从服务器由于某些原因中断了同步的进程，这个时候可以按照以下的步骤来重新同步：

1、从主服务器导出欲同步的数据库；

2、在从服务器运行 stop slave 停止同步；

3、导入第1步的数据库到从服务器；

4、在从服务器上运行 start slave 开始同步。

CHANGE MASTER TO

MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;

5.切换slave为master，在slave上：

mysql> STOP SLAVE;

mysql> RESET MASTER;

6 重置二进制log文件

FLUSH MASTER;

当前网站的调整

master-host = 192.168.0.3 #master的IP

master-user = slave #上面操作中，建立的用户名

master-password = 123456 #上面操作中，建立的密码

重启slave， 检查salve状态

slave:mysql> show slave status;

#很多很多列

接下来，将主机数据 copy 过来

这个流程比较复杂：）各个步骤注意所在的机器

slave:mysql> stop slave; #停掉slave的复制先。

master:mysql> flush tables with read lock; #锁掉master服务器的所有表，

禁止写入。

master:mysql> show master status; #还是上面的语句，查看并记录下 File

mysql-bin.000002, Position 1087

+------------------+----------+----------------------------------+---

---------------+

| File | Position | Binlog_Do_DB |

Binlog_Ignore_DB |

+------------------+----------+----------------------------------+---

---------------+

| mysql-bin.000002 | 1087 | DB_AdidasFootball,DB_CodeBuilder | test |

+------------------+----------+----------------------------------+---

---------------+

1 row in set (0.00 sec)

chluo@master:~$ mysqldump AdidasFootball > AdidasFootball.sql #在命令

行中导出DB的数据，这里是bash操作：）

master:mysql> unlock tables; #导出完成之后，解锁。 master可以继续跑起

来了。

chluo@slave:~$ mysql AdidasFootball < AdidasFootball.sql #在slave的

命令行中导入DB的数据，这里又是bash操作：）

slave:mysql> change master to

-> master_log_file='mysql-bin.000002', #将这里修改为刚记录下来的数据

-> master_log_pos=1087; #还有这里

slave:mysql> start slave;