MySQL has a large portion of the online market. Where Microsoft SQL Server and Oracle were once dominant, MySQL's free architecture and improved interface through the years have made it one of the top three databases used across the world. It's used by some of the largest websites including Facebook and Pinterest. Small startups that use WordPress inherently use MySQL since the application is integrated with the database. Webmasters, ecommerce startups, bloggers, and programmers can all benefit by understanding and learning MySQL administration.

What is MySQL?

There are two regions in a website design: the front-end and the back-end. The front-end is the site design and interface users see when they open the site. The back-end is the database. The database is the power horse for an application. It houses all the data and information needed by the front-end. Webmasters have several options when choosing a database, and one of these options is MySQL.

Before you understand MySQL, it helps to understand database functionality. A database is made up of tables, stored procedures, and functions. These three parts drive the back-end of your application.

Tables are the database engine's storage components. Architecture of database storage is extremely technical, but an easy way to envision a database table is to visualize a spreadsheet. Spreadsheets have columns and rows. Where these columns and rows intercept are fields. The fields contain one item of data.

When you set up a spreadsheet, your columns make up the components of your data. For instance, a spreadsheet that contains customer information has a first name, last name and address column. Each row has a column of information. These rows represent your records. Using the customer example, each row is a customer.

This is a simplified way to explain a MySQL table, but it helps new database users envision how data is stored and represented. If you look at a MySQL table, it's laid out like a spreadsheet of information. The trick is to understand how to retrieve, filter, edit and delete data.

Stored procedures are the programming objects within your database. For instance, suppose you want to select the last customer who ordered, and then send that customer an email. You could write a stored procedure. You can view stored procedures as small functions for your application. They perform certain functionality, which usually results in displayed, editing, inserting or updating data.

Functions are similar to regular programming functions. They act as standard procedures that you use regularly in your SQL programming. For instance, suppose you need to add two numbers each time you query your MySQL tables. Instead of writing stored procedure code each time you add the numbers, you can create a function and use it to save you programming time.

Why is MySQL So Popular?

MySQL isn't popular just because it's free. Most free software isn't associated with high-end enterprise solutions, but MySQL is an exception to this rule. MySQL can be used with small startups or high-traffic enterprise applications. Banks, social networks, universities and government agencies use MySQL as their database solution.

MySQL is also secure. Security is a sensitive issue, since the protection of your customer and employee records should be a major concern. With MySQL, administrators can encrypt data and set up authentication to protect all company assets.

There are several more open-source database applications on the market that were deployed after MySQL. MySQL's success is partly due to the numerous hosting companies that offer services with unlimited MySQL database services. Oracle and SQL Server are expensive platforms, and most hosting companies only offer 1 SQL Server if you choose to work with it instead of an open-source database solution. Since MySQL is free and open-source, hosting companies offer unlimited databases with a lower cost than Microsoft platforms. This type of marketing gave MySQL its strong foundation in application development in global cloud and desktop applications.

Relational Data Integrity

MySQL is a relational database. These systems offer data integrity. They are distinct from systems such as Mongo that relies on document style records. Relational databases are used in numerous applications, but the major reason they are used to create applications is for data integrity and transactional style record manipulation.

Relational databases work on the concept of a primary and foreign key relationship. The primary key is the main unique field that identifies a record. This unique identifier is then stored in other tables to build a relationship between a main table and a secondary related table. Each customer has its own unique identifier, and then this identifier is stored in the order table. When you need to query MySQL with a list of customers and related orders, you join the two tables using specific SQL syntax.

MySQL is also a transactional database, which means that you can roll back changes to your database. For instance, suppose you want to delete a customer but you don't want to delete the customer if there are active order records associated with the customer. You attempt to delete the customer, but MySQL's relational foreign key constraints give you an error when you attempt to delete it. You can then roll back any changes you made based on commit and rollback features. You commit changes if there were no errors found, or you can roll back previous statements if an error is thrown by the database server.

Data integrity is what sets MySQL and other relational databases apart from more modern databases such as NoSQL. NoSQL databases such as Mongo are used for analytical data and capturing any number of unstructured data. MySQL requires your data to be more structured, so it's a reliable database system for people who want to secure the structure and relationship between tables.

You might wonder how MySQL can generate revenue to support its continued service. While the database engine itself is free, MySQL makes its money through support options. This is the common open-source option for developers. 

The basic installation options are the database engine and the Workbench. The engine is what stores and queries your data sets. The Workbench is the main user interface for designing and maintaining your databases. You can also use the command line features in MySQL, but new people find the Workbench easier to user when they are learning MySQL functionality.

If you choose a hosting provider, they normally offer MySQL databases as an addition to hosting services. Support services are by yearly subscription. Standard edition is offered for $2,000, Enterprise edition is $5,000 and Cluster Carrier Grade edition is $10,000. Most basic enterprise application solutions use Standard edition with internal support.

For people learning MySQL, the database platform has a strong user community. They also offer a developer community where new database designers and programmers can collaborate. 

There are two main installation environments with MySQL: WAMP and LAMP. Linux, Apache, MySQL and PHP (LAMP) is the most common type of installation. This type of installation uses any distribution of Linux and Apache is the web server software. MySQL and PHP work seamlessly with Linux and Apache, so people familiar with the Linux environment will not have many problems installing and configuring MySQL.

Windows, Apache, MySQL, and PHP (WAMP) is the other option for developers and administrators familiar with Windows. When MySQL was first deployed, it was more compatible with the Linux and Unix environments. MySQL developers have expanded the platform and now offer Windows developers and administrators the option to install the open-source database instead of struggling to move it to a Linux environment. MySQL has an MSI installer package that you can use to install the database system on your Windows server.

You don't need Apache either, if you want to use just a PHP and MySQL environment. MySQL is just the database system, so you can choose any number of web services to host the application environment. You can also use PHP with Windows Internet Information Services (IIS), so you aren't limited to just Apache. The environment you choose determines the structure of your application and infrastructure, so choose your platform carefully with your technology consultant.

Finding the MySQL Download

The MySQL website is found at MySQL.com. The download link is a tab on their home page. You have two options: Enterprise or Cluster CGE. Enterprise edition is typically what most site owners can use. Cluster edition is used when you have a large farm of database engines that you need to work together to support a large application.

When you download the installation files for MySQL, ensure that you only download them from an official source. In most cases, this is only on the main MySQL website itself. In rare occasions, your host or provider might give you a link to the site or a fully installed server without installing the database yourself. However, you should still install MySQL to your development environment. When you build SQL queries, you should always first build them in development and then move these queries to a production environment.

Another advantage of MySQL is that the database engine runs on all three main operating systems: Linux, Windows, and Mac. This article will cover installing MySQL after you've successfully downloaded the binary files.

Installing MySQL for Windows

Normally, you install MySQL on a server, but the database also runs on a regular desktop operating system. You must install MySQL as an administrator. For Windows, right-click the install file and select "Run as Administrator." The install file is an MSI, so it's a native Windows Installer package. This option elevates privileges on newer Windows OS versions such as 7, 8 or 2012.

Once you download the executable, right-click on "wpilauncher.exe" and select "Run as Administrator." Choose MySQL Windows 5.1 and click "Install."

The database engine asks you to enter a root password. The "root" user is just like the administrator in Windows. It has full control of the database, and you'll use it to add new users, design databases, back up your data, and perform administration queries against the server.

Installing MySQL in Linux

Linux is a command line based system, at least more than Windows. It's recommended that MySQL is installed using RPM Package Manager. You'll need to log in as the root user on your Linux server or desktop.

Some Linux distributions come with MySQL packages. They are separated into several components including the ending, client, developer libraries and benchmark and performance tools. You just need the database engine to run MySQL, so all the others are extras you can use to learn how to work with MySQL.

After you log in to root, change the current directory path to the one containing the RPM packages. Once you're there, use the following command

Replace the version above with the MySQL version you downloaded.

Install MySQL on Mac

Installing MySQL on a Mac is similar to Windows except the file extensions are different. You can download the packaged executables for Mac and use the Installer to install the database. Just like Linux and Windows, client tools should be installed on the machine you want to use to create queries and manage the database. Using these tools will make it much easier for you to navigate and understand MySQL database maintenance and programming rather than focus on specific command line options. Client tools also give you a GUI to work with.

Understanding MySQL Directory Contents

After you install the database engine, the next step is to understand the engine and files associated with the engine.

You should know where the data is stored. You can use this information to automatically back up your database. In Windows, you can change the location of your data using the my.ini file in the MySQL installation directory. Unless you gave the installation program a custom location, MySQL is installed into the "Program Files\mySQL\MySQL Server x.x" directory.

In the my.ini file, look at the "datadir" value. This is where your data is stored. By default, it's stored in the "All Users\Application Data\MySQL\MySQL Server x.x\Data" directory.

Linux uses the same variable, but the file name that stores variables and preferences is my.cnf. The default data location is the "/var/lib/mysql" folder.

The next section is server administration. Administration and development work together, but they have defined differences. The administration side of MySQL is more for maintaining your database. You can import and export data, manage server instances, add security, and add new MySQL instances.

The data modeling tool lets you create objects based on visual graphics. Instead of drawing a model in third-party software such as Visio, you can create data models that are then turned into your database design. Data modeling tools are one reason MySQL is popular over other competitor products.

MySQL and Applications

MySQL powers huge systems across the world, but it's also a scalable and dynamic way to build a small system. MySQL uses the SQL language to retrieve, edit and remove information. You can use any number of application languages to interface with the database. The most commonly used is PHP.

PHP has internal functions that work directly with MySQL. Coders don't need to create complex PHP layers to work with the data. Instead, coders can call functions that handle the technological communication between the application and the database.

We'll use PHP to show you how to connect your applications to the database. You still need to create SQL statements in PHP. You also need to test and secure these applications, but knowing basic SQL will help you build applications that retrieve, edit and remove records from the database.