MySQL数据库备份之复制
MySQL数据库备份之复制目录:
1.MySQL复制简介
2.MySQL复制的优势与思路
3.数据复制环境之主服务器设置
4.数据复制环境之从服务器设置
5.创建复制帐号
6.获取主服务器二进制日志信息
7.对现有数据库进行快照备份
8.配置从服务器连接主服务器进行数据复制
9.数据同步验证
1. MySQL复制
使用MySQL复制功能可以将主服务器上的数据复制到多台从服务器上。默认情况下复制是异步传输方式，从服务器不需要总是连接主服务器去更新数据。也就是说数据更新可以在远距离连接的情况下进行，甚至是在使用拨号网络的临时连接环境下也可以进行。根据自定义设置，我们可以对所有数据库或部分数据库甚至是部分数据表进行复制。通过主从复制，在企业级应用环境中就不必再担心数据库的单点故障，当一台服务器宕机时其他服务器一样可以提供非常稳定可靠的数据服务。
2. MySQL复制的优势：
[*] 高性能：通过将请求分配给多台不同的从服务器提高性能与速度。在这种环境中，所有对数据库的写操作必须提供给主服务器，但读操作可以被平均地分配给多台从服务器。
[*] 数据安全：数据是从主服务器复制到从服务器的，而且从服务器可以随时暂停复制，这样我们就实现数据备份与还原。
[*] 远程数据分享：如果企业拥有多处位于其他地理位置的分公司，而这些分公司希望共享总部的数据库资源，就可以使用复制实现数据的共享，而且分公司不必时时连接主服务器，可以仅在需要时进行复制。
MySQL复制有两种核心的格式，一种是基于SQL语句的复制（SBR：Statement Based Replication），另一种是基于行的复制（RBR：Row BasedReplication），另外有时也可以使用混合模式复制。MySQL 5.6默认采用的是基于语句的复制。
MySQL服务器之间的复制是基于二进制日志机制。在主服务器上当MySQL实例进行写操作时会同时生成一条操作事件日志并写入二进制日志文件中，而从服务器则负责读取主服务器上的二进制日志，并在从服务器本机重新执行该事件，从而实现复制数据至本地服务器。当主服务器开启了二进制日志功能后，所有的SQL语句都将被记录至日志。而从服务器复制这些二进制日志的条目，并且根据自己的需要决定哪些语句需要重新在从服务器执行，而哪些语句不需要再执行。我们无法控制主服务器仅记录特定语句到日志文件。如果没有进行其他设置，所有主服务器中的日志事件都将在从服务器上重新执行，当然你可以配置从服务器仅执行主服务器中的一部分日志事件。因为从服务器会记录二进制日志的进度与位置（比如执行到了第几条语句），所以从服务器可以断开与主服务器之间的连接，并在重新建立连接后继续进行复制工作。
在具体操作过程中，主服务器与从服务器都需要配置一个唯一的ID编号。另外，所有的从服务器还必须设置主服务器的主机名、日志文件名、文件位置等参数。
一步一步操作MySQL复制
本节我们将演示如何一步一步实现MySQL服务器之间的数据复制（演示环步骤中命令提示符为master则代表指令在MySQL主服务器上运行，提示符为slave1则代表指令在MySQL从服务器上运行）。整个过程一般分为以下几个步骤：
[*] 在主服务器上开启二进制日志功能，设置唯一的服务器ID编号。这些设置需要重启MySQL服务。
[*] 在所有从服务器上设置唯一的服务器ID编号，这些设置需要重启MySQL服务。
[*] 在主服务器上为不用的从服务器创建可以读取主服务器日志文件的账户，或使用相同的统一账户。
[*] 在进行数据复制之前，你还需要记录主服务器上二进制日志的位置标记。
3.数据复制环境之主服务器设置
在实际的生产环境中，可能在我们还没有部署数据复制前，数据库中就已经存在大量数据，所以，这里我们事先创建一个测试用数据库及数据表，用来演示如何对已经存在的数据进行数据同步备份。
# mysql -u root -p
mysql> create database hr;
mysql> use hr;
mysql> create table employees(
-> employee_id INT NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> e_mail varchar(50),
-> PRIMARY KEY(employee_id));
mysql> INSERT INTO employees values
->(1,'TOM','tom@example.com'),
->(2,'Jerry','jerry@example.com');
mysql> exit
我们需要在主服务器上开启二进制日志并设置服务器编号，服务器唯一编号必须是1至232-1之间的整数，根据自己的实际情况进行设置。进行这些设置需要关闭MySQL数据库并编辑my.cnf或my.ini文件，并在设置段添加相应的配置选项。关于MySQL软件的安装这里不再赘述，请参考4.11.2章节内容。
# vim /etc/my.cnf
log-bin=Jacob-bin#启用二进制日志，并设置二进制日志文件前缀
server-id=254#设置服务器编号
# service mysqld restart
#service iptables stop
l注意：在配置文件中不可以使用skip-networking参数选项，否则从服务器将无法与主服务器进行连接并复制数据。
4.数据复制环境之从服务器设置
如果从服务器ID编号没有设置，或服务器ID编号与主服务器有冲突，就必须关闭MySQL服务并重新编辑配置文件，设置唯一的服务器编号，最后重启MySQL服务。如果有多台从服务器，则所有的服务器ID编号都必须是唯一的。可以考虑将服务器ID编号与服务器IP地址关联，这样ID编号同时可以唯一标识一台服务器计算机，如采用IP地址的最后一位作为MySQL服务器ID编号。
# vim /etc/my.cnf
server-id=2
# service mysqld restart
#service iptables stop
对于复制而言，MySQL从服务器上二进制日志功能是不需要开启的。但是，你也可以通过启用从服务器的二进制日志功能，实现数据备份与恢复，此外在一些更复杂的拓扑环境中，MySQL从服务器也可以扮演其他从服务器的主服务器。
5.创建复制帐号
执行数据复制时，所有的从服务器都需要使用账户与密码连接MySQL主服务器，所以在主服务器上必须存在至少一个用户账户及相应密码供从服务器连接。这个账户必须拥有REPLICATION SLAVE权限，你可以为不同的从服务器创建不同的账户与密码，也可以使用统一的账户与密码。MySQL可以使用CREATE USER语句创建用户，使用GRANT语句为账户赋权。如果该用户仅为数据库复制所使用，则该账户仅需要REPLICATION SLAVE权限即可。下面的例子将在MySQL主服务器上创建一个拥有复制权限的slave_cp账户，该账户可以从example.com域内的任何主机连接主服务器，密码为SlaveAdmin。
# mysql -u root -p
mysql> CREATE USER 'slave_cp'@'%.example.com'>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_cp'@'%.example.com';
mysql> exit
6.获取主服务器二进制日志信息
在进行主从数据复制之前我们了解一些主服务器的二进制日志文件的基本信息，这些信息在对从服务器的设置中需要用到，这些信息包括主服务器二进制文件名称及当前日志记录位置，这样从服务器就可以知道从哪里开始进行复制操作。我们可以使用如下操作查看主服务器二进制日志数据信息：
# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+--------------------------------+------------+---------------------+-------------------------+--------------------------+
| File|Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------------+------------+---------------------+-------------------------+--------------------------+
| jacob-log.000001|1276||||
+--------------------------------+------------+---------------------+--------------------------+-------------------------+
mysql> UNLOCK TABLES;
其中，File列显示的是二进制日志文件名，Position为当前日志记录位置。
FLUSH TABLES WITH READ LOCK命令的作用是对所有数据库的所有表执行只读锁定，只读锁定后所有数据库的写操作将被拒绝，但读操作可以继续。执行锁定可以防止在查看二进制日志信息的同时有人对数据进行修改操作，最后使用UNLOCK TABLES语句对全局锁执行结束操作。
7.对现有数据库进行快照备份
如果在使用二进制日志进行数据复制以前，MySQL数据库系统中已经存在大量数据资源，对这些资料进行数据备份的一种方法使用使用mysqldump工具，在主服务器上使用该工具对数据备份后即可在从服务器上进行数据还原操作。当希望的数据达到主从一致后，就可以使用数据复制功能进行自动从此同步操作。具体操作如下（作者的环境中主服务器IP地址为172.16.0.254，从服务器IP地址为172.16.0.1），实际生产环境中需要根据自己的需要有选择的对数据库进行备份与还原：
# mysqldump --all-databases --lock-all-tables > /tmp/dbdump.sql
# scp /tmp/dbdump.sql 172.16.0.1:/tmp/
# mysql -u root -pCHANGE MASTER TO
-> MASTER_HOST='172.16.0.155',
-> MASTER_USER='data_cp',
-> MASTER_PASSWORD='SlaveAdmin',
-> MASTER_LOG_FILE='jacob-log.000001',
-> MASTER_LOG_POS=1351;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
9.数据同步验证
所有主从均设置完毕后，我们可以通过在主服务上创建新的数据资料，然后在从服务器上查看，所有数据将自动同步。
# mysql -u root -p
mysql> create database test2;
mysql> use test2;
mysql> create table t_table(
->name char(20),
-> age int,
-> note varchar(50));
mysql> INSERT INTO t_table values
-> ('linda',23, 'Beijing'),
-> ('jerry',33, 'shanghai'),;
mysql> exit
# mysql -u root -p
mysql> select * from test2.t_table;
mysql> exit
页:
[1]