MySQL

From Dave-Wiki
Revision as of 02:14, 15 February 2024 by Dave (talk | contribs) (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...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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

 CREATE USER '[newuser]'@'localhost' IDENTIFIED 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