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.
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
- 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.
- Go to the CloudSQL console.
- Create a new MySQL 2nd generation Instance.
- In the show advanced configuration enable the below items.
- Automatic Backups
- Enable Binary logging
- Lets wait for the Instance creation complete.
- Refer the below link for GCP Doc to create the CloudSQL Link
- 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.
-- This is command for install mysql for ubuntu 18.04
-- This file location is for Ubuntu 18.04
- Connect to the CloudSQL instance.
- Create a database called sqladmin.
- Create a table and insert the sample data.
- Create an user for replication.
create database sqladmin;
Take the dump of the Master database using the below command.
mysqldump --databases sqladmin -h CLOUD_SQL_IP -u root -p \
--default-character-set=utf8 > sqladmin.sql
Restore the Backup on the Slave.
-- 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.
- Login to the slave and run the below query.
CHANGE MASTER TO MASTER_HOST='CLOUD_SQL_IP',
- Then Check the status.
show slave status\G;
Sometimes you will get the below error.
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.
- Take a note Retrieved_Gtid_Set from the slave status.
- Reset the master settings in the slave and purge it GTID.
set global GTID_PURGED="afee7444-8ff7-11e8-8ed3-42010a800056:8-9093";
- Insert some data on the CloudSQL.
insert into sqladmin.test_tbl values (4),(5);
- Check the data on Slave.
select * from sqladmin.test_tbl;
p.s. Nayan is a platform that offers high precision services for traffic monitoring and road safety. Check out our website