Question 16 :
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.
Question 17 :
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
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.
Restart MySQL on the master.
Add the following to my.cnf on the slave(s):
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.
Question 18 :
MySQL - Replication Features and known problems
Below is an explanation of what is supported and what is not:
Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID, and TIMESTAMP values.
RAND() in updates does not replicate properly. Use RAND(some_non_rand_expr) if you are replicating updates with RAND(). You can, for example, use UNIX_TIMESTAMP() for the argument to RAND().
LOAD DATA INFILE will be handled properly as long as the file still resides on the master server at the time of update propagation. LOAD LOCAL DATA INFILE will be skipped.
Update queries that use user variables are not replication-safe (yet).
Temporary tables starting in 3.23.29 are replicated properly with the exception of the case when you shut down slave server ( not just slave thread), you have some temporary tables open, and the are used in subsequent updates. To deal with this problem, to shut down the slave, do SLAVE STOP, then check Slave_open_temp_tables variable to see if it is 0, then issue mysqladmin shutdown. If the number is not 0, restart the slave thread with SLAVE START and see if you have better luck next time. There will be a cleaner solution, but it has to wait until version 4.0. In earlier versions temporary tables are not being replicated properly - we recommend that you either upgrade, or execute SET SQL_LOG_BIN=0 on your clients before all queries with temp tables.
MySQL only supports one master and many slaves. We will in 4.x add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce 'agent' processes to help doing load balancing by sending select queries to different slaves.
Starting in Version 3.23.26, it is safe to connect servers in a circular master-slave relationship with log-slave-updates enabled. Note, however, that many queries will not work right in this kind of setup unless your client code is written to take care of the potential problems that can happen from updates that occur in different sequence on different servers. Note that the log format has changed in Version 3.23.26 so that pre-3.23.26 slaves will not be able to read it.
If the query on the slave gets an error, the slave thread will terminate, and a message will appear in the .err file. You should then connect to the slave manually, fix the cause of the error (for example, non-existent table), and then run SLAVE START sql command (available starting in Version 3.23.16). In Version 3.23.15, you will have to restart the server.
If connection to the master is lost, the slave will retry immediately, and then in case of failure every master-connect-retry (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages.
Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if disk cache was not synced before the system died. Your system fault tolerance will be greatly increased if you have a good UPS.
If the master is listening on a non-standard port, you will also need to specify this with master-port parameter in my.cnf . In Version 3.23.15, all of the tables and databases will be replicated. Starting in Version 3.23.16, you can restrict replication to a set of databases with replicate-do-db directives in my.cnf or just exclude a set of databases with replicate-ignore-db. Note that up until Version 3.23.23, there was a bug that did not properly deal with LOAD DATA INFILE if you did it in a database that was excluded from replication.
Starting in Version 3.23.16, SET SQL_LOG_BIN = 0 will turn off replication (binary) logging on the master, and SET SQL_LOG_BIN = 1 will turn in back on - you must have the process privilege to do this.
Starting in Version 3.23.19, you can clean up stale replication leftovers when something goes wrong and you want a clean start with FLUSH MASTER and FLUSH SLAVE commands. In Version 3.23.26 we have renamed them to RESET MASTER and RESET SLAVE respectively to clarify what they do. The old FLUSH variants still work, though, for compatibility.
Starting in Version 3.23.21, you can use LOAD TABLE FROM MASTER for network backup and to set up replication initially. We have recently received a number of bug reports concerning it that we are investigating, so we recommend that you use it only in testing until we make it more stable.
Starting in Version 3.23.23, you can change masters and adjust log position with CHANGE MASTER TO.
Starting in Version 3.23.23, you tell the master that updates in certain databases should not be logged to the binary log with binlog-ignore-db.
Starting in Version 3.23.26, you can use replicate-rewrite-db to tell the slave to apply updates from one database on the master to the one with a different name on the slave.
Starting in Version 3.23.28, you can use PURGE MASTER LOGS TO 'log-name' to get rid of old logs while the slave is running. 11.5 Replication Options in my.cnf
If you are using replication, we recommend you to use MySQL Version 3.23.30 or later. Older versions work, but they do have some bugs and are missing some features.
On both master and slave you need to use the server-id option. This sets an unique replication id. You should pick a unique value in the range between 1 to 2^32-1 for each master and slave. Example: server-id=3
The following table has the options you can use for the MASTER:
log-bin=filename Write to a binary update log to the specified location. Note that if you give it a parameter with an extension (for example, log-bin=/mysql/logs/replication. log ) versions up to 3.23.24 will not work right during replication if you do FLUSH LOGS . The problem is fixed in Version 3.23.25. If you are using this kind of log name, FLUSH LOGS will be ignored on binlog. To clear the log, run FLUSH MASTER, and do not forget to run FLUSH SLAVE on all slaves. In Version 3.23.26 and in later versions you should use RESET MASTER and RESET SLAVE
log-bin-index=filename Because the user could issue the FLUSH LOGS command, we need to know which log is currently active and which ones have been rotated out and in what sequence. This information is stored in the binary log index file. The default is `hostname`.index. You can use this option if you want to be a rebel. (Example: log-bin-index=db.index) sql-bin-update-same If set, setting SQL_LOG_BIN to a value will automatically set SQL_LOG_UPDATE to the same value and vice versa.
binlog-do-db=database_name Tells the master it should log updates for the specified database, and exclude all others not explicitly mentioned. (Example: binlog-do-db=some_database)
binlog-ignore-db=database_name Tells the master that updates to the given database should not be logged to the binary log (Example: binlog-ignore-db=some_database)
The following table has the options you can use for the SLAVE:
master-host=host Master hostname or IP address for replication. If not set, the slave thread will not be started. (Example: master-host=db-master.mycompany.com)
master-user=username The user the slave thread will us for authentication when connecting to the master. The user must have FILE privilege. If the master user is not set, user test is assumed. (Example: master-user=scott)
master-password=password The password the slave thread will authenticate with when connecting to the master. If not set, an empty password is assumed. (Example: master-password=tiger)
master-port=portnumber The port the master is listening on. If not set, the compiled setting of MYSQL_PORT is assumed. If you have not tinkered with configure options, this should be 3306. (Example: master-port=3306)
master-connect-retry=seconds The number of seconds the slave thread will sleep before retrying to connect to the master in case the master goes down or the connection is lost. Default is 60. (Example: master-connect-retry=60)
master-info-file=filename The location of the file that remembers where we left off on the master during the replication process. The default is master.info in the data directory. Sasha: The only reason I see for ever changing the default is the desire to be rebelious. (Example: master-info-file=master.info)
replicate-do-table=db_name.table_name Tells the slave thread to restrict replication to the specified database. To specify more than one table, use the directive multiple times, once for each table. . (Example:
replicate-ignore-table=db_name.table_name Tells the slave thread to not replicate to the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table.(Example:
replicate-wild-do-table=db_name.table_name Tells the slave thread to restrict replication to the tables that match the specified wildcard pattern. . To specify more than one table, use the directive multiple times, once for each table. . (Example: replicate-do-table=foo%.bar% will replicate only updates to tables in all databases that start with foo and whose table names start with bar)
replicate-wild-ignore-table=db_name.table_name Tells the slave thread to not replicate to the tables that match the given wild card pattern. To specify more than one table to ignore, use the directive multiple times, once for each table.(Example: replicate-ignore-table=foo%.bar% - will not upates to tables in all databases that start with foo and whose table names start with bar)
replicate-ignore-db=database_name Tells the slave thread to not replicate to the specified database. To specify more than one database to ignore, use the directive multiple times, once for each database. This option will not work if you use cross database updates. If you need cross database updates to work, make sure you have 3.23.28 or later, and use replicate-wild-ignore-table=db_name.%(Example: replicate-ignore-db=some_db)
replicate-do-db=database_name Tells the slave thread to restrict replication to the specified database. To specify more than one database, use the directive multiple times, once for each database. Note that this will only work if you do not use cross-database queries such as UPDATE some_db.some_table SET foo='bar' while having selected a different or no database. If you need cross database updates to work, make sure you have 3.23.28 or later, and use replicate-wild-do-table=db_name.% (Example: replicate-do-db=some_db)
log-slave-updates Tells the slave to log the updates from the slave thread to the binary log. Off by default. You will need to turn it on if you plan to daisy-chain the slaves.
replicate-rewrite-db=from_name->to_name Updates to a database with a different name than the original (Example: replicate-rewrite-db=master_db_name->slave_db_name
skip-slave-start Tells the slave server not to start the slave on the startup. The user can start it later with SLAVE START.
Question 19 :
MySQL - SQL Commands Related to Replication
Replication can be controlled through the SQL interface. Below is the summary of commands:
SLAVE START Starts the slave thread. (Slave)
SLAVE STOP Stops the slave thread. (Slave)
SET SQL_LOG_BIN=0 Disables update logging if the user has process privilege. Ignored otherwise. (Master)
SET SQL_LOG_BIN=1 Re-enables update logging if the user has process privilege. Ignored otherwise. (Master)
SET SQL_SLAVE_SKIP_COUNTER=n Skip the next n events from the master. Only valid when the slave thread is not running, otherwise, gives an error. Useful for recovering from replication glitches.
RESET MASTER Deletes all binary logs listed in the index file, resetting the binlog index file to be empty. In pre-3.23.26 versions, FLUSH MASTER (Master)
RESET SLAVE Makes the slave forget its replication position in the master logs. In pre 3.23.26 versions the command was called FLUSH SLAVE(Slave)
LOAD TABLE tblname FROM MASTER Downloads a copy of the table from master to the slave. (Slave)
CHANGE MASTER TO master_def_list Changes the master parameters to the values specified in master_def_list and restarts the slave thread. master_def_list is a comma-separated list of master_def where master_def is one of the following: MASTER_HOST, MASTER_USER, MASTER_PASSWORD, MASTER_PORT, MASTER_CONNECT_RETRY, MASTER_LOG_FILE, MASTER_LOG_POS. Example:
CHANGE MASTER TO
You only need to specify the values that need to be changed. The values that you omit will stay the same with the exception of when you change the host or the port. In that case, the slave will assume that since you are connecting to a different host or a different port, the master is different. Therefore, the old values of log and position are not applicable anymore, and will automatically be reset to an empty string and 0, respectively (the start values). Note that if you restart the slave, it will remember its last master. If this is not desirable, you should delete the `master.info' file before restarting, and the slave will read its master from my.cnf or the command line. (Slave)
SHOW MASTER STATUS Provides status information on the binlog of the master. (Master)
SHOW SLAVE STATUS Provides status information on essential parameters of the slave thread. (Slave)
SHOW MASTER LOGS Only available starting in Version 3.23.28. Lists the binary logs on the master. You should use this command prior to PURGE MASTER LOGS TO to find out how far you should go.
PURGE MASTER LOGS TO 'logname' Available starting in Version 3.23.28. Deletes all the replication logs that are listed in the log index as being prior to the specified log, and removed them from the log index, so that the given log now becomes first. Example:
PURGE MASTER LOGS TO 'mysql-bin.010'
This command will do nothing and fail with an error if you have an active slave that is currently reading one of the logs you are trying to delete. However, if you have a dormant slave,and happen to purge one of the logs it wants to read, the slave will be unable to replicate once it comes up. The command is safe to run while slaves are replicating - you do not need to stop them. You must first check all the slaves with SHOW SLAVE STATUS to see which log they are on, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log.
Question 20 :
MySQL - Replication FAQ
Why do I sometimes see more than one Binlog_Dump thread on the master after I have restarted the slave?
Binlog_Dump is a continuous process that is handled by the server in the following way:
Catch up on the updates.
Once there are no more updates left, go into pthread_cond_wait(), from which we can be awakened either by an update or a kill.
On wake up, check the reason. If we are not supposed to die, continue the Binlog_dump loop.
If there is some fatal error, such as detecting a dead client, terminate the loop.
So if the slave thread stops on the slave, the corresponding Binlog_Dump thread on the master will not notice it until after at least one update to the master (or a kill), which is needed to wake it up from pthread_cond_wait(). In the meantime, the slave could have opened another connection, which resulted in another Binlog_Dump thread.
The above problem should not be present in Version 3.23.26 and later versions. In Version 3.23.26 we added server-id to each replication server, and now all the old zombie threads are killed on the master when a new replication thread connects from the same slave