Category Archives: Database Administration

How To Create a New User and Grant Permissions in MySQL

Posted on by in , ,
In the cases where more restrictions may be required, there are ways to create users with custom permissions. Let’s start by making a new user within the MySQL shell: CREATE USER 'nikesh'@'localhost' IDENTIFIED BY 'password'; Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell. Therefore, the first thing to do is to create new database and provide the user with access to that database. create database nikeshdb; GRANT ALL PRIVILEGES ON nikeshdb.* TO 'nikeshshk'@'localhost'; The asterisks in this command refer to table that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables. Once you have finalized the permissions that you want...

Remove Duplicate Rows from a Table in SQL Server

Posted on by in ,
Firstly, we will create a table, where we will insert some duplicate rows to understand the topic properly. Create a table called ATTENDANCE by using the following code: CREATE TABLE [dbo].[ATTENDANCE]( [EMPLOYEE_ID] [varchar](50) NOT NULL, [ATTENDANCE_DATE] [date] NOT NULL ) ON [PRIMARY] Now insert some data into this table. INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A001',CONVERT(DATETIME,'01-01-11',5)) INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A001',CONVERT(DATETIME,'01-01-11',5)) INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5)) INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5)) INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5)) INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A003',CONVERT(DATETIME,'01-01-11',5)) After inserting the data, check the data of the below table. If we grouped the employee_id and attendance_date, then A001 and A002 become duplicates. EMPLOYEE_ID ATTENDANCE_DATE A001 2011-01-01 A001 2011-01-01 A002 2011-01-01 A002 2011-01-01 A002 2011-01-01 A003 2011-01-01 So how can we delete those duplicate data? Solution First, insert an identity column in that table by using the following code: ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1) Now the table data will be like the following table: EMPLOYEE_ID ATTENDANCE_DATE AUTOID A001 2011-01-01 1 A001 2011-01-01 2 A002...

Unknown table engine ‘InnoDB’

Posted on by in ,
If You are facing problem with Unknown table engine 'InnoDB' on to your mysql Database. You can do following to get rid of this problem. First look at the ib_logfile0 and ib_logfile1 in /var/lib/mysql. If they have been set to size different from the default, that probably will prevent mysqld's startup. The default for innodb_lkog_file_size is 5M. Add this in my.cnf 
[mysqld]
innodb_log_file_size = 12M
And then Proceed by doing following 
/etc/init.d/mysql stop

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak # these are your
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak # log files

/etc/init.d/mysql start
 ...

Change MySQL root Password

Posted on by in , ,

Change MySQL root Password

Method #1: Use mysqladmin command to change root password

If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows: $ mysqladmin -u root password NEWPASSWORD However, if you want to change (or update) a root password, then you need to use the following command: $ mysqladmin -u root -p'oldpassword' password newpass For example, If the old password is abc, you can set the new password to 123456, enter: 
$ mysqladmin -u root -p'abc' password '123456'

Method #2: Changing MySQL root user password using mysql command

This is an another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update or change the password using the...