MySQL

Create a remote user

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password|caching_sha2_plugin BY 'password';  
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Change host for a user

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';

Show columns statement

SHOW COLUMNS FROM City;

Query for big tables

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "my_table"
ORDER BY (data_length + index_length) DESC;

Disable foreign key checks

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

Enable logging temporarily

SET global log_output = 'FILE';
SET global general_log_file='/var/log/mysql/mysql-all.log';
SET global general_log = 1;

Equivalent statements

LEFT JOIN     LEFT OUTER JOIN
RIGHT JOIN    RIGHT OUTER JOIN
FULL JOIN     FULL OUTER JOIN
INNER JOIN    JOIN

Run script on database, redirect output to log

mysql -u root -p db_name < sql_statement_file.sql >> query.log 2>&1

User defined variables

User defined variables can be assigned a value in one statement and referred to in another statement.

User variables are written as @var_name

For SET, either = or := can be used as the assignment operator.

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL.

One way to set a user-defined variable is by issuing a SET statement

SET @var_name = expr [, @var_name = expr] ...

Also see SELECT ... INTO statement.