CentOS6 mysql 데이터경로(datadir) 변경

*테스트 환경

CentOS release 6.5 (Final) 2.6.32-431.el6.x86_64

mysql-5.1.71

selinux disabled

iptables stop

=

mysql 기본 경로 /var/lib/mysql에서 /data/mysql로 변경 테스트

mysql 실행

# service mysqld start
MySQL 데이타베이스 초기화 중:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
mysqld (을)를 시작 중:                                     [  OK  ]

mysql root 패스워드 변경

# mysqladmin -u root -p password '패스워드입력'
Enter password:          <<<< 초기 패스워드는 없으므로 엔터

변경한 패스워드로 mysql접속해서 현재 데이터경로 확인

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql> quit
Bye

mysql 중지

# service mysqld stop
mysqld 를 정지 중:                                         [  OK  ]

data 디렉토리 생성

# mkdir /data

기존 데이터를 /data 경로로 복제

# cp -R /var/lib/mysql /data

복제한 데이터의 소유자를 mysql로 변경

# cd /data/
# ls -al
합계 12
drwxr-xr-x   3 root root 4096 2022-02-25 17:07 .
dr-xr-xr-x. 24 root root 4096 2022-02-25 17:06 ..
drwxr-xr-x   4 root root 4096 2022-02-25 17:07 mysql
# cd mysql/
# ls -al
합계 20496
drwxr-xr-x 4 root root     4096 2022-02-25 17:07 .
drwxr-xr-x 3 root root     4096 2022-02-25 17:07 ..
-rw-r----- 1 root root  5242880 2022-02-25 17:07 ib_logfile0
-rw-r----- 1 root root  5242880 2022-02-25 17:07 ib_logfile1
-rw-r----- 1 root root 10485760 2022-02-25 17:07 ibdata1
drwx------ 2 root root     4096 2022-02-25 17:07 mysql
drwx------ 2 root root     4096 2022-02-25 17:07 test
# cd ..
# chown -R mysql.mysql mysql
# ls -al
합계 12
drwxr-xr-x   3 root  root  4096 2022-02-25 17:07 .
dr-xr-xr-x. 24 root  root  4096 2022-02-25 17:06 ..
drwxr-xr-x   4 mysql mysql 4096 2022-02-25 17:07 mysql
# cd mysql/
# ls -al
합계 20496
drwxr-xr-x 4 mysql mysql     4096 2022-02-25 17:07 .
drwxr-xr-x 3 root  root      4096 2022-02-25 17:07 ..
-rw-r----- 1 mysql mysql  5242880 2022-02-25 17:07 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 2022-02-25 17:07 ib_logfile1
-rw-r----- 1 mysql mysql 10485760 2022-02-25 17:07 ibdata1
drwx------ 2 mysql mysql     4096 2022-02-25 17:07 mysql
drwx------ 2 mysql mysql     4096 2022-02-25 17:07 test

my.cnf 수정

# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql      <<<<<<<< /data/mysql 로 수정
socket=/var/lib/mysql/mysql.sock      <<<<<<<< /data/mysql/mysql.sock 로 수정
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql 서비스를 실행하면 실행은 되나 접속이 안된다.

# service mysqld start
mysqld (을)를 시작 중:                                     [  OK  ]
# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

mysql 서비스 중지

# service mysqld stop
mysqld 를 정지 중:                                         [  OK  ]

기존 데이터 mv로 이동

# mv /var/lib/mysql/ /var/lib/mysql-old

/etc/init.d/myslqd 수정

# vi /etc/init.d/mysqld
get_mysql_option mysqld datadir "/var/lib/mysql"     <<<<<"/data/mysql" 로 수정

다시 실행해보지만 똑같다...

# service mysqld start
mysqld (을)를 시작 중:                                     [  OK  ]
# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
# service mysqld stop
mysqld 를 정지 중:                                         [  OK  ]

변경한 데이터 경로위치를 기존 데이터 경로로 링크를 걸어준다.

# ln -s /data/mysql /var/lib/mysql

이제 잘된다. datadir 경로 확인 정상.

# service mysqld start
mysqld (을)를 시작 중:                                     [  OK  ]
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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> select @@datadir;
+--------------+
| @@datadir    |
+--------------+
| /data/mysql/ |
+--------------+
1 row in set (0.00 sec)

mysql>

==

정리하자면

1. mysql 실행

# service mysqld start

2. mysql root 패스워드 변경

# mysqladmin -u root -p password '패스워드입력'

3. 기존 데이터 경로(datadir) 확인

# mysql -u root -p
mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

4. mysql 중지

# service mysqld stop

5. data디렉토리 생성

# mkdir /data

6. 기존 데이터 복사

# cp -R /var/lib/mysql /data

7. 소유자 변경

# cd /data
# chown -R mysql.mysql mysql

8. 기존 데이터 이동

# mv /var/lib/mysql /var/lib/mysql-old

9. my.cnf 수정

# vi /etc/my.cnf
변경 전
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

변경 후
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

10. /etc/init.d/mysqld 수정

# vi /etc/init.d/mysqld
변경 전
get_mysql_option mysqld datadir "/var/lib/mysql"

변경 후
get_mysql_option mysqld datadir "/data/mysql"

11. 기존데이터경로로 링크 생성

# ln -s /data/mysql /var/lib/mysql

12. 서비스 실행

# service mysqld start

13. 변경된 데이터 경로 확인

# mysql -u root -p
mysql> select @@datadir;
+--------------+
| @@datadir    |
+--------------+
| /data/mysql/ |
+--------------+
1 row in set (0.00 sec)

 

마지막으로 selinux가 enforcing 상태일 경우 위 과정을 모두 다 해도 mysqld 실행이 되지 않는다.

로그를 보면 아래 로그가 확인됨.

220225 17:09:52 mysqld_safe Starting mysqld daemon with databases from /data/mysql
220225 17:09:52 [Warning] Can't create test file /data/mysql/localhost.lower-test
220225 17:09:52 [Warning] Can't create test file /data/mysql/localhost.lower-test
/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
220225 17:09:52 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
220225 17:09:52  InnoDB: Initializing buffer pool, size = 8.0M
220225 17:09:52  InnoDB: Completed initialization of buffer pool
220225 17:09:52  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.
220225 17:09:52 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

이럴 경우 selinux를 disabled로 설정해야 한다.

# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted

# vi /etc/selinux/config
변경 전
SELINUX=enforcing

변경 후
SELINUX=disabled

# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          disabled
Policy version:                 24
Policy from config file:        targeted

# setenforce 0