One of most valuable thing is good documentation – for common database actions. I didn´t find any well documented step-by-step guide to change data directory in MariaDB, Debian environment. Please be aware that this one guide is not perfect and some hidden traps can be found there. I have a dream to have some scripts for these basic database tasks…. 🙂
First I check current datadir:
MariaDB [(none)]> show variables where variable_name like ‚datadir‘;
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| datadir | /var/lib/mysql/ |
+—————+—————–+
1 row in set (0.00 sec)
Now I will create new directory target for storing data – /MYDATA:
root@deb74:~# mkdir /MYDATA
root@deb74:~# id mysql
uid=115(mysql) gid=124(mysql) groups=124(mysql)
root@deb74:~# chown mysql:mysql /MYDATA
root@deb74:~#
root@deb74:~# cp -R -p /var/lib/mysql /MYDATA
root@deb74:~# chmod 775 -R /MYDATA
Now it´s time to adjust /etc/mysql/my.cnf – I just changed datadir variable in [mysqld] section.
Now the mysql_install_db re-initialization:
root@deb74:~# mysql_install_db
Installing MariaDB/MySQL system tables in ‚/MYDATA‘ …
140422 14:45:07 [Note] InnoDB: The InnoDB memory heap is disabled
140422 14:45:07 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140422 14:45:07 [Note] InnoDB: Compressed tables use zlib 1.2.7
140422 14:45:07 [Note] InnoDB: Using Linux native AIO
140422 14:45:07 [Note] InnoDB: Not using CPU crc32 instructions
140422 14:45:07 [Note] InnoDB: Initializing buffer pool, size = 256.0M
140422 14:45:07 [Note] InnoDB: Completed initialization of buffer pool
140422 14:45:07 [Note] InnoDB: Highest supported file format is Barracuda.
140422 14:45:07 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463
140422 14:45:07 [Note] InnoDB: Database was not shutdown normally!
140422 14:45:07 [Note] InnoDB: Starting crash recovery.
140422 14:45:07 [Note] InnoDB: Reading tablespace information from the .ibd files…
140422 14:45:07 [Note] InnoDB: Restoring possible half-written data pages
140422 14:45:07 [Note] InnoDB: from the doublewrite buffer…
InnoDB: Doing recovery: scanned up to log sequence number 1600737
140422 14:45:07 [Note] InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percent: 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
140422 14:45:07 [Note] InnoDB: 128 rollback segment(s) are active.
140422 14:45:07 [Note] InnoDB: Waiting for purge to start
140422 14:45:07 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.15-63.0 started; log sequence number 1600737
140422 14:45:07 [Note] Recovering after a crash using /var/log/mysql/mariadb-bin
140422 14:45:07 [Note] Starting crash recovery…
140422 14:45:07 [Note] Crash recovery finished.
140422 14:45:07 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table ‚mysql.gtid_slave_pos‘ doesn’t exist
140422 14:45:09 [Note] InnoDB: FTS optimize thread exiting.
140422 14:45:09 [Note] InnoDB: Starting shutdown…
140422 14:45:10 [Note] InnoDB: Shutdown completed; log sequence number 1619188
OK
Filling help tables…
140422 14:45:10 [Note] InnoDB: The InnoDB memory heap is disabled
140422 14:45:10 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140422 14:45:10 [Note] InnoDB: Compressed tables use zlib 1.2.7
140422 14:45:10 [Note] InnoDB: Using Linux native AIO
140422 14:45:10 [Note] InnoDB: Not using CPU crc32 instructions
140422 14:45:10 [Note] InnoDB: Initializing buffer pool, size = 256.0M
140422 14:45:11 [Note] InnoDB: Completed initialization of buffer pool
140422 14:45:11 [Note] InnoDB: Highest supported file format is Barracuda.
140422 14:45:11 [Note] InnoDB: 128 rollback segment(s) are active.
140422 14:45:11 [Note] InnoDB: Waiting for purge to start
140422 14:45:11 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.15-63.0 started; log sequence number 1619188
140422 14:45:11 [Note] InnoDB: FTS optimize thread exiting.
140422 14:45:11 [Note] InnoDB: Starting shutdown…
140422 14:45:13 [Note] InnoDB: Shutdown completed; log sequence number 1619198
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 MariaDB 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 deb74 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 MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd ‚/usr‘ ; /usr/bin/mysqld_safe –datadir=’/MYDATA‘
You can test the MariaDB daemon with mysql-test-run.pl
cd ‚/usr/mysql-test‘ ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from
SkySQL Ab. You can contact us about this at sales@skysql.com.
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
root@deb74:~#
Reboot is needed, service is started properly with newly added path.
I really do have some unclear thoughts, whole process seems more complicated than it should be. Anyway it´s working for me till now….
Any comments appreciated! 🙂
-a-