Dumps export issue

3825553

Dear all this is my first post in oracle community (Apology in advance for any mistake)

i have dumps of around 250 GB which i export from Live production server (assume 1.1.1.1)  after that i copied (scp) to its relevant server (assume 1.1.1.2) which is used for only reporting and after  scp on server B i restored these dumps on that server for update .The whole 250 GB export took around 70-80 mints ( minimum) .

default path for dumps export is u /back (which space is around 1TB) but some time i forgot to move old dumps so i faced "export failed due to space issue". then what i do

1: kill the current process

2: make some space on relevant directory

3: Then restart same from process from step one.

So my questions are:

1: can i export dumps in chunks (  83 GB + 83 GB + 83 GB) if yes then its possible to to restore my server B (1.1.1.2) where i do scp and then restoring ? if yes how?

2: if in current senior , where i have 250 GB and its exporting failed around 200GB due to space issue on that directory , can i resume that from where it stop (if incase i killed that export ), if yes how

3: how to speed up this dumps export and after scp (restoration on server B ) because restoration takes around 2 hrs + .

Dave Stokes-MySQL Community Team-Oracle

1. Personally, i hate cutting files into chunks and then reassemble.  Disk drives are cheap and i would suggest that you invest in some extra space to avoid these problems.  Yes, you can use one of many tools to split and reassemble but you end up spending more time on handling that data than using it.

And could you use MySQL Asyc Replication to copy the data live from one server to the other automatically?

2. Probably not.  Depending on the tool you are using to make backups, it is very hard to tell exactly where the transfer died due to lack of space.  You might be able to track it down and restart from there but that is going to take a lot of time which you probably do not have in abundance.

3. Replication, mysqldbcopy from the MySQL Utilities.  Dumps are notoriously slow and you could turn off transactions while loading but that may cause other problems.

Dave Stokes-MySQL Community Team-Oracle

1. Personally, i hate cutting files into chunks and then reassemble.  Disk drives are cheap and i would suggest that you invest in some extra space to avoid these problems.  Yes, you can use one of many tools to split and reassemble but you end up spending more time on handling that data than using it.

And could you use MySQL Asyc Replication to copy the data live from one server to the other automatically?

2. Probably not.  Depending on the tool you are using to make backups, it is very hard to tell exactly where the transfer died due to lack of space.  You might be able to track it down and restart from there but that is going to take a lot of time which you probably do not have in abundance.

3. Replication, mysqldbcopy from the MySQL Utilities.  Dumps are notoriously slow and you could turn off transactions while loading but that may cause other problems.