Difference between revisions of "MySQL Workbench"

From Hostek.com Wiki
Jump to: navigation, search
(→‎How do I connect to my database using MySQL Workbench?)
 
(2 intermediate revisions by the same user not shown)
Line 18: Line 18:
  
 
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.  
 
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.  
−
#Login to your cPanel
 
−
#Under Databases go to Remote MySQL. <br /> 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.
 
  
−
==How to use MySQL Workbench to backup your MySQL database==
+
Depending on your cPanel theme, the location and text may be different. The screenshots below are from the themes x3 and paper lantern.
−
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.
+
  
 +
#Login to your cPanel
 +
#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]]
  
−
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.
+
=== 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]]
  
−
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".
+
==How to use MySQL Workbench to backup your MySQL database==
  
 +
[[File:Mysqlworkbenchexportmenu.png|350px]] [[File:Mysqlworkbenchdataexport.png|350px]]
  
−
Testing the connection should give you three check marks and show "Database connection tested successfully." Click on "Next".
+
# Click on Server in the menu and then click on Data Export  
−
 
+
# Click the box next to the Schema (also known as a database) that you are wanting to export. (Optionally, you can change the location where the file is going to be dumped)
−
 
+
# Press the 'Start Export' button
−
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==
 
==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
 
  
 +
[[File:mysqlworkbenchimportmenu.png|350px]] [[File:Mysqlworkbenchdataimport.png|350px]]
  
−
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.
+
# Click on Server in the menu and then click on Data Import <br />
 +
# Click on the button next to the "Import From Self-Contained File" textbox and select the MySQL dump file you want to import and select the target schema (also known as a database) or alternatively you can create a new schema.
 +
# Press the 'Start Import' button
  
  
 
[[Category:Databases-MySQL]]
 
[[Category:Databases-MySQL]]

Latest revision as of 22:26, 16 March 2015


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

  1. Login to your control panel
  2. Under Databases, Click on MySQL.
    Mysqlicon.png
  3. You can find the host next to the database name.
    Mysqlscreen.png

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.

  1. Login to your cPanel
  2. Under Databases go to Remote MySQL.
    x3 Theme Paper Lantern Theme
  3. 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.
    x3 Theme Paper Lantern Theme

Configuring MySQL Workbench

  1. Open MySQL Workbench on your local machine
  2. Click on the (+) icon
    Mysqlworkbench.png
  3. Fill in the connection details and you can optionally store your password in the vault.
    Mysqlworkbenchlogin.png

How to use MySQL Workbench to backup your MySQL database

Mysqlworkbenchexportmenu.png Mysqlworkbenchdataexport.png

  1. Click on Server in the menu and then click on Data Export
  2. Click the box next to the Schema (also known as a database) that you are wanting to export. (Optionally, you can change the location where the file is going to be dumped)
  3. Press the 'Start Export' button


How to use MySQL Workbench to Restore your MySQL database

Mysqlworkbenchimportmenu.png Mysqlworkbenchdataimport.png

  1. Click on Server in the menu and then click on Data Import
  2. Click on the button next to the "Import From Self-Contained File" textbox and select the MySQL dump file you want to import and select the target schema (also known as a database) or alternatively you can create a new schema.
  3. Press the 'Start Import' button
Retrieved from "https://wiki.hostek.com/index.php?title=MySQL_Workbench&oldid=2438"