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

...