MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.MySQL is one of the most popular open source relational database management system (RDBMS). When your traffic grows then you need to increase the instances of your mysql server and have to replicate the data on multiple servers. MYSQL provide 2 ways to replicate data, master – master and master – slave.
- Master – Master
In this mode data is written to both the servers, so whenever one server gets the write request it will sync data to other server. This mode will be best suited when your project have more write requests and you want best redundancy. You can send write requests to any server.
- Master – Slave
In this mode only the master server sync data with other servers, so you will have only 1 server which takes the write requests and other servers will be used for read only purpose. This mode will be best suited for projects which have more read requests than write ones and can be used for data backups.
In this tutorial we will learn how to replicate data using Master – Slave configuration.
Advantages of replication in MySQL include:
- Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
- Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
- Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
- Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.
Different methods of replication.
The traditional method is based on replicating events from the master’s binary log, and requires the log files and positions in them to be synchronized between master and slave. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied on the slave.
Different types of synchronization
Replication in MySQL supports different types of synchronization.
asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves.
semisynchronous replication is supported in addition to the built-in asynchronous replication With semisynchronous replication, a commit performed on the master blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction.It also supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time.
synchronous replication all node should be in sync, use NDB Cluster .
There are a number of solutions available for setting up replication between servers, and the best method to use depends on the presence of data and the engine types you are using.
Types of replication format
There are two core types of replication format,Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You can also use a third variety, Mixed Based Replication (MBR).
how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and potential problems and their resolution.
Binary Log File Position Based Replication Configuration
This replication where 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.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. Unless you 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.
Important: You cannot configure the master to log only certain events.
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 records the current position within the binary log, it is possible for slaves to be disconnected, reconnect and then resume processing.
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.
sudo apt-get install mysql-server mysql-client
########Setup the master node########################
To configure a master to use binary log file position based replication, you must enable binary logging and establish a unique server ID. If this has not already been done, a server restart is required.
Binary logging must be enabled on the master because the binary log is the basis for replicating changes from the master to its slaves. If binary logging is not enabled on the master using the
log-bin option, replication is not possible.
Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)−1. How you organize and select the numbers is your choice.
To configure the binary log and server ID options, shut down the MySQL server and edit the
my.ini file. Within the
[mysqld] section of the configuration file, add the
server-id options. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging using a log file name prefix of
mysql-bin, and configure a server ID of 1, use these lines:
[mysqld] log-bin=mysql-bin server-id=1
After making the changes, restart the server.
The following options have an impact on this procedure:
- if you omit
server-id(or set it explicitly to its default value of 0), the master refuses any connections from slaves.
- For the greatest possible durability and consistency in a replication setup using
InnoDBwith transactions, you should use
sync_binlog=1in the master
- Ensure that the
skip-networkingoption is not enabled on your replication master. If networking has been disabled, the slave cannot communicate with the master and replication fails.
————————– setup section Part 1 ———————————–
I have server and client machine.
before making any changes in configuration file take a backup of config file.
/etc/mysql/my.cnf to disable IP binding.
Comment out the following lines:
bind-address = 127.0.0.1 skip-networking
Create new settings for replication by running the following commands:
cat >/etc/mysql/conf.d/replication.cnf <<EOF [mysqld] server-id = 100 log_bin = /var/log/mysql/mysql-bin.log binlog-do-db = YOUR_DATABASE_ONE EOF
Restart MySQL server.
a)Creating a User for Replication:
Each slave connects 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 can choose to create a different account for each slave, or connect to the master using the same account for each slave.
Although you do not have to create an account specifically for replication, you should be aware that the replication user name and password are stored in plain text in the master info repository file or table.Therefore, you may want to create a separate account that has privileges only for the replication process, to minimize the possibility of compromise to other accounts.
————————– setup section Part 2 ———————————–
Create a slave user in MySQL by running following commands in the MySQL console.
CREATE USER 'slave'@'SLAVE_SERVER_IP_ADDRESS' identified by 'YOUR_SLAVE_PASSWORD'; GRANT ALL ON *.* TO 'slave'@'SLAVE_SERVER_IP_ADDRESS'; FLUSH PRIVILEGES;
CREATE USER 'slave'@'192.168.43.2' identified by 'abc@123';
GRANT ALL ON *.* TO 'slave'@'192.168.43.2'; FLUSH PRIVILEGES;
b)Obtaining the Replication Master Binary Log Coordinates:
To configure the slave to start the replication process at the correct point, you need the master’s current coordinates within its binary log.
If the master has been running previously without binary logging enabled, the log file name and position values displayed by
SHOW MASTER STATUS is empty.
If the master has been binary logging previously, use this procedure to obtain the master binary log coordinates:
FLUSH TABLES WITH READ LOCK, which blocks
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:
Now, lock write access to your database:
FLUSH TABLES WITH READ LOCK;
Leave the client from which you issued the
FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.
In a different session on the master, use the
SHOW MASTER STATUS statement to determine the current binary log file name and position:
Get master node status:
————————– setup section Part 3 ———————————–
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | world | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
Note: Write down the values of the “File” and “Position” fields as we will need to reference them later for the slave node.
File column shows the name of the log file and the
Position column shows the position within the file. In this example, the binary log file is
mysql-bin.000002 and the position is 154. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.
You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.
c)Choosing a Method for Data Snapshots:
If the master database contains existing data it is necessary to copy this data to each slave. There are different ways to dump the data from the master database. The following sections describe possible options.
To select the appropriate method of dumping the database, choose between these options:
- Use the mysqldump tool to create a dump of all the databases you want to replicate. This is the recommended method, especially when using
- If your database is stored in binary portable files, you can copy the raw data files to a slave. This can be more efficient than using mysqldump and importing the file on each slave, because it skips the overhead of updating indexes as theINSERT statements are replayed. With storage engines such as InnoDB this is not recommended.
In this example we will use mysqldump
d)Creating a Data Snapshot Using mysqldump
Open another SSH session on master and dump out your database using following command:
————————– setup section Part 4 ———————————–
mysqldump -u MYSQL_USERNAME -pMYSQL_PASSWORD --databases YOUR_DATABASE_ONE > database.sql
Return to previous SSH session and issue the following command in MySQL console to unlock write access:
It is possible to exclude certain databases from the dump using the mysqldump tool. If you want to choose which databases to include in the dump, do not use –all-databases. Choose one of these options:
- Exclude all the tables in the database using –ignore-table option.
- Name only those databases which you want dumped using the –databases option.
########Setup the replication on slave node########################
Each replication slave must have a unique server ID. If this has not already been done, this part of slave setup requires a server restart.
If the slave server ID is not already set, or the current value conflicts with the value that you have chosen for the master server, shut down the slave server and edit the [mysqld] section of the configuration file to specify a unique server ID. For example:
————————setup section for slave part1—————————————
Edit /etc/mysql/my.cnf to disable IP binding.
Comment out the following lines:
bind-address = 127.0.0.1 skip-networking
Create new settings for replication by running following command:
cat > /etc/mysql/conf.d/replication.cnf <<EOF [mysqld] server-id = 101 log_bin = /var/log/mysql/mysql-bin.log binlog-do-db = YOUR_DATABASE_ONE EOF
Restart MySQL server.
Import database.sql created from master node just now by using this command:
mysql -u MYSQL_USERNAME-pMYSQL-PASSWORD < database.sql
After making the changes, restart the server.
If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from any of the other slaves.
If you omit server-id (or set it explicitly to its default value of 0), the slave refuses to connect to a master.
You do not have to enable binary logging on the slave for replication to be set up. However, if you enable binary logging on the slave, you can use the slave’s binary log for data backups and crash recovery, and also use the slave as part of a more complex replication topology. For example, where this slave then acts as a master to other slaves.
a)Setting the Master Configuration on the Slave
To set up the slave to communicate with the master for replication, configure the slave with 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;
Note: The value for MASTER_LOG_FILE and MASTER_LOG_POS is “File” and “Position” that we wrote down from the master node setup.
On slave execute the below command ,it will detailed info.
#mysql -uroot -p<your mysq password> -e 'SHOW SLAVE STATUS \G '
check if you have any lag.
#mysql -uroot -p<your mysq password> -e ‘SHOW SLAVE STATUS \G ‘ | egrep ‘Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master’
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
If the server-id option for the master is not correctly set, slaves cannot connect to it. Similarly, if you have not set theserver-id option correctly for the slave, you get the following error in the slave’s error log:
Warning: You should set server-id to a non-0 value if master_host is set; we will force server id to 2, but this MySQL server will not act as a slave.
The slave stores information about the master you have configured in its master info repository. The master info repository can be in the form of files or a table, as determined by the value set for –master-info-repository. When a slave uses –master-info-repository=FILE, two files are stored in the data directory, named master.info and relay-log.info. If –master-info-repository=TABLE instead, this information is saved in the master_slave_info table in the mysql database. In either case, do not remove or edit the files or table. Always use the CHANGE MASTER TO statement to change replication parameters.