Due to coronavirus my company is forced to scale down and cut costs. Long story short, I went the route of least disruption I set the original database server (that's going to be turned off for good in 2 weeks) as a MASTER replicator, locked all tables for reading, dumped the data which resulted in a 34GB .sql file and unlocked the tables. Afterwards I copied the file over to the other server and ran mysql import through mysql-client using the following code
cat default-start-import.sql newdatabase.sql default-end-import.sql | mysql -uroot -p newdatabase
With the contents of default-start-import.sql and default-end-import.sql being the following
SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;
COMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;
The problem is, this 34GB dump has been running for 2 days and by looking at the processlist and the amount of tables I have, I'd say its at about 50% import. And this supposedly slave server is not doing anything else at the moment. It's idling.
The CPU usage my MySQL is at 9% at worst and memory usage is at 44% at worst. Looking at the queries it takes about 1-2 seconds to insert a row. I have tables with millions of rows. If each row takes a second this import will take years to complete. I've set vm.swappiness to 10 in sysctl to reduce hard drive swapping and the server runs with steady 2.4 load, which is largely - fine. I wouldn't say it's overburdened.
I've disabled slow query log
innodb_flush_method = O_DIRECT I've given innodb a buffer of 20 gigabytes
Is there anything else I could do to speed up the process. This feels like an insane amount of timewaste. It's just 34GB, I'm sure there are organizations that bring up slave servers with hundreds of gigabytes of databases in no time and here I am struggling on 30gb database. How?!?