`
stevencage
  • 浏览: 10372 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论

MySQL主从服务器配置

 
阅读更多

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Mysql 主从方案介绍

mysql 主从方案主要作用:
读写分离,使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql 语句非常的慢,导致锁表,影响前台服务。如果前台使用master ,报表使用slave ,那么报表sql 将不会造成前台锁,保证了前台速度。
发 扬不同表引擎的优点。目前Myisam 表的查询速度比innodb 略快,而写入并发innodbmyIsam 要好。那么,我们可以使用innodb 作为 master ,处理高并发写入,使用master 作为slave ,接受查询。或在myisam slave 中建立全文索引,解决innodb 无全文索引的弱点。
热备,slavemaster 的数据 准实时 同步。

准备工作。先分别安装两台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 就没有必要再开启了。注释掉。

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;

完成。

注意事项

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

 

 

 

============================= 老王指正===========================

有几个错误:

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 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” 的主机上连接。当然填写 “%” 也可以。
   
账户权限:需要为 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 的值,这样,从属服务器就可以从正确的位置开始进行复制。使用 --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;

当前网站的调整

 

 

分享到:
评论

相关推荐

    mysql主从服务器配置

    mysql主从服务器配置 一步步实现mysql的主从配置: 主服务器:192.168.1.19 从服务器:192.168.1.20 需要提供两台虚拟机作为测试。

    mysql主从服务器配置.pdf

    mysql主从服务器配置,1.mysql主从服务器介绍: MySQL主从又叫做Replication、AB复制。 简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步。 MySQL主从是基于binlog的,主...

    Linux下MySQL主从服务器的搭建详细实例完整版.pdf

    Linux下MySQL主从服务器的搭建详细实例完整版,欢迎大家来踩 Linux下MySQL主从服务器的搭建详细实例完整版,欢迎大家来踩 Linux下MySQL主从服务器的搭建详细实例完整版,欢迎大家来踩

    mysql 主从服务器配置 文档

    linux下mysql双机互备配置。实际操作的结果

    Mysql主从服务器双向配置

    mysql linix 主备同步 写数据库 读数据库

    mysql主从服务器配置特殊问题

    1.由于设置了slave的配置信息,mysql在数据库data目录下生成master.info,所以如有要修改相关slave的配置要先删除该文件,否则修改... 您可能感兴趣的文章:mysql 主从服务器的简单配置mysql主从服务器同步心得体会配置My

    配置mysql5.5主从服务器(转)

    配置mysql5.5主从服务器(转)

    Mysql主从配置

    自己手动配置Mysql主从服务器的步骤及方法的记录笔记。

    201407Windows中 MySQL5.5主从服务器配置及主或从挂掉问题处理

    Windows,MySQL5.5,主从配置,主或从挂掉问题处理

    mysql主从同步和一台服务器两个mysql

    mysql主从同步配置详解、一台服务器两个mysql,本地安装两个mysql实现主从同步

    mysql主从配置文件

    -&gt; 首先配置master服务器 -&gt; 下载MySQL -&gt; 修改配置文件 启用二进制日志、设置id、添加复制权限用户 -&gt; 配置环境变量 -&gt; 安装服务 -&gt; 配置Slave服务器 -&gt; 连接到master

    nagios监控mysql主从

    编写的脚本用来监控mysql主从模式 环境:  Centos5.6 mysql只做同步指定库,没有同步mysql库用户数据  登陆从服务器mysql,添加验证的用户  grant replication client on *.* to 'nagios'@localhost identified ...

    MySQL主从镜像双机

    1、在主控服务器上执行以下命令,为从服务器创建一...GRANT REPLICATION SLAVE ON *.* TO replic@192.168.1.143 IDENTIFIED BY '123456'; 2、修改主从服务器配置文件 主my.cnf内容: log-bin=mysqlmaster server-id=1

    mysql主从服务器的配置.md

    mysql读写分离的配置,配置django配置,数据库配置,linux配置

    MySQL8.0 主从复制配置过程介绍

    MySQL8.0 主从复制配置过程介绍,手把手教你如何配置主从服务器

    Mysql主从同步备份策略

    Mysql主从同步备份策略 五月 18th, 2009 at 17:30 - 3,803 views 环境: 主从服务器上的MySQL数据库版本同为5.1.34 主机IP:192.168.0.1 从机IP:192.168.0.2 一. MySQL主服务器配置 1.编辑配置文件/etc/my.cnf # ...

    mysql5.5 主从配置

    mysql5.5 主从配置,可以使得mysql服务器配置更加简单,熟练

    Mysql服务器主从配置

    一个Mysql主从配置的操作文档,Mysql版本要求大于等于5.1.7

    MySQL主从原理及配置详解

    MySQL主从配置及原理,供大家参考,具体内容如下 一、环境选择: 1.Centos 6.5 2.MySQL 5.7 二、什么是MySQL主从复制 MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器,另一台或多台...

Global site tag (gtag.js) - Google Analytics