Solusi Can’t create database (errno: 13) pada MySQL

Secara default data mysql tersimpan di /var/lib/mysql , namun karena alasan kemudahan backup dan kapasitas saya mengubahnya di partisi lain yang termount di /mysqldata/. Untuk keperluan tersebut saya menjalankan perintah berikut:

sed -i 's/datadir=/var/lib/mysql/datadir=/ripanel/mysqldata/g' /etc/my.cnf
mkdir -p /mysqldata
/etc/init.d/mysqld stop
cp -rap /var/lib/mysql/* /mysqldata
chown mysql.mysql /mysqldata 
/etc/init.d/mysqld start

Dengan cara tersebut mysql sepertinya berjalan normal bisa read databases secara normal, sampai suatu saat saya menyadarinya ternyata database ini readonly tidak bisa menambah databases. Beberapa kali pesan kesalahn tertampil di phpmyadmin dan saya coba menggunakan terminal hasilnya juga sama.

[root@antorij-centos6 antorij]# 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.73 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> show databases;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema        |
| com_antorij               |
| mysql                     |
+---------------------------+
89 rows in set (0.95 sec)

mysql> create database antorij;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'antorij' at line 1

mysql> select host, user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+
2 rows in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> less /etc/group;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'less /etc/group' at line 1

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database duhgusti;
ERROR 1006 (HY000): Can't create database 'duhgusti' (errno: 13)
mysql>

Kesalahan ini ternyata mudah diatasi dengan menonaktikan SELinux atau mengubah menjadi permissive dan memastikan kepemilikan /mysqldata/ sebagai user dan group mysql.

[root@antorij-centos6 antorij]# /usr/sbin/setenforce Permissive
[root@antorij-centos6 antorij]# chown mysql:mysql -R /mysqldata/

Kesimpulannya untuk mengubah folder data mysql gunakan cara berikut:

sed -i 's/datadir=/var/lib/mysql/datadir=/ripanel/mysqldata/g' /etc/my.cnf
mkdir -p /mysqldata
/etc/init.d/mysqld stop
cp -rap /var/lib/mysql/* /mysqldata
chown mysql.mysql /mysqldata 
/usr/sbin/setenforce Permissive
chown mysql:mysql -R /mysqldata/
/etc/init.d/mysqld star

Leave a Reply

Your email address will not be published. Required fields are marked *