#
# MySQL
#

## if can't login to MySQL
change PhpMyAdmin config to
https://stackoverflow.com/questions/5908752/can-anyone-confirm-that-phpmyadmin-allownopassword-works-with-mysql-databases 
by default root password is ""

## login
/usr/bin/mysql -u local -p;

## use table
use table;

## show all tables
show tables;

## import .sql (mysql terminal)
source /var/www/file.sql;

## Clean Mysql Binary Logs
http://www.uptimemadeeasy.com/mysql/using-the-mysql-binary-log/
https://dba.stackexchange.com/questions/72770/disable-mysql-binary-logging-with-log-bin-variable/226944

## MySQL Config file on ubuntu:
nano /etc/mysql/mysql.conf.d/mysql.cnf
nano /etc/mysql/mysql.conf.d/mysqld.cnf

## export .sql
mysqldump -u root -p db_name > /var/www/db_name.sql
mysqldump -u root -p tddb art_lookup > /var/www/art_lookups2.sql

## replace field 
UPDATE table SET column = REPLACE(column, 'Street', 'St');

## show where server saves OUTFILE (Ubuntu: /var/lib/mysql-files/)
SHOW VARIABLES LIKE "secure_file_priv";

## Changing mysql root password for mysql 8.0.25...
https://linuxhint.com/change-mysql-root-password-ubuntu/

## Change Auth Type or Create New User
https://devanswers.co/phpmyadmin-access-denied-for-user-root-localhost/

## Change MySQL auth type for root:
https://thequickblog.com/how-to-change-authentication-method-in-mysql/ 2021-08-16

#
# Queries
#

## Replace string characters:
update product_origs set articleNumberClean=REPLACE( articleNumberClean, ' ', '' ) where id<1000000000
Socket vs port
Socket is a file based communication, and you can't access the socket from another machine. 
On the other hand, ports are open to the world (depends on configuration) and you can access the mysql from other machine using host+port combination.
Also, as much I understand sockets, they are just combination of host+port, just in the file format. So, I don't see any clear benefit in using any of them (as much my knowledge goes).
How to set max execution time in MySQL config? - it is needed for ArdogaParts. Some parts of BO brokes all project.
nano /etc/mysql/my.cnf
# I’m getting error after setting max execution time. Can’t find any good resource on internet on this
# error: mysql: [ERROR] Found option without preceding group in config file

#SET SESSION MAX_EXECUTION_TIME=2000;
#SET GLOBAL MAX_EXECUTION_TIME=2000;

#wait_timeout = 60
#interactive_timeout = 60

Restarting MySQL
sudo /etc/init.d/mysql restart

# Field type for price field
DECIMAL(10, 2)

# Delete records where string is longer than 100 symbols:
DELETE FROM data where length(quoteText)>100;

# To search in table with any letters - a to ą, ą to a and so on. 
COLLATION = utf8_general_mysql500_ci

# Why field!=1 doesn’t gives records with NULL value? Becuase NULL doesn’t attend for numbers and conditioning rules. NULL means nothing, so not comparing to anything.

# If we need to find all results where field!=1
SELECT * FROM users WHERE (status is NULL or status==0)

# Mysql import:
mysql -u username -p database_name < file.sql


# Cool Queries

Count values and echo something
SELECT ARL_SEARCH_NUMBER,COUNT(`ARL_SEARCH_NUMBER`) 
FROM `art_lookup` 
WHERE `ARL_SEARCH_NUMBER` in('07119963151', '06L109259A') 
GROUP BY `ARL_SEARCH_NUMBER`

Ardoga buhalterija detalės per mėnesį su sąskaitos nr.:
SELECT carts.unique_nb, cart_items.code, (FORMAT(cart_items.price/100*115/100*121,2)), carts.name, cart_items.created_at
FROM `cart_items`
INNER JOIN carts
ON carts.id = cart_items.cart_id
where cart_items.created_at like '2021-10%'
ORDER BY `cart_items`.`id` ASC