Collections the commands combinations to manipulate data:
- Insert a string after every N lines
- Output from a file starting from a specific line
- Build many SQL sentence from .csv file
- Convert single column into multiple columns
- Extract a number from a string
- Ignore empty lines
- Combine all rows into a comma-separated single row
- Show progress of load script
- Kill all MySQL process
- Dump users and privileges
- Modify many tables in many databases
This example add SELECT SLEEP(0.3); for every 100 lines:
sed '0~100 s/$/\nSELECT SLEEP(0.3);/g' < inputfile.sql > outputfile.sql
cat outputfile.sql | awk '{if (NR>=1000) print}'
With start and end by line number:
cat outputfile.sql | awk '{if (NR>=1000 && NR <1003) print}'
Shows row 1000, 1001 and 1002 only.
With the command sed '1d' demo.csv remove the header on .csv file (the first line), them remove the Carriage Return (CR) with the command tr -d "\r", now has the data clean, you can get the fields for build SQL sentence with awk command and the stdout is save on demo.sql.
sed '1d' demo.csv |\
tr -d "\r" |\
awk -F';' '{ print "UPDATE foo.bar SET code_id = \""$2"\" WHERE id = " $1 ";"}' > demo.sql
With this command, you can convert each row to contain 5 column, and add , for each value.
cat ids.txt | xargs printf -- '%s,\n' | xargs -n 5
cat ids.txt | sed 's/[^0-9]*//g'
cat ids.txt | sed -e /^$/d
cat ids.txt | tr '\n' ','
Maybe do you need remove the last char , from the string, put the last result into variable and use this command for example:
IDS=$(cat ids.txt | tr '\n' ',')
echo "SELECT fk FROM foo WHERE id IN (${ids::-1});"
pv -e -p -t updates.sql | mysql -h 127.0.0.1 -u $MYSQL_USER -p$MYSQL_PASSWORD --batch --silent
The command m is a alias from mysql -h 127.0.0.1 -u $MYSQL_USER -p$MYSQL_PASSWORD.
m -se "SHOW PROCESSLIST" | awk '{print "KILL "$1";"}' | m
For RDS use the follow commands, and in this example kill the all sleep process:
m -se "SHOW PROCESSLIST" | grep Sleep | awk '{print "CALL mysql.rds_kill("$1");"}' | m -f
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -BNe "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" \
| \
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD \
| \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/--\1--/;//{x;p;x;}'
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -Bse "SELECT DISTINCT CONCAT(\"ALTER TABLE \", TABLE_SCHEMA,\".foo ADD created_at datetime NOT NULL;\") AS sql_text FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA LIKE 'site\_%' AND TABLE_NAME = 'foo';"
\ |
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD