The first machine will be called Master while all others will be Slave.
The Master is the main data source. It is on this basis that you will make your INSERT, UPDATE, and DELETE.
The slaves are content to copy all changes to your master, either in the data or the structure. If you add a new table in the master database, the slaves also added
Setting up replication for two servers:
Turning immediately to the replication configuration. As you will see, this is not very complicated, there is only a logical sequence to follow.
I- Master Server Configuration:
The first thing to do is to configure the master server so that it writes to a binary log all transactions performed. To do this, open the MySQL configuration file, /etc/mysql/my.cnf.
Scroll through the file until you find the server-id line. Each server in your replication architecture must have a unique id, 1 is the default. You can assign a different value as you see fit..
Under the server-id line, add the following lines:
Line1 : sets the location on binary log generated by MySQL.
Line2 : specifies that the log files will be deleted after 10 days .
Line3 : the log file can not exceed 1GB weight. When this limit is reached, MySQL moves to the next file.
Line4-5: To add a database to replicate, add binlog_do_db tracking database name.
Line6: If you want to skip a base, iadd binlog_ignore_db ttracking database name.
Now restart your MySQL server to apply the configuration changes.
Creation of the replication user : Connect now to MySQL with root user or any user can create users and with the GRANT permission.
log_bin = /var/log/mysql/mysql-bin.log #line1
expire_logs_days = 10 #line2
max_binlog_size = 1G #line3
binlog_do_db = database #line4
binlog_do_db = database_2 #line5
binlog_ignore_db = database_to_ignore #line6
Line1 : sets the location on binary log generated by MySQL.
Line2 : specifies that the log files will be deleted after 10 days .
Line3 : the log file can not exceed 1GB weight. When this limit is reached, MySQL moves to the next file.
Line4-5: To add a database to replicate, add binlog_do_db tracking database name.
Line6: If you want to skip a base, iadd binlog_ignore_db ttracking database name.
Now restart your MySQL server to apply the configuration changes.
service mysql restart
if you are using systemd run:
systemctl restart mysqld
mysql -u root -p
The following command creates and assigns slave_user user rights for replication. GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Consider replacing password with a password worthy of the name. We will now show the position in the binary log from which the slave will begin replicating. Before the show, we'll locker tables so there are no writing done in the meantime.
USE database;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
You should get the something similar to the following table:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1201 | database | |
+------------------+----------+--------------+------------------+
1 row in set (0.000000000000001 sec)
mysql-bin.000001 : file name.
1207 : the current position.
Now open a new terminal and connect to your Master server. This is necessary because any change in the first tab will result to unlock the tables.
The following command is used to perform a dump of the database. The file we will recover contains all SQL statements needed to recreate the database in the slave .
mysqldump -u root -p --opt database > database.sqlNow go back to your first terminal and unlock the tables.
UNLOCK TABLES;
We have now completed the configuration of the master database.
I- Slave Server Configuration:
Connect to the slave server and open the MySQL shell. We will create the database that you want to replicate.
CREATE DATABASE database;
Now , import database.sql (the easiest way is to transfer the master server to the slave server via scp)
mysql -u root -p database < database.sql
We will modify the file /etc/mysql/my.cnf slave server to make the necessary changes.
As earlier, find the server-id line and assign an id to the server (this must be different from that of the master server).
server-id = 10relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
replicate-do-db = database
replicate-ignore-db = database_to_ignore
You can now restart your MySQL slave server.
service mysql restart
Now that our server is properly configured, we can tell MySQL and begin replicating. For this, we must provide some details about the server and password to connect to it.
CHANGE MASTER TO MASTER_HOST='192.168.75.10', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1201;
This command performs several actions. First, it tells our server will connect to the master server(IP : 192.168.75.10).
Second, it indicates the identifiers to connect to it (slave_user user and password password).
Finally, it tells the slave from where to start and how to use its replication file (replace the values that you got on the master while ago).
Now you can start replication.
START SLAVE;
To verify that everything is working, type the following command to display a summary of the configuration.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1201
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 968023
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: database
Replicate_Ignore_DB: database_to_ignore
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: 1201
Relay_Log_Space: 5622
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.0000000001 sec)
If a problem occurs, you will find the error in the line Last_Error
