Difference between revisions of "MySQL Workbench"
(→How do I connect to my database using MySQL Workbench?) |
(→How do I connect to my database using MySQL Workbench?) |
||
| Line 24: | Line 24: | ||
#Under Databases go to Remote MySQL. <br /> [[File:Mysqlcpanelicon.png|350px|x3 Theme]] [[File:Mysqlcpanelicon_paper.png|350px|Paper Lantern Theme]] | #Under Databases go to Remote MySQL. <br /> [[File:Mysqlcpanelicon.png|350px|x3 Theme]] [[File:Mysqlcpanelicon_paper.png|350px|Paper Lantern Theme]] | ||
#If you have a specific static IP you want to grant remote access to, you can enter it here and click on "Add Host". Otherwise you can grant remote access from any IP by entering % as the host and click on Add Host. <br /> [[File:Mysqlremoteaccess.png|350px|x3 Theme]] [[File:Mysqlremoteaccess_paper.png|350px|Paper Lantern Theme]] | #If you have a specific static IP you want to grant remote access to, you can enter it here and click on "Add Host". Otherwise you can grant remote access from any IP by entering % as the host and click on Add Host. <br /> [[File:Mysqlremoteaccess.png|350px|x3 Theme]] [[File:Mysqlremoteaccess_paper.png|350px|Paper Lantern Theme]] | ||
| + | |||
| + | === Configuring MySQL Workbench === | ||
| + | |||
| + | # Open MySQL Workbench on your local machine | ||
| + | # Click on the (+) icon <br /> [[File:Mysqlworkbench.png|350px]] | ||
| + | # Fill in the connection details and you can optionally store your password in the vault. <br /> [[File:Mysqlworkbenchlogin.png|350px]] | ||
==How to use MySQL Workbench to backup your MySQL database== | ==How to use MySQL Workbench to backup your MySQL database== | ||
Revision as of 21:47, 16 March 2015
Contents
What is MySQL Workbench?
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, and much more. MySQL Workbench is available on Windows, Linux and Mac OS.
Where can I get MySQL Workbench?
You can obtain a copy of MySQL Workbench here: MySQL Workbench Download
How do I connect to my database using MySQL Workbench?
Before connecting connecting to your database using MySQL Workbench, you'll have to first get the host name or the IP address of the Hostek MySQL server your database is on.
Windows
- Login to your control panel
- Under Databases, Click on MySQL.
- You can find the host next to the database name.
Linux
You should be able to use your domain name as the host. However in order to connect externally to your database with programs such as MySQL workbench you'll need to enable remote MySQL access to your database.
Depending on your cPanel theme, the location and text may be different. The screenshots below are from the themes x3 and paper lantern.
- Login to your cPanel
- Under Databases go to Remote MySQL.
- If you have a specific static IP you want to grant remote access to, you can enter it here and click on "Add Host". Otherwise you can grant remote access from any IP by entering % as the host and click on Add Host.
Configuring MySQL Workbench
- Open MySQL Workbench on your local machine
- Click on the (+) icon
- Fill in the connection details and you can optionally store your password in the vault.
How to use MySQL Workbench to backup your MySQL database
Once you have installed MySQL Workbench launch it and under the "Server Administration" click on "New Server Instance". This will pull up a new Instance wizard.
Under the "Specify the Host Machine the Database Server is running on" click on "Remote Host" and for the address put an IP of the server that your database is on.
Under the "Set the Database Connection values" screen you can leave "Connection Name" the same as the server IP or change it to something more meaningful like "My wordpress database server". Leave "Connection Method" as "Standard (TCP/IP)". Under the "Parameters" tab make sure that hostname matches the IP from the previous step. Leave port as "3306" and enter your database username. Click on "Store in Vault" and enter the password for the above user. Leave "Default Schema" empty".
Testing the connection should give you three check marks and show "Database connection tested successfully." Click on "Next".
On "Set remote configuration parameters" UNCHECK "Enable SSH login based administration" and click on "Next".
Under "Create the Instance Profile" you can use something like "My backup" and click "Finish".
After the steps above your "Workbench Central" should now have new connection listed under the "Server Administration" on the right. Click on "Manage Import / Export" link and then from the pop-up window click the instance you created earlier.
Under the "Export to Disk" tab place a check-mark next to your database name. Under "Options" to the right check "Export to Self-Contained File" and click on "Start Export".
How to use MySQL Workbench to Restore your MySQL database
Once you have installed MySQL Workbench launch it and under the "Server Administration" click on "New Server Instance". This will pull up a new Instance wizard.
Under the "Specify the Host Machine the Database Server is running on" click on "Remote Host" and for the address put an IP of the server that your database is on.
Under the "Set the Database Connection values" screen you can leave "Connection Name" the same as the server IP or change it to something more meaningful like "My wordpress database server". Leave "Connection Method" as "Standard (TCP/IP)". Under the "Parameters" tab make sure that hostname matches the IP from the previous step. Leave port as "3306" and enter your database username. Click on "Store in Vault" and enter the password for the above user. Leave "Default Schema" empty".
Testing the connection should give you three check marks and show "Database connection tested successfully." Click on "Next".
On "Set remote configuration parameters" UNCHECK "Enable SSH login based administration" and click on "Next".
Under "Create the Instance Profile" you can use something like "My backup" and click "Finish".
After the steps above your "Workbench Central" should now have new connection listed under the "Server Administration" on the right. Click on "Manage Import / Export" link and then from the pop-up window click the instance you created earlier. You may need to enter your database password.
You will select "Import from Dump Project Folder" and you can select the location through the "Folder Path" field. You will then click on the Load Folder Contents Button
Click on the Start Import button, you may be asked for the database password again. The length of time it takes for the import depends on the size of the backup of your database.