MySQL Cheatsheet

mysql.md

MySQL Cheatsheet

Export Tables

mysqldump -h 127.0.0.1 -u root --password=root --column-statistics=0 SOURCE-DATABASE TABLE-NAME TABLE-NAME | gzip > EXPORT.sql.gz
gunzip < EXPORT.sql.gz | mysql -h 127.0.0.1 -u root --password=root TARGET-DATABASE

Export Database but ignore some tables

mysqldump -h 127.0.0.1 -u root --password=root --column-statistics=0 --ignore-table=SOURCE-DATABASE.TABLE-NAME --ignore-table=SOURCE-DATABASE.TABLE-NAME SOURCE-DATABASE | gzip > EXPORT.sql.gz
gunzip < EXPORT.sql.gz | mysql -h 127.0.0.1 -u root --password=root TARGET-DATABASE

Create database

mysql -h 127.0.0.1 -u root --password=PASSWORD -e 'CREATE SCHEMA `TARGET-DATABASE` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'

DROP SCHEMA mydatabase;
CREATE SCHEMA mydatabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create user

CREATE USER 'myuser'@'%' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

Log queries to the mysql.general_log table

SET GLOBAL time_zone = 'Europe/Budapest';
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

Import database from gzip

gunzip < EXPORT.sql.gz | mysql -h 127.0.0.1 -u root --password=PASSWORD TARGET-DATABASE
gzip -d -c EXPORT.sql.gz | mysql -h 127.0.0.1 -u root --password=PASSWORD TARGET-DATABASE
zcat EXPORT.sql.gz | mysql -h 127.0.0.1 -u root -p PASSWORD TARGET-DATABASE

gunzip, zcat: Linux
gzip: Linux, Windows
https://serverfault.com/questions/137965/how-do-i-load-a-sql-gz-file-to-my-database-importing

Search for all occurrences of a string in a database

mysqldump --user=USERNAME --password=PASSWORD --no-create-info --extended-insert=FALSE DATABASE | grep -i "SEARCH STRING"

https://stackoverflow.com/questions/562457/search-for-all-occurrences-of-a-string-in-a-mysql-database#562501

Find recently modified tables

select table_schema as database_name,
       table_name,
       update_time
from information_schema.tables tab
where update_time > (current_timestamp() - interval 1 day)
      and table_type = 'BASE TABLE'
      and table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
      -- and table_schema = 'your database name' 
order by update_time desc;

https://dataedo.com/kb/query/mysql/find-recently-modified-tables