This article is related to the article “How to get a full backup and database export of your marketplace”, which you can read here. It is also assumed that you already have the database export available. After reading this article, you should have a good overview of how to access the export file.
These instructions have been written primarily for MacOS 10.14 (Mojave), but they should be compatible with most modern MacOS computers. If you have an older (5-10 or more years old) laptop with an older operating system, you might run into some compatibility issues. In this case, please contact firstname.lastname@example.org, and we will do our best to help.
After you have requested and received a database export file from us, the first thing you need to do is to unpack the compressed .zip file into a subfolder. macOS comes with pre-installed software that can do this on. You need to double-click the zip file.
After you’ve uncompressed the file, you’ll recognize that the zip file contains the readme, the images folder with all the images of your marketplace, and the <marketplacename>_export.sql file, which is the database file that has the rest of the content of your marketplace. This SQL file has, for example, all the information about your marketplace’s listings, user accounts, and more.
The .sql file is a so-called database file created in MySQL, an open-source relational database management system (you can read more here if you’re interested). You can open the .sql file in any normal text editor, but it can be difficult to interpret, especially if you have not worked with databases before. As such, even though it requires a bit of preparation, it’s usually a better idea to install proper database software and use that to open the file.
Instructions for macOS
- Required software
- Installing the requirements
- Creating a blank database in MySQL
- Importing your own database file
- Browsing the database
1. Required software
- MySQL Community Server 5.7
- Sequel Pro database management application
2. Installing the requirements
MySQL Community Server 5.7:
- Go to https://dev.mysql.com/downloads/mysql/5.7.html#downloads
- Note: The link above takes you to the download page of MySQL 5.7. It is important that you download MySQL version 5.7! The newest versions, from 8.0., are not compatible with this process.
- Select “5.7.26” as the version from the drop-down menu.
- Select “macOS” as the operating system from the drop-down menu.
- Select the file that has a description ending with “DMG Archive” (this may be called “macOS 10.14 (x86, 64-bit), DMG Archive”) and click on “Download”.
- Download and open the DMG file.
- Double-click on the PKG file inside the archive, and follow the installation instructions. Warning: do NOT dismiss the last dialogue box that shows you a password!
- When shown the automatically-generated password for the “root” user at the end of the setup, be sure to write it down! It is required to access the database. When you have the password saved, you can finish the installer.
- Done! MySQL Community Server is now installed.
Sequel Pro database management application:
- Go to https://www.sequelpro.com/
- Click on the button labeled “DOWNLOAD” - this will get you the latest version.
- After the download has finished, locate and open the downloaded .dmg file; a new window should open when you do this.
- Copy (or drag+drop) the Sequel Pro app in your mac’s Applications folder.
- Done! Sequel Pro is now installed.
3. Creating a blank database in MySQL
To import/open the database file, you’ll need first to have an existing, blank database file running on your own local database. The SQL file is then imported on top of this blank file.
1. Open the System Preferences menu (more info here)
2. Select “MySQL” from the preferences menu
3. If the MySQL server status is “stopped”, click on “Start MySQL Server”
4. Open the terminal on your MacBook (step-by-step guide here)
5. Enter this command, exactly as written:
/usr/local/mysql/bin/mysql -uroot -p
6. Enter your root password, which you got at the final step of installing the MySQL server. If you lost your root password, you need to reset it, which can be a bit tricky. You can read some instructions here or search for “MySQL 5.7 reset root password” on Google.
7. When you are logged in and see the prompt that says “mysql>”, enter this command, but change ‘yourpassword’ to a secure password!:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘yourpassword’;
You should see “Query OK, 0 rows affected (0,00 sec)”
NOTE: Changing the password from ‘yourpassword’ is important. Leaving any default weak password as your root password is a security risk.
NOTE: If you try copy-pasting the command, it may not function since sometimes the terminal doesn’t understand copy-pasted quotation marks. If the command doesn’t work when copy-pasting, try writing it from scratch.
8. Enter this command, exactly as written:
create database marketplace;
You should see “Query OK, 1 row affected (0,00 sec)”.
9. Enter this command, exactly as written:
You should see “Database changed”.
4. Importing your own database file
Locate your data export file, which ends in .sql. If you downloaded the archive to your “Downloads” folder and unpacked it there, the path to the folder may be something like the following (replace the references to your username, marketplace name, and the number):
Note: There are two things you need to change here to get the correct path.
- You need to enter your own Mac username here, which you can find if you open a new terminal window and type “pwd”. You should see a line that says “/Users/YOURUSERNAME”, where the second part is your actual username.
- You need to change the folder name to whatever folder you unpacked the zip file's contents in. Of course, if you unpacked it in a different folder, you need to copy that folder structure instead of the template mentioned above.
Then, it's time to import the file.
Type the following command in the MySQL prompt. You need to change 2 things in the command below:
- Replace the word “PATH” with the full path you got from the step above (the one where you replaced 'YOURUSERNAME'), and:
- Replace “YOURMARKETPLACE” with your marketplace name. This is usually the original name of your marketplace, or the first part of the URL address of your trial marketplace, which was in the form of "https://YOURMARKETPLACE.sharetribe.com". If needed, you can check the exact file name of the .sql file from the folder where you uncompressed the backup in the first place.
Here's the command for the MySQL prompt (make sure you're in the prompt, and you replaced the 2 things mentioned above):
You should see several rows worth of entries come up very quickly, saying things such as “Query OK, 0 rows affected (0.00 sec)”. This means everything worked well, and the database is now online and accessible with Sequel Pro or any other SQL client!
5. Browsing the database
- Open Sequel Pro
- Select QUICK CONNECT
- Add the following information:
- Name: localhost
- Host: 127.0.0.1
- Username: root
- Password: The password that you changed in step 3.7
- Leave the other fields blank or with the default values
- Click the Connect button
- Once connected, go to the dropdown menu on the top left labeled “Choose Database” and from the menu select the option “marketplace”
That’s it! You can now browse through the Database. Bear in mind that some tables might be empty due to the nature of the Database dump.