MySQL: Difference between revisions

From Dave-Wiki
 
Line 17: Line 17:
===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 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