MySQL: Difference between revisions

From Dave-Wiki
No edit summary
 
(One intermediate revision by the same user not shown)
Line 4: Line 4:


===Dump ALL Databases===
===Dump ALL Databases===
   mysqldump -u root -p --all-databases --single-transaction > all-db_$(date +%F).sql
    
mariadb-dump \
  --all-databases \
  --single-transaction \
  --routines \
  --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  -p \
  | gzip > /mnt/web-data/www-mysql-full_$(date +%Y%m%d%H%M%S).sql.gz


=Users and Permissions=
=Users and Permissions=
Line 22: Line 31:
First, prepare the Grant statements:
First, prepare the Grant statements:


  SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';')
  <nowiki>SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user;</nowiki>
FROM mysql.user;


Then copy and paste those commands back into the mysql CLI to execute them.
Then copy and paste those commands back into the mysql CLI to execute them.

Latest revision as of 14:53, 9 June 2026

mysqldump

Dump a Single Database

 mysqldump -u root -p --databases [db-name] > db_$(date +%F).sql

Dump ALL Databases

mariadb-dump \
 --all-databases \
 --single-transaction \
 --routines \
 --events \
 --hex-blob \
 --default-character-set=utf8mb4 \
 -p \
 | gzip > /mnt/web-data/www-mysql-full_$(date +%Y%m%d%H%M%S).sql.gz

Users and Permissions

MySQL 8.4

Show All Users

SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

Show Grants for One User

SHOW GRANTS FOR 'username'@'host';

Show Grants for All Users

First, prepare the Grant statements:

SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user;

Then copy and paste those commands back into the mysql CLI to execute them.

Older Stuff for Posterity

Show MySQL users, their passwords and hosts

 SELECT User,Host,authentication_string FROM mysql.user ORDER BY User ASC, Host ASC;

Show privileges for a user from a given host

 SHOW GRANTS FOR 'user_name'@'host';

Create a user that can login from localhost

 CREATE USER '[newuser]'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Create permissions for above user

 GRANT ALL PRIVILEGES ON *.* TO '[newuser]'@'localhost';
 GRANT ALL PRIVILEGES ON [database].* TO '[newuser]'@'localhost';

Export a User

tbd