MYSQL主从数据库的优势

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

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

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

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

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

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

据、删除数据等语句仍然会发送到主服务器中，以便主服务器和从服务器数据的同步。

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

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

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

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

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

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

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

一是数据更新的时间。如果要采取这种方式进行备份，那么对于数据同步的时间，要小于等于数据备份的周期。只有如此，才能够确保最近更新的数据被正

确的备份。此时即使主服务器出现了故障，用户丢失的也只是最近一个备份周期的数据。

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

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

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

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

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

Mysql主从方案介绍mysql主从方案主要作用：

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

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

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

准备工作。先分别安装两台MYSQL配置MASTER。找到my.cnf文件，修改：server-idlog_bin

expire_logs_daysmax_binlog_sizebinlog_do_dbbinlog_do_dbbinlog_ignore_db

=1

=/var/log/mysql/mysql-bin.log=10=100M

=DB_AdidasFootball=DB_CodeBuilder=test

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

binlog_do_db为需要复制的db。binlog_ignore_db为忽略复制的db。需要增加DB的话，就增加相应的一行。

重启master数据库，运行检查：mysql>showmasterstatus;#检查是否以master形式启动了。

+------------------+----------+----------------------------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|

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

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

1rowinset(0.00sec)

mysql>showvariableslike\"%log%\";

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

在master上为slave建立用户mysql>grantreplicationslave,reload,superon*.*to'slave'@'10.*'identifiedby'123456';这样，主机配置完毕。

配置slaveserver-id=2#随便什么数字，多台slave注意不能为重复就可以了。#log_bin=/var/log/mysql/mysql-bin.log#slave的binlog就没有必要再开启了。注释掉。master-host=192.168.0.3#master的IPmaster-user=slave#上面操作中，建立的用户名master-password=123456#上面操作中，建立的密码

重启slave，检查salve状态slave:mysql>showslavestatus;#很多很多列

接下来，将主机数据copy过来这个流程比较复杂：）各个步骤注意所在的机器

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

master:mysql>flushtableswithreadlock;#锁掉master服务器的所有表，禁止写入。

master:mysql>showmasterstatus;#还是上面的语句，查看并记录下Filemysql-bin.000002,Position1087

+------------------+----------+----------------------------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|

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

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

1rowinset(0.00sec)

chluo@master:~$mysqldumpAdidasFootball>AdidasFootball.sql#在命令行中导出DB的数据，这里是bash操作：）

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

chluo@slave:~$mysqlAdidasFootballchangemasterto

->master_log_file='mysql-bin.000002',#将这里修改为刚记录下来的数据->master_log_pos=1087;#还有这里slave:mysql>startslave;

完成。

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

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

1：其中，作为主机，server-id必须为1.似乎没有这个规定。。

2：binlog_ignore_db为忽略复制的db。

主服务器最好不要使用binlog_ignore_db，否则binlog就不完整了，不能回到过去某个时间点，可以通过从服务器忽略不想复制的库。3：grantreplicationslave,reload,superon安全考虑，不要给super

4：master-host=192.168.0.3

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

///////////////////////

MySQL主从复制是其最重要的功能之一。主从复制是一台服务器充当主服务器，另一台或多台服务器充当从服务器，主机自动复制到从机。对于多级复制，数据服务器即可充当主机，也可充当从机。MySQL复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。要做MySQL主从关系的设置，需要有两台MySQL主机。所以在开始配置之前需要准备两个MySQL服务器，可以是在虚拟机里安装，也可以在真实机器上安装。最好是在主/从服务器上安装相同的MySQL版本。或者是slave上的MySQL版本要高于Master上的版本。另外，推荐是选择较新的正式的MySQL版本。MySQL手册中对配置主从复制架构的提示和建议：1.WerecommendusingthemostrecentMySQLversionavailablebecausereplicationcapabilitiesarecontinuallybeingimproved.2.Wealsorecommendusingthesameversionforboththemasterandtheslave.3.Werecommendupgradingmastersandslavesrunningalphaorbetaversionstonew(production)versions.4.Replicationfroma5.0.3mastertoa5.0.2slavewillfail;froma5.0.4mastertoa5.0.3slavewillalsofail.5.Ingeneral,slavesrunningMySQL5.0.xmaybeusedwitholdermasters(eventhoserunningMySQL3.23,4.0,or4.1),butnotthereverse.6.Youcannotreplicatefromamasterthatusesanewerbinarylogformattoaslavethatusesanolderformat(forexample,fromMySQL5.0toMySQL4.1.)7.ThebinarylogformatasimplementedinMySQL5.0isconsiderablydifferentfromthatusedinpreviousversions.MajorchangesweremadeinMySQL5.0.3(forimprovementstohandlingofcharactersetsandLOADDATAINFILE)and5.0.4(forimprovementstohandlingoftimezones).一、操作环境说明主机(master)：WindowsServer2003，IP：10.0.0.101MySQL版本：mysql-essential-5.1.47-win32.msiMySQL安装路径：D:\\MySQLServer5.1从机(slave)：WindowsServer2003，IP：10.0.0.102MySQL版本：mysql-essential-5.1.47-win32.msiMySQL安装路径：D:\\MySQLServer5.1其他相关软件：MySQL管理客户端：Navicat8forMySQL(Windows)本文的实验环境是在Vmwareworkstation7.0虚拟机下搭建的。二、配置MySQL主机(master)：首先打开MySQL服务器的配置文件my.ini(Linux下是文件my.cnf)，一般都在MySQL安装目录下，本实验中在D:\\MySQLServer5.1下。记得先备份以下原来的配置文件。然后在my.ini文件末尾加入下面的代码：#MasterConfigserver-id=1log-bin=mysql-binbinlog-do-db=testbinlog-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”的主机上连接。当然填写“%”也可以。账户权限：需要为slave账户开放“全局许可权”：Reload、Super、ReplicationSlave、ReplicationClient。因为配置中是对test数据库进行复制，所以还要开放该对数据库test的一切权限。用MySQL命令行操作的话，请参考以下形式的语句：grantusageon*.*to'slave'@'10.%'identifiedby'slave';该命令就是添加一个用户了。“@”前面的“slave”是用户名，后面的是有效的域，“10.%”也就是说以“10”开头的任意IP都能使用这个帐号访问这台MySQL服务器，也可以设置成固定的IP比如“10.0.0.102”。by后面的“slave”是密码。MySQL主服务器配置完毕。把MySQL服务重新启动一下，在命令行窗口中执行：netstopmysql//关闭MySQL服务netstartmysql//开启MySQL服务当然也可以在Windows服务管理控制台操作MySQL服务。三、配置MySQL从机(slave)：打开从服务器的配置文件my.ini，同样在末尾加入下面的代码：#SlaveConfigserver-id=2master-host=10.0.0.101master-port=3306master-user=slavemaster-password=slavereplicate-do-db=test下面来解释下：Server-id从服务器标识id号Master-host主服务器的IP地址Master-port主服务器的端口Master-user用于和主服务器同步数据的用户名，我们在配置主服务器时已经添加了，即slaveMaster-password就是Master-user的密码Replicate-do-db要备份的数据库名称注意，如果my.ini文件中原来就有一个server-id=1的配置，需要删除掉(MySQL5.0较早的版本中有，本实验的5.1.47版本没有)。配置语句也都是小写字母。重启从服务器上的MySQL服务。四、测试登录到从服务器命令行界面，执行以下命令，查看从服务器的配置是否正确：Showslavestatus;执行后会显示很多东西,然后找到下面这两项：Slave_IO_Running:YesSlave_SQL_Running:Yes如果两项都是Yes那么就配置成功了。如果不全是Yes，则有可能是主服务器上slave账户的权限配置问题，需要反复检查。接着执行命令：loaddatafrommaster;这个命令就是从主服务器载入数据。本命令用于对主服务器进行快照，并拷贝到从属服务器上。它可以更新MASTER_LOG_FILE和MASTER_LOG_POS的值，这样，从属服务器就可以从正确的位置开始进行复制。使用--replicate-*-do-*和--replicate-*-ignore-*选项指定的表和数据库排除规则均被兑现。--如果命令执行后显示信息“QueryOK,0rowsaffected(0.13sec)”，则说明正确执行了数据复制。本“loaddatafrommaster”语句只对MyISAM类型的表起作用。如果试图载入一个非MyISAM表，会导致以下错误：ERROR11(08S01):Neterrorreadingfrommaster并且当拍摄快照时，会获得对主服务器的全局读取锁定。在载入操作期间，该锁定会阻止对主服务器的更新。然后，在主服务器建表和增删记录，应该在从服务器中看到对应的变化。说明配置成功了。五、MySQL主从复制错误的处理MySQL主从复制架构配置成功后，首先应该在主服务器上锁住表，然后备份数据，同步到从服务器上。flushtableswithreadlock;--copydatafiles...unlocktables;或者是通过mysqldump工具来备份和同步数据：mysqldump--user=root--password=xxxx--master-data=1--all-databases>dbsnapshot.sql然后主从复制开始正确工作。如果主服务器已经存在一定数量的数据，或从服务器由于某些原因中断了同步的进程，这个时候可以按照以下的步骤来重新同步：1、从主服务器导出欲同步的数据库；2、在从服务器运行stopslave停止同步；3、导入第1步的数据库到从服务器；4、在从服务器上运行startslave开始同步。CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;5.切换slave为master，在slave上：mysql>STOPSLAVE;mysql>RESETMASTER;6重置二进制log文件FLUSHMASTER;当前网站的调整