How to install MySQL on Ubuntu: Advanced manual

Choosing the right OS for your database development tasks is highly important; however, with the virtualization of operating systems becoming more and more common, companies get more flexibility in selecting an operating system to run their database servers on. Since Linux (and Ubuntu as part of the Linux family) play a significant role in today's business and software ecosystem, lots of enterprises are migrating their workloads there. This guide is intended to help you install MySQL server on Ubuntu.

Prerequisites for installing MySQL on Ubuntu

To install MySQL on Ubuntu, you will need the following:

• The Ubuntu operating system.
• A user account with sudo privileges.
• A stable network connection.
• Access to the terminal.

Let's look at how to install MySQL 5.7 on Ubuntu 20.04 LTS using the command line. The installation guide for MySQL 8.0 will be provided below.

1. Download and install MySQL server and client

Since Ubuntu 20.04 used in this guide only has MySQL 8.0 in the APT repository, we need to add MySQL 5.7 repository first before installing it.

Step 1: Add the MySQL 5.7 APT Repository

1.1 Download the MySQL repository configuration package by executing the following command:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

1.2 After the MySQL package has been successfully downloaded, install it:

sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

1.3 Next, select Ubuntu Bionic.

After that, select the MySQL Server & Cluster option. Then, select mysql-5.7 and finally select Ok.

1.4 Next, update the APT repository:

sudo apt update

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 467B942D3A79BD29

1.5 Then execute the apt update again:

sudo apt update

To check whether MySQL 5.7 repository has been successfully installed, execute:

sudo apt-cache policy mysql-server

You should see MySQL 5.7 repository at the bottom of the list.

Step 2: Install MySQL 5.7

2.1 Now that you have a MySQL 5.7 repository in your system, you can proceed to install it. For this, run the following command:

sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*

2.2 Press Y to begin the installation and set the root password when asked.

2. Secure your MySQL root user account

Before starting to use MySQL 5.7, you need to secure it first. For this, use the command:

sudo mysql_secure_installation

Provide a password when asked, and then answer the security questions.

3. Check the MySQL version

Want to make sure that you have installed everything correctly? You can do that by checking your current MySQL version. First, you need to log in to MySQL using the root password you have set earlier.

mysql -u root -p

After that, execute the following command:

SELECT VERSION();

You should see your installed MySQL version.

4. Add MySQL user and set privileges

To create a new MySQL user account on Ubuntu, use the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'user_password';

Now that we have created a new account, we can grant privileges to it. Suppose, we want to give the user the privilege to create and select database objects. For this, we run:

GRANT CREATE, SELECT ON *.* TO 'username'@'localhost';

If you want to get the list of all the users in MySQL, execute the following command:

SELECT user FROM mysql.user;

As you can see, we have been successful in the endeavor of creating a new user.

5. Test MySQL 5.7

After installation, MySQL server starts running automatically. However, if you need to verify that, execute the command:

sudo systemctl status mysql

The output should show that the service is active.

6. Log in to your MySQL server

And in the end, you need to log in to MySQL. For this, simply execute:

mysql -u root -p

Now you are ready to work with MySQL 5.7 on Ubuntu. You can create databases, fill in them with data, execute queries, and much, much more. Good luck!

Let's look at how to install MySQL 5.7 on Ubuntu 20.04 LTS using the command line. The installation guide for MySQL 8.0 will be provided below.

1. Download and install MySQL 8.0 APT Repository

First of all, you need to download the latest release package using the wget command:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

Next, you need to install the MySQL package. For this, execute the command below:

sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

In the Package configuration window that appears, select the first option and then select Ok.

In the next window, select mysql-8.0 and hit Enter to continue.

And finally, select the last option and press Enter to proceed:

Next, update the APT repository:

sudo apt update

2. Install MySQL 8.0

Now, you need to run the below command to install MySQL server and client. Press Y to begin the installation, then insert a password for the root user when asked.

sudo apt install -f mysql-client=8.0* mysql-community-server=8.0* mysql-server=8.0*

In the next windows, provide a password for the root user, read the information about the authentication system based on SHA256-based password methods, and choose the authentication plugin. The installation will proceed, and MySQL 8.0 will be installed in your system.

3. Check MySQL version

To make sure, that the installation has been successful, run the following command:

mysql -V

If you need more information about your MySQL server, run:

mysqladmin -u root -p version

4. Create a MySQL user

To create a new MySQL user account on Ubuntu, execute the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'user_password';

If you need more information about your MySQL server, run:

mysqladmin -u root -p version

Once MySQL server is installed, there are several steps developers and DBAs can take to optimize its performance and security. By adjusting configuration settings, choosing the right storage engine, and utilizing built-in tools, you can significantly improve MySQL server’s efficiency and reliability.

Tune the my.cnf file

Steps to tune your my.cnf file for optimal MySQL performance.

1 - Locate the my.cnf file
On Ubuntu, the my.cnf file is usually located in the /etc/mysql/ directory. The typical paths are:

/etc/mysql/my.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

*for newer versions of MySQL

2 - Backup the configuration file
Before making any changes, it’s good practice to back up the current configuration file. This way, you can easily restore the original settings if necessary.

sudo nano /etc/mysql/my.cnf

3 - Open the my.cnf file for editing
Use a text editor to open the my.cnf file. In Ubuntu, you can use nano, or any text editor you're comfortable with.

sudo nano /etc/mysql/my.cnf

4 - Adjust memory-related settings
The following settings help optimize the performance by utilizing system memory effectively.

• innodb_buffer_pool_size determines how much memory InnoDB should allocate for caching data and indexes. Set it to 70-80% of your available RAM if MySQL is the only major service running on the system.

innodb_buffer_pool_size = 4G # Example for a 4GB RAM system

• query_cache_size helps speed up repetitive SELECT queries. If you're using MySQL 5.6 or earlier (or if you're okay with deprecated features), enable the query cache.

query_cache_type = 1    # Enable query cache
query_cache_size = 128M # Set the query cache size to 128MB

The following settings help optimize the performance by utilizing system memory effectively.

query_cache_type = 0 # Disable query cache

• tmp_table_size and max_heap_table_size specify the maximum allowable size for temporary tables in MySQL. Increase these values to allow larger temporary tables to be created in memory instead of on disk.

tmp_table_size = 64M 
max_heap_table_size = 64M

5 - Adjust connection settings

• max_connections sets the maximum number of simultaneous client connections MySQL will allow. The default is typically 151, but you can increase it based on your application needs.

max_connections = 200

• wait_timeout and interactive_timeout control the number of seconds MySQL will wait before closing an idle connection. Lowering these values helps free up resources faster.

wait_timeout = 28800         # Set the wait timeout to 8 hours for inactive 
connections 
interactive_timeout = 28800  # Set the interactive timeout to 8 hours for user 
sessions

6 - Enable slow query logging

Enable slow query logging to identify performance bottlenecks caused by long-running queries.

• slow_query_log turns on slow query logging.
• long_query_time sets the threshold (in seconds) for what constitutes a "slow" query.

slow_query_log = 1             # Enable slow query logging slow_query_log_file = 
/var/log/mysql/mysql-slow.log  # Specify the path for the slow query log file 
long_query_time = 2            # Log queries taking more than 2 seconds

7 - Optimize InnoDB settings

InnoDB is the default storage engine for MySQL, and several settings can be adjusted to improve performance.

• innodb_flush_log_at_trx_commit controls how often InnoDB writes to the transaction log. Setting it to 2 improves performance but with a potential trade-off in durability.

innodb_flush_log_at_trx_commit = 2

• innodb_log_file_size specifies the size of the InnoDB transaction log file. Increasing it can improve performance for large transactions.

innodb_log_file_size = 256M

• innodb_log_buffer_size specifies the buffer size used to store changes before writing to the transaction log. Increase this for better performance with large transactions.

innodb_log_buffer_size = 32M

• innodb_file_per_table controls how InnoDB stores table data. Enabling this option ensures that each InnoDB table is stored in its own tablespace, which can reduce fragmentation.

innodb_file_per_table = 1

8 - Optimize table and index storage

InnoDB's settings can be optimized for better table and index storage.

• innodb_autoextend_increment controls the increment size when InnoDB automatically extends the tablespace. A larger increment can reduce fragmentation.

innodb_autoextend_increment = 64M

9 - Save and close the configuration file

Once you've made the necessary changes, save and close the file. In nano, press CTRL + X, then Y to confirm the changes, and press Enter to save.

10 - Restart MySQL to apply changes

After editing the my.cnf file, restart the MySQL service to apply the changes.

sudo systemctl restart mysql

11 - Monitor and fine-tune the configuration

After restarting MySQL, it's essential to monitor the performance to ensure that the changes have the desired effect. Then you can use tools like dbForge Studio for MySQL to analyze your MySQL server's performance and adjust settings further based on your findings.

Difference between InnoDB and MyISAM

InnoDB and MyISAM are two of the most used storage engines in MySQL. They differ in several key aspects and choosing the right storage engine for your MySQL database is a critical decision, as it can have a significant impact on both performance and data integrity. The storage engine determines how MySQL stores, retrieves, and manages data, and each engine has its strengths and weaknesses based on the specific needs of your application.

Summary:

Securing and optimizing your MySQL server on Ubuntu

After installing MySQL server on Ubuntu, it's highly recommended to use mysql_secure_installation and mysqltuner to improve both the security and performance of your MySQL instance.

mysql_secure_installation

• Setting a strong root password.
• Removing anonymous users who may pose a security risk.
• Disabling remote root login to prevent unauthorized access.
• Removing the default test database, which could be exploited by attackers.

Why is it important to use after the installation? MySQL installations by default are not configured with strong security settings. Running mysql_secure_installation right after installation helps harden the database server and ensures that it is better protected from potential threats.

When to run it? When to run it? It's best to run mysql_secure_installation immediately after installing MySQL on Ubuntu, before the server is used in a production environment. This ensures that basic security measures are in place right from the start.

mysqltuner

What does it do? mysqltuner is a script that provides performance suggestions based on your MySQL server’s current configuration and workload. It analyzes server variables such as buffer sizes, query cache, and disk I/O, then offers tuning recommendations to improve the server's performance.

Why is it important to use after the installation? After installing MySQL, the default settings might not be optimized for your specific workload. mysqltuner helps identify areas where MySQL's performance can be improved, such as adjusting memory settings or increasing the buffer pool size, making it suitable for the scale and traffic of your application.

When to run it? After installation, it’s a good idea to run mysqltuner to evaluate your server's configuration. You should run it again periodically as your MySQL workload evolves, especially after making major changes to the configuration or handling larger datasets.

Alternatives to mysql_secure_installation and mysqltuner

While mysql_secure_installation and mysqltuner are popular tools for securing and optimizing your MySQL server, you can also use dbForge Studio for MySQL as an alternative.

dbForge Studio for MySQL provides a comprehensive suite of features that can help with both security and performance tuning without needing to rely on command-line tools. Here’s how dbForge Studio for MySQL can assist you:

• Security. You can manage user accounts and privileges through an intuitive GUI, allowing you to set strong passwords, remove unused accounts, and ensure that your database is secured properly.
• Performance optimization. dbForge Studio offers built-in tools for query optimization, such as the query profiler and execution plan analysis. It also allows you to adjust server parameters directly from the GUI to fine-tune your MySQL server’s performance.
• Ease of use. Unlike command-line tools, dbForge Studio provides a user-friendly interface to manage your MySQL server, making it a great choice for users who prefer a graphical interface over using scripts.

Whether you're securing your MySQL server or optimizing its performance, dbForge Studio for MySQL can be an excellent alternative to traditional tools like mysql_secure_installation and mysqltuner.

To update MySQL on Ubuntu, follow these steps:

1. Run the command to update your package list:

2. After the update is complete, upgrade the outdated packages and dependencies:

Now, you are ready to work with MySQL 8.0 on Ubuntu. You can design databases, populate them with data, run queries, and much more. There are various ways to manage databases on MySQL server running on Ubuntu: you can use the command line, a specialized IDE like dbForge Studio for MySQL installed via CrossOver or Wine, or remotely from an IDE on Windows, such as the aforementioned Studio. Good luck!

Manage MySQL using a client or an IDE

To manage your MySQL server and databases that reside on it, you can, by all means, use the built-in MySQL command-line client. It has always been a hot topic discussion whether it is better to handle databases in specialized IDEs or by using the command line. Every developer has their preferences. However, it is an undeniable fact – the bigger your workloads are, the more difficult it becomes to manage them from the command line.

One of the best IDEs for managing, developing, and administering MySQL and MariaDB databases today is dbForge Studio for MySQL. The solution is designed to cover every possible routine database-related task.

With dbForge Studio for MySQL Studio, you can:

• Design MySQL databases of any complexity
• Develop databases in the advanced Code Editor
• Administer user accounts and manage privileges
• Compare and deploy database schemas and data
• Copy and migrate MySQL databases
• Create reports and analyze data
• Debug triggers, functions, scripts, and stored routines
• ...and much, much more!

dbForge Studio for MySQL is a classic Windows application. However, you can always connect to a MySQL server run on any OS, including Ubuntu. When connecting to a remote MySQL server, make sure that the user under which you’re logging in, has the rights to access the server remotely. You can also install and run dbForge Studio for MySQL on Ubuntu via CrossOver, a commercial compatibility solution that allows running Windows applications on macOS and Linux. Alternatively, you can use Wine, a free compatibility layer, to run Windows applications on Linux. Both methods allow you to use dbForge Studio for MySQL on Ubuntu. To learn how to install dbForge Studio on Linux via CrossOver or Wine, refer to our product documentation.

In modern DevOps workflows, automation is crucial for ensuring consistency and efficiency, particularly when deploying and managing databases like MySQL. You can automate MySQL installation and management through CI/CD pipelines using tools like Ansible, Terraform, or Docker. By integrating these tools into your deployment processes, you can streamline MySQL setup, ensure reliability, and improve scalability—all while reducing the need for manual intervention.

Install MySQL using Docker

1 - Install Docker (if it's not already installed).

Open a terminal and run:

sudo apt update

sudo apt install docker.io

sudo systemctl start docker

sudo systemctl enable docker

2 - Pull the MySQL Docker image

Pull the official MySQL Docker image from Docker Hub:

sudo docker pull mysql:latest

3 - Run MySQL container

Now, run the MySQL container with a custom password. Replace your_password with a strong password of your choice:

sudo docker run --name mysql-container -e 
MYSQL_ROOT_PASSWORD=your_password -d mysql:latest

• This command will:
• a. Download the MySQL image (if not already downloaded).
• b. Start a new MySQL container named mysql-container.
• c. Set the MySQL root password as specified.

4 - Access MySQL

To access the MySQL server running inside the container, use the following command:

sudo docker exec -it mysql-container mysql -u root -p

Then, enter the password (your_password) when prompted.

5 - Verify MySQL is running

To check if the MySQL container is running, use:

sudo docker ps

You now have MySQL Server running in a Docker container on your Ubuntu system. You can configure the container further if needed, such as mapping ports to access MySQL from outside the container.

On Ubuntu, you can install dbForge Studio for MySQL either using Wine or CrossOver.

Install dbForge Studio for MySQL on Ubuntu using CrossOver

Step 1: Install prerequisite software

dbForge Studio requires .NET Framework 4.7.2 to function properly. Follow these steps to install it:

1. Open CrossOver. On the Install a Windows Application page, search for “Microsoft .NET Framework 4.7.2” and select it.

2. Click Install.

3. Choose a bottle to install the package into (create a new one if needed, selecting Windows 10 64-bit).

4. The installation will begin and may take some time. If CrossOver freezes, abort and retry.

5. Once installed, click Finish.

Step 2: Install dbForge Studio

1. Ensure the dbForge Studio installer is downloaded and located on your local drive (e.g., Downloads folder).

2. In the bottle, click Install or Install Application into Bottle.

3. Choose Install an unlisted application.

4. Select the dbForge Studio installer and click Choose installer.

5. Follow the installation wizard: select a destination folder, keep the default file associations, and choose your startup optimization preference.

6. Wait for the installation to complete and click Finish. Ensure the Launch dbForge Studio for MySQL checkbox is cleared.

7. After confirmation, dbForge Studio will appear in the bottle.

Install dbForge Studio for MySQL on Ubuntu using Wine

Step 1: Install Wine

1. Open a Terminal (Ctrl + Alt + T).

2. Update APT with:

sudo apt update

3. Install WineHQ with these commands:

wget -nc https://dl.winehq.org/wine-builds/winehq.key 
sudo apt-key add winehq.key 
sudo apt-add-repository 'deb https://dl.winehq.org/wine-builds/ubuntu/ 
bionic main' sudo apt update 
sudo apt install --install-recommends winehq-staging:amd64

4. Verify Wine installation:

wine --version

5. Set up Wine in an isolated environment:

WINEARCH=win64 WINEPREFIX="/home/$USER/.wine_dbforge" winecfg

If Mono is not installed, cancel the dialog; we will install it later.

Step 2: Install Winetricks

1. Install Winetricks:

sudo apt-get install winetricks

2. Verify Winetricks installation:

winetricks --version

3. Choose Install an unlisted application.

Step 3: Run Winetricks and install .NET

1. Run Winetricks to install .NET:

WINEPREFIX="/home/$USER/.wine_dbforge" winetricks 
winetricks dlls list | grep -i "dotnet" 
WINEPREFIX="/home/$USER/.wine_dbforge" winetricks dotnet48

2. Follow the .NET installer’s instructions and click Finish after installation.

Step 4: Download dbForge Studio for MySQL

Download the installer from Devart’s official website.

Step 5: Install dbForge Studio for MySQL with Wine

Run the following command to start the installation:

WINEARCH=win64 WINEPREFIX="/home/$USER/.wine_dbforge" wine 
/home/ubuntu/Downloads/dbforgemysql.exe

Follow the installation wizard.

Step 6: Run dbForge Studio for MySQL

To launch dbForge Studio, use this command:

WINEPREFIX="/home/$USER/.wine_dbforge" wine "/home/ubuntu/.wine_dbforge/drive_c/Program 
Files/Devart/dbForge Studio for MySQL/dbforgemysql.exe"

This should open dbForge Studio for MySQL.

Connect to MySQL Server on Ubuntu from dbForge Studio on Windows

You don't need to install dbForge Studio for MySQL on Ubuntu to work with a MySQL server installed there. If you prefer a Windows environment, you can easily connect to your MySQL server on Ubuntu from dbForge Studio for MySQL installed on Windows. This allows you to access, query, and manage your MySQL databases from your Windows machine without needing to be directly on the Ubuntu server.

1. Ensure MySQL is configured for remote access on Ubuntu.

2. Get the IP address of the Ubuntu server

Use the following command to find the server's IP address:

ip a

3. Connect from dbForge Studio on Windows

• Open dbForge Studio for MySQL on your Windows machine.
• Click on New Connection in the toolbar.
• In the connection dialog, enter the following:
  Server: The IP address of your Ubuntu server (e.g., 192.168.1.100).
  Port: 3306 (default MySQL port).
  User: The MySQL user you created for remote access.
  Password: The password for the MySQL user.
  Database: Optional, the name of the database you want to connect to.
• Click Test Connection to verify the connection, then click OK to save.

4. Troubleshooting

• The MySQL service is running on Ubuntu.
• There are no firewall restrictions on either machine blocking port 3306.
• The MySQL user has sufficient privileges and is allowed to connect from your Windows machine's IP address.

Learn how to install dbForge Studio for MySQL on Ubuntu using Wine. Watch our step-by-step video guide, covering everything from setting up Wine to configuring the necessary .NET frameworks.

See in action

To remove MySQL from Ubuntu 20.04, first, you need to stop MySQL server. For this, run the following command and provide a password for ubuntu when asked:

sudo service mysql stop

Then, run the below command to uninstall MySQL server and agree to remove MySQL packages:

sudo apt-get purge mysql-server mysql-client

In the window that appears, hit Enter:

In the next window, select Yes and again press Enter.

Finally, execute the following commands:

sudo apt-get autoremove

sudo apt-get autoclean

sudo apt update

sudo apt install mysql-server

sudo mysql_secure_installation

sudo systemctl status mysql

sudo mysql

sudo systemctl restart mysql

mysql --version

sudo systemctl status mysql

sudo mysql

exit;

sudo netstat -tuln | grep 3306

sudo mysql_secure_installation

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 127.0.0.1

sudo systemctl restart mysql

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strongpassword'; 
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost'; 
FLUSH PRIVILEGES;

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

symbolic-links=0

sudo systemctl restart mysql

GRANT SELECT ON database_name.* TO 'user'@'localhost'; 
FLUSH PRIVILEGES;

sudo apt install mysql-server

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

mysqlx-bind-address = 127.0.0.1

sudo systemctl restart mysql

sudo apt update 
sudo apt upgrade

sudo apt install unattended-upgrades

mysqldump -u root -p database_name > /path/to/backup/database_name.sql