MySQL - Administration
First check if your MySQL server is running or not. You can use the following command to check it :
ps -ef | grep mysqld
If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command −
Now, if you want to shut down an already running MySQL server, then you can do it by using the following command −
root@host# cd /usr/bin ./mysqladmin -u root -p shutdown Enter password: ******
For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql.
The following program is an example of adding a new user guest with SELECT, INSERT and UPDATE privileges with the password guest123; the SQL query 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)
When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example, the password mypass is encrypted to 6f8c114b58f2ce9e.
Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don't use it, then you won't be able to connect to MySQL using the new user account at least until the server is rebooted.
You can also specify other privileges to a new user by setting the values of following columns in user table to 'Y' when executing the INSERT query or you can update them later using UPDATE query.
Another way of adding user account is by using GRANT SQL command. The following example will add user zara with password zara123 for a particular database, which is named as 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 also create an entry in the MySQL database table called as user.
NOTE − MySQL does not terminate a command until you give a semi colon (;) at the end of the SQL command.
The /etc/my.cnf File Configuration In most of the cases, you should not touch this file. By default, it will have the following entries:
[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 specify a different directory for the error log, otherwise you should not change any entry in this table.
Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database :
MySQL - Triggers
posted on 2019-11-29 21:44:07 - mysql Tutorials
MySQL - Grant_ Revoke Privilege
posted on 2019-11-26 23:15:04 - mysql Tutorials
MySQL Vs SQL
posted on 2019-11-25 05:02:26 - mysql Tutorials
how to use range in golang
posted on 2022-05-03 08:28:11 - Go lang Tutorials
find variable type in golang
posted on 2022-05-03 05:24:55 - Go lang Tutorials
for loops in golang
posted on 2022-05-03 04:27:40 - Go lang Tutorials