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@rahmatriyanto-centos6 rahmatriyanto]# 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_rahmatriyanto | | mysql | +---------------------------+ 89 rows in set (0.95 sec) mysql> create database rahmatriyanto; 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 'rahmatriyanto' 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@rahmatriyanto-centos6 rahmatriyanto]# /usr/sbin/setenforce Permissive [root@rahmatriyanto-centos6 rahmatriyanto]# 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