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

🗑️ 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

Turning AIs Into Cavemen Is Actually A Good Idea

    Token limits have been the number 1 problem of vibecoding. Aside from high usage, vague prompting and inefficient context management are two key causes that contribute to this problem. Tokens are counted from two things, the user’s input and agent’s output. You can have the best prompt in the world, but if your AI puke a Shakespearean essay for each response, your token will reach its limit faster. Similarly, if your prompt is too long, your token usage will also skyrocket. Prompting Prompting seems easy enough, but the reality is, if you want quality output, the input must be of similar quality. Here are some tips you can try to achieve a better, token-efficient prompt. Stick to English language only Certain languages may use higher tokens. Switching between languages might not be a good idea. Sure you can control your input prompt, but the output may use more tokens. Extremely short input Do not use ‘please’ or ‘thank you’. Get rid of filler words. Straight to the point....
Post a Comment
Read more

Git Revert is Easier Than You Might Think

    Its surprisingly easy and not as complicated as some people might say. You just need to know which one is the 'bad' commit, then revert to one commit before the 'bad' one. In this example, person 'M' accidentally pulled from the dev branch instead of main. So to revert this, just follow these steps: Find the 'bad' commit In this case it's git message have something like 'pulled dev into branch...'. This is the bad commit, do not copy the git hash of this commit. Instead, get the last 'good' commit. Copy the commit hash of the one before the 'bad' one. Paste it somewhere safe, we will get back to this later. Do not revert right now.   Backup your commits after the 'bad' one After pulling from dev, 'M' pushed a few more commits afterwards. If we've already reverted, finding the committed changes would be a bit harder. So now is the best chance to copy all the commit hashes for cherry-picking later.    Rev...
Post a Comment
Read more