MySQL

<< Back to wiki homepage

Table of contents:

Memory

Advise an innodb_buffer_pool_size value

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

(Needless to say, you need to know what you're doing)

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";

It'll show which directories you can use to export.

Replication

(This whole thing assumes you enabled log-bin on master)

Prepare master for replication

CREATE USER '${replication_user}'@'${slave_ip}' IDENTIFIED BY '${replication_password}';
GRANT REPLICATION SLAVE ON *.* TO '${replication_user}'@'${slave_ip}';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Transfer data to slave

Using innobackupex since it's faster than mysqldump.

innobackupex --user=${backup_user} --password=${backup_password_if_needed} --slave-info --stream=tar /tmp/ | ssh -C ${slave_ip} "tar xfi - -C /var/lib/mysql"

After this, you need to apply the logs on the slave.

innobackupex --user=${backup_user} --password=${backup_password_if_needed} --apply-log /var/lib/mysql

Then you can unlock the tables on the master.

UNLOCK TABLES;

Now get the log file and position from transferred data (on slave):

cat /var/lib/mysql/xtrabackup_slave_info

Prepare slave for replication

(Get the log file and position from previous step)

CHANGE MASTER TO MASTER_HOST='${master_ip}', MASTER_USER='${replication_user}', MASTER_PASSWORD='${replication_password}', MASTER_LOG_FILE='${master_log_file}', MASTER_LOG_POS=${master_log_pos};
START SLAVE;

If your mysql is new enough, you can use MASTER_AUTO_POSITION=1 instead of MASTER_LOG_FILE and MASTER_LOG_POS.

Check replication status

SHOW SLAVE STATUS \G

If you see Slave_IO_Running: No and Slave_SQL_Running: No, check the error log. If both are Yes, you're good to go.