Home / server / Questions / 1093900
In Process
Damn devil
Asked: 2022-02-17 11:08:50 +0800 CST

MySQL replcating huge changing data

  • 772

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.

mysql replication large-data master-slave

2 Answers

  1. 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

  2. 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