antisaWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>mysql mariadb sql}}

====== mysql miscellaneous commands ======
===== Create user and grant rights =====
  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 =====
  SHOW GRANTS FOR 'user'@'localhost';

===== Check mysql table collation =====
  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();

===== Change collation and charset for one table =====

  ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

===== Change collation and charset for tables =====
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:

<code sql>
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';
</code>

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

Or

  mysqladmin -u root -p'OLDPASSWORD' password NEWPASSWORD
===== Select only certain tables from DB =====
  mysql -p -N information_schema -e "select table_name from tables where table_schema = 'dbname' and table_name like 'wp_3_%'" > tables.txt

===== Backup database directly on another server =====

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


====== Tested on ======
  * 

====== See also ======
  * [[wiki:mysqldump_tips|mysqldump tips]]

====== References ======