{"id":383,"date":"2026-08-30T11:54:03","date_gmt":"2026-08-30T11:54:03","guid":{"rendered":"https:\/\/www.vps.tc\/blog\/?p=383"},"modified":"2026-08-30T09:20:37","modified_gmt":"2026-08-30T09:20:37","slug":"how-to-install-and-secure-mysql-on-a-vps","status":"publish","type":"post","link":"https:\/\/www.vps.tc\/blog\/en\/how-to-install-and-secure-mysql-on-a-vps\/","title":{"rendered":"How to Install and Secure MySQL on a VPS"},"content":{"rendered":"<div class=\"aiw-toc\" style=\"border:1px solid #dbe3ea;border-radius:8px;padding:16px 20px;margin:0 0 28px\"><strong>Table of Contents<\/strong><\/p>\n<ol style=\"margin:10px 0 0;padding-left:22px\">\n<li><a href=\"#what-to-check-before-installing-mysql-on-a-vps\">What to Check Before Installing MySQL on a VPS<\/a><\/li>\n<li><a href=\"#installing-mysql-on-ubuntu-and-debian\">Installing MySQL on Ubuntu and Debian<\/a><\/li>\n<li><a href=\"#first-security-steps\">First security steps<\/a><\/li>\n<li><a href=\"#should-mysql-be-exposed-to-the-internet\">Should MySQL be exposed to the internet?<\/a><\/li>\n<li><a href=\"#authentication-and-tls\">Authentication and TLS<\/a><\/li>\n<li><a href=\"#resource-usage-and-basic-mysql-tuning\">Resource usage and basic MySQL tuning<\/a><\/li>\n<li><a href=\"#backups-must-include-a-restore-test\">Backups must include a restore test<\/a><\/li>\n<li><a href=\"#checks-to-keep-after-installation\">Checks to keep after installation<\/a><\/li>\n<li><a href=\"#connecting-the-application-to-mysql\">Connecting the application to MySQL<\/a><\/li>\n<li><a href=\"#final-checks-for-a-mysql-vps\">Final checks for a MySQL VPS<\/a><\/li>\n<li><a href=\"#frequently-asked-questions\">Frequently asked questions<\/a><\/li>\n<\/ol>\n<\/div>\n<h2 id=\"what-to-check-before-installing-mysql-on-a-vps\">What to Check Before Installing MySQL on a VPS<\/h2>\n<p>Installing MySQL on a VPS takes a few minutes. The part that matters starts after <code>apt install<\/code> finishes: who can connect, where the server listens, how the data will be backed up, and what happens when the disk fills up.<\/p>\n<p>Start by matching the operating system to the database package your application actually supports. Ubuntu 22.04 and 24.04 commonly provide MySQL 8.0 packages in their default repositories. Debian 12 normally provides MariaDB 10.11 instead. MariaDB is in the same database family, but it is not interchangeable with MySQL in every configuration or application.<\/p>\n<p>If your application requires MySQL 8.0 or 8.4, check compatibility before choosing the distribution repository, MySQL&#8217;s official repository, or a provider image. A random third-party installer run as root may be quick. Finding every file and repository it added later is not.<\/p>\n<h3>A short pre-install checklist<\/h3>\n<ul>\n<li>Can you connect over SSH with a separate administrative account?<\/li>\n<li>Is the operating system up to date, and is the system clock correct?<\/li>\n<li>Is there enough disk space for the database, binary logs, and backups?<\/li>\n<li>Will the application and MySQL share the VPS, or will another server connect remotely?<\/li>\n<li>Does your provider offer snapshots, and have you remembered that a snapshot is not a complete backup?<\/li>\n<\/ul>\n<p>I install new setups in a Proxmox virtual machine on my Dell OptiPlex 7050 first. If I break <code>bind-address<\/code> or a systemd override there, no customer site notices. There is nothing romantic about experimenting on production.<\/p>\n<h2 id=\"installing-mysql-on-ubuntu-and-debian\">Installing MySQL on Ubuntu and Debian<\/h2>\n<p>The commands below use Ubuntu&#8217;s MySQL package layout. On Debian, check which database server the repository will install before you proceed:<\/p>\n<pre><code>cat \/etc\/os-release\napt-cache policy mysql-server mariadb-server<\/code><\/pre>\n<p>The <code>Candidate<\/code> line shows the package version your configured repositories would select. On Ubuntu, the usual installation is:<\/p>\n<pre><code>sudo apt update\nsudo apt full-upgrade -y\nsudo apt install -y mysql-server\nsudo systemctl enable --now mysql\nsudo systemctl status mysql --no-pager<\/code><\/pre>\n<p><code>enable --now<\/code> starts the service immediately and enables it at boot. Seeing <code>active (running)<\/code> is encouraging. It is not the security review.<\/p>\n<p>Check what is actually running:<\/p>\n<pre><code>mysql --version\nsudo mysql -e \"SELECT VERSION(), USER(), @@hostname;\"<\/code><\/pre>\n<p>I always record <code>@@hostname<\/code>. I once mistook a production server for staging while preparing to remove an old log directory. Shell completion displayed the production hostname before I pressed <code>Enter<\/code>, and I stopped. My production prompts have been red ever since.<\/p>\n<h3>If the service does not start, read the logs<\/h3>\n<p>Before restarting MySQL repeatedly, find out why it failed. A reboot can hide the useful evidence.<\/p>\n<pre><code>sudo journalctl -u mysql -b --no-pager -n 100\nsudo ss -lntp | grep 3306\nsudo systemctl is-enabled mysql<\/code><\/pre>\n<p><code>journalctl<\/code> shows messages from the current boot. <code>ss<\/code> shows which address is listening on TCP port 3306. For a local-only setup, I normally expect <code>127.0.0.1:3306<\/code>, and possibly <code>::1:3306<\/code> when IPv6 is configured.<\/p>\n<h2 id=\"first-security-steps\">First security steps<\/h2>\n<p>Run the package&#8217;s security helper after installation:<\/p>\n<pre><code>sudo mysql_secure_installation<\/code><\/pre>\n<p>The questions vary with the MySQL version and distribution package. They may cover the root authentication method, anonymous users, the test database, and remote root access. Removing anonymous users, removing the test database, and refusing remote root access are sensible choices for most VPS installations.<\/p>\n<p>Ubuntu packages commonly configure the database root account for Unix socket authentication. In that case, connect through the operating-system root account:<\/p>\n<pre><code>sudo mysql<\/code><\/pre>\n<p>Trying <code>mysql -u root -p<\/code> may fail, and that does not necessarily indicate a broken installation. Do not give your application the database root account. Create a separate account with only the permissions it needs.<\/p>\n<h3>Creating the application database and user<\/h3>\n<p>Generate a long, unpredictable password and store it in a password manager. Putting a password directly in a command can leave it in shell history, so I prefer entering it at the client prompt.<\/p>\n<pre><code>sudo mysql<\/code><\/pre>\n<pre><code>CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;\nCREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'long-and-random-password';\nGRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP\nON shop.* TO 'shop_app'@'localhost';\nEXIT;<\/code><\/pre>\n<p><code>utf8mb4<\/code> is a sensible default for current applications because it supports four-byte Unicode characters. The <code>utf8mb4_0900_ai_ci<\/code> collation is available in MySQL 8.0, but it is not available in every MariaDB release. Check first if you are not running MySQL.<\/p>\n<p>The application may not need <code>CREATE<\/code>, <code>ALTER<\/code>, or <code>DROP<\/code> after deployment. I often create a separate migration account with those permissions and keep the runtime account narrower. Convenience on day one becomes confusing access control three months later.<\/p>\n<p>Review the result:<\/p>\n<pre><code>sudo mysql -e \"SHOW GRANTS FOR 'shop_app'@'localhost';\"<\/code><\/pre>\n<p>Then test the same connection method your application will use:<\/p>\n<pre><code>mysql -u shop_app -p -h 127.0.0.1 -D shop -e \"SELECT DATABASE(), CURRENT_USER();\"<\/code><\/pre>\n<p>There is a small difference between <code>localhost<\/code> and <code>127.0.0.1<\/code>. The MySQL client may use a Unix socket for <code>localhost<\/code>; <code>127.0.0.1<\/code> forces TCP. Test both only if your application configuration might use both.<\/p>\n<h2 id=\"should-mysql-be-exposed-to-the-internet\">Should MySQL be exposed to the internet?<\/h2>\n<p>Usually, no. If the web application and MySQL run on the same VPS, keep the database on the loopback address. With Ubuntu&#8217;s MySQL packages, the setting is usually in:<\/p>\n<pre><code>sudo vim \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/pre>\n<p>I use a plain <code>.vimrc<\/code> because the editor I know is the editor I can use on an unfamiliar server. Check the <code>[mysqld]<\/code> section:<\/p>\n<pre><code>[mysqld]\nbind-address = 127.0.0.1<\/code><\/pre>\n<p>Some packages leave the line commented with <code>#<\/code>. Validate the configuration before restarting:<\/p>\n<pre><code>sudo mysqld --validate-config\nsudo systemctl restart mysql\nsudo systemctl status mysql --no-pager\nsudo ss -lntp | grep 3306<\/code><\/pre>\n<p>On the MySQL 8.0 versions I run, a successful <code>mysqld --validate-config<\/code> returns without output. I still check the service status and journal after the restart. A one-character typo can keep the database from starting.<\/p>\n<p>If an application on another VPS must connect, do not open port 3306 to the entire internet. Allow only the known source address in the firewall:<\/p>\n<pre><code>sudo ufw allow from 203.0.113.25 to any port 3306 proto tcp\nsudo ufw status numbered<\/code><\/pre>\n<p>If that address can change, a private network, VPN, or SSH tunnel may be easier to control. If you do not use UFW, apply the same restriction with nftables or your provider&#8217;s security group. A rule for <code>0.0.0.0\/0<\/code> is often described as temporary and then forgotten.<\/p>\n<h3>When a remote database account is necessary<\/h3>\n<p>Limit the MySQL account to the source address instead of creating <code>'shop_app'@'%'<\/code>:<\/p>\n<pre><code>CREATE USER 'shop_app'@'203.0.113.25' IDENTIFIED BY 'long-and-random-password';\nGRANT SELECT, INSERT, UPDATE, DELETE ON shop.*\nTO 'shop_app'@'203.0.113.25';<\/code><\/pre>\n<p>The host portion is part of a MySQL account&#8217;s identity. <code>'shop_app'@'localhost'<\/code> and <code>'shop_app'@'203.0.113.25'<\/code> are different accounts. When a user exists but cannot connect, this usually explains why:<\/p>\n<pre><code>SELECT user, host FROM mysql.user;<\/code><\/pre>\n<h2 id=\"authentication-and-tls\">Authentication and TLS<\/h2>\n<p>MySQL 8.0 uses <code>caching_sha2_password<\/code> for new users by default. Older PHP versions and client libraries may not support it. Before changing the server to an older authentication method, update the application driver if possible.<\/p>\n<p>Inspect the authentication plugin used by existing accounts:<\/p>\n<pre><code>sudo mysql -e \"SELECT user, host, plugin FROM mysql.user;\"<\/code><\/pre>\n<p>For a database connection between separate servers, encrypt the traffic and make the client verify the server certificate. You can inspect some TLS-related settings with:<\/p>\n<pre><code>sudo mysql -e \"SHOW VARIABLES LIKE 'require_secure_transport';\"\nsudo mysql -e \"SHOW VARIABLES LIKE 'tls_version';\"<\/code><\/pre>\n<p>Certificate paths, ownership, and client options depend on the distribution and MySQL version. A self-signed certificate without client-side CA verification can make traffic look encrypted while leaving the server identity unverified.<\/p>\n<p>If remote access is required, configure CA verification in the application and require TLS for the account:<\/p>\n<pre><code>ALTER USER 'shop_app'@'203.0.113.25' REQUIRE SSL;<\/code><\/pre>\n<p>If the account does not exist, this command fails as it should. <code>ALTER USER<\/code> modifies an existing account; it does not create one. Compare the user and host values with your own installation first.<\/p>\n<h2 id=\"resource-usage-and-basic-mysql-tuning\">Resource usage and basic MySQL tuning<\/h2>\n<p>A common VPS mistake is assigning nearly all available RAM to MySQL. If nginx, PHP-FPM, and Redis share the machine, leave room for them and for the operating system. The right <code>innodb_buffer_pool_size<\/code> depends on the workload, connection count, and query patterns.<\/p>\n<p>On a 4 GB VPS running a small application, starting around 1 GB and watching memory pressure is safer than assigning 2 GB by habit. I collect metrics first and tune second. Fancy numbers do not replace measurements.<\/p>\n<p>Find the default option files and inspect the current values:<\/p>\n<pre><code>sudo mysqld --verbose --help 2&gt;\/dev\/null | grep -A 1 \"Default options\"\nsudo mysql -e \"SHOW VARIABLES LIKE 'innodb_buffer_pool_size';\"\nsudo mysql -e \"SHOW VARIABLES LIKE 'max_connections';\"<\/code><\/pre>\n<p>Back up the configuration file before changing it:<\/p>\n<pre><code>sudo cp -a \/etc\/mysql\/mysql.conf.d\/mysqld.cnf \n  \/etc\/mysql\/mysql.conf.d\/mysqld.cnf.$(date +%F)<\/code><\/pre>\n<p>For a small installation, a starting point might look like this:<\/p>\n<pre><code>[mysqld]\ninnodb_buffer_pool_size = 1G\nmax_connections = 100\nslow_query_log = ON\nslow_query_log_file = \/var\/log\/mysql\/mysql-slow.log\nlong_query_time = 1<\/code><\/pre>\n<p>A high <code>max_connections<\/code> value does not create capacity. Every connection consumes memory, and the setting needs to fit the PHP-FPM process count and the application&#8217;s connection behavior. Slow query logging is useful, but it also needs rotation and disk monitoring.<\/p>\n<p>After editing:<\/p>\n<pre><code>sudo mysqld --validate-config\nsudo systemctl restart mysql\nsudo systemctl --no-pager --full status mysql\nsudo journalctl -u mysql -b -n 50 --no-pager<\/code><\/pre>\n<p>If the service fails, restore the configuration backup. I once spent longer admiring a tuning change than I spent checking whether the service could start with it. A database that will not boot is not tuned.<\/p>\n<h2 id=\"backups-must-include-a-restore-test\">Backups must include a restore test<\/h2>\n<p>A provider snapshot is useful, but it is not a complete MySQL backup. Writes may continue while the snapshot is taken, and the snapshot normally stays with the same provider or failure domain. Keep database backups in separate storage as well.<\/p>\n<p>For a small database, <code>mysqldump<\/code> is a reasonable starting point:<\/p>\n<pre><code>sudo install -d -m 700 \/var\/backups\/mysql\nsudo sh -c 'mysqldump --single-transaction --routines --triggers --databases shop &gt; \/var\/backups\/mysql\/shop-$(date +%F).sql'<\/code><\/pre>\n<p>The shell redirection is inside <code>sudo sh -c<\/code> deliberately. If you write <code>sudo mysqldump ... &gt; \/var\/backups\/...<\/code>, your normal shell tries to create the file before <code>sudo<\/code> takes effect.<\/p>\n<p><code>--single-transaction<\/code> gives a consistent dump for InnoDB tables without holding a long table lock. Very large databases may need a physical backup tool such as Percona XtraBackup or MySQL Enterprise Backup instead.<\/p>\n<p>Do not leave the only dump on the VPS. I copy backups to a separate machine with <code>rsync<\/code> and keep versioned, encrypted copies with <code>borgbackup<\/code>. Once a month I restore one. An untested backup is a file with good intentions.<\/p>\n<p>On an Ubuntu package using socket authentication, a dump made with <code>--databases shop<\/code> can be restored in a test environment with:<\/p>\n<pre><code>sudo mysql &lt; \/path\/to\/shop-2026-08-30.sql<\/code><\/pre>\n<p>Use a test database or test VPS, not the live database. Check the character set, stored procedures, user privileges, application connectivity, and the age of the dump.<\/p>\n<h2 id=\"checks-to-keep-after-installation\">Checks to keep after installation<\/h2>\n<p>After MySQL is running, I monitor a few things continuously:<\/p>\n<ul>\n<li>The service state<\/li>\n<li>Disk usage and inode availability<\/li>\n<li>Connection counts and rejected connections<\/li>\n<li>InnoDB buffer pool usage<\/li>\n<li>Slow query volume<\/li>\n<li>The timestamp of the last successful backup<\/li>\n<\/ul>\n<p>On my home Proxmox system, every machine runs <code>node_exporter<\/code> for Prometheus. Disk and RAM alerts have warned me about serious problems before I added detailed MySQL metrics. A MySQL exporter can help, but its account should have only the read permissions it needs. Monitoring does not need root.<\/p>\n<p>For a quick disk check, I still reach for <code>ncdu<\/code>:<\/p>\n<pre><code>sudo ncdu -x \/var\/lib\/mysql\nsudo du -sh \/var\/log\/mysql \/var\/lib\/mysql<\/code><\/pre>\n<p>The <code>-x<\/code> flag keeps the scan on one filesystem. That matters when a mounted backup directory would otherwise distort the result.<\/p>\n<p>Pay attention to retention when binary logging is enabled. Binary logs support point-in-time recovery, but unlimited retention can fill a VPS before the next alert reaches you. Set retention as part of the backup plan, not as an afterthought.<\/p>\n<h3>Security check commands<\/h3>\n<pre><code>sudo ss -lntp | grep 3306\nsudo mysql -e \"SELECT user, host FROM mysql.user;\"\nsudo mysql -e \"SHOW VARIABLES LIKE 'local_infile';\"\nsudo ufw status verbose<\/code><\/pre>\n<p>If the application does not need <code>local_infile<\/code>, consider disabling it. Changing the SSH port is not security by itself; strong authentication, current packages, firewall rules, least privilege, and monitored logs do more.<\/p>\n<p>For the operating-system side, see <strong><a href=\"https:\/\/www.vps.tc\/blog\/en\/10-essential-steps-to-secure-and-harden-your-linux-server\/\">10 Essential Steps to Secure and Harden Your Linux Server<\/a><\/strong>. If you are preparing a fresh machine, <strong><a href=\"https:\/\/www.vps.tc\/blog\/en\/launch-a-secure-vps-in-30-minutes-pro-admin-guide\/\">Launch a Secure VPS in 30 Minutes | Pro Admin Guide<\/a><\/strong> is a useful companion for putting the setup in the right order.<\/p>\n<h2 id=\"connecting-the-application-to-mysql\">Connecting the application to MySQL<\/h2>\n<p>Your application configuration contains the database host, port, name, username, and password. Keep the password out of Git repositories, error messages, and world-readable <code>.env<\/code> files. Check the file&#8217;s owner and permissions too:<\/p>\n<pre><code>sudo chown root:www-data \/var\/www\/shop\/.env\nsudo chmod 640 \/var\/www\/shop\/.env\nsudo -u www-data test -r \/var\/www\/shop\/.env &amp;&amp; echo \"ok\"<\/code><\/pre>\n<p>This assumes the web process runs as <code>www-data<\/code>; verify the service user on your distribution before copying the command.<\/p>\n<p>When the web server and database share a VPS, keep the connection on loopback. On separate servers, use a private network, firewall restrictions, and TLS together. Several small boundaries are easier to inspect than one large assumption.<\/p>\n<p>If you run WordPress, Joomla, or Drupal, look at query caching, PHP-FPM, and page caching alongside database settings. I have seen people debate TTFB by the millisecond while the obvious page cache was disabled. Measure first, then fix the bottleneck you can actually see.<\/p>\n<h2 id=\"final-checks-for-a-mysql-vps\">Final checks for a MySQL VPS<\/h2>\n<p>A green service state is satisfying, but the real test arrives a few days later. Is disk growth monitored? Did the backup complete? Can you restore it? Is port 3306 closed from the public internet? If you cannot answer those questions, the setup is still unfinished.<\/p>\n<p>My minimum setup is an individual application account, MySQL listening on loopback, a provider firewall, backups stored elsewhere, a tested restore, and alerts for service and disk problems. Larger systems may add replication, point-in-time recovery, a separate database server, and connection pooling, but the basic discipline stays the same.<\/p>\n<p>Before I close the terminal, I run <code>SELECT @@hostname;<\/code> once more. The two-second pause has saved me from at least one dangerous command. Which command on your server deserves the same pause?<\/p>\n<h2 id=\"frequently-asked-questions\">Frequently asked questions<\/h2>\n<h3>How do I set a MySQL root password on a VPS?<\/h3>\n<p>Ubuntu packages commonly configure root with Unix socket authentication, allowing access through <code>sudo mysql<\/code>. Create a separate account for applications instead of giving them root. If password-based root access is genuinely required, change the authentication method only after understanding the access path and its risks.<\/p>\n<h3>Should I expose MySQL port 3306 to the internet?<\/h3>\n<p>Not when the application and MySQL share the same VPS; use <code>bind-address = 127.0.0.1<\/code>. If remote access is unavoidable, allow only specific source IPs in the firewall, restrict the MySQL account by host, and use TLS with certificate verification.<\/p>\n<h3>Should I choose MySQL or MariaDB?<\/h3>\n<p>Choose according to the versions supported by your application and its plugins. Debian&#8217;s default MariaDB package can be mistaken for MySQL, causing compatibility problems later. Check <code>apt-cache policy<\/code> and the application documentation before installing.<\/p>\n<h3>How should I test a MySQL VPS backup?<\/h3>\n<p>Restore the dump or physical backup to a separate test VPS and verify that the application can connect. Check the restore time, missing tables, user privileges, character sets, and the actual age of the backup.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A practical walkthrough for installing MySQL on a VPS, limiting access, creating restricted users, tuning resources, and testing real backups.<\/p>\n","protected":false},"author":2,"featured_media":381,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[1417,1414,43,1411,83,29],"class_list":["post-383","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-linux","tag-backups","tag-database-administration","tag-linux","tag-mysql-en","tag-server-security","tag-vps"],"lang":"en","translations":{"en":383,"tr":382},"pll_sync_post":[],"_links":{"self":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts\/383","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/comments?post=383"}],"version-history":[{"count":1,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts\/383\/revisions"}],"predecessor-version":[{"id":385,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts\/383\/revisions\/385"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/media\/381"}],"wp:attachment":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/media?parent=383"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/categories?post=383"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/tags?post=383"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}