1

I have 6 remote servers that dial phone numbers daily. I have a total record count of over 300 million combined and now I want to procress on them. The table data looks like this using this query

SELECT lead_id,
    entry_date,
    modify_date,
    `status`,
    list_id,
    phone_number,
    called_count
FROM vicidial_list
WHERE list_id = '202202183'
LIMIT 10;

lead_id  entry_date                   modify_date  status    list_id  phone_number  called_count  
-------  -------------------  -------------------  ------  ---------  ------------  --------------
8455687  2022-02-18 12:08:51  2022-02-18 14:08:17  PU      202202183  XXXXXX1775                 3
8455688  2022-02-18 12:08:51  2022-02-18 13:57:20  PU      202202183  XXXXXX0485                 2
8455689  2022-02-18 12:08:51  2022-02-18 13:57:20  NA      202202183  XXXXXX2277                 2
8455690  2022-02-18 12:08:51  2022-02-18 14:53:55  PU      202202183  XXXXXX8044                13
8455691  2022-02-18 12:08:51  2022-02-18 13:57:20  NA      202202183  XXXXXX1802                 2
8455692  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX9687                 1
8455693  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX0603                 1
8455694  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX1667                 1
8455695  2022-02-18 12:08:51  2022-02-18 14:53:02  AA      202202183  XXXXXX8783                13
8455696  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX2902                 1

I need to perform queries on these but the problem is:

  1. I don't want to manipulate data on 6 Remote Master DB's. Becuase this slows down performance of dialing solution during production hours.
  2. I want to be able to perform queries (add columns to default table and update it) on one combined server and not individually on each servers.

I want to be replicate/insert them on Local DB where I want to add two more columns in addititon to existing columns where:

  1. One new column is "Suffix of DB IP+lead_id" e.g. for server W.X.Y.Z, "Z8455687" as first entry.
  2. Another column, which shows count_exported, default is 0.

As I have no experience of DB Administration, I am looking for ways to solve this. I want to replicate these on one local system daily during non production hours? Pleae help me how to do this. Do I use MySQL sheduler or cronjob? And how to go about it. Following is how I want my new table to look, if my remote DB IP suffix are, A,B,C,D,E and F:

srv_id   server_  lead_id  entry_date                   modify_date  status    list_id  phone_number  called_count  called_count  
-------  -------  -------  -------------------  -------------------  ------  ---------  ------------  ------------  --------------
X.Y.Z.A  A8455687  8455687  2022-02-18 12:08:51  2022-02-18 14:08:17  PU      202202183  XXXXXX1775               3              0
X.Y.Z.A  A8455688  8455688  2022-02-18 12:08:51  2022-02-18 13:57:20  PU      202202183  XXXXXX0485               2              0
X.Y.Z.B  B8455689  8455689  2022-02-18 12:08:51  2022-02-18 13:57:20  NA      202202183  XXXXXX2277               2              0
X.Y.Z.B  B8455690  8455690  2022-02-18 12:08:51  2022-02-18 14:53:55  PU      202202183  XXXXXX8044              13              0
X.Y.Z.C  C8455691  8455691  2022-02-18 12:08:51  2022-02-18 13:57:20  NA      202202183  XXXXXX1802               2              0
X.Y.Z.D  D8455692  8455692  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX9687               1              0
X.Y.Z.E  E8455693  8455693  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX0603               1              0
X.Y.Z.F  F8455694  8455694  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX1667               1              0
X.Y.Z.F  F8455695  8455695  2022-02-18 12:08:51  2022-02-18 14:53:02  AA      202202183  XXXXXX8783              13              0
X.Y.Z.D  D8455696  8455696  2022-02-18 12:08:51  2022-02-18 13:15:55  NA      202202183  XXXXXX8783               1              0
Improve this question
3
  • At what frequency do you need the data to stay up to date? What are some example queries you want to run against the local server after replicating the data?
    – J.D.
    Feb 19, 2022 at 0:50
  • @J.D. considering we upload data almost daily, ideally, I would like it with day-1 frequency
    – Aun Zaidi
    Feb 19, 2022 at 0:54
  • Look at the SELECT ... FOR UPDATE ... SKIP LOCKED as a way for each server to get its own records as it needs them. Then populate a new column with the server that processed them.
    – danblack
    Feb 20, 2022 at 3:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.