MySQL: Difference between revisions
No edit summary |
|||
| (5 intermediate revisions by the same user not shown) | |||
| Line 4: | Line 4: | ||
===Dump ALL Databases=== | ===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= | =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: | |||
<nowiki>SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user;</nowiki> | |||
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=== | ===Show MySQL users, their passwords and hosts=== | ||
SELECT User,Host,authentication_string FROM mysql.user ORDER BY User ASC; | SELECT User,Host,authentication_string FROM mysql.user ORDER BY User ASC, Host ASC; | ||
===Show privileges for a user from a given host=== | ===Show privileges for a user from a given host=== | ||
| Line 17: | Line 47: | ||
===Create a user that can login from localhost=== | ===Create a user that can login from localhost=== | ||
CREATE USER '[newuser]'@'localhost' IDENTIFIED BY 'password'; | CREATE USER '[newuser]'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; | ||
===Create permissions for above user=== | ===Create permissions for above user=== | ||
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