Brevi appunti per amministrazione di mysql
Lista dei DB
mysql -u root -p > show databases;
Dump del DB
mysql -u root -p dbname > dbname.sql
Import del DB dump
mysql -u root -p dbname < dbname.sql
Creating a New User Account
To create a new user account, first log in as root. Next, use the following command to create the user.
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'some_password'; flush privileges;
This command would give the new user all privileges on all databases and tables. The user could only log in from the host specified by localhost. For the changes to take effect, you must call the flush privileges; command to make the server reread the user table.
The previous command is not something you would generally do. A more reasonable command line might look like this.
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db.* TO 'username'@'localhost' IDENTIFIED BY 'password'; flush privileges;
This example explicitly identifies the privileges being granted. This is preferable as privileges are limited to only the user and database where access is required. The privileges are only applied to the database 'db' and not all the databases on the sever.
Change a User's Password
If you need to change a users password and nothing else, use the following set of commands.
mysql -u root -p use mysql; update user set password=password('new_password') where user='username'; flush privileges;
The password function encrypts the password in the database. Remember to execute flush privileges; so that your changes take effect.