Join Log in
Score:-1
Damn devil avatar Server

MySQL replcating huge changing data

ph flag
Damn devil

Hi I have a master MySQL server with 950 gb of data and slave has stopped for some reason and there is a difference of 80gb of data. so I started to setup new slave .

Since the data is changing and multiple databases dumping, copying and restoring will take lot of time . So do I have any other option.

24
0 + 0
Score:0
avatar Server
ua flag
Rick James

Do not panic. The size (on disk) of a table (or the entire database) is not likely to be identical between Primary and Replica.

SELECT COUNT(*) FROM tbl will give you the exact number of rows (at the time that query started); run that on both servers. If they are the same (or close), I declare a "false alarm". If different, we need more details to help you.

dba.stackexchange.com would be a better forum for MySQL administration questions.

0 + 0
Score:0
Damn devil avatar Server
ph flag
Damn devil

Finally resolved the issue,

Here are some challenges I faced and resolved.

  1. slave replication stopped (only the master was handling everything R&W)
  2. Master server is private and cannot be accessible to the public( master and slave are in private clouds)
  3. cannot take a dump and restore because the single table was around 300GB (because MySQL stores the tables in /tmp/shm/ before restoring .which in my case was only 80GB)
  4. master-slave servers don't have an active internet connection .( not able to install any packages.

And here's how I resolved it .

1 . tried adding routes to install packages. ( to install NFS -utils and percona xtrabackup )

  1. only slave got an internet connection. master was still blocked .

  2. installed packages in slave ( installed all packages in master manually around 35 packages )

4 created NFS in slave in / directory and mounted to master

5 performed percona xtrabackup and took backup in mounted folder in master .

6 moved the folder to /var/lib/mysql after backup and started mysql .

working fine.

If your data is changing always and large. Always percona xtrabackup should be your first choice .

Below is the doucument i followed for percona setup https://www.percona.com/doc/percona-xtrabackup/2.4/backup_scenarios/full_backup.html

0 + 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.