MySQL 5数据同步备份(master-slave)

    MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日 志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新。如上所述，每个mster/slave上都有3个线程。每个 master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。在MySQL 4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。

    一般使用MySQL的时候，如果数据量不大，我们都使用一台MySQL，的时候使用mysqldump工具就可以了，但是随着业务不断发展，问题出现了：
首先：数据量往往直线上升，单独一台数据库服务器开始出现性能的瓶颈，数据访问越来越慢。
    其次：备份也变得困难了，因为mysqldump是导出一份文本文件，而数据量特别大的时候，这样的备份往往需要很长时间，可能有人会说，我们可以直接通 过拷贝数据文件来备份数据库，这样很方便，快捷，不错，这样是比mysqldump方便快捷，但是，直接拷贝数据文件备份的方式要求我们必须先关闭 mysql服务，然后再拷贝数据文件，否则，你拷贝的文件很可能是坏的。而实际运行的mysql服务往往要求在任何时候都不可以停止服务，所以这样的备份 方式在此情况下不可行。
如果你遇到了类似上面的问题，你就可以使用建立MySQL主从服务器的方式来解决，下面先来看看主从服务器的设置：
    Recommended：MySQL主从服务器最好使用相同的软件版本，以避免不不可预期的故障。

(1)配置主服务器
#vi /etc/my.cnf
[mysqld]
log-bin=hnolwebserver-bin //日志文件的名称，这里可以制定日志到别的目录 如果没有设置则默认主机名的一个日志名称
server-id = 1 //数据库的id这个应该默认是1就不用改动
binlog-do-db=nsdb//需同步的数据库，多数据库用binlog-do-db=nsdb1。
character-set-server=gb2312 //设置支持gb2312的字符集。

#/usr/local/mysql/bin/mysql -u root -p

注意：关于要复制多个数据库时，binlog-do-db和replicate-do-db选项的设置，如果要备份多个数据库，只要重复设置相应选项就可以了。

比如：

binlog-do-db=a
binlog-do-db=b

replicate-do-db=a
replicate-do-db=b

———————————————————————————–
#建tianya数据库。
#mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.200.101' IDENTIFIED BY 'root'; #添加一个主机为192.168.18.101的backup帐号，具有全局REPLICATION SLAVE权限。
在phpMyAdmin或SQL Yog中导出nsdb数据库的SQL数据为nsdb.sql。
#mysql>show master status\G;
*************************** 1. row ***************************
File: hnolwebserver-bin.000010
Position: 649
Binlog_Do_DB: nsdb
Binlog_Ignore_DB:
1 row in set (0.01 sec)
记下File与Position在设置从服务中需要用到。

PS:

1.在主服务器上，设置读锁，这个操作是为了确保没有数据库操作以便获得一个一致性的快照
flush tables with read lock;

2.得到当前主服务器的当前二进制日志明和偏移量值。这个操作的目的是为了在从数据库启动以后，从这个点开始进行恢复
show master status
3.备份现有的数据库
4.主数据库恢复写操作
unlock tables;

(2)配置从服务器
修改/etc/my.cnf，如是多实例Msyql，则修改my_multi.cnf中的任一个实例定义中

       [mysqld]
master-host=192.168.18.102 #主服务端主机
master-user=backup #主服务端开设的帐号
master-password=backup#主服务端开设的密码
master-port=3306 #主服务端Mysql端口
server-id=2 #设置数据库id默认主服务器是1可以随便设置但是如果有多台从服务器则不能重复。
master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差
replicate-do-db=nsdb#同步的数据库
character-set-server=gb2312 #设置中文字符集。

将nsdb.sql导入到从服务器中。与主服务器数据库一致.

#/usr/local/mysql/bin/mysql -u root -p
设置主服务器的各种参数:
#mysql>slave stop; #停止slave的服务
#mysql>CHANGE MASTER TO
#mysql>MASTER_HOST='192.168.18.102',#主服务器的IP地址
#mysql>MASTER_USER='backup', #同步数据库的用户
#mysql>MASTER_PASSWORD='backup', #同步数据库的密码
#mysql>MASTER_LOG_FILE='hnolwebserver-bin.000016', #主服务器二进制日志的文件名(前面要求记住的参数)
#mysql>MASTER_LOG_POS=2567; #日志文件的开始位置(前面要求记住的参数)
#mysql>slave start; #启动同步数据库的线程

PS:

从机的 log-bin 的参数 建议添加，binary logging - not required for slaves, but recommended

当从属服务器含有中继日志，并且您出于某种原因想要执行此日志时。要这么做时，不需要连接主服务器。您只需要使用CHANGE MASTER TO并启动SQL线程（START SLAVE SQL_THREAD）。                         

设置：

mysql> CHANGE MASTER TO

    ->     RELAY_LOG_FILE='slave-relay-bin.006',

    ->     RELAY_LOG_POS=4025;

甚至可以在一个用于独立非从属服务器的非复制型设置中使用上述操作，在崩溃之后进行复原。假 设服务器已崩溃，同时您已恢复了备份。您想要重新播放服务器自己的二进制日志（不是中继日志，而是正规的二进制文件），例如名为myhost- bin.*。首先，应在安全的地方制作这些二进制日志的备份，以防您没有完全遵守以下步骤，意外地让服务器清理了二进制文件。使用SET GLOBAL relay_log_purge=0，进一步增加安全性。然后启动不含--log-bin选项的服务器。使用--replicate-same- server-id, --relay-log=myhost-bin（让服务器相信，这些正规的二进制日志是中继日志）和--skip-slave-start options选项。当服务器启动后，发布以下语句：

mysql> CHANGE MASTER TO

    ->     RELAY_LOG_FILE='myhost-bin.153',

    ->     RELAY_LOG_POS=410,

    ->     MASTER_HOST='some_dummy_string';

mysql> START SLAVE SQL_THREAD;

服务器会读取并执行自己的二进制日志，完成崩溃复原。当复原完成后，运行STOP SLAVE，关闭服务器，删除master.info和relay-log.info，并使用原来的选项重新启动服务器。

要让服务器认为它是一个从属服务器，需要指定MASTER_HOST（甚至使用假值）。

主要配置参数：

MASTER_HOST和MASTER_PORT是主服务器主机和其TCP/IP接口的主机名（或IP地址）。注意，如果MASTER_HOST与localhost相等，那么，和MySQL的其它部分一样，接口可以被忽略。

如果指定了MASTER_HOST或MASTER_PORT，则从属服务器会假定主服务器与 以前不一样（即使您指定的主机或接口值与当前值是一样的。）在此情况下，主服务器二进制日志的名称和位置的原有值不再适用，因此，如果您不指定语句中的 MASTER_LOG_FILE和MASTER_LOG_POS，MASTER_LOG_FILE=''和MASTER_LOG_POS=4会被静默地添 加。

在MASTER_LOG_FILE和MASTER_LOG_POS坐标点，从属服务器I/O 线程在启动之后从主服务器读取。如果只指定了其中一个，则从属服务器不能指定RELAY_LOG_FILE或RELAY_LOG_POS。如果 MSATER_LOG_FILE和MASTER_LOG_POS都没有被指定，则从属服务器会使用在CHANGE MASTER被发布前的最后一个slave SQL thread坐标。当只想改变要使用的 密码时，这可以确保复制的连续性。即使从属服务器SQL线程落后于从属服务器I/O线程，也可以确保复制的连续性。

CHANGE MASTER会删除所有的中继日志文件并启动一个新的日志，除非指定了RELAY_LOG_FILE或RELAY_LOG_POS。在此情况下，中继日志被保持；relay_log_purge全局变量被静默地设置为0。

CHANGE MASTER TO可以更新master.info和relay-log.info文件的内容。

当拥有主服务器快照并拥有日志和对应的偏移量时，CHANGE MASTER对于设置从属服务器是有用的。在把快照载入从属服务器之后，您可以在从属服务器上运行CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master。

补充：
在从服务器上使用show slave status
Slave_IO_Running,为No,则说明IO_THREAD没有启动，请执行slave start [IO_THREAD]
Slave_SQL_Running为No则复制出错,查看Last_error字段排除错误后执行slave start [SQL_THREAD]

查看Slave_IO_State字段
空 //复制没有启动
Connecting to master//没有连接上master
Waiting for master to send event//已经连上

补充：可以使用LOAD DATA FROM MASTER语句来建立slave。但有约束条件：
数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备RELOAD和SUPER权限。
在master端执行RESET MASTER清除已有的日志变更，
此时slave端会因为找不到master日志无法启动IO_THREAD，请清空data目录下
relay-log.info,hosname-relay-bin*等文件重新启动mysql
中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的–
relay-log和–relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用
–relay-log-info-file启动选项修改文件名。
双机互备则是两个mysql同时配置为master及slave

主服务器上的相关命令：
show master status
show slave hosts
show logs
show binlog events
purge logs to ‘log_name’
purge logs before ‘date’
reset master(老版本flush master)
set sql_log_bin=

从服务器上的相关命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志