How mysql replication works?
MySQL replication basically consists of three steps which works as follows:
- The master records changes to data in its binary log.
- The slave copies the changes recorded in the master’s binlog to its relay log.
- Then the slave replays the change-set recorded in its relay log, applying these changes to its own data.
On Master:
When a transaction is sent to a MySQL server running with binary logging enabled, the transaction is executed normally, and just prior to completing the transaction the server records the change serially in the binary log. Once this is done, it tells the storage engine that the transaction is ready to be committed.
On Slave:
The slave runs two threads to handle its role as a slave.
Firstly, the slave runs a I/O thread that opens a standard client connection to the master (using the replication user account) and starts a non-SQL BINARYLOG DUMP command, which causes the master to start a Binlog dump thread and allows the slave to read log entries from the master's binary log. The slave compares each entry with the latest entry that it already has on the disk. If it fails to find new entries, it sleeps and waits for the master to send a new entry signal. If there are new entries, it records these in the relay log on the slave.
A second thread, the SQL slave thread, reads from the relay log and replays the queries, which completes the process that started with a query being sent to the master and ends with the query being committed to the slave server database.
Section 1 : Master MySQL server setup for replication
We are doing a fresh installation of mysql server and configuring it as a master server here
1) Install MySQL server
# yum install mysql-server
# yum install mysql
Now start the mysql server
# service mysqld start
The mysql server shall read from the configuration file /etc/my.cnf and initialize the mysql database by populating the directories
- /var/lib/mysql
- /var/log/mysql
2) Configuring mysql server as master by editing the mysql configuration file, /etc/my.cnf with the following entries under mysqld section
[mysqld]
binlog-format=mixed
log_bin=/var/log/mysql/mysql-bin.log
max_binlog_size=200M
expire_logs_days=45
server-id=100
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Let me explain each of the above entries and their relevance
server-id :
Set a Unique ID for the server. By default, server-id is set as 0. If server-id is omitted or set to 0 in the my.cnf file, then master refuses connections from all the slaves.
log-bin :
The master must be told to store a binary log. log-bin will be used to write a log on the desired location. The exact path of the binary log file should be specified.
max_binlog_size :
Binary log files will keep growing and to manage their sizes, this parameter is helpful. Once the size limit is reached, a new binary log file is created. The default maximum size for a binary log is 1G.
expire_logs_days :
Over time, there may be too many log files. It is good practice to automatically delete old binary logs. As per our setting, all binary log files older than 45 days will be deleted automatically. So if the slave server is down for 45 days or more, then the replication shall not happen and we need to start from beginning.The default value for this parameter is 0, which means no automatic removal.
sync_binlog, innodb_flush_log_at_trx_commit :
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, set innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file. However, this will have an impact on performance.
sync_binlog - A value of 1 is the safest choice because in the event of a crash we loose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. The default value of 1 is the value required for ACID compliance.
binlog-format :
MySQL supports two forms of replication: statement-based and row-based replication. There is also a hybrid mode (mixed) that is used by default.
To check which replication mode a MySQL server is running in, SELECT the value of the binlog_format system variable:
mysql> show variables like 'binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
We are setting the replication mode as mixed
3) To make the changes done in /etc/my.cnf effective, restart the mysql server
# service mysqld restart
4) Each slave mysql server that connects to a master mysql server in a replication setup (for the purposes of reading the binary log on the master) must have an account to log in. This account must be granted the dedicated permission REPLICATION SLAVE.
a) Login to the master mysql server
# mysql -u root -p
b) master mysql > CREATE USER 'repl_user'@'%' IDENTIFIED BY 'mydba';
master mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
So we have created a user repl_user with password mydba on the master server. Using this user account, slave server shall login to master server.
c) To update the permissions table, flush the privileges:
master mysql > FLUSH PRIVILEGES;
d) In mysql database, in the table user, an entry like this shall be found
mysql> select user,host from user;
+-----------+-------------+
| user | host |
+-----------+-------------+
| repl_user | % |
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| | mastermysql |
| root | mastermysql |
+-----------+-------------+
Section 1 : Slave MySQL server setup replication
We are doing a fresh installation of mysql server and configuring it as a slave server here
1) Install MySQL server
# yum install mysql-server
# yum install mysql
Now start the mysql server
# service mysqld start
The mysql server shall read from the configuration file /etc/my.cnf and initialize the mysql database by populating the directories
- /var/lib/mysql
- /var/log/mysql
2) Stop the mysql server
# service mysqld stop
3) Edit the [mysqld] section in /etc/my.cnf file to configure as slave server
[mysqld]
server-id=102
master-host=<master host ip>
master-connect-retry=60
master-user=<repl_user_name>
master-password=<password>
relay-log=/var/lib/mysql/slave-relay.log
relay-log-index=/var/lib/mysql/slave-relay-log.index
#In case, if we are enabling binary logging for slave(to use the slave as master server later)
log_bin=/var/log/mysql/mysql-bin.log
log-slave-updates = 1
read-only = 1
Let me explain the significance of each of the entries
server-id – gives the Slave its unique ID
master-host - tells the Slave the IP address of the Master mysql server for connection.
master-connect-retry - Here we will specify the connection retry interval.
master-user - Specify the user created in master server, through which the slave connects to the Master server for replication
master-password - Specify the password of the replication user mentioned above
This is how a slave server /etc/my.cnf file will look like
#Slave entries
server-id=102
master-host=192.168.1.33
master-connect-retry=60
master-user=repl_user
master-password=mydba
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
Sometimes in future, we may like to use the slave server as master server. In that case, we shall enable binary logging.
log-bin - Path of the binary log
log-slave-updates makes the slave log the replicated events to its binary log ( Use this if binary logging is enabled )
read-only prevents data changes on the slaves, because slave is really usually used for reads
4) Do not start the mysql server for now
Now having setup the MySQL master and slave server, now need to start the replication process.
Section 2 : Setting up the Replication between Master and Slave
On Master
1) Connect to MySQL Master server
# mysql -u root -p
2) First lock the tables in master so that no write activity happens
master mysql> FLUSH TABLES WITH READ LOCK;
3) Leave this shell open and create another mysql client shell, i.e. another session: In this mysql shell run
master mysql> SHOW MASTER STATUS;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 418 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Write down the values for the column FILE and POSITION. You will need them later when setting up the Slave to start replication - mysql-bin.000003, 418
4) On OS command prompt, get a data snapshot:
# mysqldump --all-databases --lock-all-tables >dbdump.db # I used this
Another alternative is to use --master-data option, which automatically appends CHANGE MASTER TO statement required on the slave to start the replication process.
# mysqldump --all-databases --master-data -u root -p --hex-blob > dbdump.db
mysqldump: It is a binary for producing SQL statements from a MySQL database. This means, "take my database and produce a file that contains SQL statements, which would build an identical database if executed"
--all-databases: Backs up all databases on the master.
--hex-blob: If you have BLOBs (binary objects) in your database, this option ensures they are stored in a way that allows them to be imported correctly (that is, stored as hexadecimal characters). Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob.
Transfer this database dump to slave server
5) Release lock and allow the Master to get updates
mysql> unlock tables;and Slave mysql servers
On Slave
1) Start MySQL server with --skip-slave-start option so that replication does not start
# mysqld_safe –-skip-slave-start
2) From another operating system command prompt, import the master database dump
# mysql –u root –p < dbdump.db
3) Stop MySQL server on slave
4) Possible problem
While we imported the master mysql database dump on the slave server, we had imported the system databases from master too into the slave server. So now the slave server mysql root password would be lost and it shall be overwritten with root server mysql password.
In case, if we intend to use a separate root password for slave mysql server, do the following
a) Start MySQL so that it will not ask for password. Also, make sure it does not start replication:
# mysqld_safe –-skip-slave-start –skip-grant-tables
b) Then connect with mysql –u root from os command prompt and issue a command to update root password as follows:
# mysql -u root
slave mysql> use mysql;
slave mysql> update user set password=PASSWORD(‘new-password’) WHERE User = ‘root’;
slave mysql> flush privileges;
c) Stop MySQL Server that you started with skipping slave start and grant tables.
5) Start MySQL Server with skipping Slave Start
# mysqld_safe –-skip-slave-start
6) Set Master server log co-ordinates on the Slave. Slave server has to start the replication from this log co-ordinates.
slave mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.33', MASTER_USER='repl_user', MASTER_PASSWORD='mydba', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=418;
This is how you tell Slave how to connect to Master in order to replicate. Note the log coordinates. These are the coordinates got from step 3, On Master above.
7) Stop the mysql server started by skipping replication on step 5.
8) Start the slave mysql server normally
# service mysqld start
So now master-slave replication has been setup and initiated successfully.
Checking if Master-Slave Replication is working fine
Having started the slave MySQL node, you can log in and issue some commands to make sure that Slave is running OK.
1) On slave mysql prompt, give the following command:
slave mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 43 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 892 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
2) On slave mysql prompt, give the following command
slave mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.33
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 489
Relay_Log_File: slave-relay.000003
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 489
Relay_Log_Space: 473
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
This will display the current status on slave. Check the *_Errno and *_Error columns for errors
3) On slave mysql prompt, give the following command
slave mysql> show status like ‘Slave%’;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
+----------------------------+-------+
3 rows in set (0.00 sec)
Check if the value of Slave_running is ON.
Some general queries
1) How to know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?
Run the following command in mysql shell prompt of SLAVE server
slave mysql> SHOW SLAVE STATUS;
Check the "Seconds_Behind_Master" column in the output from SHOW SLAVE STATUS.
2) How to force the master to block updates until the slave catches up?
a) On the master mysql server, execute these statements:
master mysql> FLUSH TABLES WITH READ LOCK;
master mysql> SHOW MASTER STATUS;
Record the replication coordinates (the current binary log file name and position) from the output of the SHOW statement.
b) On the slave mysql server, issue the following statement, where the arguments to the MASTER_POS_WAIT() function are the replication coordinate values obtained in the previous step:
slave mysql> SELECT MASTER_POS_WAIT('log_name', log_pos);
The SELECT statement blocks until the slave reaches the specified log file and position. At that point, the slave is in synchrony with the master and the statement returns.
c) On the master, issue the following statement to enable the master to begin processing updates again:
master mysql> UNLOCK TABLES;
3) How to start mysql replication process all over again in a fresh manner?
So while learning to setup mysql replication, we would like to start fresh all over again by dropping the mysql databases and tables.
In master and slave server, stop the mysqld service first
# service mysqld stop
In master and server remove the files in the following folders
#rm /var/lib/mysql/*
#rm /var/log/mysql/*
Ensure that [mysqld] section in mysql configuration file, /etc/my.cnf has just the following entries in both the master and slave servers
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Now start the mysql service in both master and slave server
# service mysqld start
This shall initialize the mysql database and populate the directories /var/lib/mysql/ and /var/log/mysql/.
Then proceed as in Section 2
