MySQL snippets

mysql_snippets.sql

### Queries for replacing strings in MySQL database records
- https://www.webhostface.com/kb/knowledgebase/mysql-search-replace/
UPDATE `wp_options` SET option_value = REPLACE(option_value, 'newdev/', '') WHERE `option_value` LIKE '%newdev/%' LIMIT 1;

- Regular expression
SELECT * FROM tablename WHERE meta_key REGEXP '^wp_([0-9]+)_' limit 10;

###  MySQL dump specific rows, etc
-- Run in bash terminal, not in mysql> cli

-- EXPORT (specific table/rows)
mysqldump -u root -p DB_NAME wpsv_postmeta --where="post_id IN ('3514719','3514721','3514723','3514725','3514727')" > wpsv_postmeta_sample.sql

-- IMPORT
mysql -u root -p DB_NAME < wpsv_postmeta_sample.sql

-- zip
zip test.zip testfile1.txt

-- Overcoming max_allowed_packet error
-- https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html
mysqldump --max_allowed_packet=32MB -u root -p local wp_options > wp_options.sql

### Interval
SELECT count(*)
FROM wp_tablename
WHERE updated >= DATE_SUB(SYSDATE(), INTERVAL 1 DAY)


-- no create table statement (data only)
mysqldump -t -u root -p ...