{"id":527,"date":"2014-04-22T15:28:32","date_gmt":"2014-04-22T14:28:32","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=527"},"modified":"2014-04-22T15:28:32","modified_gmt":"2014-04-22T14:28:32","slug":"change-data-directory-mariadb-10-0-x","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=527","title":{"rendered":"Change data directory – MariaDB 10.0.x"},"content":{"rendered":"

One of most valuable thing is good documentation – for common database actions. I didn\u00b4t 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<\/strong>. I have a dream to have some scripts for these basic database tasks…. \ud83d\ude42<\/p>\n

<\/p>\n

First I check current datadir:<\/p>\n

MariaDB [(none)]> show variables where variable_name like ‚datadir‘;<\/strong><\/span><\/span>
\n+—————+—————–+<\/span>
\n| Variable_name | Value |<\/span>
\n+—————+—————–+<\/span>
\n| datadir | \/var\/lib\/mysql\/ |<\/span>
\n+—————+—————–+<\/span>
\n1 row in set (0.00 sec)<\/span><\/p>\n

Now I will create new directory target for storing data – \/MYDATA:<\/p>\n

root@deb74:~# mkdir \/MYDATA<\/strong><\/span><\/span><\/p>\n

root@deb74:~# id mysql<\/strong><\/span><\/span>
\nuid=115(mysql) gid=124(mysql) groups=124(mysql)<\/span><\/p>\n

root@deb74:~# chown mysql:mysql \/MYDATA<\/strong><\/span><\/span>
\nroot@deb74:~#<\/span><\/p>\n

root@deb74:~# cp -R -p \/var\/lib\/mysql \/MYDATA<\/strong><\/span><\/span>
\nroot@deb74:~# chmod 775 -R \/MYDATA<\/strong><\/span><\/span><\/p>\n

Now it\u00b4s time to adjust \/etc\/mysql\/my.cnf – I just changed datadir variable in [mysqld] section.<\/p>\n

Now the mysql_install_db re-initialization:<\/p>\n

root@deb74:~# mysql_install_db<\/strong> <\/span><\/span>
\nInstalling MariaDB\/MySQL system tables in ‚\/MYDATA‘ …<\/span>
\n140422 14:45:07 [Note] InnoDB: The InnoDB memory heap is disabled<\/span>
\n140422 14:45:07 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins<\/span>
\n140422 14:45:07 [Note] InnoDB: Compressed tables use zlib 1.2.7<\/span>
\n140422 14:45:07 [Note] InnoDB: Using Linux native AIO<\/span>
\n140422 14:45:07 [Note] InnoDB: Not using CPU crc32 instructions<\/span>
\n140422 14:45:07 [Note] InnoDB: Initializing buffer pool, size = 256.0M<\/span>
\n140422 14:45:07 [Note] InnoDB: Completed initialization of buffer pool<\/span>
\n140422 14:45:07 [Note] InnoDB: Highest supported file format is Barracuda.<\/span>
\n140422 14:45:07 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463<\/span>
\n140422 14:45:07 [Note] InnoDB: Database was not shutdown normally!<\/span>
\n140422 14:45:07 [Note] InnoDB: Starting crash recovery.<\/span>
\n140422 14:45:07 [Note] InnoDB: Reading tablespace information from the .ibd files…<\/span>
\n140422 14:45:07 [Note] InnoDB: Restoring possible half-written data pages <\/span>
\n140422 14:45:07 [Note] InnoDB: from the doublewrite buffer…<\/span>
\nInnoDB: Doing recovery: scanned up to log sequence number 1600737<\/span>
\n140422 14:45:07 [Note] InnoDB: Starting an apply batch of log records to the database…<\/span>
\nInnoDB: 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 <\/span>
\nInnoDB: Apply batch completed<\/span>
\n140422 14:45:07 [Note] InnoDB: 128 rollback segment(s) are active.<\/span>
\n140422 14:45:07 [Note] InnoDB: Waiting for purge to start<\/span>
\n140422 14:45:07 [Note] InnoDB: Percona XtraDB (http:\/\/www.percona.com) 5.6.15-63.0 started; log sequence number 1600737<\/span>
\n140422 14:45:07 [Note] Recovering after a crash using \/var\/log\/mysql\/mariadb-bin<\/span>
\n140422 14:45:07 [Note] Starting crash recovery…<\/span>
\n140422 14:45:07 [Note] Crash recovery finished.<\/span>
\n140422 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<\/span>
\n140422 14:45:09 [Note] InnoDB: FTS optimize thread exiting.<\/span>
\n140422 14:45:09 [Note] InnoDB: Starting shutdown…<\/span>
\n140422 14:45:10 [Note] InnoDB: Shutdown completed; log sequence number 1619188<\/span>
\nOK<\/span>
\nFilling help tables…<\/span>
\n140422 14:45:10 [Note] InnoDB: The InnoDB memory heap is disabled<\/span>
\n140422 14:45:10 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins<\/span>
\n140422 14:45:10 [Note] InnoDB: Compressed tables use zlib 1.2.7<\/span>
\n140422 14:45:10 [Note] InnoDB: Using Linux native AIO<\/span>
\n140422 14:45:10 [Note] InnoDB: Not using CPU crc32 instructions<\/span>
\n140422 14:45:10 [Note] InnoDB: Initializing buffer pool, size = 256.0M<\/span>
\n140422 14:45:11 [Note] InnoDB: Completed initialization of buffer pool<\/span>
\n140422 14:45:11 [Note] InnoDB: Highest supported file format is Barracuda.<\/span>
\n140422 14:45:11 [Note] InnoDB: 128 rollback segment(s) are active.<\/span>
\n140422 14:45:11 [Note] InnoDB: Waiting for purge to start<\/span>
\n140422 14:45:11 [Note] InnoDB: Percona XtraDB (http:\/\/www.percona.com) 5.6.15-63.0 started; log sequence number 1619188<\/span>
\n140422 14:45:11 [Note] InnoDB: FTS optimize thread exiting.<\/span>
\n140422 14:45:11 [Note] InnoDB: Starting shutdown…<\/span>
\n140422 14:45:13 [Note] InnoDB: Shutdown completed; log sequence number 1619198<\/span>
\nOK<\/span><\/p>\n

To start mysqld at boot time you have to copy<\/span>
\nsupport-files\/mysql.server to the right place for your system<\/span><\/p>\n

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !<\/span>
\nTo do so, start the server, then issue the following commands:<\/span><\/p>\n

‚\/usr\/bin\/mysqladmin‘ -u root password ‚new-password‘<\/span>
\n‚\/usr\/bin\/mysqladmin‘ -u root -h deb74 password ‚new-password‘<\/span><\/p>\n

Alternatively you can run:<\/span>
\n‚\/usr\/bin\/mysql_secure_installation‘<\/span><\/p>\n

which will also give you the option of removing the test<\/span>
\ndatabases and anonymous user created by default. This is<\/span>
\nstrongly recommended for production servers.<\/span><\/p>\n

See the MariaDB Knowledgebase at http:\/\/mariadb.com\/kb or the<\/span>
\nMySQL manual for more instructions.<\/span><\/p>\n

You can start the MariaDB daemon with:<\/span>
\ncd ‚\/usr‘ ; \/usr\/bin\/mysqld_safe –datadir=’\/MYDATA‘<\/span><\/p>\n

You can test the MariaDB daemon with mysql-test-run.pl<\/span>
\ncd ‚\/usr\/mysql-test‘ ; perl mysql-test-run.pl<\/span><\/p>\n

Please report any problems at http:\/\/mariadb.org\/jira<\/span><\/p>\n

The latest information about MariaDB is available at http:\/\/mariadb.org\/.<\/span>
\nYou can find additional information about the MySQL part at:<\/span>
\nhttp:\/\/dev.mysql.com<\/span>
\nSupport MariaDB development by buying support\/new features from<\/span>
\nSkySQL Ab. You can contact us about this at sales@skysql.com.<\/span>
\nAlternatively consider joining our community based development effort:<\/span>
\nhttp:\/\/mariadb.com\/kb\/en\/contributing-to-the-mariadb-project\/<\/span><\/p>\n

root@deb74:~#<\/span><\/p>\n

Reboot is needed, service is started properly with newly added path.<\/p>\n

I really do have some unclear thoughts, whole process seems more complicated than it should be. Anyway it\u00b4s working for me till now….<\/p>\n

Any comments appreciated! \ud83d\ude42<\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

One of most valuable thing is good documentation – for common database actions. I didn\u00b4t 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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,13],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/527"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=527"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/527\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=527"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}