MySQL InnoDB add new instance to existing cluster

innodbMySQL

Server version: 5.7.23-log MySQL Community Server (GPL)

I'm running an InnoDB cluster and trying to add a new node, the cluster is a couple of years old and binlogs are being cleared after 2 weeks.

Adding a new node to a new cluster is straight forward enough because the master has all the binlogs but I can't find anywhere pointing me to instructions on how to add a new node to an existing cluster.

It's in production and I don't want to 'test and see' in case I break something so I'm looking for a definitive set of instructions.

Can anyone assist please?

Best Answer

The old way of adding a Slave might be best:

Stop mysqld on an existing Slave (Replica)
Copy the disk (or at least all of MySQL's stuff)
Change the server_id
See if something in the Master contains a list of servers in the topology; if so, augment it
Start mysqld on both replicas.

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Thesql innodb_flush_log_at_trx_commit clarification

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

It only affects recovery.

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

Correct. There may be reasons to hide changes that other transactions see (as part of multi-version concurrency control). If you're trying to build tests to prove things - make sure you understand how transaction-isolation levels work :)

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Correct. To be able to recover all (setting: 1) ti also assumes that the underlying hardware is not adding additional buffering to improve IO performance. I have no reason to assume that Amazon doesn't do this, so I think setting it to 2 is a good cloud practice.

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance. It doesn't create more work.

Shameless plug for additional reading - When does MySQL perform IO?

Best Answer

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Thesql innodb_flush_log_at_trx_commit clarification

Related Question