0

I have a MariaDB database with 1TB of data that I need to delete old records from.

The total deletion numbers will left the database with less than half of it's actual size so i thought that could be simpler if i migrate to a new RDS, using DMS and its filter functionality to copy only the records that i need and also replicate ongoing changes.

The second reason to migrate is because i can't "give back" the free storage space after deletion to reduce costs, so amazon suggests to migrate to a smaller RDS.

Considering this scenario, some doubts emerged.

1 - Its said that DMS does not create all the table objects. Do i have to worry about the auto increment IDs? I mean, DMS will filter out records from the beggining of the sequence while migrating, so the DB will keep incrementing from the last ID when inserting new records?

2 - When exactly do I need to create the constraints, FKs and other non created objects?

3 - Not all tables will have records being filtered. Is it better to separate them in different migration tasks?

4 - I've never migrated anything using this tools. Is there any kind of hidden trap that i need to worry about?

OBS: The migration will be from AND to MariaDB

ronssm
  • 51
  • 1
  • 9

2 Answers2

0

1 - Before migrating I think it is advisable to delete the old records. This will make it easier to migrate. Maybe that will help you ...

 https://pt.stackoverflow.com/questions/139437/remover-registros-antigos-mysql-automaticamente

2 - Do not create restrictions before deleting old records. Because that can make mistakes.

3 - It is good to separate the tables, both to delete the records and to import. And that makes your migration faster in loading. Since it has 1TB.

4 - Since you have never done this kind of migration, I recommend focusing on deleting the records so it is easier to import into your new system.

Good luck! Send news

Paulo Boaventura
  • 1,365
  • 1
  • 9
  • 29
  • I was thinking that i could use the filter functionality from DMS to filter while migrating, so i just migrate the records that i need without needing to worry about deleting them before. – ronssm Jan 06 '20 at 12:32
  • OK, but now I don't understand ... What is your error after your migration? Since you did it. In case you had any mistake. Submit the log. If you had errors, it should be filtering on this new system. And that probably got into conflict in your previous database. Now I find it much more complex that you are filtering the migration. Since you want to remove some data tables ... Anyway ... You should do this in Maria DB since you mentioned that you do not know the new system and then do the clean import. This should considerably decrease the size of your database. Send news – Paulo Boaventura Jan 06 '20 at 14:50
  • Paulo, eu ainda não migrei a base. As minhas dúvidas são justamente para decidir se esse é o melhor caminho. Eu sei que o DMS possui uma funcionalidade que me permite filtrar quais registros vão ser migrados e é justamente isso que está gerando dúvida sobre o comportamento futuro. Já que o DMS não cria todas as constraints e outros objetos na base, será que eu preciso me preocupar com os IDs de auto increment? Ou o DMS vai continuar incrementando os IDs a partir do último? Veja que eu vou migrar apenas uma parte dos registros. – ronssm Jan 06 '20 at 14:55
  • good... I think you should test by parts, yes. To get these answers, no developer will give you the answers. For being your specific case. You have specific rules ... I cannot give you an exact reference as each migration is specific. – Paulo Boaventura Jan 13 '20 at 01:26
0

If you can dump, move, and load all but the biggest table by normal means, then do the following for the biggest for the dump part:

mysqldump ... --where='...' ...

The where clause should be crafted to keep the rows you want to keep.

It sounds like you want to delete half of a big table. Such a DELETE can be quite problematic. By SELECTing the ones to keep, there should be a lot fewer hassles.

Be sure to have innodb_file_per_table=ON in the new system.

Rick James
  • 135,179
  • 13
  • 127
  • 222