How To Configure Mysql Server

mysql

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:

MySQL is also an Oracle-backed open source relational database management system based on Structured Query Language (SQL). MySQL runs on virtually all platforms, including Linux, unix and Windows. Although it can be used in a wide range of applications, MySQL is most often associated with web applications and online publishing.

MySQL is an important component of an open source enterprise stack called LAMP,. LAMP is a web development platform that uses Linux as the operating system, Apache as the web server, MySQL as the relational database management system and PHP as the object-oriented scripting language. (Sometimes Perl or Python is used instead of PHP.)

Originally conceived by the Swedish company MySQL AB, MySQL was acquired by Sun Microsystems in 2008 and then by Oracle when it bought Sun in 2010. Developers can use MySQL under the GNU General Public License (GPL), but enterprises must obtain a commercial license from Oracle.

Today, MySQL is the RDBMS behind many of the top websites in the world and countless corporate and consumer-facing web-based applications, including Facebook, Twitter and YouTube.

WHAT IS MYSQL SERVER

MySQL server is a SQL complaint server, in other words it is a relational model database server. It is very popular because it is free. It was developed by Sun and moved to Oracle when Oracle acquired Sun. Oracle continued improving it. The latest version is 5.7.

The relational model is a way to organize data in tables, where each table as a primary key (which is unique) and rows can relate to each other using that key.

What is MySQL server used for?

MySQL is a database management system.

It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.

How does MySQL server work?

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

HOW MYSQL WORKS

How Does MySQL Work?

How MySQL Works - What is MySQL

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 MySQL 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.

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

Features And Benefits Of Mysql

MySQL is a relational database management system (RDBMS) based on the SQL (Structured Query Language) queries. It is one of the most popular languages for accessing and managing the records in the table. MySQL is open-source and free software under the GNU license. Oracle Company supports it.

The following are the most important features of MySQL:

Easy to use

MySQL is easy to use. We have to get only the basic knowledge of SQL. We can build and interact with MySQL by using only a few simple SQL statements.

It is secure

MySQL consists of a solid data security layer that protects sensitive data from intruders. Also, passwords are encrypted in MySQL.

Client/ Server Architecture

MySQL follows the working of a client/server architecture. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they can query data, save changes, etc.

Free to download

MySQL is free to use so that we can download it from MySQL official website without any cost.

It is scalable

MySQL supports multi-threading that makes it easily scalable. It can handle almost any amount of data, up to as much as 50 million rows or more. The default file size limit is about 4 GB. However, we can increase this number to a theoretical limit of 8 TB of data.

Speed

MySQL is considered one of the very fast database languages, backed by a large number of the benchmark test.

High Flexibility

MySQL supports a large number of embedded applications, which makes MySQL very flexible.

Compatible on many operating systems

MySQL is compatible to run on many operating systems, like Novell NetWare, Windows* Linux*, many varieties of UNIX* (such as Sun* Solaris*, AIX, and DEC* UNIX), OS/2, FreeBSD*, and others. MySQL also provides a facility that the clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).

Allows roll-back

MySQL allows transactions to be rolled back, commit, and crash recovery.

Memory efficiency

Its efficiency is high because it has a very low memory leakage problem.

High Performance

MySQL is faster, more reliable, and cheaper because of its unique storage engine architecture. It provides very high-performance results in comparison to other databases without losing an essential functionality of the software. It has fast loading utilities because of the different cache memory.

High Productivity

MySQL uses Triggers, Stored procedures, and views that allow the developer to give higher productivity.

Platform Independent

It can download, install, and execute on most of the available operating systems.

Partitioning

This feature improves the performance and provides fast management of the large database.

GUI Support

MySQL provides a unified visual database graphical user interface tool named “MySQL Workbench” to work with database architects, developers, and Database Administrators. Msql workbench provides SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user administration, backup, and many more. MySQL has a fully GUI supports from MySQL Server version 5.6 and higher.

Dual Password Support

MySQL version 8.0 provides support for dual passwords: one is the current password, and another is a secondary password, which allows us to transition to the new password.

Why is MySQL so Popular?

DB Engines Ranking - What is MySQL

MySQL is indeed not the only (R)DBMS on the market, but it is one of the most popular ones and only second to Oracle Database when scored using critical parameters like the number of mentions in search results, professional profiles on LinkedIn, and frequency of technical discussions on internet forums. The fact that many major tech giants rely on it further solidifies the well-deserved position. Why so? Here are the reasons:

Flexible and easy to use

You can modify the source code to meet your own expectations, and don’t need to pay anything for this level of freedom, including the options for upgrading to the advanced commercial version. The installation process is relatively simple, and shouldn’t take longer than 30 minutes.

High performance

A wide array of cluster servers backs MySQL. Whether you are storing massive amounts of big e-Commerce data or doing heavy business intelligence activities, MySQL can assist you smoothly with optimum speed.

An industry standard

Industries have been using MySQL for years, which means that there are abundant resources for skilled developers. MySQL users can expect rapid development of the software and freelance experts willing to work for a smaller wage if they ever need them.

Secure

Your data should be your primary concern when choosing the right RDBMS software. With its Access Privilege System and User Account Management, MySQL sets the security bar high. Host-based verification and password encryption are both available.

HOW TO CONFIGURE MYSQL SERVER

Setting Up the MySQL Database Server in the Windows Operating System

The MySQL database server is one of the most popular open-source database servers commonly used in web application development.

This document recommends a sequence of steps to set up the MySQL database server 5.6 versions in the Windows operating system. It does not cover MySQL configuration details, it describes a sequence of required steps.

For information about installing and configuring MySQL database server for other operating systems, refer to the Installing and Upgrading MySQL documentation.

The Setting Up the MySQL Database Server 5.1 versions in the Windows Operating System document provides the instructions on setting up the 5.1 versions of MySQL Server on Windows.

Starting the Download

  1. Go to http://dev.mysql.com/downloads/installer/.
  2. Click the Download button.
  3. Save the installer file to your system.

Starting the Installation

After the download completes, run the installer as follows:

  1. Right-click the downloaded installation file (for example, mysql-installer-community-5.6.14.0.msi ) and click Run. The MySQL Installer starts.
  1. On the Welcome panel, select Install MySQL Products.
  1. On the License Information panel, review the license agreement, click the acceptance checkbox, and click Next.
  1. On the Find latest products panel, click Execute. When the operation is complete, click Next.
  1. On the Setup Type panel, choose the Custom option and click Next.
  1. On the Feature Selection panel, ensure MySQL Server 5.6.x is selected, and click Next.
  1. On the Check Requirements panel, click Next.
  1. On the Installation panel, click Execute. When the server installation is completed successfully, the information message appears on the Installation panel. Click Next.
  1. On the Configuration panel, click Next.
  1. At the first MySQL Server Configuration page (1/3), set the following options:
    • Server Configuration Type. Select the Development Machine option.
    • Enable TCP/IP Networking. Ensure the checkbox is selected and specify the options below:
    • Port Number. Specify the connection port. The default setting is 3306 – leave it unchanged if there is not special reason to change it.
    • Open Firewall port for network access. Select to add firewall exception for the specified port.
    • Advanced Configuration. Select the Show Advanced Options checkbox to display an additional configuration page for setting advanced options for the server instance if required.
Choosing this option is necessary to get to the panel for setting the network options where you will turn off the firewall for the port used by the MySQL server.

Click Next.

  1. At the second MySQL Server Configuration page (2/3), set the following options:
    • Root Account Password.
    • MySQL Root Password. Enter the root user’s password.
    • Repeat Password. Retype the root user’s password.
The root user is a user who has full access to the MySQL database server – creating, updating, and removing users, and so on. Remember the root password – you will need it later when creating a sample database.
  • MySQL User Accounts. Click Add User to create a user account. In the MySQL User Details dialog box, enter a user name, a database role, and a password (for example, !phpuser ). Click OK.

Click Next.

  1. At the third MySQL Server Configuration page (3/3), set the following options:
    • Windows Service Name. Specify a Windows Service Name to be used for the MySQL server instance.
    • Start the MySQL Server at System Startup. Leave the checkbox selected if the MySQL server is required to automatically start at system startup time.
    • Run Windows Service as. Choose either:
    • Standard System Account. Recommended for most scenarios.
    • Custom User. An existing user account recommended for advanced scenarios. Click Next.
  2. At the Configuration Overview page, click Next.
  3. When the configuration is completed successfully, the information message appears on the Complete panel. Click Finish.
To check that the installation has completed successfully, run the Task Manager. If the MySQLd-nt.exe is on the Processes list – the database server is running.

Disadvantages/shortfalls of MySQL

Following are the few disadvantages of MySQL:

  • MySQL version less than 5.0 doesn’t support ROLE, COMMIT, and stored procedure.
  • MySQL does not support a very large database size as efficiently.
  • MySQL doesn’t handle transactions very efficiently, and it is prone to data corruption.
  • MySQL is accused that it doesn’t have a good developing and debugging tool compared to paid databases.
  • MySQL doesn’t support SQL check constraints

Drop your comment.

Related Post

Leave a Reply

Your email address will not be published.