GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on db_name.* TO 'user'@'localhost' IDENTIFIED BY 'password'
FLUSH PRIVILEGES;

SHOW GRANTS FOR 'user'@'localhost';

USE db_name;
SELECT DISTINCT C.collation_name, T.table_name, T.table_schema FROM information_schema.tables AS T, information_schema.`collation_character_set_applicability` AS C WHERE C.collation_name = T.table_collation AND T.table_schema = DATABASE();

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

This outputs list of queries to apply, so pipe it into sql file then run it.

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db' AND TABLE_TYPE = 'BASE TABLE';

Command (delete first row in convert.sql, it contains sql statement)

mysql -p db2 < make_queries_for_utf8_conversion.sql > convert.sql

then run

mysql -p db2 < convert.sql

Change collation and charset for columns:

SELECT CONCAT('ALTER TABLE ', C.TABLE_NAME, ' CHANGE ', C.COLUMN_NAME, ' ', C.COLUMN_NAME, ' ',  C.COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS queries
FROM information_schema.COLUMNS AS C
LEFT JOIN information_schema.TABLES AS T
ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME IS NOT NULL AND C.TABLE_SCHEMA='db' AND T.TABLE_TYPE='BASE TABLE';

USE mysql;
UPDATE user SET password=PASSWORD("pwd01") WHERE user='root';
FLUSH PRIVILEGES;

Or

mysqladmin -u root -p'OLDPASSWORD' password NEWPASSWORD

mysql -p -N information_schema -e "select table_name from tables where table_schema = 'dbname' and table_name like 'wp_3_%'" > tables.txt

mysqldump –extended-insert=FALSE -uroot -pmypwd dbname tablename | pbzip2 -p4 -m1000 -c | ssh root@example.com 'cat > /backup/dbname_backup.sql.bz2'

• mysqldump tips