#format wiki
#language en
= 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 {{{
#!/bin/bash
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."
}}}


...