MySQL Utilities: copy, replicate, show, failover… over and over again.

So, after installing Workbench 6.0.7 on my pc, and playing around with the MySQL Utilities that are included, I thought I’d do similar to what others have done (Thanks Tony D.) and share my experience on how I’ve used them. If you haven’t installed Workbench before, you might want to check your platform first: http://www.mysql.com/support/supportedplatforms/workbench.html.

So, even if you’re not using any of the recent versions and editions of Workbench (Utilities comes with all of them, Tools menu -> “Start Shell for MySQL Utilities“) you can just download the standalone scripts on a Linux machine. Flexibility being the name of the game again.

Connector/Python install

I’ll install python (& distutils) & the Connector/Python on Oracle Linux and execute it all from the linux slave I want to create.

On Oracle Linux 6.3:

1. Download Connector/python & Install.

 cd /usr/local
 tar zxvf mysql-connector-python-1.0.12.tar.gz
 cd mysql-connector-python-1.0.12

– check python is ok:

 python
 >>> from distutils.sysconfig import get_python_lib
 >>> print get_python_lib()
 /usr/lib/python2.6/site-packages
 >>> print (get_python_lib())
 /usr/lib/python2.6/site-packages
 >>> quit()
 python setup.py install

2. Install the Utilities

 cd /usr/local
 tar zxvf mysql-utilities-1.3.5_Linux.tar.gz
 cd mysql-utilities-1.3.5
 python ./setup.py build
 python ./setup.py install
 which mysqldbexport
 /usr/bin/mysqldbexport

Setup Replication

On master node:

 cd /usr/local
 tar zxvf mysql-5.6.14-linux-glibc2.5-i686.tar.gz
 ln -s /usr/local/mysql-5.6.14-linux-glibc2.5-i686 mysql
 cd mysql
 chown -R mysql:mysql .
 cp my56.cnf /etc/my.cnf
 vi /etc/my.cnf
 - SET "server-id = 21"
 - make sure basedir & datadir are ok.
 - make sure hostname is correct.
 - make sure log  are ok.
 mkdir -p /opt/mysql/5.6.14/data
 cd /opt/mysql/5.6.14
 chown -R mysql:mysql .
 cd /usr/local/mysql
 scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/5.6.14/data --basedir=/usr/local/mysql
 chown -R root .
 chown -R mysql data
 bin/mysqld_safe --user=mysql --datadir=/opt/mysql/5.6.14/data &
 /usr/local/mysql/bin/mysqladmin -u root password 'oracle'
 mysql -uroot -poracle
 create database nexus;
 use nexus
 source cre_tab_replicant.sql;
 source insert_nexus.sql;
 grant replication slave on *.* to 'replicant'@'192.168.56.106' identified by 'pkdick';
 grant all on *.* to 'root'@'ol63uek03' identified by 'oracle';
 grant all on *.* to 'root'@'ol63uek02' identified by 'oracle' with grant option;

On slave node:

 cd /usr/local
 tar zxvf mysql-5.6.14-linux-glibc2.5-i686.tar.gz
 ln -s /usr/local/mysql-5.6.14-linux-glibc2.5-i686 mysql
 cd mysql
 chown -R mysql:mysql .
 cp my56.cnf /etc/my.cnf
 vi /etc/my.cnf
 - SET "server-id = 31"
 - make sure basedir & datadir are ok.
 - make sure hostname is correct.
 . make sure log  are ok.
 mkdir -p /opt/mysql/5.6.14/data
 cd /opt/mysql/5.6.14
 chown -R mysql:mysql .
 cd /usr/local/mysql
 scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/5.6.14/data --basedir=/usr/local/mysql
 chown -R root .
 chown -R mysql data
 bin/mysqld_safe --user=mysql --datadir=/opt/mysql/5.6.14/data &
 /usr/local/mysql/bin/mysqladmin -u root password 'oracle'
 grant replication slave on *.* to 'replicant'@'ol63uek02' identified by 'pkdick';
 grant all on *.* to 'root'@'ol63uek02' identified by 'oracle' with grant option;

Time to copy the data across:
On Master:

mysqldbcopy    --source=root:oracle@ol63uek02:3356 --destination=root:oracle@ol63uek03:3356 nexus:nexus

Let’s get replication up and running:

mysqlreplicate --master=root:oracle@ol63uek02:3356 --slave=root:oracle@ol63uek03:3356 --rpl-user=replicant:pkdick -vvv
mysqlrplshow --master=root:oracle@ol63uek02:3356 -v -l -r --discover-slaves-login=root:oracle
insert into replicant (`First Name`,`Last Name`,`Replicant`) values ('Ridley', 'Tyrell','No'), ('Eldon','Scott', 'No');

– check slave, from a mysql master connection:

mysql> show slave hosts;
mysqlrpladmin health  --master=root:oracle@ol63uek02:3356 --discover-slaves-login=root:oracle

Ok. It’s working.
Now this is a simple sample environment and not production nor has it been existing for any long period of time. In any ‘normal’ environment, you’ll have purged binlogs and backed up plenty of times since t=0, install & creation time. What does this mean? When you try and set it up with master-auto-position=1, you might get:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

So we have some alternatives here:
– Use old log_file & log_position. And then activate GTID_MODE after replication has been setup (or not use GTID, but that’s not an option I want to consider).
– Run “reset master” on the slave and then import the mysqldump file obtained from running with –set-gtid-purged=ON. The resulting dump file contains the “set @@GLOBAL.GTID_PURGED=” line that will allow us to align master and slave.
– Take a more ‘hands-on’ approach, and that’s to manually enter the “set global.GTID_PURGED” on the slave, taking the masters “gtid_executed” value, from “show global variables like ‘gtid_executed';”

Time to think about Failing over:

mysqlfailover --master=root:oracle@ol63uek02:3356 --log=utilfailover.log  --discover-slaves-login=root:oracle --force

Kill -9 mysqld process
Watch slave become master.

To go backwards, to initial situation:

On ol63uek03:

mysql> reset master;

On ol63uek02:

mysqlreplicate --master=root:oracle@ol63uek02:3356 --slave=root:oracle@ol63uek03:3356 --rpl-user=replicant:pkdick -vvv

About these ads

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: