In this tutorial, I will go over the steps to clone your MySQL instance on the same server. This tutorial will only cover the steps to accomplish a clone on a Linux server. To ensure you do not cause corruption to the existing MySQL instance you will need to ensure that all files, logs, etc. are run in a completely different directory as the initial instance. And then when you start up the cloned instance it will need to point to the new directory locations. Failure to do this will cause instability and can inflict corruption in the initial instance.
Areas that you will learn about in this tutorial include:
- Starting and stopping MySQL
- Editing the MySQL configuration file
- Locations of important MySQL files
- Knowledge of MySQL file ownership and permissions
- Common MySQL environment variables
- Common cnf variables
- What is the UUID and auto.cnf file
- How to copy or move the data directory
The following steps are required to clone a MySQL 8.0 instance on the same server on Ubuntu 18/Linux:
1. Shutdown the MySQL database instance
Most likely if you are on a Linux server your MySQL instance is started and stopped using systemctl (ubuntu). First, find the current status of the present MySQL instance running on your server. If the instance is up and running then you need to shut down the instance before cloning it.
systemctl status MySQL
systemctl stop mysql
Check to ensure no running processes:
ps xa |grep mysql
Next, locate the my.cnf file and all options files used and ensure you know and understand all the file locations. As they will all need to be different for the cloned instance. It is not uncommon to have a few different conf files for your instance. Therefore, you must do some discovery efforts to see where all the file destinations are as well as port/socket/PID information and locations.
On most Linux servers you can run the ‘locate’ command to find the full path of a file.
- locate my.cnf — this file may redirect you to the actual .cnf file,! includedir to search specific directories for option files
- locate mysql.cnf — this file may redirect you to the actual .cnf file, !includedir to search specific directories for option files
- locate mysqld.cnf
- locate mylogin.cnf — if this does not exist then nothing to do here, otherwise you will need to handle
- locate mysqld-auto.cnf — if this does not exist then nothing to do here, otherwise you will need to handle
View the .cnf files to obtain the information to find all directories referenced.
Your directories may differ from what was on my system. Some of the config files may just include a pointer to another file or options directory.
NOTE: any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf
vi /etc/mysql/mysql.cnf
vi /etc/mysql/conf.d/mysql.cnf
vi /etc/mysql/my.cnf
vi /etc/mysql/mysql.conf.d/mysqld.cnf
On my Ubuntu Linux system, the /etc/mysql/mysql.conf.d/mysqld.cnf contained only the following, so this was fairly easy to identify the handful of file locations.
Your configuration file may contain much more than this.
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
Locate the current MySQL executable and base direction that it is located in. If you are just doing a clone, and not testing running different versions, then you will need to reference these executables later.
locate mysqld
locate mysqld_safe
/usr/bin/mysqld_safe
/usr/sbin/mysqld
Generally, the MySQL base executables and client programs and plugins are installed to /usr/bin and /user/sbin.
2. Create a new directory for the cloned instance
It is advisable to use a descriptive word in the new directories that you are creating and remain consistent, example test or clone would suffice.
a) Create a new config file location
The current MySQL instance has the configuration files in /ect/mysql.
As root os user:
mkdir /etc/mysql_test
cd /etc/mysql_test
mkdir mysql.conf.d
Ensure the necessary ownership and permissions are as follows:
owner = root
perm = drwxr-xr-x
b) Data dir location
The current MySQL instance has the database files in /var/lib/mysql.
as root os user:
mkdir /var/lib/mysql_test
cd /var/lib/mysql_test
chown -R mysql:mysql /var/lib/mysql_test
chmod 750 /var/lib/mysql_test
Ensure the necessary ownership and permissions are as follows:
owner = mysql
perm = drwxr-x---
c) Base file location
the current base file location, where the MySQL executables are located is
/usr
We do not need to change this as we want to use the same installed version of the MySQL executables.
d) Log file location
The current MySQL instance has the log files in /var/log/mysql.
As root os user:
mkdir /var/log/mysql_test
chown mysql:mysql /var/log/mysql_test
chmod 750 /var/log/mysql_test
Ensure the necessary ownership and permissions are as follows:
owner = mysql
perm = drwxr-x---
e) Other directories, for socket and PID file
The current MySQL instance has pid and socket files located in /var/run/mysql.
As root os user
mkdir /var/run/mysqld_test
chown mysql:mysql /var/run/mysqld_test
Ensure the necessary ownership and permissions are as follows:
owner = mysql
perm = drwxr-xr-x
Note: The files in this directory are created when the MySQL instances startup, so nothing will need to be copied here. Likewise, when the MySQL instance is down there should not be any files in this directory for the PID and socket files.
3. Copy all files to the new directory
a) Configuration files
cp -a /etc/mysql/. /etc/mysql_test
/etc/mysql/conf.d/ - contains options file, empty directory
/etc/mysql/mysql.conf.d/ - contains options file so you do want to copy all files from this directory to the new directory
cp -a /etc/mysql/mysql.conf.d/. /etc/mysql_test/mysql.conf.d
b) Database files
cd /var/lib/mysql
cp -a /var/lib/mysql/. /var/lib/mysql_test
Note: -a already implies –preserve=all, that is wider than -p = –preserve=mode,ownership,timestamps.
c) Log files
cd /var/log/mysql
cp -a /var/log/mysql/. /var/log/mysql_test
Remember, we are not going to run a different MySQL version so we do not need a new base directory. Likewise, we do not need to copy the PID or socket files from the original instance as those files are removed upon shutdown of the instance.
The following command is also helpful to ensure all the ownership and permissions are set appropriately:
/usr/sbin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql_test
4. Edit the configuration file for the CLONE .cnf files only
a) First start by ensuring all directory references are edited to the new directory locations
vi /etc/mysql_test/my.cnf
!includedir /etc/mysql_test/mysql.conf.d/
cp /etc/mysql_test/my.cnf /etc/mysql_test/mysql.cnf
vi /etc/mysql_test/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file = /var/run/mysqld_test/mysqld_test.pid
socket = /var/run/mysqld_test/mysqld_test.sock
datadir = /var/lib/mysql_test
log-error = /var/log/mysql_test/test_error.log
port=3308
tmpdir=/var/lib/mysql_test
server_id=10
Share-memory-base-name=test
Notice that there are a few additions on the cloned configuration file. First of all, if the original instance uses the default port 3306 for TCP connections then we must assign a new port for the clone connections to use. Next, by default, the first MySQL instance installed on a Linux server assumes the server_id of 1. So, we need to set a unique server id for the clone instance. As best practice, you should also set unique tmpdir and share-memory-base-name parameters so all resources are unique for the clone instance.
Save changes to the .cnf file
Examples of several other options that must have different values for each server instance
--port=port_num set any port other than the default original instance, the firewall may need to be modified for this new port
--socket={file_name|pipe_name}
--pid-file=file_name set to a file name and location other than the default original instance
If you use the following log file options, their values must differ for each server:
--log-error[=file_name]
--log-bin[=file_name]
--general_log_file=file_name
--slow_query_log_file=file_name
--tmpdir=dir_name
Only need to change the basedir if you are running two different versions of MySQL on the same server, that setup is outside the scope of this tutorial.
--basedir=dir_name
NOTE: Default options are read from the following .cnf files in order, so be sure to account for all of the cnf files when analyzing the initial instance settings.
In my environment I do not have a file in /etc/my.cnf.
/etc/my.cnf /etc/mysql/my.cnf ~/. my.cnf
5. Create separate OS Linux environment variables for the cloned instance, this way when you use MySQL client programs you point to the correct files
It is best practice to create the environment variable file in your home directory, for the user that will start up the clone instance and connect to MySQL client programs.
In my case, it is the root user
cd
vi mysql_test.env
MYSQL_HOME=/etc/mysql_test
USER=mysql
MYSQL_UNIX_PORT=/var/run/mysqld_test/mysqld_test.sock
MYSQLX_UNIX_PORT=/var/run/mysqld_test/mysqldx_test.sock
MYSQL_TCP_PORT=3308
export MYSQL_HOME USER MYSQL_UNIX_PORT MYSQL_TCP_PORT MYSQLX_UNIX_PORT
6. Create the error log file as an empty file to ensure not errors during startup
vi /var/log/mysql_test/test_error.log
chown mysql:mysql /var/log/mysql_test/test_error.log
chmod 775 /var/log/mysql_test/test_error.log
7. Handle the UUID and auto.cnf file
You can start the server for each installation using the command bin/mysqld_safe under its corresponding base directory. The mysqld_safe determines the proper –basedir option to pass to mysqld, and you need specify only the –socket and –port options to mysqld_safe. You should also specify the –defaults-file option file to use for start up.
For the first time starting up the clone you need to be aware of the auto.cnf file and its purpose. When starting mysqld, the MySQL server automatically obtains a UUID. This is a Universal Unique Identifier that sets up the server_uuid parameter and basically is used for replication. If an auto.cnf file is found in the datadir then that UUID is used. Therefore, for the cloned instance be sure to rename the auto.cnf in the copied over new directory as you want a new UUID assigned for the clone instance. If the startup process does not find the auto.cnf file it will auto-generate a new one. You do NOT need to manually create this file.
cd /var/lib/mysql_test
mv auto.cnf auto.cnf. Backup
8. Startup the cloned instance
You should now be ready to start the cloned instance.
Start by setting the env variables to point to clone test instance:
. mysql_test.env
To start up the cloned instance I am using the mysqld_safe program. To ensure the startup uses the new cnf, database files, and logfiles I must specify the file that I defined those configuration options. Additionally, I include the user option so the MySQL instance runs as the os user MySQL. The last character ‘&’ ensures the mysqld daemon will run in the background so that I do not need to keep my session open.
/usr/bin/mysqld_safe --defaults-file=/etc/mysql_test/my.cnf --user=mysql &
9. Sanity test that the clone MySQL instance is now running and users can log in and use the database
Test your user logins to the new clone system.
root@ubuntu01:/var/log/mysql_test# mysql -uroot -p --socket=/var/run/mysqld_test/mysqld_test.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> SHOW VARIABLES LIKE '%datadir%';
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| datadir | /var/lib/mysql_test/ |
+---------------+----------------------+
1 row in set (0.00 sec)
The cloned instance is now up and running and usable. To start up your original MySQL instance you should get out of your ssh session and start a new one so the environment variables are reset. Then start up your original MySQL instance as usual. If all steps were done properly you should now see two instances of MySQL running. Be sure to log into the client programs appropriately. For the original instance, you can log into client MySQL programs as usual. However, for the cloned instance be sure you pass the appropriate socket and/or port. You should set the clone/test environment variables as well, before starting/stopping the database instance or using the MySQL client programs such as MySQL shell or mysqldump.
This tutorial should have provided you with enough information so that you too can clone your MySQL instance and run it simultaneously on the same server, without affecting the original instance.