You can find a full list of privileges supported by MySQL here. There are multiple types of privileges that can be granted to a user account. ERROR 1396 (HY000): Operation DROP USER failed for as when working with the databases to avoid the error you can use: DROP USER IF EXISTS OK, 0 rows affected, 1 warning (0.00 sec)ħ)Grant permissions to a MySQL user account To delete a user account, use the following command: DROP USER you try to delete a user account which doesn’t exist an error will occur. ![]() The output should look similar to below: +-+-+ You can list all MySQL or MariaDB user accounts by querying the ers table: SELECT user, host FROM er In both cases, the output should look like this: Query OK, 0 rows affected (0.00 sec) If you have MySQL 5.7.5 and older or MariaDB 10.1.20 and older, then use: SET PASSWORD FOR = PASSWORD('new_password') If you have MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer, to change the password use the following command: ALTER USER IDENTIFIED BY 'new_password' You can find your server version by issuing the following command: mysql -version The syntax for changing a MySQL or MariaDB user account password depends on the server version you are running on your system. Query OK, 0 rows affected, 1 warning (0.00 sec) Same as when working with the databases to avoid an error when trying to create a user account which already exists you can use: CREATE USER IF NOT EXISTS IDENTIFIED BY 'user_password' If you want to grant access from another host(s) just change the localhost with the remote machine IP or use '%' wildcard for the host part, which means that the user account will be able to connect from any host. In the command above we have set the hostname part to localhost which means that this user will be able to connect to the MySQL server only from the localhost ( i.e from the system where MySQL Server runs). To create a new MySQL user account run the following command, just replace ‘database_user’ with the name of the user that you want to create: CREATE USER IDENTIFIED BY 'user_password' To avoid this error you can use the following command: DROP DATABASE IF EXISTS database_name Ī user account in MySQL consists of a user name and host name parts. ![]() If you try to delete a database that doesn’t exist you will see the following error message: ERROR 1008 (HY000): Can't drop database 'database_name' database doesn't exist To delete a MySQL or MariaDB, database run the following command: DROP DATABASE database_name Make sure that you are not removing a wrong database, as once you delete the database it cannot be recovered. This is a non-reversible action and should be executed with caution. ![]() 3)Delete a MySQL databaseĭeleting a MySQL database is as simple as running a single command. These databases are necessary for the proper functionality of the MySQL installation. The information_schema, mysql, performance_schema, and sys databases are created at installation time and they are storing information about all other databases, system configuration, users, permission and other important data. The output will look something like this: +-+ You can list all databases that exist on our MySQL or MariaDB server with the following command: SHOW DATABASES In the output above, Query OK means that the query was successful, and 1 warning tells us that the database already exists and no new database was created. Query OK, 1 row affected, 1 warning (0.00 sec) To avoid errors if the database with the same name as you are trying to create exists you can use the following command: CREATE DATABASE IF NOT EXISTS database_name If you try to create a database that already exists you will see the following error message: ERROR 1007 (HY000): Can't create database 'database_name' database exists To create a new MySQL database run the following command, just replace database_name with the name of the database that you want to create: CREATE DATABASE database_name ![]() To open the MySQL prompt, type the following command and enter the MySQL root user password when prompted: mysql -u root -p All commands will be executed as a root user. How to Manage MySQL Databases and Users from the Command Line Before you beginīefore you start with this tutorial, we are assuming that you already have MySQL or MariaDB server installed on your system.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |