Overview of How to Connect Database to MySQL?
Before we discuss how to Connect Database to MySQL. we will see the introduction of MySQL. MySQL is free and an open-source database. MySQL is a relational database management system (RDBMS).
MySQL is open-source and free software under the terms of the General Public License (GNU), and also available under licenses proprietary. MySQL developed by the Swedish company MySQL AB and later bought by Sun Microsystems as now Oracle Corporation.
MySQL is used in many web application which is based on database-driven like WordPress which is on demand now, Drupal, phpBB and Joomla, also MySQL is used by many popular websites, like Twitter, Facebook, YouTube, and Flickr.
Features of MySQL
MySQL is offered under two different editions: the open source MySQL Community Server and the proprietary Enterprise Server. MySQL Enterprise Server is differentiated by a series of proprietary extensions which installs as server plugins, but otherwise shares the version numbering system and is built from the same code base.
Major features as available in MySQL 5.6:
- It supports cross-platform.
- Can create stored procedures, using a procedural language.
- Can create Triggers.
- Can create Cursors.
- Views can be updated.
- With the help of InnoDB Storage Engine can create Online Data Definition Language (DDL) also using InnoDB and NDB Cluster Storage Engines can do ACID compliance.
- It supports built-in replication like Asynchronous replication, Semi-synchronous replication, Synchronous replication, Virtual Synchronous.
- It also supports full-text search and indexing and partitioned tables in an optimizer to store the huge data and to run in the less execution time.
- The Federated, Archive, InnoDB, Memory (heap), MyISAM, Merge, Blackhole, CSV, and NDB Cluster are native storage engines.
To connect to the MySQL database first we will see the steps to install the MySQL, download the MySQL workbench from this URL: https://www.mysql.com/products/workbench/
We will create a new Connect database to MySQL after installation:
To create a new connection may be an initial connection or an additional connection. An instance of MySQL server must be installed, must be started, and must be accessible to MySQL Workbench before to create a new connection.
To create a new connection, follow these steps:
Step 1: Start MySQL Workbench by the double clock on it. Once it is open you will see MySQL Connections option, if you click on it then you see the existing connections else no connections exist if it is the first time using as in the below figure.
MySql workbench screen
Step 2: Click the [+] icon from the MySQL Workbench screen which is near MySQL Connections and then open-label option Setup New Connection wizard. Provide all the details which are asking in the Setup New Connection wizard Connection, connection name, for example, we will give “MyFirstConnection” and keep all other fields as the default values and then click Test Connection button. If the test connection shows successful then go to create the connection by clicking ok button.
create new connection screen
Step 3: Further if you want to do some configuration setting then click on Configure Server Management button and provide the details like the location of configuration files, SSH login based management, the correct start and stop commands to use for the connection, native windows remote management all those setting can be done depending on the requirements.
Status of the server
Once you click on the ok button it displays you the below image.
Step 4: In this window, it asks for the password, so provide the password and click ok.
After clicking on ok, it displays window to show message connection parameters are correct then again click the ok button.
Window display correct parameters message
After ok then you come back to the connection window, click again ok button.
Now it shows you the list of connection available under SQL development section, where you will find your just created new connection as well, so click on your newly created connection.
Now you will see the databases list in the area on the left.
Now it opens the connection with object browser and the query editor. The object browser shows you all the list of database and once you click on the database further you can see the list of all the tables available in that particular database, view available and routines and so all. The query editor window uses to write the query and execute on the database, so for this, you need to select the query and click on the run command.
The alternative way to connect to the MySQL database is through the command line, so next, we will see how to connect the database to MySQL.
We need to perform the following steps to connect to the MySQL database –
First, log in to your account A2 Hosting using SSH. Then open the command line, to open command line click start button from keyboard then type cmd and press enter it to open the black wind.
Next type the following command.
mysql -u name –p
Replace name with your username and click enter it shows Password prompt so type your password. Now the mysql>prompt appears once you type the correct password, so now you are connected to MySQL database.
Next, if you want to see list of all available database then type at the mysql> prompt type following command.
mysql> show databases;
After knowing all the available databases if you want to access a specific database then at the mysql> prompt type the following command.
use database name;
Replace database name with your accesses database name.
Now you are inside of the database you can run the SQL query on the database like creating a table, accessing the table, creating the view and so on.
For example, consider the query to create a table:
CREATE TABLE employee (
id INT(6) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(20) )
Farther if you need a list of command and syntax then you can take the help at the mysql> prompt by typing help and to exit the MySQL program at the mysql> prompt \q type.
1. Download and install MySQL workbench from this URL https://www.mysql.com/products/workbench/.
2. Start MySQL Workbench by the double clock on it, provide the connection name and click ok.
3. Click on your connection name and writing the query in the query editor.
4. An alternative way to connect the database to MySQL is through the command line. Type following command in sequence at the command prompt.
mysql -u name –p
mysql> show databases;
use database name;
Then type and run the required SQL queries.
This has been a guide on how to Connect Database to MySQL. Here we discussed the Introduction, Features, and Installation of Connect Database to MySQL. You can also go through our other suggested articles to learn more –