===== MySQL Commands =====
<code>
mysql -u root -p
mysql -h ubuntu-vb -u root -p
mysql -u root -p football

select version(), curdate(), curtime(), now(), current_user;

show databases;
create database football;
use football;
show tables;
select database();

mysqlshow -u root -p --count football

show binary logs;
show master status;
flush binary logs;

show status like '%Connections%';
show variables like '%bin%';

show engine innodb status\G
show processlist; <OR> SELECT * FROM information_schema.processlist ORDER BY id;
show open tables;
status;

set persist max_connection = 1000; (Like ALTER SYSTEM scope=BOTH)
set persist_only max_connection = 1000; (Like ALTER SYSTEM scope=SPFILE)
set global slow_query_log = ON; (Like ALTER SYSTEM scope=MEMORY)
set session sql_mode = 'TRADITIONAL'; (Like ALTER SESSION)
</code>

==== Passwords and User Accounts ====
=== Creating Users ===
<code>
CREATE USER 'ian'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'ian'@'%' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON *.* TO 'ian'@'localhost';
GRANT SUPER ON *.* TO 'ian'@'localhost';
</code>
Global variables can be used to manage user passwords ... \\
These can be overridden at the user level. \\
<code>
default_password_lifetime = 120 (Time in days before password expires)
password_history = 5 (Number of passwords that must be different)
password_reuse_interval = 180 (Prohibit reuse of passwords during number of days specified)

alter user 'ian'@'localhost' password expire;
alter user 'ian'@'localhost' password expire never;
alter user 'ian'@'localhost' password expire interval 180 day;

alter user 'ian'@'localhost' password history default;
alter user 'ian'@'localhost' password history 2;

alter user 'ian'@'localhost' password reuse interval 360;

alter user 'ian'@'localhost' account lock;

grant all on football.* to 'ian'@'localhost';

select concat_ws('@',user, host) from mysql.user;
select user, password_expired, password_last_changed, password_lifetime, account_locked from mysql.user;
</code>

=== Password Validation Component ===
<code>
INSTALL COMPONENT 'file://component_validate_password';
SELECT VALIDATE_PASSWORD_STRENGTH('password');
SHOW VARIABLES LIKE 'validate_password%';
</code>
[[https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html]]\\

==== Backups and Recovery ====
<code>
mysqldump -uroot -p --all-databases > mysqldb.sql

drop database football; <OR> mysqladmin -uroot -p drop football
mysqladmin -uroot -p create football
mysql -uroot -p football < mysqldb.sql
</code>