云南网站建设创新企业 昆明多彩网络公司

mysql自动备份同步 主从策略即时备份

来源：昆明多彩网络公司 日期：2011-02-24 阅读： 发表评论

1、A、B两台mysql服务器，A为主服务器，B为从服务器（用来备份的服务器）。

2、可以先在主服务器新建一个给从服务器使用的帐户：
sql命令命令为：GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* to 'backup@%' IDENTIFIED BY 'kmwzjs.com'; 可以将IP改成%，代表是所有的ip都能链接，或者10.%，则是10开头的ip，这条命令给于了同步数据库从数据访问的所有权限。
但是如果需要使用load data from master命令来一次性复制主数据库到从数据库的话，需要赋予改帐户所有权限，sql命令为：grant usage on *.* to 'backup'@%' identified by 'kmwzjs.com'。

4、修改主数据库的my.ini配置内容，在[mysqld]下面增加   
  ###下面这些是必须设置的###
server-id=1 #主数据库的进程，只要不和其他数据库进程代号冲突即可，一般是用服务器的最后ip来记录
log-bin=F:/server-php/MySQL Server 5.1/logs/log-bin.log #开启主数据库的二进制日志（改日志只记录数据库的修改，不记录查询），从数据库就是通过访问该日志来同步主数据库的，必须开启 binlog_format=mixed  #加上这个，主数据库修改数据就不会有警告了，默认不是这个的设置的。

###这些是可选设置#####
   binlog-ignore-db  #指定要被同步的数据库，如果不设置，则为所有的数据库，官方建议在slave端使用，多个数据库用,分割。
   binlog-do-db=aij  #不想被同步的数据库，要想过滤一些不想同步的数据库，官方建议在slave端使用，多个数据库用,分割。
   master-connect-retry=60 #中断重新连接时间间隔为60秒

5、修改从数据库的my.ini配置内容，在[mysqld]下面增加
 server-id=2 #从服务器B的ID值。注意不能和主服务器的ID值相同
 master-host=192.168.1.101 #主服务器的IP地址   
 master-user=backup #从服务器连接主服务器的帐号
 master-password=kmwzjs.com #从服务器连接主服务器的帐号密码
 master-port=3306 #主服务器端口

######以下内容为可选######
   replicate-do-db=mytest #需要同步的数据库，多个数据库用,分割
   binlog-ignore-db=mysql,information_schema #不需要同步的数据库，多个数据库用,分割
   log-bin=D:\mysqllog\log-bin.log #二进制变更日志，适用与双向同步，单向同步可以不设置
   master-connect-retry=60 #同步时间间隔为60秒
  
   如果从数据库data文件夹下存在master.info、relay-log.info就先停止从数据库的mysql（net stop mysql）、然后删除这两个文件，以后如果要修改主服务器和从服务器的my.ini配置，都要停止从服务器，然后删除这两个文件，因为从数据库的不会重复生成这两个文件。

6、这一步比较关键了，要将主服务器需要同步的数据库都要拷贝到从服务器上面来，这里有两种方式：
   第一种，将两个服务器的mysql停止，将主服务器的data文件夹下面的数据库文件拷贝到从数据库的数据data文件夹下，然后启动两个服务器。目的是先手动同步两个服务器的数据库，注意拷贝的时候不要拷贝mysql和information_schema这个两个数据库。
   第二种方法，先重启从服务器的mysql服务，在从服务器上使用stop slave;load data from master的sql语句，先停止同步，从服务器默认是开通同步的，然后直接将主服务器上的数据库统统拷贝到从服务器上来（仅对MyISAM引擎的表有用，所以库太多就无法一一将表修改过来，该命令会忽略mysql内置库），然后再start slave开始同步。
   注意，上面两种方法实施之前，最好使用以下流程来做：
   FLUSH TABLES WITH READ LOCK; //锁定所有数据库，在内存中的数据库也将存到硬盘上去,注意是大写命令。
    -- copy data files ...  //这里拷贝数据库到从数据库，因为数据库也将被锁定，不用担心在拷贝的时候被写入数据库。
    UNLOCK TABLES; //解除锁定，注意是大写命令

7、检测是否同步与排错可用以下命令查看：

1、在slave从服务器B上 输入：show slave status(也可以在phpmyadmin中输入sql语句)，主要查看：
Slave_IO_Running: Yes （#注：如果这个为NO，可重新修改my.ini 中相关slave的配置信息，重新启动查看 slave的状态还是显示未修改的数据，应为 第一次是读取my.ini，之后就会在mysql/下生成一个master.info 的文件，因此第二次就不会读取my.ini 的内容，而是读取master.info中的内容，为此要想使重新修改的my.ini生效的话，删除master.info文件 ，重起mysql既可解决。如果不能解决则可能是彼此之间的通讯问题或其它）。
Slave_SQL_Running: Yes （#注：如果这里为NO，很有可能是因为你的A库和B库的数据库不一致造成的。停止主从服务器MYSQL服务后删除A库中所有log-bin.log文件和B库所有的relay_log文件 并重启AB服务器即可解决）。Seconds_Behind_Master是否为0，0就是已经同步。

2、在master主服务器A上输入show processlist
mysql> show processlist\G
出现 Command: Binlog Dump 表现已经同步成功。

备注：MySQL主从复制经常出现的Slave_IO_Running:NO或Slave_SQL_Running:NO问题解决办法
在主数据库上查看POS位置：
mysql> show master status;
将查询到的信息给在从数据库上mysql> slave stop;
mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;
mysql> slave start;
mysql> show slave status
问题解决