Relocate MySQL databases

[Maelstorm]

I have a default installation of MySQL 5.7 on my machine. The default locations of the database directories (there are three of them (mysql, mysql_secure, and mysql_tmpdir)) is /var/db. I want to move them to /usr/local/var/db. I have tried symlinking them, moving them and changing the config in /usr/local/etc/mysql/my.cnf and by using the mysql_dbdir option in /etc/rc.conf to no avail. When I do it, I get a bunch of weird errors that state that the operating system returned error code 2. I have checked ownership and permissions and I can't seem to figure this one out.

Here is the file /usr/local/etc/mysql/my.cnf for reference. Note that this is the default unmodified file. I borked the install up so much trying to get this to work that I ended up having to reinstall it.

# $FreeBSD: head/databases/mysql57-server/files/my.cnf.sample.in 414707 2016-05-06 14:39:59Z riggs $

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

Relevant lines from /etc/rc.conf:

# MySQL Database Server
mysql_enable="YES"
#mysql_optfile="/usr/local/etc/mysql/my.cnf"
#mysql_dbdir="/usr/local/var/db/mysql/data"

The big difference here is that I know how to use MySQL (SQL commands and such), just not how to really set it up for use. I did do some searching online and found the following articles on what I am trying to do, but for some reason it doesn't work.

Thread moving-mysql-data-dir.3711
https://serverfault.com/questions/314200/how-to-change-mysql-data-directory
http://www.freebsdonline.com/content/view/813/531/

Any ideas as to what I am doing wrong?

 

[Chris_H]

If I were you -- and don't think I'm not.
The way I'd probably attempt to do it. Would be to perform a complete (mysql) dump of all the DB's. Then I'd deinstall databases/mysql, and perform a fresh install. But modifying ${prefix} within the Makefile. Pointing it to your desired location (/usr/local/var) as apposed to /var.

But that's just me.

HTH

--Chris

 

[anlashok]

Error code 2 is No such file or directory so its probable that it was trying to read the config from where you specified in the rc.conf, but where you had actually placed either the config or data files was somewhere slightly different?. For me my.conf is in /usr/local/etc/my.cnf and not in /usr/local/etc/mysql/my.cnf and where the running server reports it is pulling its config from. Probably worth double checking. I've not set the mysql_optfile setting in rc.conf, but I am using 5.6 not 5.7

If you are using innodb tables, did you also change the innodb directories as well are the other ones you mention, eg:

innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql

 

[Deleted member 54719]

This should be a no brainer through use of symlinks and/or relocation in the my.cnf file. If something is keeping that from working then try to figure out why. I would not do hackish workarounds that ignore the underlying issue.

 

[Maelstorm]

Apologies for the late reply, but I did get it working. It turns out that you can move the datadir, but you cannot move the other directories. They must stay in /var/db. That's ok because they don't take up much room anyways. So I now have a working MySQL database on my web server.