| |
Using MySQL Workbench To Copy/View/Edit/Test A Remote Website Locally
By Alex Trent
Expert Author
Article Date: 2010-05-06
This guide is designed to be used for Linux desktops and servers. For best results, use the same Linux version for the desktop as is installed on the server.
To quote the authors:
MySQL Workbench is a cross-platform, visual database design tool developed by MySQL. It is the highly anticipated successor application of the DBDesigner4 project. MySQL Workbench will be available as a native GUI tool on Windows, Linux and OS X.
http://wb.mysql.com/?page_id=6
Take a look at the screenshots here:
http://wb.mysql.com/?page_id=35
Exporting the database and website
Use the following command to export the database from the server:
?mysqldump -u USER -p DATABASE > FILENAME.sql
Then zip the folder of the website, using the command:
tar czvf WEBSITEFOLDERNAME.tgz WEBSITEFOLDERNAME
Now copy the SQL file and the tar/gzipped website from the server to your Linux Desktop.
Be sure to copy any config files for the webserver from /etc/apache2 and /etc/php5.
Importing the database
Install MySQL Server:
On Debian/Ubuntu systems use: sudo aptitude install mysql-server
See the MySQL website for other versions of Linux:
http://dev.mysql.com/downloads/mysql/
Import the SQL file into the database:
?mysql -u USER -p DATABASE < FILENAME.sql
Download and install the latest release of MySQL Workbench here:
http://dev.mysql.com/downloads/workbench/5.2.html
Viewing/Editing the data
Open MySQL Workbench and follow the steps to setup a connection to local host:
http://www.ghacks.net/2009/12/25/complete-database-administration-package-mysql-workbench/
In MySQL Workbench viewing/editing a table is as simple as right clicking on the table in the object window and left clicking on ‘Edit Table Data‘ in the pop-up menu.
Regular SQL queries can be typed in the ‘SQL Query' window.
If the database can be accessed and edited, then move on to setting up the website.
Loading the Website
Apache2 and PHP5 will need to be installed:
sudo aptitude install php5 apache2
Backup your config files for your desktop in /etc/apache2 and /etc/php5. Then copy the config files for your website to the appropriately corresponding folders in /etc/apache2 and /etc/php5. As long as your Linux version on your server and desktop are the same or very similar, it should be this easy as long as there are not any other dependencies other than php5 for the website to function properly. If versions are different it may take some effort, but comparing/editing the config files to match enough to get the site working should be possible.
Extract and copy the website data from your TGZ file to a path/directory structure matching that on the server.
One last step must be taken to ensure that the local website does not interfere with a remote live version. The hosts file must be editited to redirect all requests for your website to localhost. To accomplish this open the /etc/hosts file with gedit/nano/vim and add a line at the end following this example for http://www.sqlpronews.com/
/etc/hosts
.....other stuff already in the file......
127.0.0.1 www.sqlpronews.comsqlpronews
Then the website should be viewable in a web browser by just typing ‘localhost' in the address bar. If the website can be accessed then the SQL data can now be edited using MySQL Workbench and the results will be immediately accessible!
Now, you can test changes and they will not affect users of a live website. Don't forget to remove the line from the /etc/hosts file so that you can view the live website again when needed.
About the Author:
Alex Trent is a staff writer for WebProNews
|
|
