MYSQL - CREATE command denied to user

Question

Hey guys, I created a remote mysql base on tutorial here https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql . However I failed to create tables from the remote site.

Here command I ran

On Database Server:

create databases test; create user ‘test’@‘localhost’ identified by ‘password’; create user ‘test’@‘web_server’ identified by ‘password’;

grant all privileges on test.* to ‘test’@‘localhost’; grant all privileges on test.* to ‘test’@‘web_server’;

flush privileges;

I successfully conencted but failed to create tables; Due to command denied user…

Andrew SB · Answer

Hi! Could you give an example of how you tried to create a table? You need to make sure that the table is in the test database as the test user only has permissions there. For example, this would create a table named table in the `test: database:

CREATE TABLE IF NOT EXISTS test.table (
    id int(5) NOT NULL AUTO_INCREMENT,
    name varchar(50) DEFAULT NULL, PRIMARY KEY(id)
);

The result is:

mysql> show columns in test.table;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Check out this article for more information on working with MySql:

How To Create a Table in MySQL and MariaDB on an Ubuntu Cloud Server

Related

Question

MYSQL - CREATE command denied to user

Related