This section describes how replication handles various problematic issues.
auto_increment Columns, LAST_INSERT_ID()
When a query inserts into such a column, or uses LAST_INSERT_ID(), one or two Intvar_ og_event are written to the binlog just before the Query_log_event.
User Variables (Since 4.1)
When a query uses a user variable, a User_var_log_event is written to the binlog just before the Query_log_event.
Example: SQL_MODE, FOREIGN_KEY_CHECKS. Not dealt with. Guilhem is working on it for version 5.0.
USER(), LOAD_FILE(). Not dealt with. Will be solved with row-level binlogging (presently we have query-level binlogging, but in the future we plan to support row-level binlogging too).
Non-repeatable UDF Functions
“Non repeatable” means that they have a sort of randomness, for example they depend on the machine (to generate a unique ID for example). Not dealt with. Will be solved with row-level binlogging.
For the moment, a substituted normal query is written to the master's binlog. Using prepared statements on the slave as well is on the TODO.
Temporary tables depend on the thread which created them, so any query event which uses such tables is marked with the LOG_EVENT_THREAD_SPECIFIC_F flag. All events have in their header the id of the thread which created them, so the slave knows which temporary table the query refers to.
When the slave is stopped (STOP SLAVE or even mysqladmin shutdown), the in-use replicated temporary tables are not dropped (like clients' temporary tables are). This way, when the slave restarts they are still available.
When a connection using temporary tables terminates on the master, the master automatically writes some DROP TEMPORARY TABLE statements for them so that they are dropped on the slave as well.
When the master brutally dies, then restarts, it drops all temporary tables which remained in tmpdir, but without writing it to the binlog, so these temporary tables are still on the slave, and they will not be dropped before the next slave's mysqld restart. To avoid this, the slave drops all replicated temporary tables when it executes a Start_log_event read from the master. Indeed such an event means the master's mysqld has restarted so all preceding temporary tables have been dropped.
Presently we have a bug: if the slave mysqld is stopped while it was replicating a temporary table, then at restart it deletes this table (like a normal temporary table), which may cause a problem if subsequent queries on the master refer to this table.
LOAD DATA [LOCAL] INFILE (Since 4.0)
The master writes the loaded file to the binlog, but in small blocks rather than all at once. The slave creates a temporary file, the concatenation of each block. When the slave reads the final Execute_load_log_event, it loads all temporary files into the table and deletes the temporary files.If the final event was instead a Delete_file_log_event then these temporary files are deleted without loading.
MySQL Related Interview Questions
|PHP Interview Questions||MySQL Interview Questions|
|PHP+MySQL Interview Questions||Drupal Interview Questions|
|MYSQL DBA Interview Questions||PHP5 Interview Questions|
|WordPress Interview Questions||Joomla Interview Questions|
|CakePHP Interview Questions||CodeIgniter Interview Questions|
|PHP7 Interview Questions|
A Guided Tour Of The Mysql Source Code
Important Algorithms And Structures
How Mysql Performs Different Selects
How Mysql Transforms Subqueries
Mysql Client/server Protocol
Prepared Statements And Stored Routines
Myisam Storage Engine
Innodb Storage Engine
Writing A Custom Storage Engine
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.