In this experimental scenario , gathering the table growth statistics using query exporter and loading the data into time series Prometheus database . Grafana dashboards shows the growth in minutes basis
Table growth statistics gathered from localhost mysql instance for testing purpose
Launched three separate docker containers ( Query Exporter , Prometheus and PMM Server ) for each components . Added additional Dashboards in PMM Server for testing purpose
Prometheus and Query Exporter Configuration files mapped with localhost volume like below ,
docker run -p 9560:9560/tcp -v "$PWD/config.yaml:/config.yaml" --rm -it adonato/query-exporter:latest -- /config.yaml
docker run -p 9090:9090 -v /tmp/prometheus.yml:/etc/prometheus/prometheus.yml prom/prometheus
It is easy to migrate the MySQL databases from one cloud provider to another cloud provider , but without downtime is little difficult to migrate the 1 TB of data . Using GTID based replication is easy to achieve in few hours of effort
Before go into detail , we will go through explanations of each things,
What is Cloud MySQL ?
Google Cloud Managed service for MySQL Database servers . It is supporting MySQL 5.6 and 5.7 with first and second generation instances . First and Second generation differs with allocating RAM and Storage
What is Percona xtradb cluster ?
Synchronous multi-master replication using percona MySQL server and percona xtrabackup using galera Library . As recommended it should contains odd number of nodes and same data spanned with all nodes
What is multi-source replication ?
MySQL multi-source replication enables a replication slave to receive transactions from multiple immediate masters in parallel
In this scenario , we have to migrate more than 3 GCP Cloud MySQL Managed instances into
Percona Galera Cluster . Before proceed to migrate the data , it is requires to compare MySQL 5.7 and Percona MySQL 5.7
So that we can avoid impact after migrating the data into new server
Instructions follow to setup multi source replication ,
In GCP MySQL Instance , take a user database backup from each instances separately through mysqldump or using export in GCP GUI Console and move to backup files to destination server using gsutil or scp utility
Same Changes needs to edit for additional node of percona cluster
Restore multiple source cloud mysql backup into one percona cluster instance
mysql --user=root --password --host=perconainstanceslave < mysqldump1.sql
mysql --user=root --password --host=perconainstanceslave < mysqldump2.sql
Once restore is completed , we need to add multiple source of GCP Cloud MySQL instances into single percona cluster instance using below command
CHANGE MASTER TO MASTER_HOST="gcpinstance1", MASTER_USER="slaveuser1",MASTER_PASSWORD="slaveuser1", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;
CHANGE MASTER TO MASTER_HOST="gcpinstance2", MASTER_USER="slaveuser2",MASTER_PASSWORD="slaveuser2", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;
One we execute this , we will get below error due to applied transactions exists already in slave server or we will get duplicate transactions issues commonly
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
So,We need to get gtid_purged values from each separate source backups to ignore the deleted transactions in the binary log and troubleshoot above issues
To set global GTID_PURGED values , needs to do reset master in percona galera cluster . If server is in cluster not possible to execute reset master command . Needs to disable wsrep_provider variable in wsrep.conf to execute reset master and restart mysql service in slave server
Interesting , table exist on slave server . But we are getting above error frequently and unable to broken the replication because database size is too big
We are having environment as GTID replication setup with windows server (Master) to Ubuntu Linux machine (Slave) . When we dig into all the findings , concluded its may be with case sensitivity issue . Lower_case_table_names variable value is same on both servers . But as per MySQL documents
Database and table names are not case-sensitive in Windows , but are case-sensitive in most varieties of Unix.Column,index,stored routine, and event names are not case-sensitive on any platform,nor are column aliases
So , decided to tackle the current situation without data loss in slave server . Changing the lower_case_table_names is not best practice and it will corrupt case_sensitive names data files .
we have stopped the slave , executed all the DML and DDL statements in slave server to match the schema deployments with master server .
And followed GTID replication error skipping process to skip the transactions manually handled in slave servers
Sounds interesting from Percona Backup tool for MongoDB !!! I just wants to try and explore the tool with docker on today !!! Docker is first time for me , but in few days docker become favorite one to use for all kind of HA scenario works
Lets move into today's practices and issues ,
Note : Percona Backup for MongoDB supports Percona Server for MongoDB or MongoDB Community Server version 3.6 or higher with MongoDB replication enabled
Step 1 : Launched the Ubuntu 16.04 machine from AWS , then updated the latest packages and installed the docker
sudo apt-get update
sudo apt install docker.io
sudo systemctl start docker
sudo systemctl enable docker
Once docker installed with latest package verify the docker version using docker --version
Step 2 : Installed two docker mongo containers with replica set enabled
We need to login with docker command line and configure and start the replication between mongo servers . login into primary mongo container and execute below commands in mongo shell ,
Wow !!! Its easy too restore sensitive data without any fear !!!
Its really tough sometimes , we restored the sensitive data without knowing and test mails triggered to customer as $100 deducted from your account for purchase . Its strange scenario when we missed to cleansing the customer data in DEV Sandbox !!!
We are running 5 node percona cluster on Ubuntu 16.04, and its configured with master-slave replication. Suddenly we got an alert for replica broken from slave server, which was earlier configured with normal replication
We have tried to sync the data and configure the replication, unable to fix that immediately due to huge transactions and GTID enabled servers. So we have decided to follow with innobackupex tool, and problem fixed in 2 hours
Followed all the steps from percona doc and shared the experience in my environment
Steps involving to repair the broken Replication :
1.Backup master server
2.Prepare the backup
3.Restore and Configure the Replication
4Check Replication Status
1.Backup master server
We need to configure the complete master server database into a slave. So we are taking a full backup from the master server, before proceeding the backup we should check disk space available for the backup because of its system-level backup
We have created a specific user for taking a backup from master server, once the backup is completed will get OK like below,
2. Prepare the backup for Restore
We need to prepare the backups to apply the transaction logs into data files, once it is OK, data files are ready to restore
Before moving the prepared files into slave server, verify the GTID information from xtrabackup_binlog_info
3.Restore and Configure the Replication
We can restore the backup else to create a new data directory and move the files into the directory. Stop mysql service before start restoring
innobackupex --copy-dir /mnt/2019-11-26_09-35-31
Default it will restore the data related files in server datadir , using the server my.cnf configuration file .Once changed the data directory, we need to change the owner and permission of the MySQL data directory
chown mysql:mysql /mnt/mysqldatanew
And restart the service with a new data directory, once its started login with master MySQL root user password. Because we have taken file backup from the master so metadata will be the same of master
Execute below commands to configure the replication
4. Check Replication Status
Once slave is configured, verify the replication status as below