MySQL多实例数据库的备份与恢复

有时为了提高数据库的使用效率，会在一台机器上创建多个mysql实例的情况，每个实例对应一个单独的端口号，然后根据业务功能对不同的实例库来操作数据。

在说明多实例数据库的备份与恢复之前，先来简单介绍下一般(单实例)数据库的备份与恢复：

01.源主机上利用mysqldump命令来导出数据，如

mysqldump –uusername –ppassword --all-databases > allDB.sql

将本机上的所有数据库导入allDB.sql文件中，其中username和password为数据库连接的用户名和密码

02.目标主机上安装msyql应用，并开启mysql服务

03.在目标主机上执行导入操作，如

mysql –uusername –ppassword < allDB.sql

04.补充说明

a.利用第三方客户端工具，如SQLyog、Navicat等，也可以进行如上数据库的备份

与恢复操作

b.Mysql服务的默认端口号为3306，若要修改端口号，需先（mysqladmin –uroot

–ppassword shutdown）停止mysql服务，再编辑/etc/f的port={$port}，然

后再（mysqld_safe &）开启服务就可以了

下面说说多实例数据库的备份与恢复过程，大致步骤跟上面说的一般数据库的备份与恢复差不多，不同的是需要依次启动多个mysql实例

01.源主机上对mysql每个实例进行mysqldump的导出操作，其基本命令为

mysqldump –S /tmp/mysql.sock –uusername –ppassword --all-databases > mysql.sql

其中，/tmp/mysql.sock为对应实例的套接字，如下图为每个实例对应的套接字

由于源主机上一共有10个实例（分别对应端口3306~3315），当时请教了运维，然后运维的冯晓给编写了个批量执行备份的脚本（后面提及）

02.源主机导出多实例配置文件/etc/f，这个是mysql的配置文件，其中包含了mysql

多实例的应用配置

03.目标主机上安装msyql应用，并开启mysql服务

04.将从源主机上导出的f文件导入并替换目标主机的/etc/f文件（最好先将

目标主机的f文件备份后再将源主机的f导入进来）

05.将源主机的data目录（一般是在mysql安装目录下的var/）导入到目标主机的data

目录，如下图

06.逐个启动mysql实例：mysqld_multi –config-file=/etc/f start 2

将start 2分别改为start 3、start 4、…、start 10，这些实例编号在f中配置

07.利用mysqld_multi report查看哪些实例已经成功开启，如下图

当所有实例都成功启动，即可看到/tmp/下如步骤01中的套接字截图

08.目标主机上逐个导入备份的SQL文件，如mysql -S /tmp/mysql.sock -uroot –pPwd

< mysql_3306.sql

其中，mysql_3306.sql为从源主机上导出对应端口为3306的实例的数据备份文件。

依次修改/tmp/mysql.sock和mysql_3306.sql，将10个实例逐步导入到数据库中，同

样这个数据导入操作也可以通过第三方客户端工具进行导入

至此，可以在本机或通过第三方工具连接数据库查看数据库已经正常导入。

在多实例的导出导入过程中，容易出问题的地方就在mysql多实例的启动上。我刚开始的操作是在目标主机上将MySQL服务停止后就将源主机的f文件拷贝过来，然后直接利用mysqld_multi –config-file=/etc/f start来启动全部实例，结果由于mysql服务已停止导致mysql无法连接访问而无法启动多实例，最后冯晓过来帮忙处理才按照上面的步骤解决掉。

最后附上导出数据脚本及mysql的几个小应用

a.运维提供的导出数据脚本

#!/bin/bash

port="3306 3307 3308 3309 3310 3311 3312 3313 3314 3315"

MYSQLDUMP="/home/mysql.5.1.39/bin/mysqldump"

PASSWORD="yourpwd"

for p in $port

do

if [ $p == 3306 ]

then

socket="/tmp/mysql.sock"

else

socket="/tmp/mysql${p}.sock"

fi

$MYSQLDUMP -uroot -S${socket} --opt --default-character-set=utf8 --single-transaction --all-database -p${PASSWORD} > /home/mysql_dump/mysql_${p}.sql

done

b.Mysql用户信息存储在mysql库的user表中，如下图

其中root用户的host字段对应为本地，即表示root（密码为空）用户仅能在本地进行登录；ec用户对应的host字段为“%”，“%”表示任何IP，即外部任何IP都可以连接进来（当然首先你要知道密码）；另外这个表中还存储了用户的所有权限信息，比如是否有库的创建权限等（这里没有列出所有字段，有兴趣的同学可以自己看下）

c.查询当前实例dat a目录的命令:show variables like '%datadir%';

d.查看当前库的连接进程的命令：show processlist;

如上图表示数据库“ec_mall”当前只有一个进程