Re: CHANGE MYSQL DATADIR TO ANOTHER HARDISK
Shut down mysql, move all the datafiles to /data and create a symbolic link from the current directory to /data. This should work. regards anandkl On 8/18/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, On my debian system, i have two hardisk. I want to change the mysql datadir to another hardisk (/dev/cciss/c0d1p1 /data ). Please tell me how to do this. /dev/cciss/c0d0p1 34G / /dev/cciss/c0d1p1 68G /data -- Krishna Chandra Prajapati
Re: MySQL Error Number 1045 Access denied
Hi Andrew, I think he means re-install MySQL and provide a root password (you said when you originally installed it that you didn't give it a password.) After you've done this, try mysql from the command-line explicitly telling it to use the user root: mysql --user=root --password The --password here tells mysql to prompt you for a password, or alternatively you can provide the password on the command-line too: mysql --user=root --password=password Once you can login via the command-line there's no reason why the Admin GUI shouldn't be able to login too. Regards, Andy Quoting AndrewMcHorney [EMAIL PROTECTED]: How does one done this? At 08:41 AM 8/17/2008, Sreekanth CHAVA wrote: Hi Andrew As per your Question , you can test with the below solution :- Try to reconfigure MySQL by providing the root password and then try to run the administer GUI. This should solve the problem. CHAVA On Sat, Aug 16, 2008 at 10:22 PM, AndrewMcHorney [EMAIL PROTECTED]wrote: Hello I am still getting the MySQL Error Number 1045 Access denied error message when running the adminstrator gui. I attempted to start mysql from the dos command line via mysql and I am getting the following. ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO). I am logging in as root, locahost and no password because I did not enter one when I installed the software. I will be changing that. For the command line I just entered mysql and a return. How can I fix this. I am off for the week and I would like to get this working so I can create a database or 2. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sreekanth CHAVA No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 270.6.4/1616 - Release Date: 8/16/2008 5:12 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Shellam Business Systems Architect Network Mail NetServe Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sub query help
Dear All,
I am interested in performing a sub query that removes duplicate records from
a temporary table prior to pushing the data to the main table.
I am not sure if it is possible and thought I would ask prior to the endeavor.
I currently use php to perform this operation but is really bogs down the db.
Here is the code example i am using now to perform this function:
// select the name from the dedup row
$sql = select name,id from dedup;
$ret = run_query($sql);
// loop through and check if the name exists in the lead table
while($ddRow=mysql_fetch_assoc($ret)){
$sql2 = select name,id from leads where name LIKE '$ddupRow[name]';
$ret2 = run_query($sql2);
// if rows returned is greater than 0 delete from the dedup table
if(myqsl_num_rows( $ret2 ) 0){
$del_sql = DELETE FROM dedup WHERE id = '$ddRow[id]';
run_query($del_sql);
// else insert the new record
}else{
$iQ=INSERT into leads (name) VALUES ( '$ddupRow[name]');
; run_query($iQ);
}
}
I am wondering if it is possible to do this with one query without the double
hit to the DB?
Or ,if possible, perform one large query and remove the duplicates in one blow?
Any thought or suggestions would be very much appreciated.
Kind Regards,
Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Error Number 1045 Access denied
Alternatively you can try this if the box is unix/linux : sudo mysqld_safe --skip-grant-tables --user=root mysql -u root Change your root password Restart mysql after this. Cheers Sharad Andy Shellam wrote: Hi Andrew, I think he means re-install MySQL and provide a root password (you said when you originally installed it that you didn't give it a password.) After you've done this, try mysql from the command-line explicitly telling it to use the user root: mysql --user=root --password The --password here tells mysql to prompt you for a password, or alternatively you can provide the password on the command-line too: mysql --user=root --password=password Once you can login via the command-line there's no reason why the Admin GUI shouldn't be able to login too. Regards, Andy Quoting AndrewMcHorney [EMAIL PROTECTED]: How does one done this? At 08:41 AM 8/17/2008, Sreekanth CHAVA wrote: Hi Andrew As per your Question , you can test with the below solution :- Try to reconfigure MySQL by providing the root password and then try to run the administer GUI. This should solve the problem. CHAVA On Sat, Aug 16, 2008 at 10:22 PM, AndrewMcHorney [EMAIL PROTECTED]wrote: Hello I am still getting the MySQL Error Number 1045 Access denied error message when running the adminstrator gui. I attempted to start mysql from the dos command line via mysql and I am getting the following. ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO). I am logging in as root, locahost and no password because I did not enter one when I installed the software. I will be changing that. For the command line I just entered mysql and a return. How can I fix this. I am off for the week and I would like to get this working so I can create a database or 2. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sreekanth CHAVA No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 270.6.4/1616 - Release Date: 8/16/2008 5:12 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Error Number 1045 Access denied
FYI,
I don't think this was sent to the list, forgive me if it was and I
didn't see it.
Andy
--
You probably don't have to reinstall it. If you never gave root a
password, just login as Andy says below and, when prompted for the
password, just press the ENTER key. Unless you gave root a password and
forgot it, you should find yourself in the mysql program. Enter
something like:
update mysql.user set password = password('new pwd') where user = 'root';
flush privileges;
There are other ways to change a pwd, but I use that since it's easier
for me to remember one way.
Jim
On Mon, Aug 18, 2008 at 3:07 AM, Andy Shellam
[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi Andrew,
I think he means re-install MySQL and provide a root password (you
said when you originally installed it that you didn't give it a
password.)
After you've done this, try mysql from the command-line explicitly
telling it to use the user root:
mysql --user=root --password
The --password here tells mysql to prompt you for a password, or
alternatively you can provide the password on the command-line too:
mysql --user=root --password=password
Once you can login via the command-line there's no reason why the
Admin GUI shouldn't be able to login too.
Regards,
Andy
Quoting AndrewMcHorney [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]:
How does one done this?
At 08:41 AM 8/17/2008, Sreekanth CHAVA wrote:
Hi Andrew
As per your Question , you can test with the below
solution :-
Try to reconfigure MySQL by providing the root
password and then
try to run the
administer GUI. This should solve the problem.
CHAVA
On Sat, Aug 16, 2008 at 10:22 PM, AndrewMcHorney
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]wrote:
Hello
I am still getting the MySQL Error Number 1045 Access
denied error message
when running the adminstrator gui. I attempted to start
mysql from the dos
command line via mysql and I am getting the following.
ERROR 1045 (28000):
Access denied for user 'ODBC'@'localhost' (using
password: NO). I am
logging in as root, locahost and no password because I
did not enter one
when I installed the software. I will be changing that.
For the command line
I just entered mysql and a return. How can I fix this. I
am off for the week
and I would like to get this working so I can create a
database or 2.
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Sreekanth CHAVA
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 270.6.4/1616 - Release
Date: 8/16/2008 5:12 PM
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Andy Shellam
Business Systems Architect
Network Mail
NetServe Support
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Error Number 1045 Access denied
If you are desperate, you can also start mysqld with --skip-grant-tables
option.
Then use mysql, update root password using the update statement that Jim
wrote about.
-Paul
On Mon, 2008-08-18 at 17:25 +0100, Andy Shellam wrote:
FYI,
I don't think this was sent to the list, forgive me if it was and I
didn't see it.
Andy
--
You probably don't have to reinstall it. If you never gave root a
password, just login as Andy says below and, when prompted for the
password, just press the ENTER key. Unless you gave root a password and
forgot it, you should find yourself in the mysql program. Enter
something like:
update mysql.user set password = password('new pwd') where user = 'root';
flush privileges;
There are other ways to change a pwd, but I use that since it's easier
for me to remember one way.
Jim
On Mon, Aug 18, 2008 at 3:07 AM, Andy Shellam
[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi Andrew,
I think he means re-install MySQL and provide a root password (you
said when you originally installed it that you didn't give it a
password.)
After you've done this, try mysql from the command-line explicitly
telling it to use the user root:
mysql --user=root --password
The --password here tells mysql to prompt you for a password, or
alternatively you can provide the password on the command-line too:
mysql --user=root --password=password
Once you can login via the command-line there's no reason why the
Admin GUI shouldn't be able to login too.
Regards,
Andy
Quoting AndrewMcHorney [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]:
How does one done this?
At 08:41 AM 8/17/2008, Sreekanth CHAVA wrote:
Hi Andrew
As per your Question , you can test with the below
solution :-
Try to reconfigure MySQL by providing the root
password and then
try to run the
administer GUI. This should solve the problem.
CHAVA
On Sat, Aug 16, 2008 at 10:22 PM, AndrewMcHorney
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]wrote:
Hello
I am still getting the MySQL Error Number 1045 Access
denied error message
when running the adminstrator gui. I attempted to start
mysql from the dos
command line via mysql and I am getting the following.
ERROR 1045 (28000):
Access denied for user 'ODBC'@'localhost' (using
password: NO). I am
logging in as root, locahost and no password because I
did not enter one
when I installed the software. I will be changing that.
For the command line
I just entered mysql and a return. How can I fix this. I
am off for the week
and I would like to get this working so I can create a
database or 2.
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Sreekanth CHAVA
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 270.6.4/1616 - Release
Date: 8/16/2008 5:12 PM
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Andy Shellam
Business Systems Architect
Network Mail
NetServe Support
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul Choi [EMAIL PROTECTED]
Intermittent Can't connect to MySQL server on 'host' (4) (2003) after 20+ days uptime
Greetings to all, I am having a weird issue with MySQL that I can't solve. We are getting intermittent client connection errors code 2003 to the database server for 10mins seemingly at random, and after 20+ days of uptime. Unfortunately, I have not been able to correlate these connection problems with any other queries, jobs, etc, so I was hoping someone here might be able to help me out. The problem is as follows. Seemingly at random, the master suddenly stops accepting connections, and the clients return connection error 2003, indicating the master did not respond in a timely manner. This goes on for about 10 minutes, at which point the master starts accepting connections again, without any human input. This happened at 4am on Sunday morning for example, so it healed itself before I could get myself out of bed and comprehend the situation, let alone connect somewhere and try and fix it. We are seeing this happen about 4 or 5 times a week for the last 2 weeks, and there seems to be no pattern as to the time or date. Sometimes it happens twice in one day, and then disappears for 4 days. There was no spike in activity as far as we can tell, and the CPU and network usage were stable at about 2% and 4% of capacity respectively. Also, we have slow query log turned on and set to 1sec, and there are no queries anywhere near the gaps in connection. We are running MySQL 5.0.44 on a single master on its on hardware, with a replication slave on a different machine. We have a write through memcached setup in front my MySQL, which handles the majority of the requests, so MySQL is seeing about 20 to 30 ops (select, inserts, updates) per second on average. All of this is running on Amazon EC2 instances, and have dedicated boxes (we are running the 64bit Large Instance, which is supposed to be a dedicated virtual box with 2 CPU, 2 cores apiece and 8G of ram, with 1.5/2G free.) We then have two other machines that run the front end web servers running PHP 5.1.6 and load balancers, which connect to the database when the cache doesnt have the required information. I did not post this to the PHP section since it seems like a more general issue with the server as opposed to the clients. After the second time it happened, we switched out our AWS hardware in hopes that it was a hardware fluke, but to no avail. The problem reared its uglyhead 3 days later. We doubt it is the internal Amazon network since the external monitoring of the box continues to work and spit out information, and no other box is showing similar connection symptoms. Also, all of our boxes are in the same Amazon Zone, which implies that they are in the same colo. This makes me think that a combination of our configuration and queries are causing the trouble. I checked the archives, but it seems that the people who encountered this error saw it during setup/configuration, and not randomly after 30 days of uptime. I doubt anyone has the answer, so I was hoping someone could help me understand the best way to debug this problem in order to find the reason for these random outages. Thanks in advance for any and all help! Pieter de Zwart
Re: Intermittent Can't connect to MySQL server on 'host' (4) (2003) after 20+ days uptime
HI Pieter I have a suggestion.this might not be very helpful Try to reconfigure the connections between the client and Mysql server where the problem exists.and then try to notice the uptime and logs of the server. CHAVA On Mon, Aug 18, 2008 at 12:00 PM, Pieter de Zwart [EMAIL PROTECTED] wrote: Greetings to all, I am having a weird issue with MySQL that I can't solve. We are getting intermittent client connection errors code 2003 to the database server for 10mins seemingly at random, and after 20+ days of uptime. Unfortunately, I have not been able to correlate these connection problems with any other queries, jobs, etc, so I was hoping someone here might be able to help me out. The problem is as follows. Seemingly at random, the master suddenly stops accepting connections, and the clients return connection error 2003, indicating the master did not respond in a timely manner. This goes on for about 10 minutes, at which point the master starts accepting connections again, without any human input. This happened at 4am on Sunday morning for example, so it healed itself before I could get myself out of bed and comprehend the situation, let alone connect somewhere and try and fix it. We are seeing this happen about 4 or 5 times a week for the last 2 weeks, and there seems to be no pattern as to the time or date. Sometimes it happens twice in one day, and then disappears for 4 days. There was no spike in activity as far as we can tell, and the CPU and network usage were stable at about 2% and 4% of capacity respectively. Also, we have slow query log turned on and set to 1sec, and there are no queries anywhere near the gaps in connection. We are running MySQL 5.0.44 on a single master on its on hardware, with a replication slave on a different machine. We have a write through memcached setup in front my MySQL, which handles the majority of the requests, so MySQL is seeing about 20 to 30 ops (select, inserts, updates) per second on average. All of this is running on Amazon EC2 instances, and have dedicated boxes (we are running the 64bit Large Instance, which is supposed to be a dedicated virtual box with 2 CPU, 2 cores apiece and 8G of ram, with 1.5/2G free.) We then have two other machines that run the front end web servers running PHP 5.1.6 and load balancers, which connect to the database when the cache doesnt have the required information. I did not post this to the PHP section since it seems like a more general issue with the server as opposed to the clients. After the second time it happened, we switched out our AWS hardware in hopes that it was a hardware fluke, but to no avail. The problem reared its uglyhead 3 days later. We doubt it is the internal Amazon network since the external monitoring of the box continues to work and spit out information, and no other box is showing similar connection symptoms. Also, all of our boxes are in the same Amazon Zone, which implies that they are in the same colo. This makes me think that a combination of our configuration and queries are causing the trouble. I checked the archives, but it seems that the people who encountered this error saw it during setup/configuration, and not randomly after 30 days of uptime. I doubt anyone has the answer, so I was hoping someone could help me understand the best way to debug this problem in order to find the reason for these random outages. Thanks in advance for any and all help! Pieter de Zwart -- Sreekanth CHAVA
Re: Intermittent Can't connect to MySQL server on 'host' (4) (2003) after 20+ days uptime
Regardless of who has implemented the network and the status of provided monitoring tools, this has all the look and feel of intermittent network issues. I would run an independant network scan (maybe nmap?) from one of the affected clients to the affected host and I bet you will find that the same fluctuations occur on other ports. On Mon, Aug 18, 2008 at 3:22 PM, Sreekanth CHAVA [EMAIL PROTECTED] wrote: HI Pieter I have a suggestion.this might not be very helpful Try to reconfigure the connections between the client and Mysql server where the problem exists.and then try to notice the uptime and logs of the server. CHAVA On Mon, Aug 18, 2008 at 12:00 PM, Pieter de Zwart [EMAIL PROTECTED] wrote: Greetings to all, I am having a weird issue with MySQL that I can't solve. We are getting intermittent client connection errors code 2003 to the database server for 10mins seemingly at random, and after 20+ days of uptime. Unfortunately, I have not been able to correlate these connection problems with any other queries, jobs, etc, so I was hoping someone here might be able to help me out. The problem is as follows. Seemingly at random, the master suddenly stops accepting connections, and the clients return connection error 2003, indicating the master did not respond in a timely manner. This goes on for about 10 minutes, at which point the master starts accepting connections again, without any human input. This happened at 4am on Sunday morning for example, so it healed itself before I could get myself out of bed and comprehend the situation, let alone connect somewhere and try and fix it. We are seeing this happen about 4 or 5 times a week for the last 2 weeks, and there seems to be no pattern as to the time or date. Sometimes it happens twice in one day, and then disappears for 4 days. There was no spike in activity as far as we can tell, and the CPU and network usage were stable at about 2% and 4% of capacity respectively. Also, we have slow query log turned on and set to 1sec, and there are no queries anywhere near the gaps in connection. We are running MySQL 5.0.44 on a single master on its on hardware, with a replication slave on a different machine. We have a write through memcached setup in front my MySQL, which handles the majority of the requests, so MySQL is seeing about 20 to 30 ops (select, inserts, updates) per second on average. All of this is running on Amazon EC2 instances, and have dedicated boxes (we are running the 64bit Large Instance, which is supposed to be a dedicated virtual box with 2 CPU, 2 cores apiece and 8G of ram, with 1.5/2G free.) We then have two other machines that run the front end web servers running PHP 5.1.6 and load balancers, which connect to the database when the cache doesnt have the required information. I did not post this to the PHP section since it seems like a more general issue with the server as opposed to the clients. After the second time it happened, we switched out our AWS hardware in hopes that it was a hardware fluke, but to no avail. The problem reared its uglyhead 3 days later. We doubt it is the internal Amazon network since the external monitoring of the box continues to work and spit out information, and no other box is showing similar connection symptoms. Also, all of our boxes are in the same Amazon Zone, which implies that they are in the same colo. This makes me think that a combination of our configuration and queries are causing the trouble. I checked the archives, but it seems that the people who encountered this error saw it during setup/configuration, and not randomly after 30 days of uptime. I doubt anyone has the answer, so I was hoping someone could help me understand the best way to debug this problem in order to find the reason for these random outages. Thanks in advance for any and all help! Pieter de Zwart -- Sreekanth CHAVA -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB Restore using 3rd party tools
Hello, Couple of questions - experienced MSSQL developer but new to mySQL. I have 2 servers on a local network. I did a dump from one to its local drive, and I want to restore it to the 2nd server. 1. is it possible to do this with 3rd party tools such as SQLyog or EMS Sql Manager? When I try to navigate to the .sql file, it is looking at my local drives and not the drives of the server I have the connection to. 2. If it is possible, does the .sql file have to be on the local disk of the server I am trying to restore to, or should it work from a mapped drive of a local network. Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Restore using 3rd party tools
Hi Dan, I cannot speak for EMS or SQLyog, but certainly with the MySQL Administrator you create the backup files on your client PC (the PC running the tool) and then when you restore it, you select the file from your local PC and it sends the SQL commands required to restore the database to the server. If you have a dump file on the server as in your case, there are a couple of options: 1. Copy the dump file to the 2nd server and on the 2nd server restore it using mysql --username=root --password /path/to/dump_file.sql or 2. Connect to the 2nd server from your first box and restore the local file: mysql --host=2nd server --username=root --password /path/to/dump_file.sql Substitute the required options in the mysql command. This is assuming a UNIX / Linux system. Windows it's the same idea, although you could run your 3rd-party tool on your 1st server but connect to the 2nd server - then you have access to the file directly using your tool. Regards, Andy Dan O'Keefe wrote: Hello, Couple of questions - experienced MSSQL developer but new to mySQL. I have 2 servers on a local network. I did a dump from one to its local drive, and I want to restore it to the 2nd server. 1. is it possible to do this with 3rd party tools such as SQLyog or EMS Sql Manager? When I try to navigate to the .sql file, it is looking at my local drives and not the drives of the server I have the connection to. 2. If it is possible, does the .sql file have to be on the local disk of the server I am trying to restore to, or should it work from a mapped drive of a local network. Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHANGE MYSQL DATADIR TO ANOTHER HARDISK
Better yet, either do a cp -a (if GNU cp) or mv, then modify your my.cnf to reflect the new location. Your file permissions and ownership will have been preserved if you used either of these commands, and it's good to go. On Mon, 2008-08-18 at 11:59 +0530, Ananda Kumar wrote: Shut down mysql, move all the datafiles to /data and create a symbolic link from the current directory to /data. This should work. regards anandkl On 8/18/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, On my debian system, i have two hardisk. I want to change the mysql datadir to another hardisk (/dev/cciss/c0d1p1 /data ). Please tell me how to do this. /dev/cciss/c0d0p1 34G / /dev/cciss/c0d1p1 68G /data -- Krishna Chandra Prajapati -- Paul Choi [EMAIL PROTECTED]
