Archive

Archive for the ‘Database’ Category

Mysql database connection from php in different conditions

In this topic we will discuss about different types of mysql database connection in php.

The general configuration we do to connect a mysql database from PHP is

<?

$dbhost = ‘localhost’;
$dbuser = ‘root’;
$dbpass = ‘password’;

$conn = mysql_connect(‘$dbhost’,’ $dbuser’, ‘$dbpass’) or die (‘Error connecting to mysql’);

?>

So in my discussions below I will be changing the connection statement only in order to connect successfully.Rest will be the same as you know.

Conditions-1:mysql doesnot have password.

Lets say your mysql server does no have any password.So your coinnection statement will be

$conn = mysql_connect(‘$dbhost’,’ $dbuser’, ”) or die (‘Error connecting to mysql’);

Condition-2:-Mysql port changed

Mysql runs on a default port 3306.But for security reasons people change the mysql port.In our case let’s say I have changed the mysql port to 3309.Then my connect statement will be

$conn = mysql_connect(‘$dbhost:3309′,’ $dbuser’, ‘$dbpass’) or die (‘Error connecting to mysql’);

Condition-3:port same but mysql socket file changed.

If in some servers you need to change the default socket file location i.e /var/lib/mysql/mysql.sock

to some other location like /var/lib/mysql-databases/mysqld2/mysql.sock then how would be our connection statement?We get different location for mysql socket file as sometimes we change the mysql default data directory.

$conn = mysql_connect(‘$dbhost:/var/lib/mysql-databases/mysqld2/mysql.sock’,’ $dbuser’, ‘$dbpass’) or die (‘Error connecting to mysql’);

Condition-4:Port and socket changed

Lets say we changed both port and socket for some security reasons.port cahnged to 3309 and socket to /var/lib/mysql-databases/mysqld2/mysql.sock What will be our connection statement?

$conn = mysql_connect(‘$dbhost:/var/lib/mysql-databases/mysqld2/mysql.sock’,’ $dbuser’, ‘$dbpass’) or die (‘Error connecting to mysql’);

Advertisements
Categories: Database Tags: , ,

How To Run Multiple instances of Mysql Server on a Single Linux Server

Yesterday I faced a problem where our developer team wanted to have two mysql server with different root passwords.The problem was I am having only one redhat linux test server.After lots of thinking I searched for “multiple instances of mysql server” in Google.I found lots of different article on this and all are little bit confusing.Finally after lots of testing I succeeded in getting up and running two different instances of mysql in singleserver.Below are the steps you can follow to do the same.

Step-1:Login to your server as root user

Step-2:Login to your mysql server as root and execute the following command


mysql>GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'secret';
mysql>FLUSH PRIVILEGES;

It means we are giving shutdown privileges to the user “multi_admin”

step-3:come out of mysql prompt and stop mysql server.To stop you can execute

[root@localhost ~]#	service mysql stop
or
[root@localhost ~]#/sbin/service mysql stop

Step-4:Now we need to locate the mysql config file “my.cnf” and change it as per our requirement which is located at /etc/my.cnf

N:B:-If you are not finding the my.cnf file then go to your mysql installation folder.In my case it is /usr/share/mysql.You will find four configuration files like “my-small.cnf”,” my-medium.cnf”,” my-large.cnf “,” my-huge.cnf”.You can take any one and put it in /etc and rename it to my.cnf.

You can also execute the command below to get all of the above file.

[root@localhost ~]# find / -name mysql*.cnf

Step-5:open my.cnf and comment out the following lines in [mysql] section

# The MySQL server
[mysqld]
#port           = 3306
#socket         = /var/run/mysql/mysql.sock
# Change following line if you want to store your database elsewhere
#datadir        = /var/lib/mysql

Step-6:Now just below [mysqld] section put the following lines

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = admin123

Step -7:Then to create our desired two instances add the below lines after [mysql_multi] section. We have to define different unique values for each server instance or else the data and socket files for both servers collide and as a result mysql server will fail to start or your data could be corrupted.

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysql1.err

[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql
log-error  = /var/log/mysql2.err

Step-8:Save the configuration file and now create the files and folders as we have mentioned in the above configuration.To do that execute the following commands.

[root@localhost ~]#  mkdir -P /var/lib/mysql-databases/myqld2

Step-9:For mysql instance 1 we are using the defaults for previously running mysql server But we need to Create the data directory for instance2.Create it by

[root@localhost ~]# mkdir /var/lib/mysql-databases/mysql

Step-10:Copy the mysql database files from the original instance to the second instances database directory and change the ownership of the data directory to the mysql user so the instance can read them.

[root@localhost ~]# cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld2/mysql
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql-databases

Step-11:Now the two instances are ready to run.We can start them by the folowing command

[root@localhost ~]#mysqld_multi start
To view the status of the instances you can run
[root@localhost ~]# mysqld_multi report
Output:
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

You can see that the mysqld_multi script has started multiple mysql processes with the following commands.

ps -e | grep "mysql"

To stop both instances just execute the below command.

rhys@linux-n0sm:~> mysqld_multi stop

We are also able to control individual instances by referring to the assigned number.

rhys@linux-n0sm:~> mysqld_multi stop 1

To verify this
rhys@linux-n0sm:~> mysqld_multi report

Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running

rhys@linux-n0sm:~> mysqld_multi start 1
rhys@linux-n0sm:~> mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

Here you need to remember that both the instances running on different port and also having different socket files.You can refer to the topic how to connect to mysql in php in case you are facing any problem to connect ot mysql through some script

Set ,reset ,change or recover mysql passwords without worry

For system and database admins changing myssql password is a essential and risky task.It is very simple but yet very difficult sometimes. You may have the fear of data loss.Also sometimes you will get error like “ mysqladmin:  connect to server at ‘localhost’ failed error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’. “ So I will discuss in this topic on how to deal with mysql password problems.

There are 2 types of scenario you may face like

1.Changing Password for Normal user

2.Changing root password

To change password of a normal user:-

1.If you know the current password for a normal user then you can cahnge it by following command

 $mysqladmin -u username -p oldpassword password newpassword

EX:-To change password for a user called “kirti” and having password “parida” execute

$mysqladmin -u kirti -p  parida password ranjan

2.if you dont know the password of the normal user then login as root and follow the steps to reset the password.

Step-1:Login to the MySQL server, type the following command at the shell prompt:

$ mysql -u root -p 

Step-2: Use the mysql database (type commands at the mysql> prompt):

 mysql> use mysql; 

Step-3:Change password for a user:

 mysql> update user set password=PASSWORD("newpass") where User='username';

 EX:- mysql> update user set password=PASSWORD("parida123") where User='kirti';

step-4 Reload privileges:

 mysql> flush privileges;
mysql> quit

Note:So what we have done here is MySQL stores usernames and passwords in the user table inside the MySQL database. So we are updating a password using the above method to update or change passwords.

This method you need to use while using PHP or Perl scripting.

To Change the root password:-

N:B-Here I will discuss how to deal with root user.You have to remember that it is not the root user of your system it is root user of mysql database .Both are completely different.

1.If you know the current password for root user then you can cahnge it by using mysqladmin command from your shell

 $ mysqladmin -u root -p oldpassword newpass 

After changing the new password if you will encounter the following error

 Enter password:

 If you get...

 mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

then you have to follow the instructions below on how to recover your MySQL password.

2.If you know the current password for root user then you can cahnge it by updating the mysql user table(Recommended as it works in all situation and easy )

step-1: Login to the MySQL server, type the following command at the shell prompt:

 $ mysql -u root -p

step-2 :Use the mysql database (type commands at the mysql> prompt):

 mysql> use mysql;

step-3: Change password for a user:

 mysql> update user set password=PASSWORD("newpass") where User='root';

step-4: Reload privileges:

 mysql> flush privileges;
mysql> quit

Recover root user Password

This is the most critical part of your learnin as this deals with the worst possible scenario in your work.So please carefully follow the instructions.

You can recover a MySQL database server password with the following five easy steps:

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for a password.

Step # 3: Connect to the MySQL server as the root user.

Step # 4: Set a new root password.

Step # 5: Exit and restart the MySQL server.

Here are the commands you need to type for each step.As the commands directly affect system services so you need to login as root user(system root user) in your system.

Step # 1 : Stop the MySQL service:

 # /etc/init.d/mysql stop

Output:

 Stopping MySQL database server: mysqld.

Step # 2: Start the MySQL server w/o password:

 # mysqld_safe --skip-grant-tables &

Output:

 [1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to the MySQL server using the MySQL client:

 # mysql -u root

Output:

 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: -fedora_12-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Set a new MySQL root user password:

 mysql> use mysql;
mysql> update user set password=PASSWORD("newpassword") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop the MySQL server:

 # /etc/init.d/mysql stop

Output:

 Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Start the MySQL server and test it:

 # /etc/init.d/mysql start
# mysql -u root -p

Quick way to Reset Forgotten MySQL Root Password

Have you ever forgotten the root password on one of your MySQL servers? If you have forgotten then read below how to reset it quickly..

Log in as root in the system and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

 $mysqld_safe --skip-grant-tables 

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

 $mysql --user=root mysql

 mysql>update user set password=PASSWORD('password') where user='root';
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.