How VPS - How to use/setup VPS
  • Home
  • Management guides
    • Web servers software
      • Directadmin
      • Hocvps Script
      • Centmin Mod
      • CWP
      • Kloxo-MR
      • Plesk
    • Control Panels
    • Securing VPS/Servers
      • SSL Certificates
      • Upgrading
      • Authentication
  • Operating System
    • CentOS
    • Fedora
    • Debian
    • Linux
    • Arch
    • BSD
    • CoreOS
  • Reviews
  • Coupon
    • Domain Coupon
    • Hosting Coupon
No Result
View All Result
  • Home
  • Management guides
    • Web servers software
      • Directadmin
      • Hocvps Script
      • Centmin Mod
      • CWP
      • Kloxo-MR
      • Plesk
    • Control Panels
    • Securing VPS/Servers
      • SSL Certificates
      • Upgrading
      • Authentication
  • Operating System
    • CentOS
    • Fedora
    • Debian
    • Linux
    • Arch
    • BSD
    • CoreOS
  • Reviews
  • Coupon
    • Domain Coupon
    • Hosting Coupon
No Result
View All Result
How VPS - How to use/setup VPS
No Result
View All Result
Home Operating System Debian

Setup MySQL Master-Slave Replication on Debian/Ubuntu

How VPS by How VPS
November 1, 2019
in Debian
0
0
SHARES
24
VIEWS
Share on FacebookShare on Twitter

Contents

  1. Introduction
  2. Setup the master node
  3. Setup the slave node
  4. Want to contribute?


Introduction

When you are running a critical website, it is important to make sure that you have at least one redundant backup server. This ensures that your database is syncing in real-time. MySQL refers to database syncing as replication. This short tutorial provides instructions on how to setup a master-slave MySQL replication.

Setup the master node

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 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
binlog-do-db = YOUR_DATABASE_TWO
EOF

Restart MySQL server.

/etc/init.d/mysql restart

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;

Now, lock write access to your database:

FLUSH TABLES WITH READ LOCK;

Get master node status:

SHOW MASTER STATUS;

Note: Write down the values of the “File” and “Position” fields as we will need to reference them later for the slave node.

Open another SSH session and dump out your database using following command:

mysqldump -u MYSQL_USERNAME -pMYSQL_PASSWORD --databases YOUR_DATABASE_ONE YOUR_DATABASE_TWO > database.sql

Return to previous SSH session and issue the following command in MySQL console to unlock write access:

UNLOCK TABLES;

Transfer the database.sql created in previous step to slave node.

Setup the slave node

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
binlog-do-db = YOUR_DATABASE_TWO
EOF

Restart MySQL server.

/etc/init.d/mysql restart

Import database.sql created from master node just now by using this command:

mysql -u MYSQL_USERNAME-pMYSQL-PASSWORD < database.sql

Now, lets start the replication. Open MySQL console, run following commands:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='MASTER_SERVER_IP_ADDRESS', MASTER_USER='slave', MASTER_PASSWORD='YOUR_SLAVE_PASSWORD', MASTER_LOG_FILE='FILE_VALUE_FROM_MASTER', MASTER_LOG_POS=POSITION_VALUE_FROM_MASTER;
SLAVE START;

Note: The value for MASTER_LOG_FILE and MASTER_LOG_POS is “File” and “Position” that we wrote down from the master node setup.

Want to contribute?

You could earn up to $300 by adding new articles

Submit your article
Suggest an update
Request an article
How VPS

How VPS

Related Posts

Debian

How to Install WonderCMS on Debian 9

November 1, 2019
Debian

Using MySQL Views on Debian 7

November 1, 2019
Debian

How to Install and Configure TaskBoard on Debian 9

November 1, 2019
Next Post

Setup Squid3 Proxy Server on Debian

How to Install TextPattern CMS 4.6.2 on a Debian 9 LAMP VPS

How to Install BookStack on Debian 9

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Follow Us

  • 121 Followers
  • 87.2k Followers

Recommended

How to Set Time, Timezone and Synchronize System Clock Using timedatectl Command

4 years ago

How to Install Apache on CentOS 7

3 years ago

Setup a Non-root User with Sudo Access on Ubuntu

3 years ago

How to Install Pagekit 1.0 CMS on a Fedora 26 LAMP VPS

3 years ago

Instagram

    Please install/update and activate JNews Instagram plugin.

Categories

  • Arch
  • Authentication
  • Backups
  • BSD
  • Centmin Mod
  • CentOS
  • Control Panels
  • CoreOS
  • CWP
  • Debian
  • Directadmin
  • Encryption
  • Fedora
  • Firewalls
  • Hocvps Script
  • Hosting providers
  • Kloxo-MR
  • Linux
  • Mitigations
  • Operating System
  • Plesk
  • Reviews
  • Securing VPS/Servers
  • Security Patches
  • SSL Certificates
  • Uncategorized
  • Upgrading
  • VPS/Servers management guides
  • Vulnerability Detection
  • Web servers software
  • Webhosting Control Panel

Topics

Apache Web Server Bluehost Review 2019 Bluehost Review 2020 Bluehost Review 2021 Centmin Mod CentminMod centos install htop fsck htop install HTTP DoS attack Install Snort on an Ubuntu install Zabbix on CentOS install Zabbix on CentOS 7 Linux Commands linux guide linux install htop linux vps setup guide MariaDB MariaDB Error Mysql mysqld error optimize MariaDB optimize Mysql snort Ubuntu
No Result
View All Result

Highlights

Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

Webmin Reviews

Virtualmin Reviews

CentOS Web Panel Reviews

Ajenti Reviews

ISPConfig Reviews

Trending

Failed to download metadata for repo 'appstream' on Centos 8
CentOS

How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8

by How VPS
February 25, 2022
0

I tried to update some extensions by use yum on centOs which I specified in Dockerfile. After...

How to Fix MySQL Error "Plugin 'InnoDB' registration as a STORAGE ENGINE failed"?

How to Fix MySQL Error “Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed”?

November 17, 2020
How to optimize Mysql or MariaDB

How to optimize Mysql or MariaDB

November 3, 2020
Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

February 17, 2020
Webmin Reviews

Webmin Reviews

February 17, 2020
How VPS – How to use/setup VPS

We bring you the best Premium WordPress Themes that perfect for news, magazine, personal blog, etc. Visit our landing page to see all features & demos.
LEARN MORE »

Recent News

  • How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8 February 25, 2022
  • How to Fix MySQL Error “Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed”? November 17, 2020
  • How to optimize Mysql or MariaDB November 3, 2020

Categories

  • Arch
  • Authentication
  • Backups
  • BSD
  • Centmin Mod
  • CentOS
  • Control Panels
  • CoreOS
  • CWP
  • Debian
  • Directadmin
  • Encryption
  • Fedora
  • Firewalls
  • Hocvps Script
  • Hosting providers
  • Kloxo-MR
  • Linux
  • Mitigations
  • Operating System
  • Plesk
  • Reviews
  • Securing VPS/Servers
  • Security Patches
  • SSL Certificates
  • Uncategorized
  • Upgrading
  • VPS/Servers management guides
  • Vulnerability Detection
  • Web servers software
  • Webhosting Control Panel

[mc4wp_form]

© 2018 JNews - City News Magazine WordPress theme. All rights belong to their respective owners.
JNews is a top selling 2018 WordPress News, Blog, Newspaper & Magazine Theme.

No Result
View All Result
  • Home

© 2023 JNews - Premium WordPress news & magazine theme by Jegtheme.