Question

MySQL
Multiple Choice
Answer as soon as possible

1. Which one of the following is not a Replication Data Format?
(a) Row
(b) Mixed
(c) Statement
(d) Sync

2. When executing the following statement on the master:
UPDATE enormous_table SET col1 = 0;
Which replication format would be more efficient?
(Assume enormous_table is a large table with millions of rows)
(a) Statement
(b) Row
(c) Neither

3. When executing the following statement on the master:
INSERT INTO summary_table(col1, col2, sum_col3)
SELECT col1, col2, sum(col3)
FROM enormous_table
GROUP BY col1, col2;
Which replication format would be more efficient?
(Assume enormous_table is a large table with millions of rows)
(a) Statement
(b) Row
(c) Neither

4. Which one of the following is a way to detect if there is an error replicating data on the slave?
(a) Run SHOW MASTER STATUS and Look for an error description in LAST_SQL_ERROR in the results.
(b) Run SHOW MASTER STATUS and Look for an error description in INVALID_COMMAND_FOUND in the results.
(c) Run SHOW SLAVE STATUS and Look for an error description in INVALID_COMMAND_FOUND in the results.
(d) Run SHOW MASTER STATUS and Look for an error description in SYNCING = NO in the results.
(e) Run SHOW SLAVE STATUS and Look for an error description in LAST_SQL_ERROR in the results.

5. Which one of the following is the correct command to skip the next command in the binlog on the slave (this is sometimes used to skip a command that is causing a replication error)?
(a) SET GLOBAL SQL_SLAVE_SKIP = YES;
(b) SET GLOBAL SQL_SLAVE_SKIP_COMMANDS = 1;
(c) SET GLOBAL SQL_SLAVE_SKIP_ON_ERROR = YES;
(d) SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

6. Which one of the following is the command for temporarily disabling the binlog on the master so you can run a statement without it getting replicated to the slave(s)?
(a) SET REPLICATE_TO_SLAVE = NO;
(b) SET SQL_LOG_BIN = 0;
(c) SET ENABLE_BINLOG = 0;
(d) SET DISABLE_BINLOG = YES;

Answer 1

Find the answers below for your questions:

1. Replication data formats available for replicating databases from master to slave are statement based, row based, mixed format based.

Statement based replication :in this method the binary log stores the SQL statements used to store the databases on the master server. The slave reads this data and reexecutes these SQL statements.

Row based replication: in this method the binary log stores the record level changes occurred at master side and the slave reads this data and manipulates it's records.

Mixed based replication: in this method the server can either choose statement based or row based replication method dynamically depending on certain conditions.

Conclusion: hence a,b,c are proper replication data formats. d is not a replication data format.

2. Given statement : update enormous_table set col1 =0

It is more efficient to use statement based replication method rather than row based due to following reasons:

a. Update statement is single statement where it can update the whole rows using one statement. If we execute the same statement in the slave then the data will exactly replicated in slave similar to slave.

b.it is said that there are millions of rows in that table hence if we use row based all these records needs to be saved in binary log and slave will fetch it and executes it. So it will be better if we go with statement based as we can log only this statement and execute it.

Hence option a is efficient

3. The insert statement specified in the question 3 groups the vaues of the table and inserts summary data.

If we go with statement based replication then when the same above statement if executed in slave may result in different output than master data as there is no order by clause in the select Statement. Hence the select statement might retrieve data in different order and stores in slave assuming there is auto increment field.

So in this case row based replication is suitable as it logs all the records and when slave executes these records , we get correct copy of data in slave.

Hence we go with option b

4. Command : SHOW MASTER STATUS is used to know the information about binary log file in master server as we want the information of a slave server.Hence options a,b,d are wrong.

For slave the command is SHOW SLAVE STATUS

As there is no invalid_command_found field returned by above command option c is wrong.

Option d is the correct one as the error message when detected will be stored in Last_SQL_Error field .

5. option d is correct one for skipping the replicated errors as per the command provided

6. For temporarily disabling the binlog on the master we use command

SET SQL_LOG_BIN=0 . If this value is 1 then it will enable to log in the binlog. By default it will be 1 .

Hence option b is correct as per the syntax and command provided.