Skip to main content

Master Slave Replication to Automatically Backup Your MySQL Database


We can make backup for some databases by periodically running a kind of dump query, like mysqldump in MySQL. That's the simplest method but it can drain our server resources and it's not suitable for large databases. MySQL comes up with master-slave features that allow you to replicate your database to another location (slave). This mechanism enables MySQL to generate a log file which records any action performed to the database. Then, that action will be run in slave database too.

For example, we have two database servers with IP address 192.168.0.1 (Master) and 192.168.0.2 (Slave).

1) Configure my.cnf in master server

# Master Settings

# locate where the changes record will be stored
log-bin = /var/log/mysql/mysql-bin.log

# set unique ID for master database in master-slave network (up to you)
server-id = 111

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# select database which will be replicated
# by default system will log all databases
binlog-do-db = my_blog
binlog-do-db = my_store
binlog-do-db = my_company

# or you can select database wich will be ignored
binlog-ignore-db = testing

binlog_format = mixed

2) Create replication user in master database server

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3) Restart MySQL server

4) Show master status

SHOW MASTER STATUS;

5) Make initial master databases dump that will be used by the slave (run mysqldump for some databases you want to replicate)

6) Import those dumped databases to slave database

7) Configure my.cnf in slave server

log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
server-id = 222
binlog-do-db = my_blog
binlog-do-db = my_store
binlog-do-db = my_company
binlog_format = mixed

8) Set master user which is used for replication

CHANGE MASTER TO MASTER_HOST='192.168.0.1', 
MASTER_USER='slave_user', MASTER_PASSWORD='password';

9) Start and monitor the slave

START SLAVE;
SHOW SLAVE STATUS;

10) Replication errors can be occured. You can show slave status and looks for Last_Error or Last_SQL_Error. You can skip the error by setting up SQL_SLAVE_SKIP_COUNTER.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE;

11) Change it back to 0 if you don't want to skip any error.

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 0; 
START SLAVE;

Comments

Popular posts from this blog

Rangkaian Sensor Infrared dengan Photo Dioda

Keunggulan photodioda dibandingkan LDR adalah photodioda lebih tidak rentan terhadap noise karena hanya menerima sinar infrared, sedangkan LDR menerima seluruh cahaya yang ada termasuk infrared. Rangkaian yang akan kita gunakan adalah seperti gambar di bawah ini. Pada saat intensitas Infrared yang diterima Photodiode besar maka tahanan Photodiode menjadi kecil, sedangkan jika intensitas Infrared yang diterima Photodiode kecil maka tahanan yang dimiliki photodiode besar. Jika  tahanan photodiode kecil  maka tegangan  V- akan kecil . Misal tahanan photodiode mengecil menjadi 10kOhm. Maka dengan teorema pembagi tegangan: V- = Rrx/(Rrx + R2) x Vcc V- = 10 / (10+10) x Vcc V- = (1/2) x 5 Volt V- = 2.5 Volt Sedangkan jika  tahanan photodiode besar  maka tegangan  V- akan besar  (mendekati nilai Vcc). Misal tahanan photodiode menjadi 150kOhm. Maka dengan teorema pembagi tegangan: V- = Rrx/(Rrx + R2) x Vcc V- = 150 / (150+10) x Vcc V- = (150/160) x 5

Configuring Swap Memory on Ubuntu Using Ansible

If we maintain a Linux machine with a low memory capacity while we are required to run an application with high memory consumption, enabling swap memory is an option. Ansible can be utilized as a helper tool to automate the creation of swap memory. A swap file can be allocated in the available storage of the machine. The swap file then can be assigned as a swap memory. Firstly, we should prepare the inventory file. The following snippet is an example, you must provide your own configuration. [server] 192.168.1.2 [server:vars] ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa Secondly, we need to prepare the task file that contains not only the tasks but also some variables and connection information. For instance, we set /swapfile  as the name of our swap file. We also set the swap memory size to 2GB and the swappiness level to 60. - hosts: server become: true vars: swap_vars: size: 2G swappiness: 60 For simplicity, we only check the exi

API Gateway Using KrakenD

The increasing demands of users for high-quality web services create the need to integrate various technologies into our application. This will cause the code base to grow larger, making maintenance more difficult over time. A microservices approach offers a solution, where the application is built by combining multiple smaller services, each with a distinct function. For example, one service handles authentication, another manages business functions, another maintains file uploads, and so on. These services communicate and integrate through a common channel. On the client side, users don't need to understand how the application is built or how it functions internally. They simply send a request to a single endpoint, and processes like authentication, caching, or database querying happen seamlessly. This is where an API gateway is effective. It handles user requests and directs them to the appropriate handler. There are several tools available for building an API gateway, su

Deliver SaaS According Twelve-Factor App

If you haven't heard of  the twelve-factor app , it gives us a recommendation or a methodology for developing SaaS or web apps structured into twelve items. The recommendation has some connections with microservice architecture and cloud-native environments which become more popular today. We can learn the details on its website . In this post, we will do a quick review of the twelve points. One Codebase Multiple Deployment We should maintain only one codebase for our application even though the application may be deployed into multiple environments like development, staging, and production. Having multiple codebases will lead to any kinds of complicated issues. Explicitly State Dependencies All the dependencies for running our application should be stated in the project itself. Many programming languages have a kind of file that maintains a list of the dependencies like package.json in Node.js. We should also be aware of the dependencies related to the pla

Beautiful Rain (JDorama)

Saya selalu tertarik dengan film-film inspirasional, baik movie atau pun serial drama. Akhir-akhir ini saya tertarik untuk menonton drama serial jepang. Saya googling dengan keyword "inspirational japan dorama" kemudian saya dapati sejumlah review  beberapa film bagus dari sejumlah netizen.  Salah satu yang kemudian saya tonton adalah Beautiful Rain . Setiap episode film ini selalu membuat saya sangat terharu sampai meneteskan air mata. :' Yah, ini mungkin saja karena saya yang terlalu melankolis. Hahaha. Ini sedikit review dari saya tentang film ini.

Manage Kubernetes Cluster using Rancher

Recently, I sought a simpler method to deploy and maintain Kubernetes clusters across various cloud providers. The goal was to use it for development purposes with the ability to manage the infrastructure and costs effortlessly. After exploring several options, I decided to experiment with Rancher. Rancher offers a comprehensive software stack for teams implementing container technology. It tackles both the operational and security hurdles associated with managing numerous Kubernetes clusters. Additionally, it equips DevOps teams with integrated tools essential for managing containerized workloads. Rancher also offers an open-source version, allowing free deployment within one's infrastructure. The Rancher platform can be deployed either as a Docker container or within a Kubernetes cluster utilizing the K3s engine. We can read the documentation on how to install Rancher on K3s using Helm . Rancher itself enables the creation and provisioning of Kubernetes clusters and