51

I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: drive, a large eSATA external drive.

What steps should I take?

3
  • 1
    Although this is almost 10 years, still I find the answers placed do not match the "a" article in the question, that makes me fee the necessity of being able to move a selective database, not the entire data directory! So I decided to comment with a correct approach to satisfy the question, as I can't put a regular answer. This can be achieved with SymLink in not more than 10 seconds and the detail can be found at "blog.binary.men/index.php/2021/05/21/…" hoping someone else will learn if in need. Commented May 21, 2021 at 10:43
  • @BrokenArrow The link to the blog article is dead and the server it is on has an expired SSL certificate.
    – Tony
    Commented Jul 12, 2023 at 22:26
  • @Tony Just stop the MySQL server, move the data directory somewhere else, and create a SymLink to the original path with the name of data directory as if the data directory is still there :) Commented Jul 13, 2023 at 11:38

4 Answers 4

49
  1. Shutdown mysql.
  2. Move all the files in your current data directory to the new location (check out the location in step 3 - datadir parameter).
  3. Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
  4. Start mysql.
4
  • I found I had to use / and not \ as directory separators in the directory path for datadir. I am using WAMPserver on Windows 10.
    – Steve
    Commented Oct 16, 2016 at 6:59
  • The ProgramData directory may be hidden in your file explorer. I thought it didn't exist until I changed the setting to show Hidden Files.
    – WestAce
    Commented Feb 21, 2018 at 19:00
  • 1
    On more recent versions the my.ini file is in the C:/ProgramData/MySQL foldder. Also do NOT edit it with Windows notepad. Use Notepad++ or similar because Notepad changes file encoding and the MySQL service will not be able to parse file upon startup. Commented Feb 4, 2020 at 2:57
  • I had to add network service permissions to the folder according to this answer.
    – Nae
    Commented May 19, 2020 at 21:32
13

Although you are asking for windows, here are some good directions for those ending up here looking for linux/ubuntu directions:

Assumptions:

  • You have mysql data on an external/usb/alternate drive /mountpoint/var/lib/mysql, perhaps from a computer that died or old installation
  • You wish to move it to your new Ubuntu installation locally /var/lib/mysql
  • Stop MySQL with:
    sudo /etc/init.d/mysql stop.
  • Make a backup of the current (clean) MySQL data directory with:
    sudo cp -Rp /var/lib/mysql /var/lib/mysql.backup.
  • Then copy the old data directory from the old drive with:
    sudo cp -Rp /mountpoint/var/lib/mysql /var/lib/mysql
    where mountpoint is the path to the mount.
  • Now restart MySQL with:
    sudo /etc/init.d/mysql start

With any luck, MySQL should restart, and you should have your old databases back.

Source [email protected]

2
  • 6
    (!) this will not work because config is still pointing to old location Commented Jul 11, 2018 at 10:50
  • I welcome more detail as to why you believe this does not work. Which config file are you referring to? This has worked fine for myself and clearly others.
    – Cyrille
    Commented May 1, 2021 at 19:25
4

Solution 1:

  1. First of all, backup your data using `mysqldump.
  2. If you install Mysql Server from official package, you can do it by running program MySQLINstanceConfig.exe from bin directory of your server.
  3. Import data

MySQLINstanceConfig.exe

Second solution (not recommended, but works on same machine)

  1. Stop Mysql server.
  2. Copy data from your data directory to your new directory
  3. Open my.cnf file (In my case "C:\ProgramData\MySQL\MySQL Server 5.5\my.ini")
  4. find row like datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\" and change path to your new location
  5. Start your server

As of version 5.6 MySQLInstanceConfig.exe is no longer distributed. This functionality is being replaced by the installer. http://bugs.mysql.com/bug.php?id=66306

0
4

For Windows, as of the MySQL Installer 1.4.6 (2015-04-07), the data location may be specified in a registry key that specifies the location of the my.ini file, so if the data was initially installed in a non-default location the my.ini file won't be in C:\ProgramData\MySQL\MySQL Server x.x\.

The location of the registry values are (at least as of MySQL 5.6):

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MySQL AB\MySQL Server x.x\

Location is a REG_SZ that points to the folder where the MySQL application files are located; by default: C:\Program Files\MySQL\MySQL Server x.x\

DataLocation is a REG_SZ that points to the folder where my.ini is located; by default also: C:\Program Files\MySQL\MySQL Server x.x\

Also, the my.ini file path is included in the service command under this registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MySQLXX in the ImagePath REG_EXPAND_SZ value.

The default value of this string is: "C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server x.x\my.ini" MySQLXX

So the bottom line is that in order to move data files in this scenario you have to follow the steps posted by @ITCuties with some additions.

Here are the full steps:

  1. Shutdown mysql.
  2. Look up the above DataLocation registry value to find the current my.ini file location, and if you are wanting to also move the my.ini file update the DataLocation path and move the my.ini file to this new path.
  3. If you are changing the location of the my.ini file, you need to update the path in the service command registry key listed above.
  4. Open the my.ini file, locate the datadir parameter. Move the files from this datadir path to the path in which you want the data to reside, and then also update the datadir parameter with this new path.
  5. Start mysql.
1
  • +1 much as a love *nix, the bills get paid by windows support.
    – Rawheiser
    Commented Jan 27, 2021 at 14:51