Differences

This shows you the differences between two versions of the page.

--- mysqlcommands [2019/02/05 16:32] – z0hpvk
+++ mysqlcommands [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 5: / Line 5: @@
 mysql -u root -p football
-select version(), current_date;
+select version(), curdate(), curtime(), now(), current_user;
-select version(), now();
+show databases;
 create database football;
 use football;
-select database();
 show tables;
+select database();
 mysqlshow -u root -p --count football
@@ Line 20: / Line 21: @@
 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. \\
@@ Line 42: / Line 60: @@
 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>