MySQL: Pacemaker cannot start the failed master as a new slave?
Solution 1:
Eureka!
Both of us forgot a very very important log file, it's... /var/log/mysqld.log
:
socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Atomicorp
[Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000082' at position 58569, relay log './mysqld-relay-bin.000002' position: 58715
[Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000082' at position 58569
[Warning] Aborted connection 10 to db: 'unconnected' user: 'test_user' host: 'localhost' (init_connect command failed)
[Warning] The MySQL server is running with the --read-only option so it cannot execute this statement
[Note] /usr/libexec/mysqld: Normal shutdown
As you can guess, I tracked the user activity by combining the binlog and init-connect
:
init_connect = "INSERT INTO audit.accesslog (connect_time, user_host, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());"
but serving-6192
is set read-only when starting as a slave, and then when Pacemaker perform monitor operation with test_user
:
# Check for test table
ocf_run -q $MYSQL $MYSQL_OPTIONS_TEST \
-e "SELECT COUNT(*) FROM $OCF_RESKEY_test_table"
init_connect
command failed with the above error:
The MySQL server is running with the
--read-only
option so it cannot execute this statement
The solution is I should set the init_connect
option to the empty string before initializing the monitor action (don't forget to turn it back when promoting a node to become a master)
To anyone who are using event scheduler: also note that you must turn it on when promoting a slave to become a master:
set_event_scheduler() {
local es_val
if ocf_is_true $1; then
es_val="on"
else
es_val="off"
fi
ocf_run $MYSQL $MYSQL_OPTIONS_REPL \
-e "SET GLOBAL event_scheduler=${es_val}"
}
get_event_scheduler() {
# Check if event-scheduler is set
local event_scheduler_state
event_scheduler_state=`$MYSQL $MYSQL_OPTIONS_REPL \
-e "SHOW VARIABLES" | grep event_scheduler | awk '{print $2}'`
if [ "$event_scheduler_state" = "ON" ]; then
return 0
else
return 1
fi
}
mysql_promote() {
local master_info
if ( ! mysql_status err ); then
return $OCF_NOT_RUNNING
fi
ocf_run $MYSQL $MYSQL_OPTIONS_REPL \
-e "STOP SLAVE"
# Set Master Info in CIB, cluster level attribute
update_data_master_status
master_info="$(get_local_ip)|$(get_master_status File)|$(get_master_status Position)"
${CRM_ATTR_REPL_INFO} -v "$master_info"
rm -f $tmpfile
set_read_only off || return $OCF_ERR_GENERIC
set_event_scheduler on || return $OCF_ERR_GENERIC
Also don't forget to turn it off when demoting:
'pre-demote')
# Is the notification for our set
notify_resource=`echo $OCF_RESKEY_CRM_meta_notify_demote_resource|cut -d: -f1`
my_resource=`echo $OCF_RESOURCE_INSTANCE|cut -d: -f1`
if [ $notify_resource != ${my_resource} ]; then
ocf_log debug "Notification is not for us"
return $OCF_SUCCESS
fi
demote_host=`echo $OCF_RESKEY_CRM_meta_notify_demote_uname|tr -d " "`
if [ $demote_host = ${HOSTNAME} ]; then
ocf_log info "post-demote notification for $demote_host"
set_read_only on
set_event_scheduler off
Cheers,