MySQL实施热备份

MySQL内部复制功能是建立在两个或两个以上服务器之间，通过设定它们之间的
主-从关系来实现的。其中一个作为主服务器，其它的作为副服务器。以下将
详细讨论如何配置两台服务器，将一个设为主服务器，另一个设为副服务器。
并且描述一下在它们之间进行切换的处理过程。

1      操作步骤

1.1   主服务器授权

授权副服务器可以连接主服务器并可以进行更新。这是在主服务器上进行的，创建一个
username和password供副服务器访问时使用。在MySQL命令行下输入

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘username’@’slave-server-hostname’ IDENTIFIED BY ‘password’;

根据实际情况替换username，slave-server-hostname，password。

 

1.2   数据复制

一旦建立好访问许可后，接下来就是从主服务器向副服务器复制数据。可以从mysqldump
工具。

#锁住表，禁止update。

mysql> FLUSH TABLES WITH READ LOCK;

#导出数据 Windows中为命令提示符。

shell> mysqldump –opt databasename > backup.sql

#解除锁定

mysql> UNLOCK TABLES

将备份好的数据库脚本backup.sql拷贝到副服务器。

另外也可以使用LOAD DATA FROM MASTER语句将主服务器的数据传输到副服务器，但
使用上有些限制。
注意：在执行数据复制的过程中，要确保不能对主服务器执行更新操作。

1.3   配置主服务器

在选项配置文件中赋予主服务器一个server-id，该id必须是1到2^23-1范围
内的唯一值。主服务器和副服务器的server-id不能相同。

另外，还需要配置主服务器，使之启用二进制日志，即在选项配置文件中添加log-bin
启动选项。

选项文件示例(其中#开头的为注释)：

[mysqld]

#唯一值，并不能与副服务器相同

server-id=3

#日志文件以binary_log为前缀，如果不给log-bin赋值，日志文件将以#master-server-hostname为前缀

log-bin = binary_log

#日志文件跳过的数据库(可选属性)

binlog-ignore-db=mysql

#日志文件操作的数据库(可选属性)

binlog-do-db=netbar

 

 

 

 

1.4   重启主服务器

配置好以上选项后，重启MySQL服务，新选项将生效。现在，所有对数据库中信息的
更新操作将被写进日志中。

 

注意：如果主服务器的二进制日志已经启用，关闭并重新启动之前应该对以前的二
进制日志进行备份。重新启动后，应使用RESET MASTER语句清空以前的日志。

 

1.5   配置副服务器

在副服务器上的MySQL选项配置文件中添加以下参数。

[mysqld]

#唯一，并与主服务器上的server-id不同。

server-id=7

#主服务器的主机名或者ip地址

master-host=master-server-hostname

#如果主服务器没有在默认的端口上监听，则需确定master-port选项

master-port=3306

#步骤2.1中创建的用户名和密码

master-user=username

master-password=password

#复制操作要针对的数据库(可选，默认为全部)

replicate-do-db=netbar

#如果主副服务器间连接经常失败，需增加master-retry-count和master-connect-retry

#master-retry-count连接重试的次数

#master-connect-retry 连接失败后等待的秒数

master-retry-count = 999

master-connect-retry = 60

 

 

 

 

 

 

1.6   重启副服务器

副服务器上MySQL服务重启后，还在数据目录中创建一个master.info文件，其中包含
所有有关复制过程的信息(连接主服务器的相关信息及与主服务器交换数据的相关信息)。
在初次启动以后，副服务器将检查这个master.info文件，以得到相关信息。如果想修
改复制选项，删除master.info并重启MySQL服务，在启动过程中使用选项配置文件中的
新选项进行重新创建了master.info文件。

 

将主服务器上备份好的数据库脚本文件(backup.sql)导入到副服务器数据库中，以便保
证主-副服务器上进行复制操作的起点一样。

2      管理复制过程

2.1   管理主服务器

mysql> SHOW PROCESSLIST

显示数据库服务器上有关线程的信息

mysql> SHOW BINLOG EVENTS

显示二进制日志中的信息(MySQL 4.0以后的版本中)

mysql> SHOW MASTER STATUS

显示主服务器中有关二进制日志的其他信息。

2.2   管理副服务器

mysql> START SLAVE

用于开始或恢复复制，START SLAVE命令执行后，并不意味着一切都OK，需要用 
SHOW SLAVE STATUS来监督副服务器的活动

在SHOW SLAVE STATUS的命令输出后，应该包含Slave_IO_Running对应的值为YES，
Slave_SQL_Running对应的值为YES

mysql> STOP SLAVE

停止或结束复制

mysql> SHOW SLAVE STATUS

查看服务器的状态信息

mysql> CHANGE MASTER TO master_def [, master_def] ...

master_def:

      MASTER_HOST = 'host_name'

    | MASTER_USER = 'user_name'

    | MASTER_PASSWORD = 'password'

    | MASTER_PORT = port_num

    | MASTER_CONNECT_RETRY = count

    | MASTER_LOG_FILE = 'master_log_name'

    | MASTER_LOG_POS = master_log_pos

    | RELAY_LOG_FILE = 'relay_log_name'

    | RELAY_LOG_POS = relay_log_pos

    | MASTER_SSL = {0|1}

    | MASTER_SSL_CA = 'ca_file_name'

    | MASTER_SSL_CAPATH = 'ca_directory_name'

    | MASTER_SSL_CERT = 'cert_file_name'

    | MASTER_SSL_KEY = 'key_file_name'

    | MASTER_SSL_CIPHER = 'cipher_list'

 

CHANGE MASTER TO更新了副服务器与主服务器连接和通讯的参数，即更新了master.info
和relay-log.info两个文件中的内容。如果不需要改变已有参数则不需要指定相对于的
参数名。在更改参数值之前需调用STOP SLAVE停止副服务器上的IO_THREAD和SQL_THREAD进程。

2.3   数据恢复

mysql> LOAD DATA FROM MASTER

该语句在MySQL4.0以上版本使用，这是一种副服务器从主服务器获得数据的方法。但
使用这个命令有些限制，目前MySQL建议只能在主服务器数据库很小以及主服务器对长
时间不存在问题的情况设置了读锁的的情况下使用。此外只有MyISAM类型的表可以使
用这个命令复制数据。

 

4      附录

5.1   选项配置文件

Windows MySQL将在Windows安装目录(比如C:\WINNT或C:\WINDOWS)中的名为my.cnf的选项文件和
C:\my.cnf中查找启动选项。作为代替，my.cnf可以采用文件名为my.ini,位于Windows安装目录中。

Unix/Linux MySQL将检查/etc/my.cnf文件，如果没有MySQL使用默认启动选项。也可以手工创建该文件。

5.2   数据目录

Windows MySQL的安装目录下的data目录，比如C:\mysql\data目录

Unix/Linux 如果是源码方式安装(tar.gz)MySQL，数据目录在/usr/loca/mysql/data目录；
如果是用RPM包安装的，数据目录在/var/lib/mysql目录

5.3   从MySQL数据库和表中倒出结构和数据

shell> mysqldump [OPTIONS] database [tables]

如果你不给定任何表，整个数据库将被倾倒。 通过执行mysqldump --help，你能得到你mysqldump的
版本支持的选项表。 

注意，如果你运行mysqldump没有--quick或--opt选项，mysqldump将在倾倒结果前装载整个结果集到
内存中，如果你正在倾倒一个大的数据库，这将可能是一个问题。 

mysqldump支持下列选项(常用的)： 

--add-locks 

在每个表倾倒之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。 

-F, --flush-logs 

在开始倾倒前，洗掉在MySQL服务器中的日志文件。 

-f, --force, 

即使我们在一个表倾倒期间得到一个SQL错误，继续。 

-h, --host=.. 

从命名的主机上的MySQL服务器倾倒数据。缺省主机是localhost。 

-l, --lock-tables. 

为开始倾倒锁定所有表。 

-t, --no-create-info 

不写入表创建信息(CREATE TABLE语句） 

-d, --no-data 

不写入表的任何行信息。如果你只想得到一个表的结构的倾倒，这是很有用的！ 

--opt 

同--quick --add-drop-table --add-locks --extended-insert --lock-tables。应该给你为
读入一个MySQL服务器的尽可能最快的倾倒。 

-pyour_pass, --password[=your_pass] 

与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口令。 

-P port_num, --port=port_num 

与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用 Unix套接字。） 

-u user_name, --user=user_name 

与服务器连接时，MySQL使用的用户名。缺省值是root。 

 

最常见的mysqldump使用可能制作整个数据库的一个备份： 

mysqldump --opt database > backup-file.sql