MYSQL - CREATE command denied to user

February 23, 2016 6k views
MySQL MariaDB PHP
5a6c1d69e18cff4e04aab85a355f0b01148ec40b
By:
Felice

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...

Log In to Comment
1 Answer
asb MOD February 23, 2016

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
by Justin Ellingwood
MySQL and MariaDB are popular SQL databases available on Ubuntu. This article introduces how to create a table in either of these database programs. Within a database, a table the primary organizational structure that stores your data.
Reply
Have another answer? Share your knowledge.

Related Questions