Setting up the MySQL database

There are many different ways to set up your MySQL database. It really depends on the type of platforms you are running, whether it is Windows, Linux, Mac, or any other type of operating system; it will also depend on the purpose of your database—whether it's for development and testing, or for a large-scale production server.

For large scale services (such as social media), the best way is to compile MySQL from the source, because such as project requires a ton of optimization, configuration, and sometimes customization in order to handle the large amount of users and traffic.

However, you can just download the pre-compiled binaries if you're going for normal use, as the default configuration is pretty sufficient for that. You can install a standalone MySQL installer from their official website or the download installation packages that come with several other pieces of software besides MySQL.

In this chapter, we will be using a software package called XAMPP, which is a web server stack package developed by a group called Apache Friends. This package comes with Apache, MariaDB, PHP, and other optional services that you can add on during the installation process. Previously, MySQL was part of the package, but it has since been replaced with MariaDB starting from version 5.5.30 and 5.6.14. MariaDB works almost the same as MySQL, except those commands involving advanced features, which we will not be using in this book.

The reason why we use XAMPP is that it has a control panel that can easily start and stop the services without using Command Prompt, and provides easy access to the configuration files without you having to dig into the installation directory by yourself. It is very quick and efficient for application development that involves frequent testings. However, it is not recommended that you use XAMPP on a production server as some of the security features have been disabled by default.

Alternatively, you may also install MySQL through other similar software packages such as AppServ, AMPPS, LAMP (Linux only), WAMP (Windows only), ZendServer, and so on.

Now, let's learn how to install XAMPP:

1. First, go to their website at https://www.apachefriends.org and click on one of the download buttons located at the bottom of your screen, which displays the icon of your current operating system:

2. Once you click on the Download button, the download process should start automatically within a few seconds, and it should proceed to install the program once it's done. Make sure that Apache and MySQL/MariaDB are included before the installation process starts.

3. After you have installed XAMPP, launch the control panel from the start menu or from the desktop shortcut. After that, you may notice that nothing has happened. This is because the XAMPP control panel is hidden within the taskbar by default. You may display the control panel window by right-clicking on it and selecting the Show / Hide option in the pop-up menu. The following screenshot shows you what this looks like on a Windows machine. For Linux, the menu may look slightly different, but overall it is very similar. For macOS, you must launch XAMPP from the launchpad or from the dock:

4. Once you have clicked the Show / Hide option, you will finally see the control panel window displayed on your screen. If you click the Show / Hide option again, the window will be hidden away:

5. Their control panel is pretty much self-explanatory at first glance. On the left, you can see the names of the services that are available in XAMPP, and on the right, you will see the buttons that indicate Start, Config, Logs, and so on. For some reason, XAMPP is showing MySQL as the module name but it is in fact running MariaDB. Don't worry; both work pretty much the same since MariaDB is a fork of MySQL.
6. In this chapter, we'll only need Apache and MySQL (MariaDB), so let's click the Start buttons of these services. After a second or two, you'll see that the Start buttons are now labeled as Stop, which means the services have been launched!:

7. To verify this, let's open up the browser and type localhost as the website address. If you see something like the following image, it means that the Apache web server has been successfully launched!:

8. Apache is very important here as we'll be using it to configure the database using a web-based administrative tool called phpMyAdmin. phpMyAdmin is an administrative tool for MySQL written in PHP scripting language, hence its name. Even though it was originally designed for MySQL, it works pretty well for MariaDB as well.
9. To access the phpMyAdmin control panel, type localhost/phpmyadmin on your browser. After that, you should see something like this:

10. On the left-hand side of the page, you will see the navigation panel, which allows you access to the different databases available in your MariaDB database. On the right-hand side of the page are various tools that let you view table, edit table, run SQL command, export data to spreadsheet, set privileges, and so on.
11. By default, you can only modify the General settings of the database on the setting panel located on the right. You must select a database from the navigation panel on the left before you are able to modify the settings of a particular database.
12. A database is like a cabinet that you can store log books within. Each log book is called a table and each table contains data, which is sorted like a spreadsheet. When you want to obtain a data from MariaDB, you must specify which cabinet (database) and log book (table) you would like to access before getting the data from it. Hopefully, this will make you better understand the concept behind MariaDB and other similar database systems.

13. Now, let's get started by creating our very first database! To do so, you can either click the New button located above the database names on the navigation panel or click the Databases button located at the top of the menu. Both buttons will bring you to the Databases page, and you should be able to see this located below the menu buttons:

14. After that, let's create our very first database! Type in your desired database name and click the Create button. Once the database has been created, you will be redirected to the Structure page, which will list down all the tables contained in this database. By default, your newly created database doesn't contain any tables, so you will see a line of text that says No tables found in database:

15. Guess what we'll be doing next? Correct, we will create our first table! First, let's insert the name of the table you want to create. Since we'll be doing a login page later in this chapter, let's name our table user. We'll leave the default number of columns as it is and click Go.
16. After that, you will be redirected to another page, which contains many columns of input fields for you to fill in. Each column represents a data structure which will be added to your table after it's been created.
17. The first thing you need to add to the table structure is an ID that will automatically increase upon each new data insertion. Then, add a timestamp column to indicate the date and time of the data insertion, which is good for debugging. Last but not least, we will add a username column and password column for login validation. If you're unsure on how to do this, please refer to the following image. Make sure you follow the settings that are being circled in the image:

18. The type of the structure is very important and must be set according to its intended purpose. For example, the id column must be set as INT (integer number) as it must be a full number, while username and password must be set as either VARCHAR or other similar data types (CHAR, TEXT, and so on) in order for it to save the data correctly.

19. The timestamp, on the other hand, must be set to TIMESTAMP type, and must set the default value to CURRENT_TIMESTAMP, which notifies MariaDB to automatically generate the current timestamp upon data insertion.
20. Please note that the index setting for the ID column must be set to PRIMARY, and make sure that the A_I (auto increment) checkbox is ticked. When you check the A_I checkbox, an Add Index window will appear. You can keep the default settings as they are and then you can click the Go button to complete the steps and start creating the table:

21. After you have created the new table, you should be able to see something similar like the following image. You can still edit the structure settings anytime by clicking the Change button; you can also remove any of the columns by clicking on the Drop button located at the right-hand side of the column. Please note that deleting a column will also remove all the existing data belonging to that column, and this action cannot be undone:

22. Even though we'll usually add data to the database through our programs or web pages, we can also add data directly on phpMyAdmin for testing purposes. To add data using phpMyAdmin, first, you must create a database and table, which we have done in the previous steps. Then, click the Insert button located at the top of the menu:

23. After that, you'll see that a form has appeared, which resembles the data structure that we created previously:

24. You can simply ignore the ID and timestamp values as they will be automatically generated when you save the data. In this case, only username and password need to be filled in. For the sake of testing, let's put test as the username and 123456 as the password. Then, click the Go button to save the data.

Please note that you should not save your password in a human-readable format on your actual production server. You must encrypt the password with a cryptographic hash function such as SHA-512, RIPEEMD-512, BLAKE2b, and so on before passing it to the database. This will ensure that the password is not readable by hackers in case your database is being compromised. We will cover this topic at the end of this chapter.

Now that we have finished setting up our database and inserted our first test data, let's proceed to learn some of the SQL commands!