MySQL Grant all privileges on database : GRANT ALL PRIVILEGES is used to grant the privileges on database in MySQL. We sometimes need to give the full privileges to the user on database. Here in this tutorial we are going to explain how you can grant all privileges on database to the users.

MySQL Grant all privileges on database

There are many ways to Grant all privileges to the user on any database. Here are some useful ways to grant the privileges on database in MySql.

MySQL Grant all privileges WITH GRANT OPTION

If you want to grant all privileges with grant option in mysql use the following Query –

GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'%' WITH GRANT OPTION;

This will give all privileges to the user my_user with grant option.

Here is another way to grant all privileges to the user.

MySQL Grant all privileges without GRANT OPTION

GRANT ALL PRIVILEGES ON my_database.* to 'my_user@localhost' INDENTIFIED by 'my_passwd';

The above example will give the full PRIVILEGES to user identified by the password without grant option.

MySQL Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER privileges to user

You can Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER privileges simply using the below query on database.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER 
ON my_database.* TO 'my_user@localhost' IDENTIFIED BY 'my_password';

The above example will give the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX and ALTER PRIVILEGES to the user on the database.

Can’t connect to local MySQL server through socket ‘/var/mysql/mysql.sock’ – Sometimes you get this error while starting the mysql server. There can be different reason for this. Here in this solution we are going to tell how you can fix the above error.

Can’t connect to local MySQL server through socket ‘/var/mysql/mysql.sock’

2. Now go to client [client] section and add the following

If [Client] Section is not available-

[client]
 socket=/var/lib/mysql/mysql.sock

If [Client] Section is available-

If client section is already available in my.cnf file then only add the below code in [client] section as below –

 socket=/var/lib/mysql/mysql.sock

After Adding the above it will fix your issue.

Get list of MySQL user accounts : If you are looking to get the list of the user accounts in mysql you can get this simply using the query from mysql.user table. Here in this example we are going to explain how you can get all the users of the mysql using the simple query.

Get list of MySQL user accounts

Here is simple query which will give you the list of the MySql User Accounts –

SELECT User FROM mysql.user;

The above example will give you the mysql users. It will produce output something like this –

MySql get Username, Password And Hostname

You can get the username, hostname and password from mysql simply using the query below –

SELECT User, Password, Host FROM mysql.user;

If you the above query it will give you the username, password and hostname. The output will be something like this –

SQL Prevent Duplicate INSERT – MySql : If you are working with mysql insert operation and want to stop duplicate insertion operation . You can use INSERT IGNORE statement. This will prevent the duplicate row insertion.

SQL Prevent Duplicate INSERT – MySql

Here is an example of Insert ignore statement in mysql

mysql> INSERT IGNORE INTO users (user_id, email) VALUES (32, test@ymail.com);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE INTO users (user_id, email) VALUES (32, test@ymail.com);
Query OK, 0 rows affected (0.00 sec)

The above example clearly shows when you run the same query to insert the same data it will not insert the data again.

Sql create table with primary key

CREATE TABLE Users
(
ID int NOT NULL AUTO_INCREMENT,
Name varchar(100),
Email varchar(100),
Address varchar(100),
City varchar(100),
PRIMARY KEY (ID)
);

this will create table users with primary key ID and autoincrement functionality.

Sql create table with primary key with Syntax

CREATE TABLE Users
(
ID int,
Name varchar(100),
Email varchar(100),
Address varchar(100),
City varchar(100),
PRIMARY KEY (ID)
);

Which will create table users with primary key ID.

Mysql find duplicate records in table

Syntax for MySQL find duplicate records in table

Simple Way to find the duplicate records in Sql is as

   Select id Count( id ) As total_ids 
   From Users Group By id 
   Having Count( id) > 1  Order By total_ids desc;