How to Stop MySQL from Auto-Updating on Ubuntu Server 22.04 (and Verify It Won’t Happen)

If you run MySQL on Ubuntu Server 22.04, you already know the problem:
Ubuntu is very helpful about updates — sometimes too helpful.

An unattended MySQL upgrade can:

• Restart the service

• Break replication

• Introduce subtle behavior changes

• Ruin a carefully controlled production setup

This post shows how to stop MySQL from auto-updating and how to prove in advance that no update will sneak in.

No hand-waving. Real commands. Verifiable results.

---

The Real Culprit: Unattended Upgrades

Ubuntu uses a background service called unattended-upgrades to silently install updates. That includes MySQL unless you explicitly stop it.

You have three levels of control. Most servers should use Level 1 or 2.

---

Option 1 — Freeze MySQL with apt-mark hold (Recommended)

This is the simplest and safest approach.

sudo apt-mark hold mysql-server mysql-client mysql-common

What this does:

• APT will refuse to upgrade these packages

• Even if someone runs apt upgrade

• Even if unattended-upgrades runs overnight

Verify the hold:

apt-mark showhold

Expected output:

mysql-server
mysql-client
mysql-common

To undo later:

sudo apt-mark unhold mysql-server mysql-client mysql-common

---

Option 2 — Let Ubuntu Update Everything Except MySQL

If you want security updates for the OS but not for MySQL, blacklist it from unattended upgrades.

Edit the config:

sudo nano /etc/apt/apt.conf.d/50unattended-upgrades

Find:

Unattended-Upgrade::Package-Blacklist {
};

Add:

Unattended-Upgrade::Package-Blacklist {
        "mysql-server";
        "mysql-client";
        "mysql-common";
};

Restart the service:

sudo systemctl restart unattended-upgrades

This keeps the system secure while freezing MySQL.

---

Option 3 — Disable Automatic Updates Entirely (Use With Caution)

This is a blunt instrument.

sudo systemctl disable --now unattended-upgrades

Or:

sudo nano /etc/apt/apt.conf.d/20auto-upgrades

Set:

APT::Periodic::Unattended-Upgrade "0";

Only do this if you commit to manual patching.

---

How to Verify Beforehand That MySQL Will NOT Update

This is the part most guides skip. Don’t trust configuration — verify behavior.

---

1. Check What APT Thinks Is Upgradable

apt list --upgradable | grep -i mysql

Correct result: no output
If you see MySQL packages here, they are not frozen.

---

2. Simulate a Full Upgrade (Dry Run)

sudo apt -s upgrade | grep -i mysql

The -s flag means simulate only.
Nothing will be installed.

Correct result: no MySQL packages listed.

---

3. Simulate Unattended Upgrades Directly

This tests the exact logic Ubuntu uses overnight.

sudo unattended-upgrades --dry-run --debug | grep -i mysql

If MySQL is held or blacklisted, it will be skipped.

---

4. Double-Check Package Holds

apt-mark showhold

If MySQL isn’t listed, it’s not protected.

---

5. (Optional) Verify Pinning Rules

If you’re using apt pinning:

apt-cache policy mysql-server

You should see a pin priority preventing upgrades.

---

The “I’m Safe” Checklist

Run these three commands:

apt list --upgradable | grep -i mysql
apt -s upgrade | grep -i mysql
unattended-upgrades --dry-run --debug | grep -i mysql

If all three return empty, MySQL will not update. Period.

---

Final Advice

• Production server? Use apt-mark hold

• Security-conscious server? Use unattended-upgrade blacklist

• Never rely on assumptions — always simulate

Ubuntu does exactly what you tell it to do.
The problem is most admins never tell it not to touch MySQL.

Useful sed / awk liners for MySQL

http://code.openark.org/blog/mysql/useful-sed-awk-liners-for-mysql

MySQL C# tutorial

http://zetcode.com/db/mysqlcsharptutorial/

InnoDB ibdata1 never shrinks after data is removed

http://bugs.mysql.com/bug.php?id=1341

Time offsets to UTC in MySQL

The offset from UTC is given in the format ±[hh]:[mm], ±[hh][mm], or ±[hh]. It is appended to the time. The offset from UTC changes with daylight saving time, e.g. a time offset in Chicago, would be "-06:00" for the winter (Central Standard Time) and "-05:00" for the summer (Central Daylight Time).

The following times all refer to the same moment: "18:30Z", "22:30+04", "1130-0700", and "15:00-03:30". Nautical time zone letters are not used with the exception of Z. To calculate UTC time one has to subtract the offset from the local time, e.g. for "15:00-03:30" do 15:00 − (−03:30) to get 18:30 UTC.

The offset can also be used in the case where the UTC time is known, but the local offset is not. In this case the offset is "-00:00", which is semantically different from "Z" or "+00:00", as these imply that UTC is the preferred reference point for those times.

SELECT CONVERT_TZ('2004-01-01 11:30:00','-07:00','+00:00');

Insert data into MySQL from a CSV file

LOAD DATA INFILE '/var/lib/mysql/btdata.csv' INTO TABLE btpl.parset FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (idsystem, name, dataurl, description);

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

mysqlimport — A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax.

Invoke mysqlimport like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

Command invocation for a CSV file exported from Excel:

mysqlimport --fields-terminated-by=, --ignore-lines=1 -u root -p rolling mbf_symbols.csv

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html

MySQL and REGEX

"A regular expression is a powerful way of specifying a pattern for a complex search." (Excerpt from MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/regexp.html)

The financial institutions use letters to identify the months of the year in the following way:

F - January

G - February

H - March

J - April

K - May

M - June

N - July

Q - August

U - September

V - October

X - November

Z - December

So you can have a date in the form F10 which identifies January 2010 or J11 which identifies April 2011.

Let say you have a prices table in a MySQL database and you want to check that the values in the field contract in that table are correct according to the following rules:

- The first character in the field should be a letter valid for a month (F - January, G - February, etc).

- The second and third chars in the field should be a decimal digit (0..9).

You need to specify a query in SQL for MySQL that identifies all the records that have a wrong contract according to the above rules.

There are several ways of doing this in MySQL, one could be a query like the following:

SELECT * FROM prices

WHERE substr(contract, 1, 1) NOT IN ('F', 'G', 'H', 'J', 'K', 'M', 'N', 'Q', 'U', 'V', 'X', 'Z')

OR substr(contract, 2, 1) NOT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)

OR substr(contract, 3, 1) NOT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);

Other query could be:

SELECT * FROM prices

WHERE substr(contract, 1, 1) NOT IN ('F', 'G', 'H', 'J', 'K', 'M', 'N', 'Q', 'U', 'V', 'X', 'Z')

OR substr(contract, 2, 1) NOT BETWEEN 0 AND 9

OR substr(contract, 3, 1) NOT BETWEEN 0 AND 9;

As you can see the queries are long so here is where you can use the power of REGEX (Regular Expressions) that MySQL implements as an extension to the SQL language.

Using REGEX the query will be:

SELECT * FROM prices

WHERE contract NOT REGEXP '^[FGHJKMNQUVXZ][0-9]{2}';

The REGEX expression is negated (NOT REGEXP) so the query is going to return values in the field contract of the prices table that don't have at the beginning (the symbol ^) a letter F or G or H, etc. (the part which is specified between square brackets, which is called a class) and 2 digits (the class [0-9] with the number of occurrences, part {2}).

As you can see REGEX provides a concise and flexible way for matching strings of text against a certain pattern.

Have a good day.

--

J. E. Aneiros

GNU/Linux User #190716 en http://counter.li.org

perl -e '$_=pack(c5,0105,0107,0123,0132,(1<<3)+2);y[A-Z][N-ZA-M];print;'

PK fingerprint: 5179 917E 5B34 F073 E11A  AFB3 4CB3 5301 4A80 F674

Using MySQL temporary table

1. Create temporary table with the settlements table structure:

CREATE TEMPORARY TABLE sett LIKE settlements;

2. Insert into the temporary table the data from the settlements for a certain date:

INSERT INTO sett (date,symbol,contract,price)
SELECT date,symbol,contract,price
FROM pltracker.settlements
WHERE date = '2010-09-03';

2a. You can do the previous two steps in only one:

CREATE TEMPORARY TABLE sett
SELECT date,symbol,contract,price
FROM settlements
WHERE date = '2010-09-03';

3. Update the date of the data at the temporary table for the specific date of the settlements:

UPDATE sett
SET date = '2010-09-06'
WHERE date = '2010-09-03';

3a. Since you are updating all the data in the table you can use this SQL statement instead:

UPDATE sett
SET date = '2010-09-06';

4. Insert data back into the settlements table:

INSERT INTO settlements (date,symbol,contract,price)
SELECT *
FROM sett;

5. Check that the new data is in place at the settlements table:

SELECT * FROM settlements
WHERE date = '2010-09-06';

6. The temporary table will be automatically drop when you close the session or you can drop it as any other table:

DROP TABLE sett;

Yesterday in MySQL query

SELECT * FROM table
WHERE date = CURDATE() - INTERVAL 1 DAY;

REPLACE vs. INSERT ... ON DUPLICATE UPDATE in MySQL

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts.

For another MySQL extension to standard SQL — that either inserts or updates — it should be use “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

REPLACE (http://dev.mysql.com/doc/refman/5.1/en/replace.html)

INSERT ... ON DUPLICATE UPDATE (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html)

Recover MySQL Database root password

If you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.

Login as root to the Unix-like (Unix, Linux or BSD) machine with the MySQL server.

Stop the MySQL server by using either of the following command

#/etc/init.d/mysql stop

Now you need to Start MySQL server without password

# mysqld_safe --skip-grant-tables &

Connect to mysql server using mysql client with the following command

# mysql -u root

Now you should be having mysql prompt

mysql>

Now you need to Setup new MySQL root user password

mysql> use mysql;

mysql> update user set password=PASSWORD(”newrootpassword”) where user=’root’;

mysql> flush privileges;

mysql> quit

Note: Replace newrootpassword with the new root password for MySQL server. Flush Privileges is needed to making the password change effect immediately.

Now you need to Stop MySQL Server using the following command

# /etc/init.d/mysql stop

Test Your New Mysql root password

First you need to start mysql server using the following command

# /etc/init.d/mysql start

# mysql -u root -p

Now it will prompt for root password and enter your new root password.

Taken from: http://www.debianadmin.com/recover-mysql-database-root-password.html