如何在Ubuntu 16.04上将MySQL数据目录更改为另一个位置
在本文中，我们将学习如何更改MySQL数据目录或将MySQL数据库数据重新放置到新位置，当数据库增长非常快时，或者出于某些安全原因，我们想移动数据目录时，可能会使用这种情况。到新位置。
先决条件
具有具有Sudo权限的非root用户的Ubuntu计算机。
MySQL已安装并正常运行。
我们要在其中移动数据库数据位置的新卷或新位置，新位置将是/mnt/data_vol/MySQL，因为data_vol是连接到计算机或服务器的新卷。
更改MySQL数据文件夹位置
在继续之前，我们将首先找到数据目录的当前位置
$ mysql –u root –p Output: Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 472 Server version: 5.6.30-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
当提示您输入MySQL的根密码时，请输入密码。运行以下命令以了解MySQL的当前工作数据目录。
Mysql> select @@datadir; Output: +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)
输出将显示MySQL数据库使用/var/lib/MySQL作为默认文件夹作为数据目录。在我们首先进行任何修改之前，我们将检查数据的完整性，我们将停止MySQL并检查状态
$ sudo systemctl stop mysql
由于systemctl将不显示任何服务命令
$ sudo systemctl status mysql Output: mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: inactive (dead) since Mon 2016-09-12 13:57:43 IST; 1s ago Process: 17669 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS) Process: 17668 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS) Process: 17664 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 17668 (code=exited, status=0/SUCCESS) Sep 12 13:55:14 ubuntu-16 systemd[1]: Starting MySQL Community Server... Sep 12 13:55:15 ubuntu-16 systemd[1]: Started MySQL Community Server. Sep 12 13:57:40 ubuntu-16 systemd[1]: Stopping MySQL Community Server... Sep 12 13:57:43 ubuntu-16 systemd[1]: Stopped MySQL Community Server.
确认MySQL已停止后，将数据移至新位置。要移动数据，我们将Rsync与–a选项（用于保留数据文件的权限）和–v（用于显示详细输出）一起使用。
以下是将数据移到新位置的完整命令–
$ rsync –av /var/lib/mysql /mnt/data_vol/ OutPut: sending incremental file list mysql/ mysql/auto.cnf mysql/debian-5.7.flag mysql/ib_buffer_pool mysql/ib_logfile0 mysql/ib_logfile1 mysql/ibdata1 mysql/mysql/ mysql/mysql/columns_priv.MYD mysql/mysql/columns_priv.MYI mysql/mysql/columns_priv.frm mysql/mysql/db.MYD mysql/mysql/db.MYI mysql/mysql/db.frm mysql/mysql/db.opt …. mysql/sys/x@0024user_summary.frmmysql/sys/x@0024user_summary_by_file_io.frm mysql/sys/x@0024user_summary_by_file_io_type.frm mysql/sys/x@0024user_summary_by_stages.frm mysql/sys/x@0024user_summary_by_statement_latency.frm mysql/sys/x@0024user_summary_by_statement_type.frm mysql/sys/x@0024wait_classes_global_by_avg_latency.frm mysql/sys/x@0024wait_classes_global_by_latency.frm mysql/sys/x@0024waits_by_host_by_latency.frm mysqlsys//x@0024waits_by_user_by_latency.frm mysql/sys/x@0024waits_global_by_latency.frm sent 199,384,083 bytes received 6,858 bytes 132,927,294.00 bytes/sec total size is 199,307,568 speedup is 1.00
rsync之后，将数据文件夹成功移动到新位置。出于安全原因，我们将保留数据文件夹直到确认数据位于新位置，然后将当前数据目录从/var/lib/mysql重命名为/var/lib/mysql_backup。下面是更改当前数据目录的命令。
以下是更改当前数据目录的命令–
$ sudo mv /var/lib/mysql /var/lib/mysql_backup
现在，我们将更改默认数据目录，要进行更改，我们有很多方法，但是我们将编辑/etc/mysql/mysql.conf.d/mysqld.cnf中的mysqld.cnf文件。
要编辑mysqld.cnf，下面是命令
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf Output: [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /mnt/data_vol/mysql/ tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking
更改Apparmor别名设置
另外，我们需要编辑/etc/apparmor.d/tunables/alias
在文件的底部，我们需要在别名规则中添加以下几行。
$ sudo vi /etc/apparmor.d/tunables/alias Output: # ------------------------------------------------------------------ # # Copyright (C) 2010 Canonical Ltd. # # This program is free software; you can redistribute it and/or # modify it under the terms of version 2 of the GNU General Public # License published by the Free Software Foundation. # # ------------------------------------------------------------------ # Alias rules can be used to rewrite paths and are done after variable # resolution. For example, if '/usr' is on removable media: # alias /usr/ -> /mnt/usr/, # # Or if mysql databases are stored in /home: # alias /var/lib/mysql/ -> /home/mysql/, alias /var/lib/mysql/ -> /mnt/data_vol/mysql
一旦文件被编辑，我们需要重启apparmor。
以下是重新启动Apparmor的命令。
更改默认数据目录后，我们需要运行以下命令，该命令将创建最小目录文件夹结构以传递脚本环境。
$sudomkdir/var/lib/mysql/mysql–p
现在我们将重新启动mysql服务。
$ sudo systemctl start mysql
现在，我们将使用以下命令检查MySQL服务的状态
$ sudo systemctl status mysql
Output:
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2016-09-12 14:17:27 IST; 23s ago
Process: 18481 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCC
Process: 18477 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCES
Main PID: 18480 (mysqld)
Tasks: 28 (limit: 512)
Memory: 137.3M
CPU: 329ms
CGroup: /system.slice/mysql.service
└─18480 /usr/sbin/mysqld
Sep 12 14:17:26 ubuntu-16 systemd[1]: Starting MySQL Community Server...
Sep 12 14:17:27 ubuntu-16 systemd[1]: Started MySQL Community Server.为了确保新数据目录已更改，我们将运行以下命令
$ mysql -uroot -p Output: Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.13-0ubuntu0.16.04.2 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. select @@datadir +----------------------+ | @@datadir | +----------------------+ | /mnt/data_vol/mysql/ | +-----------------+ 1 row in set (0.00 sec) mysql>
一旦确认数据目录已更改，我们将删除默认数据目录，该目录位于/var/lib/mysql_backup，下面是删除旧数据库目录的命令。
$ sudo rm –rf /var/lib/mysql_backup
在上述配置和步骤中，我们学习了将MySQL数据目录重新定位到新位置，这将有助于我们保护或将更多数据存储到其他位置。