-1

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.

Damn devil
  • 9
  • 2

2 Answers2

0

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.

Rick James
  • 2,463
  • 1
  • 6
  • 13
0

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

Damn devil
  • 9
  • 2