{"id":2745,"date":"2024-12-17T11:05:00","date_gmt":"2024-12-17T11:05:00","guid":{"rendered":"https:\/\/bynatree.com\/?p=2745"},"modified":"2024-12-17T11:05:00","modified_gmt":"2024-12-17T11:05:00","slug":"mysql-replication-system-on-centos-8-with-mysql-8-0","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2024\/12\/17\/mysql-replication-system-on-centos-8-with-mysql-8-0\/","title":{"rendered":"MySQL Replication System on CentOS 8 with MySQL 8.0"},"content":{"rendered":"<blockquote><p><span style=\"font-weight: 400;\">MySQL replication is a process of copying data changes from a primary server (master) to one or more secondary servers (slaves). This creates a redundant and scalable setup for your database.<\/span><\/p><\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2746\" src=\"https:\/\/divaind.com\/ie1\/wp-content\/uploads\/2024\/12\/Screenshot-2024-12-12-154012.png\" alt=\"\" width=\"1264\" height=\"394\" srcset=\"https:\/\/divaind.com\/ie1\/wp-content\/uploads\/2024\/12\/Screenshot-2024-12-12-154012.png 1264w, https:\/\/divaind.com\/ie1\/wp-content\/uploads\/2024\/12\/Screenshot-2024-12-12-154012-300x94.png 300w, https:\/\/divaind.com\/ie1\/wp-content\/uploads\/2024\/12\/Screenshot-2024-12-12-154012-1024x319.png 1024w, https:\/\/divaind.com\/ie1\/wp-content\/uploads\/2024\/12\/Screenshot-2024-12-12-154012-768x239.png 768w\" sizes=\"auto, (max-width: 1264px) 100vw, 1264px\" \/><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Master Server:<\/b> <b>(192.168.42.128)<\/b><span style=\"font-weight: 400;\"> The main server that holds the original, authoritative copy of the data. All write operations occur on the master.<\/span><\/span><\/li>\n<li><span style=\"color: #000000;\"><b>Slave Server:<\/b> <b>(192.168.42.129)<\/b><span style=\"font-weight: 400;\"> A secondary server that receives updates (replicates) from the master. Slaves are typically used for read-only operations (e.g., reports, analytics) or load balancing read traffic.<\/span><\/span><\/li>\n<\/ul>\n<p><span style=\"color: #000000;\"><b>Replication Process:<\/b><\/span><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Binary Logging:<\/b><span style=\"font-weight: 400;\"> The master server enables binary logging, which records all data modifications (INSERT, UPDATE, DELETE) as binary events in a file called the binary log.<\/span><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Slave Configuration:<\/b><span style=\"font-weight: 400;\"> The slave server is configured to connect to the master and specify replication settings. This includes details like the master&#8217;s hostname, port, username, and password.<\/span><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>I\/O Thread (Slave):<\/b><span style=\"font-weight: 400;\"> The slave runs an I\/O thread that continuously reads binary events from the master&#8217;s binary log.<\/span><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Relay Log (Slave):<\/b><span style=\"font-weight: 400;\"> The slave stores the received binary events in its own local file called the relay log.<\/span><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>SQL Thread (Slave):<\/b><span style=\"font-weight: 400;\"> The slave runs a separate SQL thread that processes the events from the relay log. It applies these changes (CREATE, UPDATE, DELETE) to the slave&#8217;s database, keeping it synchronized with the master.<\/span><\/span><\/li>\n<\/ol>\n<p><span style=\"color: #000000;\"><b>Benefits of Replication:<\/b><\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>High Availability:<\/b><span style=\"font-weight: 400;\"> If the master server fails, a slave can be promoted to become the new master, minimizing downtime.<\/span><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Read Scalability:<\/b><span style=\"font-weight: 400;\"> Slaves can handle read-only traffic, reducing the load on the master server and improving performance.<\/span><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Data Backup and Offloading:<\/b><span style=\"font-weight: 400;\"> Slaves can be used for backups or running long-running queries without impacting the master&#8217;s performance.<\/span><\/span><\/li>\n<\/ul>\n<p><span style=\"color: #000000;\"><b>Replication Types:<\/b><\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"color: #000000;\"><b>Asynchronous Replication (Default):<\/b><span style=\"font-weight: 400;\"> The most common type. The slave applies changes eventually, potentially lagging slightly behind the master.<\/span><\/span><\/li>\n<li><span style=\"color: #000000;\"><b>Semi-synchronous Replication:<\/b><span style=\"font-weight: 400;\"> The master waits for at least one slave to acknowledge receiving the event before committing the transaction. Offers a balance between availability and consistency.<\/span><\/span><\/li>\n<\/ul>\n<h2><span style=\"font-weight: 400;\">step-by-step process for setting up MySQL replication:<\/span><\/h2>\n<h3><span style=\"font-weight: 400; color: #000000;\">Master Server Configuration:<\/span><\/h3>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><b>Enable Binary Logging:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/h4>\n<\/li>\n<\/ol>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">Edit the master server&#8217;s configuration file <strong>(<\/strong><\/span><strong>my.cnf<\/strong><span style=\"font-weight: 400;\"><strong>).<\/strong> Within the <\/span><strong>[mysqld]<\/strong><span style=\"font-weight: 400;\"> section, add or uncomment the option <\/span><strong>log_bin = mysql-bin<\/strong><\/span><span style=\"font-weight: 400;\"><span style=\"color: #000000;\"> (or a desired filename). This enables binary logging, which records data changes for replication.<\/span><br \/>\n<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">sudo vi \/etc\/my.cnf\nbind-address =192.168.42.128\nserver-id=1\nlog_bin =mysql-bin<\/pre>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><b>Configure Replication User:<\/b><\/h4>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">\u00a0<span style=\"color: #000000;\">Create a dedicated user on the master server with the necessary permissions for replication. Grant this user privileges like <\/span><\/span><span style=\"color: #000000;\"><strong>REPLICATION SLAVE<\/strong><\/span><span style=\"font-weight: 400;\"><span style=\"color: #000000;\"> to allow slave servers to connect and access the binary log.<\/span><br \/>\n<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">sudo mysql -u root -p\nCREATE USER 'replica'@'192.168.42.129' IDENTIFIED BY '*****@123';\nCREATE USER replica'@'192.168.42.129' IDENTIFIED BY 'mypassword';\nGRANT REPLICATION SLAVE ON *.*TO 'replica'@'192.168.42.129';\nFLUSH PRIVILEGES;<\/pre>\n<h3><span style=\"font-weight: 400; color: #000000;\">Slave Server Configuration:<\/span><\/h3>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><b>Edit Slave Configuration:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/h4>\n<\/li>\n<\/ul>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">Edit the slave server&#8217;s configuration file <strong>(<\/strong><\/span><strong>my.cnf<\/strong><span style=\"font-weight: 400;\"><strong>).<\/strong> Similar to the master, ensure binary logging is enabled using <\/span><strong>log_bin<\/strong><span style=\"font-weight: 400;\"> (though it&#8217;s not used for replication on the slave).<\/span><\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">sudo vi \/etc\/my.cnf\nbind-address =192.168.42.129\nserver-id=2\nlog_bin =mysql-bin<\/pre>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><b>Specify Master Information:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/h4>\n<\/li>\n<\/ul>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">Within the <\/span><span style=\"font-weight: 400;\">[mysqld]<\/span><span style=\"font-weight: 400;\"> section, add the following options:<\/span><\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">CHANGE MASTER TO\n     MASTER_HOST='192.168.42.128' ,\n     MASTER_USER='replica' ,\n     MASTER_PASSWORD='P****@123' ,\n     MASTER_LOG_FILE='binlog.0000013' ,\n     MASTER_LOG_POS=156;<\/pre>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><b>Start Slave (Optional):<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/h4>\n<\/li>\n<\/ul>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">You can optionally start the slave SQL thread using the command <\/span><strong>START SLAVE<\/strong><span style=\"font-weight: 400;\"> on the slave server. This will initiate the connection to the<strong> master<\/strong> and begin reading the <strong>binary log<\/strong>.<\/span><\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">sudo systemctl restart mysqld\nmysql -p\nSTOP SLAVE;\nreset slave;\nSTART SLAVE;<\/pre>\n<h3><b>Identify Master Log File and Position:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/h3>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">On the master server, use the command <\/span><strong>SHOW MASTER STATUS<\/strong><span style=\"font-weight: 400;\"> to get the current binary log filename and position. You&#8217;ll need this information for the next step.<\/span><\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">mysql&gt; show master status\\G \n *************************** 1. row ***************************                                                                \n             File: binlog.000013\n         Position: 897\n     Binlog_Do_DB:\n Binlog_Ignore_DB:\nExecuted_Gtid_Set:\n1 row in set (0.01 sec)<\/pre>\n<h3><span style=\"font-weight: 400; color: #000000;\">Verification and Monitoring:<\/span><\/h3>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><span style=\"color: #000000;\"><b>Start Slave SQL Thread:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/span><\/h4>\n<\/li>\n<\/ol>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">Once the slave&#8217;s position is set, use the command <\/span><strong>START SLAVE<\/strong><span style=\"font-weight: 400;\"> to initiate the replication process.<\/span><\/span><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<h4><span style=\"color: #000000;\"><b>Monitor Replication Status:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/span><\/h4>\n<\/li>\n<\/ol>\n<p><span style=\"color: #000000;\"><span style=\"font-weight: 400;\">Use the command <\/span><strong>SHOW SLAVE STATUS<\/strong><span style=\"font-weight: 400;\"> on the slave server to view the current replication status. Look for indicators like <\/span><span style=\"font-weight: 400;\"><strong>Slave_IO_Running<\/strong>: Yes<\/span><span style=\"font-weight: 400;\"> and <\/span><span style=\"font-weight: 400;\">Slave_SQL_Running: Yes<\/span><\/span><span style=\"font-weight: 400;\"><span style=\"color: #000000;\"> to confirm successful replication.<\/span><br \/>\n<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:mysql decode:true \">mysql&gt; SHOW SLAVE STATUS\\G\n*************************** 1. row ***************************\n               Slave_IO_State: Waiting for source to send event\n                  Master_Host: 192.168.42.128\n                  Master_User: replica\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: binlog.000013\n          Read_Master_Log_Pos: 897\n               Relay_Log_File: Slave-relay-bin.000017\n                Relay_Log_Pos: 321\n        Relay_Master_Log_File: binlog.000013\n             Slave_IO_Running: Yes\n            Slave_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 897\n              Relay_Log_Space: 1436\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Master_SSL_Allowed: No\n           Master_SSL_CA_File:\n           Master_SSL_CA_Path:\n              Master_SSL_Cert:\n            Master_SSL_Cipher:\n               Master_SSL_Key:\n        Seconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Master_Server_Id: 1\n                  Master_UUID: a3cb60af-cc36-11ee-bbbf-000c29c89eea\n             Master_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates\n           Master_Retry_Count: 86400\n                  Master_Bind:\n      Last_IO_Error_Timestamp:\n     Last_SQL_Error_Timestamp:\n               Master_SSL_Crl:\n           Master_SSL_Crlpath:\n           Retrieved_Gtid_Set:\n            Executed_Gtid_Set:\n                Auto_Position: 0\n         Replicate_Rewrite_DB:\n                 Channel_Name:\n           Master_TLS_Version:\n       Master_public_key_path:\n        Get_master_public_key: 0\n            Network_Namespace:\n1 row in set, 1 warning (0.00 sec)<\/pre>\n<blockquote><p><b>Conclusion:<\/b><span style=\"font-weight: 400;\"> By following these steps we can create a basic MySQL replication setup to create a redundant and scalable database environment.<\/span><\/p><\/blockquote>\n<p><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL replication is a process of copying data changes from a primary server (master) to one or more secondary servers (slaves). This creates a redundant and scalable setup for your database. Master Server: (192.168.42.128) The main server that holds the original, authoritative copy of the data. All write operations occur on the master. Slave Server:&hellip;<\/p>\n","protected":false},"author":1,"featured_media":2747,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,26],"tags":[35,43,58,60,88,127,202],"class_list":["post-2745","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","category-performance-tuning","tag-databasereplication","tag-mysql8","tag-sysadmin","tag-techsetup","tag-centos","tag-databasemanagement","tag-mysql","category-24","category-26","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2745","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2745"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2745\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2747"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2745"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2745"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2745"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}