How to do an in-place migration to Tungsten

Posted in: MySQL, Open Source, Technical Track

Overview

On this post I will explain how to do an in-place migration to Tungsten. We will start with a MySQL topology consisting of 3 nodes (1 master, 2 slaves) using native MySQL replication, and convert that into a 3 node Tungsten cluster. I will use a Red Hat based distro but the steps should be similar if you are using Ubuntu.

Migration steps

1. First of all, confirm MySQL replication is correctly running on all nodes:

show slave status\G

2. Check the Tungsten prerequisites are met:

a. Check OS is supported

cat /etc/redhat-release

b. Check db version is supported

mysql --version

c. Check RAM requirements are met

free -m

d. Check disk requirements

df -h

Next, we will prepare the user environment

e. Configure file and process limits by adding the following lines to /etc/security/limits.conf

tungsten - nofile 65535
mysql - nofile 65535
tungsten - nproc 8096
mysql - nproc 8096

These changes to limits.conf require a MySQL restart to take effect for mysqld process.

f. Set swappiness to a “good” value

echo 5 > /proc/sys/vm/swappiness
vi /etc/sysctl.conf
vm.swappiness=5

g. If you are using iptables, add required network ports by running the following commands:

## THL replication
iptables -I INPUT 4 -p tcp -m tcp --dport 2112 -j ACCEPT
## Manager RMI
iptables -I INPUT 4 -p tcp -m tcp --dport 7800:7805 -j ACCEPT
iptables -I INPUT 4 -p tcp -m tcp --dport 9997 -j ACCEPT
## Replication connection listener port
iptables -I INPUT 4 -p tcp -m tcp --dport 10000:10001 -j ACCEPT
## Tungsten manager
iptables -I INPUT 4 -p tcp -m tcp --dport 11999:12000 -j ACCEPT
## Tungsten connector
iptables -I INPUT 4 -p tcp -m tcp --dport 9999 -j ACCEPT
## Verify rules
iptables -vnL

h. Install JDK and Ruby packages. Verify that the installed versions are supported.

yum install java-1.7.0-openjdk ruby
java -version
ruby --version

i. Add sudo configuration for tungsten:

vi /etc/sudoers.d/tungsten
Defaults !requiretty
tungsten ALL=(root) NOPASSWD: ALL

Verify that you can become root as tungsten user

sudo su -

3. Prepare MySQL configuration

vi /etc/my.cnf
sync_binlog=1
max_allowed_packet = 52M

Create MySQL users. Make sure they are propagated to all nodes.

-- tungsten replicator user
CREATE USER tungsten@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO tungsten@'%' WITH GRANT OPTION;
-- tungsten connector/application user
CREATE USER app_user@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO app_user@'%';
REVOKE SUPER ON *.* FROM app_user@'%';

4. Install the Tungsten package and verify install files are in place

rpm -ivh /tmp/tungsten.rpm
ls /opt/continuent/software

Installing the Tungsten rpm will copy install files to /opt/continuent/software and create the tungsten user.
It will only run the installation process if either /etc/tungsten.ini or /etc/tungsten/tungsten.ini files are present (which is not the case here yet).

5. Each node needs to be able to connect to each other via password-less ssh, so generate a key as tungsten user on one node and copy it to the other nodes:

## on one node only
su - tungsten
ssh-keygen
cat .ssh/id_rsa.pub > .ssh/authorized_keys
chmod 600 .ssh/authorized_keys

Propagate the keys to the other nodes:

scp -pr .ssh root@mysql2:/opt/continuent/
scp -pr .ssh root@mysql3:/opt/continuent/

Adjust perms on the destination nodes as root

chown -R tungsten:tungsten /opt/continuent/.ssh

Tungsten supports two different installation methods: staging host, where you push the config changes and files from a central location, and .ini files, where each host is installed/configured independently. We will use the .ini files method.

6. Create tungsten.ini file on all nodes.

As we are migrating from a native MySQL installation we need to specify start-and-report=false.
The connector is usually installed on each app server, but for simplicity I am installing on each db node here. You can use any available port, I am using 9999 on the example.

vi /etc/tungsten/tungsten.ini
[defaults]
application-user=app_user
application-password=password
application-port=9999
replication-user=tungsten
replication-password=password
replication-port=3306
home-directory=/opt/continuent/
user=tungsten
start-and-report=false
# this is needed only for the migration
skip-validation-check=MySQLNoMySQLReplicationCheck
[testcluster]
master=mysql1
members=mysql1,mysql2,mysql3
connectors=mysql1,mysql2,mysql3

7. Run tpm install on each node (no particular order)

Note that all tpm or trepctl commands required running as tungsten user:

su - tungsten
/opt/continuent/software/continuent-tungsten-4.0.0-2667425/tools/tpm install

Exit your session and login again so it picks up the new environment variables for tungsten user

At this point the cluster is installed but not functional.

We need to have each Tungsten replicator use the correct binlog coordinates and replace MySQL replication with Tungsten.

Note that the procedure listed on the official docs doesn’t work properly

8. Confirm that native replication is working on all slave nodes.

As tungsten user, tpm mysql command will open a MySQL CLI connection to the local MySQL server using the user and pass you specified under replication-user, replication-password and replication-port on tungsten.ini

-- slave 1
echo 'SHOW SLAVE STATUS\G' | tpm mysql | \
egrep 'Master_Host|Last_Error|Relay_Master_Log_File|Exec_Master_Log_Pos|Slave_SQL_Running|Slave_IO_Running'
Master_Host: 192.168.57.101
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error:
Exec_Master_Log_Pos: 1122
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
-- slave 2
echo 'SHOW SLAVE STATUS\G' | tpm mysql | \
egrep 'Master_Host|Last_Error|Relay_Master_Log_File|Exec_Master_Log_Pos|Slave_SQL_Running|Slave_IO_Running'
Master_Host: 192.168.57.101
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error:
Exec_Master_Log_Pos: 1123
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

9. On the master only, start all Tungsten processes and put Replicator online.
Set the cluster policy to maintenance to prevent any auto reconfiguration of services. Remember to use tungsten user to run any Tungsten commands.

startall
trepctl online
echo 'set policy maintenance' | cctrl

On the master, check the status (state should be ONLINE and pendingError=none). Also inspect THL entries to verify that transactions are being correctly recorded.

trepctl status

Tungsten Replicator will start reading the MySQL binary log from the current position, creating the corresponding THL event data entries, which you can verify with

thl list

10. On each slave, native replication SQL thread will have stopped with an error.

Manually stop the replication IO thread, and check the position relative to the master where the SQL thread has stopped. It should be the same position on every slave.

echo "STOP SLAVE;" | tpm mysql
echo 'SHOW SLAVE STATUS\G' | tpm mysql | \
egrep ' Master_Host| Last_Error| Relay_Master_Log_File| Exec_Master_Log_Pos'
Master_Host: 192.168.57.101
Relay_Master_Log_File: mysql-bin.000007
Last_Error: Error 'Table 'tungsten_testcluster.heartbeat' doesn't exist' on query...
Exec_Master_Log_Pos: 120

11. On each slave, start Tungsten replicator.

It will create the tungsten metadata db and tables, and automatically start from the correct position. Verify it is working properly with the status command.

replicator start
trepctl online
trepctl status

12. Start the remaining processes on each host (Manager and Connector), and verify the status is ok with cctrl.

Note that the manager can take a couple of seconds to start.

startall
echo ls | cctrl

13. Remove native MySQL replication configuration from all slaves

echo "RESET SLAVE ALL;" | tpm mysql

14. Test you can connect to the database using Tungsten connector

mysql -uapp_user -ppassword -happserver1 -P9999

15. At this point you would modify all application servers to start using the Tungsten Connector. Once that is complete, you may set the policy to automatic so auto-failover is enabled. The policy setting is global so only execute one time (from any node)

echo 'set policy automatic' | cctrl
email
Want to talk with an expert? Schedule a call with our team to get the conversation started.

Lead Database Consultant

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *