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.
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 – I don’t need to start an application for PHP and MySQL to be running. Likewise, this will give me the freedom to manage the two servers as I wish without needing to deal with what MAMP has predefined.
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’d document them here.
Contents
Running MySQL
There is a MySQL installation package available. Once installed, you can start/stop MySQL from the command line using Terminal or from the system preferences. Even better, you can have the MySQL server start on computer startup. This means it’s always running and hands off!
Installation Location
The MySQL files, including data, are installed at:
1 | /usr/local/mysql/ |
Note: The directory mysql is a symbolic link to the actual MySQL directory which includes the version number.
The data files are located in:
1 2 | /usr/local/mysql/data |
Each database has it’s own folder.
The biggest thing to note here is that the /usr directory is a hidden directory. There are two easy ways to access it:
- If you are comfortable with using Terminal for command line operations, that’s the quickest and easiest way to access these files
- If you’d like to use Finder, choose Go -> Go To Folder and type in /usr/local. You can then access the MySQL directory and files.
File Permissions
By default, MySQL data is owned by the user _mysql and group. The permissions are set to 750 (User: read/write/execute, Group: read/execute, Everyone: no access). For this reason, you won’t be able to view these files via Finder as these you are most likely logged in as a different user to OS X.
To view the files, you need to modify the permissions first. Via command line you can use chmod and set them to 755.
1 2 3 4 5 | # Move to data directory cd /usr/local/mysql/data # Change permission on the database directory sudo chmod -R 755 database_directory_name/ |
NOTES:
- sudo will give you admin permissions to make this change and will require the system password
- -R will recursively set these permissions on all of the child files and directories.
Moving Data
As I said above, I’m moving from MAMP databases to the native MySQL and for that reason I’m moving the databases from the MAMP directory to the native directory. It 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.
You could use the finder to copy the data from one location to another but I’m going to highlight how to do this via Terminal because I really think the command line approach is easiest for this task.
1 2 3 4 5 6 | # Create the destination directory mk dir /usr/local/mysql/data/database_directory_name/ # Copy a database from MAMP's data location to the Native data location sudo cp /Applications/MAMP/db/mysql/database_directory_name/* /usr/local/mysql/data/database_directory_name/ |
- sudo gives you admin access
- cp is the command to perform the copy
- Provide the source directory path followed by an asterisk (*) which means all files
- Provide the destination directory path
1 2 3 4 5 6 | # Change ownership sudo chmod -R _mysql:_mysql database_directory_name/ # Change permissions sudo chmod -R 755 database_directory_name/ |
NOTE: 750 are the permissions that MySQL will set by default. Using 755 allows your OS X user have access to the data as well. I don’t advise using this on a production server however.
Accessing MySQL Databases
It is possible to access MySQL data via the command line. I do not prefer that personally and thus will not touch on it here.
I prefer to use MySQL Workbench. MySQL Workbench is desktop software that is provided by Sun (owners of MySQL) that provides database access and management as well server administration. It’s great software.
