How to reset MySQL root password?

If you forgot root password for your MySQL server, you can follow the steps below to reset it.

1. Login to the server as root and stop MySQL service.

# /etc/init.d/mysqld stop

2. Start mysqld_safe service.

# mysqld_safe –skip-grant-tables &

3. Login to MySQL server now. It won’t ask you for a password.

# mysql -u root

4. Set up a new MySQL root user password:

mysql> use mysql;

mysql> update user set password=PASSWORD(”NEW-ROOT-PASSWORD”) where User=’root‘;

mysql> flush privileges;

mysql> quit

5. Restart your MySQL service.

# /etc/init.d/mysqld restart

6. Try to login using new password. It should work fine.

# mysql -u root -p

That’s it !!!

Mysql and C API

//Just a simple example to show that we can work with mysql using c language

// Happy programming
#include<my_global.h>
#include<my_sys.h>
#include<mysql.h>

static char *opt_host_name = “yourip”; //eg 192.168.0.254
static char *opt_user_name = “userdatabase”;
static char *opt_password = “userpassword”;
static unsigned int opt_port_num = 3306;
static char *opt_socket_name = NULL;
static char * opt_db_name = “dbname”;
static unsigned int opt_flags = 0;

static MYSQL *conn;

int main(int argc, char *argv[])
{
conn = mysql_init(NULL);

if(conn == NULL)
{
fprintf(stderr,”mysql init() failed\n”);
exit(1);
}

if(mysql_real_connect(conn,opt_host_name,opt_user_name,opt_password,

opt_db_name,opt_port_num,opt_socket_name,opt_flags) == NULL)
{
fprintf(stderr,”mysql_real_connect() failed\n”);
mysql_close(conn);
exit(1);

}
else
{
printf(“connected to mysql server on:%s\t”,opt_host_name);
}

mysql_close(conn);
return 0;
}

General Instructions for Building Client Programs

When you write a MySQL client program in C, you’ll need a C compiler, obviously.
The examples shown here use gcc, which is probably the most common compiler used
on Unix.You’ll also need the following in addition to the program’s own source files:

• The MySQL header files
• The MySQL client library

The header files and client library constitute the basis of MySQL client programming
support. If they are not installed on your system already, you’ll need to obtain them. If
MySQL was installed on your system from a source or binary distribution, client programming
support should have been installed as part of that process. If RPM files were
used, this support won’t be present unless you installed the developer RPM. Should you
need to obtain the MySQL header files and library

Compiling and Linking Client Programs

To compile and link a client program, you might need to specify where the MySQL
header files and client library are located, because often they are not installed in locations
that the compiler and linker search by default. For the following examples, suppose that
the header file and client library locations are /usr/local/include/mysql and
/usr/local/lib/mysql.
To tell the compiler how to find the MySQL header files when you compile a source
file into an object file, pass it an -I option that names the appropriate directory. For
example, to compile myclient.c to produce myclient.o, you might use a command
like this:
% gcc -c -I/usr/local/include/mysql myclient.c
To tell the linker where to find the client library and what its name is, pass
-L/usr/local/lib/mysql and -lmysqlclient arguments when you link the object file
to produce an executable binary, as follows:
% gcc -o myclient myclient.o -L/usr/local/lib/mysql -lmysqlclient
If your client consists of multiple files, name all the object files on the link command.
The link step may result in error messages having to do with functions that cannot be
found. In such cases, you’ll need to supply additional -l options to name the libraries
containing the functions. If you see a message about compress() or uncompress(), try
adding -lz or -lgz to tell the linker to search the zlib compression library:
% gcc -o myclient myclient.o -L/usr/local/lib/mysql -lmysqlclient -lz
If the message names the floor() function, add -lm to link in the math library.You
might need to add other libraries as well. For example, you’ll probably need -lsocket
and -lnsl on Solaris.

You can use the mysql_config utility to determine the proper flags for compiling
and linking MySQL programs. For example, the utility might indicate that the following
options are needed:
% mysql_config –cflags
-I’/usr/local/mysql/include/mysql’
% mysql_config –libs
-L’/usr/local/mysql/lib/mysql’ -lmysqlclient -lz -lcrypt -lnsl -lm
To use mysql_config directly within your compile or link commands, invoke it
within backticks:
% gcc -c `mysql_config –cflags` myclient.c
% gcc -o myclient myclient.o `mysql_config –libs`
The shell will execute mysql_config and substitute its output into the surrounding
command, which automatically provides the appropriate flags for gcc.
If you don’t use make to build programs, I suggest you learn how so that you won’t
have to type a lot of program-building commands manually. Suppose that you have a
client program, myclient, that comprises two source files, main.c and aux.c, and a
header file, myclient.h.You might write a simple Makefile to build this program as follows.
Note that indented lines are indented with tabs; if you use spaces, the Makefile
will not work.
CC = gcc
INCLUDES = -I/usr/local/include/mysql
LIBS = -L/usr/local/lib/mysql -lmysqlclient
all: myclient
main.o: main.c myclient.h
$(CC) -c $(INCLUDES) main.c
aux.o: aux.c myclient.h
$(CC) -c $(INCLUDES) aux.c
myclient: main.o aux.o
$(CC) -o myclient main.o aux.o $(LIBS)
clean:
rm -f myclient main.o aux.o
Using the Makefile, you can rebuild your program whenever you modify any of the
source files simply by running make, which displays and executes the necessary commands:
% make
gcc -c -I/usr/local/mysql/include/mysql myclient.c
gcc -o myclient myclient.o -L/usr/local/mysql/lib/mysql -lmysqlclient

That’s easier and less error prone than typing long gcc commands. A Makefile also
makes it easier to modify the build process. For example, if your system is one for which
you need to link in additional libraries such as the math and compression libraries, edit
the LIBS line in the Makefile to add -lm and -lz:
LIBS = -L/usr/local/lib/mysql -lmysqlclient -lm -lz
If you need other libraries, add them to the LIBS line as well.Thereafter when you
run make, it will use the updated value of LIBS automatically.
Another way to change make variables other than editing the Makefile is to specify
them on the command line. For example, if your C compiler is named cc rather than
gcc, you can say so like this:
% make CC=cc
If mysql_config is available, you can use it to avoid writing literal include file and
library directory pathnames in the Makefile.Write the INCLUDES and LIBS lines as follows
instead:
INCLUDES = ${shell mysql_config –cflags}
LIBS = ${shell mysql_config –libs}
When make runs, it will execute each mysql_config command and use its output to
set the corresponding variable value.The ${shell} construct shown here is supported
by GNU make; you might need to use a somewhat different syntax if your version of
make isn’t based on GNU make.
If you’re using an integrated development environment (IDE), you may not use a
Makefile at all.The details will depend on your particular IDE.

Reset Mysql password

How to Reset the Root PasswordIf you have never set a root password for MySQL, the server does not require a password at all for connecting as root. However, it is recommended to set a password for each account. See Section 5.6.1, “General Security Guidelines”.

If you set a root password previously, but have forgotten what it was, you can set a new password. The following procedure is for Windows systems. The procedure for Unix systems is given later in this section.

The procedure under Windows:

1. Log on to your system as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager:

   Start Menu -> Control Panel -> Administrative Tools -> Services

   Then find the MySQL service in the list, and stop it.

   If your server is not running as a service, you may need to use the Task Manager to force it to stop.

3. Create a text file and place the following command within it on a single line:

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

   Save the file with any name. For this example the file will be C:\mysql-init.txt.

4. Open a console window to get to the DOS command prompt:

   Start Menu -> Run -> cmd

5. We are assuming that you installed MySQL to C:\mysql. If you installed MySQL to another location, adjust the following commands accordingly.

   At the DOS command prompt, execute this command:

   C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

   The contents of the file named by the --init-file option are executed at server startup, changing the root password. After the server has started successfully, you should delete C:\mysql-init.txt.

   If you install MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option:

   C:\> "C:\Program Files\MySQL\MySQL Server 
   5.0\bin\mysqld-nt.exe"
            --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"
            --init-file=C:\mysql-init.txt

   The appropriate --defaults-file setting can be found using the Services Manager:

   Start Menu -> Control Panel -> Administrative Tools -> Services

   Find the MySQL service in the list, right-click on it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.

6. Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.
7. You should be able to connect using the new password.

In a Unix environment, the procedure for resetting the root password is as follows:

MySQL Enterprise.  For expert advice on security-related issues, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

1. Log on to your system as either the Unix root user or as the same user that the mysqld server runs as.
2. Locate the .pid file that contains the server’s process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system’s hostname.

   You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command:

   shell> kill `cat /mysql-data-directory/host_name.pid`

   Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

3. Create a text file and place the following command within it on a single line:

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

   Save the file with any name. For this example the file will be ~/mysql-init.

4. Restart the MySQL server with the special --init-file=~/mysql-init option:

   shell> mysqld_safe --init-file=~/mysql-init &
   

   The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.

5. You should be able to connect using the new password.

Alternatively, on any platform, you can set the new password using the mysql client(but this approach is less secure):

1. Stop mysqld and restart it with the --skip-grant-tables --user=root options (Windows users omit the --user=root portion).
2. Connect to the mysqld server with this command:

   shell> mysql -u root

3. Issue the following statements in the mysql client:

   mysql> UPDATE mysql.user SET Password=PASSWORD('
   newpwd')
       ->                   WHERE User='root';
   mysql> FLUSH PRIVILEGES;

   Replace “newpwd” with the actual root password that you want to use.

4. You should be able to connect using the new password.