To setup point-in-time recovery for MDS using Object Storage, these are the prerequisites:
- a MDS instance running
- a backup plan (default)
- a compute instance
- an Object Storage Bucket
In this article, I won’t focus on how to create a MDS instance, a compute instance, enable backups and Object Storage Bucket, this is easy and there is already a lot of literature about them (you can also check several of my previous articles).
This is part 1 of the Point-in-Time Recovery in OCI MDS series. It’s only about streaming the binary logs to Object Store. In part 2, we will see how to use them and perform PITR.
Saving the binary logs
In fact most of the work will be to setup the compute instance to store the binary logs coming from MDS (one single compute instance could stream those binlogs from multiple MDS source).
On the compute instance we need some extra packages:
- mysql-client (we need mysqlbinlog and mysql_config_editor)
- mysql-shell (because I like to use it)
The first two packages are available in the MySQL Repositories and the last one needs EPEL on rpm based distributions.
We need to create an
ACCESS_KEY_ID and a
SECRET_ACCESS_KEY to access our Object Storage Bucket:
We copy these keys on one single line separated with colon ‘
:‘ in a file, for example
And we mount it like this:
# chmod 600 ~/.passwd-ocifs # mkdir /mnt/oci # s3fs lefred-bucket /mnt/oci -o endpoint=us-ashburn-1 \ > -o passwd_file=~/.passwd-ocifs \ > -o url=https://ixxxxxxxxxx.compat.objectstorage.us-ashburn-1.oraclecloud.com/ \ > -onomultipart -o use_path_request_style
The mount point to Object Storage Bucket is ready and we will be able to stream the binary logs to
A dedicated MySQL User
We will now create a dedicated MySQL User that will fetch the binary logs from the MySQL Instance:
We connect to our MDS instance (I use MySQL Shell) and we do:
SQL> CREATE USER 'binlog_to_object_storage'@'10.0.0.%' IDENTIFIED BY 'C0mpl1c4t3d!Passw0rd' REQUIRE SSL; SQL> GRANT REPLICATION SLAVE ON *.* TO 'binlog_to_object_storage'@'10.0.0.%';
I used a range ip for the host part of the credential account but you could have used the private IP of the compute instance.
Don’t mess up with credentials
To connect to MDS with the new credentials, we could specify the user, host and password in the command line… but it’s not always safe and also it can behave strangely when we use special characters and we want to script all that (it’s the case here as I use an exclamation mark, !, in the password).
The best solution that MySQL provides us, is to use
mysql_config_editor that works with all MySQL clients (
mysqlbinlog too of course, we will see in the next chapter why we need it).
So now we need to create a config path using
mysql_config_editor. As my MDS instance is called my-mds, I will use the same name but you can use what suits you the best:
[root@mysql-shell ~]# mysql_config_editor set --login-path=my-mds \ --host=10.0.1.128 --user=binlog_to_object_storage --password Enter password:
10.0.1.128 is the IP of my MDS instance.
We can test the connection using the old
mysql client for example:
[root@mysql-shell ~]# mysql --login-path=my-mds Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 87 Server version: 8.0.26-u1-cloud MySQL Enterprise - Cloud
This is perfect, we can move forward.
As you may know,
mysqlbinlog has the possibility of reading the binary logs from a live server and just store them to disk using the options
--raw --read-from-remote-server. This is what we gonna use.
I’ve created a wrapper slightly inspirited by the one of my old friend Tamas.
We need to save it on the compute instance in
the opc user is not allowed to exec fusermount, we need to use root
The file is here: binlog_to_object_storage.sh (for MDS HA, use this script that includes the hostname in the saved binary log’s file name: binlog_to_object_storage.sh)
We also need a configuration file. As I wrote before, the same sever could be used to stream multiple different MDS instances.
Let’s create the configuration file in
/root/conf and call it
MYSQL_LOGIN_PATH=my-mds OBJECT_STORAGE_MOUNT=/mnt/oci BINLOGS_DIR=my-mds
We only need this three variables what will define the credentials to use and where to store the data.
I also created a systemd service script to start the process, same script can be also used for multiple streaming processes.
So we create a file called
[Unit] Description=Streaming MDS binary logs to Object Storage using %i After=network.target [Service] Type=simple User=root Restart=on-failure ExecStart=/root/bin/binlog_to_object_storage.sh /root/conf/%i.conf [Install] WantedBy=multi-user.target
We just need to reload the systemclt daemon and start our new service:
As you can see, we provide the configuration name (my-mds) to the service:
And we can see in Object Storage that the binary logs are now stored in it even after being purged in the MySQL Database Service instance:
It’s very easy to stream the binary logs of a MySQL Database Service instance to Object Storage in OCI. MySQL provides all the needed tools.
In the second part of the series, we will see how we can use those binary logs to perform Point-in-Time Recovery.
Enjoy MySQL and MySQL Database Service !
[…] part 1 of the series about Point-in-Time Recovery in OCI MDS, we saw how to stream the binary log to […]