Php/Mysql/Apache

P

piermartiri

Hi. Please I need your help.

I have been using a FreeBSD (vps) server with goldvision company for several years. And every so often my clients complain that records are lost from the database (mysql), the servers in charge tell me that innodb is corrupted, but I think something happens when they restart the server, like it goes back to a previous version . Managers have access to restart the server. This last problem occurred on January 17, all movements for that day were lost. Has someone something similar happened to him that can help me?
Thank you.

I write from Chile.
This text was translated by google translator.
 
Hello!

There are some recomendations that may prevent data corruption:
The production database server must not to be restarted as long as possible.

If you need to restart the server:
Before restarting database, you have to shutdown all database clients.
By then shutdown the mysql and restart the server manually from console or shell access (if needed).
You must never to do "hard restarting" database server from VPS manage interface.

Try to setup or increase rcshutdown_timeout in /etc/rc.conf
Code: 
rcshutdown_timeout="180" # Seconds to wait before terminating rc.shutdown
 
Use a command like service mysql-server stop && shutdown -r now. That will stop the service before actually restarting. Don't use the reboot(8) command, it doesn't close/stop services it kills them. Never kill MySQL as this will stop it from flushing its caches to disk and you will end up with corrupted tables and/or data loss.
 
Another doubt. The answer I received from the goldvision server was that the innodb database had been corrupted, but I find it curious that only one day's records were lost. If the database had been damaged, the entire database would have been damaged, not just one day, right? Only the last day (January 17) was lost, but there were all the records from previous days. This happened in all the databases that I have in mysql, I must have about 30 databases.
Do you recommend using innodb or myisam? or does that have nothing to do with what happened?

I have serious problems with this loss of information, my clients no longer trust my service, and obviously they are right.

I appreciate your help again
 
piermartiri said:
If the database had been damaged, the entire database would have been damaged, not just one day, right?
Click to expand...
That depends. It's possible the binlogs were damaged, if you have those enabled.
piermartiri said:
Do you recommend using innodb or myisam?
Click to expand...
MyISAM is going to be removed in future MySQL versions. MyISAM has no crash recovery and is non-transactional. There are only a few specific use cases where MyISAM would be better than InnoDB.
 
It has been a few years since I've used MySQL and I know it would probably be unorthodox but wouldn't a possible workaround until you figure out the real solution be to write the MySQL commands to log file starting at the beginning of each day and wipe at the end of the day if no transactions were lost? If there was data loss you could then just rerun the commands from the log file. I know not everyone is OK with using hacky solutions to problems so feel free to completely ignore me.
 
What is the total size of your mysql databases?
Can you evaluate the size using the command "du -hs /var/db/mysql"?
Have you checked your logs about abnormal mysql termination (like mysql crashed or mysql killed)?

Also you must have everyday backups the whole VPS.

I have some experience with FreeBSD inside VPS.
Some of the VPS-providers are provide low quality service using low-quality equpment.
I had trouble with some of them with different issues.
Strange providers can do: "unexepted backups with freezing VPS", "reboot VPS because of something", "rollback the last state of VPS in case provider failure", etc.

Try to setup external monitoring for your VPS. You have to monitor networking accesibility for your VPS.
If you can't to setup monitoring then run the local scrips every few seconds for runtime data collection.

Try to change your VPS provider.
Try to move your service to the dedicated physical server.
Try to split your server. Move the mysql to the different VPS to the different provider.
Use nginx (or other reverse http proxy) for servicing incoming http connections. Having nginx allows you to move or split parts of your service without necessity to change DNS-records for client's domains.
 
Thank you for your answers.

The folder size is 4.1G and there are 41 individual innodb databases separated by folders.

No, I have not reviewed the lock records, I would appreciate if you could tell me where to review those files.

Yes, the company "goldvision" makes backup copies 1 time a day, but I think the problem is what you say "undo the last state of the VPS in case of provider failure" and that deletes all the changes made. Now ask to make 4 copies daily, but I don't think that is the solution.

"Try setting up external monitoring for your VPS. You should monitor the network accessibility for your VPS." Please I would appreciate if you can give me some starting points to configure this.

Could you give me a recommendation of a good company that offers a good vps service or dedicated server? I can no longer continue with this company working, I have to find a new vps provider.

I will investigate how nginx works, I thought it was the same as apache.
 
Logs: /var/log/messages /var/log/debug.log - review them daily.
Try to read root's mail from your's server.

About backups:
The company can't create correct backup of your server while mysql running.
All the provider's backups may be wrong and inconsistent.
Ask the "goldvision" how their "backups" works.
Check your server inside while "goldvision" makes backup.
Check 'uptime' of your server. It may be rebooted by provider at backup time.

Monitoring is a subject for a new thread.
0) SNMP port must be firewalled. SNMP-write must be disabled.
1) run any SNMP server at your FreeBSD. (bsnmpd is already installed. Another one is snmpd or net-snmp)
2) run SNMP collector at different computer. MRTG, CACTI, NAGIOS are collect data from server via SNMP.
For first time you can run SNMP-monitoring tool on a windows-desktop.

I can't recoment a good vps-service, because I am so far from your country. The network latency from Chile to Europe will be too big.
 
Losing a day feels like they (the VPS) had some issue and restored from the previous day's backup - that's why there doesn't seem to be anything else amiss, "just" the loss of data.

That's just a feeling - not very scientific!

I have used FreeBSD, Apache, MySQL and PHP for over a decade on a number of servers and have not suffered anything like what you've described. That might be luck, obviously.

I think you have the right approach - time to find a more reliable provider (or at least try another provider to confirm that doing so improves things.)
 
I think as others are saying, it's likely the innodb table corruptions are caused by the VPS provider.

They're probably using something like rsync and restoring a database table(s) from that is a recipe for disaster (as you have found out).

My question to your providers would be "why are you restarting the VPS?". Any restart should be up to the client (YOU), not them. They sound like a provider you best move away from quickly.

They can do backups at any time they want, they won't interfere with you, but they should also never just reboot your server (virtual or not).
 
Check if your logs (server wide) are missing for the day, your provider could be restoring a snapshot of the vps. Just a thought.

I think MySQL is crashing, out of memory.
 
You must log in or register to reply here.
Back
Top