MySQL 57 VM

These notes describe the process required to install MySQL 5.7.x onto Ubuntu 24.04 LTS. Ubuntu 24.04 ships with MySQL 8+ and while there are ways to install different versions of MySQL these have not supported MySQL 5.7. Therefore, these instructions describe installing the MySQL 5.7.44 binary distribution onto Ubuntu.

Overview

This guide assumes that you have just created a Ubuntu 24.04 LTS virtual machine and are able to SSH into it.

This guide will describe the following steps in detail:

  1. Change to the 'root' account
  2. Update Ubuntu
  3. Change default timezone
  4. Install dependencies
  5. Configure 'mysql' user and group
  6. Prepare additional volume
  7. Retrieve and unzip MySQL
  8. Create data and replication directories
  9. Initialising the database
  10. Configuring MySQL
  11. Running the database for the first time
  12. Connecting with the 'mysql' client
  13. Installing the startup script in /etc/init.d
  14. Setup replication

Change to the 'root' account

First, run the following command to run sub-shell as 'root'. The shell prompt should change from a dollar sign - '$' - to a hash '#': 

$ sudo bash
#

Update Ubuntu

Run the following two commands. Keep an an eye on the output as they may complain they cannot connect to places and you may need to re-run them. 

# apt update
# apt dist-upgrade

Change default timezone

# dpkg-reconfigure tzdata

Install dependencies

MySQL links to the following library as 'libaio.so.1'. 

# apt install libaio1t64
# ln -sf /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/libaio.so.1

Configure 'mysql' user and group

The following commands will add the 'mysql' group and user, respectively. 
# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql

Prepare additional volume

Now you need to decide where you intend to install the MySQL files. By default, MySQL will want you to unzip into '/usr/local'. If you are happy with unzipping into '/usr/local/', there will be fewer files that need to be edited.

The following assumes that you want to include into a new volume mounted on '/data' and the MySQL archived will be unzipped into there. Additionally, the data and replication directories will be placed in that directory.

First create the directory you intend to mount the volume to, e.g: 

# mkdir /data

The following assumes that the new volume is available as the device '/dev/xvdbb'. Apart from selecting 'p' for new partition, and 'w' at the end to write to disk, you should just select the default values. 

# fdisk /dev/xvdbb
Command (m for help): n
Partition type
	p	primary (0 primary, 0 extended, 4 free)
	e	extended (container for logical partitions)
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-2097151999, default 2048): 2028
Last sector, +/-sectors or +/-size{K,M,G,T,P} (2048-2097151999, default 2097151999): 2097151999

Created a new partition 1 of type 'Linux' and of size 1000 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.

If the volume has been properly partitioned, there should now be a file '/dev/xvdbb1'. The following lines will format that partition as 'EXT4' and will label the volume as 'DATA'. 

# mkfs -t ext4 /dev/xvdbb1
# e2label /dev/xvdbb1 DATA

Then edit the 'fstab' file and add the following line: 

# nano /etc/fstab

LABEL=DATA      /data           ext4    defaults        0 0

On 'systemd' based systems, before attempting to mount the volume, you will need to do a daemon reload to re-read the fstab file: 

# systemctl daemon-reload

To test that the 'fstab' file has been edited appropriately, attempt to mount the new volume. 

# mount /data

You can confirm that the volume has been mounted by checking that a 'lost+found' folder appears in the directory. 

# ls /data
lost+found

Retrieve and unzip MySQL

MySQL is available from MySQL Community Downloads page. For earlier verions of MySQL, you will need to follow the Download Archives link; then MySQL Community Server link.

On that page, you can then select the Production Version, Operating System, and OS Version, you are targeting.

For my purposes, choosing Production Version as '5.7.44', Operating System as 'Linux - Generic', and OS Version as 'Linux - Generic (glibc 2.12) (x86, 64-bit)', it provides a link to:
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

Assuming that you are still located in your login user's home directory you can retrieve the file using: 

# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

The following commands will change into the '/data' directory, then extract the archive file into a versioned mysql directory. Finally, for convenience, the MySQL installation instructions suggest that a symbolic link be created that links to the versioned directory. 

# cd /data
# tar xvf ~ubuntu/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
# ln -sf mysql-5.7.44-linux-glibc2.12-x86_64 mysql

It is also recommended that create a 'mysql-files' directory, that the MySQL server is able to run scripts from. 

# mkdir             /data/mysql/mysql-files
# chown mysql:mysql /data/mysql/mysql-files
# chmod 750         /data/mysql/mysql-files

Create data and replication directories

These instructions assume that you want to create with the new volume:

  1. a data directory - where the database table files are stored; and
  2. a replication directory - where the binary log is stored.
# mkdir -p          /data/{mysql-data,mysql-replication,mysql-log}
# chown mysql:mysql /data/{mysql-data,mysql-replication,mysql-log}
# chmod 750         /data/{mysql-data,mysql-replication,mysql-log}

# sudo -u mysql touch /data/mysql-log/localhost.localdomain.err

Initialising the database

# cd /data/mysql
# bin/mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql-data --explicit_defaults_for_timestamp

You should get something like: 

2025-03-15T04:20:59.808316Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-03-15T04:20:59.851462Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-03-15T04:20:59.916996Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e62e736d-0154-11f0-b1d7-02dc038140ef.
2025-03-15T04:20:59.920915Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-03-15T04:21:00.747394Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2025-03-15T04:21:00.747628Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2025-03-15T04:21:00.749928Z 0 [Warning] CA certificate ca.pem is self signed.
2025-03-15T04:21:00.945015Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

If you get the following error, refer above to the section above titled "Install dependencies":

bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

I don't think the following is necessary - which is suggested by the MySQL install documentation - as there are pem files already in the '/data/mysql-data' directory... 

# bin/mysql_ssl_rsa_setup --user=mysql --basedir=/data/mysql --datadir=/data/mysql-data

Configuring MySQL

To configure MySQL, a MySQL configuration file should be created: '/etc/my.cnf'. 

# nano /etc/my.cnf

The following should be copied into it: 

[mysqld]
socket=/tmp/mysqld.sock

bind-address = 0.0.0.0

max_allowed_packet      = 1G
innodb_log_file_size    = 1G
innodb_buffer_pool_size = 1G

event_scheduler=on

sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION"

log_bin_trust_function_creators=1

user=mysql
port=3306

basedir=/data/mysql
datadir=/data/mysql-data

log-error=/data/mysql-log/localhost.localdomain.err

server_id         = 10
read_only         = 0
log_bin           = /data/mysql-replication/mysql-bin.log
relay_log         = /data/mysql-replication/mysql-relay.log
log_slave_updates = 1
sync_binlog       = 1

[mysql]
socket=/tmp/mysqld.sock

[mysqladmin]
socket=/tmp/mysqld.sock

Running the database for the first time

First add a directory owned by 'mysql' for the server to create its "pid" and "sock" files in.

Correction: on Ubuntu /var/run aka /run is mounted as tmpfs so the following directory would no longer be there on reboot. 

# mkdir -p          /var/run/mysqld
# chown mysql:mysql /var/run/mysqld

To run MySQL for the first time, run the following: 

# /data/mysql/bin/mysqld_safe --user=mysql &

Connecting with the 'mysql' client

Note: if using a "minimized" Ubuntu Server installation, you will need to install 'libncurses6'. 

# apt install libncurses6

The '/data/mysql/bin/mysql' executable links against the libraries 'libncurses.so.5' and 'libtinfo.so.6', however, Ubuntu 24.04 LST only ships with only 'libncurses.so.6'. To get around this, create the following symbolic links: 

# ln -sf /usr/lib/x86_64-linux-gnu/libncurses.so.6 /usr/lib/libncurses.so.5
# ln -sf /usr/lib/x86_64-linux-gnu/libncurses.so.6 /usr/lib/libtinfo.so.5
# ln -sf /usr/lib/x86_64-linux-gnu/libncurses.so.6 /usr/lib/libtinfo.so

Then enter the following to connect. As the password is blank due to initialising the database with the --initialize-insecure flag, just hit enter when prompted for a password: 

# /data/mysql/bin/mysql -u root -p
Enter password:
To change the root password: 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

If you want to connect as 'root' from other computers you will need to do similar to the following: 

mysql> CREATE USER 'root'@'{ip address}' IDENTIFIED BY '{password}';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'{ip address}' WITH GRANT OPTION;

Installing the startup script in /etc/init.d

When ready, exit to complete the setup. 

mysql> exit;

# cp /data/mysql/support-files/mysql.server /etc/init.d
# sudo systemctl enable mysql.server

Run the following to shutdown the existing 'mysqld_safe' process: 

# /data/mysql/bin/mysqladmin -u root -p shutdown

Then you should be able to startup MySQL again using the standard 'service' command: 

# service mysql start

This can be checked by running the following: 

# ps -A | grep "mysqld"

You should see something similar to: 

  20112 ?        00:00:00 mysqld_safe
  20398 ?        00:00:00 mysqld

Setup replication

If you want to setup replication, you will need to create another VM and perform the above steps except when creating the 'my.cnf' file.

Change: 

server_id         = 10
read_only         = 0

To: 

server_id         = 11
read_only         = 1

This ensures that both servers have their own ID, and that the replicant server is read only.

In each MySQL instance, run the following to give it permission to act as a replication client. The '{IP Address}' must be the IP address of the other instance. 

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'{IP Address}' IDENTIFIED BY '{Password}';

Note, you will of course need to configure any network firewalls to allow the instances to communicate with each other on the MySQL port.

Finally, 

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST     = '{IP Address}',
                 MASTER_USER     = 'repl',
                 MASTER_PASSWORD = '{Password}',
                 MASTER_LOG_FILE = 'mysql-bin.000001',
                 MASTER_LOG_POS  = 0,
                 MASTER_SSL      = 1;

Note: A more ellaborate setup may include generating your own Certificate Authority certificates and keys. See https://vmsetup.com/ubuntu_20/ for more details. 

START SLAVE;

SHOW SLAVE STATUS;