{"id":1266,"date":"2015-12-21T01:02:33","date_gmt":"2015-12-20T19:02:33","guid":{"rendered":"http:\/\/promincproductions.com\/blog\/?p=1266"},"modified":"2015-12-21T01:02:33","modified_gmt":"2015-12-20T19:02:33","slug":"osx-mysql-database-moving-from-mamp-to-os-built-in-mysql","status":"publish","type":"post","link":"https:\/\/promincproductions.com\/blog\/osx-mysql-database-moving-from-mamp-to-os-built-in-mysql\/","title":{"rendered":"OSX &#8211; MySQL Database &#8211; Moving from MAMP to OS Built In MySQL Server"},"content":{"rendered":"<p>When I first wanted to setup a locally hosted PHP\/MySQL stack on my MacBook, I came across MAMP and it seemed logical to run with that.<\/p>\n<p>But after some time of running MAMP, I realized that OS X has the ability to run MySQL inherently in the operating system (as well as PHP) which removes one step when trying to access the localhost &#8211; I don&#8217;t need to start an application for PHP and MySQL to be running. \u00a0Likewise, this will give me the freedom to manage the two servers as I wish without needing to deal with what MAMP has predefined.<\/p>\n<p>In my process of migrating the databases from MAMP to the OS X installed version of MySQL I ran into some struggles and thought I&#8217;d document them here.<\/p>\n<figure id=\"attachment_1270\" aria-describedby=\"caption-attachment-1270\" style=\"width: 409px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP.jpg\" rel=\"attachment wp-att-270\" data-lasso-id=\"581\" data-rel=\"lightbox-gallery-dlKcHyYE\" data-rl_title=\"\" data-rl_caption=\"\" title=\"\"><img decoding=\"async\" class=\"size-medium wp-image-1270\" src=\"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP-409x500.jpg\" alt=\"MySQL on OS X Without MAMP\" width=\"409\" height=\"500\" srcset=\"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP-409x500.jpg 409w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP-768x940.jpg 768w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP-150x184.jpg 150w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP-600x734.jpg 600w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP.jpg 800w\" sizes=\"(max-width: 409px) 100vw, 409px\" \/><\/a><figcaption id=\"caption-attachment-1270\" class=\"wp-caption-text\">Moving from MAMP to MySQL hosted natively in OS X.<\/figcaption><\/figure>\n<h2>Running MySQL<\/h2>\n<p>There is a <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/osx-installation-pkg.html\" target=\"_blank\" data-lasso-id=\"582\" rel=\"noopener\">MySQL installation package<\/a>\u00a0available. \u00a0Once installed, you can start\/stop MySQL from the command line using Terminal or from the system preferences. \u00a0Even better, you can have the MySQL server start on computer startup. \u00a0This means it&#8217;s always running and hands off!<\/p>\n<figure id=\"attachment_1267\" aria-describedby=\"caption-attachment-1267\" style=\"width: 500px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/Screen-Shot-2015-12-20-at-10.29.05-AM.png\" rel=\"attachment wp-att-267\" data-lasso-id=\"583\" data-rel=\"lightbox-gallery-dlKcHyYE\" data-rl_title=\"\" data-rl_caption=\"\" title=\"\"><img decoding=\"async\" class=\"size-medium wp-image-1267\" src=\"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/Screen-Shot-2015-12-20-at-10.29.05-AM-500x252.png\" alt=\"Run MySQL from System Prefences\" width=\"500\" height=\"252\" srcset=\"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/Screen-Shot-2015-12-20-at-10.29.05-AM-500x252.png 500w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/Screen-Shot-2015-12-20-at-10.29.05-AM-150x76.png 150w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/Screen-Shot-2015-12-20-at-10.29.05-AM-600x302.png 600w, https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/Screen-Shot-2015-12-20-at-10.29.05-AM.png 689w\" sizes=\"(max-width: 500px) 100vw, 500px\" \/><\/a><figcaption id=\"caption-attachment-1267\" class=\"wp-caption-text\">Start or stop the MySQL server from system preferences on OS X.<\/figcaption><\/figure>\n<h2>Installation Location<\/h2>\n<p>The MySQL files, including data, are installed at:<\/p>\n<pre><code>\/usr\/local\/mysql\/<\/code><\/pre>\n<p>Note: \u00a0The directory <em>mysql<\/em> is a symbolic link to the actual MySQL directory which includes the version number.<\/p>\n<p>The data files are located in:<\/p>\n<pre><code>\/usr\/local\/mysql\/data\r\n<\/code><\/pre>\n<p>Each database has it&#8217;s own folder.<\/p>\n<p>The biggest thing to note here is that the \/usr directory is a hidden directory. \u00a0There are two easy ways to access it:<\/p>\n<ul>\n<li>If you are comfortable with using Terminal for command line operations, that&#8217;s the quickest and easiest way to access these files<\/li>\n<li>If you&#8217;d like to use Finder, choose\u00a0<strong>Go -&gt; Go To Folder<\/strong> and type in <strong>\/usr\/local<\/strong>. \u00a0You can then access the MySQL directory and files.<\/li>\n<\/ul>\n<h2>File Permissions<\/h2>\n<p>By\u00a0default, MySQL data is owned by the user\u00a0<strong>_mysql<\/strong> and group. \u00a0The permissions are set to 750 (User: read\/write\/execute, Group: read\/execute, Everyone: no access). \u00a0For this reason, you won&#8217;t be able to view these files via Finder as these\u00a0you are most likely logged in as a different user to OS X.<\/p>\n<p>To view the files, you\u00a0need to modify the permissions first. \u00a0Via command line you can use chmod and set them to 755.<\/p>\n<pre><code># Move to data directory\r\ncd \/usr\/local\/mysql\/data\r\n\r\n# Change permission on the database directory\r\nsudo chmod -R 755 database_directory_name\/<\/code><\/pre>\n<p>NOTES:<\/p>\n<ul>\n<li>sudo will give you admin permissions to make this change and will require the system password<\/li>\n<li>-R will recursively set these permissions on all of the child\u00a0files and directories.<\/li>\n<\/ul>\n<h2>Moving Data<\/h2>\n<p>As I said above, I&#8217;m moving from MAMP databases to the native MySQL and for that reason I&#8217;m moving the databases from the MAMP directory to the native directory. \u00a0It is possible to configure MySQL to use a different data location, however I felt this was the best approach I should take so I could fully move away from MAMP once this conversion is complete.<\/p>\n<p>You could use the finder to copy the data from one location to another but I&#8217;m going to highlight how to do this via Terminal because I really think the command line approach is easiest for this task.<\/p>\n<pre><code># Create the destination directory\r\nmk dir \/usr\/local\/mysql\/data\/database_directory_name\/\r\n\r\n# Copy a database from MAMP's data location to the Native data location\r\nsudo cp \/Applications\/MAMP\/db\/mysql\/database_directory_name\/* \/usr\/local\/mysql\/data\/database_directory_name\/\r\n<\/code><\/pre>\n<ul>\n<li class=\"p1\">sudo gives you admin access<\/li>\n<li class=\"p1\">cp is the command to perform the copy<\/li>\n<li class=\"p1\">Provide\u00a0the source directory path\u00a0followed by an asterisk (*) which means all files<\/li>\n<li class=\"p1\">Provide the destination directory path<\/li>\n<\/ul>\n<pre><code># Change ownership\r\nsudo chmod -R _mysql:_mysql database_directory_name\/\r\n\r\n# Change permissions\r\nsudo chmod -R 755 database_directory_name\/\r\n<\/code><\/pre>\n<p>NOTE: 750\u00a0are the permissions that MySQL will set by default. \u00a0Using 755 allows your OS X user have access to the data as well. \u00a0I don&#8217;t advise using this on a production server however.<\/p>\n<h2>Accessing MySQL Databases<\/h2>\n<p>It is possible to access MySQL data via the command line. \u00a0I do not prefer that personally and thus will not touch on it here.<\/p>\n<p>I prefer to use <a href=\"https:\/\/dev.mysql.com\/downloads\/workbench\/\" target=\"_blank\" data-lasso-id=\"584\" rel=\"noopener\">MySQL Workbench<\/a>. \u00a0MySQL Workbench is desktop software that is provided by Sun (owners of MySQL) that provides database access and management\u00a0as well server administration. \u00a0It&#8217;s great software.<\/p>","protected":false},"excerpt":{"rendered":"<p>When I first wanted to setup a locally hosted PHP\/MySQL stack on my MacBook, I came across MAMP [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1270,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"wprm-recipe-roundup-name":"","wprm-recipe-roundup-description":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[150,5],"tags":[],"class_list":["post-1266","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","category-website-development"],"jetpack_featured_media_url":"https:\/\/promincproductions.com\/blog\/wp-content\/uploads\/2015\/12\/MySQL-on-OS-X-Without-MAMP.jpg","jetpack_shortlink":"https:\/\/wp.me\/p4BbcR-kq","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/posts\/1266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/comments?post=1266"}],"version-history":[{"count":3,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/posts\/1266\/revisions"}],"predecessor-version":[{"id":1271,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/posts\/1266\/revisions\/1271"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/media\/1270"}],"wp:attachment":[{"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/media?parent=1266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/categories?post=1266"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promincproductions.com\/blog\/wp-json\/wp\/v2\/tags?post=1266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}