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.