{"id":4080,"date":"2014-12-19T18:03:35","date_gmt":"2014-12-19T10:03:35","guid":{"rendered":"http:\/\/rmohan.com\/?p=4080"},"modified":"2014-12-19T18:03:57","modified_gmt":"2014-12-19T10:03:57","slug":"mysql-changing-the-storage-location-for-databases","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=4080","title":{"rendered":"MySQL: changing the storage location for databases"},"content":{"rendered":"<p>By default when you install MySQL it stores databases on the C drive, you can change this during installation but perhaps you forgot to change the data files path during installation or completely missed the option to do this, or perhaps you used the Web Platform Installer which doesn&#8217;t give you any option to change the data files path.<\/p>\n<p>On windows 2008 the datadir is \u201cC:\\ProgramData\\MySQL\\MySQL Server 5.5\\data\u201d<\/p>\n<p>This is generally not the best idea as your c drive should be only for the OS and installed programs, you should put all your data on a different drive and keep it separate from the OS. So if you find yourself needing to change this after installation, here is how.<\/p>\n<p>Open your my.ini file<\/p>\n<p>On windows 2008 this is in C:\\ProgramData\\MySQL\\MySQL Server 5.5\\\u201d<\/p>\n<p>On other windows version just look at the mysql service properties to find out where the defaults-file is.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/rmohan.com\/wp-content\/uploads\/2014\/12\/mysql-service.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4081\" src=\"http:\/\/rmohan.com\/wp-content\/uploads\/2014\/12\/mysql-service.png\" alt=\"mysql-service\" width=\"419\" height=\"467\" srcset=\"https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service.png 419w, https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service-269x300.png 269w, https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service-134x150.png 134w, https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service-400x445.png 400w\" sizes=\"(max-width: 419px) 100vw, 419px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>now find the \u201cdatadir\u201d parameter and change it to where you want to store your databases.<\/p>\n<p>E.G.<br \/>\ndatadir=&#8221;D:\\MySQL\\data\\&#8221;<\/p>\n<p>Now STOP the MySQL service.<\/p>\n<p>Now you must copy the original data directory to the new location.<\/p>\n<p>E.G.<br \/>\ncopy \u201cC:\\ProgramData\\MySQL\\MySQL Server 5.5\\data\u201d to \u201cD:\\mysql\\data\u201d<\/p>\n<p>now restart MySQL.<\/p>\n<p>If the service fails to start, double check that you have entered the path correctly in my.ini and that the path exists, and that all the data files have copied across properly.<\/p>\n<p>Check your new data folder permissions and make sure it has \u201cNETWORK SERVICE\u201d with full permissions. On Windows 2008 these permissions may not exist by default.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/rmohan.com\/wp-content\/uploads\/2014\/12\/mysql-service1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4082\" src=\"http:\/\/rmohan.com\/wp-content\/uploads\/2014\/12\/mysql-service1.png\" alt=\"mysql-service\" width=\"419\" height=\"467\" srcset=\"https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service1.png 419w, https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service1-269x300.png 269w, https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service1-134x150.png 134w, https:\/\/mohan.sg\/wp-content\/uploads\/2014\/12\/mysql-service1-400x445.png 400w\" sizes=\"(max-width: 419px) 100vw, 419px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>From your paths, I can safely assume the following:<\/p>\n<ul>\n<li>You are running MySQL in Windows<\/li>\n<li>You used the MySQL MSI to install<\/li>\n<\/ul>\n<p>What you need to do is establish the file my.ini<\/p>\n<p>When installing MySQL for Windows using the MSI, the location of my.ini is expected to be <code>C:\/ProgramData\/MySQL\/MySQL Server 5.1<\/code>.<\/p>\n<p>Please run the following in a DOS Window:<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">cd C<\/span><span class=\"pun\">:\/<\/span><span class=\"pln\">ProgramData<\/span><span class=\"pun\">\/<\/span><span class=\"pln\">MySQL<\/span><span class=\"pun\">\/<\/span><span class=\"pln\">MySQL Server <\/span><span class=\"lit\">5.1<\/span><span class=\"pln\">\r\ndir <\/span><span class=\"pun\">*.<\/span><span class=\"pln\">ini<\/span><\/code><\/pre>\n<p>You will see some sample my.ini files. However, if there is no file named my.ini in that folder, you must create one. Whether one exists or not, please run this:<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">cd C<\/span><span class=\"pun\">:\/<\/span><span class=\"pln\">ProgramData<\/span><span class=\"pun\">\/<\/span><span class=\"pln\">MySQL<\/span><span class=\"pun\">\/<\/span><span class=\"pln\">MySQL Server <\/span><span class=\"lit\">5.1<\/span><span class=\"pln\">\r\nnotepad my<\/span><span class=\"pun\">.<\/span><span class=\"pln\">ini<\/span><\/code><\/pre>\n<p>If you are asked to create it, please do so.<\/p>\n<p>Next, create the following entry under the [mysqld] group header in my.ini:<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pun\">[<\/span><span class=\"pln\">mysqld<\/span><span class=\"pun\">]<\/span><span class=\"pln\">\r\ndatadir<\/span><span class=\"pun\">=<\/span><span class=\"pln\">F<\/span><span class=\"pun\">:\/<\/span><span class=\"pln\">naveen<\/span><span class=\"pun\">\/<\/span><span class=\"pln\">data<\/span><\/code><\/pre>\n<p>Save my.ini<\/p>\n<p>Next, stop mysql from the DOS command line like this:<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">C<\/span><span class=\"pun\">:\\&gt;<\/span><span class=\"pln\"> net stop mysql<\/span><\/code><\/pre>\n<p>Next, make a copy of the entire data folder in the new location<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">C<\/span><span class=\"pun\">:\\&gt;<\/span><span class=\"pln\"> xcopy <\/span><span class=\"str\">\"C:\\ProgramData\\MySQL\\MySQL Server 5.1\\data\"<\/span><span class=\"pln\"> F<\/span><span class=\"pun\">:\\<\/span><span class=\"pln\">naveen<\/span><span class=\"pun\">\\<\/span><span class=\"pln\">data <\/span><span class=\"pun\">\/<\/span><span class=\"pln\">s<\/span><\/code><\/pre>\n<p>Last step, start up mysql<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">C<\/span><span class=\"pun\">:\\&gt;<\/span><span class=\"pln\"> net <\/span><span class=\"kwd\">start<\/span><span class=\"pln\"> mysql<\/span><\/code><\/pre>\n<p>Try logging into mysql. Once you can login to mysql successfully, run this command:<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">show variables <\/span><span class=\"kwd\">like<\/span> <span class=\"str\">'datadir'<\/span><span class=\"pun\">;<\/span><\/code><\/pre>\n<p>If <code>F:\\naveen\\data<\/code> shows up as the datadir, CONGRATULATIONS, YOU HAVE DONE IT RIGHT !!!<\/p>\n<p>Once you are satisfied all your apps hitting MySQL works, you can delete everything in <code>C:\\ProgramData\\MySQL\\MySQL Server 5.1\\data\\*<\/code><\/p>\n<p>Give it a Try !!!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By default when you install MySQL it stores databases on the C drive, you can change this during installation but perhaps you forgot to change the data files path during installation or completely missed the option to do this, or perhaps you used the Web Platform Installer which doesn&#8217;t give you any option to change [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4080"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4080"}],"version-history":[{"count":2,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4080\/revisions"}],"predecessor-version":[{"id":4084,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4080\/revisions\/4084"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4080"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4080"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4080"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}