Move MySQL data

from the Artful MySQL Tips List

1 Document where all MySQL data is:

In the mysql client program, issue ...

  select @@datadir, @@innodb_data_home_dir, @@innodb_directories;
and note the values.

Absent such a connection ...

Linux: Run ...

  ps -aux | grep mysql
... and look for the datadir setting.

Windows: Default data folder is C:\Program Files\MySQL\MySQL Server 8.0\data or C:\ProgramData\Mysql. The latter folder is hidden by default, so change folder options in Windows Explorer to unhide it. If the file isn't there, its location is specified in my.ini, so search the hard disk(s) for my.ini, then for datadir, innodb_data_home_dir, innodb_directories settings. ...or...

  Find the MySQL service command
  Under Properties find datadir in the command
2 Stop MySQL
Linux: sudo /etc/init.d/mysql stop
Windows: in services.msc stop MySQL service
3 Copy the existing data directory or directories to the new location(s) ... 4 Edit my.cnf/ini with a text editor, update all instances of the data folders discovered in #1.

In Linux, issue ...

  sudo nano /etc/apparmor.d/usr.sbin.mysqld
Change instances of /var/lib/mysql and of other MySQL data folder specs to the new path(s). Then restart AppArmor with...
  sudo /etc/init.d/apparmor reload
5 Restart MySQL

Last updated 27 Sep 2020

Return to the Artful MySQL Tips page