Replication is used to copy[Backup] data from one MySQL database server to one or more MySQL database servers.
Master -- The MySQL database server, which is serving data to be copied
Slave -- The MySQL database server, copies data which is served by Master
With MySQL, replication is asynchronous by default. This means slaves do not need to be connected permanently to receive updates from the master. For example, if your slave is switched OFF or not connected with master and you are switching slave ON or connect with Master at a later time, then it will automatically synchronize with the Master.
Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
There are two core types of replication formats
Statement Based Replication (SBR) -- which replicates entire SQL statements. In this, the master writes SQL statements to the binary log. Replication of the master to the slave works by executing that SQL statements on the slave.
Row Based Replication (RBR) -- which replicates only the changed rows. In this, the master writes events to the binary log that indicate how individual table rows are changed. Replication of the master to the slave works by copying the events representing the changes to the table rows to the slave.
You can also use a third variety, Mixed Based Replication (MBR). In this, both statement-based and row-based logging is used. Log will be created depending on which is most appropriate for the change.
Statement-based format was the default in MySQL versions older than 5.7.7. In MySQL 5.7.7 and later, row-based format is the default.
Consider 2 MySQL Servers for replication setup, one is a Master and the other is a Slave.
We are going to configure the Master that it should keep a log of every action performed on it. We are going to configure the Slave server that it should look at the log on the Master and whenever changes happens in log on the Master, it should do the same thing.
First of all, we need to create a user on the Master. This user is going to be used by Slave to create a connection with the Master.
CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password'; GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%'; FLUSH PRIVILEGES;
user_password according to your Username and Password.
my.inf (my.cnf in Linux) file should be edited. Include the following lines in [mysqld] section.
server-id = 1 log-bin = mysql-bin.log binlog-do-db = your_database
The first line is used to assign an ID to this MySQL server.
The second line tells MySQL to start writing a log in the specified log file. In Linux this can be configured like
log-bin = /home/mysql/logs/mysql-bin.log.
If you are starting replication in a MySQL server in which replication has already been used, make sure this directory is empty of all replication logs.
The third line is used to configure the database for which we are going to write log. You should replace
your_database with your database name.
skip-networking has not been enabled and restart the MySQL server(Master)
my.inf file should be edited in Slave also. Include the following lines in [mysqld] section.
server-id = 2 master-host = master_ip_address master-connect-retry = 60 master-user = user_name master-password = user_password replicate-do-db = your_database relay-log = slave-relay.log relay-log-index = slave-relay-log.index
The first line is used to assign an ID to this MySQL server. This ID should be unique.
The second line is the I.P address of the Master server. Change this according to your Master system I.P.
The third line is used to set a retry limit in seconds.
The next two lines tell the username and password to the Slave, by using which it connect the Master.
Next line set the database it needs to replicate.
The last two lines used to assign
relay-log-index file names.
skip-networking has not been enabled and restart the MySQL server(Slave)
Copy Data to Slave
If data is constantly being added to the Master, we will have to prevent all database access on the Master so nothing can be added. This can be achieved by run the following statement in Master.
FLUSH TABLES WITH READ LOCK;
If no data is being added to the server, you can skip the above step.
We are going to take data backup of the Master by using
mysqldump your_database -u root -p > D://Backup/backup.sql;
your_database and backup directory according to your setup. You wll now have a file called
backup.sql in the given location.
If your database not exists in your Slave, create that by executing the following
CREATE DATABASE `your_database`;
Now we have to import backup into Slave MySQL server.
mysql -u root -p your_database <D://Backup/backup.sql --->Change `your_database` and backup directory according to your setup
To start replication, we need to find the log file name and log position in the Master. So, run the following in Master
SHOW MASTER STATUS;
This will give you an output like below
+---------------------+----------+-------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------------+----------+-------------------------------+------------------+ | mysql-bin.000001 | 130 | your_database | | +---------------------+----------+-------------------------------+------------------+
Then run the following in Slave
SLAVE STOP; CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='user_name', MASTER_PASSWORD='user_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=130; SLAVE START;
First we stop the Slave. Then we tell it exactly where to look in the Master log file.
MASTER_LOG_FILE name and
MASTER_LOG_POS, use the values which we got by running
SHOW MASTER STATUS command on the Master.
You should change the I.P of the Master in
MASTER_HOST, and change the user and password accordingly.
The Slave will now be waiting. The status of the Slave can be viewed by run the following
SHOW SLAVE STATUS;
If you previously executed
FLUSH TABLES WITH READ LOCK in Master, release the tables from lock by run the following
Now the Master keep a log for every action performed on it and the Slave server look at the log on the Master. Whenever changes happens in log on the Master, Slave replicate that.
Whenever there is an error while running a query on the slave, MySQL stop replication automatically to identify the problem and fix it. This mainly because an event caused a duplicate key or a row was not found and it cannot be updated or deleted. You can skip such errors, even if this is not recommended
To skip just one query that is hanging the slave, use the following syntax
SET GLOBAL sql_slave_skip_counter = N;
This statement skips the next N events from the master. This statement is valid only when the slave threads are not running. Otherwise, it produces an error.
STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE;
In some cases this is fine. But if the statement is part of a multi-statement transaction, it becomes more complex, because skipping the error producing statement will cause the whole transaction to be skipped.
If you want to skip more queries which producing same error code and if you are sure that skipping those errors will not bring your slave inconsistent and you want to skip them all, you would add a line to skip that error code in your
For example you might want to skip all duplicate errors you might be getting
1062 | Error 'Duplicate entry 'xyz' for key 1' on query
Then add the following to your
slave-skip-errors = 1062
You can skip also other type of errors or all error codes, but make sure that skipping those errors will not bring your slave inconsistent. The following are the syntax and examples
slave-skip-errors=[err_code1,err_code2,...|all] slave-skip-errors=1062,1053 slave-skip-errors=all slave-skip-errors=ddl_exist_errors