刚刚接触linux,mysql database占满了SSD,加了块机械硬盘,迁移database。
参考了如下两篇文章,说的差不多,但还是遇到了不少error,记录一下。
Ubuntu 下mysql数据库存放位置迁移 - 简书 (jianshu.com)
(52条消息) ubuntu mysql目录_Ubuntu下MySql数据存储目录迁移_橙湖工作室的博客-CSDN博客
版本: mysqld 8.0.28-0ubuntu0.20.04.3
创建和拷贝database完全按照上述两篇blog
##找mysql存放路径
sudo mysql #进入数据库,多种代码,用自己习惯的。
show variables like '%dir%'; #数据路径,datadir = /var/lib/mysql
#关闭mysql service
sudo service mysql stop
#创建新的database存放文件夹,
mkdir /your/path
chmod 777 /your/path #可暂时将该文件夹权限全部打开
#保守起见,用copy
sudo cp -av /var/lib/mysql/ /your/path/
copy完成后,修改mysqld.cnf,也就是mysql 服务端的配置文件。
我的文件位置与上述blog不同,附上寻找方法
cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
my.cnf中并没有datadir,而在/etc/mysql/mysql.conf.d/mysqld.cnf中。
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
将原来的datadir注释掉,新建datadir = /your/path/mysql,在此附上vi 操作方法。
[mysqld]
#
# * Basic Settings
#
user = mysql
datadir = /your/path/mysql
# pid-file = /var/run/mysqld/mysqld.pid
# socket = /var/run/mysqld/mysqld.sock
# port = 3306
# datadir = /var/lib/mysql
接下来继续修改apparmor 配置 文件
sudo vi /etc/apparmor.d/usr.sbin.mysqld
将# Allow data dir access这一段,改成下面的形式:添加橙色代码,保存
# Allow data dir access
# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
/your/path/mysql/ r,
/your/path/mysql/** rwk,
sudo vi /etc/apparmor.d/abstractions/mysql
做如下修改:添加橙色代码,保存
# ------------------------------------------------------------------
# /var/lib/mysql{,d}/mysql{,d}.sock rw,
# /{var/,}run/mysql{,d}/mysql{,d}.sock rw,
/data/mysql/mysql/mysql.sock rw,
/usr/share/{mysql,mysql-community-server,mariadb}/charsets/ r,
/usr/share/{mysql,mysql-community-server,mariadb}/charsets/*.xml r,
#重启apparmor服务
sudo systemctl restart apparmor
#重启mysql服务
sudo systemctl start mysql
重启mysql时遇到error,
Error 13 权限不够 permission denied
这里附查看status和error log的代码
#查看mysql 的服务状态,此处会显示Error 13
sudo systemctl status mysql
#查看mysql error.log
cat /var/log/mysql/error.log #如果log路径不对,可以在上文提到的mysqld.cnf中找到
检查/var/lib和/your/path的权限
#/var/lib的权限
cd /var
#列出权限
ll
drwxr-xr-x 77 root root 4096 3月 11 10:22 lib/
可以看到lib的权限是 drwxr-xr-x,也就是755
4=r, 2=w, 1=x 分别对应read, write 和execute权限
(53条消息) Ubuntu修改文件权限_slwhy的博客-CSDN博客_ubuntu修改文件权限
#修改/your/path的权限
cd /your
sudo chmod 755 path
#重启mysql
sudo systemctl start mysql
mysql是启动成功了,但是service启动极慢,要40min,之后慢慢处理吧