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."
...