Running and Shutting down MySQLi Server
MySQLi is reached out of MySQL so first check if your MySQL worker is running or not. You can utilize the accompanying order to check this −
ps -ef | grep mysqld
On the off chance that your MySql is running, at that point you will see mysqld measure drilled down in your outcome. On the off chance that worker isn't running, at that point you can begin it by utilizing the accompanying command−
root@host# cd /usr/bin
./safe_mysqld &
Presently, in the event that you need to close down an all around running MySQL worker, at that point you can do it by utilizing the accompanying order −
root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******
Setting Up a MySQLi User Account
For adding another client to MySQLi Which is improved adaptation of MySQL, you simply need to add another section to client table in data set mysql.
The following is an illustration of adding new client visitor with SELECT, INSERT and UPDATE advantages with the secret phrase guest123; the SQL inquiry is −
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user (
host, user, password, select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host | user | password |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)
While adding another client, make sure to scramble the new secret phrase utilizing PASSWORD() work given by MySQL. As you can find in the above model the secret word mypass is encoded to 6f8c114b58f2ce9e.
Notice the FLUSH PRIVILEGES proclamation. This advises the worker to reload the award tables. On the off chance that you don't utilize it, at that point you won't have the option to interface with mysql utilizing the new client account at any rate until the worker is rebooted.
You can likewise indicate different advantages to another client by setting the benefits of following sections in client table to 'Y' when executing the INSERT question or you can refresh them later utilizing UPDATE inquiry.
- Select_priv
- Insert_priv
- Update_priv
- Delete_priv
- Create_priv
- Drop_priv
- Reload_priv
- Shutdown_priv
- Process_priv
- File_priv
- Grant_priv
- References_priv
- Index_priv
- Alter_priv
Another method of adding client account is by utilizing GRANT SQL order; following model will add client zara with secret key zara123 for a specific information base called TUTORIALS.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123';
This will likewise make a section in mysql information base table called client.
NOTE − MySQL doesn't end an order until you give a semi colon (;) toward the finish of SQL order.
The /etc/my.cnf File Configuration
A large portion of the cases, you ought not touch this record. Of course, it will have the accompanying passages −
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
[mysql.server]
user = mysql
basedir = /var/lib
[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
Here, you can indicate an alternate registry for mistake log, else you ought not change any passage in this table.
Administrative MySQLi Command
Here is the rundown of significant MySQLi orders, which you will utilize time to time to work with MySQL data set −
- USE Databasename − This will be utilized to choose a specific information base in MySQLi workarea.
- SHOW DATABASES − Lists the information bases that are open by the MySQLi DBMS.
- SHOW TABLES − Shows the tables in the information base once a data set has been chosen with the utilization order.
- SHOW COLUMNS FROM tablename − Shows the ascribes, sorts of qualities, key data, regardless of whether NULL is allowed, defaults, and other data for a table.
- SHOW INDEX FROM tablename − Presents the subtleties of all files on the table, including the PRIMARY KEY.
- SHOW TABLE STATUS LIKE tablename\G − Reports subtleties of the MySQLi DBMS execution and insights.