The information contained in this chapter are a synthesis of MySQL official documentation. Please read the official documentation to be constantly aligned (links are in the chapter).
More information at http://dev.mysql.com/doc/refman/5.1/en/replication.html
Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-configuration.html
Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as "events" to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.
Once binary logging has been enabled, all statements are recorded in the binary log. Each slave receives a copy of the entire contents of the binary log. It is the responsibility of the slave to decide which statements in the binary log should be executed; you cannot configure the master to log only certain events. If you do not specify otherwise, all events in the master binary log are executed on the slave. If required, you can configure the slave to process only events that apply to particular databases or tables.
Each slave keeps a record of the binary log coordinates: The file name and position within the file that it has read and processed from the master. This means that multiple slaves can be connected to the master and executing different parts of the same binary log. Because the slaves control this process, individual slaves can be connected and disconnected from the server without affecting the master's operation. Also, because each slave remembers the position within the binary log, it is possible for slaves to be disconnected, reconnect and then "catch up" by continuing from the recorded position.
Both the master and each slave must be configured with a unique ID (using the server-id option). In addition, each slave must be configured with information about the master host name, log file name, and position within that file. These details can be controlled from within a MySQL session using the CHANGE MASTER TO statement on the slave. The details are stored within the slave's master.info file.
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterbaseconfig.html
On a replication master, you must enable binary logging and establish a unique server ID.
To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the section "mysqld" on "my.cnf" or "my.ini" file (filename depends on running Operating System).
This file is usually located in "C:\Program Files\MySQL\MySQL Server 5.1\" folder (windows systems).
[mysqld]
log-bin=mysql-bin
server-id=1
After making the changes, restart the server:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin.exe" -u root -p shutdown
-u is used to identify the user which has to perform the shutdown operation
-p is used to ask for the password associated to the given user
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slavebaseconfig.html
On a replication slave, you must establish a unique server ID, by editing the section "mysqld" on "my.cnf" or "my.ini" file (filename depends on running Operating System). This file is usually located in "C:\Program Files\MySQL\MySQL Server 5.1\" folder (windows systems).
[mysqld]
server-id=2
After making the changes, restart the server (look the section above for more details).
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-repuser.html
Each slave must connect to the master using a MySQL user name and password, so there must be a user account on the master that the slave can use to connect. Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. You may wish to create a different account for each slave, or connect to the master using the same account for each slave.
For example, to set up a new user, repl, that can connect for replication from any host within the mydomain.com domain, issue these statements on the master:
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
The domain is used to tell from where the new user repl can interact with MASTER for replication.
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterstatus.html
To configure replication on the slave you must determine the master's current coordinates within its binary log. To obtain the master binary log coordinates, follow these steps:
1. Start a session on the master by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:
mysql> FLUSH TABLES WITH READ LOCK
2. Use the SHOW MASTER STATUS statement to determine the current binary log file name and position:\
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slaveinit.html
To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position;
Using parameters of our example:
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=73;
More information at http://dev.mysql.com/doc/refman/5.1/en/replication-master-sql.html and http://dev.mysql.com/doc/refman/5.1/en/replication-slave-sql.html
Master Server
PURGE BINARY LOGS
RESET MASTER
SET sql_log_bin
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
Slave Servers
CHANGE MASTER TO
LOAD DATA FROM MASTER
LOAD TABLE tbl_name FROM MASTER
MASTER_POS_WAIT()
RESET SLAVE
SET GLOBAL read_only=TRUE/FALSE
SET GLOBAL sql_slave_skip_counter
START SLAVE
STOP SLAVE