MySQL: Difference between revisions
(Created page with "=mysqldump= ===Dump a Single Database=== mysqldump -u root -p --databases [db-name] > db_$(date +%F).sql ===Dump ALL Databases=== mysqldump -u root -p --all-databases --single-transaction > all-db_$(date +%F).sql =Users and Permissions= ===Show MySQL users, their passwords and hosts=== SELECT user,host,password FROM mysql.user; ===Show privileges for a user from a given host=== SHOW GRANTS FOR 'user_name'@'host'; ===Create a user that can login from localhost...") |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 8: | Line 8: | ||
=Users and Permissions= | =Users and Permissions= | ||
===Show MySQL users, their passwords and hosts=== | ===Show MySQL users, their passwords and hosts=== | ||
SELECT | |||
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=== | ||
SHOW GRANTS FOR 'user_name'@'host'; | SHOW GRANTS FOR 'user_name'@'host'; | ||
===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=== | ||
GRANT ALL PRIVILEGES ON *.* TO '[newuser]'@'localhost'; | GRANT ALL PRIVILEGES ON *.* TO '[newuser]'@'localhost'; | ||
GRANT ALL PRIVILEGES ON [database].* TO '[newuser]'@'localhost'; | GRANT ALL PRIVILEGES ON [database].* TO '[newuser]'@'localhost'; | ||
Latest revision as of 01:58, 24 January 2025
mysqldump
Dump a Single Database
mysqldump -u root -p --databases [db-name] > db_$(date +%F).sql
Dump ALL Databases
mysqldump -u root -p --all-databases --single-transaction > all-db_$(date +%F).sql
Users and Permissions
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