Web

GCP MySQL replica setup

GCP MySQL Replica

CloudSQL is fully managed MySQL / PostgreSQL database system. CloudSQL reduces the workload for DBAs and anyone can easily manage even without a DBA. In many cases, people wants to have a replica of their production database for Testing purpose or even some other purpose. CloudSQL provides the flexibility to have external replicas on VM or On-prem. In this blog we are going to configure external replica for CloudSQL.

MySQL with GTID

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs. — From MySQL Documentation

To understand more about GTID, please refer the below link. MySQL reference

The process overview

  • Create the CloudSQL — Master.
  • Create the Compute engine and setup MySQL — Slave.
  • Enable GTID in the Slave.
  • Take the dump from the master along with the GTID.
  • Restore the dump on the slave.
  • Configure Replication.

Create the CloudSQL

  • Go to the CloudSQL console.
  • Create a new MySQL 2nd generation Instance.
  • In the show advanced configuration enable the below items.
    1. Automatic Backups
    2. Enable Binary logging
  • Lets wait for the Instance creation complete.
  • Refer the below link for GCP Doc to create the CloudSQL Link

Create the Compute Engine VM

  • Go to Compute Engine console and create the VM.
  • Give a name and choose the CPU, Memory, OS, and Networking and etc.
  • It should be in Public and assign a static external IP address.
  • Lets wait for Instance creation complete.
  • More details step for creating the VM, please refer the link.

Install MySQL 5.7 on the Slave

1
2
3
-- This is command for install mysql for ubuntu 18.04
apt-get update
apt-get install mysql-server

Enable GTID

1
2
3
4
5
6
7
8
9
10
11
-- This file location is for Ubuntu 18.04
vi /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 1212
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
replicate-ignore-db = mysql
binlog-format = ROW
expire_logs_days = 1
read_only = ON
log_bin = /var/log/mysql/mysql-bin.log

Setting up the database on Master

  • Connect to the CloudSQL instance.
  • Create a database called sqladmin.
  • Create a table and insert the sample data.
  • Create an user for replication.
1
2
3
4
5
6
7
create database sqladmin;
use sqladmin;
create table test_tbl (numbers int);
insert into test_tbl values (1),(2),(3);
create user 'rep_user'@'%' identified by 'rep_password';
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%';
flush privileges;

Prepare the Secondary

  • Take the dump of the Master database using the below command.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysqldump --databases sqladmin -h CLOUD_SQL_IP -u root -p \ 
    --single-transaction \
    --master-data=1 \
    --routines \
    --triggers \
    --events \
    --flush-privileges \
    --hex-blob \
    --default-character-set=utf8 > sqladmin.sql
  • Restore the Backup on the Slave.

    1
    2
    3
    4
    -- Restore the backup
    mysql -u username -p sqladmin < sqladmin.sql
    -- Sometimes you will end up with an error like ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. Then you need to run the below command on the Slave MySQL.
    RESET MASTER;

Establish the replication

  • Login to the slave and run the below query.
    1
    2
    3
    4
    CHANGE MASTER TO MASTER_HOST='CLOUD_SQL_IP', 
    MASTER_USER='rep_user',
    MASTER_PASSWORD='rep_password',
    MASTER_AUTO_POSITION=1;
  • Then Check the status.
    1
    show slave status\G;

Sometimes you will get the below error.

1
Error 'Operation ALTER USER failed for 'root'@'%'' on query. Default database: ''. Query: 'ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B''

This means the command executed on the Master and slave already has its own password. A deep blog about this issue here.

Fix this error

  • Take a note Retrieved_Gtid_Set from the slave status.
  • Reset the master settings in the slave and purge it GTID.
    1
    2
    3
    reset master;
    set global GTID_PURGED="afee7444-8ff7-11e8-8ed3-42010a800056:8-9093";
    start slave;

Check the replication

  • Insert some data on the CloudSQL.
    1
    insert into sqladmin.test_tbl values (4),(5);
  • Check the data on Slave.
    1
    select * from sqladmin.test_tbl;

References

  1. GCP MySQL
  2. GCP Read replica

Some good reads you may like

  1. React CI/CD
  2. Angular Youtube integration
  3. Angular maps and clusters

p.s. Nayan is a platform that offers high precision services for traffic monitoring and road safety. Check out our website

Share

© 2019 NAYAN All Rights Reserved