Utilities in MySQL Workbench
If you’re a user of MySQL Workbench then you may have noticed a pocket knife icon appear in the top right hand corner – click on that and a terminal opens which gives you access to the MySQL utilities. In this post I’m focussing on the replication utilities but you can also refer to the full MySQL Utilities documentation.
What I’ll step through is how to uses these utilities to:
- Set up replication from a single master to multiple slaves
- Automatically detect the failure of the master and promote one of the slaves to be the new master
- Introduce the old master back into the topology as a new slave and then promote it to be the master again
Tutorial Video
Before going through the steps in detail here’s a demonstration of the replication utilities in action…
To get full use of these utilities you should use the InnoDB storage engine together with the Global Transaction ID functionality from the latest MySQL 5.6 DMR.
Do you really need/want auto-failover?
For many people, the instinctive reaction is to deploy a fully automated system that detects when the master database fails and then fails over (promotes a slave to be the new master) without human intervention. For many applications this may be the correct approach.
There are inherent risks to this though – What if the failover implementation has a flaw and fails (after all, we probably don’t test this out in the production system very often)? What if the slave isn’t able to cope with the workload and makes things worse? Is it just a transitory glitch and would the best approach have been just to wait it out?
Following a recent, high profile outage there has been a great deal of debate on the topic between those that recommend auto-failover and those that believe it should only ever be entrusted to a knowledgeable (of the application and the database architecture) and well informed (of the state of the database nodes, application load etc.) human. Of course, if the triggering of the failover is to be left to a human then you want that person to have access to the information they need and an extremely simple procedure (ideally a single command) to execute the failover. Probably the truth is that it all depends on your specific circumstances.
The MySQL replication utilities aim to support you whichever camp you belong to:
- In the fully automated mode, the utilities will continually monitor the state of the master and in the event of its failure identify the best slave to promote – by default it will select the one that is most up-to-date and then apply any changes that are available on other slaves but not on this one before promoting it to be the new master. The user can override this behaviour (for example by limiting which of the slaves are eligible for promotion). The user is also able to bind in their own programs to be run before and after the failover (for example, to inform the application).
- In the monitoring mode, the utility still continually checks the availability of the master, and informs the user if it should fail. The user then executes a single command to fail over to their preferred slave.
Step 1. Make sure MySQL Servers are configured correctly
For some of the utilities, it’s important that you’re using Global Transaction IDs; binary logging needs to be enabled; may as well use the new crash-safe slave functionality… It’s beyond the scope of this post to go through all of those and so instead I’ll just give example configuration files for the 5 MySQL Servers that will be used:
my1.cnf
[mysqld] binlog-format=ROW log-slave-updates=true gtid-mode=on disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+ master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 datadir=/home/billy/mysql/data1 server-id=1 log-bin=util11-bin.log report-host=utils1 report-port=3306 socket=/home/billy/mysql/sock1 port=3306
my2.cnf
[mysqld] binlog-format=ROW log-slave-updates=true gtid-mode=on disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+ master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 datadir=/home/billy/mysql/data2 server-id=2 log-bin=util12-bin.log report-host=utils1 report-port=3307 socket=/home/billy/mysql/sock2 port=3307
my3.cnf
[mysqld] binlog-format=ROW log-slave-updates=true gtid-mode=on disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+ master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 datadir=/home/billy/mysql/data3 server-id=3 log-bin=util2-bin.log report-host=utils2 report-port=3306 socket=/home/billy/mysql/sock3 port=3306
my4.cnf
[mysqld] binlog-format=ROW log-slave-updates=true gtid-mode=on disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+ master-info-repository=TABLE relay-log-info-repository=TABLE master-info-file=/home/billy/mysql/master4.info datadir=/home/billy/mysql/data4 server-id=4 log-bin=util4-bin.log report-host=utils2 report-port=3307 socket=/home/billy/mysql/sock4 port=3307
my5.cnf
[mysqld] binlog-format=ROW log-slave-updates=true gtid-mode=on disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+ datadir=/home/billy/mysql/data5 master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 #master-info-file=/home/billy/mysql/master5.info server-id=5 log-bin=util5-bin.log report-host=utils2 report-port=3308 socket=/home/billy/mysql/sock5 port=3308
The utilities are actually going to be run from a remote host and so it will be necessary for that host to access each of the MySQL Servers and so a user has to be granted remote access (note that the utilities will automatically create the replication user):
[billy@utils1 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;" [billy@utils1 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "grant all on *.* to root@'%' with grant option;" [billy@utils2 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;" [billy@utils2 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "grant all on *.* to root@'%' with grant option;" [billy@utils2 ~]$ mysql -h 127.0.0.1 -P3308 -u root -e "grant all on *.* to root@'%' with grant option;"
OK – that’s the most painful part of the whole process out of the way!
Set up replication
While there are extra options (such as specifying what username/password to use for the replication user or providing a password for the root user) I’m going to keep things simple and use the defaults as much as possible. The following commands are run from the MySQL Utilities terminal – just click on the pocket-knife icon in MySQL Workbench.
mysqlreplicate --master=root@utils1:3306 --slave=root@utils1:3307 # master on utils1: ... connected. # slave on utils1: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3306 # master on utils1: ... connected. # slave on utils2: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3307 # master on utils1: ... connected. # slave on utils2: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3308 # master on utils1: ... connected. # slave on utils2: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done.
That’s it, replication has now been set up from one master to four slaves.
You can now check that the replication topology matches what you intended:
mysqlrplshow --master=root@utils1 --discover-slaves-login=root; # master on utils1: ... connected. # Finding slaves for master: utils1:3306 # Replication Topology Graph utils1:3306 (MASTER) | +--- utils1:3307 - (SLAVE) | +--- utils2:3306 - (SLAVE) | +--- utils2:3307 - (SLAVE) | +--- utils2:3308 - (SLAVE)
Additionally, you can also check that any of the replication relationships is correctly configure:
mysqlrplcheck --master=root@utils1 --slave=root@utils2 # master on utils1: ... connected. # slave on utils2: ... connected. Test Description Status --------------------------------------------------------------------------- Checking for binary logging on master [pass] Are there binlog exceptions? [pass] Replication user exists? [pass] Checking server_id values [pass] Is slave connected to master? [pass] Check master information file [pass] Checking InnoDB compatibility [pass] Checking storage engines compatibility [pass] Checking lower_case_table_names settings [pass] Checking slave delay (seconds behind master) [pass] # ...done.
Including the -s option would have included the output that you’d expect to see from SHOW SLAVE STATUSG on the slave.
Automated monitoring and failover
The previous section showed how you can save some serious time (and opportunity for user-error) when setting up MySQL replication. We now look at using the utilities to automatically monitor the state of the master and then automatically promote a new master from the pool of slaves. For simplicity I’ll stick with default values wherever possible but note that there are a number of extra options available to you such as:
- Constraining which slaves are eligible for promotion to master; the default is to take the most up-to-date slave
- Binding in your own scripts to be run before or after the failover (e.g. inform your application to switch master?)
- Have the utility monitor the state of the servers but don’t automatically initiate failover
Here is how to set it up:
mysqlfailover --master=root@utils1:3306 --discover-slaves-login=root --rediscover MySQL Replication Failover Utility Failover Mode = auto Next Interval = Wed Aug 15 13:19:30 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB util11-bin.000001 2586 Replication Health Status +---------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +---------+-------+---------+--------+------------+---------+ | utils1 | 3306 | MASTER | UP | ON | OK | | utils1 | 3307 | SLAVE | UP | ON | OK | | utils2 | 3306 | SLAVE | UP | ON | OK | | utils2 | 3307 | SLAVE | UP | ON | OK | | utils2 | 3308 | SLAVE | UP | ON | OK | +---------+-------+---------+--------+------------+---------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs
mysqlfailover will then continue to run, refreshing the state – just waiting for something to go wrong.
Rather than waiting, I kill the master MySQL Server:
mysqladmin -h utils1 -P3306 -u root shutdown
Checking with the still-running mysqlfailover we can see that it has promoted utils1:3307.
MySQL Replication Failover Utility Failover Mode = auto Next Interval = Wed Aug 15 13:21:13 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB util12-bin.000001 7131 Replication Health Status +---------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +---------+-------+---------+--------+------------+---------+ | utils1 | 3307 | MASTER | UP | ON | OK | | utils2 | 3306 | SLAVE | UP | ON | OK | | utils2 | 3307 | SLAVE | UP | ON | OK | | utils2 | 3308 | SLAVE | UP | ON | OK | +---------+-------+---------+--------+------------+---------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs
Add the recovered MySQL Server back into the topology
After restarting the failed MySQL Server, it can be added back into the mix as a slave to the new master:
mysqlreplicate --master=root@utils1:3307 --slave=root@utils1:3306 # master on utils1: ... connected. # slave on utils1: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done.
The output from mysqlfailover (still running) confirms the addition:
MySQL Replication Failover Utility Failover Mode = auto Next Interval = Wed Aug 15 13:24:38 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB util12-bin.000001 7131 Replication Health Status +---------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +---------+-------+---------+--------+------------+---------+ | utils1 | 3307 | MASTER | UP | ON | OK | | utils1 | 3306 | SLAVE | UP | ON | OK | | utils2 | 3306 | SLAVE | UP | ON | OK | | utils2 | 3307 | SLAVE | UP | ON | OK | | utils2 | 3308 | SLAVE | UP | ON | OK | +---------+-------+---------+--------+------------+---------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs
If it were important that the recovered MySQL Server be restored as the master then it is simple to manually trigger the promotion (after quitting out of mysqlfailover):
mysqlrpladmin --master=root@utils1:3307 --new-master=root@utils1:3306 --demote-master --discover-slaves-login=root switchover # Discovering slaves for master at utils1:3307 # Checking privileges. # Performing switchover from master at utils1:3307 to slave at utils1:3306. # Checking candidate slave prerequisites. # Waiting for slaves to catch up to old master. # Stopping slaves. # Performing STOP on all slaves. # Demoting old master to be a slave to the new master. # Switching slaves to new master. # Starting all slaves. # Performing START on all slaves. # Checking slaves for errors. # Switchover complete. # # Replication Topology Health: +---------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +---------+-------+---------+--------+------------+---------+ | utils1 | 3306 | MASTER | UP | ON | OK | | utils1 | 3307 | SLAVE | UP | ON | OK | | utils2 | 3306 | SLAVE | UP | ON | OK | | utils2 | 3307 | SLAVE | UP | ON | OK | | utils2 | 3308 | SLAVE | UP | ON | OK | +---------+-------+---------+--------+------------+---------+ # ...done.
As always, we’d really appreciate people trying this out and giving us feedback!
You are probably not unaware (at least you should not be!) that on planet.mysql.com there have been quite a lot of discussion about automatic failover recently (triggered by recent failure at GitHub).
You don’t relate to this at all and I think you should. Reading this post leaves the impression that automativ failover is unproblematic.
I think you should address/comment the problems raised by others (what of course are – more or less -independent of whether WB or other methods/tools are used for setting up the failover system).
Hi Peter,
take a closer look at the section called “Do you really need/want auto-failover?”
Andrew.
oops .. I totally missed this. I am trying to find my mousehole!
Where to get MySQL Utilities from if one does not want to install MySQL Workbench but only the command line utils?
Hi Ulf,
I think you already know the answer to this now but you can either get the source from Launchpad or wait until we release it in a package independently of WorkBench.
Andrew.
[…] want to evaluate the new mysqlfailover utility designed for MySQL 5.6 and GTIDs. There’s a nice demo. The mysqlfailover utility offers hooks like MHA but does not pass any information on the cluster […]
Hi All,
I am seeing some issues while setting up MySQL Master Master replication and I would appreciate any help in resolving this problem.
I Installed Server vsersion:5.5.29-ndb-7.2.10-cluster-gpl-log MySQL Cluster Community server on two separate windows 2003 machines.installed my database on both machines.Setup replication user.In mysql command line utility on mySLQ server1,ran the following commands:
change master to master_host=’IP1′,master_user=’user1′,master_password=’PW1′;
start slave;
On the second mysql server2, ran the commands:
change master to master_host=’IP2′,master_user=’user1′,master_password=’PW1′;
start slave;
However, when I run the following command on machine 1, I see:
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.71
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: MySQLStreamA-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: activevos.AeURNValues,activevos.AeCounter,activev
os.AeLock,activevos.AeMetaInfo
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 112
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because m
aster and slave have equal MySQL server ids; these ids must be different for rep
lication to work (or the –replicate-same-server-id option must be used on slave
but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_Bind:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
Similar error is seen in machine2 also.
I double checked that each of the mysql servers had unique server IDs.And I varified it using ‘SHOW VARIABLES’ command as well as from the .ini file. Also here are the replication settings in .ini file:
On machine1:
# If you’re using replication with chained slaves (A->B->C), you need to
# enable this option on server B. It enables logging of updates done by
# the slave thread into the slave’s binary log.
log_slave_updates
# *** Replication related settings
# Unique server identification number between 1 and 2^32-1. This value
# is required for both master and slave hosts. It defaults to 1 if
# “master-host” is not set, but will MySQL will not function as a master
# if it is omitted.
server-id = 3
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 3
sync_binlog=1
log-bin = C:mysqlloglog-bin.log # change this to a path/name appropriate to your system
relay-log=MySQLStreamA-relay-bin
And on machine2:
# If you’re using replication with chained slaves (A->B->C), you need to
# enable this option on server B. It enables logging of updates done by
# the slave thread into the slave’s binary log.
log_slave_updates
# *** Replication related settings
# Unique server identification number between 1 and 2^32-1. This value
# is required for both master and slave hosts. It defaults to 1 if
# “master-host” is not set, but will MySQL will not function as a master
# if it is omitted.
server-id = 4
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 4
sync_binlog=1
log-bin = C:mysqlloglog-bin.log # change this to a path/name appropriate to your system
relay-log=MySQLStreamB-relay-bin
It is not clear to me why I am seeing this error.
Any suggestions on how I can resolve this problem?
Thanks.
Hi,
Great works, this tutorial helped me a lot to make my database architecture, but after replication phase, when I can’t see Graph topology inspite mysqlrplcheck works very well and SHOW SLAVE HOSTS command too, but I can’t see the topology using mysqlrplshow,
please help !
thanks
Othman,
what exactly are you typing and seeing? What should the topology look like?
Regards, Andrew.
Hi, I’m relatively new at mysql, and I need to set up a topology like this, only a master and one slave, a have a few question about it, and would appreciate if you answer them, this replication mode means that any changes made in the master’s database will be made in the slaves’s? also, when the master fails, automatically a slave starts serving, but when the master comes up again, Does mysql automatically detects it?
Esteban,
Yes – data will be replicated from the master to the slaves.
If the original master is recovered then you manually use the utilities to add the master back in as a slave; once it has caught up with any changes it has missed then you can again use the utilities to promote it back into being the master.
Andrew.
hello,i do this test in root user!
my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
server-id=1
log-bin=util11-bin.log
report-host=utils1
report-port=3306
port=3306
[root@utils1 ~]# rpm -qa | grep mysql
php-mysql-5.1.6-27.el5_5.3
mysql-5.0.77-4.el5_5.4
mysql-server-5.0.77-4.el5_5.4
the server can not start now!
[root@utils1 ~]# /etc/init.d/mysqld restart
停止 MySQL： [失败]
Timeout error occurred trying to start MySQL Daemon.
启动 MySQL： [失败]
what should i do?
thanks!!!
runningday,
it looks like you’re trying to run MySQL 5.0 with the config file setup with MySQL 5.6 features. You should download & install MySQL from mysql.com and then try again. Note that you should also customise the config file to your envuironment – e.g. report-host should be your hostname.
Andrew.
I have a small 1 Mgm Node, 2 Data Nodes, and 2 SQL(API) Nodes setup. I have not setup any SQL node as “slave” explicitly, so my assumption is that both my SQL Nodes are Masters. Is that assumption correct? I can make modifications to either one and the changes are reflected on the other server. However, that is not true during a fail-over scenario.
If I down one of the SQL Nodes while connected to it from my UI interface, and proceed to try to perform modification operations, I get exceptions like this one:
Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
Now, I read that the connection goes read-only during fail over if the autocommit property is set to true. Would I have to change this property value to false, if so, where? on each of the SQL Node’s my.cnf, the Mgmnt config.ini or in my JDBC URL? or is this even possible with MySQL Cluster?
Or should I try handling the exception in my code, refresh the connection to hopefully get the new SQL Node that is running and try to execute the update query? My JDBC URL already has loadbalance, plus the list of SQL Nodes to use, it has the loadBalanceStrategy set to bestResponseTime, has the black list property,
SQL state failover for errors starting with 08, and sub class failover property for java.sql.SQLTransientConnectionException.
Any hints, ideas, and/or samples would be much appreciated.
-Ed
Hi Ed,
you are correct that if part of the Cluster, both MySQL Servers can be treated as masters for any tables created using the NDBCLUSTER storage engine (ENGINE=NDB when creating or altering the table). The data isn’t actually stored or even cached (unless you’re using MySQL Server’s query cache – which in most cases isn’t a good idea with MySQL Cluster) in the MySQL Server – the mysqld just provides an SQL interface to reach through to the data nodes for the data.
In the event that one MySQL Server should fail, there should be no impact on the other and all traffic can be sent to the other. You can just load ballance traffic between your 2 MySQL Servers.
I confess that I’m not an expert on Connector/J but this chapter of the Connector/J manual covers configuring failover for MySQL Cluster and hopefully will give you the information you need… http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-load-balancing-failover.html
If you need more help with Connector/J then you could also try this dedicated Connector/J forum.
Regards, Andrew.
Thank you, Andrew. I noticed that I can add the failOverReadOnly flag to my JDBC URL and set it to false to create a r/w connection instead of read-only.
If you know of any good JDBC wrappers, proxy, or whatever they’re called, which is open source, please let us know. I’m interested in something like HA-JDBC (http://ha-jdbc.github.io/) that could handle fail-over in a seamless way, without me having to change code(Java). I’d like to keep my code DB agnostic if at all possible.
Thanks again for your help and suggestions.
-Ed
hola buenos dias. no soy experto en MySQL, pero me gusta, tengo 3 sucursales(SUC 1, SUC 2, SUC 3) y 1 matriz (MA1), en cada tienda un programa de inventario(POS) en JAVA, y un servidor MySQL en cada sucursal. Mis necesidades son: SI SE REALIZA UNA VENTA EN SUC 1(INSERT), QUE SE MUESTRE EN SUC 2, SUC3 Y MA1, SI LA MERCANCIA LLEGA EN MA1 (INSERT) QUE LAS 3 SUCURSALES SE MUESTRE EL INVENTARIO, TAMBIEN REALIZAR UN TRASPASO (SUC 1->SUC 2->SUC 3->MA1[VICEVERSA CONVINADOS]), PREGUNTA: CON LA REPLICACION RESUELVO MI DUDA?
TRADUCCION EN INGLES (PERDONE SI NO PUEDO TRADUCIRLO (https://translate.google.com.mx))>
hi good morning. I’m no expert on MySQL, but I like, I have 3 branches (SUC 1 SUC 2 SUC 3) and 1 matrix (MA1) in each store an inventory program (POS) in Java, and MySQL server in each branch . My needs are: IF YOU MAKE A SALE IN SUC 1 (INSERT), YOU POST IN SUC 2 MA1 SUC3 AND IF THE MERCHANDISE ARRIVES IN MA1 (INSERT) THAT ALL 3 BRANCHES INVENTORY SHOWN ALSO MAKE A TRANSFER (SUC 1 -> SUC 2 -> SUC 3 -> MA1 [VICEVERSA CONVINADOS]) QUESTION: REPLICATION TO RESOLVE MY QUESTIONS?
TRANSLATION IN ENGLISH (FORGIVE IF I can not translate (https://translate.google.com.mx))>
Hi Rafael,
If I understand you correctly, you want to be able to insert or modify the data in any MySQL server and have the data appear in all of the other MySQL servers. To do that, you could set up a replication ring – where A->B->C-D->A.
The risk with this approach is that (apart from with MySQL Cluster) MySQL doesn’t contain any conflict detection/resolution functionality and so the data could become inconsistent if you modified the data at A and then again at D before the update from A had replicated around the ring.
Regards, Andrew.
Hi Andrew,
Firstly, thanks for the awesome video. I’ve been putting together a tree topology and have everything working until i found your video and decided to use the utilities. The account that I use to connect between the master and the two slaves is password protected. Now when I run the following command from the utilities I get the following error “Master connection values invalid or cannot be parsed”.
mysqlrplshow –master=root:password@localhost:3306 –discover-slaves-login=root:password;
Any ideas?
Thanks in advance
As a general rule with MySQL I avoid specifying localhost as there are restrictions around it – try swapping it for 127.0.0.1
Andrew.
I tried with the IP but no luck. I forgot to mention Im using windows server. Is the command still the same?
Thanks
Pancho,
commands should be the same on any OS.
Have you tried connecting with the mysql client from and to the same servers and with the same connect strings and use credentials?
Andrew.
hi i tried the scenario but at the second step i got this error when i write this instruction:
mysqlrplshow –master=root@utils1;
mysqlrplshow: error: The –discover-slaves-login is required to test slave conne
ctivity.
help plzzzz
Looks like it’s now mandatory to include the –discover-slaves-login=[:] for the utility to connect to the slaves to discover any nested slaves.
Andrew.
Hello Andrew!
I’am testing mysqlfailover and I think it is a good tool for help all us, but I have a question:
Hot can I specify a password in the sintaxis?
Thanks for your help!
Hi Frank – yes, just specify it with the user name in this form:. Documentation for the command can be found here.
Hi Andrew,
I think there is a type in the following two lines:
mysqlrplshow –master=root@utils1;
and
mysqlrplcheck –master=root@utils1 –slave=root@utils2:3307 –discover-slaves-login=root
should be replaced with
mysqlrplshow –master=root@utils1 –discover-slaves-login=root
and
mysqlrplcheck –master=root@utils1 –slave=root@utils2:3307
Hi Max,
you’re correct – the synytax changed since this post was originally written. I’ve now update it.
Thanks, Andrew.
Hi all,
i had configured fail-over in master-slave replication.with the following steps.
1)keeping master(A) read only(set global read_only=1;)
2)keeping slave(B) read only off(set global read_only=0;)
3)stop slave(B) io_thread;
4)wait until it reads all relay log.
5)stop slave(B);
6)reset master(B);
7)show master status(B);note the binlog pos.
8)set old master(A) to be slave(change master to master_host=’—‘,master_user=’—‘,master_password=’—‘,master_log_pos=–,master_log_file=’–‘;)
9)start slave(A);
10)flush tables(A);
A)steps performed in current master
B)steps performed in current slave.
but @ this stage i am facing error:
slave_io_running: connecting
slave_sql_running: yes
Last_IO_Error: error connecting to master
Could some one help me out @ this stage.
Thanks a million in advance.
Harry,
have you checked that the replication user has been created with the correct priviliges on B?
Andrew.
Hi!
Very nice tutorial! It helped me a lot!
But I ran into an issue when trying to make auto-failover work. I execute the mysqlfailover –master=root@utils1:3306 –discover-slaves-login=root –rediscover command but this is the output on the screen:
mysqlfailover –master=root@DEIBTEMSL004:3306 –discover-slaves-login=root –rediscover
# Discovering slaves for master at DEIBTEMSL004:3306
# Checking privileges.
# WARNING: You may be mixing host names and IP addresses. This may result in negative status reporting if your DNS services do not support reverse name lookup.
#
# Failover console will start in 10 seconds.
# Discovering slaves for master at DEIBTEMSL004:3306
2013-09-22 17:05:28 PM CRITICAL Query failed. 1193: Unknown system variable ‘GTID_DONE’
ERROR: Query failed. 1193: Unknown system variable ‘GTID_DONE’
I’m using MySQL v.5.6.14.
What could be the problem? All your help is highly appreciated.
Barni,
the GTID_DONE variable was removed from MySQL 5.6 – what version of MySQL Utilities are you using?
Andrew.
i have counter this problem also,
I’m using utilities coming with mysql workbench 5.2.44, what is the problem with it?
Thanks Andrew! this has really helped me alot. Keep it up.
One question. If I have an application connected to the master database with all the neccesary IP grants, Do I have to manually re-point my application to the promoted slave or there’s a cleaver way I can achieve this.
Thanks alot.
Hi Bright,
it’s the application’s responsibility to switch to the new master. To help automate this, you can bind in your own scripts that are invoked when the failover happens – take a look at the exec-* options from http://dev.mysql.com/doc/workbench/en/mysqlfailover.html
Regards, Andrew.
Hi andrew, mysqlfailover looks great but I’m also struggling to work out how to redirect traffic to the new (potentially random) ip address of the new master if it’s left to mysqlfailover to automatically choose the best slave of N to promote. How do you automatically get the new master IP address? How do you interface mysqlfailover with a proxy or load balancer sitting in front of the cluster to actually start using the newly promoted master ip? I’ve googled it to death but all the blog posts etc all fail to link the back end with any front end. 😐
regan,
There’s nothing in mysqlfailover to handle switching of the application (e.g. no handling of a virtual IP address) but you do have the option of binding in your own scripts at various points – including when the failover is about to start and again once it has completed. Take a look at the
--exec**command-line arguments in the mysqlfailover documentation.Regards, Andrew.
Hi Andrew,
I know it is old thread and hope I will get your attention to get my question addressed as well.
I need to understand the use/configure of Virtual IP during failover.
Here is my scenario:
Master is running on Host A – IP : x.x.x.1
Slave is running on Host B – IP : x.x.x.2
I would like Application connect to Host C – IP x.x.x.3 (Virtual IP) regardless of where Master is running.
How can I get this done? I know you mentioned document link but it is not available. Please help me and send me any supporting document(s).
Much appreciated.
I’d suggest taking a look at MySQL Fabric.
Andrew.
Informed that Oracle MySQL will shelve Fabric. How about using HA Proxy?
I’m afraid that I don’t have any experience with HA Proxy
hi
Thanks for your post. i tried following the steps mentioned on the site.
When i use mysqlreplicate, it gave me this error.
Please help.
[root@localhost1 ~]# mysqlreplicate –master=root:root@127.0.0.1:3306 –slave=root:root@172.31.1.253:3306 ot:root@172.31.1.253:3306
# master on 127.0.0.1: … connected.
# slave on 172.31.1.253: … connected.
# Checking for binary logging on master…
# Setting up replication…
Traceback (most recent call last):
File “/usr/bin/mysqlreplicate”, line 167, in
options, opt.test_db)
File “/usr/lib/python2.6/site-packages/mysql/utilities/command/setup_rpl.py”, line 100, in setup_replication
if not rpl.setup(rpl_user, 10):
File “/usr/lib/python2.6/site-packages/mysql/utilities/common/replication.py”, line 633, in setup
res = self.slave.get_slaves_errors()
File “/usr/lib/python2.6/site-packages/mysql/utilities/common/replication.py”, line 1483, in get_slaves_errors
res = self.get_status()
File “/usr/lib/python2.6/site-packages/mysql/utilities/common/replication.py”, line 1261, in get_status
return self.exec_query(“SHOW SLAVE STATUS”, col_options)
File “/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py”, line 924, in exec_query
cursor_class=mysql.connector.cursor.MySQLCursorBufferedRaw)
File “/usr/lib/python2.6/site-packages/mysql/connector/connection.py”, line 1154, in cursor
raise errors.OperationalError(“MySQL Connection not available.”)
mysql.connector.errors.OperationalError: MySQL Connection not available.
[root@localhost1 ~]#
———————————————————————–
master server my.cnf file
———————————————————————–
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
log-error=’/var/lib/mysql/log_error’
general_log
general_log_file=’/var/lib/mysql/log_general’
slow_query_log
slow_query_log_file=’/var/lib/mysql/log_slow_query’
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
event_scheduler=ON
log_bin = myreplication
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog_format = mixed
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/var/lib/mysql
report-host=localhost1
server_id = 1
report-port=3306
expire_logs_days=5
#read_only = 1
———————————————————————–
slave 1 my.cnf file
———————————————————————–
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
log-error=’/var/lib/mysql/log_error’
general_log
general_log_file=’/var/lib/mysql/log_general’
slow_query_log
slow_query_log_file=’/var/lib/mysql/log_slow_query’
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
event_scheduler=ON
log_bin = myreplication
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog_format = mixed
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/var/lib/mysql
report-host=localhost2
server_id = 2
report-port=3306
expire_logs_days=5
#read_only = 1
———————————————————————–
slave 2 my.cnf file:
———————————————————————–
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
log-error=’/var/lib/mysql/log_error’
general_log
general_log_file=’/var/lib/mysql/log_general’
slow_query_log
slow_query_log_file=’/var/lib/mysql/log_slow_query’
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
event_scheduler=ON
log_bin = myreplication
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog_format = mixed
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/var/lib/mysql
report-host=localhost3
server_id = 3
report-port=3306
expire_logs_days=5
#read_only = 1
Hi Vishal,
there seems to be a typo at the end of your command…
[root@localhost1 ~]# mysqlreplicate –master=root:root@127.0.0.1:3306 –slave=root:root@172.31.1.253:3306 ot:root@172.31.1.253:3306Is this what you were attempting…
mysqlreplicate --master=root:root@127.0.0.1:3306 --slave=root:root@172.31.1.253:3306Andrew.
Hi Andrew
Ya it was a typo.
this is what i tried:
mysqlreplicate –master=root:root@localhost:3306 –slave=root:Itkt123@172.31.1.32:3306 -vvvv
The error was same as mentioned earlier.
Please suggest what could have gone wrong.
Regards
Vishal Goel
9540506073
Vishal,
what version of Utilities and Conector/Python are you using (could be that it’s a bug that’s already been fixed)? Could you also try replacing 127.0.0.1 with localhost in case that makes a difference.
Andrew.
after funning mysqlfailover to setup the automatic failover is there an easy way to set that as a windows service or something or do I have to leave a user logged into a system with that running?
You can use the –daemon option so that you don’t need to stay logged in but it’s your responsibility to register it as a service.
Andrew.
I have created an easy to setup Vagrant env to follow this post experimentally: https://github.com/tsenart/mysql56-replication
Let me know what you think!
hi..
Can any one help me Configuration steps of auto switching/ Fail over by using mysql5.7 in window platforms….
Is it possible to setup auto switching of dbs in win8 as a master and win 7 as a slave…
The steps are not clear to setup Fail over please give the solution for setting up in windows platform instead of unix platform…
please help me…
Thank you..
Hi Rajith,
where do you suspect that the process might be different on Windows?
Andrew.
hi Andrew sir…
Iam actually doing project work on this…
so please tell me the steps to setup replication and fail over mode or auto switch of databases on windows platform…
Please help me …
In advance thank you…
Hi Andrew,
Thanks for the post I appreciate !! I have one simple doubt:
So in the newer versions (5.6.9+)
The line on the my.cnf will be changed FROM
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
TO
enforce-gtid-consistency=true
or just
enforce-gtid-consistency
?
Thaks a lot !!!
Correct – although the bevaviour also changed sightly – the old parameter stopped all changes to non-transactional tables (i.e. those using MyISAM) but with the new one it’s only transactions that mix transactional and non-transactional tables that are blocked.
Andrew.
mysqlreplicate –master=root@192.168.1.4:3306 \ –slave=sandeep@192.168.1.7:3306 –rpl-user=sandeep:system -b
When I execute the above statement in utilities iam getting errors like:
Error 1045(28000):Access Denied for the user ‘sandeep’@’192.168.1.4′(using password:NO).
Please help me sir
Thank you..
Try using
--slave=sandeep:system@192.168.1.7:3306(assuming “system” is the password for sandeep on that mysqld).Andrew.
Hi sir,,,
Same Error Iam facing
I will give detail steps what i did:
Iam using 2 laptops master is windows8 and slave is windows7
Step1:
I have changed in server section below [mysqld] of my.ini:
In master(192.168.1.4: password is ‘system’): my.ini:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery=1
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=”C:/ProgramData/MySQL/MySQL Server 5.7/data\”
socket=mysql
report-host=localhost
Step2:
In slave(ip is 192.168.1.7 password is ‘system’) my.ini:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery=1
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=2
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=”C:/ProgramData/MySQL/MySQL Server 5.7/data\”
socket=mysql
report-host=localhost
Step3: restarted both the servers
Step4: In mysql utilities i executed the follwing:
mysqlreplicate –master=root@192.168.1.4:3306 \ –slave=sandeep:slave@192.168.1.7:3306 –rpl-user=sandeep:system -b
output:
#master 192.168.1.4:….connected…
#slave 192.168.1.7:..Error:…can’t connected to the slave server…
Error 1045(28000):Access Denied for the user ‘sandeep’@’192.168.1.4′(using password:YES)
Help me sir..
Thank you….
From 192.168.1.4, try running
mysql -h 192.168.1.7 -P 3306 -u sandeep -p slaveto check that you have the correct password and privs.Andrew.
mysqluc> mysqlreplicate –master=root:system@127.0.0.1:3306 –slave=root:system@192.168.1.184:3306
# master on 127.0.0.1: … connected.
# slave on 192.168.1.184: … connected.
# Checking for binary logging on master…
# Setting up replication…
ERROR: failed to synch slave with master.
ERROR: Cannot setup replication.
Execution of utility: ‘mysqlreplicate –aster=root:system@127.0.0.1:3306 –slave=root:system@192.168.1.184:3306’ ended with return code ‘1’ but no error message was streamed to the standard error, please review the output from its execution.
Reply me sir..
Thank you..
Have you tried connecting to each of these servers using the root/system credentials from the machine running mysqlreplicate utility? If that doesn’t work then you need to grant extra user privs. If not, could you please share the .cnf file for each of the servers?
Andrew.
Hi Andrew,
Nice article. I have a question that MySQL provided the auto failover support with utilities. But is there any possibility that my client connected with master DB would also be able to detect automatically that old-master DB is down and now master DB is one of the slave and connect to it.
I hope, I delivered my question with clarity.
Regards
MWM
I’d suggest taking a look at MySQL Fabric as it will fail over the application to the new master…
Andrew.
Dear Andrew,
As mentioned in your article:
“by default it will select the one that is most up-to-date and then apply any changes that are available on other slaves but not on this one before promoting it to be the new master. The user can override this behaviour (for example by limiting which of the slaves are eligible for promotion)”
Kindly, tell me the way through which I can limits my slave to become master.
Regards
MWM
Hi MWM,
you can use the
--candidatesoption with themysqlfailovercommand; see http://dev.mysql.com/doc/mysql-utilities/1.4/en/mysqlfailover.htmlI found it:
we can use –candidates=
Regards
MWM
Dear Andrew,
MySQL replication utilities support semi-synchronous or asynchronous replication ?
Regards
MWM
Asynchronous; you’d have to enable semi-sync yourself.
Best Regards, Andrew.
Dear Andrew,
I am Back … 🙂 …
I read your blog again and I found your statement: “Binding in your own scripts to be run before or after the failover (e.g. inform your application to switch master?)”
Sir, we have –exec-post-failover option to use with failover utility. I will make a script to tell application about the current master. But from where I can get the information that which mysql instance become a new master through mysqlfailover utility.
Waiting for your positive response.
Regards
MWM
Addition to my previous comment … means i want some variable so that my script should read that variable/information and take decision that this mysql instance is now the new master made by utility.
Regards
MWM
If you take a look at Table 5.2 in the mysqlfailover documentation it states that the host and port of the new master will be passed to the script as parameters.
Regards, Andrew.
Dear Andrew,
Thank you for the quick response.
Can you suggest some example to run scripts.
I made my own script and pass it to –exec-after=/etc/failover.sh with mysqlfailover command but its giving me the results. During failover health monitor shows that script run completed OK.
Regards
MWM
Sorry .. some corrections:
my script is not giving me the results.
secondly –exec-after=/etc/failover.sh
Dear Andrew,
Skip my all previous comments, because I take another step. 🙂
My script was not giving me the results because some commands of my script needs root privileges. I sorted them so now —exec-after run scripts successfully.
Sir, now I just want your help regarding example of how new master IP and port is passed to the external script.
Waiting for your positive response.
Thank you for all your support.
Regards
MWM
My assumption is that they are passed as command-line arguments in the order in which they appear in Table 5.2 but I’ve raised a bug report for the documentation to be made more explicit.
Andrew.
This has now been fixed under the bug report http://bugs.mysql.com/bug.php?id=73115
Hi Andrew. I’m wondering if you can help me. I’m trying to set-up a master-slave on a single master and a single slave, on two separate servers (called, embarrassingly C-3PO and BOBAFETT). I’ve used your profile config files (slightly altered as it’s a Windows system) and then issuing the following:
mysqluc> mysqlreplicate –master=root:PASSWORD@c-3po:3306 –slave=root:PASSWORD@bobafett:3306
And I get the following output showing a failure:
# master on c-3po: … connected.
# slave on bobafett: … connected.
# Checking for binary logging on master…
# Setting up replication…
ERROR: Cannot setup replication.
Execution of utility: ‘mysqlreplicate –master=root:PASSWORD@c-3po:3306 –slave=root:PASSWORD@bobafett:3306’ ended with return code ‘1’ but no error message was streamed to the standard error, please review the output from its execution.
I’ve granted privileges as described by your instructions and I can connect to the two servers remotely, so I don’t think it’s that.
Have you any idea what I could have done wrong?
I think I found out what I did wrong. I’d given the binlog the same name as the general log. Doh.
Hello Andrew. First of all thank you for your post. It is very useful. I’ve two mysql servers with gtid and mysqlfailover. Everything is working well but I’ve seen that when a master server fails and mysqlfailover has changed to the other server as a master, when I start the old master it is still a master too. Is there any way to the old master autoregister as slave when it comes up?
Hope you can help me.
Thank you very much.
Hi Javier,
what do you mean when you say that the old server starts as a master again? A server is only a master if other servers have connected to it as slaves; the other servers should now be slaves to the new master – are you seeing them revert back to the original master after it has been returned to service? Of course, the old master has binary logging enabled (a prerequisite to being a master) and so if you didn’t need that then you could turn it off in that server’s config file.
Note that MySQL Fabric is now GA and that may offer a more complete failover solution for you –it’s worth taking a look.
Best Regards, Andrew.
Thanks Andrew
What I mean is when a master is down and mysqlfailover has changed to another master, the old master does not connect as a slave to the new master automatically. Is it possible??
Thank you again
No automated way of doing that with mysqlfailover; you’ll need to issue the CHANGE MASTER commands or use mysqlreplicate afer recovering the server – there are more options with MySQL Fabric.
Best Regards, Andrew.
Hi Andrew,
I’ve setup 1 master and 1 slave for testing the autofailover,.
master:192.168.56.101 port:3306
slave:192.168.56.102 port:3306
I’ve some problem with this error status when I run mysqlrplshow :
# master on 192.168.56.101: … connected.
# Finding slaves for master: 192.168.56.101:3306
WARNING: There are slaves that have not been registered with –report-host or –report-port
– unknown host:3306
# Replication Topology Graph
No slaves found.
Both are running in Centos 6.4. Could you help me?.
thanks.
Hi Andrew,
Fix the error already, but another error came up:
When I run the mysqlfailover, the Failover Mode = fail, then I use –force to make it Auto.
the failover test running well. but when I try to switch back to original config. it didn’t work.
the error:
candidate is not connected to the correct master.
Errors found. Switchover aborted.
Replication Topology Health:
192.168.56.102 3306 master up on ok
192.168.56.101 3306 slave warn Slave is not connected to master
Kindly please help, Thank You.
The error is saying that you haven’t added the original master back into the topology as a slave (and so it isn’t in a position to become the new master as it won’t have the latest changes).
Andrew.
OK, I understand and fix it already.
My other question, I have 1 master (in centos) and 3 slave (2 in centos and 1 in win7).
When I try to set the win7 slave and put it into topology it said: slave using different innodb setting with master.
Sorry for being OOT, maybe you could help.
Thank You.
Have you tried setting report-host and report-port in your .cnf file (there’s a dependency on this)?
Andrew.
Yes, just add it and that fix the error already.
Thank you.
Hi,
I like this tutorial but i feel like I am missing something. I want the sort of replication and failover behaviour you describe here but my situation is that I have an existing primary server with a very large amount of existing data. Do these new replication tools (new to me anyway) handle pulling over the existing data from the Master to the Slave(s)? Do I have to do that initial copy by hand in advance like you did before the new tools existed?
Thanks
You still need to populate any data that isn’t still in your binary logs.
Andrew.
Could you please tell me what is the cons and pros of using a HA Clustering for MySQL(share storage via NFS or SAN) with this failed over scene ?
The best place to start is with this MySQL HA WHite paper.
Best Regards, Andrew.
Seems like there are at least 2 options to make a slave, either with firing “CHANGE MASTER TO” from mysql console, or by firing “mysqlreplicate” from OS console. Did i get that right ?
Thanks.
Yes – you’re correct
Best Regards, Andrew.
Hello andrew,
I have installed MySQL 5.6.21 Community edition on Windows 7 64bit OS, and have configured Master Slave replication with using another Windows 7 64 bit System with the same edition of MySQL software.
But When I execute mysqlrplcheck,
mysqlrplcheck –master=root:password@ip:port –slave=root@ip
I get the following error,
Traceback (most recent call last):
File “G:\ade\build\sb_0-13304663-1411585705.14\Python-2.7.6-windows-x86-64bit\lib\site-packages\cx_Freeze\initscripts\Console.py”, line 27,in
File “scripts\mysqlrplcheck.py”, line 146, in
File “.\mysql\utilities\common\server.py”, line 520, in check_hostname_alias
File “.\mysql\utilities\common\server.py”, line 921, in is_alias
File “.\mysql\utilities\common\server.py”, line 774, in _update_alias
ValueError: too many values to unpack
Also for mysqlfailover, the command works fine, detects slave, displays health information, but when the master fails the same error would be returned.
One more thing both mysqlrplcheck and mysqlfailover have worked fine in the master slave configuration which I had set up in CentOS.
Can you suggest me any methods so that I can solve this error.
PS: I have also installed Active-Perl and Active Python in the master system and I do not have the G:\ drive in my system, but it is shown in the error.
Possibly a problem with your Python installation?
Hello Andrew,
I will try it.
Thank you for your help.
Regards,
Deekshith
Hello Andrew,
We have configured simple 2 host replication (Master and Slave) with following mysql-community-server. Our Slave server was down 2 days some power failure problem.
When we start slave server there were lot of data difference on salve and master server. We wont to recover it without DB read lock. Is there any workaround to recover/sync missing data from master server.
Databases size are very huge it very critical to dump and restore manually.
—————————————————
mha4mysql-node 0.54-1 all Master High Availability Manager and Tools for MySQL, Node Package
mysql-apt-config 0.3.3-2ubuntu14.04 all Auto configuration for MySQL APT Repo.
mysql-client 5.6.23-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version
mysql-common 5.6.23-1ubuntu14.04 i386 MySQL Common
mysql-community-client 5.6.23-1ubuntu14.04 amd64 MySQL Client
mysql-community-server 5.6.23-1ubuntu14.04 amd64 MySQL Server
mysql-server 5.6.23-1ubuntu14.04 amd64 MySQL Server meta package depending on latest version
—————————————————
>> /etc/mysql/my.cnf
[…..
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_trust_function_creators=TRUE
binlog_format = MIXED
expire_logs_days = 5
binlog_do_db = xxxx
binlog_ignore_db = xxxx
#bind-address = 127.0.0.1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log-error = /var/log/mysql/error.log
max_allowed_packet = 6G
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
skip-name-resolve
……]
Have you considered using MySQL Enterprise Backup for fast, on-line backups of the data (assuming your tables are in InnoDB)?
Andrew.
Hi Sir,
I have tried replication using the above method. Unfortunately, i got the following error.
# master on 10.24.184.12: … connected.
# slave on 10.24.184.13: … connected.
# Checking for binary logging on master…
# Setting up replication…
ERROR: failed to synch slave with master.
ERROR: Cannot setup replication.
Master my.ini
[mysqld]
server-id=9
expire_logs_days = 30
log-bin = “C:/logmysql/mysql-bin.log”
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
master-verify-checksum=1
report-host=10.24.184.12
report-port=3306
port=3306
Slave my.ini
sync_relay_log_info=10000
binlog_format=ROW
log-slave-updates=true
log-bin=C:\logs\mysql-bin.log
gtid-mode=ON
enforce-gtid-consistency=true
server-id=8
report-host=10.24.184.13
report-port=3306
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
I could able to connect root of master in slave and root account of slave in master.
Please look in to the issue as soon as possible.
Hi Aneesha,
what exact command did you run?
Any clues in the log files on either machine?
Andrew.
One more thing. I have already used the following command to establish replication.
CHANGE MASTER TO MASTER_HOST=’10.24.184.12′, MASTER_USER=’replicator’, MASTER_PASSWORD=’abcdef’, MASTER_AUTO_POSITION=1;
Replication is working properly. But i need to make the auto failover condition.. That is why i have tried this replication method. Can anyone help me.
Hi Andrew,
I used the following command.
mysqlreplicate –master=root:abcdef@10.24.184.12:3306 –slave=root:abcdef@10.24.184.13:3306 –rpl-user=replicator:abcdefg
mysqlreplicate is likely failing because you already have replication set up. You should be able to use mysqlfailover with the servers that you’ve already have replication working for *or* you could take a look at MySQL Fabric which has the extra benefit of helping with application failover to the newly promoted master.
Andrew.
Hi Andrew,
I have tried mysqlfailover command. But i could not get any replication health status.
0 rows found is retrieved. Failover mode is auto.
Replication is working fine. When i changed anything in master, it also reflected in slave.
When i excecute mysqlrplshow and mysqlrplcheck commands, it shows the correct result. But i tried to run the mysqlfailover command, it does not show master and slave in health status.
I used the following command
mysqlfailover –master=root:abcedfg@10.24.184.12:3306 –discover-slaves-login=root:abcedfg
Hi Andrew,
I got the following result when try to execute the mysqlfailover command.
Failover Mode = auto Next Interval = Tue May
Master Information
——————
Binary Log File Position Binlog_Do_DB Binlog
mysql-bin.000016 9568
GTID Executed Set
8fe8b710-cd34-11e4-824d-fa163e52e544:1-1143
Replication Health Status
0 Rows Found.
Q-quit R-refresh H-health G-GTID Lists U-UUIDs U
Hi Andrew,
Started mysqlfailover utility as a daemon and it monitor for some time and stops working after some time. However, I see the linux level process was still active including the pid file available, but not failing over. Is the mysqlfailover suppose to run in the background if it starts as a daemon?
Command to start the failover
mysqlfailover –verbose –master=root@10.52.201.11 –discover-slaves-login=root –failover-mode=auto –candidates=root@10.52.201.101,root@10.206.75.34 –daemon=start –log=mysqlfailover.log –pidfile=failover_daemon.pid
When I brought down the master, mysqlfailover is not detecting the failure and no activities from mysqlfailover utility.
Master and slave are running with mysql Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using EditLine wrapper.
Any clue to trouble shoot the issue?
Hi Ravi,
I looked into it a little and you may have hit upon a bug – it would be good if you could create a new Raise a MySQL bug report.
Hi Andrew,
Thanks for your response. I did create a bug #76905 (http://bugs.mysql.com/bug.php?id=76905) back in April and I didn’t see any feedbackup/response from support team. Is it the right place?
Appreciated your support.
Hi Sir,
Great tutorial and i managed to implement this with two nodes. Sir im relatively new to mysql, now i would like to add a new node to it so 1 master 2 slaves. how can i do that? Appreciate your time and reply Sir thanks in advance
Sorry for the delay – I’m not able to check at this point but it’s worth raising the question on the MySQL Utilities forum.
CONFIGURATION FILE OF MASTER !!!
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# – “/etc/mysql/my.cnf” to set global options,
# – “~/.my.cnf” to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with –help to get a list of available options and with
# –print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain “#” chars…
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
auto_increment_increment = 10
auto_increment_offset = 1
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log – should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
binlog-format = ROW
log_bin = /var/log/mysql/mysql-bin.log
log-slave-update = true
gtid-mode = on
#disable-gtid-unsafe-statements = true
enforce-gtid-consistency= true
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = read_replica
report-host = 52.74.227.48
report-port = 3306
#binlog_ignore_db = include_database_name
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:25M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
#innodb_log_files_in_group = 2
#innodb_buffer_pool_size = 16M
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
#
!includedir /etc/mysql/conf.d/
CONFIGURATION FILE OF SLAVE !!!
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# – “/etc/mysql/my.cnf” to set global options,
# – “~/.my.cnf” to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with –help to get a list of available options and with
# –print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain “#” chars…
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log – should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 4
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog-format = ROW
log-slave-updates = true
gtid-mode = on
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
report-host = 52.74.230.28
report-port = 3306
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
#
!includedir /etc/mysql/conf.d/
SETTING REPLICATION USING MYSQL UTILITIES !!
mysqluc> mysqlreplicate –master=root:root@localhost:3306 –slave=root@54.169.73.7:3306
# master on localhost: … connected.
# slave on 54.169.73.7: … connected.
# Checking for binary logging on master…
# Setting up replication…
ERROR: failed to synch slave with master.
ERROR: Cannot setup replication.
An error was found, while executing the command, use “show last error” command to display details
I’m afraid that I’m not directly involved with MySQL at the moment and so I’d suggest posting this question to the MySQL Replication forum.
Great post…Thanks so much. Really helped me get the utilities functioning so I can eventually use in a production environment.
David