Cheat Linux Administrator

DATABASE POSTGRES

1. Login Postgres psql -U user_database -h ip_address_database -d nama_database -p port_database 2. Grant CONNECT to the database: GRANT CONNECT ON DATABASE database_name TO username; 3. Grant USAGE on schema: GRANT USAGE ON SCHEMA schema_name TO username; 4. Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username; 5. Grant all privileges on all tables in the schema: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username; 6. Grant all privileges on all sequences in the schema: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username; 7. Grant all privileges on the database: GRANT ALL PRIVILEGES ON DATABASE database_name TO username; 8. Grant permission to create database: ALTER USER username CREATEDB; 9. Make a user superuser: ALTER USER myuser WITH SUPERUSER; 10. Remove superuser status: ALTER USER username WITH NOSUPERUSER; 11. Pengecekan jumlah user konek Select usename, count(1) as jml FROM pg_stat_activity group by usename order by jml desc; 12. Pengecekan user aktif Select usename, count(1) as jml FROM pg_stat_activity where state not like '%idle%' group by usename order by jml desc; 13. Pengecekan aktivity user postgres SELECT * FROM pg_stat_activity where usename='postgres' ; 14. Kill user aktif SELECT pg_terminate_backend(pid) FROM pg_stat_activity where usename='postgres; 15. Pengecekan query lebih dari 7 menit (bisa disesuaikan interval waktunya) Select * FROM pg_stat_activity WHERE pid <> pg_backend_pid() And state not in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') and usename != 'replicate' And (now() - pg_stat_activity.query_start) > interval '7 minutes' order by query_start; 16. PENGECEKAN QUERY sedang Menjalankan query apa SELECT * FROM pg_stat_activity where usename='postgres' ; 17. Pengecekan DEADLOCK dan BLOCK database select  blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process, blocked_activity.application_name  AS blocked_application, blocking_activity.application_name AS blocking_application FROM    pg_catalog.pg_locks blocked_locks inner JOIN pg_catalog.pg_stat_activity  blocked_activity on  blocked_activity.pid  =   blocked_locks.pid inner JOIN pg_catalog.pg_locks blocking_locks      on  blocking_locks.locktype =   blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid  IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid !=  blocked_locks.pid INNER JOIN pg_catalog.pg_stat_activity  blocking_activity  ON  blocking_activity.pid   =   blocking_locks.pid WHERE   NOT blocked_locks.GRANTED; 18.

DATABASE MYSQL

LINUX

---Network---

nmcli connection show -a nmcli device status nmcli connection modify enp0s8 IPv4.method manual (make static) nmcli connection modify ens18 IPv4.address 10.0.2.27/24 nmcli connection modify ens18 IPv4.gateway 10.0.2.11 nmcli connection modify ens18 IPv4.dns 8.8.8.8

---Compres/Ekstrak---

---Compress--- tar -cvf nama_file.tar file1 file2 file3 tar -zcvf nama.tar.gz nama_folder/file tar -jcf nama.tar.bz2 nama_folder/file zip nama_file.zip file1 file2 file3 zip -r nama_folder.zip folder_name gzip nama_file gzip -9 nama_file  (terrdapat level1-9 | level 1 compress file cepat hasil besar | level 9 proses compress lama hasil compres kecil) gzip -c nama_file > nama_file.gz  (mempertahankan file asli)     ---Ekstrak-- tar -xvf nama_file.tar tar -xzvf nama_file.tar.gz tar -xjvf nama_file.tar.bz2 unzip nama_file.zip gunzip nama_file.gz

---Find---

find /home/yys  -mtime +20 find /home/yys -type f -iname "*.tar.gz" -mtime +4 grep -i 'panda' nama_file.txt   # mencari string dengan kata kunci panda, -i opsi untuk cari string baik kapital atau kecil grep -i 'panda' -A3 -B3 nama_file.txt  #A3 menampilkan 3 baris setelahnya, B3 menampilkan 3 baris sebelumnya grep -i 'panda\|singa\|zebra' nama_file.txt   # mencari string dengan kata kunci panda singa zebra pada file name_file.txt #find kecuali find /var/log/ -type f -iname "messages-*" ! -iname "messages*.gz" #find kecuali dan gzip file dengan  file yang dimodifikasi lebih dari 1hari lalu find /var/log/ -type f -iname "messages-*" ! -iname "messages*.gz" -mtime +1 -exec gzip -v9 {} \; find /home/yys -type f -iname "*csv.gz" -mtime +10 -exec rm {} \; find /home/yys -type f -iname "*csv.gz" -mtime +10 | wc -l find /home/yys -type f -iname "*csv.gz" -mtime +10 | sort -rh find Downloads -type f -mtime +30 -printf "%T@\t%Tc %6k MiB %p\n" | sort -n | cut -f 2- | head -n10 #mengambil pid dengan kata kunci jar pgrep -f .jar #cari dan kill kill -9 $(pgrep -f .jar) #cara search dan edit menggunakan sed, cari 172.27.27.5 dan ganti atau edit dengan 127.0.0.1 sed -i 's|172.27.27.5|127.0.0.1|' /var/lib/pgsql/12/backups/scripts/grant_db_transaction_data.sh

DOCKER/CONTAINER

KUBERNETES