How to Manage MySQL Databases and Users from the Command Line
MySQL is the most mainstream open-source social information base administration framework. MySQL worker permits us to make various clients and information bases and award fitting advantages so the clients can get to and oversee information bases.
This instructional exercise discloses how to utilize the order line to make and oversee MySQL or MariaDB information bases and clients.
Before you begin
Before you start with this instructional exercise, we are expecting that you as of now have MySQL or MariaDB worker introduced on your framework. All orders will be executed as a root client.
To open the MySQL brief, type the accompanying order and enter the MySQL root client secret phrase when provoked:
mysql -u root -p
Create a new MySQL database
To make another MySQL information base run the accompanying order, simply supplant database_name with the name of the information base that you need to make:
CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec)
In the event that you attempt to make an information base that as of now exists you will see the accompanying blunder message:
ERROR 1007 (HY000): Can't create database 'database_name'; database exists
To evade mistakes if the information base with a similar name as you are attempting to make exists you can utilize the accompanying order:
CREATE DATABASE IF NOT EXISTS database_name;
Query OK, 1 row affected, 1 warning (0.00 sec)
In the yield above, Query OK implies that the question was fruitful, and 1 admonition reveals to us that the information base as of now exists and no new data set was made.
List all MySQL databases
You can list all information bases that exist on our MySQL or MariaDB worker with the accompanying order:
The yield will look something like this:
+--------------------+ | Database | +--------------------+ | information_schema | | database_name | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
The information_schema, mysql, performance_schema, and sys information bases are made at establishment time and they are putting away data pretty much any remaining information bases, framework design, clients, authorization and other significant information. These information bases are important for the legitimate usefulness of the MySQL establishment.
Delete a MySQL database
Erasing a MySQL information base is as basic as running a solitary order. This is a non-reversible activity and should be executed with alert. Ensure that you are not eliminating an off-base information base, as once you erase the information base it can't be recuperated.
To erase a MySQL or MariaDB, information base run the accompanying order:
DROP DATABASE database_name;
Query OK, 0 rows affected (0.00 sec)
On the off chance that you attempt to erase an information base that doesn't exist you will see the accompanying mistake message:
ERROR 1008 (HY000): Can't drop database 'database_name'; database doesn't exist
To maintain a strategic distance from this blunder you can utilize the accompanying order:
DROP DATABASE IF EXISTS database_name;
Create a new MySQL user account
A client account in MySQL comprises of a client name and host name parts.
To make another MySQL client account run the accompanying order, simply supplant 'database_user' with the name of the client that you need to make:
Make USER 'database_user'@'localhost' IDENTIFIED BY 'user_password';
In the order above we have set the hostname part to localhost which implies that this client will have the option to interface with the MySQL worker just from the localhost ( i.e from the framework where MySQL Server runs). In the event that you need to allow access from another host(s) simply change the localhost with the far off machine IP or use '%' special case for the host part, which implies that the client record will have the option to interface from any host.
Same as when working with the information bases to dodge a blunder when attempting to make a client account which as of now exists you can utilize:
CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'user_password';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Change a MySQL user account password
The sentence structure for changing a MySQL or MariaDB client account secret key relies upon the worker rendition you are running on your framework.
You can discover your worker rendition by giving the accompanying order:
In the event that you have MySQL 5.7.6 and fresher or MariaDB 10.1.20 and more current, to change the secret word utilize the accompanying order:
ALTER USER 'database_user'@'localhost' IDENTIFIED BY 'new_password';
On the off chance that you have MySQL 5.7.5 and more seasoned or MariaDB 10.1.20 and more established, at that point use:
SET PASSWORD FOR 'database_user'@'localhost' = PASSWORD('new_password');
In the two cases, the yield should resemble this:
Query OK, 0 rows affected (0.00 sec)
List all MySQL user accounts
You can list all MySQL or MariaDB client accounts by questioning the mysql.users table:
SELECT user, host FROM mysql.user;
The yield should seem to be like beneath:
+------------------+-----------+ | user | host | +------------------+-----------+ | database_user | % | | database_user | localhost | | debian-sys-maint | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 6 rows in set (0.00 sec)
Delete MySQL user account
To erase a client account , utilize the accompanying order:
DROP USER 'database_user@'localhost';
In the event that you attempt to erase a client account which doesn't exist a blunder will happen.
ERROR 1396 (HY000): Operation DROP USER failed for 'database_user'@'localhost'
Same as when working with the information bases to maintain a strategic distance from the blunder you can utilize:
DROP USER IF EXISTS 'database_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Grant permissions to a MySQL user account
There are numerous kinds of advantages that can be conceded to a client account. You can locate a full rundown of advantages upheld by MySQL here . In this guide we will experience a few models:
To amazing all advantages to a client account over a particular information base, utilize the accompanying order:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
To excellent all advantages to a client account over all information bases, utilize the accompanying order:
GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
To fabulous all advantages to a client account over a particular table from an information base, utilize the accompanying order:
GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
On the off chance that you need to give just explicit advantages to a client account over a particular information base sort:
GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';
Revoke permissions from a MySQL user account
On the off chance that you need to repudiate at least one advantages or all advantages from a client account, the linguistic structure is practically indistinguishable from allowing it. For instance, on the off chance that you need to repudiate all advantages from a client account over a particular information base, utilize the accompanying order:
REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
Display MySQL user account privileges
To discover the privilege(s) conceded to a particular MySQL client account type:
SHOW GRANTS FOR 'database_user'@'localhost';
+---------------------------------------------------------------------------+ | Grants for database_user@localhost | +---------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'database_user'@'localhost' | | GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost' | +---------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
This instructional exercise covers just the rudiments, however it should be a decent beginning for any individual who needs to figure out how to oversee MySQL information bases and clients from the order line. You can likewise check the instructional exercise about how to reset a MySQL root secret word in the event that you have failed to remember it.
There's nothing more to it! In the event that you have any inquiries or criticism, don't hesitate to leave a remark.
While working with any technology, We need to interact with a file system by creating a new file, adding content, and deleting it. With NodeJS work...
While working with NodeJS, We may need to work with the original JSON file. In this composition, We'll see writing data to a original JS...
Today, I'm going to produce a sample operation to show how to use original JSON file in Angular operation. There area colorful way we can use o...
As Developer, We should be apprehensive of all the ways to do a task, So we can perform the job moreeffeciently.However, including If we talk...
In this composition, We'll see how to display a simple table, listing with Array of Objects and nesting list inReact.js. This composition is su...
FTP (File Transfer Protocol) is a standard organization convention used to move documents to and from a far off organization. For safer and quicker...
An emblematic connection, otherwise called a symlink, is a unique kind of record that focuses to another document or registry. It is something like...
Minecraft is one of the most mainstream rounds ever. It is a sandbox computer game where players investigate endless universes and fabricate variou...
The hostname is set during the establishment of the working framework or powerfully relegated to the virtual machine when it is made. This g...
On Linux and UNIX working frameworks, you can utilize the mount order to append (mount) document frameworks and removable gadgets, for example, USB...
A Virtual Host is an Apache arrangement mandate that permits you to run more than one site on a solitary worker. With Virtual Hosts, you can indica...
Boa constrictor is the most mainstream python information science and AI stage, utilized for huge scope information handling, prescient investigati...
Particle is an open-source cross-stage code manager created by GitHub. It has an inherent bundle chief, inserted Git control, keen autocompletion, ...
An HTML editor is a software for enhancing HTML, the markup of an internet web page. It stands for Tiny Moxiecode Content Editor. TinyMCE is a effe...
Redis is an open-source in-memory key-esteem information store. It tends to be utilized as an information base, store and, message representative, ...
phpMyAdmin is an open-source PHP based instrument for overseeing MySQL and MariaDB workers over an online interface. phpMyAdmin permits you ...
While diverting the yield of an order to a document or funneling it to another order, you may see that the blunder messages are imprinted on the sc...
Deb is the establishment bundle design utilized by all Debian based dispersions. The Ubuntu vaults contain a huge number of deb bundles that...
CodeIgniter Subquery: This tutorial will explain you how to write subquery in codeigniter using active records. SubQuery is a query ...