SQL / MySQL Common Commands

This page will soon get a much more updates as I learnt some more syntax commands and queries.
I also use InfluxDB databases of which query syntax and commands are very similar to SQL. No, really they are almost exactly the same, I like it!
Note to self, I may need to rename and reorganize this page to cover more databases systems than just SQL, as I remember that I also work with some MongoDB for my Python coding...

SQL Management Command Lines

Connect to an SQL server:
mysql -h localhost -u USER_NAME -p (PASSWORD_WILL_BE_PROMPTED) Show SQL server status:
show status; Show SQL DB(s):
show databases; Show a DB's tables (Have to select a DB with use DB_NAME; ie: use MYSQL;):
use MYSQL; show tables; Show FIELDS in a table:
describe TABLE_NAME; (i.e: describe USER; ) Show SQL users:
select user from mysql.user; Update a user's password:
update user set authentication_string=PASSWORD("NewPassword") where User='UserName'; Or::
SET authentication_string FOR 'UserName'@'localhost' = PASSWORD('NewPassword'); Create and Manage user:
Source: dev.mysql.com
CREATE USER 'UserName'@'localhost' IDENTIFIED BY 'mypass'; GRANT ALL ON db1.* TO 'UserName'@'localhost'; GRANT SELECT ON db2.invoice TO 'UserName'@'localhost'; ALTER USER 'UserName'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

More useful basics at: freecodecamp.org/news/basic-sql-commands/
I'll try to add them here as I use them, as soon as I have the time.

SQL Dump All Databases for Backup (Export):
mysqldump --all-databases > dump-$( date '+%Y-%m-%d_%H-%M-%S' ).sql -u root -p SQL Import a Backup:
CREATE DATABASE new_database; mysql -u username -p new_database < data-dump.sql Verify an SQL Dump:
head -n 5 data-dump.sql tail -n 5 data-dump.sql Backup with no access to the SQL Server itself (still need to be root):
/etc/init.d/mysql stop mkdir -p /opt/database/backup-$(DATE -I)/ cp -R /var/lib/mysql/* /opt/database/backup-$(DATE -I)/ cd /opt/database/backup-$(DATE -I) tar -czfv * > /opt/mysqlBackup-$(DATE -I).tar.gz /etc/init.d/mysql start