How to Install MySQL on Ubuntu and Tune Its Performance

How do you install MySQL on Ubuntu?

To install MySQL on Ubuntu, you add the mysql-server package with apt, start the service, then lock down the root account, user privileges and network access. However, one command does not finish the job. Security, privileges and a few core settings follow the install. This guide walks through that whole chain in order.

One note first. We are a digital marketing and web team, not a hosting company. So our explanations rest on the official MySQL documentation and the official Ubuntu server guide, not on claims about running servers. When we are not sure about a version number or a default value, we leave it out.

We wrote this for two readers. The first owns a website or an online store. You want to talk to your hosting provider in the same language and ask the right question. The second runs a VPS or a server. You want commands you can follow step by step. Each section has something for both of you.

The same series covers the MariaDB difference, PostgreSQL, backups with mysqldump and databases in Docker. So we do not repeat those topics here. Instead, we point to them where they matter.

Should you install MySQL on Ubuntu yourself, or leave it to your host?

If you use shared hosting or a managed server, do not install MySQL yourself. The database server already runs, so you only create a database and a user in the control panel. Most plans do not let you touch server-level settings anyway, so the commands in this article will not apply to you.

A self-managed VPS is different. Because of that, install, security and tuning are your job there. To carry that job, you need a routine: keep the server updated, take backups and watch the logs. Without that routine, even a perfect configuration will not save you.

We suggest handing the work to your host or a system administrator in these cases:

• An outage costs you sales directly, and you have no tested way to restore from backup.
• You would change the database of a live store without testing the change first.
• You are setting up remote access, firewall rules or encrypted connections for the first time.
• Replication, clustering or a large data migration, which are hard to undo.

In short, this guide does not push you to do everything alone. It helps you make the decision with open eyes, and it helps you describe the job clearly if you hire someone.

Which MySQL package and version should you choose?

On Ubuntu, the mysql-server package from the distribution repository is the simplest route. The official Ubuntu server guide also points to this package for installation. Also, it arrives with the distribution's own security updates. For a first install, that makes it the option with the fewest surprises.

About version numbers, we will be honest: we do not name one here. Whatever you choose, follow the principle of the current stable release. For example, a release with long support brings less upkeep than a short-lived one. Check the support dates on the official MySQL site.

On Debian, the repositories usually offer MariaDB. If you want Oracle's MySQL, you may need to add the MySQL project's own package repository. The details change between releases, so open the official install documentation and follow it there. Also, if you are weighing MariaDB against MySQL, our companion article in this series compares them.

Does your application ask for a specific version? For example, a CMS may list one on its requirements page. Check that page first. Otherwise an old plugin or theme can break on a newer release.

How do you install MySQL on Ubuntu step by step?

The commands below follow the path in the official Ubuntu Server guide. First, refresh the package list. Then install the package. When the install ends, the service normally starts by itself. Even so, you should check its state yourself.

sudo apt update
sudo apt install mysql-server
sudo service mysql status
sudo ss -tap | grep mysql

The first command refreshes package data. The second installs the server. Third, you see whether the service runs. Finally, the fourth lists the address MySQL listens on. So you answer two questions at once: is the service up, and is it open to the outside?

You make the first connection with this command:

sudo mysql -u root

The Ubuntu guide says this connection asks for no password. The reason is that the root account authenticates through auth_socket. In other words, if your operating system user has the right, you get in. This behavior is safe, because no password crosses the network. However, you should not let your application use this account. We explain why below.

If you must restart the service, the guide shows this command:

sudo systemctl restart mysql.service

What does mysql_secure_installation do, and should you run it?

mysql_secure_installation is an interactive tool that hardens a fresh install. According to the official MySQL documentation, it helps you set a root password, remove root accounts reachable from outside the local host, remove anonymous users and delete the test database that every user can reach. You can also enable the password validation component.

sudo mysql_secure_installation

The tool asks questions and you answer them. The documentation lists 3306 as the default connection port. The --use-default option skips the questions and runs silently, but consider it only for unattended setups.

Is it really necessary? Our short answer is yes, run it. It closes the four gaps that people forget most often, and it does so in one session. Besides, the command is short and easy to reverse.

Notice one thing. The Ubuntu MySQL guide does not mention this tool. Instead, it says the root account stays protected by auth_socket for local access. Therefore not every step may give you the same result in your environment. Read each prompt, and know what you do at steps such as the root password.

You can read the full behavior on the official mysql_secure_installation page.

How do you create MySQL users and privileges?

Give every application its own database and its own user. That way, a hole in one application does not spread to the others, because each account has its own limits. The syntax in the official MySQL documentation is simple: CREATE USER opens the account, then GRANT hands out the privileges.

CREATE DATABASE shop CHARACTER SET utf8mb4;
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'type-a-strong-password-here';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';

The name and the password in this example are made up, so use your own values. The 'localhost' part of the account name means the account can connect only from the same machine. The '%' sign, by contrast, is a wildcard that allows connections from any host.

One principle pays off here: give the application only what it needs. A normal web application mostly reads and writes data, so it does not need the right to create or drop tables. However, some install wizards ask for CREATE during the first setup. In that case, take the privilege back after the setup ends.

REVOKE CREATE, DROP ON shop.* FROM 'shop_app'@'localhost';

Check privileges with SHOW GRANTS after every change. This habit helps you catch an overly broad privilege early.

Should you open MySQL to remote connections?

For most websites, no. Because the database and the application share one machine, MySQL only needs to listen on the local address. The Ubuntu guide puts the bind-address setting in /etc/mysql/mysql.conf.d/mysqld.cnf. After you change the value, you must restart the service.

However, remote access makes sense in two cases only. Either the application and the database live on separate machines, or you need to connect with a management tool. In the second case, an SSH tunnel is a safer path than opening port 3306 to everyone.

ssh -L 3307:127.0.0.1:3306 user@192.0.2.10

In this command, 192.0.2.10 is a documentation example address. The command links port 3307 on your own computer to MySQL on the server. So MySQL never faces the internet, while your management tool connects to a local port.

To see what the outside world can reach, you can confirm the server address with our IP lookup tool. Then check the firewall rules in your provider's documentation. Before you open remote access, we suggest asking your hosting provider. Also, a wrong rule can expose your database to automated scanners.

Where is the MySQL config file, and which files does it read?

MySQL reads its option files in a fixed order at startup. According to the official documentation, on Linux these are /etc/my.cnf, /etc/mysql/my.cnf, $MYSQL_HOME/my.cnf if it exists, any file you pass with --defaults-extra-file, and ~/.my.cnf. A file read later wins.

Options live in groups that start with a square bracket. The group for the server is [mysqld]. For example, you add a block like this:

[mysqld]
innodb_buffer_pool_size = 2G
slow_query_log = 1

If you are not sure which files MySQL reads, the documentation suggests this command:

mysqld --verbose --help

The start of the output lists the files MySQL looks for and the groups it knows. On Ubuntu, you usually write your settings into files under /etc/mysql/. Be careful, though: if you define the same option in two places, the last one wins. So when a setting seems to do nothing, first look for a second, conflicting definition.

The documentation also says MySQL ignores option files that anyone can write to, for security. So keep file permissions tight.

Where should you start with MySQL performance tuning?

First, do not tune before you measure. Most performance problems come from badly written queries and missing indexes, not from memory settings. So your order should be this: find the slow query first, fix the index and the query next, and look at memory last.

The table below summarizes four layers and when each one pays off. It is a comparison without numbers, because the gain depends entirely on your workload.

The last row sits outside MySQL settings, yet it deserves a mention. Serving a result from a cache is often cheaper than computing it on every request. We cover that topic in our guide to how caching works with Redis and Memcached.

What is innodb_buffer_pool_size, and how do you set it?

innodb_buffer_pool_size is the size of the memory area where InnoDB keeps table and index data. The MySQL documentation describes it as an area in main memory where InnoDB caches data as it reads it. Hot data comes from memory, so disk reads drop. According to the documentation, the default is 128 MB.

How much should you give it? The official documentation says that on dedicated servers, up to 80% of physical memory is often assigned to the buffer pool. Look at two words there: "up to" and "dedicated". Instead, it is a ceiling, not a target. It also applies only to machines reserved for the database.

If the web server, PHP and the database share one machine, leave memory for the operating system, the PHP processes and connections too. Example calculation: on a shared machine with 8 GB of memory, the 80% ceiling comes to about 6.4 GB. Since PHP and the operating system will want much of that, you start lower, watch memory use and raise the value if needed. This is a starting approach, not a guarantee.

You write the permanent value into the option file:

[mysqld]
innodb_buffer_pool_size = 2G

The 2G here is only an example. Pick your own value from your data size and memory.

Can you change innodb_buffer_pool_size on a live server?

Yes. According to the MySQL documentation, you can resize the buffer pool without a restart. A SET GLOBAL command is enough. However, the value must be a multiple of innodb_buffer_pool_chunk_size times innodb_buffer_pool_instances. Otherwise MySQL rounds it up to the nearest valid multiple.

SELECT @@innodb_buffer_pool_size;
SET GLOBAL innodb_buffer_pool_size = 2147483648;
SHOW STATUS WHERE Variable_name = 'Innodb_buffer_pool_resize_status';

The third command shows the progress of the resize. The documentation also says active transactions must finish before the resize begins. So a change during a busy hour can cause a wait.

There is one more warning. According to the documentation, the number of chunks, which is the pool size divided by the chunk size, should not exceed 1000 if you want to avoid performance problems. For example, small servers never come close to that limit. On very large memory, though, you should check it.

SET GLOBAL affects only the running server. After a restart, the value returns to what the option file says. So write the value you want to keep into my.cnf as well. On a production system, plan this work hours ahead, so a possible wait does not catch you off guard.

For details, read the buffer pool resizing page and the buffer pool overview.

How do you turn on the MySQL slow query log?

The slow query log writes queries that run longer than a set time into a file. According to the MySQL documentation, slow_query_log is off by default and long_query_time defaults to 10 seconds. So if you do nothing, a query lands in the log only after it passes 10 seconds. For most websites, that threshold is too high.

To switch it on temporarily on a running server, use these commands:

SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;

The file path and the 1 second threshold are examples. The MySQL process must be able to write to that folder, or no log appears. So choose the threshold to fit your site. Starting high and lowering it later keeps the log from swelling.

To make it permanent, write the same three lines under the [mysqld] group. A SET GLOBAL change disappears after a restart.

The log_queries_not_using_indexes option in the documentation records queries that skip indexes, whatever their runtime. On small tables, though, skipping an index can be normal. So it fills the log fast. Starting with the time threshold alone is usually easier to read.

How do you read the slow query log?

Still, a long log file is hard to read by hand. MySQL ships a summary tool for that job called mysqldumpslow. It groups similar queries and shows how often each ran and how long it took. So you can tell a one-off slowdown from a repeating one.

mysqldumpslow /var/log/mysql/mysql-slow.log

Ask this first: which query burns the most total time? One heavy query and a medium query that runs a thousand times need different fixes. You fix the first with an index or a rewrite. You fix the second with a cache or with better application code.

Copy a query from the log and inspect it with EXPLAIN. We cover that below. Remember that the log can hold personal data, because query text may carry parameter values. Do not put the file in a public folder, and clean it before you share it.

For details, read the slow query log page.

What is an index, and when does it help?

An index is an extra data structure that helps you reach rows faster. Think of the index at the back of a book. Without it, you read the whole book to find one word. Likewise, MySQL scans the table row by row when no index exists.

If you often search a column with WHERE, JOIN or ORDER BY, that column is a candidate. For example, if an online store queries its orders table by customer all day, you index the customer id column.

CREATE INDEX idx_orders_customer ON orders (customer_id);
SHOW INDEX FROM orders;

An index is not free, however. Every write updates the index too, and it takes disk space. Also, many needless indexes slow down writes. So do not index every column. Add indexes for the real queries you see in the log.

One more note: a column with very few distinct values is often a poor candidate. Also, column order matters in a composite index. According to the documentation, MySQL in some cases uses only the leftmost prefix of the key.

If you are new to SQL, our SQL learning roadmap is a good start. For interviews, try our SQL interview questions guide.

How do you read EXPLAIN output?

EXPLAIN shows how MySQL plans to run a query. You put it in front of the query. The documentation says it works for SELECT, DELETE, INSERT, REPLACE and UPDATE statements. In the output, you see which index MySQL picked and how many rows it expects to examine.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

These are the most important columns:

• type: tells you how MySQL reads the table, and ALL means a full table scan.
• possible_keys: lists the indexes MySQL could use.
• key: shows the index MySQL actually chose.
• rows: gives an estimate of the rows MySQL must examine.
• Extra: adds detail, and Using filesort and Using temporary deserve attention.

The documentation says a full table scan is normally not good and usually very bad. Your goal on large tables is to move type away from ALL and toward values such as ref, range or const, which use an index.

Handle the rows column with care. The documentation says that for InnoDB tables this number is an estimate and may not always be exact. So do not treat one number as absolute truth. Compare the before and after values instead.

Which EXPLAIN values are warning signs?

The table collects the values you will meet most often and what they mean. The meanings come from the MySQL EXPLAIN output page.

The documentation names Using filesort and Using temporary as things to watch if you want queries as fast as possible. On a small table, though, they may cause no harm. So read the values together with the row count.

Here is an example flow. In the log, you found the query that lists orders by customer. EXPLAIN shows type = ALL and a high rows estimate. You add the index, run EXPLAIN again and see type change to ref. That proves the improvement.

MySQL also offers EXPLAIN ANALYZE for real run times. We did not verify the details of that option on the official page, so check the documentation for your own version before you use it. You can find every column on the EXPLAIN output page.

Should you choose MariaDB instead of MySQL?

Most of the SQL in this guide also runs on MariaDB, but not every setting name or default matches. So if you use MariaDB, verify the commands against the documentation for your own version. We collected the differences in a separate article that compares MariaDB and MySQL.

Ask yourself this: which one does your application require? Many CMS platforms support both. In practice, going with whatever your hosting provider offers usually causes the fewest problems. This choice alone will not make your site fast. Index and query quality usually matter more.

How does MySQL tuning affect site speed and SEO?

A dynamic page sends queries to the database to build its response. If a query runs slowly, the server takes longer to answer. That delays the page. Visitors wait, and search engines also favor slow sites less. We explain this link in detail in our article on how site speed affects SEO.

In e-commerce, the effect is more direct, because product lists, filters and carts run many queries. We cover that in our e-commerce page speed article. To get a speed report card for a page, a Lighthouse performance test is a good start.

Keep one thing in mind, though. Lighthouse measures the browser side. It does not point at a slow database query directly, and it shows up only as server response time. So to find the real culprit, you must read the slow query log.

If your site runs custom software, the database design and query quality get set during development. For a project like that, see our custom software development service. We can also review your site's speed problems together under SEO consulting.

What are the most common mistakes when you set up MySQL?

Most mistakes come from haste, not from ignorance. The list below gathers the common ones and a simple fix for each.

• Connecting the application with the root account: give each application its own user.
• Opening port 3306 to everyone: start with local listening and an SSH tunnel.
• Growing the buffer pool without measuring: read the slow query log first.
• Indexing every column: add indexes only for queries you see in the log.
• Skipping backups: always take a backup before you change settings.
• Defining one option in two files: check conflicts with mysqld --verbose --help.

Backups deserve their own heading. We cover the strategy in our website backup strategy guide, and we cover mysqldump for databases in the companion article of this series. The database is the most valuable part of a site. Every change you make without a backup is a risk.

Hosting choice is part of this decision too. To learn which plan allows database settings, read our guide to choosing web hosting.

What should you do right after you install MySQL on Ubuntu?

When the install ends, follow the order below. Each step assumes the previous one worked. Run the list once, then review it again every three months.

1. Confirm the service runs and see which address it listens on.
2. Read and apply the mysql_secure_installation steps.
3. Create a separate database and user for each application.
4. Check the privileges with SHOW GRANTS.
5. Keep remote access closed if you do not need it.
6. Turn on the slow query log with a sensible threshold.
7. Inspect the first three logged queries with EXPLAIN.
8. Add an index if needed and measure again.
9. Set the buffer pool size from your real memory use.
10. Take a backup and test a restore once.

The point of this list is the order of work. Each item could be a topic on its own, but the order matters. Security and backups come first, and speed tuning comes after.

If you are unsure about going alone, handing the job to a named expert is the wise move. We do not run servers. We do help with the speed and visibility problems of your website, through our web design service and our e-commerce consulting.