Posts From Category: sql

How to grant privileges for a database to a user, when both already exist.

In this case the database name will be project_node1, the user project_user and the password project_pass. All the following commands have to be executed as root or with a user with enough privileges.

-- list system users
SELECT user, host FROM mysql.user;

-- see current privileges
SHOW GRANTS FOR 'project_user'@'%';

-- delete all previous privileges (if needed)
-- REVOKE ALL PRIVILEGES ON `project_node1`.* FROM 'project_user'@'%';

-- grant new privileges and flush
GRANT ALL PRIVILEGES ON `project_node1`.* TO 'project_user'@'%';
FLUSH PRIVILEGES;

Reference(s)

https://serverfault.com/questions/115950/how-do-i-change-the-privileges-for-mysql-user-that-is-already-created

Read More

Connection

Connect to DB from CLI

mysql -u {$user} -p    

User

See current logged user SELECT CURRENT_USER();

Change password when we know the old one

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('myNewPassword');

Read More

DDL Queries

• Show all dbs - SHOW DATABASES;
• Select one - USE {$db};
• Show tables in a db - SHOW TABLES;
• Show description of DB - DESCRIBE {$db}

Create new table

CREATE TABLE table_name (
	id numeric,
	description text);

Read More