Joomla MAMP

FOUR: Exporting Your Live Server SQL Database For Use On Your Localmachine Site

The following article was originally published 15 June 2017. Both MAMP and Joomla versions have both advanced since then but the basic fundamentals of using MAMP and Joomla remain the same to this day.


phpMyAdmin logo

This is Part 4 of Web Development On Your Macintosh Computer Using MAMP and Joomla! CMS.

Now that we have a copy of Joomla! running on our localhost machine we can get the information and images from the live server so that our two sites are synchronized. We'll do the export of the live site data with phpMyAdmin. This tool is usually available on your server through your cpanel or you can have phpMyAdmin as a stand alone install on your server. This IS NOT the phpMyAdmin that is in your MAMP program. This phpMyAdmin has to be residing on the server where your live site is. Hence you have a version of phpMyAdmin in MAMP which is your localmachine site and also one on the live server. In my cpanel I have to click on MySQL databases icon to I find a link to phpMyAdmin which is on the next screen at the very bottom. If you do not have phpMyAdmin available for you it is something that you should install. It is a very valuable tool that you can upload to the server. If you do install phpMyAdmin on your live site you must ensure that you protect the directory where it is found. You do not want anyone else having access to phpMyAdmin on your site since it allows users to make, delete, and edit databases on your server.

Article continues below...

Do you need help for your Joomla website?

We offer Joomla training and teach Joomla best practices so you get the most out of your website.

We also provide Joomla optimized web hosting with site security, website monitoring, maintenance and updates.

Click the 'Request Joomla Help' button and fill out the request form.

Go to the phpMyAdmin screen of your live site. You either got there via your cpanel or directly through the URL of your browser. Either way, ensure that it is password protected. You had to login to cpanel so that way is protected, but if you have it installed so that you can get there directly through the browser URL, make sure that directory is password protected. You don't want this tool loose where anyone can use it.

When you are on the phpMyAdmin page you will see a left column that has a drop down menu underneath a heading that says 'Database'. From the dropdown menu select your database. You may see several SQL databases listed there. The one you want is the Joomla! database. You need to know the name of your database. Select it and then the page will refresh taking you to a screen where you should see lots of table listings with the prefix 'jos_' (without the quotes) on the page. If you are seeing that then you are in the right place.

In the main body of the page you'll see your database name and underneath will be some headings such as 'Structure', 'SQL', 'Search', 'Query', 'Export', 'Import', and 'Operations'. Click on the link that says 'Export'. The page will refresh and you will see in the main body "View dump (schema) of database'. There will be a column that says 'Export' and there is a link that says 'Select All/Unselect All'. Make sure the button that is selected is the 'SQL' button. Click the 'Select All' link. On the right side are SQL options. There likely will be a checkmark in the box that says 'Structure', and below that two check boxes; one for "Add AUTO_INCREMENT value' and "Enclose table and field names with backquotes'. Beneath that one will be a checkmark in the box 'Data', also checked will be 'Complete inserts', 'Extended inserts'. I have a field showing below that, with a label beside it saying 'Maximal length of created query', with the number 50000 in it. Also there is a checkmark for the box 'Use hexadecimal for binary fields'. Finally there is a drop down beside a label "Export type' that says 'INSERT'.

Now there is an unchecked box that you must check that says 'Save as file' near the bottom of the page. Check that box. You can add compression to the export if you have a large site but in my case I left it marked as 'None'. I left the 'File name template' field alone. It has '__DB__' in it. The export will name the file for you.

If you got this far and everything looks good then click the 'Go' button on the bottom right side of the page. When I click this button it saves an exported file to my hard drive. This is the file you are going to import into your localmachine site. In some cases when the screen refreshes a window opens up with the SQL export data as text content. If this is the case for you then simply copy (select all) and paste this text into a text document. A TextEdit document, TextWrangler, BBEdit, or any of those kinds of text files will be fine. Save that text document somewhere on your hard drive where you will be able to find it for your localmachine import.

Coming up next, getting that live site export into your localmachine site.