What is MySQL?

First things first, you have to know how to pronounce it: MY-ES-KYOO-EL’ [maɪˌɛsˌkjuːˈɛl]. Sometimes people call it “my sequel” or other names, but at least you know the official pronunciation. A Swedish company called MySQL AB originally developed MySQL in 1994. The US tech company Sun Microsystems then took full ownership when they bought MySQL AB in 2008. US tech giant Oracle in 2010 acquired Sun Microsystems itself, and MySQL has been practically owned by Oracle since.

In regard to the general definition, MySQL is an open source relational database management system (RDBMS) with a client-server model. RDBMS is a software or service used to create and manage databases based on a relational model. Now, let’s take a closer look at each term:

How Does MySQL Work?

The image explains the basic structure of the client-server structure. One or more devices (clients) connect to a server through a specific network. Every client can make a request from the graphical user interface (GUI) on their screens, and the server will produce the desired output, as long as both ends understand the instruction. Without getting too technical, the main processes taking place in a MySQL environment are the same, which are:

1. MySQL creates a database for storing and manipulating data, defining the relationship of each table.
2. Clients can make requests by typing specific SQL statements on MySQL.
3. The server application will respond with the requested information and it will appear on the clients’ side.

That’s pretty much it. From the clients’ side, they usually emphasize which MySQL GUI to use. The lighter and more user-friendly the GUI is, the faster and easier their data management activities will be. Some of the most popular MSQL GUIs  are MySQL WorkBench, SequelPro, DBVisualizer, and the Navicat DB Admin Tool. Some of them are free, while some are commercial, some run exclusively for macOS, and some are compatible with major operating systems. Clients should choose the GUI depending on their needs. For web database management, including a wordpress site the most obvious go-to is phpMyAdmin.

Database

A database is simply a collection of structured data. Think of taking a selfie: you push a button and capture an image of yourself. Your photo is data, and your phone’s gallery is the database. A database is a place in which data is stored and organized. The word “relational” means that the data stored in the dataset is organized as tables. Every table relates in some ways. If the software doesn’t support the relational data model, just call it DBMS.

Open source

Open source means that you’re free to use and modify it. Anybody can install the software. You can also learn and customize the source code to better accommodate your needs. However, The GPL GNU public license) determines what you can do depending on conditions. The commercially licensed version is available if you need more flexible ownership and advanced support.

Client-server model

Computers that install and run RDBMS software are called clients. Whenever they need to access data, they connect to the RDBMS server. That’s the “client-server” part.

MySQL is one of many RDBMS software options. RDBMS and MySQL are often thought to be the same because of MySQL’s popularity. A few big web applications like Facebook, Twitter, YouTube, Google, and Yahoo! all use MySQL for data storage purposes. Even though it was initially created for limited usage, it is now compatible with many important computing platforms like Linux, macOS, Microsoft Windows, and Ubuntu.

SQL

Msql and Sql are not the same, Be aware that MySQL is one of the most popular RDBMS software’s brand names, which implements a client-server model. So, how do the client and server communicate in an RDBMS environment? They use a domain-specific language – Structured Query Language (SQL). If you ever encounter other names that have SQL in them, like PostgreSQL and Microsoft SQL server, they are most likely brands which also use Structured Query Language syntax. RDBMS software is often written in other programming languages, but always use SQL as their primary language to interact with the database. MySQL itself is written in C and C++.

Computer scientist Ted Codd developed SQL in the early 1970s with an IBM based relational model. It became more widely used in 1974 and quickly replaced similar, then-outdated languages, ISAM and VISAM. History aside, SQL tells the server what to do with the data. It is similar to your WordPress password or code. You input it into the system to gain access to the dashboard area. In this case, SQL statements can instruct the server to perform certain operations:

• Data query: requesting specific information from the existing database.
• Data manipulation: adding, deleting, changing, sorting, and other operations to modify the data, the values or the visuals.
• Data identity: defining data types, e.g. changing numerical data to integers. This also includes defining a schema  or the relationship of each table in the database
• Data access control: providing security techniques to protect data, this includes deciding who can view or use any information stored in the database

Create a MySQL Database Using CLI

If you are running a Linux server with MySQL but no cPanel, you can simply use the terminal to create a MySQL database, database user, and password, as well as, assign all privileges to the user for the database.

1. SSH into your server as root.
2. Log into MySQL as root:Copymysql -u root
3. Create a new database user:CopyGRANT ALL PRIVILEGES ON *.* TO ‘db_user’@’localhost’ IDENTIFIED BY ‘P@s$w0rd123!’;NOTE: Be sure to modify db_user with the actual name, you would like to give the database user, as well as, P@s$w0rd123! with the password to be given to the user.
4. Log out of MySQL by typing: \q.
5. Log in as the new database user you just created:Copymysql -u db_user -pNOTE: Be sure to modify db_user with the actual database user name.Then, type the new database user’s password and press Enter.
6. Create a new database:CopyCREATE DATABASE db_name;NOTE: Be sure to modify db_name with the actual name you would like to give the database.

Congratulations! Now, you know how to create a MySQL database from the command line.

HOW TO CONNECT YOUR SITE TO MYSQL DATABASE

There are three types of methods in PHP to connect MySQL database through backend:

• MySQL
• MySQLi
• PDO

mysql() is now obsolete because of security issues like SQL Injection etc, but the other two are being actively used.

MySQLi

MySQLi is an API used as a connector function to link the backend of the PHP app to the MySQL database. It works just like the previous version, but it is safer and faster, and provides a better set of functions and extensions. MySQLi was introduced with PHP 5.0.0 and the drivers were installed in 5.3.0. The API was designed to support MySQL from version 4.1.13 to newer ones.

PDO

PHP Data Objects (PDO) extension is a Database Abstraction Layer. It is like an interface for the backend to interact with the MySQL database and make changes without making any change in the PHP code. It also gives you the freedom to work with multiple databases. The major advantage of using PDO is that your code stays simple and portable.

In this article, I’ll discuss how to connect MySQL Database on different servers and I’ll also give you an overview of connecting Database using PDO.

1. Connect MySQL using Localhost Server
2. Connect MySQL using Cloudways Server
3. Connect MySQL using PDO
4. Connect MySQL using Remote MySQL

Create MySQL Database at the Localhost

Before you start building PHP connection to MySQL database you need to know what PHP Myadmin is. It’s a control panel from where you can manage the database that you’ve created. Open your browser and go to localhost/PHPMyAdmin or click “Admin” in XAMPP UI.

When you first installed XAMPP, it only created the username for it to be accessed, you now have to add a password to it by yourself. For this, you have to go to User account where the user is the same as the one shown in this picture:

Now click Edit privileges and go to Change Admin password, type your password there and save it. Remember this password as it will be used to connect to your Database.

Note: It is not necessary to change the password to access databases on the localhost. It is a good practice and that is why we have used a password.

Create Database

Now return to the homepage of PHPMyAdmin. Click the New button to create a new database.

In the new window, name your database as per your need, I am naming it “ practice ”. Now select Collation as utf8_general_ci, as we are using it for learning purposes and it will handle all of our queries and data that will be covered in this tutorial series. Now click on Create and your database will be created.

The newly created database will be empty now, as there are no tables in it. I will be covering that in the upcoming series where we will learn how to create tables and insert data in it. In this tutorial, we are going to connect this database to localhost using PHP

Create a Folder in htdocs

Now, locate the folder where you installed XAMPP and open the htdocs folder (usually c:/xampp). Create a new folder inside c:/xampp/htdocs/ and name it “practice” we will place web files in this folder. Why we have created a folder in htdocs? XAMPP uses folders in htdocs to execute and run your PHP sites.

Note: If you are using WAMP, then add your practice folder in c:/wamp/www folder.

Create Database Connection File In PHP

Create a new PHP file and name it db_connnection.php and save it. Why am I creating a separate database connection file? Because if you have created multiple files in which you want to insert data or select data from the databases, you don’t need to write the code for database connection every time.

1. <?php
3. function OpenCon()
4. {
5. $dbhost = “localhost”;
6. $dbuser = “root”;
7. $dbpass = “1234”;
8. $db = “example”;
11. $conn = new mysqli($dbhost, $dbuser, $dbpass,$db) or die(“Connect failed: %s\n”. $conn -> error);
14. return $conn;
15. }
17. function CloseCon($conn)
18. {
19. $conn -> close();
20. }
22. ?>

Here is the explanation of the variable that we have used in our db_connection file:

1. $dbhost will be the host where your server is running it is usually localhost.
2. $dbuser will be the username i.e. root and $dbpass will be the password which is the same that you used to access your PHPMyAdmin.
3. $dbname will be the name of your database which we have created in this tutorial.

You just have to include it by using PHP custom function include (include ‘connection.php’) on the top of your code and call its function and use it. It also helps when you are moving your project location from one PC to another and you have to change the values on the single file and all the changes will be applied to all the other files automatically.

Create a new PHP file to check your database connection

Create a new PHP file to connect to your database. Name it index.php and add this code in this file.

1. <?php
2. include ‘db_connection.php’;
4. $conn = OpenCon();
6. echo “Connected Successfully”;
8. CloseCon($conn);
10. ?>

Run it!

Now open your browser and goto localhost/practice/index.php and you should see this screen:

Confirmation Message

Congratulations! You’ve successfully connected your database with your localhost! If you are not able to see this screen, then check if you have done everything right in your db_connection.php file.

Create MySQL Database at Cloudways Server or any webserver

For the purpose of this tutorial, I assume that you have a PHP application installed on a web server. My setup is:

• PHP 7.3
• MySQL

After successfully launching a PHP Application on Cloudways go in the application tab and check the details of the database and also click on the button to launch database manager .

Connect to MySQL Database

To set up a connection to the database use the mysql_connect function. This function returns a pointer (also known as a database handle) to the database connection. This handle will be used in the code later on. Once you have the handle, remember to add your database credentials.

• Create a new PHP file and name it db_connnection.php and save it.

Why am I creating a separate database connection file? Because if you have created multiple files in which you want to insert data or select data from the databases, you don’t need to write the code for database connection every time. You just have to include it by using PHP custom function include (include ‘connection.php’) on the top of your code and call its function and use it.

At this point you have the option of either using MySQLi procedural connection query or PHP PDO database connection

MySQLi Procedural Query

1. <?php
3. $servername = “localhost”;
5. $username = “username”;
7. $password = “password”;
9. $db = “dbname”;
13. // Create connection
15. $conn = mysqli_connect($servername, $username, $password,$db);
19. // Check connection
21. if (!$conn) {
23. die(“Connection failed: ” . mysqli_connect_error());
25. }
27. echo “Connected successfully”;
29. ?>

Connect MySQL Database with PHP Using PDO

1. <?php
3. $servername = “localhost”;
5. $username = “username”;
7. $password = “password”;
9. $db = “dbname”;
13. try {
15. $conn = new PDO(“mysql:host=$servername;dbname=myDB”, $username, $password, $db);
17. // set the PDO error mode to exception
19. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
21. echo “Connected successfully”;
23. }
25. catch(PDOException $e)
27. {
29. echo “Connection failed: ” . $e->getMessage();
31. }
33. ?>

Check Connection

1. <?php
3. include ‘db_connection.php’;
7. echo “Connected Successfully”;
9. mysqli_close($conn);
11. ?>

For PDO Close the Connection like this

1. $conn = null;

Remote MySQL

For Remote PHP MySQL connection Log into the Cloudways Platform with your credentials. Click on “Servers” in the top menu bar. Then, click on your target server from the list.

• Next, go to the Security menu option in the left menu, and then click the MySQL tab.
• Add the IP address to the “Add IP to Whitelist” text area and click the “Add” button.
• If you have multiple IP addresses, repeat the process.
• Once you are done, click the “Save Changes” button to finalize the changes

After successfully, set the IP Whitelisting set the IP address in connection and run the query.

Top MySQL Management tools

Mysql Workbench

MySQL Workbench is a visual tool for database designers, developers, and DBAs. MySQL Workbench gives information modeling, SQL advancement, and comprehensive organization devices for server arrangement, client organization, reinforcement, and much more. MySQL Workbench is accessible on Windows, Linux and Mac OS X.

MySQL Workbench is a very popular MySQL database manager for developers. It provides handy administration tools like server configuration, user management, and many others.It is available for MAC OS, Linux, and Windows operating systems.

We advise all those budding developers who want to learn database administration to use it to their advantage. The management features will help them in mastering database administration in full.

Pros

1. It saves SQL statements
2. Provides offline access to remote DBs
3. Stores multiple connections in one location
4. Visual schema and query builder available

Cons

1. It is more complex as compared to PHPMyAdmin.
2. Users often report software crashes.

Navicat For Mysql

Navicat is a series of graphical database administration and software development. It could be a single application that permits you to associate to PHP Mysql connection and MariaDb databases at the same time. Consistent with cloud databases like Amazon RDS, Amazon Aurora, Oracle Cloud, Google Cloud, and Microsoft Azure.

Navicat for MySQL provides all the advanced tools that a developer requires to fully manage his database operations. Its compatibility with cloud databases makes it handy to use while managing a cloud-based application. Some of its main features are:

Pros

1. Intuitive, easy-to-use UI.
2. Provides easy connection to MySQL database via SSH terminal.
3. Schedule database jobs – backup, restore, run reports, and more.
4. Import and export data from ODBC, Excel, Access, DBF, TXT, CSV, XML, JSON.
5. Visual schema and query builders available.
6. Compatible with Windows, Linux, and MAC operating systems.
7. Team collaboration feature available

Cons

1. The professional version is expensive
2. Execution on Linux requires Wine, which slows down the IDE.
3. Time-consuming process management

MYSQL Yog

This MySQL management tool has three packages available i.e Professional, Enterprise, and Pro. You can choose any one of them after testing out.

The MySQL administrators can easily work with this platform and can handle their database tasks with efficiency. It is available for Windows operating systems only.

Pros

1. Smooth query designer tool available
2. Offers advanced features that help in learning database administration.

Cons

1. The pro version is heavy on pocket
2. Users often report occasional software crashes
3. Doesn’t support any databases except MySQL
4. No native support for Linux and MAC operating systems.
5. No drag and drop support between multiple tabs.

Cloudways MySQL Database Manager

Cloudways MySQL database manager is by far one of the handiest and useful MySQL management tools for developers. It is very easy to use because of its user-friendly UI and provides all the required MySQL administration tools a database manager should have:

Pros

1. Offers user-friendly and customizable UI with advanced features.
2. Designs the schema tables, constraints, and queries visually.
3. Easy connection to MySQL database using SSH terminal.

Cons

1. No drag and drop support available for multiple tabs.
2. Lack of support for databases other than MariaDB, MySQL.

Conclusion

A good database structure is the backbone of any application. Whether it’s a CMS or an online task tracking platform, you need a MySQL database(s) to keep track of app and user information and server data to the application processes.

In this tutorial, we have learned two things:

• How to create a new database
• How to connect MySQL database with PHP

Both MySQLi and PDO have their preferences. However, before starting off one should keep in mind that MySQL is only used to connect MySQL with PHP, if you need to migrate to another database, you’ll get to modify the whole code. On the other hand, PDO works with 12 diverse databases, which makes the migration much less demanding.