mysql (mariadb) command line notes

login
```
$ mysql -uroot -pmypassword
```
list dbs
```
MariaDB [(none)]> show databases;
```
use a db e.g. mysql for users
```
MariaDB [(none)]> use mysql ;
```
show tables in db
```
MariaDB [shopify]> show tables;
```

look at fields in table

MariaDB [shopify]> describe <table-name>;

view users and host

MariaDB [mysql]> select user,Host,password from user ;

set password in console

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO root@192.168.70.1 IDENTIFIED BY 'mypassword' ;

Example count by email

SELECT LastCreated,Email FROM User 
WHERE Email LIKE "%@tenable.com" 
and LastCreated >= CAST('2023-05-09' as Date) 
ORDER BY LastCreated desc;

Mariadb / Galera cluster

View settings

sudo mysql --defaults-file=/etc/mysql/debian.cnf -e "show variables where VARIABLE_NAME like 'wsrep_cluster%' or VARIABLE_NAME in ('wsrep_sst_method', 'wsrep_on') or VARIABLE_NAME like '%auto_increment%';"
sudo mysql --defaults-file=/etc/mysql/debian.cnf -e "show global status where VARIABLE_NAME in ('wsrep_cluster_size','wsrep_ready','wsrep_connected');"
echo "Done."

...