1

have two database. They are db1 and db2. db1 master, slave at db2.

d1 have write/read mode. d2 have read mode.

I have a problem like this: When the master db fails, The slave db will enable the write mode. How do I synchronize the master db with the information written to the slave db after activating master db again.

I understand how master db gives error, but I have some questions:

  1. How do I enable slave mode after master db fail?
  2. How do I activate the master db again?
  3. Most important: How can I synchronize the information written in slave databases with master databases.
  4. Finally, how do I cancel the slave databases's write mode?

You can see the my.cnf file as below:

<CLASSHOST:db1 db2>
# MySQL config file (for very large systems 1G memory and up)

# The following options will be passed to all MySQL clients
[client]
port    = 3306
socket  = /u1/database/mysql/mysql.sock

# The MySQL server
[mysqld]
skip-innodb
user    = mysql
port    = 3306
socket  = /u1/database/mysql/mysql.sock

## Each processor MUST have its own server_id !!
#  Concat SITE with local SERVERID
server_id=<CFGTAG:SERVICE_SITE_ID><CFGTAG:MYSQLSERVERID>

## Performance tuning variables
key_buffer=384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8

# DISABLE query_cache : it can hang tue MySQL server for 10-50 seconds.
query_cache_type = 0
query_cache_size = 0
# Try number of CPUs*2 for thread_concurrency
thread_concurrency = 8

# this holds about 500000 SpamList rows - the only x HEAP table
set-variable = max_heap_table_size=30M

max_connections=200
back_log=50
# Prevent host-cache from locking out host.
max_connect_errors=999999999

# Log options: need BINARY log, log all updates on slave processors,
#     log slow queries

log_bin=<CFGTAG:MYHOSTNAME>-bin
log_slave_updates
log_slow_queries=slow.log

# ------------------------------------------------------------------
# Replication variables BLOCK...
#           COMMON VARIABLES
# These variables control which DBs are snared by a slave processor
# db1 may not be a slave in HA mode. It MUST be a slave in DR mode
# db2 will always be a slave in both HA and DR mode.

replicate-do-db=x
replicate-do-db=xx
replicate-do-db=xxx
replicate-do-db=xxxx
replicate-do-db=xxxxx
replicate-do-db=xxxxxx
replicate-do-db=xxxxxxx
replicate-do-db=xxxxxxxx
replicate-do-db=xxxxxxxxx

# replicate-same-server-id prevents loops. FALSE is default NEVER change it.
replicate-same-server-id=FALSE

## We choose not to set these via this method
# master-host=my master host
# master-user=our replication user 
# master-password=  

# -----  HOST SPECIFIC replication Variables 

</CLASSHOST>
<CLASSHOST:db1 db2 >
# Dispense auto-increment keys ...
#    The "skip" value is the greater of 2 or the total no of GEO sites.
auto_increment_increment=<CFGTAG:SITE_MYSQL_AUTO_INCR_INCR>
#    Each site's slice is determined by its SITE_ID.
auto_increment_offset=<CFGTAG:SERVICE_SITE_ID>

## -------- End of replication variables block
</CLASSHOST>
<CLASSHOST:db2 >
# HA only, and only for the SLAVE. We should use it on the 3rd copy, too.
# users with the SUPER privilege (repl and root) are not blocked.
read_only

## -------- End of replication variables block
</CLASSHOST>
<CLASSHOST:admin1 db1 db2>

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

</CLASSHOST>

How can I implement these steps? Or I need it in another file.

Can you inform me about this topic.

Thanks,

  • I see one my.cnf; is the other one the same except for the server-id? – Rick James Feb 9 '18 at 3:17
  • this my.cnf file is just template. The "<.....>" information is filling in for db1 and db2 when the system is running. For example: server_id = <CFGTAG: SERVICE_SITE_ID> <CFGTAG: MYSQLSERVERID> For db1: server_id = 1 For db2: server_id = 2 is happening. @RickJames – oxxxo Feb 13 '18 at 6:43
  • So my.cnf is separate for two databases. @RickJames – oxxxo Feb 13 '18 at 6:50
  • Your Master and Slave are on two separate servers (or VMs), correct? There is virtually no reason to have M & S on the same hardware! – Rick James Feb 13 '18 at 22:09
  • Okey, Understand you. But I have some question about that--> 1- How do I enable slave mode after master db fail? 2-How do I activate the master db again? 3-Most important: How can I synchronize the information written in slave databases with master databases. 4-Finally, how do I cancel the slave databases's write mode? @RickJames – oxxxo Feb 27 '18 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.