MySQL export from command line with query

Very simple.

Always check here:

mysql -u root -p -e "SELECT * FROM employees" my_database > employees.csv
mysql -u posao_statistike -p -e "SELECT d.*, s.grad FROM daljincinecitani d LEFT JOIN sap_maticna_nis_copy_nova_copy s ON d.SER_BR like concat('%',s.fab_broj)" posao_statistike > ~/rezultat.csv

https://www.geeksforgeeks.org/how-to-export-query-result-in-mysql/

SELECT ShortURL, l.LongURL, tag
FROM Links l LEFT JOIN
     Domain d
     ON l.LongURL LIKE concat('%', d.url, '%');
https://stackoverflow.com/questions/42026673/join-mysql-tables-based-on-partial-match-of-strings

Mysql export db and time zone

When exporting mysql db to different server and have datetime or timestamp fields then it is very important what the time zone is the other server is.

Check mysql for time zone. For phpmyadmin go to variables and there check time zone variable and then go to the server where you need to import db and check time zone on the linux server itself.

You need to make sure both time zones are the same.

https://linuxize.com/post/how-to-set-or-change-timezone-on-ubuntu-20-04

Or just add --skip-tz-utc to mysqldump. But this is ok for one db but most likely with backup/restore and tar it’s going to be problem. So just use time zone on linux server and restart mysql/mariadb.

https://stackoverflow.com/questions/14886427/mysql-data-export-changes-times

S3 copy

You cannot copy from remote to remote. You need to first copy locally and then copy to remote, or vice versa.

If you need to copy from local to s3 you need to setup public ACL then or add policy to allow public access to uploaded files.

https://gcore.com/docs/storage/manage-object-storage/configure-aws-sli-s3cmd-and-aws-javascript-sdk/configure-access-control-on-s3-storage-with-aws-cli-and-s3cmd

https://s3tools.org/s3cmd-sync

## You may choose to remove --recursive if is required only for the bucket or folder and not for objects within.
s3cmd setacl --acl-private --recursive s3://mybucket-name
s3cmd setacl --acl-private --recursive s3://mybucket-name/folder-name
s3cmd setacl --acl-private --recursive s3://mybucket-name/folder-name/object-name
s3cmd setacl --acl-public --recursive s3://mybucket-name
s3cmd setacl --acl-public --recursive s3://mybucket-name/folder-name
s3cmd setacl --acl-public --recursive s3://mybucket-name/folder-name/object-name

For example:

s3cmd sync --skip-existing . s3://sonic/rs/ -c ~/.s3cfg-digitalocean
s3cmd info s3://sonic/ -c ~/.s3cfg-digitalocean
s3cmd setpolicy ~/polisa.json s3://sonic/ -c ~/.s3cfg-digitalocean
s3cmd setpolicy ~/polisa.json s3://sonic/ -c ~/.s3cfg-digitalocean
s3cmd sync --skip-existing s3://leaflets/rs/ ./

cat ~/polisa.json
{"Id":"PublicSharePolicy","Version":"2012-10-17","Statement":[{"Action":["s3:*"],"Effect":"Allow","Resource":["arn:aws:s3:::sonic","arn:aws:s3:::sonic/*"],"Principal":"*"}]}

Excel: Search two strings in two columns

You cannot compare it with = sign.

You need to use:

For partial recognition:

=ISNUMBER(SEARCH(B1,A1))

For exact recognition:

=EXACT(C2,F2)

Make sure to trim cell before comparing if you are using EXACT function or otherwise you are going to get bad results.

Excel unmerge and copy data to fill missing

  1. Unmerge all cells in column A and B
  2. Select all rows in column A and B from your first row of data to your last row of data.
  3. Press CTRL+G to open the Go To box
  4. Click Special
  5. Choose Blank Cells
  6. In the formula bar enter = then press the Up arrow. You’ll end up with a formula like =A3. DO NOT PRESS ENTER
  7. Press CTRL+ENTER and this will populate your formula to all selected cells and simulate a “fill down”.
  8. Copy-paste as values as required.

Taken from https://www.reddit.com/r/excel/comments/10w12bt/comment/j7kjeo7/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

PostgreSQL remote connection

Problem: Cannot connect remotely mysql or postgresql

Solution:

Allow ports 3306 for mysql or 5432 for pgs.

Go to /etc/mysql/mariadb.conf.d/50-server.cnf and change bind-address = 0.0.0.0. Restart mysql pr maria actually.

Go to hestiacp postgresql configuration part. Go and change listen_addresses = ‘*’, remove comment. Restart psg.

Done.