MySQL数据库备份之复制

MySQL数据库备份之复制目录:1.MySQL复制简介2.MySQL复制的优势与思路3.数据复制环境之主服务器设置4.数据复制环境之从服务器设置5.创建复制帐号6.获取主服务器二

3.数据复制环境之主服务器设置

在实际的生产环境中，可能在我们还没有部署数据复制前，数据库中就已经存在大量数据，所以，这里我们事先创建一个测试用数据库及数据表，用来演示如何对已经存在的数据进行数据同步备份。

[root@master ~]# 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文件，并在[mysqld]设置段添加相应的配置选项。关于MySQL软件的安装这里不再赘述，请参考4.11.2章节内容。

[root@master ~]# vim /etc/my.cnf

[mysqld]

log-bin=Jacob-bin#启用二进制日志，并设置二进制日志文件前缀

server-id=254#设置服务器编号

[root@master ~]# service mysqld restart

[root@master ~]# service iptables stop

l注意：在配置文件中不可以使用skip-networking参数选项，否则从服务器将无法与主服务器进行连接并复制数据。

4.数据复制环境之从服务器设置

如果从服务器ID编号没有设置，或服务器ID编号与主服务器有冲突，就必须关闭MySQL服务并重新编辑配置文件，设置唯一的服务器编号，最后重启MySQL服务。如果有多台从服务器，则所有的服务器ID编号都必须是唯一的。可以考虑将服务器ID编号与服务器IP地址关联，这样ID编号同时可以唯一标识一台服务器计算机，如采用IP地址的最后一位作为MySQL服务器ID编号。

[root@slave1 ~]# vim /etc/my.cnf

[mysqld]

server-id=2

[root@slave1 ~]# service mysqld restart

[root@slave1 ~]# service iptables stop

对于复制而言，MySQL从服务器上二进制日志功能是不需要开启的。但是，你也可以通过启用从服务器的二进制日志功能，实现数据备份与恢复，此外在一些更复杂的拓扑环境中，MySQL从服务器也可以扮演其他从服务器的主服务器。

5.创建复制帐号

执行数据复制时，所有的从服务器都需要使用账户与密码连接MySQL主服务器，所以在主服务器上必须存在至少一个用户账户及相应密码供从服务器连接。这个账户必须拥有REPLICATION SLAVE权限，你可以为不同的从服务器创建不同的账户与密码，也可以使用统一的账户与密码。MySQL可以使用CREATE USER语句创建用户，使用GRANT语句为账户赋权。如果该用户仅为数据库复制所使用，则该账户仅需要REPLICATION SLAVE权限即可。下面的例子将在MySQL主服务器上创建一个拥有复制权限的slave_cp账户，该账户可以从example.com域内的任何主机连接主服务器，密码为SlaveAdmin。

[root@master ~]# mysql -u root -p mysql> CREATE USER 'slave_cp'@'%.example.com' IDENTIFIED BY 'SlaveAdmin'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_cp'@'%.example.com'; mysql> exit

6.获取主服务器二进制日志信息

在进行主从数据复制之前我们了解一些主服务器的二进制日志文件的基本信息，这些信息在对从服务器的设置中需要用到，这些信息包括主服务器二进制文件名称及当前日志记录位置，这样从服务器就可以知道从哪里开始进行复制操作。我们可以使用如下操作查看主服务器二进制日志数据信息：

[root@master ~]# 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），实际生产环境中需要根据自己的需要有选择的对数据库进行备份与还原：

[root@master ~]# mysqldump --all-databases --lock-all-tables > /tmp/dbdump.sql [root@master ~]# scp /tmp/dbdump.sql 172.16.0.1:/tmp/ [root@slave1 ~]# mysql -u root -p 8.配置从服务器连接主服务器进行数据复制