mysql

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

Privileges

Manually remove-add superuser

DELETE FROM mysql.user
WHERE  user = 'root'
       AND host = 'localhost';

INSERT INTO mysql.user
SET user = 'root',
    host = 'localhost',
    authentication_string = Password('some_password'),
    Select_priv = 'y',
    ssl_cipher = 'y',
    x509_issuer = 'y',
    x509_subject = 'y',
    Insert_priv = 'y',
    Update_priv = 'y',
    Delete_priv = 'y',
    Create_priv = 'y',
    Drop_priv = 'y',
    Reload_priv = 'y',
    Shutdown_priv = 'y',
    Process_priv = 'y',
    File_priv = 'y',
    Grant_priv = 'y',
    References_priv = 'y',
    Index_priv = 'y',
    Alter_priv = 'y',
    Show_db_priv = 'y',
    Super_priv = 'y',
    Create_tmp_table_priv = 'y',
    Lock_tables_priv = 'y',
    Execute_priv = 'y',
    Repl_slave_priv = 'y',
    Repl_client_priv = 'y',
    Create_view_priv = 'y',
    Show_view_priv = 'y',
    Create_routine_priv = 'y',
    Alter_routine_priv = 'y',
    Create_user_priv = 'y',
    Event_priv = 'y',
    Trigger_priv = 'y',
    Create_tablespace_priv = 'y';

Disable auth

Disable remote listening

Show all users

select User, Host from mysql.user;

Create a user

CREATE USER '${USERNAME}'@'${HOST}' IDENTIFIED BY '${PASSWORD}';

Host is generally 'localhost' but some weird stuff can happen depending on your /etc/hosts file.

Procedures / functions

List saved procedures

SHOW PROCEDURE STATUS where db = 'dbname';

Show definition of a procedure

SHOW CREATE PROCEDURE dbname.procedurename \G

Call a saved procedure

CALL procedure_name;

Exporting data

Export to file

${your select query here} into outfile '/tmp/some_file';

If you get The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, check it with:

SHOW VARIABLES LIKE "secure_file_priv";

MySQL

Memory

Advise an innodb_buffer_pool_size value