mysql数据同步备份（同步整个数据库)
时间：2006-07-27 来源：lichuanhua
一.mysql 主从服务的配置
A服务器： 192.168.67.124 主服务器master
B服务器： 192.168.67.125 副服务器slave
操作系统:slackware linux 10
A: 主服务器设置(主服务器设置)
1. #mysql –u root –p
Mysql>grant replication slave,reload,super on *.* To
[email protected] identified by ‘123’ ;
上面是Master开放一个账号back密码123给IP:192.168.67.125有档案处理的权限
查看刚才开放的账号
#mysql mysql
Mysql> select * from user; 可以查到用户的信息
2. 关闭数据库
#mysqladmin –u root –p shutdown
3. 同步mysql主和从的数据库.把主服务器下面的数据拷贝到从服务器下,我是使用源码编译的mysql.数据库在/usr/local/mysql目录下,数据就在/usr/local/mysql/var下,就是把主服务器/usr/local/mysql/var下的所有数据拷贝到从服务器的/usr/local/mysql/var里
#scp –r /usr/local/mysql/var/* [email protected]:/usr/local/mysql/var/
上面是把主服务器的数据拷贝到从的服务器中
4.修改主服务器的配置文件/etc/my.cnf
确认master上的 `my.cnf` 文件 [mysqld] 区间有 log-bin 选项。这个区间还必须有 server-id=master_id 选项，的值必须是 1 到 2^32-1 之间的正整数。
在此设置为：
[mysqld]
log-bin
server-id=1
启动主服务器的mysql
#mysqld_safe &
5.查看主服务器的同步配置情况,如图所示
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
B: 从服务器设置(从服务器设置)
1.关闭从服务器数据库
#mysqladmin –u root –p shutdown
2.修改从服务器的/etc/my.cnf在my.cnf文件中增加如下选项：
[mysqld]
server-id=slave_id
slave_id 的值和 master_id 类似，是 1 到 2^32-1 之间的正整数。另外，这个ID必须不能和master的ID一样。
在此设置为：
[mysqld]
server-id=2
注意：由于设置了slave的配置信息，mysql在数据库目录下生成master.info 所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效.此文件在/usr/local/mysql/var/下
3.启动从数据库服务
# #mysqld_safe &
4.在slave上执行如下语句，把各个选项的值替换成真实值：
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name', //主服务器的IP地址
MASTER_USER='replication_user_name', //同步数据库的用户
MASTER_PASSWORD='replication_password', ////同步数据库的密码
MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名
MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置
recorded_log_file_name和recorded_log_position是在主服务器里面查看的,在192.168.67.124机器上查看主服务器的同步配置
在192.168.67.124上:
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
如上面显示: recorded_log_file_name就是上面的 File
recorded_log_position就是上面的 Position
在从服务器192.168.67.125上执行sql语句如下:
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.67.124',
MASTER_USER='back',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='darkstar-bin.000024',
MASTER_LOG_POS=316;
5. 启动从数据库的slave
mysql> slave start;
配制完成
C. 双机互备模式
1. 如果在A加入slave设置，在B加入master设置，则可以做B->;A的同步。
在A的配置文件中 mysqld 配置项加入以下设置，vi /etc/my.cnf：
master-host=192.168.67.125
master-user=back
master-password=123
master-port=3306
2. #mysql –u root –p
Mysql>grant replication slave,reload,super on *.* To
[email protected] identified by ‘123’ ;
在B的配置文件中 mysqld 配置项加入以下设置,vi /etc/my.cnf：
log-bin=
这样设置就能使其B数据库同步A数据库
二. mysql 数据同步相关说明
1. 同步机制实现概述
MySQL同步机制基于master把所有对数据库的更新操作(更新、删除 等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。
每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。
2. 同步实现细节
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线程合并成一个了，它不使用中继日志。
slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，不过I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。
在mysql中执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况
在master上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 21
User: root
Host: localhost
db: mysql
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 25
User: back
Host: 192.168.67.125:34085
db: NULL
Command: Binlog Dump
Time: 746
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.01 sec)
在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。
在slave上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 70479
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 16971
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 8
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)
这表明线程2是I/O线程，它正连接到master上；线程3是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。
注意，Time 字段的值告诉我们slave上的日志比master晚了多久。
3. Master 同步线程状态
以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来。
Sending binlog event to slave
事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。
Finished reading one binlog; switching to next binlog
读取完了一个二进制日志，正切换到下一个。
Has sent all binlog to slave; waiting for binlog to be updated
已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。
Waiting to finalize termination
当前线程停止了，这个时间很短
4. Slave的I/O线程状态
以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。
Connecting to master
该线程证尝试连接到master上。
Checking master version
确定连接到master后出现的一个短暂的状态。
Registering slave on master
确定连接到master后出现的一个短暂的状态。
Requesting binlog dump
确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。
Waiting to reconnect after a failed binlog dump request
如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由
--master-connect-retry 选项来指定。
Reconnecting after a failed binlog dump request
该线程正尝试重连到master。
Waiting for master to send event
已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。
Queueing master event to the relay log
已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。
Waiting to reconnect after a failed master event read
读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
5. Slave的SQL线程状态
以下列出了slave的SQL线程 State 字段中最常见的几种状态：
Reading event from the relay log
从中继日志里读到一个事件以备执行。
Has read all relay log; waiting for the slave I/O thread to update it
已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。
A服务器： 192.168.67.124 主服务器master
B服务器： 192.168.67.125 副服务器slave
操作系统:slackware linux 10
A: 主服务器设置(主服务器设置)
1. #mysql –u root –p
Mysql>grant replication slave,reload,super on *.* To
[email protected] identified by ‘123’ ;
上面是Master开放一个账号back密码123给IP:192.168.67.125有档案处理的权限
查看刚才开放的账号
#mysql mysql
Mysql> select * from user; 可以查到用户的信息
2. 关闭数据库
#mysqladmin –u root –p shutdown
3. 同步mysql主和从的数据库.把主服务器下面的数据拷贝到从服务器下,我是使用源码编译的mysql.数据库在/usr/local/mysql目录下,数据就在/usr/local/mysql/var下,就是把主服务器/usr/local/mysql/var下的所有数据拷贝到从服务器的/usr/local/mysql/var里
#scp –r /usr/local/mysql/var/* [email protected]:/usr/local/mysql/var/
上面是把主服务器的数据拷贝到从的服务器中
4.修改主服务器的配置文件/etc/my.cnf
确认master上的 `my.cnf` 文件 [mysqld] 区间有 log-bin 选项。这个区间还必须有 server-id=master_id 选项，的值必须是 1 到 2^32-1 之间的正整数。
在此设置为：
[mysqld]
log-bin
server-id=1
启动主服务器的mysql
#mysqld_safe &
5.查看主服务器的同步配置情况,如图所示
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
B: 从服务器设置(从服务器设置)
1.关闭从服务器数据库
#mysqladmin –u root –p shutdown
2.修改从服务器的/etc/my.cnf在my.cnf文件中增加如下选项：
[mysqld]
server-id=slave_id
slave_id 的值和 master_id 类似，是 1 到 2^32-1 之间的正整数。另外，这个ID必须不能和master的ID一样。
在此设置为：
[mysqld]
server-id=2
注意：由于设置了slave的配置信息，mysql在数据库目录下生成master.info 所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效.此文件在/usr/local/mysql/var/下
3.启动从数据库服务
# #mysqld_safe &
4.在slave上执行如下语句，把各个选项的值替换成真实值：
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name', //主服务器的IP地址
MASTER_USER='replication_user_name', //同步数据库的用户
MASTER_PASSWORD='replication_password', ////同步数据库的密码
MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名
MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置
recorded_log_file_name和recorded_log_position是在主服务器里面查看的,在192.168.67.124机器上查看主服务器的同步配置
在192.168.67.124上:
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
如上面显示: recorded_log_file_name就是上面的 File
recorded_log_position就是上面的 Position
在从服务器192.168.67.125上执行sql语句如下:
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.67.124',
MASTER_USER='back',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='darkstar-bin.000024',
MASTER_LOG_POS=316;
5. 启动从数据库的slave
mysql> slave start;
配制完成
C. 双机互备模式
1. 如果在A加入slave设置，在B加入master设置，则可以做B->;A的同步。
在A的配置文件中 mysqld 配置项加入以下设置，vi /etc/my.cnf：
master-host=192.168.67.125
master-user=back
master-password=123
master-port=3306
2. #mysql –u root –p
Mysql>grant replication slave,reload,super on *.* To
[email protected] identified by ‘123’ ;
在B的配置文件中 mysqld 配置项加入以下设置,vi /etc/my.cnf：
log-bin=
这样设置就能使其B数据库同步A数据库
二. mysql 数据同步相关说明
1. 同步机制实现概述
MySQL同步机制基于master把所有对数据库的更新操作(更新、删除 等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。
每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。
2. 同步实现细节
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线程合并成一个了，它不使用中继日志。
slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，不过I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。
在mysql中执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况
在master上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 21
User: root
Host: localhost
db: mysql
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 25
User: back
Host: 192.168.67.125:34085
db: NULL
Command: Binlog Dump
Time: 746
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.01 sec)
在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。
在slave上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 70479
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 16971
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 8
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)
这表明线程2是I/O线程，它正连接到master上；线程3是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。
注意，Time 字段的值告诉我们slave上的日志比master晚了多久。
3. Master 同步线程状态
以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来。
Sending binlog event to slave
事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。
Finished reading one binlog; switching to next binlog
读取完了一个二进制日志，正切换到下一个。
Has sent all binlog to slave; waiting for binlog to be updated
已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。
Waiting to finalize termination
当前线程停止了，这个时间很短
4. Slave的I/O线程状态
以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。
Connecting to master
该线程证尝试连接到master上。
Checking master version
确定连接到master后出现的一个短暂的状态。
Registering slave on master
确定连接到master后出现的一个短暂的状态。
Requesting binlog dump
确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。
Waiting to reconnect after a failed binlog dump request
如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由
--master-connect-retry 选项来指定。
Reconnecting after a failed binlog dump request
该线程正尝试重连到master。
Waiting for master to send event
已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。
Queueing master event to the relay log
已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。
Waiting to reconnect after a failed master event read
读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
5. Slave的SQL线程状态
以下列出了slave的SQL线程 State 字段中最常见的几种状态：
Reading event from the relay log
从中继日志里读到一个事件以备执行。
Has read all relay log; waiting for the slave I/O thread to update it
已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。
相关阅读 更多 +
