因为现在主要的生产环境都是基于虚拟机(vagrant)搭建的,所以在销毁虚拟机的时候会有一些成本问题。(上一次销毁虚拟机导致mysql数据丢失的惨痛教训还历历在目 :tired_face:),今天实践一下把开发环境中mysql数据存储到宿主主机上。有一些问题需要记录。(也有一个需要请教的问题最后再说)
迁移准备
首先查看当前mysql数据到存储位置。以ubuntuapt-get的安装方式为例,默认的存储路径在/var/lib/mysql,可以打开配置文件/etc/mysql/mysql.conf.d/mysqld.cnf进行确认(自定义安装的同学,需要找到自己的配置文件确认位置):
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
其中,datadir就是指定的数据存储目录。
先看一下,当前数据库的状态:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| vagrant |
+--------------------+
5 rows in set (0.00 sec)
开始迁移
首先停止mysql服务,sudo serivce mysql stop
创建新的存储目录,mkdir /data/
移动/复制之前存放数据库目录文件,到新的数据库存放目录位置
sudo cp -R /var/lib/mysql/ /data/
修改mysql数据库目录所属(注:此处有坑!)
chown mysql:mysql -R /data/mysql/
修改配置文件,指定数据存放目录
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /data/mysql/
log-error = /var/log/mysql/error.log
启动数据库服务,查看是否生效
vagrant@newiep:$ sudo service mysql restart
# 链接数据库
mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /data/mysql/ |
| ignore_db_dirs | |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| lc_messages_dir | /usr/share/mysql/ |
| plugin_dir | /usr/lib/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
15 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| vagrant |
+--------------------+
5 rows in set (0.00 sec)
主要过程说完啦,你以为会一帆风顺?No!No!No! 解决一下坑:
坑1: 使用 vagrant 搭建的的开发环境,修改挂载出来的共享目录权限
如果不指定目录所属owner,那么在 vagrant 启动后是没办法通过 chown 命令来修改的,需要在启动前就指定 owner
修改 Vagrantfile :
# 增加数据目录挂载,并指定权限
config.vm.synced_folder ".data/mysql", "/data/mysql", id: "mysql",
owner: "mysql", group: "mysql",
mount_options: ["dmode=775,fmode=664"]
坑2 : mysql 有个安全模式,会限制一些目录的访问
按理说修改 owner 后,就没问题啦!但是还是启动不了 mysql 服务,日志显示
...
2017-02-07T07:30:28.997699Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2017-02-07T07:30:28.997857Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2017-02-07T07:30:28.998081Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-02-07T07:30:29.603731Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-02-07T07:30:29.603776Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-02-07T07:30:29.603783Z 0 [ERROR] Failed to initialize plugins.
2017-02-07T07:30:29.603786Z 0 [ERROR] Aborting
...
这里有个相关的问题:mysql on virtualbox with datadir on shared folder
简单来说,mysql 有个配置文件,用来指定一些目录的访问权限,打开 sudo vi /etc/apparmor.d/usr.sbin.mysqld 添加如下代码:
# Allow data dir access
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
#下面两行是手动添加的
/data/mysql/ r,
/data/mysql/** rwk,
重新启动mysql!
总感觉这种方式不够完美,有更好的方法请一定告诉我!:pray: :pray: :pray:
问题来啦:现在每次启动 vagrant,都需要重新启动一下mysql,否则,会找不到数据库。初步怀疑:在vagrant 启动过程中,挂载数据目录是在内部服务(比如mysql)启动之后!,至于具体解决方法有缘再更新!
本作品采用《CC 协议》,转载必须注明作者和本文链接