Overview of MySQL
MySQL is one of the most popular open source database management systems, used by organizations and developers around the world to power web applications and data-driven services. Originally created in 1995 by a Swedish company, MySQL has gone through several major releases and is now owned by Oracle Corporation.
Some key advantages that make MySQL a top choice for many use cases:
- Free and open source software with permissive licensing
- Wide language and platform support
- Powerful query and data manipulation capabilities
- High performance and scalability
- Robust access control and security options
- Constant innovation through new releases
MySQL 8.0 is the latest major version – first released in 2018 with significant enhancements. For those wanting more stability, MySQL 5.7 is the current long term support release, supported until 2023. Ubuntu 22.04 includes packaged versions of both MySQL 8.0 and 5.7.
In this post, we will go through installing MySQL on Ubuntu 22.04 step-by-step using the APT package manager.
Prerequisites
Before installing MySQL, it‘s best to ensure your base Ubuntu system is updated and has the necessary compiler tools in case any dependencies need compiling:
sudo apt update sudo apt install build-essential
You should also check that your hardware meets MySQL‘s memory requirements based on workload size and available RAM.
Some guidelines:
- 2GB minimum for MySQL server
- Increase to 4-8GB or more for large databases
- Set higher tmp disk space (e.g. 5GB in /tmp) if importing big datasets
Lastly, having a non-root user account for running MySQL services improves security. We will create one called mysqluser:
sudo useradd -r -m -U -d /home/mysqluser -s /bin/bash mysqluser
Okay, now we are ready to install MySQL!
Installing MySQL with APT
The easiest way to install MySQL on Ubuntu is by using its Advanced Packaging Tool (APT). This will handle obtaining all required packages from Ubuntu‘s repositories and installing them onto your system.
First decide whether to install the latest MySQL 8.0 or go with the older but more stable 5.7 release. Using the -y flag automates confirming the install:
MySQL 8.0:
sudo apt install mysql-server-8.0 -y
MySQL 5.7:
sudo apt install mysql-server-5.7 -y
APT will take care of the entire process – downloading packages, resolving dependencies, unpacking files, compiling if necessary, and configuring everything to get MySQL running with minimal effort.
Below we show the output for installing MySQL 8.0:
Once complete, MySQL should be started and running. Let‘s check its status:
sudo systemctl status mysql
The active (running) status confirms MySQL is installed and operating correctly.
Setting the MySQL Root Password
Out of the box, Ubuntu‘s MySQL packages don‘t set a root password so this account is not secured. Anyone able to access MySQL locally could login with full privileges, allowing them to view data, modify databases, or execute destructive SQL statements.
Let‘s fix this immediately by setting a strong root password:
sudo mysql_secure_installation
This will launch an interactive shell to configure MySQL security options.
The first prompt allows selecting your preferred password validation policy. Press Enter to keep the default medium level.
Next, enter your new root password when asked. Make sure to set a strong password that cannot be easily guessed.
The subsequent prompts clear the test database, disable remote root logins, and reload privileges to apply the changes. Answer yes (or press Enter) to accept the defaults.
Once complete, test logging into the MySQL shell using the new password:
sudo mysql -u root -p
When prompted, enter the root password you set. If successful, you should enter the MySQL shell and see something like:
Type \q and press Enter to quit the MySQL shell.
The root account now has a properly secured password. But generally, you should avoid using root for regular query activity. Instead, create dedicated user accounts with more limited permissions based on admin needs.
Creating MySQL User Accounts
While connected to the MySQL server, add new accounts with customized access levels appropriate for how they will be used – whether for an individual developer, web application connecting to the database, or admin staff maintaining the system.
For example, to create an admin user with full privileges:
CREATE USER ‘dbadmin‘@‘localhost‘ IDENTIFIED BY ‘strong_password‘;GRANT ALL PRIVILEGES ON . TO ‘dbadmin‘@‘localhost‘;
The first statement creates a local user account named dbadmin that must provide the specified password to connect. The second grants this user full control over the MySQL server, including managing user accounts.
Similar statements can create more restricted accounts like users that can only access certain databases or tables. Refer to MySQL GRANT syntax for additional options.
Properly restricting accounts prevents developers or apps from accidentally (or intentionally!) changing things they should not have access to. It limits the damage from credential leaks since compromised lower privilege accounts do not provide full control.
And by not using root routinely, you avoid accumulation of orphan objects owned by root that can cause certain administration tasks to fail.
Now that access policies are tuned to needs, users can get started leveraging MySQL‘s features for managing data.
Importing and Querying Data
Most usages of MySQL involve storing and retrieving data from tables and running analytical queries. This data typically comes from applications that generate it directly or by importing from other systems.
For testing purposes, you can load sample databases full of tables and sample data that come included with MySQL.
A popular one many official manuals and tutorials reference is the Sakila movie rental database. Import it using the mysql client:
mysql -u root -p < /usr/share/mysql/sakila-db/sakila-schema.sql mysql -u root -p < /usr/share/mysql/sakila-db/sakila-data.sql
Now log in and confirm the tables loaded properly:
mysql -u root -p -e ‘SHOW TABLES‘ sakila
With sample data available, you can start executing queries – finding films by title, listing highest grossing categories, calculating revenue per store, etc. This helps learning MySQL‘s SQL syntax.
Writing applications that connect to MySQL for managing real data works similarly. Configuring connection parameters instead of using the mysql client directly.
Tuning the MySQL Server
Especially when expecting high load or large datasets, the default MySQL configuration may not suit performance needs. Critical tuning includes adjusting:
- Memory allocation to cache hot data
- Concurrency through thread counts
- File I/O patterns to storage infrastructure
- Replication topology for scale and availability
Consider switching the storage engine from the default InnoDB to one better optimized for your access patterns – MyISAM for read performance or TokuDB for compression.
Also explore partitioning schemes that improve manageability for large tables and shard data across nodes.
Refer to MySQL optimization guides for more detailed tuning advice.
Benchmark with production-grade data and workload before finalizing configuration. Remember to back up the database regularly as insurance against failures or mistakes.
Conclusion
Getting MySQL running on Ubuntu 22.04 is straightforward using its mature APT packages that automate most of the installation seamlessly.
With high performance availability direct from Ubuntu‘s repositories, MySQL‘s capabilities for organizing, managing and querying data are now ready for exploration. Developers can power innovative applications while administrators can securely operate MySQL at scale.
Refer to the MySQL reference manuals for guidance on more advanced topics. The knowledge and passion of the open source community continually improves this feature-rich database for the world to benefit from.
I hope you found this step-by-step overview useful for successfully installing MySQL on your Ubuntu system!