MySQL数据库的维护、备份和复制

1 数据库预防性维护工作的基本原则

数据库系统的预防性维护工作的基本原则

• 激活MySQL服务器的自动恢复能力。
• 有计划地安排一些预防性的维护工作，定期对数据表进行检查。
• 指定一份数据库备份计划。

遇到数据表损坏会数据丢失问题，请按以下原则处理。

• 检查数据表，尽可能对发现的问题进行修复。
• 如果对你的数据表进行的检查和修复仍不能是你的MySQL服务器恢复运行，就要用你的备份和二进制日志来进行数据恢复。
• 在MySQL服务器启动时，事务性存储引擎将自动进行数据表检查和恢复处理。在次基础上，MySQL管理员还可以激活MyISAM存储引擎的数据表自动恢复功能。
• 使用mysqldump和mysqlhotcopy程序为数据库制作备份。
• 可以使用check table 和 repair table等sql语句让MySQL服务器根据需要执行几种数据表的维护操作。mysqlcheck工具程序为这些sql语句提供了一个命令行操作界面。myisamchk工具程序（不通过MySQL服务器，可以直接操作数据表文件的独立程序）也能对数据表进行检查并对它们进行多种修复。

2 在MySQL服务器运行时维护数据库

一般是在服务器运行时进行数据完整性检查或修复（check table、repaire table、mysqlcheck程序），也可以使用myisamchk程序单独访问数据表文件，这时，要阻止MySQL服务器修改数据表，否则产生冲突。

还有一些场景需要阻止MySQL服务器访问数据表：

• 在使用myisampack程序压缩某个MyISAM数据表的时候。
• 在重新安装某个MyISAM数据表的数据文件和索引文件的时候。
• 在重新安置一个数据库的时候。
• 制作备份数据的时候。
• 恢复数据的时候。

如果不想关停服务器，又不像让运行着的服务器与你正在使用的外部程序相互干扰，就必须使用某种锁定机制来与服务器进行协调。

• 内部锁定机制

        防止来自不同客户端程序的查询请求相互混杂和干扰。

• 外部锁定机制

        防止其他程序修改它正在使用的数据表文件。本身存在不足，不在讨论它。

2.1 以只读方式或读/写方式锁定一个或多个数据表

两个会话，一个会话发出lock tables语句锁定鼠标（不能中途退出连接，服务器自动解除刚刚设置的锁定），另一个会话操作数据，然后回到第一个会话释放（unlock tables）刚才锁定的数据表。flush tables语句通知MySQL服务器把给定的数据表挂起的改动写入硬盘。

使用哪种方式锁定表取决于是读操作还是读/写操作：

• 如果只是检查或复制某些文件，以只读方式修改他们就足够了。
• 如果需要修改某些文件，以读/写方式锁定它们。

这里讨论的利用内部锁定机制锁定给定数据表的办法仅使用于那些把每个数据表分别表示为一组相关文件的存储引擎（比如MyISAM），不适用与吧多个数据表的新新仙尼保存在一个给定文件的存储引擎。（比如MyISAMhe Falcon存储引擎）

2.1.1 以只读方式锁定一个数据表

MySQL服务器将替你阻止其它客户陈程序修改正在读取的数据，但允许其它客户程序从中读取数据。

示例：

（1）窗口A，锁表：

（2）窗口B，处理数据：

（3）窗口A，解锁：

2.1.2 以读/写方式锁定一个数据表

完全阻止MySQL服务器在你对数据进行修改的时候访问它。

示例：

（1）窗口A:锁表

（2）窗口B：操作数据

（3）窗口A:解锁

 

2.2 以只读方式锁定所有的数据库

要想阻止客户程序修改任何一个数据表，最简单的方法莫过于以只读的方式锁定所有数据库里的所有数据表。可以这样锁定：

flush tables with read lock;

set global read_only = on;

flush语句将申请一个全局性的读操作锁，set语句将在其它客户释放它们锁定的所有数据表并完成正在执行的事务之后才开始执行，在此之前一直出于被阻塞状态。在set语句执行完毕之后，其它客户将只能进行读操作而不能进行写操作。

可以使用下面这些语句解锁锁定：

set global read_only = off;

unlock tables;

3 预防性维护

保证数据库完好无损的一些基本策略：

• 激活MySQL服务器的自动恢复能力。
• 有计划地安排一些预防性的维护工作，定期对数据表进行检查。
• 指定一份数据库备份计划。

3.1 激活MySQL服务器的自动恢复能力

这是数据库完整性维护工作的第一道防线。事务型存储引擎的自动恢复是默认开启的，另一种需要设置。

启动过程能自动完成许多必要的纠错和修复工作。

• 如果启用了InnoDB存储引擎，它能自动查出和纠正很多问题。比如说，对于记载在“重做日志”（redo log）里的事务（那些已被提交但没来得及写入数据表的事务），它会重做（redo）一遍；对于记载在“撤销日志”（undo log）里的事务（在崩溃发生时正在执行的未提交的事务），它会进行回滚。如果自动恢复工作因为某个无法回复的问题而失败了，MySQL服务器会再“错误日志”里写出一条消息后退出执行。可以尝试手动恢复。
• Falcon存储引擎也具备类似的能力。它将根据它的serial日志去尝试进行自动恢复。
• 启用MyISAM数据表的自动恢复能力的方法是使用--myisam-recover=level选项来穷MySQL服务器。level可以多个以逗号隔开：backup（如果自动恢复工作需要修改某个数据表，先为它创建一个备份）、force（强行恢复，哪怕会因此而丢失一个以上的数据行）、quick（快速恢复）、default（普通回复，跟设置为空一样）

3.2 定期进行预防性维护

在linux系统上，可以在crontab文件上创建一个cron作业，例如：

/ect/crontab文件里编辑：

15 15 * * * /usr/bin/mysqlcheck --all-databases --check-only-changed --silent

--all-databases将使mysqlcheck程序去检出所有数据库里的所有数据表。

--check-only-changed高数mysqlcheck程序跳过已在最后一次成功检查后来未修改过的所有数据表。

--silent的作用是阻止输出——除非在数据表里发现了错误。

注意：当一个数据表被检查的时候，它将不能被更新。因此，自动化的维护策略不一定适用于那些需要频繁更新的大数据表。

4 自制作数据库备份

数据备份可以用来恢复或者复制服务器。

数据库备份按照它们的格式可以分为两大类：

• 文本格式备份

        这类备份是通过使用mysqldump程序把数据表内容写到转储文件里而得到的，其内容主要由create table和insert两种sql语句构成，把转储文件重新加载到MySQL服务器上就可以恢复相关的数据表。

• 二进制备份

        这类备份是通过直接复制那些包含着数据表内容的文件而得到的。制作这类备份的具体办法有许多种，诸如mysqlhotcopy、cp、tar、rsync之类的程序都可以用来为数据库制作二进制备份。

想让数据库恢复工作达到最佳效果，必须遵守以下几个原则：

• 定期对数据库进行备份。
• 启用MySQL服务器的二进制日志。恢复数据库是，先用备份文件把思考恢复到当初对它进行备份时的状态，然后运行二进制日志里的内容将重建数据表在备份以后发生的修改。
• 给备份文件起的名字既要有规律，又要有意义。
• 不要把备份文件和数据库放在同一个文件系统上。
• 定期使用文件系统的备份功能来备份数据库的备份文件。
• 定期对备份文件进行失效处理以防止它们填满你的硬盘。

接下来介绍几种具体的备份方法：

4.1 用mysqldump程序制作文件备份

备份：

mysqldump opitons  db_name table_name > dump_name

加载：

mysql opitons db_name < dump_name

常用的options：

• 人们通常在mysqldump命令行上给出一个数据库名，在它后面再选择性地给出几个数据表名。如果你想一次转储多个数据库，就要使用--databases选项。这将把你在命令行上给出的名字全部解释为数据库名。如果你想转储所有的数据库，可以使用--databases或--all-databases。如果打算把转储文件加载到另一个服务器上去，请谨慎使用该选项，这个选项将会转储mysql数据库里的各个权限表。
• 默认情况下，建表语句（create table）和内容语句（insert）都会转储。如果指向转储其中之一,可以使用--no-create-info或--no-data
• --opt可以对转储过程进行优化，它是默认启用的。如果要禁止，可以使用--skip-opt。它将启用--extended-insert选项，insert语句一次插入多个数据行。如果想一次插入单行，可以使用--skip-extended-insert选项。
• --flush-logs和--lock-all-tables选项组合能在数据库检查工作中帮上大忙。--lock-all-tables选项用来申请一个全局级读操作锁，--flush-logs选项用来关闭再重新打开日志文件。如果启用了二进制日志功能，刷新日志将创建一个新的二进制日志文件来记载此后发生的数据修改。这样你就能把你的日志同步到你进行备份时的时间。
• 在转储InnoDB或Falcon数据表的时候，最好是用--single-transaction选项把转储操作放在一个事务里执行，这可以确保得到一个稳定的备份。
• 如果数据库包含着存储程序，触发器和事件，可以用--routines、--triggers和--events明确地把他们包括到转储输出里，这几个选项还有一个--skip形式。默认只有触发器会被转储。

4.2 制作二进制数据库备份

要备份一个数据库或数据表，除了mysqldump，另一种办法是直接复制数据表文件。客户已使用常规的文件系统工具（如cp，tar或rsync），或者时候用专门为此开发大的工具（比如mysqlhotcopy或InnoDB Hot Backup）.直接复制的时候，需要注意两点：

• 一定要确保没有人子啊使用那张数据表。
• 在直接复制某个数据表时，一定要把恢复这个数据表所需要的所有文件复制下来。如MyISAM复制表的.frm、.MYD和.MYI文件，InnoDB表复制.frm，表空间文件和日志文件。

4.2.1 制作一个完整的二进制备份

存放着数据表内容的所有文件；

供特定存储引擎时候用的各种日志文件；

二进制日志文件；

如果是在复制机制中的从服务器上制作备份，应该把中继日志（relay log）文件以及master.info和relay-log.info文件复制下来，并从临时目录看一下有没有名字为SQL_LOAD-xxx形式的文件（load data需要）。这个临时目录默认为tmpdir系统变量的值，可以在启动服务器时由--slave-load-tmpdir选项指定。

4.2.2 制作一个部分的二进制备份

跟制作完整的二进制备份类似，只是复制时要指定某些文件而已。

4.2.3 用mysqlhotcopy工具制作备份

mysqlhotcopy工具是一个能帮我们制作数据库备份的Perl DBI脚本。“hot”指的是备份工作将在MySQL保持运行时进行。

有如下优点：

• 比mysqldump程序更快。
• 使用方便。能完成锁定工作
• 它能刷新日志，让备份文件和日志文件的检查点保持同步。

有如下局限性：

• 必须在服务器主机上运行，并且服务器必须正在运行。
• 只能用来备份MyISAM和ARCHIVE数据表。
• 只能再Unix和NetWare系统上使用。

下面命令将在MySQL数据目录里创建一个名为db_name_copy的子目录并把mydb数据库子目录的文件复制到哪里去。

mysqlhotcopy db_name [document_path]

 

4.3 备份InnoDB或Faclcon数据表

可以使用mysqldump程序来转储，还可使用InnoDB Hot Backup工具来制作InnoDB数据表的二进制文件。如果使用直接复制法制作二进制InnoDB备份，必须注意：

• InnoDB有它自己的日志文件用于事务管理。制作备份时必须正确地关掉MySQL服务器，这是为了确保InnoDB存储引擎有机会完成尚未完成的事务并正确地关闭它的日志。
• 需要复制的文件有：
  • 数据表的.frm文件。
  • 数据表的.ibd文件（如果InnoDB存储引擎被配置成给每个数据表分别创建一个表空间文件的话）。
  • 共享表空间文件。
  • InnoDB日志文件。
  • 用来配置共享表空间的选项文件。

为Falcon数据表制作二进制备份的步骤相似，只不过复制的是Falcon的表空间文件和日志文件（以.fts、.fl1和fl3为扩展名的文件）而已。

5 把数据库复制到另一个服务器

有两种方式把数据库复制到另外一个服务器上去，第一个是把数据库备份为一个文件，再在第二台服务器上加载。第二个办法是第一个服务器备份数据的同事通过网络直接转储到另一个服务器上去。

5.1 使用备份文件来复制数据库

（1）创建一个转储文件

mysqldump --databases db_name > db_name.sql;

--databases选项将使mysqldump在备份文件里为db_name数据库增加create database if not exists 和 use 语句。

（2）把转储文件复制到远程主机。

（3）登录远程主机，加载转储文件

msyql < /path/db_name.sql;

5.2 把数据库从一个服务器复制到另一个

mysqldump --databases db_name | mysql -h host_name;

如果本地主机无法访问远程MySQL服务器但是能够通过登录远程主机的办法去访问它，可以通过ssh来远程调用mysql程序：

msyqldump --databases sampdb |ssh host_name mysql;

如果用的是慢速网络，可以在远程命令那边使用--compress压缩数据量

mysqldump --databases db_name | mysql --compress -h host_name;

6 数据表的检查和修复

如果怀疑某个数据表收到了破坏，就先对它进行检查，如果检查出了问题，再按以下步骤修复：

• 先试试比较快但不那么全面的修复方法，看它能否解决问题。
• 如果不管用，试试更慢但是更全面的方法。

万一数据表或者数据库已丢失或无法修复，就需要使用数据库备份和二进制日志来恢复它们。

下面是检查和修复MyISAM和InnoDB数据表的几种常用办法。

检查和修复MyISAM数据表的常用办法有以下几种：

• 发出check table 和 repair table 语句。也可以使用mysqlcheck程序，它将替你连接服务器并发出那些语句。
• 使用myisamchk程序，它将直接在数据表文件上操作。

优先考虑让MySQL服务器完成这项工作，这样不必考虑使用锁定机制来协调数据表访问冲突的问题。

检查InnoDB数据表可以使用mysqlcheck程序，如下所示：

mysqlcheck db_name table_name;

如果发现问题，可以重新加载表：

mysqldump db_name table_name > table_name.sql;

mysql db_name < table_name.sql;

 

6.1 用服务器检查和修复数据表

6.1.1 用check table语句检查数据表

可以用来检查InnoDB，MyISAM，ARCHIVE，CSV数据表，视图。

语法：

check table table_name [,table_name ...] 限定符

例如：

check table tab1, tbl2, tbl3 fast medium;

 

6.1.2 用repair table语句修复数据表

可以用来修复MyISAM，ARCHIVE，CSV数据表。

用法：

repair table table_name [,table_name ...] 限定符；

例如：

repair table tbl1, tbl2, tbl3 quick;

6.1.3 使用mysqlcheck程序检查和修复数据表

mysqlcheck是check table和repair table语句的命令行界面。这个程序将连接服务器并根据你给出的选项发出相应的语句。因此，mysqlcheck程序能够检查和修复的数据表与check table和repair table语句能够检查/修复的数据表是对应的。

用法：

mysqlcheck [选项] db_name [table_name1[, table_name2 ...]]

例如：

mysqlcheck sampdb; -- 检查该库的所有表

mysqlcheck sampdb president member; --检查库后面的几张表

mysqlcheck --databases sampdb test; -- 检查--databases选项后面的几个数据库里的表

mysqlcheck --all-databases; -- 检查所有库里的所有表

mysql默认进行中级检查，但是我们可以自定义检查类型，下面就是mysqlcheck程序选项和与之对应的check table语句选项（注意：check table语句所有选项都是用与MyISAM数据表，不一定适用于其他的存储引擎）

 

mysqlcheck程序还可以修复数据表，但仅限于MyISAM数据表。下面列出了一些mysqlcheck程序选项和与之对应的repair table语句选项。（注意：repair table语句所有选项都是用与MyISAM数据表，不一定适用于其他的存储引擎）

 

6.2 用myisamchk程序检查和修复数据表

使用myisamchk程序检查和修复数据表时建议先停止服务器，避免冲突。一般切换到某个数据库的目录文件下操作：

myisamchk options tbl_name...;

tbl_name参数既可以是一个数据表的名字，也可以是一个数据表的索引文件的名字。下面两条语句时等价的。

myisamchk member;

myisamchk member.MYI;

可以使用通配符

myisamchk options *.MYI;

可以直接操作原始数据表的复制文件。

6.2.1 用myisamchk程序检查数据表

普通检查：

myisamchk tbl_name;

等价于

myisamchk --check tbl_name;

中级和高级检查：

myisamchk --medium-check tbl_name;

myisamchk --extend-check tbl_name;

 

6.2.2 用myisamchk程序修复数据表

建议：尝试用快而不全的方式修复，不能解决问题，就用慢而全的方式修复。修复前备份数据表以防万一。

（1）加上--recover，还可以加上--quick，使用索引文件来修复表，不适用数据文件。

myisamchk --recover --quick tbl_name;

（2）如果问题没有解决，去掉--quick在执行

myisamchk --recover tbl_name;

（3）如果还没解决，试试--safe-recover修复模。

myisamchk --safe-recover tbl_name;

如果出现“Can't create temp file:file_name ”的消息，可能是文艺上次修复失败时创建的临时文件没有被删除，可以加上--force选项再执行。

如果上面的三个步骤都没有成功，原因往往是索引文件丢失或无法修复，也有可能是.frm文件丢失。如果这样，更新受影响的文件再执行上面三个步骤。

如果t.frm完好无损，以下步骤可以为数据表t重新生成索引文件：

（1）切换到受损数据表所在的数据库子目录。

（2）把数据表的数据文件t.MYD移动到一个安全的地方。

（3）执行下面这条语句，使用truncate table t 声场一个新的数据文件和新的索引文件。

（4）退出mysql程序，把原来的数据文件替换刚刚创建的新的空白的数据文件。虽然现在数据文件和新的索引文件不匹配，但索引文件有一个合法的内部结构，MySQL服务器

可以根据数据文件和数据表格式文件的内容解释并重新创建。

（5）按照前面给出的步骤再次尝试修复数据表。

如果没有可用的.frm文件，就要从备份里把.frm文件恢复过来，再按照上诉步骤去修复该数据表。如果.frm已经丢失并且没有备份，但是记得create table语句，可以根据下面的步骤来修复：

（1）切换到受损数据表所在的数据库子目录。

（2）把数据表的数据文件t.MYD移动到一个安全的地方。如果要使用索引文件t.MYI，应该把它也移到安全的地方。

（3）运行mysql程序并发出create table语句重新创建数据表。将创建出新的.frm、.MYD、.MYI文件。

（4）退出mysql程序，把原来的数据文件替换刚刚创建的新的空白的数据文件。如果之前也移动了索引文件也把索引文件移过来。

（5）按照前面给出的步骤再次尝试修复数据表。

6.2.3 让myisamchk程序运营得更快

可以通过设置几个变量，让myisamchk程在运行时使用更多的内存来加快这个过程。

语法：  myisamchk --var_name=value

修复数据表时，--sort-buffer-size适用于--recover选项，--key-buffer-size适用于--safe-recover修复选项和--extend-check检查选项

 

7 使用备份进行数据恢复

数据恢复工作需要两个信息来源：备份文件和二进制文件。备份文件既可以是mysqldump程序生成的转储文件，也可以是采用某种二进制备份方法制作出来的副本。

备份文件能把数据恢复到当初制作备份时的状态。二进制日志文件里记载着制作备份以后执行过的修改数据库的语句。mysqlbinlog程序能够把这些日志文件转换回文本形式的SQL语句，只需在mysql程序加以执行，就可以让那些发生在制作备份之后，出现问题之前的数据修改重现在各有关数据表里。

 

7.1 恢复整个数据库

（1）备份将要恢复的数据库以防万一；

（2）使用最新的备份文件重新加载打算恢复的数据库。

• 如果备份文件是mysqldump生成的，按顺序加载。如果要恢复的数据库包括mysql数据库（其内容是各种权限数据表），并且你打算使用备份文件来恢复那些数据表的话，需使用--skip-grant-tables选项才能重新加载他们，否则提示“无法找到权限数据表”。也可以使用--skip-networking选项让MySQL服务器拒绝所有的远程连接。等恢复好厚，再重启它，就可以使用正常的权限数据表和监听网络接口了。
• 如果使用的是二进制备份（比如说，用mysqlhotcopy、tar或cp程序制作的备份），先关服务器，再复制备份文件到原来的数据目录下，然后重新启动服务器。

（3）根据二进制日志重新执行在备份后对数据进行修改的sql语句，稍微会讲解。

 

7.2 恢复数据表

恢复数据表比恢复数据库麻烦一些，用mysqldump程序生成的备份文件，直接加载关于要恢复的数据表的语句即可。

接下来从二进制日志文件中提取就稍微有点麻烦。mysqlbinlog程序有一个--database选项可以让该程序只输出与给定的数据表有关的语句，但是没有针对表的选项。这是，一个比较实用的方法就是，先回复整个数据库，在从中提取数据表。步骤如下：

（1）先创建一个空白的数据库，用于存放将要恢复的数据表。然后加载备份文件和二进制文件完成恢复，其中有两个细节要注意：

• mysqldump程序生成的转储文件如果有use语句，需要修改或删除里面的use语句。
• mysqlbinlog的输出文件如果有use语句，先将mysqlbinlog的输出保存为一个临时文件，在编辑那些use语句，然后再把临时文件用作mysql程序输入。

（2）在新创建的数据库里，用mysqldump程序转储你真正想要恢复的那个数据表。

（3）删除原来的数据表，把转储文件加载到原来的那个数据库重新创建那个数据表。如果是MyISAM数据表，还可以直接把数据表文件从第二个数据库子目录直接复制到原来的数据库子目录。

 

7.3 重新执行二进制日志文件里的语句

如果要执行所有二进制日志文件的内容，先切换到二进制日志文件所在的子目录

mysqlbinlog options binlog_file_name.[0-9]* | mysql;

也可以先输入到一份文件中：

mysqlbinlog options binlog_file_name.[0-9]* > file_name;

vim file_name;

msyql < file_name;

一些可用的options：

--database 提前指定的数据的语句；

--start-datetime指定开始时间，日期格式可以为：20191107220000护着"2019-11-07 22:00:00"

--stop-datetime指定截止时间。

 

8 设置复制服务器

8.1 复制机制的工作原理

MySQL数据库系统中的复制机制遵循以下原则。

• 必须给主从服务器各自分配一个唯一的复制ID。
• 一个主服务器可以有多个从服务器，一个从服务器可以设置为另一个从服务器的主服务器（服务器链）。也可以把多个主服务器复制为同一个从服务器，但是比较麻烦。
• 复制刚开始时，主从服务器必须完全同步。
• 主服务需要开启二进制日志功能，用作主从服务器直接传输各种修改动作的媒介。这种修改成为“事件”。
• 每个从服务器必须具备让它连接主服务器并请求修改的权限。当从服务器连接主服务器时，会告诉主服务器上次进展的二进制的位置（复制坐标）：一个二进制日志文件的命字和改文件里的一个位置。主服务器将从其二进制日志里的给定的坐标位置开始把在那以后发生的世界传送给从服务器。当从服务器读取完主服务器的所有事件时，它将暂停并进入等待状态。
• 当主服务器上发生新的数据修改操作时，它将把它们记入它的二进制日志以便向从服务器传输。
• 主服务器对从服务器的处理和它对待普通客户程序差不多，而每一个连接到主服务器的从服务器都将占用max_connnections系统变量所设置的系统最大连接个数。
• 在从服务器端，服务器使用两个线程来完成复制任务。I/O线程负责接收来自主服务器的待处理事件并把它们写入从服务器的中继日志（relay log）。SQL线程负责从中继日志读出事件并执行它们。中继日志相当于I/O线程向SQL线程传输数据修改信息的通信纽带。在处理完一个中继日志之后，从服务器将自动删除它。I/O线程和SQL线程的操作是互不影响的，他们可以分别启动和关停。
• 主从服务器要在二进制日志的格式和功能上（比如存储引擎）兼容。

复制MySQL时建议：

• 尽量选用最新版本。
• 尽量让主服务器和从服务器使用相同格式的二进制日志。如果主从服务器版本不匹配，应该主服务器的版本低于从服务器。

8.2 建立主从复制关系

步骤：

• 主从服务器在选项文件里分配唯一的ID值（1-2^32-1），主服务器开启二进制日志功能。重启生效。

[mysqld]

server_id=master_server_id

log-bin=binlog_name

 

[mysqld]

server_id=slave_server_id

 

• 在主服务器上，创建一个账户供从服务器连接主服务器并请求修改信息：

create user 'slave_user'@'slave_host' identified by 'slave_pass';

grant replication slave on *.* to 'slave_user'@'slave_host';

• 连接到主服务器并通过执行show master status语句确定当前的复制坐标。

注意：在主服务器上，在从确定其复制坐标到制作出将被传输到从服务器的初始复制的这段时间里，一定要保证主服务器上的数据库里的数据没有发生任何修改。

• 在从服务器上将被复制的数据库建立一份完备的副本。把主服务器上的数据库复制到从服务器以完成主从服务器之间最初的同步。可以制作备份加载到从服务器上，也可以通过网络复制。如果主服务上没有任何数据库或数据表，可以忽略。
• 连接到从服务器并使用change master语句来配置它，这包括把用来连接主服务器的参数和初始复制坐标告诉从服务器。

change master to

    master_host='master_host',（unix系统中如果主从是同一台主机，不要使用localhost，改用127.0.0.1）

    master_port，（默认端口可忽略这项）

    master_user='slave_user',

    master_password='slave_pass',

    master_log_file='log_file_name',

    master_log_pos=log_file_pos;

• 让从服务器开始复制。

start slave;

可以使用show slave status查询工作状态：

    

• 如果不想复制mysql表里的权限信息到从服务器，需要做两件事。

    （1）复制的时候，不要把mysql数据库也复制到从服务器上。也可以在复制之前先备份从服务器的mysql数据库，等传输工作完成后在恢复它。

    （2）在从服务器的选项文件里加上以下代码，让它不要执行来自主服务器的对mysql数据库的任何修改：

[mysqld]

replicate-ignore-db=mysql

如果想让从服务器忽略多个数据库，需要多次使用repliaction_ignore_db选项。

还有一个办法是在主服务器端使用--biglog-ignore-db选项排除数据库。优点是可以减少从主服务器传输到从服务器的信息量，缺点是从服务器上的二进制日志文件将不包含那些被排除在外的数据库的任何信息，而这些信息在恢复工作中很重要。因此，在从服务器上排除数据库更好。

• 建立主从后，可以进行监控和管理：
  • show table status语句。在从服务器上查看复制机制是否在工作以及当前的复制坐标。复制坐标可以用来判断主服务器上的哪几个二进制日志文件已经不再被用到了。
  • purge master logs to 'log_name'语句。在主服务上对二进制日志文件进行失效处理。在每一个从服务器上都通过show slave status语句确定了哪些日志文件不会再用后，你可以在主服务上用这条语句把那些二进制日志文件删除掉。
  • stop slave和start slave语句。用来挂起和重新开始从服务器上的复制活动。可以在末尾加上IO_THREAD或SQL_THREAD命令分别挂起或重新开启这两个线程。

类似于二进制日志文件，从服务器将把中继日志生成一系列按数字编号的文件，并且也会生成中继日志索引文件，默认名分别为hostname-relay-bin.nnnnnn和hostname-relay-bin.index。可以同启动选项--relay-log和--relay-log_index加以改变。

 

8.3 使用复制机制制作备份

 

为了解决复制时数据被修改的冲突。一个好的方案是：在从服务器上备份。备份前，停止从服务器或者暂停从服务器的复制活动，备份好后再补上备份期间发生在主服务器上的数据修改。

以下是在从服务器上备份的几种策略：

• 关停服务器，备份好后按照前面给的步骤重新启动从服务器。
• 如果不打算关停从服务器，且不在从服务器上进行修改操作，可以只关闭从服务器的SQL线程（stop slave SQL_THREAD）,备份后再开启SQL线程。
• 如果不想暂停复制活动，可以先锁表，等备份完后，再释放锁。