How to Set Up a MySQL Community Server on Your Machine

Behind every website is a database, and it often runs on MySQL. Learning to set up your server is often the first real test for a new webmaster or developer, and MySQL Community Server provides a free and open source way to get started.

What makes MySQL Community Server the best database software? What other software stands up to it? And how can you get it running correctly on your web server?

We’ll answer all of that in this article, and more. You’ll learn everything you need to know to set up a MySQL community server on your machine.

Let’s begin!

What is a MySQL Community Server?

Almost every website requires a database. The ones that don’t need it are small sites with little more than static pages. A database simplifies managing large amounts of content in a structured and accessible way.

In WordPress, the database stores entire posts, pages, and other custom data like tags and comments. In addition, if data can be changed, added, or deleted, it’s likely to be stored in a database. So it’s easy to imagine just how much data goes through a database.

SQL stands for Structured Query Language. And it’s a web language similar to JavaScript or HTML. SQL is mainly used to help you manage and manipulate databases. While alternatives exist, SQL is the most popular choice used on a vast majority of websites.

While SQL is the language you use to command your database, you’ll need a database management system to handle all the database’s intricacies. And that’s where MySQL comes in.

As the most popular database management system on the market, MySQL is the go-to for developers and web admins when setting up a database on their server.

Is MySQL Community Server Free?

While a paid enterprise version of MySQL exists, all the core features you’ll likely use are in the MySQL Community Edition. In addition, it’s free to download on all of its 20+ supported operating systems, and there are no hidden fees or sudden gotchas.

Essentially, the MySQL community server comes with no strings attached!

Furthermore, it’s available under the GPL License. That means it’s free, open source, and can be used and modified for commercial projects. Therefore, there are no legal issues with typical uses of MySQL on your server.

That said, you may need to release your entire project under the GPL license if you choose to distribute its source code, so be sure to read the license.

Compared to other software, which tends to be pay-to-use or has very restrictive free versions, MySQL is a top choice.

Key Things to Know About MySQL Community Server

Like any software, MySQL has a few limitations you’ll want to keep in mind. Some of these result from errors. Others happen because a program can only store so much data.

Either way, you’ll want to keep these in mind going in and possibly even seek out an alternative if they’re deal-breakers for your project.

Here are the top limitations to consider:

• Table size limit: There are several hard limits on row and column size. You can only have 4096 columns in a single table, though it could end up being less. In addition, there’s a row size limit of 65,535 bytes.
• MySQL imposes restrictions: Even if your storage engine supports larger rows and columns, MySQL will not allow you to create any over the limit.
• Windows imposes further limits: Windows versions of MySQL come with extra restrictions. 32-bit versions of Windows can’t use more than 2GB of RAM within a process. In addition, a Windows server can only use 4000 ports, which can fill up quickly when many clients connect to your database.
• Function and routine limits: MySQL has several self-imposed limitations on functions and routines you’ll want to be aware of if you run into problems.
• Large databases can perform poorly: When it comes to huge databases with massive volumes of content to store and sort through, MySQL can begin to suffer. Even backing it up and restoring backups can become a problem.
• Be wary of memory issues: Memory problems can be a massive issue with MySQL, and when it runs out of memory, it may not be clear what’s causing the sudden freeze.

While this may seem like a concerning list, every database management system has its pros and cons, and MySQL is suitable for most projects. Huge enterprise websites and big data may push MySQL to its limits, but small projects and decently active websites will work just fine.

MySQL Community Server vs MySQL Enterprise Edition

MySQL comes with two distinct editions: the Community Server and Enterprise editions. The former is the open source version of MySQL everyone knows and loves. The Enterprise edition exists to provide a paid but more robust version suitable for large enterprise websites.

Here are a few other security tips:

• Encrypt passwords: Storing plain text passwords in the database can be extremely dangerous. Instead, follow password guidelines and encrypt your passwords. If you’re using a WordPress plugin, they will probably handle this for you.
• Limit user access: Never give non-root users access to the user table.
• Please don’t use the root user for your server: On Linux systems, never run the MySQL server on the root user (as it has access to all commands and hackers can cause more damage).
• Limit privileges: Assign proper account privileges to users and avoid giving them more access than required. Always require a password for all users.
• Ban code on forms: Prevent users from executing code on your site through the input boxes (such as forms).
• Implement a firewall: Use a firewall to your database server to block unwanted access.
• Use SSL: SSL encrypt your website to hide all data passing through your server.
• Get rid of the test database: Remove the test database (if “mysql_secure_installation” didn’t remove it) as anyone can access it.

How to Install MySQL Community Server on Windows

Not familiar with Linux? Or you’re running your server on a Windows machine?

Lucky for you, there’s a simple MySQL installer tool you can use to get MySQL up and running on your operating system. No long, complicated setup or confusing code, just a standard Windows installer wizard.

Step 1: Download the MySQL installer for Windows. You should see two files: the web installer and the alternative installer. Choose the first if you’ll have an internet connection while downloading MySQL. If not, go with the second option. You can also download the zip archive, unzip it, and then install MySQL manually. But you can’t set it up with the installer wizard.

Step 2: Once MySQL is downloaded, double-click on the installer to open it. Proceed through the screens displayed to complete the installation.

Step 3: You’ll soon reach a setup type screen. Suppose you’re using MySQL as a developer, select Developer Default. The Server only option is best for server machines. If you’re not sure, it never hurts to pick Full to download all components. The setup may prompt you to install prerequisite software; either allow this to resolve automatically or seek out the required tools online.

Step 4: Once you download everything you selected, you’ll reach a setup screen. You can leave most settings on default (but be sure to choose Server Computer or Dedicated Computer if you’re setting up a database server on this machine).

You’ll also have to set a root password and optionally add extra users.

And if you want MySQL to run at startup, be sure to tick the relevant box.

Step 5: With the configuration finished, you’ll complete the installation. If you’re ready to try out MySQL, leave Start MySQL Workbench after Setup checked. You can always launch MySQL Workbench later by clicking the start menu and typing it into the search bar.

With that, you’ve installed MySQL properly and can begin testing it.

Install MySQL Community Server on Linux

Unlike Windows, there’s no straightforward way to run an installer and have MySQL working on your machine. Instead, you’ll need to use the command line to install MySQL. This will use the package managers built into the operating system.

Luckily, this is theoretically much easier than having to run through a ten-step installer on Windows. All you need to do is launch the Terminal to get started.

Here’s how to do it on apt-based operating systems such as Ubuntu (a note before starting: you may need to install the MySQL APT Repository before you begin):

Step 1: First, update the packages installed on your machine to ensure you’re getting the latest version of MySQL:

Step 2: Now run this code to install MySQL:

sudo apt install mysql-server

This command will execute most installation functions and start the server. If the command prompts you to choose a version, select the latest one, which is currently 8.0 (unless you know you need a different version).

Step 3: Run the secure installation to patch up security holes:

sudo mysql_secure_installation

Step 4: MySQL should start automatically, but you can run this command if you notice it won’t start:

sudo service mysql restart

For yum-based systems like CentOS 7, the steps are much the same. The difference is that you’ll need the MySQL Yum Repository. Instead, you should replace the apt commands with yum.

Install MySQL Community Server on Mac

Some versions of Mac OS X come with MySQL preinstalled! You can test to see if it’s on your computer by running this command in the Terminal:

mysql -V

If you see an output, then MySQL is already installed, and you don’t need to do anything more. However, we recommend you to use version 5.7 or 8.0. If your version is lower than this, you may want to install a new MySQL version.

Since Mac doesn’t come with the same easy package manager as Linux, we’ll instead use Homebrew, a popular program that makes installing MySQL (and all sorts of other things) extremely easy.

Step 1: After installing Homebrew, put the following command in the Terminal:

brew install mysql

This command should install MySQL 8.0 and instantly start the service.

Step 2: Run the secure installation process:

mysql_secure_installation

Step 3: If the MySQL server hasn’t started, you can do it manually:

brew services start mysql

Summary

Starting a MySQL server can seem like a daunting task, but it’s not too hard once you get the hang of it. Whether you used the Windows installer or the Terminal to set it up, you should now have the beginnings of a functional SQL database on your computer.

Now that you have a database, you must back it up and keep it protected. Check out our guide to backing up your MySQL database, so you don’t ever lose any data. Both of these are critical for keeping your websites secure.