Large DB restore from .sql.gz - jigneshpshah/greycube_helpmanual GitHub Wiki

to restore very large backups, frappe may have issues

check max_allowed_packet and increase to 1GB

mysql -uroot -p -- connect to database

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'; 
SET GLOBAL max_allowed_packet = 1073741824; -- 1GB

restore direct to db, by passing frappe checks

gunzip -c ./sites/large-db.sql.gz | mysql -u root -p db_name

if all else fails

follow this method - https://gist.github.com/vr-greycube/458a424b02eac48a09fb0e0f53717af9

• splits sql.gz into individual tables restores one by one,
• can skip data inserts for usual suspects: e.g tabDeleted Document, tabAccess Log, __global_search , tabEmail Queue

some more hacks to trim db backup

# remove inserts for tabAccess Log
zcat dump.sql.gz | sed '/LOCK TABLES `tabAccess Log` WRITE;/,/UNLOCK TABLES;/d' > cleaned_dump.sql