MySQL Cheat sheet - much used but not remembered

MySQL Cheat sheet

# create user
CREATE USER 'myuser'@'%' IDENTIFIED BY 'password';

# grant user all rights on database
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;

# create database with UTF8 character set
create database databasename DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

# To dump a Mysql database and gzip it:
mysqldump -uroot -p my_database | gzip > my_database_dump.sql.gz

# To gunzip and install a database:
zcat my_database_dump.sql.gz | mysql -uroot -p my_database

# Copy full database
mysqldump --routines -u USER -pPASSWORD -h HOST --add-drop-table -q --max_allowed_packet=512M --lock-tables=false DATABASE | mysql -u USER -pPASSWORD -h DESTINATION_HOST DATABASE

# Copy full database over ssh and gzip
ssh [email protected] 'mysqldump --routines -u USER -pPASSWORD --add-drop-table -q --max_allowed_packet=512M --lock-tables=false DATABASE | gzip -c' | gunzip | mysql -u USER -pPASSWORD -h DESTINATION_HOST DATABASE

# Copy only routines and triggers
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt DATABASE | mysql -u USER -pPASSWORD -h DESTINATION_HOST DATABASE

# Load data from csv-file
LOAD DATA LOCAL INFILE 'C:\\Users\\crepaint\\desktop\\filename.csv' INTO TABLE DATABASE.TABLE_NAME FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (field1, field2, field3);

# copy table to another table
INSERT INTO DATABASE.TABLE_TO_BE_COPIED_IN (field1, field2, field3)
SELECT field1, field2, field3
FROM DATABASE.TABLE_TO_BE_COPIED_OF

# Clone table
CREATE TABLE new LIKE original;
INSERT INTO new SELECT * FROM original;

# check slow query log
pt-query-digest /var/log/mysql/mysql-slow.log

# search replace: replace 'foo' by 'bar' in the given field
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'foo', 'bar') where instr(FIELD_NAME, 'foo') > 0;

# breadcrumbs from hierarchical data (@r := 5 sets the id of the bottom node)
SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

# Command to counter "General error: 1298 Unknown or incorrect time zone"
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql