Mysql change master ip address

When we expect higher throughput it will typically increase the read operations and the write operations of the database. So we need a way to replicate the data from the database by adding multiple servers (slaves) that allows the application servers to read from them instead of the master server. The master will handle only the write operations.

This way of setting up a Master Slave replication is very useful since it will make multiple copies of your data, provide redundancy in case one of the slaves will fail it can improve availability, scalability since we can just add more slaves when we get a higher throughput and get an overall performance. This practice by doing synchronization on multiple databases is called replication.

This tutorial expects that you already have some basic knowledge about relational database management systems and working with GNU/Linux OS.

So what we need to do now first is to install the first master MySQL server. I will install this on Ubuntu 21.10. We will also use VirtualBox on our local machine, we can download a prepared Ubuntu 21.10 image that we can just import into VirtualBox and we don’t have to install the OS by ourselfs.

We could also of course use a cloud provider to setup the servers that also have prepared images ready to use. But whatever will work for this tutorial.

Installing VirtualBox and downloading Ubuntu image

So let’s start downloading the VirtualBox here: https://www.virtualbox.org/wiki/Linux_Downloads and choose Ubuntu 19.10 / 20.04 / 20.10 / 21.04. Then open the software in Ubuntu Software and press Install.

Now we download the Ubuntu image here: https://www.osboxes.org/ubuntu/ and choose Ubuntu 21.10 Impish Indri. The file will be donwloaded with 7zip so we need to extract it. We can find the login information that we will need later by pressing the Info button.

Open up VirtualBox and press the button New

Now a popup will appear where you will enter in the name of the VM, directory to place the data, type of OS and the version.

Fill in the information above expect the Machine Folder and press Next:

Now it will ask for what memory size should be allocated. Use the default settings. If you find

Here we will press “Use an existing virtual hard disk file” and press on the directory icon.

This popup will appear and we press Add.

Locate your .vdi file where you exported the 7zip file and press Open. Now continue to save the VM.

Before we start we need to set the network to bridged adapter by going in to Settings so we can get a unique IP for the VMs:

Now we have a VM that we can start so press the Start button and Ubuntu will load.

If you face the issue with the blinking cursor or trouble starting it at all. You might want to increase either the RAM for the VM or the graphic memory. Also check if your CPU supports hardware virtualization. This can be done with the command in GNU/Linux with:

egrep -c ‘(vmx|svm)’ /proc/cpuinfo

If you get any value over 0 your hardware supports it.

Now login by using the credentials

Now we can disable the GUI if we want by opening up the Terminal and enter:

sudo systemctl set-default multi-user

Now reboot the system and we shall see the CLI only:

Setup the MySQL master VM

So now we login with the credentials and we start installing all the necessary packages and dependencies.

sudo apt-get update
sudo apt-get install mysql-server mysql-client -y

Now we need to obtain the IP address of the VM by using the command

ip addr show

Copy the value of inet on the second ethernet in this case it should be 192.168.32.10.

Setup the MySQL slave VM

So do as the instructions before by creating a new VM with the same image we downloaded. But it could be a problem to attach the .vdi file again since it will have the same UUID. So we need to change that first by going to command line and enter:

VBoxManage internalcommands sethduuid <filename>.vdi

Then continue the VM setup and then we install the packages again.

sudo apt-get update
sudo apt-get install mysql-server mysql-client -y

Lets get the IP address of this VM by getting it the same way as before and save it.

MySQL master configuration

Open up the configuration with VIM or Nano:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Find the line that contains the bind-address and replace the value with the IP address of the MySQL master which we got before. Next we will find these lines in the file and remove the comments:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

To avoid issue I had for a long time it was to remove this line as well:

mysqlx-bind-address = 127.0.0.1

Now lets restart the MySQL service

sudo service mysql restart

Confirm if the MySQL is running with:

systemctl status mysql

If we made an error in our configuration or the IP is not correct we will see that it is in a failed state here.

We will do some additional configuration to this server at a later stage. We first have to setup the MySQL slave VM.

We will now create a user that should be used for the slaves so they can login to the MySQL master. Enter these following commands below.

sudo mysql -uroot
mysql> CREATE USER ‘slave’@’SLAVE_IP_ADDRESS‘ IDENTIFIED BY ‘SLAVE_PASSWORD‘;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’SLAVE_IP_ADDRESS‘;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;

We can verify the status by using

mysql> SHOW MASTER STATUS;

Now we need to move the data we have to the slave. So exit out of the MySQL CLI. We do a MySQL dump and save it to a .sql file.

sudo mysqldump -–all-databases > data.sql

MySQL slave configuration

We open up the slave MySQL configuration and set the bind-address to the IP here and also comment out the lines as before but remember to set the server-id to 2 instead of 1. Then we restart the MySQL service.

mysql> CHANGE MASTER TO
     MASTER_HOST='<master-ip>' ,
     MASTER_USER='slave' ,
     MASTER_PASSWORD='Test1234!!#' ,
     MASTER_LOG_FILE='mysql-bin.000001' ,
     MASTER_LOG_POS=1232;

Replace the MASTER_HOST with your master IP address and MASTER_LOG_FILE with the file name you get when running SHOW MASTER STATUS; on the MySQL master and get the position and set that on MASTER_LOG_POS.

Setup public/private SSH keys for transfering

Lets transfer the data.sql file to the slave server from the master. But we need to generate a public/private key so we can transfer it with SCP. So create a key from the master by running:

ssh-keygen

Skip different name or passphrase. Then open up the public key:

cat ~/.ssh/id_rsa.pub

Copy the content and then go to the MySQL slave server. Here we need to install openssh-server.

sudo apt-get install openssh-server

Then open up the SSH config:

sudo nano /etc/ssh/sshd_config

Modify PermitRootLogin and set it to Yes. Save the file. Then create the following file:

touch ~/.ssh/authorized_keys

Then open it up and paste the public key we generated earlier. Run the following command now to restart the SSH service.

sudo systemctl restart ssh

Now we should be able to communicate to the slave server. We can test that by:

ssh -v <ip-of-slave> /bin/true

If we get an exit status of 0 it should be fine. Now we will actually transfer the dump file we generated:

scp data.sql osboxes@<ip-of-slave>:/home/osboxes

Now we should be able to see the file, and we do!

Import MySQL Dump File to MySQL slave

Now lets import the MySQL dump file:

sudo mysql -uroot < data.sql

Now we should have the same data in our slave. We can confirm that by going into the MySQL CLI.

So we have the same data. Good!

Lets go and add a new database to our master to check out if our replication works.

Alright so we have five databases currently. Let’s check if everything is replicated to our MySQL slave.

If you encounter problems during setup you could go through the Replication Checklist here: https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-problems.html

That’s it for this tutorial. There are many settings we should add for production, but I won’t be covering this here since it would be quite extensive, maybe in a future tutorial. Send me a comment if you would like to learn more about it.

What is Master_auto_position in MySQL?

What does reset master do?

What is Master_Log_File?

What is master master replication MySQL?