Archive for the ‘Mysql’ Category

h1

Manually delete records for any domain from plesk psa database

September 27, 2008

Manually delete records for any domain from psa database ( this is plesk windows with psa in mysql database, but should work on msaccess db too )

cd %plesk_dir%\mysql\bin && mysql.exe -u admin -P8306 psa -p

PASSWORD

use psa \G

delete from db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=’example.com’));
delete from data_bases where dom_id=(SELECT id FROM domains where name=’example.com’);
delete from mail where id=(SELECT id FROM domains where name=’example.com’);
delete from subdomains where id=(SELECT id FROM domains where name=’example.com’);
delete from dns_recs where id=(SELECT id FROM domains where name=’example.com’);
delete from domains where id=(SELECT id FROM domains where name=’example.com’);
delete from hosting where dom_id=(SELECT id FROM sys_users where home=’C:/inetpub/vhosts//example.com’);
delete from FROM sys_users where home=’C:/inetpub/vhosts//example.com’;
delete from dns_zone where displayName=’example.com’;

exit;

iisweb /delete “example.com”
echo y | del “C:\Inetpub/vhosts\example.com”
echo y | del “C:\Program Files\SWsoft\Plesk\Mail Servers\Mail Enable\Postoffices\example.com”
echo y | del “C:\Program Files\SWsoft\Plesk\Mail Servers\Mail Enable\config\Postoffices\example.com”

“C:\Program Files\SWsoft\Plesk\admin\bin\domain.exe” -r example.com
“C:\Program Files\SWsoft\Plesk\admin\bin\DNSMng.exe” restart

Besides you might need to delete post office manually from mailenable.msc -> postoffices and a few other things in GUI

Advertisements
h1

MySQL ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

August 25, 2008

The other day I got this error on one of our servers. Fantastico module in cpanel was showing the error: Unable to connect to mysql database.  While mysql service was running fine, the root user was unable to login to mysql database. I initially tried to reset the mysql root password like https://rhcelinuxguide.wordpress.com/2008/08/08/reset-mysql-root-password/   but that did not help.  The real issue was permission on the folder /var/lib/mysql/mysql .  It is supposed to be 711 , while it was  751.  That fixed it.

==================================================================

root@server1 [~]# mysql
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
root@server1 [~]#

root@server1 [/var/lib/mysql/mysql]# ls -lhd /var/lib/mysql
drwxr-x–x 179 mysql mysql 12K Aug 25 01:44 /var/lib/mysql/

root@server1 [/var/lib/mysql/mysql]# chmod  711 /var/lib/mysql/mysql

root@server1 [/var/lib/mysql/mysql]# ls -lhd /var/lib/mysql/mysql
drwx–x–x 2 mysql mysql 4.0K Aug 25 01:32 /var/lib/mysql/mysql/

root@server1 [/var/lib/mysql/mysql]# service mysql restart
Shutting down MySQL..

[  OK  ]
Starting MySQL                                             [  OK  ]
root@server1 [/var/lib/mysql/mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a-community MySQL Community Edition (GPL)Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. 

mysql> quit
Bye
root@server1 [/var/lib/mysql/mysql]#

==================================================================
Worked for me !
If above steps does not work, you can try this:
root@server1 [~]# /etc/init.d/mysqld stop
root@server1 [~]# safe_mysqld –skip-grant-tables &
root@server1 [~]# mysql
root@server1 [~]# USE mysql
mysql>  UPDATE user SET password=password(“new root passwd”) WHERE user=”root”;
mysql> flush privileges;
mysql> exit 

root@server1 [~]# killall mysqld mysql

root@server1 [~]# /etc/init.d/mysqld start

You can check the root mysql password in /root/.my.cnf  ( assuming you have set it up)

Sample /root/.my.cnf
#######################

root@server1 [~]# cat /root/.my.cnf
[client]
user=”root”
pass=”V1<)4K5.8″
root@server1 [~]#

h1

Reset MySQL Root Password

August 8, 2008

[root@dedicated08 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 31792 mysql 3u IPv4 434089 TCP *:mysql (LISTEN)
[root@dedicated08 ~]# kill -9 31792
[root@dedicated08 ~]# /etc/init.d/mysql start
Mysql Started

[root@dedicated08 ~]# mysql
Access denied for user ‘root’@’localhost’ (using password: NO)

[root@dedicated08 ~]# /etc/init.d/mysql stop

[root@dedicated08 ~]# mysqld_safe –skip-grant-tables &

[root@dedicated08 ~]#
[root@dedicated08 ~]# mysql -u root
mysql>
mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where
User=’root’;
mysql> flush privileges;
mysql> quit

NEW-ROOT-PASSWORD above is whatever password you want to set.

[root@dedicated08 ~]# # /etc/init.d/mysql stop
[root@dedicated08 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 31792 mysql 3u IPv4 434089 TCP *:mysql (LISTEN)
[root@dedicated08 ~]# kill -9 31792
[root@dedicated08 ~]# fuser -k /usr/bin/mysqld_safe ( in
case you see mysql process is still running )

[root@dedicated08 ~]# vi /root/.my.cnf
[root@dedicated08 ~]# cat /root/.my.cnf
[client]
user=”root”
pass=”NEW-ROOT-PASSWORD”
[root@dedicated08 ~]#

[root@dedicated08 ~]# service mysql restart

[root@dedicated08 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-standard-log

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

mysql> quit
Bye
[root@dedicated08 ~]#

h1

mysql databases backup script on cpanel servers

July 23, 2008

Daily mysql backup with 7 days retention period.

root@server1 [~]# cat /scripts/mbak.sh
#!/bin/bash
Time=`date “+%Y.%m.%d-%A”`
baksrc=/var/lib/mysql
bakdst=/backup/mysqlbackup
dumpdb=/usr/bin/mysqldump

yum -y dialog nmap ncurses

mkdir -p $bakdst/$Time

{ for I in 10 20 30 40 50 60 70 80 90 100 ; do
echo $I
sleep 1
done
echo; } | dialog –gauge ” Starting to backup all databases ” 6 70 0

ls -lhd  $baksrc/*_* | awk {‘print $9}’ | cut -d/ -f5 | grep -v ib_* > /root/mysqldd-list

for db in `cat /root/mysqldd-list` ;
do

{ for I in 10  50  100 ; do
echo $I
sleep 1
done
echo; } | dialog –gauge ” Creating backup of $db ” 6 70 0

$dumpdb $db > $bakdst/$Time/$db.sql  2> $bakdst/$Time/error.log

{ for I in 10  50  100 ; do
echo $I
sleep 1
done
echo; } | dialog –gauge ” Database : $db backup completed  ” 6 70 0

echo ” ……………..  $db backed up on `date “+%Y.%m.%d.%T-%A”`  …………………………

.. ”
sleep 3
echo ” …………….. working on the next db backup ………………………”
sleep 3
echo ” ……………… Taking some rest before that ……………………….”
sleep 3
done

$dumpdb  mysql > $bakdst/$Time/mysql.sql 2> $bakdst/$Time/error.log

echo ” All Databases backup up successfully to folder $bakdst/$Time/ ”
echo ” Check for any errors at : $bakdst/$Time/error.log ”
wall ” All Databases backup up successfully to folder $bakdst/$Time/ ”
wall ” Check for any errors at : $bakdst/$Time/error.log or the database backup ”
echo ” removing older backups”
sleep 2
echo ” hold on, let me work for 3 to 10 mins”
/usr/bin/find $bakdst -type d -maxdepth 1 -mtime +7 -exec rm -fr {} \;
echo ” …………….. Finishing up the entire process ”
sleep 3
echo ” …………….. Successfully Done ……………………….”
wall ” ***** Note Again **** Check for any errors at : $bakdst/$Time/error.log or the database backup ”
exit 0

root@server1 [~]#

root@server1 [~]# chmod +x /scripts/mbak.sh ( make the script executable )
root@server1 [~]# /bin/sh /scripts/mbak.sh (run script to make database backups )
h1

List all mysql databases on server

July 17, 2008

List all mysql databases on server

##############################

root@server1 [~]#  mysql -bse “show databases”

h1

Neat Tips and Tricks

July 2, 2006
h1

Making MySQL Database Connections with PHP

June 6, 2006

Title:       Making MySQL Database Connections with PHP
Contributor: Randall Goguen (aka Ranman)
Last Update: Wednesday April 24 08:16 EDT 2002
 
Example 1:

# Selecting data from all your columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 2:

# Selecting data from all your columns based on a column value.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col = '$your_col'")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 3:

# Selecting data from all your columns based on 3 column values.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col = '$your_col' AND (this_col = '$this'
 OR that_col = '$that')")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 4:

# Selecting data from your column if any of the values are in the IN list.

# col_id IN(1,2,3,4) is the more efficient than:
# col_id = 1 OR col_id = 2 OR col_id = 3 OR col_id = 4

# Note: expr NOT IN (value,...) is the same as NOT (expr IN (value,...)).

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col IN ('this','that','other')")
 or die("Unable to select Database");

Example 5:

# Using mysql_insert_id()

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "INSERT INTO your_table (your_col,
 another_col, any_other_cols)
 VALUES ('$your_col', '$another_col', '$any_other_cols')");
$this_id = mysql_insert_id();

$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE col_id = '$this_id'")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 6:

# Selecting data from all your columns then using a column
# as OPTION values in a FORMs SELECT tag.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col = '$your_col' AND (this_col = '$this'
 OR that_col = '$that')")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
?><SELECT NAME="name"><?
while ($row = mysql_fetch_array($result)) {
$j = $row["your_col"];
echo "<OPTION VALUE="$j">$j";
}
?></SELECT>

Example 7:

# Counting how many results there are in a column.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT count(*) FROM your_table")
 or die("Unable to select Database");
$count = mysql_result($result, 0);

Example 8:

# Selecting the column with the maximum value.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT max(col_id) FROM your_table")
 or die("Unable to select Database");
$max = mysql_result($result,0,"max(col_id)");
echo $max;

Example 9:

# Showing all Tables in a Database.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SHOW TABLES");
while ($row = mysql_fetch_array($result)) {
echo "$row[0]<br />";
}

Example 10:

# Showing all Columns in a Table .

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "DESCRIBE your_table");
while ($row = mysql_fetch_array($result)) {
echo "$row[0] $row[1]<br />";
}

Example 11:

# Adding a table to a database.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "CREATE TABLE your_table (
col_id INT NOT NULL auto_increment,
your_col SMALLINT NOT NULL,
another_col VARCHAR(255) NOT NULL,
PRIMARY KEY(col_id),
KEY(your_col),
KEY(another_col))")
 or die("Unable to Create Table");

Example 12:

# Adding data to your columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "INSERT INTO your_table (your_col, another_col,
 any_other_cols)
 VALUES ('$your_col', '$another_col', '$any_other_cols')");

Example 13:

# Making changes to your columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "UPDATE your_table SET your_col = '$your_col',
 another_col = '$another_col', any_other_cols = '$any_other_cols'
 WHERE uid = $uid");

Example 14:

# Deleting data from a columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "DELETE FROM your_table WHERE col_id = '$col_id'");

Example 15:

# See if a table exists.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "DESCRIBE your_table");
if ($result) { echo "Yes!"; } else { echo "No!"; }

Example 16:

# Checking if a table has empty results.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("YourDB", "SELECT * FROM your_table");
if (mysql_fetch_array($result)) { echo "No!"; } else { echo "Yes!"; }

Example 17:

# Creating a Databae.

$user = "root";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_create_db("testDB")
 or die("Unable to Create Database");

Example 18:

# Deleting a Database.

$user = "root";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_drop_db("testDB")
 or die("Unable to Drop Database");
# Note that 'mysql_drop_db("testDB")' did just that. It
# sent the database to binary heaven and is now deleted.

Example 19:

# Convert a TIMESTAMP.

$user = "UserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_query("SELECT DATE_FORMAT($row[created_date],
 '%W  %M %D %Y  %H:%i%p')") or die("Unable to Convert Date");
echo mysql_result($result, 0);

Example 20:

# Some basic Authentication code.

$user = "YourUserName";
$pass = "YourPassword";
$host = "localhost";
mysql_connect($host, $user, $pass)
 or die("Unable to connect to SQL server");

$result = mysql_db_query("YourDB", "INSERT INTO your_table (username,
 password) VALUES ('$username', PASSWORD('$password')");
$this_id = mysql_insert_id();

$result = mysql_db_query("YourDB", "SELECT * FROM your_table
 WHERE username = '$PHP_AUTH_USER' AND password = PASSWORD('$PHP_AUTH_PW')")
 or die("Unable to select Database");
if ($PHP_AUTH_USER == mysql_result($result,0,username)) {
echo "Yes!"; } else { echo "No!"; }

Example 21:

# Debugging a error gettting results.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_select_db("nonexistentDB");
echo mysql_errno().": ".mysql_error()."<br />";
$conn = mysql_query("SELECT * FROM nonexistent_table");
echo mysql_errno().": ".mysql_error();

http://www.huggle.com/forums/index.php?f=42 
 
Anyone who wishes to make additions or changes to this
MySQL Tutorial email them to webmaster@linuxguruz.org

This document is Copyright (c) 1999, 2000 by LinuxGuruz

Return to the LinuxGuruz MySQL Tutorials Page
Return to the LinuxGuruz Main Page