Skip to main content

Fast MySQL Script


Importing large databases sucks. It can take anywhere from as short as 10 min to a few hours. Worse is when it fails mid-import. So here is a faster & more reliable way to import.

Disclaimer:

  • This tutorial is only for macOS users. If you’re on windows, good luck.
  • This script can import a 30gb database in under 40min, tested on Macbook M1.

Prerequisites:

  • Must use MAMP.
  • Must have Homebrew installed.

Install these using Brew

  • brew install pv
  • brew install pigz

What it does, in steps:

  1. 3 arguments: gzipped SQL file, database name, MySQL user (defaults to root)
  2. Disables safety temporarily. Turns off foreign key checks, unique checks, binary logging.
  3. Cranks up InnoDB settings. Bigger buffer pool, less aggressive flushing to disk.
  4. Decompresses & imports. Uses the faster parallel gunzip (pigz) if available, otherwise falls back to regular gunzip. The pv command shows progress.
  5. Restores safety when done.

Placing the fast import script

Download the file from my GitLab

fast_mysql_import.sh



Make sure its placed inside this folder

/users/<your_username_here>

Update the permission of the file

chmod +x ~/fast_mysql_import.sh

Update zshrc, include MAMP path

echo 'export PATH="/Applications/MAMP/Library/bin:$PATH"' >> ~/.zshrc

source ~/.zshrc

Run the script

./fast_mysql_import.sh ~/Desktop/smaplivedb-20260303-0630.sql.gz smap030326 root



Credits to Danish for creating the script.
Labels:
Location: 43800 Dengkil, Selangor, Malaysia

Comments

Post a Comment

Popular posts from this blog

Setup existing IONIC project in local

Setup existing IONIC project in local  Steps: clone git repo install ionic -  npm install -g @ionic/cli masuk folder repo project install npm dependencies -  npm install run ionic project -  ionic serve buka android studio / xcode -  ionic cap open $var  - $var = ‘android’ atau ‘ios’. ada dua je option, replace $var dgn dua option tu sync changes vscode & dkt android studio / xcode -  ionic cap sync good luck Common issues: dependency conflict check https://www.npmpeer.dev/ utk tengok version yg compatible try naikkan/turunkan version dependency yg keluar dkt error. tembak je sampai hilang error g radle issue try upgrade gradle. kalau tak boleh, try remove folder android & build semula e rror cocoapod make sure install xcode make sure install cocoapod error java  home not found utk mac, buka ~/.zshrc & masukkan chang es dkt VSC tak masuk android studio / xcode try ionic cap sync try quit & buka semula cordo va.variables.gradle no...
Post a Comment
Read more

🗑️ Clear storage Mac OS

  🗑️ Clear storage Mac OS 1: Clear system cache: Go to Finder > Go > Go to Folder, then type in "~/Library/Caches" and hit enter. Select all the folders inside the Caches folder and delete them. 2: Clear system logs: Go to Finder > Go > Go to Folder, then type in "/var/log" and hit enter. Select all the files inside the Log folder and delete them. 3: Remove unused language files: Go to Finder > Go > Go to Folder, then type in "/Library/Languages" and hit enter. Delete all the language folders you don't need. 4: Uninstall unused apps: Go to the Applications folder and delete the apps you don't use. 5: Clean up system files: Use a system cleaning tool like CleanMyMac X to scan and remove unnecessary system files. 6: If you have npm installed, clear the caches once in a while with ‘sudo npm cache clean --force’ 7: If you have ionic projects, open the ‘.angular’ folder and delete the ‘cache’ folder inside it.
Post a Comment
Read more

MVC vs HMVC vs HAVC

MVC (Model-View-Controller) The most-common, traditional approach where files are grouped by type . CodeIgniter 3 uses MVC. Characteristics: Centralized folders for all Controllers, Models, and Views. Best For: Small to medium-sized projects. Pros: Very simple to set up. You literally can't mess this up. Cons: Might become serabut as the project grows. Still usable though. Directory Example: HMVC (Hierarchical Model-View-Controller) An evolution of MVC. Further organizes files into modules . Characteristics: Each feature has its own folder containing its own MVC structure. Best For: Medium to large-scale projects. Pros: Much cleaner folder structure. Can easily be moved or reused in other projects. HAVC (Hierarchical Asset-View-Controller) A specialized variation of HMVC, popularized by the Trongate framework. Characteristics: Replaces the "Model" folder with an " Asset " folder. Structure: Everything specific to a module, including models, CSS, Java...
Post a Comment
Read more