7. Jan 2021 |

9/100 – cleaning wp mysql database

Today I have to admit I did not open IDE somehow for more than just one line of code 😀 Got stuck into analyzing the logs and cleaning one of my website’s database.

More than 1.3M wp_actionscheduler_actions entries created in just 7 days.

All in One SEO plugin (do not recommend!) created around 3 million garbage entries into the WP+WooCommerce database with scheduled actions (mainly by aioseo_sitemap_ping). It is multiple times more than the whole site itself.

For example:

  • wp_actionscheduler_logs table had 1 973 366 entries
  • wp_actionscheduler_actions table had 912 058 entries

I emphasize that these entries are just unwanted garbage and do not create any value. In order to clear it you need to enter the database (through PMA or some other way) and run DELETE FROM table WHERE ...; SQL query. I can assure that deleting millions of entries takes some time from database engine 🙂

The only line I wrote today (except SQL queries) was add_filter( 'aioseo_sitemap_ping', '__return_false' ); to kill the unnecessary scheduled action.

29. Jan 2020 |

WordPressi backupi tegemine (bash script)

Varukoopia tegemine enda WordPressi lehest ei ole nii keeruline kui vÔib tunduda.

Seoses kasvava vajadusega varukoopiate jĂ€rgi tegin endale selgeks, kuidas bash scripti kasutada. Mul olid varasemalt vajalikud read olemas ja sain neid terminalis kasutada aga selline rea kaupa toimetamine muutus tĂŒĂŒtuks. Google otsing andis sellised vastused:

Aga noh, natuke liiga keerulised minu arust. NB! Kui SSH ĂŒhenduse loomine on sulle vÔÔras, siis Zone helpis on hea juhend. Aga, sul tegelikult ei ole SSHd varukoopia tegemiseks vaja. Piisab ka komplektist: notepad + FTP + cron töö (iseteeninduses). Samas, katsetamiseks ja hiljem nt lahtipakkimiseks on siiski SSH mugavam.

📌 NB! See varukoopia tegemise juhend töötab standardse WP installi puhul. Muudel juhtudel (nagu nĂ€iteks see) ma ei ole seda katsetanud.

Scripti sisu

Echo asjad panin tooltipideks, et terminalis vaikus ei oleks terve aja 🙂 Kui andmebaas on suur, siis selle export ja pakkimine vĂ”tab omajagu aega. Eeldus on, et scripti fail on ĂŒhe kausta jagu madalamal kui WP install. Antud nĂ€ites on WP install kaustas /htdocs.

#!/bin/bash
echo "Backupi tegemine algas!"
cd htdocs
echo "Andmebaasi export algas..."
wp-cli db export
echo ""
echo "Andmebaasi export tehtud!"
cd ..
echo "Alustasin pakkimist..."
tar czf "$(date +"%Y%m%d")_veebileht.tar.gz" htdocs/
echo "Pakkimine tehtud!"
rm htdocs/*.sql
echo "Andmebaas kustutatud!"
echo "Backupi tegemine lÔpetatud."

Selle ĂŒlal oleva sisu salvestad nĂ€iteks backup.sh failina, kĂ€ivitad eelpool toodud kĂ€sureaga ja ongi kĂ”ik. Mida mingi rida teeb?

3. rida – liigub kausta, kus on WP install.
5. rida – teeb andmebaasist koopia ja paneb selle WP installi kausta. Zones piisab wp db export, Veebimajutuses on vaja wp-cli db export.
6. rida – prindib tĂŒhja rea terminali 🙂 ilu pĂ€rast.
8. rida – liigub ĂŒhe kausta jagu madalamale
10. rida – teeb tar.gz formaadis koopia WP installi kaustast, kus sees on ka andmebaasi koopia. Faili nimi kujul YYYYMMDD_failinimi.tar.gz. Kui peaksid selle varukoopia tegemise nĂ€iteks cron tööga automatiseerima, siis iga pĂ€ev on erinev faili nimi. Stackoverflowst leiab mingi juhendi, kuidas kellaaeg ka sisse panna soovi korral.
12. rida – kustutab andmebaasi koopia (kĂ”ik *.sql failid WP installi kaustast), sest noh, ei jĂ€ta selliseid asju vedelema.

NB! Zones ei sellist lihtsat varianti .sh kÀivitamiseks nagu pildil nÀidatud. Seal on vaja faili 2. reale lisada ${0%/*} vÔi 3. reale panna tÀielik teekond alates /tmp kaustast, kus script tegelikult kÀivitatakse. Lisaks on kÀsk iseteeninduses selline:

/bin/bash [[$D2ND_A]]/script.sh

ja scripti failile on vaja teha terminalis: chmod +x script.sh (sest script peab olema executable)

Veelkord: See path, mis Zone iseteeninduses mÀÀrad on tĂ€ispikk kataloog, kus script asub. Sinna ei tehta vaikimisi “cd”, see on lihtsalt muutuja, mida kasutada, et nĂ€iteks kui partitsioon peaks muutuma, siis muutujas lĂ€heb partitsioon ise Ă”igeks. Cron kĂ€ivitatakse ~/tmp kataloogis. KĂ€surealt vĂ”ib proovida ise cd ~/tmp ja seal sees /data/…/script.sh kĂ€ivitada. See on siis sama olukord, nagu crontab seda kĂ€ivitaks.

Kuidas .sh faili teha?

Notepadiga teed faili (kui muud tööriista ei ole harjunud kasutama) ja hiljem muuda lihtsalt faili laiend Ă€ra 🙂

Scripti kÀivitamine

KÀsk scripti kÀivitamiseks (koos sisselogimisega) on Zones selline:

ssh virt5713@neti.ee "bash domeenid/www.neti.ee/neti.sh"

Veebimajutuses selline:

ssh -p 1022 vhost57175ssh@pood.eu "bash pood.sh"

.sh faili path sÔltub sellest, kuhu sa parasjagu sisse logimisega satud. Zone.ee ja Veebimajutuse kÀsud on pisut erinevad (aga mÔlemal on iseteeninduses juhendid olemas. See teeb korraga sisselogimise ja scripti kÀivituse. Kui oled juba sisse loginud ja .sh failiga samas kaustas, siis piisab lihtsalt:
bash failinimi.sh

Regulaarse varukoopia tegemiseks (nt ĂŒle pĂ€eva) on hea mĂ”te kasutada regulaartöid ehk cron töid, mida on lihtne seadistada iseteenindusest. See tĂ€hendab, et script pannakse tööle sinu poolt mÀÀratud ajal. Veebimajutuses nĂ€eb see vĂ€lja nĂ€iteks nii.

Kuidas lahti pakkida

Lahti pakkimise osa on mul siin olemas kÀsuridade kaupa. Win10 arvutis mul seda lihtsalt 7zipiga nÀiteks lahti pakkida ei Ônnestunud aga serveris tuleb kenasti nÀiteks sellise kÀsureaga:

tar -xzvf archive.tar.gz dir/

Kuidas kokku pakkida (excludega)

tar -zcvf "$(date +"%Y%m%d")_occo.tar.gz" --exclude=node_modules occo/

NB! Suurte failihulkade lahtipakkimisel ole ettevaatlik. Ma ĂŒkskord pakkisin Magento (~60 tuhat faili) serveri root folderisse lahti muude asjade sekka. TĂŒkk aega oli koristamist 😀

5. Sep 2019 |

Command lines for WordPress backups

Instead of Duplicator you can always use shell.

Pets shared these instructions in Zone slack. I tested and these work differently in Zone and Veebimajutus.

Everything after # (included) is not part of command line but is comment!

In Zone

4 steps to create backup:

wpfolder is the directory where you have your WP installation. For example htdocs

cd wpfolder # move to folder where your WP installation is.
wp db export # create WP database dump.
cd .. # move one folder upwards.
tar czf veeb_2019-09-05.tar.gz wpfolder/ # pack the folder where you now have database dump and wp installation.

It is very important not to keep your database dump and backup publicly available. Please store these offline if possible.

5 steps to import backup

tar xzf veeb_2019-09-05.tar.gz # unpack your backup
cd wpfolder # move to folder where you have your wp files
nano wp-config.php # change db user, pass and host
wp db import databasefilename.sql # imports copy of database to dp defined in wp-config.
wp search-replace "https://oldname" "https://newname" # replace old URLs everywhere with new.

After import you need to log in to your wp-admin and update permalinks (even if you don’t plan to change anything). I suggest you try it before to understand how everything works.

For Veebimajutus you need to know this secret

It used to be sot that instead of wp you had to type wp-cli in command lines if you use Veebimajutus. But not anymore.

Instead of “localhost” in wp-config you need to have IP 127.0.0.1 because 🙂 You can not find this information anywhere.

Moving large files between servers

Instead of dowloading and uploading (in case you need to move huge amounts of files) you can use wget. I basically means you log in to your new server and move to the folder where you would like to have the file. An you type:

wget https://google.com/myhugefile.zip

This way you can transfer gigabytes of files within seconds between servers.