Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
Description
I have a situation where I have several very large static tables that I wish to restore after a disaster or other event where the tables need to be restored, but at the same time, not impact production.
When using the mysql or mysqlimport commands to restore these files, the Slaves immediately start getting very far behind the master due to the size of the tables. Which causes a log of concern.
What I have found is a king of workaround due to some recent 10.5.x changes, but it does not really help as it appears to not be working as expected. For the purposes of this discussion, assume the 'master' has already had it's tables restored. Here the pseudocode:
$files = array('/tmp/table1.sql', '/tmp/table2.sql', '/tmp/table3.sql');
$replicas = array('host1', 'host2', 'host3');
$master = array('alreadydone')
foreach($replicas as $host)
$cnn_id = db_connect($host);
db_exec('SET @@session.sql_log_bin=0', $cnn_id);
db_exec('SET @@global.read_only=0', $cnn_id);
foreach($files as $file-table)
{ db_exec("SOURCE $file-table", $cnn_id); } db_exec('SET @@session.sql_log_bin=1', $cnn_id);
db_exec('SET @@global.read_only=1', $cnn_id);
}
The problem I have is that the 'SOURCE' command is notoriously unreliable. I'm getting 1064 errors currently, and just scratching my head. The files are created using:
mysqldump [options] database table > table.sql
It would be nice if there were an option to run the mysqlimport or mysql command to bypass the binary log, and even possibly to take a list of hosts and perform a three way insert while avoiding the binary log and the overhead it would present.