{"id":96560,"date":"2023-04-10T15:52:06","date_gmt":"2023-04-10T15:52:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96560"},"modified":"2023-08-16T17:51:29","modified_gmt":"2023-08-16T17:51:29","slug":"beginners-guide-to-mysql-replication-part-2-configuring-sources-and-replica-servers","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/beginners-guide-to-mysql-replication-part-2-configuring-sources-and-replica-servers\/","title":{"rendered":"A Beginners Guide To MySQL Replication Part 2: Configuring Source and Replica Servers"},"content":{"rendered":"<p><span class=\"TextRun SCXO198795179 BCX0\" lang=\"EN-US\" xml:lang=\"EN-US\" data-contrast=\"auto\"><span class=\"NormalTextRun SCXO198795179 BCX0\"><p><strong>This article is part of Aisha Bukar's 6 part series: A Beginners Guide to MySQL Replication. The entries include:<\/strong><\/p>\n<ul>\n<li>Part 1: <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/beginners-guide-to-mysql-replication-part-1\/\">Introduction to MySQL Replication<\/a><\/li>\n<li>Part 2: <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/beginners-guide-to-mysql-replication-part-2-configuring-sources-and-replica-servers\/\">Configuring Source and Replicas<\/a><\/li>\n<li>Part 3: <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/a-beginners-guide-to-mysql-replication-part-3-multi-source-replication\/\">Multi-Source Replication<\/a><\/li>\n<li>Part 4: <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/a-beginners-guide-to-mysql-replication-part-4-using-gtid-based-replication\/\">Using GTID Based Replication<\/a><\/li>\n<li>Part 5: <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/a-beginners-guide-to-mysql-replication-part-5-group-replication-2\/\">Group Replication<\/a><\/li>\n<\/ul>\n<\/span><\/span><span class=\"EOP SCXO198795179 BCX0\">\u00a0<\/span>In the first part of this series, we spoke about MySQL Replication, the different types, replication formats, benefits, and downsides, as well as a brief introduction to the requirements needed for setting up MySQL Replication. To ensure we stay on track with our intended topic, it&#8217;s important to have certain prerequisites in place before we begin. While I won&#8217;t be able to provide an in-depth explanation, I recommend conducting research and preparing these environments beforehand.<\/p>\n<p>1. Setup two computers, ideally virtual machines, one for the source server, and the other for the replica server.<\/p>\n<p>2. Install MySQL server software on the virtual machines and make sure they are both running the same version of the operating system and MySQL software.<\/p>\n<p>3. Ensure there is a network connectivity available. This is to help the source and replica servers communicate with each other.<\/p>\n<p>Once these three prerequisites are set, then we can finally dive into setting up our replication servers.<\/p>\n<h2>Configure the source server<\/h2>\n<p>We would be configuring our source server using the binary log file position based replication. This is essential for the replica servers to receive updates to the database, if binary logging isn\u2019t setup, replication becomes unfeasible. In order to set up binary logging in MySQL replication, we need to modify the MySQL configuration file (<code>my.cnf<\/code> or <code>my.ini<\/code>) on the source server to enable binary logging. Here\u2019s what we need to do:<\/p>\n<ul>\n<li><strong>Locate the MySQL configuration file<\/strong>: The location of the configuration file varies depending on the operating system you&#8217;re using. On Linux systems, it is typically located in <code>\/etc\/my.cnf<\/code> or <code>\/etc\/mysql\/my.cnf<\/code>. On Windows systems, it is typically located in the MySQL installation directory under the subdirectory named &#8220;my.ini&#8221;.<\/li>\n<li><strong>Modify the ini file<\/strong>: Add the following lines to the <code>[mysqld]<\/code> section of the configuration file:<\/li>\n<\/ul>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">server-id=1\r\n\r\nlog-bin=mysql-bin<\/pre>\n<p>To identify servers within a replication topology, a server <code>ID<\/code> is assigned. MySQL 8.0 assigns a default <code>server-id<\/code> value of 1, however this value can be changed dynamically and must be a positive integer ranging from 1 to (232)\u22121. You can modify the <code>server-id<\/code> value easily using the following query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SET GLOBAL server_id = your-prefered-integer-value;<\/pre>\n<p>In this article, I will be using the default <code>server-id<\/code> value of 1. The <code>`server-id`<\/code> value should be unique for each server in the replication setup. Also, the <code>`log-bin`<\/code> value sets the name of the binary log file that will be created on the source server. According to the official documentation, it is important to note that the following has an effect on the source:<\/p>\n<p>To achieve the highest level of durability and consistency in a replication configuration that uses InnoDB with transactions, it is recommended to set the following values in the source&#8217;s my.cnf file:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">innodb_flush_log_at_trx_commit=1 <\/pre>\n<p>And:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">sync_binlog=1 <\/pre>\n<p>Verify that the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_skip_networking\">skip_networking<\/a> system variable is disabled on the source. If this variable is enabled, communication between the replica and the source will be impossible, resulting in a replication failure.<\/p>\n<p>For the changes to take effect, restart the MySQL service.<\/p>\n<h2>Configure the replication server<\/h2>\n<p>Remember that in order for our replication to work, each server must have a unique <code>server-id<\/code> value. For example, if you have two servers, one source(or primary) and one replica, you can set <code>`server-id`<\/code> to 1 on the source and 2 on the replica. It is worthy to note that each replica server must be configured with a unique <code>server-id<\/code> that is greater than the source\u2019s <code>server-id<\/code> to which it is connected to. It is possible to also have multiple replica servers, this isn\u2019t a problem as long as you choose a server-id value that does not conflict with existing replicas.<\/p>\n<ul>\n<li>Stop the MySQL service on the replica server.<\/li>\n<li>Create a backup of the MySQL data directory (<code>my.ini<\/code>) on the replica server. This is to ensure that you can recover the database if something goes wrong during the replication process.<\/li>\n<li>Edit the MySQL configuration file (<code>my.cnf<\/code> or <code>my.ini<\/code>) to enable replication and set a unique <code>server-id<\/code>. This can be done by adding the following lines to the configuration file:<\/li>\n<\/ul>\n<pre class=\"lang:none theme:none\">[mysqld]\r\nserver-id= unique_integer_value<\/pre>\n<p>Or by using the MySQL command:<\/p>\n<pre class=\"lang:none theme:none \">SET GLOBAL server_id=unique_integer_value;<\/pre>\n<p>Make sure that the `unique_integer_value` is a unique integer that is different from the server ID of the master server and any other slave servers.<\/p>\n<ul>\n<li>Start the MySQL service on the replica server.<\/li>\n<\/ul>\n<h2>Create a MySQL user with replication privileges on the replica server<\/h2>\n<p>To establish a connection between the source and its replicas, each replica requires a unique MySQL username and password. Hence, it is necessary to create a corresponding user account on the source. You can create a new user account by employing the <code>CREATE USER<\/code> command.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE USER 'replication_user'@'localhost' \r\n                     IDENTIFIED BY 'password';<\/pre>\n<p>Replace `replication_user` with the desired username and `password` with the desired (far stronger) password for the new user.<\/p>\n<p>Additionally, to authorize this account for replication purposes, use the <code>GRANT<\/code> statement to confer the requisite privileges.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';<\/pre>\n<p>Note that in this example, the privilege is granted to a user named <code>`replication_user`<\/code> from any host (represented by <code>`%`<\/code>)<\/p>\n<h2>Get Binary Log Coordinates<\/h2>\n<p>In order for the replica to start the replication process at the accurate time, it is important to get the source\u2019s current coordinates within its binary log. This is to ensure that the source server&#8217;s data is synchronized with the replication servers. To do this, set the &#8216;Flush tables&#8217; option with a read lock and stop executing any commands on the source server.<\/p>\n<pre class=\"lang:none theme:none\">mysql&gt; FLUSH TABLES WITH READ LOCK;<\/pre>\n<p>Note: If you intend to export data without locking your tables, please skip this step.<\/p>\n<p>To determine the current binary log file name and position, use the <code>SHOW MASTER STATUS<\/code> statement on a separate session of the source.<\/p>\n<pre class=\"lang:none theme:none \"> mysql&gt; SHOW MASTER STATUS;<\/pre>\n<p>Then, record the current binary log coordinates (file name) for the source server and position, as they will be necessary when configuring the replication settings later.<\/p>\n<h2>Create snapshots of existing data in the source server<\/h2>\n<p>We are going to copy the existing data from the source server into the replica server. This can be easily done using the <code>mysqldump<\/code> tool, which is used to create a dump of all the databases we want to replicate. It is the most preferred method when using the InnoDB<\/p>\n<pre class=\"lang:none theme:none\">mysqldump --all-databases --master-data &gt; dbdump.db<\/pre>\n<p>This dumps all databases to a file named <code>dbdump<\/code>, and locks the table which might result to a downtime. To avoid this, you can use this command like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">mysqldump --skip-lock-tables --single-transaction --flush-logs \r\n--hex-blob --master-data=2 -A -uroot -p &gt; C:\\dump.sql<\/pre>\n<p>The \u2013master-data statement starts the replication process on the replica server. If you do not want to start the replication process yet, then start the replica with <code>`--skip-slave-start`<\/code><\/p>\n<h2>Use the `CHANGE MASTER TO` statement to configure the replica.<\/h2>\n<p>This statement helps us configure the replica in order to connect to the source server by specifying some information. It includes information such as the source server&#8217;s hostname or IP address, the replication username and password, and the binary log file and position from which replication should start.<\/p>\n<p>For example, the <code>`CHANGE MASTER TO`<\/code> statement might look like this:<\/p>\n<pre class=\"lang:none theme:none\">    CHANGE MASTER TO\r\n      MASTER_HOST='master_hostname',\r\n      MASTER_USER='replication_user',\r\n      MASTER_PASSWORD='password',\r\n      MASTER_LOG_FILE='mysql-bin.000001',\r\n      MASTER_LOG_POS=12345;<\/pre>\n<p>Make sure to replace <code>`master_hostname`<\/code>, <code>`replication_user`<\/code>, <code>`password`<\/code>, <code>`mysql-bin.000001`<\/code>, and <code>`12345`<\/code> with the appropriate values for your setup.<\/p>\n<p>Lastly, start the replication process by executing the <code>`START SLAVE`<\/code> statement on the slave server.<\/p>\n<pre class=\"lang:none theme:none\">mysql&gt; START SLAVE;<\/pre>\n<h2>Test MySQL Replication<\/h2>\n<p>You can test that your MySQL replication is working by executing the following steps:<\/p>\n<p>1. Login to the MySQL on your source server, create a database and insert some data into a table.<\/p>\n<pre class=\"lang:none theme:none\">mysql -u root -p<\/pre>\n<p>Then:<\/p>\n<pre class=\"lang:none theme:none\">mysql&gt; CREATE DATABASE testdb;<\/pre>\n<p>Exit from the source server using the <code>EXIT<\/code> statement:<\/p>\n<pre class=\"lang:none theme:none\">mysql&gt; EXIT;<\/pre>\n<p>2. Login to the replica server and verify the the replication status using the following command:<\/p>\n<pre class=\"lang:none theme:none\">mysql&gt; SHOW SLAVE STATUS\\G<\/pre>\n<p>Look for the <code>`Slave_IO_Running`<\/code> and <code>`Slave_SQL_Running`<\/code> fields in the output to ensure that replication is running without errors.<\/p>\n<p>3. Check for all existing databases in the replica server using the following statement:<\/p>\n<pre class=\"lang:none theme:none\">mysql&gt; SHOW DATABASES;<\/pre>\n<p>The newly created <code>testdb<\/code> should have been replicated and exists on the replica server. If the data is not replicated, check the replication status on the slave server for any error messages or warnings.<\/p>\n<p>Yayy! You just learnt how to replicate servers using MySQL Replication techniques.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u00a0In the first part of this series, we spoke about MySQL Replication, the different types, replication formats, benefits, and downsides, as well as a brief introduction to the requirements needed for setting up MySQL Replication. To ensure we stay on track with our intended topic, it&#8217;s important to have certain prerequisites in place before we&#8230;&hellip;<\/p>\n","protected":false},"author":341597,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,145792],"tags":[],"coauthors":[158988],"class_list":["post-96560","post","type-post","status-publish","format-standard","hentry","category-blogs","category-mysql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341597"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96560"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96560\/revisions"}],"predecessor-version":[{"id":97842,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96560\/revisions\/97842"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96560"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}