Doubt regarding Mysqlsump

Doubt regarding Mysqlsump

am 07.06.2011 08:00:01 von Adarsh Sharma

Dear all,

Is it possible to take backups of a table or complete database without
stopping the application that continuously inserts and select data from
the tables.

For taking complete backup of a database I follow the below steps :-

1. First stop the application that insert & modifies tables.
2. Mysqldump command to backup the tables.
3. After complete backup , start the application.

I know Mysql-Replication helps a lot to solve this issue but I have not
any extra server to configure it.

So , Can I solve this issue without Replication so that I don't need to
stop my application & I must have consistent backups too.

Please note that size of databases may be more than 100GB


Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Doubt regarding Mysqlsump

am 07.06.2011 08:06:39 von Claudio Nanni - TomTom

--20cf301cc3c2f0e39b04a5190523
Content-Type: text/plain; charset=ISO-8859-1

Hi Adarsh,

I think this is the best option for you:
http://www.percona.com/docs/wiki/percona-xtrabackup:start

There is also a commercial alternative, InnoBackup, but I imagine you like
it free.

Cheers

Claudio
On Jun 7, 2011 7:59 AM, "Adarsh Sharma" wrote:
> Dear all,
>
> Is it possible to take backups of a table or complete database without
> stopping the application that continuously inserts and select data from
> the tables.
>
> For taking complete backup of a database I follow the below steps :-
>
> 1. First stop the application that insert & modifies tables.
> 2. Mysqldump command to backup the tables.
> 3. After complete backup , start the application.
>
> I know Mysql-Replication helps a lot to solve this issue but I have not
> any extra server to configure it.
>
> So , Can I solve this issue without Replication so that I don't need to
> stop my application & I must have consistent backups too.
>
> Please note that size of databases may be more than 100GB
>
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>

--20cf301cc3c2f0e39b04a5190523--

Re: Doubt regarding Mysqlsump

am 07.06.2011 08:07:09 von Mark Carson

Hi

We use the --single-transaction switch thinking it does less locking or waiting
for a required table lock. You then get a snapshot without stopping.

Subject should have included the word 'hot'? Looking forward to other suggestions.

Mark

On 2011/06/07 08:00, Adarsh Sharma wrote:
> Dear all,
>
> Is it possible to take backups of a table or complete database without stopping
> the application that continuously inserts and select data from the tables.
>
> For taking complete backup of a database I follow the below steps :-
>
> 1. First stop the application that insert & modifies tables.
> 2. Mysqldump command to backup the tables.
> 3. After complete backup , start the application.
>
> I know Mysql-Replication helps a lot to solve this issue but I have not any
> extra server to configure it.
>
> So , Can I solve this issue without Replication so that I don't need to stop my
> application & I must have consistent backups too.
>
> Please note that size of databases may be more than 100GB
>
>
> Thanks
>

--
Mark Carson
Managing
Integrated Product Intelligence CC (CK95/35630/23)
EMail : mcarson@ipi.co.za/ (secondary:mcarson@pixie.co.za)
Physical Address : 34 Spanish Galliard, Mooikloof, Pretoria, South Africa
snailmail : P.O. Box 36095 Menlo Park 0102, South Africa
Tel. +27 12 996 1193/1815 Fax : +27 86 672 7012 Cell : +27 83 260 8515


This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended
only for use of the addressee. If you are not the addressee, or the person
responsible for delivering it to the person addressed, you may not copy or
deliver this to anyone else. If you received this e-mail by mistake, please
do not make use of it, nor disclose it's contents to anyone. Thank you for
notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED
IN THIS ELECTRONIC MAIL IS PROVIDED "AS IS" WITHOUT WARRANTY REPRESENTATION
OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT
LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND
THE USE OF THIS DOCUMENT.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Doubt regarding Mysqlsump

am 07.06.2011 08:07:55 von Nilnandan Joshi

--0016e6dcd27da7706004a5190b58
Content-Type: text/plain; charset=ISO-8859-1

Can you tell us which storage engine you are using?

On Tue, Jun 7, 2011 at 11:30 AM, Adarsh Sharma wrote:

> Dear all,
>
> Is it possible to take backups of a table or complete database without
> stopping the application that continuously inserts and select data from the
> tables.
>
> For taking complete backup of a database I follow the below steps :-
>
> 1. First stop the application that insert & modifies tables.
> 2. Mysqldump command to backup the tables.
> 3. After complete backup , start the application.
>
> I know Mysql-Replication helps a lot to solve this issue but I have not any
> extra server to configure it.
>
> So , Can I solve this issue without Replication so that I don't need to
> stop my application & I must have consistent backups too.
>
> Please note that size of databases may be more than 100GB
>
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=nilnandan@gmail.com
>
>

--0016e6dcd27da7706004a5190b58--

Re: Doubt regarding Mysqlsump

am 07.06.2011 08:09:44 von Adarsh Sharma

--------------060505030700050907050205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Both MYISAM & Innodb Engines are used.

Thanks

Nilnandan Joshi wrote:
> Can you tell us which storage engine you are using?
>
> On Tue, Jun 7, 2011 at 11:30 AM, Adarsh Sharma
> > wrote:
>
> Dear all,
>
> Is it possible to take backups of a table or complete database
> without stopping the application that continuously inserts and
> select data from the tables.
>
> For taking complete backup of a database I follow the below steps :-
>
> 1. First stop the application that insert & modifies tables.
> 2. Mysqldump command to backup the tables.
> 3. After complete backup , start the application.
>
> I know Mysql-Replication helps a lot to solve this issue but I
> have not any extra server to configure it.
>
> So , Can I solve this issue without Replication so that I don't
> need to stop my application & I must have consistent backups too.
>
> Please note that size of databases may be more than 100GB
>
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=nilnandan@gmail.com
>
>


--------------060505030700050907050205--

Re: Doubt regarding Mysqlsump

am 07.06.2011 08:27:19 von Johan De Meersman

----- Original Message -----
> From: "Claudio Nanni"
>
> I think this is the best option for you:
> http://www.percona.com/docs/wiki/percona-xtrabackup:start

I must say, I still haven't looked very well at xtrabackup. How does it take consistent backups of MyISAM tables? I didn't think that was possible without shutting down the applications writing to them.


Adarsh, a vital piece of information is the storage engine you're using. Are your tables InnoDB or MyISAM? Afaik (see my question above :-p ) your approach is the only one that will allow you to take a consistent backup of MyISAM tables; for InnoDB tables xtrabackup should work fine.


Another option that might be of interest would be taking only one full backup per week or month using your current procedure, and taking daily backups of the binary logs between those. Still no 100% guarantee of consistency, but everything is in there without load on your database - except for the log writing overhead of course - and you can do point-in-time restores up to the individual statement if you feel like it. Zmanda ZRM Server is one solution that provides that level of backup.

Come to think of it, you could use your current procedure for backing up the binlogs consistently, too:
1. shut application
2. issue "flush logs" to switch to a new binlog
3. restart application
4. backup all but the active binlog at your leisure for a consistent backup at that point in time

That would enable you to do a quick daily backup with minimal application downtime, and the added benefit of point-in-time restores. The downside of that approach is increased restore time: you need to first restore the latest full backup, and then incrementally apply each of the binlog backups to the point you need to restore to.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Doubt regarding Mysqlsump

am 07.06.2011 09:00:32 von Adarsh Sharma

--------------060501010904050101050309
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Johan De Meersman wrote:
> ----- Original Message -----
>
>> From: "Claudio Nanni"
>>
>> I think this is the best option for you:
>> http://www.percona.com/docs/wiki/percona-xtrabackup:start
>>
>
> I must say, I still haven't looked very well at xtrabackup. How does it take consistent backups of MyISAM tables? I didn't think that was possible without shutting down the applications writing to them.
>
I am working with both MyISAM & Innodb tables.

>
> Adarsh, a vital piece of information is the storage engine you're using. Are your tables InnoDB or MyISAM? Afaik _*(see my question above :-p )*_
Not getting U'r point marked as bold & Underline

> your approach is the only one that will allow you to take a consistent backup of MyISAM tables; for InnoDB tables xtrabackup should work fine.
>
I am not using xtrabackup but I think --single-transaction & -q options
may solved this issue
I know this is valid only for Innodb Tables but anyway's I have both
MyISAM & Innodb tables but only Innodb tables size is increasing in
seconds and MyISAM table size increased after hours.

Can U Please explain me what happened when I issue the mysqldump command
with options --single-transaction & -q option on Innodb tables of size
greater than 100 GB & on the other hand my application continuously
insert data in the tables.

Compressed backup should take more than 2 or more Hours.
> Another option that might be of interest would be taking only one full backup per week or month using your current procedure, and taking daily backups of the binary logs between those. Still no 100% guarantee of consistency, but everything is in there without load on your database - except for the log writing overhead of course - and you can do point-in-time restores up to the individual statement if you feel like it. Zmanda ZRM Server is one solution that provides that level of backup.
>

Please note that I don't have my bin-log enabled.

I can enable it if required.

Thanks
> Come to think of it, you could use your current procedure for backing up the binlogs consistently, too:
> 1. shut application
> 2. issue "flush logs" to switch to a new binlog
> 3. restart application
> 4. backup all but the active binlog at your leisure for a consistent backup at that point in time
>
> That would enable you to do a quick daily backup with minimal application downtime, and the added benefit of point-in-time restores. The downside of that approach is increased restore time: you need to first restore the latest full backup, and then incrementally apply each of the binlog backups to the point you need to restore to.
>
>
>
Yet I am not able to find the finalize the answer of the original question.

Thanks


--------------060501010904050101050309--

Re: Doubt regarding Mysqlsump

am 07.06.2011 10:27:00 von Johan De Meersman

----- Original Message -----
> From: "Adarsh Sharma"
>
> Not getting U'r point marked as bold & Underline

I checked up in the mean time, and it does not make a truly consistent backup of MyISAM - it locks all tables - yes, ALL tables - and then copies the files. Given that MyISAM doesn't support transactions, that means that any "transactions" (that is, sets of related queries) in progress will be copied in the state they were, and the backup may contain inconsistent data like unreferenced rows.

> I am not using xtrabackup but I think --single-transaction & -q
> options may solved this issue
> I know this is valid only for Innodb Tables but anyway's I have
> both MyISAM & Innodb tables but only Innodb tables size is increasing in
> seconds and MyISAM table size increased after hours.

-q is good, but not relevant to your problem. It simply prevents buffering of the output, which speeds up the dump a bit.

--single-transaction, as you say, only affects InnoDB tables.

--lock-all-tables will give you the same behaviour for MyISAM as xtrabackup, but is likely to be incompatible with --single-transaction.

Neither of those solve your consistency issue.

> Can U Please explain me what happened when I issue the mysqldump
> command with options --single-transaction & -q option on Innodb tables of
> size> greater than 100 GB & on the other hand my application continuously
> insert data in the tables.

The size isn't particularly relevant; --single-transaction basically "snapshots" the InnoDB tables so you copy all tables from the same point-in-time. I'm not 100% sure, but I think this is a pure read lock, so the inserts continue to happen; your backup process will not see them, but other processes will. This ensures a consistent InnoDB backup.

> Please note that I don't have my bin-log enabled.
> I can enable it if required.

It is ovbiously necessary if you choose to do binlog backups :-p

> Yet I am not able to find the finalize the answer of the original
> question.

The answer to "is there a way to take consistent backups of MyISAM tables without stopping the application" is "no, there is not". The binlog backup strategy I roughly outlined earlier can dramatically decrease your application's downtime, however.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Doubt regarding Mysqlsump

am 09.06.2011 11:03:24 von Karen Abgarian

--Boundary_(ID_eCcodYSW4HUaPnjFjbpgxg)
Content-type: text/plain; CHARSET=US-ASCII
Content-transfer-encoding: 7BIT

Hello, comments inline. Regards, Karen.
>
>
> I checked up in the mean time, and it does not make a truly consistent backup of MyISAM - it locks all tables - yes, ALL tables - and then copies the files. Given that MyISAM doesn't support transactions, that means that any "transactions" (that is, sets of related queries) in progress will be copied in the state they were, and the backup may contain inconsistent data like unreferenced rows.

This however means that the resulting snapshot will be consistent. The fact of taking a backup really cannot create more consistency than what the application has by design. If the application inserts related rows in say two related tables without transactions, it kind of assumes that it is prepared to handle the case when updates make it to one table and not to the other. If it is not prepared to handle it, it means that any client crash will create inconsistent data, not to mention the server crash.

>
>> I am not using xtrabackup but I think --single-transaction & -q
>> options may solved this issue
> --single-transaction, as you say, only affects InnoDB tables.
>
> --lock-all-tables will give you the same behaviour for MyISAM as xtrabackup, but is likely to be incompatible with --single-transaction.
>
> Neither of those solve your consistency issue.
>
Somebody mentioned the xtrabackup to me. How different is it from another wrapper program with the ibbackup at the core? I will be very curious to know if there exists a product that does NOT do the following at the basic technical level:

- FLUSH TABLES WITH READ LOCK; <-- locks all tables
- take backup

With the products of this kind, all that can be done is to accept that it does what it does. Which does not exclude the options like reducing the downtime with say split-mirror-like snapshots or creating a replica to experience the downtime there.

If I let my fantasy run wild about what an alternative could be, it will be something like this:

- run a cycle for all MyISAM tables
- for each table, lock it for writes
- note the position in the binary log, record this position
- read the table into the backup
- release the lock

To recover this, do the following:

- restore the table backups
- start reading binlog from the first recorded position, record by record
- determine which table the record is a change fore
- if the position is greater than the position recorded for the table, apply the change, otherwise don't.

The result will be the locks taken per table, which is equally bad compared to the normal access pattern for MyISAM tables.

> The answer to "is there a way to take consistent backups of MyISAM tables without stopping the application" is "no, there is not". The binlog backup strategy I roughly outlined earlier can dramatically decrease your application's downtime, however.
>

If we think about it, a MyISAM table by definition is a table, the consistency of which is based on whole table locks. Considering that the backup is really a request to read the whole table, locking out everybody else, the question about backup can well be rephrased as "is a MyISAM table a MyISAM table?" The answer to this is a firm yes :-).



--Boundary_(ID_eCcodYSW4HUaPnjFjbpgxg)--

Re: Doubt regarding Mysqlsump

am 09.06.2011 13:58:00 von Johan De Meersman

----- Original Message -----
> From: "Karen Abgarian"
>
> This however means that the resulting snapshot will be consistent.
> The fact of taking a backup really cannot create more consistency
> than what the application has by design. If the application inserts
> related rows in say two related tables without transactions, it kind
> of assumes that it is prepared to handle the case when updates make
> it to one table and not to the other. If it is not prepared to
> handle it, it means that any client crash will create inconsistent
> data, not to mention the server crash.

True, but I have never seen an application that checks for inconsistency in it's tables. Making sure all users have stopped using the app ensures no in-flight transactions, and then you have a consistent database - save application crashes, of course, as you mention. MyISAM was never designed for data consistency, so it is pretty hard to get, indeed. The original question was asking for consistent backups, so I'm trying to give the best there is :-)

Like the physics jokes go, "assume a perfectly flat surface without friction..."

> Somebody mentioned the xtrabackup to me. How different is it from
> another wrapper program with the ibbackup at the core? I will be

Not very, I suspect.

> very curious to know if there exists a product that does NOT do the
> following at the basic technical level:
>
> - FLUSH TABLES WITH READ LOCK; <-- locks all tables
> - take backup

You only need to FLUSH TABLES if you want the datafiles instead of an SQL dump. In the latter case, you can just lock the tables you will backup. A read lock will give you an unchanging view of the locked tables, both on MyISAM and InnoDB. On MyISAM, that read lock will by default prevent writes, with the exception of inserts if there are no holes in the table; for InnoDB a read lock wil simply give a view at the then-current SCN and allow further writes to simply go on.

If the database was in a consistent state at the time of the lock, you can take a consistent backup at that point.

> With the products of this kind, all that can be done is to accept
> that it does what it does. Which does not exclude the options like
> reducing the downtime with say split-mirror-like snapshots or
> creating a replica to experience the downtime there.

Correct, but with the same caveats about consistency.

> The result will be the locks taken per table, which is equally bad
> compared to the normal access pattern for MyISAM tables.

Yeps. Which is why you try to

* take a full backup of the db in a consistent state (say, once a month) and switch the binary logs;
* then switch the binary logs at a point where the database is in a consistent state, and copy all but the newly active one.

You can then restore the full snapshot, and be sure that at the end of each sequential binlog set you apply, it is again consistent.

As I indicated, ZRM is one product which does exactly that - save for ensuring the DB is consistent, of course - that's up to you.


> If we think about it, a MyISAM table by definition is a table, the
> consistency of which is based on whole table locks. Considering

A single table is always consistent. Data inconsistency occurs in sets of interrelated tables, in other words, on the database level.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Doubt regarding Mysqlsump

am 09.06.2011 19:55:24 von Karen Abgarian

Hi! Inline, again.
On Jun 9, 2011, at 4:58 AM, Johan De Meersman wrote:

> True, but I have never seen an application that checks for inconsistency in it's tables. Making sure all users have stopped using the app ensures no in-flight transactions, and then you have a consistent database - save application crashes, of course, as you mention. MyISAM was never designed for data consistency, so it is pretty hard to get, indeed. The original question was asking for consistent backups, so I'm trying to give the best there is :-)
>
> Like the physics jokes go, "assume a perfectly flat surface without friction..."

Ha, I remember that one about friction :) What I meant to say about internal application consistency checks, the applications are known to skip that, but they better have a plan about what to do if say the application goes down. Because we have the client/server model, it kind of implies the N:1 relationship between the applications and the database. The inconsistency can be created by one of the N application pieces, so it is quite likely.

>> very curious to know if there exists a product that does NOT do the
>> following at the basic technical level:
>>
>> - FLUSH TABLES WITH READ LOCK; <-- locks all tables
>> - take backup
>
> You only need to FLUSH TABLES if you want the datafiles instead of an SQL dump. In the latter case, you can just lock the tables you will backup. A read lock will give you an unchanging view of the locked tables, both on MyISAM and InnoDB. On MyISAM, that read lock will by default prevent writes, with the exception of inserts if there are no holes in the table; for InnoDB a read lock wil simply give a view at the then-current SCN and allow further writes to simply go on.
>
> If the database was in a consistent state at the time of the lock, you can take a consistent backup at that point.

FLUSH TABLES really is a way to take the lock on all tables (with an added bonus of flushing the stuff to disk). How else would you lock the tables, list them in the LOCK TABLES statement? That could be a lot of tables.

About the datafiles, it is a valid point. However, the value of dumping versus copying files goes down with the increase of data volume. If we have a large table, recovery from a dump would mean reinserting all data, that is, redoing all the insertion work since the application was created. This may take a while. There is also index maintenance which could take quite a lot of time. The file copy thing will actually not work with InnoDB at all, so ibbackup really is the only way to go about that.

>
>> The result will be the locks taken per table, which is equally bad
>> compared to the normal access pattern for MyISAM tables.
>
> Yeps. Which is why you try to
>
> * take a full backup of the db in a consistent state (say, once a month) and switch the binary logs;
> * then switch the binary logs at a point where the database is in a consistent state, and copy all but the newly active one.
>
> You can then restore the full snapshot, and be sure that at the end of each sequential binlog set you apply, it is again consistent.
>
> As I indicated, ZRM is one product which does exactly that - save for ensuring the DB is consistent, of course - that's up to you.
>

What is described here, is a simple recovery plan. That could be done relatively easy with scripts (if it matters, that is what I did). Perhaps the ZRM does something else that qualifies it as a product.

As we know it, taking a full backup while the database is in consistent state may lock everybody out for a very long time. What placing a lock on the tables will do is this:

- lock tables T1, T2, T3
- wait until the table T4 is unlocked (possibly a while), then place a lock on it
- continue with the rest of tables until all are locked.

This means that if there is something big going on with T4, the lock will take quite a long time. Meanwhile, everybody will wait. The wait will also have to continue until we took a snapshot of all tables, of course. So it is the time of placing the lock plus the time of taking the snapshot.

What I described before, is the procedure based on taking individual table locks, avoiding the need to lock the whole database and experiencing the large wait.


>
>> If we think about it, a MyISAM table by definition is a table, the
>> consistency of which is based on whole table locks. Considering
>
> A single table is always consistent. Data inconsistency occurs in sets of interrelated tables, in other words, on the database level.
>

I would not be so sure about "always". If someone fires an update of every row in the table and aborts it half way, half of the records in the table will be updated and half of them will not be. That's something called "statement-level consistency", which also could render the table inconsistent within itself. MyISAM table, that is.

Personally, to me the using of MyISAM tables means: I do not care about table consistency, I am prepared to lose data, I do not expect heavy concurrent access, I do not expect handling no-downtime operation, and so forth.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Doubt regarding Mysqlsump

am 09.06.2011 20:59:24 von Jerry Schwartz



>
>A single table is always consistent. Data inconsistency occurs in sets of
>interrelated tables, in other words, on the database level.
>
[JS] Not even a single table is always consistent (unless there is
transactions). Consider a single transaction that consists of two steps:

1. Delete record A
2. Add record B

Now consider

1. Delete record A
-Backup starts-
2. Add record B

You have no idea whether or not record B will be in your backup.

Worse things can happen, of course:

1. Delete record A
!!KABOOM!!

The data in the table is not going to be consistent. You'd have to analyze the
data to find out what did and didn't happen before the crash, back out step 1,
and re-run that transaction in the application.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Doubt regarding Mysqlsump

am 09.06.2011 21:11:43 von mussatto

------=_20110609121143_88089
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit



On Thu, June 9, 2011 11:59, Jerry Schwartz wrote:
>

>
>>
>>A single table is
always consistent. Data inconsistency occurs in sets of
>>interrelated tables, in other words, on the database level.
>>
> [JS] Not even a single table is always consistent
(unless there is
> transactions). Consider a single transaction
that consists of two steps:
>
> 1. Delete record A
> 2. Add record B
>
> Now consider
>
> 1. Delete record A
> -Backup starts-
> 2. Add
record B
>
> You have no idea whether or not record B
will be in your backup.
>
> Worse things can happen, of
course:
>
> 1. Delete record A
> !!KABOOM!!
>
> The data in the table is not going to be consistent.
You'd have to analyze
> the
> data to find out what did
and didn't happen before the crash, back out
> step 1,
>
and re-run that transaction in the application.
>
>
> Regards,
>
> Jerry Schwartz
Ah, LOCK TABLE
which is the myisam  equivalent of "tranactions". 
That will deal with the problem of backup in the middle yes?  
If that won't work then you do need transactions.  IMHO.

------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

------=_20110609121143_88089--

MySQL loses password?

am 12.06.2011 14:50:22 von Tim Thorburn

Hi all,

I came across something strange today on my dev machine and thought I'd
see if anyone here has run into a similar problem. To begin, my dev
machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
Today when I tried to log into the server using the old MySQL GUI tools
as root, I got an error number 1045 Access denied for user
'root'@'localhost' (using password: YES). This is odd because I've not
changed the root password on my dev machine in years. When I tried to
goto a site on this same machine, Apache throws the following message:

mysqlnd cannot connect to MySQL 4.1+ using the old insecure
authentication. Please use an administration tool to reset your
password with the command SET PASSWORD =
PASSWORD('your_existing_password'). This will store a new, and more
secure, hash value in mysql.user. If this user is used in other scripts
executed by PHP 5.2 or earlier you might need to remove the
old-passwords flag from your my.cnf file

This is the first time I've seen such a message, or had MySQL randomly
stop accepting my root password. I'll likely be doing a complete
uninstall and reinstall in a few hours on this machine, but thought I'd
ask here to see if anyone had any thoughts as to why this happened, and
how I might correct it? If at all possible, I'd prefer to not have to
do an uninstall/reinstall as I wisely hadn't backed up a few tables I
was working on over the last couple days.

Any thoughts?

Thanks in advance,
-Tim

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL loses password?

am 12.06.2011 14:59:39 von Johan De Meersman

If it's recently been upgraded or had an old pre-upgrade backup restored, that's not a major surprise; and surely you won't have ben running mysql on a win7 for several years :-)

In brief, mysql changed password encryptions between 4.1 and 5.0, for various reasons. The old password scheme is still supported, but iird the default setting for that in newer versions is off, yielding you the error you are reporting.

Of course, if none of that is the case, you may have been hacked; but it seems somehow strange that a hacker would bother to install oldstyle passwords.

The error message you provide also mentions the oldpasswd flag for PHP < 5.2 - also worth looking at.


----- Original Message -----
> From: "Tim Thorburn"
> To: mysql@lists.mysql.com
> Sent: Sunday, 12 June, 2011 2:50:22 PM
> Subject: MySQL loses password?
>
> Hi all,
>
> I came across something strange today on my dev machine and thought
> I'd
> see if anyone here has run into a similar problem. To begin, my dev
> machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
> Today when I tried to log into the server using the old MySQL GUI
> tools
> as root, I got an error number 1045 Access denied for user
> 'root'@'localhost' (using password: YES). This is odd because I've
> not
> changed the root password on my dev machine in years. When I tried
> to
> goto a site on this same machine, Apache throws the following
> message:
>
> mysqlnd cannot connect to MySQL 4.1+ using the old insecure
> authentication. Please use an administration tool to reset your
> password with the command SET PASSWORD =
> PASSWORD('your_existing_password'). This will store a new, and more
> secure, hash value in mysql.user. If this user is used in other
> scripts
> executed by PHP 5.2 or earlier you might need to remove the
> old-passwords flag from your my.cnf file
>
> This is the first time I've seen such a message, or had MySQL
> randomly
> stop accepting my root password. I'll likely be doing a complete
> uninstall and reinstall in a few hours on this machine, but thought
> I'd
> ask here to see if anyone had any thoughts as to why this happened,
> and
> how I might correct it? If at all possible, I'd prefer to not have
> to
> do an uninstall/reinstall as I wisely hadn't backed up a few tables I
> was working on over the last couple days.
>
> Any thoughts?
>
> Thanks in advance,
> -Tim
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL loses password?

am 12.06.2011 15:08:57 von Tim Thorburn

There's been no upgrade or old pre-upgrade on this machine. I say the
same dev password for years, as it is the same password I've used on my
dev machines throughout the years.

This machine started off as a fresh install of the OS several months
ago. The only version of MySQL ever on this particular machine is
5.5.11. My confusion is mostly centered around the "it worked fine on
Friday, then Saturday happened" issue. I've run a complete virus scan
and found nothing, as well as several spyware/malware scans - it's in
the process of running a scan from a rescue disc, so I'll know if
there's anything else afterward.

Seems odd anyone would bother hacking into this dev machine that's
barely connected to the Internet.

On 6/12/2011 8:59 AM, Johan De Meersman wrote:
> If it's recently been upgraded or had an old pre-upgrade backup restored, that's not a major surprise; and surely you won't have ben running mysql on a win7 for several years :-)
>
> In brief, mysql changed password encryptions between 4.1 and 5.0, for various reasons. The old password scheme is still supported, but iird the default setting for that in newer versions is off, yielding you the error you are reporting.
>
> Of course, if none of that is the case, you may have been hacked; but it seems somehow strange that a hacker would bother to install oldstyle passwords.
>
> The error message you provide also mentions the oldpasswd flag for PHP< 5.2 - also worth looking at.
>
>
> ----- Original Message -----
>> From: "Tim Thorburn"
>> To: mysql@lists.mysql.com
>> Sent: Sunday, 12 June, 2011 2:50:22 PM
>> Subject: MySQL loses password?
>>
>> Hi all,
>>
>> I came across something strange today on my dev machine and thought
>> I'd
>> see if anyone here has run into a similar problem. To begin, my dev
>> machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
>> Today when I tried to log into the server using the old MySQL GUI
>> tools
>> as root, I got an error number 1045 Access denied for user
>> 'root'@'localhost' (using password: YES). This is odd because I've
>> not
>> changed the root password on my dev machine in years. When I tried
>> to
>> goto a site on this same machine, Apache throws the following
>> message:
>>
>> mysqlnd cannot connect to MySQL 4.1+ using the old insecure
>> authentication. Please use an administration tool to reset your
>> password with the command SET PASSWORD =
>> PASSWORD('your_existing_password'). This will store a new, and more
>> secure, hash value in mysql.user. If this user is used in other
>> scripts
>> executed by PHP 5.2 or earlier you might need to remove the
>> old-passwords flag from your my.cnf file
>>
>> This is the first time I've seen such a message, or had MySQL
>> randomly
>> stop accepting my root password. I'll likely be doing a complete
>> uninstall and reinstall in a few hours on this machine, but thought
>> I'd
>> ask here to see if anyone had any thoughts as to why this happened,
>> and
>> how I might correct it? If at all possible, I'd prefer to not have
>> to
>> do an uninstall/reinstall as I wisely hadn't backed up a few tables I
>> was working on over the last couple days.
>>
>> Any thoughts?
>>
>> Thanks in advance,
>> -Tim
>>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL loses password?

am 12.06.2011 21:33:15 von Claudio Nanni - TomTom

--20cf303f695cc8600804a588dfd5
Content-Type: text/plain; charset=ISO-8859-1

This happens when you use an old client / library. Can you try from command
line? Also issue: mysql --version
Claudio
On Jun 12, 2011 3:09 PM, "Tim Thorburn" wrote:
> There's been no upgrade or old pre-upgrade on this machine. I say the
> same dev password for years, as it is the same password I've used on my
> dev machines throughout the years.
>
> This machine started off as a fresh install of the OS several months
> ago. The only version of MySQL ever on this particular machine is
> 5.5.11. My confusion is mostly centered around the "it worked fine on
> Friday, then Saturday happened" issue. I've run a complete virus scan
> and found nothing, as well as several spyware/malware scans - it's in
> the process of running a scan from a rescue disc, so I'll know if
> there's anything else afterward.
>
> Seems odd anyone would bother hacking into this dev machine that's
> barely connected to the Internet.
>
> On 6/12/2011 8:59 AM, Johan De Meersman wrote:
>> If it's recently been upgraded or had an old pre-upgrade backup restored,
that's not a major surprise; and surely you won't have ben running mysql on
a win7 for several years :-)
>>
>> In brief, mysql changed password encryptions between 4.1 and 5.0, for
various reasons. The old password scheme is still supported, but iird the
default setting for that in newer versions is off, yielding you the error
you are reporting.
>>
>> Of course, if none of that is the case, you may have been hacked; but it
seems somehow strange that a hacker would bother to install oldstyle
passwords.
>>
>> The error message you provide also mentions the oldpasswd flag for PHP<
5.2 - also worth looking at.
>>
>>
>> ----- Original Message -----
>>> From: "Tim Thorburn"
>>> To: mysql@lists.mysql.com
>>> Sent: Sunday, 12 June, 2011 2:50:22 PM
>>> Subject: MySQL loses password?
>>>
>>> Hi all,
>>>
>>> I came across something strange today on my dev machine and thought
>>> I'd
>>> see if anyone here has run into a similar problem. To begin, my dev
>>> machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
>>> Today when I tried to log into the server using the old MySQL GUI
>>> tools
>>> as root, I got an error number 1045 Access denied for user
>>> 'root'@'localhost' (using password: YES). This is odd because I've
>>> not
>>> changed the root password on my dev machine in years. When I tried
>>> to
>>> goto a site on this same machine, Apache throws the following
>>> message:
>>>
>>> mysqlnd cannot connect to MySQL 4.1+ using the old insecure
>>> authentication. Please use an administration tool to reset your
>>> password with the command SET PASSWORD =
>>> PASSWORD('your_existing_password'). This will store a new, and more
>>> secure, hash value in mysql.user. If this user is used in other
>>> scripts
>>> executed by PHP 5.2 or earlier you might need to remove the
>>> old-passwords flag from your my.cnf file
>>>
>>> This is the first time I've seen such a message, or had MySQL
>>> randomly
>>> stop accepting my root password. I'll likely be doing a complete
>>> uninstall and reinstall in a few hours on this machine, but thought
>>> I'd
>>> ask here to see if anyone had any thoughts as to why this happened,
>>> and
>>> how I might correct it? If at all possible, I'd prefer to not have
>>> to
>>> do an uninstall/reinstall as I wisely hadn't backed up a few tables I
>>> was working on over the last couple days.
>>>
>>> Any thoughts?
>>>
>>> Thanks in advance,
>>> -Tim
>>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>

--20cf303f695cc8600804a588dfd5--

Re: MySQL loses password?

am 13.06.2011 11:55:14 von Tim Thorburn

--------------080608010808010206040405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ok, ran several scans on my dev PC. Unsurprisingly, nothing was found.
Trying to use command line produces the error:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)

The same Access denied for user 'root'@'localhost' (using password: YES)
happens when trying to use MySQL Workbench 5.2.33 CE. phpMyAdmin gives
an error #2000 Cannot log in to the MySQL server.

mysql --version returns:
mysql Ver 14.14 Distrib 5.5.11, for Win64 (x86)

I'm guessing I'll be uninstalling/removing MySQL completely tomorrow and
doing a clean install of 5.5.13. Although it would be handy to know
what caused this problem in the first place. It's more than a little
concerning when Friday everything works, shutdown computer for the
night, log in again Saturday to find MySQL doesn't so much like my
passwords anymore.


On 6/12/2011 3:33 PM, Claudio Nanni wrote:
>
> This happens when you use an old client / library. Can you try from
> command line? Also issue: mysql --version
> Claudio
>
> On Jun 12, 2011 3:09 PM, "Tim Thorburn" > > wrote:
> > There's been no upgrade or old pre-upgrade on this machine. I say the
> > same dev password for years, as it is the same password I've used on my
> > dev machines throughout the years.
> >
> > This machine started off as a fresh install of the OS several months
> > ago. The only version of MySQL ever on this particular machine is
> > 5.5.11. My confusion is mostly centered around the "it worked fine on
> > Friday, then Saturday happened" issue. I've run a complete virus scan
> > and found nothing, as well as several spyware/malware scans - it's in
> > the process of running a scan from a rescue disc, so I'll know if
> > there's anything else afterward.
> >
> > Seems odd anyone would bother hacking into this dev machine that's
> > barely connected to the Internet.
> >
> > On 6/12/2011 8:59 AM, Johan De Meersman wrote:
> >> If it's recently been upgraded or had an old pre-upgrade backup
> restored, that's not a major surprise; and surely you won't have ben
> running mysql on a win7 for several years :-)
> >>
> >> In brief, mysql changed password encryptions between 4.1 and 5.0,
> for various reasons. The old password scheme is still supported, but
> iird the default setting for that in newer versions is off, yielding
> you the error you are reporting.
> >>
> >> Of course, if none of that is the case, you may have been hacked;
> but it seems somehow strange that a hacker would bother to install
> oldstyle passwords.
> >>
> >> The error message you provide also mentions the oldpasswd flag for
> PHP< 5.2 - also worth looking at.
> >>
> >>
> >> ----- Original Message -----
> >>> From: "Tim Thorburn" > >
> >>> To: mysql@lists.mysql.com
> >>> Sent: Sunday, 12 June, 2011 2:50:22 PM
> >>> Subject: MySQL loses password?
> >>>
> >>> Hi all,
> >>>
> >>> I came across something strange today on my dev machine and thought
> >>> I'd
> >>> see if anyone here has run into a similar problem. To begin, my dev
> >>> machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
> >>> Today when I tried to log into the server using the old MySQL GUI
> >>> tools
> >>> as root, I got an error number 1045 Access denied for user
> >>> 'root'@'localhost' (using password: YES). This is odd because I've
> >>> not
> >>> changed the root password on my dev machine in years. When I tried
> >>> to
> >>> goto a site on this same machine, Apache throws the following
> >>> message:
> >>>
> >>> mysqlnd cannot connect to MySQL 4.1+ using the old insecure
> >>> authentication. Please use an administration tool to reset your
> >>> password with the command SET PASSWORD =
> >>> PASSWORD('your_existing_password'). This will store a new, and more
> >>> secure, hash value in mysql.user. If this user is used in other
> >>> scripts
> >>> executed by PHP 5.2 or earlier you might need to remove the
> >>> old-passwords flag from your my.cnf file
> >>>
> >>> This is the first time I've seen such a message, or had MySQL
> >>> randomly
> >>> stop accepting my root password. I'll likely be doing a complete
> >>> uninstall and reinstall in a few hours on this machine, but thought
> >>> I'd
> >>> ask here to see if anyone had any thoughts as to why this happened,
> >>> and
> >>> how I might correct it? If at all possible, I'd prefer to not have
> >>> to
> >>> do an uninstall/reinstall as I wisely hadn't backed up a few tables I
> >>> was working on over the last couple days.
> >>>
> >>> Any thoughts?
> >>>
> >>> Thanks in advance,
> >>> -Tim
> >>>
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
> >


--------------080608010808010206040405--

Re: MySQL loses password?

am 13.06.2011 11:59:26 von Claudio Nanni - TomTom

--20cf307f35fa86502104a594f9d2
Content-Type: text/plain; charset=ISO-8859-1

You can still login if you want, check skip-grant-tables.
On Jun 13, 2011 11:55 AM, "Tim Thorburn" wrote:
> Ok, ran several scans on my dev PC. Unsurprisingly, nothing was found.
> Trying to use command line produces the error:
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: YES)
>
> The same Access denied for user 'root'@'localhost' (using password: YES)
> happens when trying to use MySQL Workbench 5.2.33 CE. phpMyAdmin gives
> an error #2000 Cannot log in to the MySQL server.
>
> mysql --version returns:
> mysql Ver 14.14 Distrib 5.5.11, for Win64 (x86)
>
> I'm guessing I'll be uninstalling/removing MySQL completely tomorrow and
> doing a clean install of 5.5.13. Although it would be handy to know
> what caused this problem in the first place. It's more than a little
> concerning when Friday everything works, shutdown computer for the
> night, log in again Saturday to find MySQL doesn't so much like my
> passwords anymore.
>
>
> On 6/12/2011 3:33 PM, Claudio Nanni wrote:
>>
>> This happens when you use an old client / library. Can you try from
>> command line? Also issue: mysql --version
>> Claudio
>>
>> On Jun 12, 2011 3:09 PM, "Tim Thorburn" >> > wrote:
>> > There's been no upgrade or old pre-upgrade on this machine. I say the
>> > same dev password for years, as it is the same password I've used on my
>> > dev machines throughout the years.
>> >
>> > This machine started off as a fresh install of the OS several months
>> > ago. The only version of MySQL ever on this particular machine is
>> > 5.5.11. My confusion is mostly centered around the "it worked fine on
>> > Friday, then Saturday happened" issue. I've run a complete virus scan
>> > and found nothing, as well as several spyware/malware scans - it's in
>> > the process of running a scan from a rescue disc, so I'll know if
>> > there's anything else afterward.
>> >
>> > Seems odd anyone would bother hacking into this dev machine that's
>> > barely connected to the Internet.
>> >
>> > On 6/12/2011 8:59 AM, Johan De Meersman wrote:
>> >> If it's recently been upgraded or had an old pre-upgrade backup
>> restored, that's not a major surprise; and surely you won't have ben
>> running mysql on a win7 for several years :-)
>> >>
>> >> In brief, mysql changed password encryptions between 4.1 and 5.0,
>> for various reasons. The old password scheme is still supported, but
>> iird the default setting for that in newer versions is off, yielding
>> you the error you are reporting.
>> >>
>> >> Of course, if none of that is the case, you may have been hacked;
>> but it seems somehow strange that a hacker would bother to install
>> oldstyle passwords.
>> >>
>> >> The error message you provide also mentions the oldpasswd flag for
>> PHP< 5.2 - also worth looking at.
>> >>
>> >>
>> >> ----- Original Message -----
>> >>> From: "Tim Thorburn" >> >
>> >>> To: mysql@lists.mysql.com
>> >>> Sent: Sunday, 12 June, 2011 2:50:22 PM
>> >>> Subject: MySQL loses password?
>> >>>
>> >>> Hi all,
>> >>>
>> >>> I came across something strange today on my dev machine and thought
>> >>> I'd
>> >>> see if anyone here has run into a similar problem. To begin, my dev
>> >>> machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
>> >>> Today when I tried to log into the server using the old MySQL GUI
>> >>> tools
>> >>> as root, I got an error number 1045 Access denied for user
>> >>> 'root'@'localhost' (using password: YES). This is odd because I've
>> >>> not
>> >>> changed the root password on my dev machine in years. When I tried
>> >>> to
>> >>> goto a site on this same machine, Apache throws the following
>> >>> message:
>> >>>
>> >>> mysqlnd cannot connect to MySQL 4.1+ using the old insecure
>> >>> authentication. Please use an administration tool to reset your
>> >>> password with the command SET PASSWORD =
>> >>> PASSWORD('your_existing_password'). This will store a new, and more
>> >>> secure, hash value in mysql.user. If this user is used in other
>> >>> scripts
>> >>> executed by PHP 5.2 or earlier you might need to remove the
>> >>> old-passwords flag from your my.cnf file
>> >>>
>> >>> This is the first time I've seen such a message, or had MySQL
>> >>> randomly
>> >>> stop accepting my root password. I'll likely be doing a complete
>> >>> uninstall and reinstall in a few hours on this machine, but thought
>> >>> I'd
>> >>> ask here to see if anyone had any thoughts as to why this happened,
>> >>> and
>> >>> how I might correct it? If at all possible, I'd prefer to not have
>> >>> to
>> >>> do an uninstall/reinstall as I wisely hadn't backed up a few tables I
>> >>> was working on over the last couple days.
>> >>>
>> >>> Any thoughts?
>> >>>
>> >>> Thanks in advance,
>> >>> -Tim
>> >>>
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>> >
>

--20cf307f35fa86502104a594f9d2--

Re: MySQL loses password?

am 13.06.2011 12:53:18 von Andrew Moore

--20cf302074d02b053104a595ba57
Content-Type: text/plain; charset=ISO-8859-1

Try starting the server using --skip-grants-table and then resetting the
password for the root account.
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#o ption_mysqld_skip-grant-tables

I've only seen/heard of the problems you're having with legacy system
tables. Did you restore the data into 5.5 using an old backup file?
http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

Might save your data!

Andy


On Mon, Jun 13, 2011 at 10:55 AM, Tim Thorburn wrote:

> Ok, ran several scans on my dev PC. Unsurprisingly, nothing was found.
> Trying to use command line produces the error:
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: YES)
>
> The same Access denied for user 'root'@'localhost' (using password: YES)
> happens when trying to use MySQL Workbench 5.2.33 CE. phpMyAdmin gives an
> error #2000 Cannot log in to the MySQL server.
>
> mysql --version returns:
> mysql Ver 14.14 Distrib 5.5.11, for Win64 (x86)
>
> I'm guessing I'll be uninstalling/removing MySQL completely tomorrow and
> doing a clean install of 5.5.13. Although it would be handy to know what
> caused this problem in the first place. It's more than a little concerning
> when Friday everything works, shutdown computer for the night, log in again
> Saturday to find MySQL doesn't so much like my passwords anymore.
>
>
> On 6/12/2011 3:33 PM, Claudio Nanni wrote:
>
>>
>> This happens when you use an old client / library. Can you try from
>> command line? Also issue: mysql --version
>> Claudio
>>
>> On Jun 12, 2011 3:09 PM, "Tim Thorburn" >> webmaster@athydro.com>> wrote:
>> > There's been no upgrade or old pre-upgrade on this machine. I say the
>> > same dev password for years, as it is the same password I've used on my
>> > dev machines throughout the years.
>> >
>> > This machine started off as a fresh install of the OS several months
>> > ago. The only version of MySQL ever on this particular machine is
>> > 5.5.11. My confusion is mostly centered around the "it worked fine on
>> > Friday, then Saturday happened" issue. I've run a complete virus scan
>> > and found nothing, as well as several spyware/malware scans - it's in
>> > the process of running a scan from a rescue disc, so I'll know if
>> > there's anything else afterward.
>> >
>> > Seems odd anyone would bother hacking into this dev machine that's
>> > barely connected to the Internet.
>> >
>> > On 6/12/2011 8:59 AM, Johan De Meersman wrote:
>> >> If it's recently been upgraded or had an old pre-upgrade backup
>> restored, that's not a major surprise; and surely you won't have ben running
>> mysql on a win7 for several years :-)
>> >>
>> >> In brief, mysql changed password encryptions between 4.1 and 5.0, for
>> various reasons. The old password scheme is still supported, but iird the
>> default setting for that in newer versions is off, yielding you the error
>> you are reporting.
>> >>
>> >> Of course, if none of that is the case, you may have been hacked; but
>> it seems somehow strange that a hacker would bother to install oldstyle
>> passwords.
>> >>
>> >> The error message you provide also mentions the oldpasswd flag for PHP<
>> 5.2 - also worth looking at.
>> >>
>> >>
>> >> ----- Original Message -----
>> >>> From: "Tim Thorburn" >> webmaster@athydro.com>>
>> >>> To: mysql@lists.mysql.com
>> >>> Sent: Sunday, 12 June, 2011 2:50:22 PM
>> >>> Subject: MySQL loses password?
>> >>>
>> >>> Hi all,
>> >>>
>> >>> I came across something strange today on my dev machine and thought
>> >>> I'd
>> >>> see if anyone here has run into a similar problem. To begin, my dev
>> >>> machine is Win7 Ultimate 64-bit, running MySQL 5.5.11 (also 64-bit).
>> >>> Today when I tried to log into the server using the old MySQL GUI
>> >>> tools
>> >>> as root, I got an error number 1045 Access denied for user
>> >>> 'root'@'localhost' (using password: YES). This is odd because I've
>> >>> not
>> >>> changed the root password on my dev machine in years. When I tried
>> >>> to
>> >>> goto a site on this same machine, Apache throws the following
>> >>> message:
>> >>>
>> >>> mysqlnd cannot connect to MySQL 4.1+ using the old insecure
>> >>> authentication. Please use an administration tool to reset your
>> >>> password with the command SET PASSWORD =
>> >>> PASSWORD('your_existing_password'). This will store a new, and more
>> >>> secure, hash value in mysql.user. If this user is used in other
>> >>> scripts
>> >>> executed by PHP 5.2 or earlier you might need to remove the
>> >>> old-passwords flag from your my.cnf file
>> >>>
>> >>> This is the first time I've seen such a message, or had MySQL
>> >>> randomly
>> >>> stop accepting my root password. I'll likely be doing a complete
>> >>> uninstall and reinstall in a few hours on this machine, but thought
>> >>> I'd
>> >>> ask here to see if anyone had any thoughts as to why this happened,
>> >>> and
>> >>> how I might correct it? If at all possible, I'd prefer to not have
>> >>> to
>> >>> do an uninstall/reinstall as I wisely hadn't backed up a few tables I
>> >>> was working on over the last couple days.
>> >>>
>> >>> Any thoughts?
>> >>>
>> >>> Thanks in advance,
>> >>> -Tim
>> >>>
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>> >
>>
>
>

--20cf302074d02b053104a595ba57--