Alien的博客

MySQL更改数据库路径

最近磁盘空间紧张,面对每天增长不少容量的mysql觉得把它的数据库路径改到其他的路径,在没有做足功课的情况下直接动手操作了,倒是遇到了不少的麻烦。
我所使用的环境如下


[root@Alien ~]# cat /etc/redhat-release
CentOS Linux release 7.0.1406 (Core)
[root@Alien ~]# mysql -V
mysql  Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1

首先把数据库停掉。然后把原来的所有数据库文件全部倒拷贝到新的路径下


systemctl stop  mariadb
cp /var/lib/mysql/* /home/mysql_date/ -R
mv /var/lib/mysql/ /var/lib/mysql.bak
chown mysql:mysql /home/mysql_date -R

然后更改数据库的my.cnf文件,把datadir和socket指向新的路径


vim /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/home/mysql_date
#socket=/var/lib/mysql/mysql.sock
socket=/home/mysql_date/mysql.sock

重新启动数据库


systemctl start mariadb 

发现日志里有Warning的警告


150609 12:11:07 mysqld_safe Starting mysqld daemon with databases from /home/mysql_date
150609 12:11:07 [Warning] Can't create test file /home/mysql_date/backup.lower-test
150609 12:11:07 InnoDB: The InnoDB memory heap is disabled
150609 12:11:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150609 12:11:07 InnoDB: Compressed tables use zlib 1.2.7
150609 12:11:07 InnoDB: Using Linux native AIO
150609 12:11:07 InnoDB: Initializing buffer pool, size = 128.0M
150609 12:11:07 InnoDB: Completed initialization of buffer pool
150609 12:11:07  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

查了半天的各种权限问题,后来发现原来是selinux搞的鬼


[root@Alien lib]# getenforce
Enforcing
[root@Alien lib]chcon -R -t mysqld_db_t  /home/mysql_date
[root@Alien lib]systemctl start mariadb

搞定selinux,启动mysql成功,用mysql命令登录数据库的时候却又在提示找不到sock文件


[root@Alien mysql_date]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

继续编辑my.cnf文件,给client指向新的sock文件


vim /etc/my.cnf
[client]
socket=/home/mysql_date/mysql.sock

修改完成重启mysql后终于可以正常登录了


[root@Alien mysql_date]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

后来发现有一些其他的应用还是提示找不到sock文件


* Error connecting to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

不知道是哪里的配置文件有问题,直接偷懒,给他创建一个软连接完事儿


[root@Alien zabbix]# ln -s /home/mysql_date/mysql.sock  /var/lib/mysql/
[root@Alien zabbix]# systemctl restart mariadb