My SQL Interview Questions and Answers

Replication in MySQL
One way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.

Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.

Note that if you are replicating a database, all updates to this database should be done through the master!

On older servers one can use the update log to do simple replication.

Another benefit of using replication is that one can get live backups of the system by doing a backup on a slave instead of doing it on the master.

MySQL - Replication Implementation Overview
MySQL replication is based on the server keeping track of all changes to your database (updates, deletes, etc) in the binary log. and the slave server(s) reading the saved queries from the master server's binary log so that the slave can execute the same queries on its copy of the data.

It is very important to realize that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). Any slaves which you set up will need copies of all the data from your master as it existed the moment that you enabled binary logging on the master. If you start your slaves with data that doesn't agree with what was on the master when the binary log was started, your slaves may fail.

A future version (4.0) of MySQL will remove the need to keep a (possibly large) snapshot of data for new slaves that you might wish to set up through the live backup functionality with no locking required. However, at this time, it is necessary to block all writes either with a global read lock or by shutting down the master while taking a snapshot.

Once a slave is properly configured and running, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect every master-connect-retry seconds until it is able to reconnect and resume listening for updates.

Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.

MySQL - HOWTO
Below is a quick description of how to set up complete replication on your current MySQL server. It assumes you want to replicate all your databases and have not configured replication before. You will need to shutdown your master server briefly to complete the steops outlined below.

Make sure you have a recent version of MySQL installed on the master and slave(s). Use Version 3.23.29 or higher. Previous releases used a different binary log format and had bugs which have been fixed in newer releases. Please, do not report bugs until you have verified that the problem is present in the latest release.
Set up special a replication user on the master with the FILE privilege and permission to connect from all the slaves. If the user is only doing replication (which is recommended), you don't need to grant any additional privileges. For example, to create a user named repl which can access your master from any host, you might use this command:
GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '';

Shut down MySQL on the master.
mysqladmin -u root -p<password> shutdown

Snapshot all the data on your master server. The easiest way to do this (on Unix) is to simply use tar to produce an archvie of your entrie data directory. The exact data directory location depends on your installation.
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir

Windows users can use WinZip or similar software to create an archive of the data directory.
In my.cnf on the master add log-bin and server-id=unique number to the [mysqld] section and restart it. It is very important that the id of the slave is different from the id of the master. Think of server-id as something similar to the IP address - it uniquely identifies the server instance in the comminity of replication partners.
[mysqld]
log-bin
server-id=1

Restart MySQL on the master.
Add the following to my.cnf on the slave(s):
master-host=<hostname of the master>
master-user=<replication user name>
master-password=<replication user password>
master-port=<TCP/IP port for master>
server-id=<some unique number between 2 and 2^32-1>

replacing the values in <> with what is relevant to your system. server-id must be different for each server participating in replication. If you don't specify a server-id, it will be set to 1 if you have not defined master-host, else it will be set to 2. Note that in the case of server-id omission the master will refuse connections from all slaves, and the slave will refuse to connect to a master. Thus, omitting server-id is only good for backup with a binary log.
Copy the snapshot data into your data directory on your slave(s). Make sure that the privileges on the files and directories are correct. The user which MySQL runs as needs to be able to read and write to them, just as on the master.
Restart the slave(s).
After you have done the above, the slave(s) should connect to the master and catch up on any updates which happened since the snapshot was taken.

If you have forgotten to set server-id for the slave you will get the following error in the error log file:

Warning: one should set server_id to a non-0 value if master_host is set.
The server will not act as a slave.

If you have forgot to do this for the master, the slaves will not be able to connect to the master.

If a slave is not able to replicate for any reason, you will find error messages in the error log on the slave.

Once a slave is replicating, you will find a file called master.info in the same directory as your error log. The master.info file is used by the slave to keep track of how much of the master's binary log is has processed. Do not remove or edit the file, unless you really know what you are doing. Even in that case, it is preferred that you use CHANGE MASTER TO command.

Page Numbers : 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Job Interview Questions for more Interview Questions with Answers