Hi there.. I'm not sure if anyone can help me with this, but I have just experienced a very disturbing situation with mysql.
Today when coming to work, one of our databases in mysql seems to have lost a lot of data, it was as if the database was restored from a backup several days ago.. but only for some tables not all of them. and some tables had also lost data that was very old, but retained data that was newer... I had a backup script that was running every night that was not working as I thought it should have been working.. The Backup script (borrowed from the net and slightly modified) does a FULL permament backup the first every month. then a FULL backup every sunday (that gets overwritten every sunday) and then a backup every day for file sthat have been changed (the date/time for the file is newer...) since last sunday The backups just does a TAR on the files in the data folder... The Full backup from sunday was OK and worked so I had to restore it from that date.. but the daily backup that should take files that have changed did not backup the tables(files) that I had inserted new rows in.. either the backup script is in error, tar did not see that the file was changed OR (and this is the worrying part) mysql/linux had not updated the files timestamp so that TAR did not see that they vere modified.. I have now changed the backup script so that instead of copying the files I use mysqldump for backup, and does a FULL backup every night (with mysqldump) Does anyone have any recomendations on how to backup and when? Anyway back to the original problem.. The database had lost a lot of data.. Searching for an error, the only thing I found out was that sometime in the middle of the night, the database had been restarted (I have a script that checks and sees if mysql exist every 5 minutes, and if it does not, I start it) and just before the restart (why it had restarted I cannot find out) there were 105 mysql-servers running (1 per connection + some spare?) (Max_c onnection was configured to 100) could this be the problem? ie that mysql had "shutdown/crashed" while max number of connections was reached (I'm not saying that it crashed/shutdown because of this) I'm not that familiar with the whole concept of mysql/linux and there are some scripts that are running in this setup that I have not checked yet (I did not configure this linux, but was "forced into it) I'm running Ver 3.23.42 for pc-linux-gnu on i686 under SuSE 7.2 I have changed a little in how connections are made to the database so hopefully it will never max out again and I've incresed the max_connection to 1000 connections.. is there anything more I should do? any other recommendations to get a healthy system? (I'm using jdbc to access mysql (mm.mysql.....) can that be a problem? (I'm going to add debugging that will write down every update to the database to a seperate file also... just so that IF tis happen again I CAN recreate the data even if the backup is not working...) If you ahve any ideas on these subjects I'd be glad to hear it! /Christian Andersson --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
