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

Amministrazione utenti

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.

Appunti/MySQL (last edited 2012-04-04 09:54:43 by jaromil)