MySQL: Allow user to create database USING LINUX TERMINAL

 MySQL: Allow user to create database USING LINUX TERMINAL

After installing MySQL on your Linux system, you can create one or more users and grant them permissions to do things like create databases, access table data, etc.

It is not recommended to use the root account, but rather create a new account and grant privileges as needed. In this tutorial, you will see how to allow a user to create a MySQL database on Linux.

In this tutorial you will learn:

• How to create a new MySQL user
• How to allow a user to create MySQL databases
• How to grant full privileges to a user

MySQL: Allow user to create database step by step instructions

---

---

1. Start by opening up MySQL with the root account.

   $ sudo mysql
   

   Or, on some configurations you may be required to enter the following command and provide your root password:

   $ mysql -u root -p
   

2. If you have not already done so, create a new user to which we will grant database creation permissions in the later steps. The following example creates user linuxconfig. Fill in the placeholder values with your own information.

   mysql> CREATE USER 'linuxconfig'@'localhost' IDENTIFIED BY 'password_here';
   

3. Now it is time to grant permissions to our linuxconfig user so that it can create new databases. We will do this by granting them the CREATE permission with the following command.

   mysql> GRANT CREATE ON *.* TO 'linuxconfig'@'localhost';
   

   If you would instead prefer to grant your user permissions to only create new tables within a certain database, say the testdb database, we would use the following command:

   mysql> GRANT CREATE ON testdb.* TO 'linuxconfig'@'localhost';
   

   Here is how you would grant full permissions to the user, which allows them to create databases, as well as access them, write new data, delete rows, etc.

   mysql> GRANT ALL PRIVILEGES ON *.* TO 'linuxconfig'@'localhost';
   

   If you need to grant additional privileges to the user, but do not want to give them ALL privileges, there are others you can use below.

   CREATE - allow user to create new tables in the database
   DROP - allow user to delete tables or the database itself
   DELETE - allow user to delete rows withs in the tables
   INSERT - allow user to insert new data rows into the tables
   SELECT - allow user to read entries in the database
   UPDATE - allow user to update existing database rows
   

4. The last step is to flush all privileges before exiting MySQL completely.

   mysql> FLUSH PRIVILEGES;
   mysql> exit
   

---

---

That’s all there is to it. Your user should now be able to create new databases or at least create new tables in the database you gave them permissions on, depending which setup you opted for.

Closing Thoughts

In this tutorial, we saw how to allow a user to create a database in MySQL on a Linux system. This is better and more secure than using the root account to do everything, and should work the same on any Linux distro. It is now easy enough to grant further privileges or take them away as needed.

Rename a MySQL Database Using cPanel:

1. Log in to cPanel.
2. In the Databases section, click MySQL Databases.
3. A new page will open. Scroll down to the database you want to rename and select the Rename link under the Actions column.
4. Type the new database name, then click Proceed.
5. 
   

[android@lamp ~]$ sudo mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[android@lamp ~]$ mysql -u root -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[android@lamp ~]$ mysql -u root -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 59

Server version: 10.3.35-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT CREATE ON *.* TO 'android'@'localhost';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'android'@'localhost';

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> FLUSH PRIVILEGES

    -> exit

    ->

    -> FLUSH PRIVILEGES;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB                                                                                                        server version for the right syntax to use near 'exit

FLUSH PRIVILEGES' at line 2

MariaDB [(none)]> FLUSH PRIVILEGES;