InnoDB recovery and KNOWNHOST Disaster!
I am here asking for your urgent help please, support and guidance.
++++++++++++++++++++++++++++++++++++++++++
If You want to buy cheap web hosting then visit http://Listfreetop.pw and select the cheapest hosting. it can be suitable for all your needs.
list of top cheapest hosting
Listfreetop.pw
Listfreetop.pw
Listfreetop.pw
+++++++++++++++++++++++++++++++++++++++++++
I have moved to Knownhost in 2014 and was there with them up right now. I was totally ignorant regarding web-hosting services and servers. I ran my vBulletin since 2006. The Previous host ran it on a utf8mb4 as the data is UTF-8 (Arabic and old eastern languages). I moved to Knownhost and they put the sites all on Latin1 by default. At that time, there were no problem. But after upgrading my vBulletin site and upgrading to a newer server with them, the problem of encoding appeared!!. it appeared early in 2018 and from that time, journey of suffering started and i have discovered theold encoding database data and the known host one. They really tried a lot to help but with no hope!
This is ok, I can imagine that this is a worldwide problem after changing from Latin1 to UTF-8 as default! and would not blame them anymore.
The Real Problem, when vBulletin team advised all customers to upgrade to InnoDB engines. I have changed MyISAM engines and converted them all to InnoDB, it was a successful conversion, tables were reviewed and repaired if any, service restarted and everything went well on two test sites, I tried this with the online one, but a problem occurred.
I restored the cpanel backup !!! NOT WORKING
I restored the complete cpanel tared backup !!! NOT WORKING!!
Ihave asked their assistance to restore from SNAPSHOT !! NOT WORKING.
The team support got nervous and said itis the last time i support this ticket blaming me for the cpanel, dump and snapshot backups!!!!. after almost 16 hours site was restored and they said that it is InnoDB recovery Problem and they thankfully have solved it!
After two days, I made a backup for my site and wanted to test it is really working now, after the last innoDB recovery, and to ensure it was not my mistake and as they admit that they repaired it and have took clean backup, so i just asked them to restore and test it !! the problem repeated again !!, a newer Snapshot was recalled, site not working , same problem!!!
One of the supervisor there said i will never support this site again after this ticket !!,
Well, Why Sir?! because a problem in your snapshot? or because my data will need your magic touch of innoDb recovery each time a restore would be needed? Or because i dared to change to innodb?!!!
Now, my site is completely lost, all the snapshots is not working. Knownhost is removing their hands and the team support says: "Our snapshots are snapshots of the full filesystem. They are intended for disaster recovery, but due to their nature have problems with InnoDB tablespaces."
====
Well, "DISASTER FREE snapshot" resulted in complete loss of my data?!!!!!!!
====
I loved knownhost support, I used to tell every one about how amazing they are, as a newbie in server issues, i can admit that to have a managed server with them will be a very good idea to start, they are very prompt in reply, happy to help with various commands you would ask them about, good backup and snapshots, i really felt safe with them as long as there is no real problem!.
But once you get a real problem with server, the Prompt reply delays, the support way of communication get nervous and very aggressive. Everything changes !
Honeymoon time is only when you ask them to do some commands and query!
=====
The last promise that they will revert the snapshot and get the site works at the time before i ask for restoration. I agreed. They will do the job afternoon!!
Now i feel unsafe
Please, I am really nervous, feeling unsafe, I am totally blind , need your help and advice.
Thank you
lottosurfers.club
domain 3d danielson
domain buy
host header
cryptonator.com
www.jillsclickcorner.com
simpleviralads.com
coinspinner.me
hosting y dominio peru
tekken 7 make money
how does tiffany d make money
I can see that your ticket is still open in our escalated queue and there appears to be several hundred ticket replies over the course of the problems you describe.
Unfortunately InnoDB corruption does happen, and changing the encoding on database tables can have wildly varying results as characters can be displayed completely different between these type sets. Anytime this type of work is performed it is paramount that a full working DB backup be taken at that time so you can always revert and new backups taken every step of the way. Making encoding conversions like this is typically outside of our realm of support simply due to the amount of unknowns that will happen when it is attempted.
To address your concerns about the backups, I'm going to have to review the ticket to specifically speak to them, but yes corruption can happen. These are disaster recovery backups, for if our equipment fails and while we make them available to customers they aren't intended to be your only source of backup, they are more of a last line of defense. We always recommend that customers keep a copy of their important data.
I do see that our staff is currently working on reverting you back to the image prior to the restorations and you've responded that a few items are back to the way you expect them.
Overall your string of tickets regarding this discusses several very complicated issues and rides the line between what we can support as a managed provider and what is deep into developer territory. While our staff often does their best to assist customers with issues outside of what may be considered normal support we are by no means experts who can dive into a complex task such as what you've described.
Your best bet if you are remaining with Vbulletin is to contract a specialist. I personally don't know any but there is a user here on WHT, eva2000 who I believe has worked for Vbulletin in the past would be a good place to start or the vbulletin forums directly. Ensuring that backups are taken and tested along each step of the process. It's also advisable to duplicate your current database and setup and perform all of these tests on a full duplicated site first.
Regardless our staff will continue to assist you but from what I've read so far we will be limited in what we can do in regards to these in depth encoding issues.
KnownHost Managed VPS Specialists
Fully Managed Shared, Reseller, VPS, KVM, WordPress, Dedicated servers and more!
KnownHost is hiring! Click here for more information!
Luckily, all thanks be to Jeremy, they reverted the snapshot now and sites are working
Sure,the problem will persist and appears each time there is a need for a restoration
Your advice please, if i leave them to another web-hosting, would it be a good idea? or the same problem will be there with all?
Thank you
I haven't seen your reply, i will read carefully and reply now
Thank you for your response
Regards,
The Real Problem, when vBulletin team advised all customers to upgrade to InnoDB engines. I have changed MyISAM engines and converted them all to InnoDB, it was a successful conversion, tables were reviewed and repaired if any, service restarted and everything went well on two test sites, I tried this with the online one, but a problem occurred.
I restored the cpanel backup !!! NOT WORKING
I restored the complete cpanel tared backup !!! NOT WORKING!!
Ihave asked their assistance to restore from SNAPSHOT !! NOT WORKING.
The team support got nervous and said itis the last time i support this ticket blaming me for the cpanel, dump and snapshot backups!!!!. after almost 16 hours site was restored and they said that it is InnoDB recovery Problem and they thankfully have solved it!
After two days, I made a backup for my site and wanted to test it is really working now, after the last innoDB recovery, and to ensure it was not my mistake and as they admit that they repaired it and have took clean backup, so i just asked them to restore and test it !! the problem repeated again !!, a newer Snapshot was recalled, site not working , same problem!!!
One of the supervisor there said i will never support this site again after this ticket !!,
Well, Why Sir?! because a problem in your snapshot? or because my data will need your magic touch of innoDb recovery each time a restore would be needed? Or because i dared to change to innodb?!!!
Well, "DISASTER FREE snapshot" resulted in complete loss of my data?!!!!!!!
I am not taking any side, however, if your data is worth the time for you, then please take local backups on your computer. Never rely on "disaster backups" offered by hosts, irrespective of any provider. Hosting providers store backups for thousands of clients on the same backup server, which means that the chance of a failure during a restoration is way higher than that of a local backup stored on your computer.
Always backup your data on your local drives, and multiple times during upgrades or changes.
Knownhost is not at fault here, honestly. They helped you beyond their support coverage and are still trying to help you. I understand that you are nervous because of your site being down, but be patient and work with them. .
Hello Daniel,
Thank you for your prompt reply here and there in support system of knownhost. My Apology for late reply.
First: Regarding my own backups:
"Anytime this type of work is performed it is paramount that a full working DB backup be taken at that time so you can always revert and new backups taken every step of the way."
I had TWO full cpanel backup, before conversion, one done by the support team on 25 feb and the other done by me on 27 Feb +PLUS the automatic day and other day backups +PLUS two separate Database backups +PLUS your day and other day snapshots
Second: Regarding the Conversion, You've been never asked for help (Ticket escalation problem):
Making encoding conversions like this is typically outside of our realm of support simply due to the amount of unknowns that will happen when it is attempted
The conversion went successful theoretically (strange characters not yet corrected in MySQL), conversion was done either to the data or to InnoDB. It was done by MY SIDE without asking your help at all, SO PLEASE, please, be assured that i have never ever asked Support team to help me in the conversion Process. You strangely repeated this and said it is beyond your scope, i have never asked your help in any conversion, some of your team kindly offered that and they were not able to help, i thanked them. That's all.
It is important to emphasize this because one of your team said he would not support my account again because conversion is not his business. Again, The ticket was about the server configuration and Restoration failure NOT about conversion. I asked him to do RESTORATION NOT the Conversion (( It is a completely managed VPS)), so his words are out of context and his"no support" reply is not acceptable.
- If conversions are My BUSINESS, The stability of Backups or at least restoration is YOURS.
- If Conversion is not your responsibility, then ensuring server configuration is YOURS.
This is up to my little understanding. This is all what i asked, that was my fault to let one of support team threaten me not to support my managed VPS again!.. This is why I asked to escalate that Ticket, to review this Response from your support team member. Please, don't repeat his words as you are judging here.
Third: Please be informed that Restoration FAILED BEFORE CONVERSION!!!
To return a step back during my conversion testing, i recalled my own cpanel backup which was done before innoDb conversion and it failed, I recalled the other cpanel backups done by support team and it failed. This is why I needed your assistance with the snapshots and again it failed. It worth your Attention please, that all that were taken before any conversion. But it seems the newly converted InnoDB affected even the previous backups??, I don't know.
At that time, the team didn't mention any about InooDB recovery. The Team excuse is that, it is due to some my.cnf configuration caused mysql to stop, and that they needed to comment it out. The 2nd and 3rd time Restoration were acclaimed to be due to InnoDB recovery.
Fourth: My tests are done on Mirror sites
It's also advisable to duplicate your current database and setup and perform all of these tests on a full duplicated site first.
i have two more mirrors and they worked well, as i thought (apart from the backup/restoration problem) so i went ahead with the online site.
My Questions:
1) I have many backups and many snapshots and each time they fail in restoration, My Question: for any reason, after month or a year, and i need to restore a backup, does it mean, it will continue to fail???
2) From now and so forth, Are backups considered useless because database is acclaimed to be corrupted?
My little thinking:
I doubt it is the corruption of database itself, as i made a MySQLdump backup, and established a new, alternative mirror site, restarted the service and it worked well on another account on the same server.
Again, Thank you Daniel for your responsibility and for being here and there, i admit it is the normal and usual way of Knownhost to followup tickets and address problems with prompt responsibility, I admit that i love this place so much. I admit it is the toughest bad situation i ever met since 2005.
Waiting for your confirmation regarding the database if still valid or not and how can we make sure the future backups will be working. And if we need to change server or account as a solution.
Thank you
I am not taking any side, however, if your data is worth the time for you, then please take local backups on your computer. Never rely on "disaster backups" offered by hosts, irrespective of any provider. Hosting providers store backups for thousands of clients on the same backup server, which means that the chance of a failure during a restoration is way higher than that of a local backup stored on your computer.
Always backup your data on your local drives, and multiple times during upgrades or changes.
Knownhost is not at fault here, honestly. They helped you beyond their support coverage and are still trying to help you. I understand that you are nervous because of your site being down, but be patient and work with them. .
Hello LinuxFox,
Thank you for your great advice, I will follow although i think i didn't depend totally on the hosting backups. But during restoration, something- each time- gives error and let MySQL to stop! either if restoration done from my cPanel backups or from their Snapshots.
I am semi-convinced, it is their fault!, I am totally talking about the failure of Restoration and backups nothing else. These are included and ensured in managed VPS contract. If i wa of that solid experience in servers, i would have bought a non-managed VPS and managed everything by my own responsibility.
Thank you (F)
Hello LinuxFox,
Thank you for your great advice, I will follow although i think i didn't depend totally on the hosting backups. But during restoration, something- each time- gives error and let MySQL to stop! either if restoration done from my cPanel backups or from their Snapshots.
I am semi-convinced, it is their fault!, I am totally talking about the failure of Restoration and backups nothing else. These are included and ensured in managed VPS contract. If i wa of that solid experience in servers, i would have bought a non-managed VPS and managed everything by my own responsibility.
Thank you (F)
As with other hosting providers, they have the following in their terms of service which you agreed to.
KnownHost highly recommends that all customers retain up to date backup copies of their data off site for disaster recovery purposes.
VPS Customers:
KnownHost provides complementary backup services for our VPS customers. These snap shots are taken every other day and stored for approximately 7-14 days. Customer agrees to maintain a current copy of all content hosted by KnownHost notwithstanding any agreement by KnownHost to provide back up services. Customer acknowledges that any backups provided by or for KnownHost services are a courtesy service intended for disaster recovery only and that KnownHost does not warrant or guarantee the availability, integrity, content or operability of these backups.
My Questions:
1) I have many backups and many snapshots and each time they fail in restoration, My Question: for any reason, after month or a year, and i need to restore a backup, does it mean, it will continue to fail???[COLOR=#ff0000]
2) From now and so forth, Are backups considered useless because database is acclaimed to be corrupted?
1) Based on the statement made about the backups being a full file system backup your safe assumption is to assume any snapshot will not be usable for InnoDB. This is really no fault of KnownHost this is due to how MySQL works and then the way the InnoDB table type is designed. The reason for this is unless the underlying backup software tells MySQL to flush data to disk and lock the database there is no guarantee there is consistent data on the file system. MySQL may have critical pieces of data still in memory that was not yet flushed to disk. If you used the MyISAM you may lose a few rows but under InnoDB the data left in memory has more serous repercussions potentially.
There are ways to potentially recovery the data depending on the reason for the corruption issue: https://dev.mysql.com/doc/refman/8.0...-recovery.html
2) There are a few documented safe ways to backup InnoDB based databases: https://dev.mysql.com/doc/mysql-back...db-backup.html which in this case essentially boils down to shutting off the MySQL server and copying the files or using mysqldump and locking the tables. The other option depending on the MySQL versions is https://www.percona.com/doc/percona-...2.1/index.html or https://mariadb.com/kb/en/library/mariabackup/
As for the topic in general InnoDB is great high performance transaction based type. The problem is it's more complicated and with that requires you're far more careful with the system. The common things we see from users are; server running out of disk, server running out of memory. Both of which anytime this happens there is a risk of unrecoverable data. Then there are of course other outside the user control situations like sudden power loss which could also cause unrecoverable data. In these scenario's the best solution if there is InnoDB corruption is to hope innodb recovery works and you're able to dump the data.
Hello Blackhorse1
I have seen this issue happening many many times. You make a backup. It looks good then when you restore, everything works except the database.
I use to help many corporate customers to schedule snapshots and backups mainly on environments with MS SQL / Oracle databases. We always leveraged the VSS layer on Windows in order to synchronize the snapshot and get a consistent image of the database.
The reason that sync is need is because the db has many GBs of data in RAM buffers. This data is not yet committed to the disk. So if you just go ahead and take a snapshot of the disk (or a backup), you are not going to include the data that is in the RAM! Then when you restore from that snapshot, 99% of the time, your DB is corrupted as many transactions were not written to the disk when the snapshot was taken.
If you were on Windows server, I would have said "use the VSS layer". Most software programs come with a component that talks to the VSS in order to quiesce the file system and flush the memory before taking the snapshot that would serve for the backup (the snapshot is taken, mounted as read only then used to create a backup).
In your case, the dirty option I see is the following: shutdown your database for a minute then take the snapshot or launch the backup. When its done, start it again. When DBs are stopped, they flush all stuff they keep in memory to the disk. It involves a short downtime but it would give you a clean and consistent snapshot
Enterprise Consultant
ITIL - Agile Scrum Master - CCNP R&S - CCNA Sec
.:. Travels West .:.
1. Before making a backup be sure to put your forum in maintenance mode. IMPORTANT!!!
2. Make sure you keep full working backups using JetBackup. cPanel backups are ok, but JetBackup is much better.
3. If you need help from a developer, maybe try Glenn from https://vbmods.rocks who is former vBulletin developer. He is very helpful.
Fast Host - Cloud Hosting w/ LiteSpeed & Imunify360 Security
Backup Storage - Backup Storage w/ SFTP, SSH & cPanel Access
Now, that things are fixed with your VPS and resolved. Make sure you learn and take regular backups, we are not talking once a week, but daily if possible off to a service such as AWS. This is easily achievable with Jetbackup as a backup solution.
Hello,
I am here asking for your urgent help please, support and guidance.
I have moved to Knownhost in 2014 and was there with them up right now. I was totally ignorant regarding web-hosting services and servers. I ran my vBulletin since 2006. The Previous host ran it on a utf8mb4 as the data is UTF-8 (Arabic and old eastern languages). I moved to Knownhost and they put the sites all on Latin1 by default. At that time, there were no problem. But after upgrading my vBulletin site and upgrading to a newer server with them, the problem of encoding appeared!!. it appeared early in 2018 and from that time, journey of suffering started and i have discovered theold encoding database data and the known host one. They really tried a lot to help but with no hope!
This is ok, I can imagine that this is a worldwide problem after changing from Latin1 to UTF-8 as default! and would not blame them anymore.
For non-english web sites/forums, knowing your default mysql server's character set and collation is very important when it comes to individual database backup/restores. As you're on VPS, you have full control over how mysql server is configured for character set /collations. I've seen this first hand with vBulletin forums and xenforo folks even have a sticky thread at https://xenforo.com/community/thread...backup.152740/ and my input at https://xenforo.com/community/thread...0/post-1276273
Also general rule I have, is always do a separate backup outside of cpanel's native backup systems - when configured and done properly, they're generally faster and more flexible than cpanel's native backup system
i.e.
- you could script individual mysql database backup/restores grouped by required mysql characterset/collation that overrides the default mysql server's character set and collation. For example I write my backup scripts to be smarter so they do individual database level backups using the characterset that is automated detected by the script for the underlying database tables to some extent when they differ from the default mysql server's character set and collation configuration . i.e. if database is utf8mb4, backup is utf8mb4. If utf8, then backup is utf8. This bypasses whatever is set for the the default mysql server's character set and collation configuration.
- you could order/schedule individual mysql database backups at a time of day which gives you the best chance of a complete backup vs incomplete backup with the least impact on the operate of your site/forums.
Tip 1.
If you're messing/changing the default mysql server's character set and collation configuration, probably best to do it on a 2nd staging vps server with exact same mysql version, /etc/my.cnf and same default mysql server's character set and collation configuration - spin up a cheap hourly billed VPS server for testing. Especially if you have many mysql databases with different underlying character set and collations which differ from default mysql server's character set and collation. As you may fix one database only to screw up all the other databases on the server.
Tip 2.
Always do conversions/upgrades or anything that alters your mysql database on a copy of the live database and never the live database. The successfully manipulated copy of live database then becomes the new live database and previous live database is untouched always - ready for making future copies if you have issues requiring re-doing conversions/upgrades. I've used this method since vBulletin 1.x way back in 2000 and do same for xenforo. There's no fear of messing up as live database is never touched at all so always available for making another a good copy from to redo/retry other methods if needed.
Last edited by eva2000; 03-17-2019 at 08:09 PM.
: CentminMod.com Nginx Installer (Nginx 1.17, PHP-FPM 5.4-5.6, 7.0-7.3 (7.4 ready), MariaDB 10 + optional ngx_pagespeed/lua) for CentOS
: Centmin Mod Latest Beta Nginx HTTP/2 HTTPS supports TLS 1.3 via OpenSSL 1.1.1 or BoringSSL
: Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack
There are a few documented safe ways to backup InnoDB based databases: https://dev.mysql.com/doc/mysql-back...db-backup.html which in this case essentially boils down to shutting off the MySQL server and copying the files or using mysqldump and locking the tables. The other option depending on the MySQL versions is https://www.percona.com/doc/percona-...2.1/index.html or https://mariadb.com/kb/en/library/mariabackup/
Hello Tony,
Thank you so much, for all valuable info you have provided me with. I understand now it is not there fault, although i was complaining from the response i got, and very worried to lose my database. I saved the link for InnoDB recovery, really appreciate your help. I searched after your post but still using mysqldump command to back up as i am on mysql 5.7 . I am afraid to use programs, and searched about mysql enterprise backup but it was so much expensive.
Regards,
Hello Blackhorse1
I have seen this issue happening many many times. You make a backup. It looks good then when you restore, everything works except the database.
I use to help many corporate customers to schedule snapshots and backups mainly on environments with MS SQL / Oracle databases. We always leveraged the VSS layer on Windows in order to synchronize the snapshot and get a consistent image of the database.
The reason that sync is need is because the db has many GBs of data in RAM buffers. This data is not yet committed to the disk. So if you just go ahead and take a snapshot of the disk (or a backup), you are not going to include the data that is in the RAM! Then when you restore from that snapshot, 99% of the time, your DB is corrupted as many transactions were not written to the disk when the snapshot was taken.
If you were on Windows server, I would have said "use the VSS layer". Most software programs come with a component that talks to the VSS in order to quiesce the file system and flush the memory before taking the snapshot that would serve for the backup (the snapshot is taken, mounted as read only then used to create a backup).
In your case, the dirty option I see is the following: shutdown your database for a minute then take the snapshot or launch the backup. When its done, start it again. When DBs are stopped, they flush all stuff they keep in memory to the disk. It involves a short downtime but it would give you a clean and consistent snapshot
Hello FIAHOST,
Thank you for your valuable reply. Unfortunately, Windows server is not a possibility for now.
But the good point i will follow with some twist (correct me if i am wrong): to stop my site then restart MySQL DB - supposing that restart will flush data from memory to the disk as well as shutting down- then, I take mysqldump backup. I don;t know why i am afraid of shutting down database and think restart is more safe, isn't it?
Thank you
Reply With Quote Reply With Quote 0 Not allowed!
04-20-2019, 10:11 AM #16 Blackhorse1 Blackhorse1 is offline
Newbie
Join Date
Mar 2019
Posts
11
Quote Originally Posted by HostXNow_Chris View Post
1. Before making a backup be sure to put your forum in maintenance mode. IMPORTANT!!!
2. Make sure you keep full working backups using JetBackup. cPanel backups are ok, but JetBackup is much better.
3. If you need help from a developer, maybe try Glenn from https://vbmods.rocks who is former vBulletin developer. He is very helpful.
Hello HostXNow_Chris,
Thank you for your reply,
1- yes this is what I am doing now, I shutdown the forum (maintenance mode), restart MysQL then take mysql dump.
2- Full cPanel backup made a problem in the last time with that snapshot dilemma, so, i am afraid to do or depend on. I will search for JetBackup and try it if possible.
3- Yes, I am a real fan and customer of Glenn, he helps me a lot and I hire him through his forum, with every possible problem within his professional scope.
Thank you
Now, that things are fixed with your VPS and resolved. Make sure you learn and take regular backups, we are not talking once a week, but daily if possible off to a service such as AWS. This is easily achievable with Jetbackup as a backup solution.
Hello TrentaHost,
I have aws account but never used for backups, I will read more and give Jetbackup with aws a try.
Thank you
Hello TrentaHost,
I have aws account but never used for backups, I will read more and give Jetbackup with aws a try.
Thank you
AWS can get expensive, perhaps like a cheap storage VPS and use Jet Backups incremental and you should be good.
For non-english web sites/forums, knowing your default mysql server's character set and collation is very important when it comes to individual database backup/restores. As you're on VPS, you have full control over how mysql server is configured for character set /collations. I've seen this first hand with vBulletin forums and xenforo folks even have a sticky thread at https://xenforo.com/community/thread...backup.152740/ and my input at https://xenforo.com/community/thread...0/post-1276273
Also general rule I have, is always do a separate backup outside of cpanel's native backup systems - when configured and done properly, they're generally faster and more flexible than cpanel's native backup system
i.e.
- you could script individual mysql database backup/restores grouped by required mysql characterset/collation that overrides the default mysql server's character set and collation. For example I write my backup scripts to be smarter so they do individual database level backups using the characterset that is automated detected by the script for the underlying database tables to some extent when they differ from the default mysql server's character set and collation configuration . i.e. if database is utf8mb4, backup is utf8mb4. If utf8, then backup is utf8. This bypasses whatever is set for the the default mysql server's character set and collation configuration.
- you could order/schedule individual mysql database backups at a time of day which gives you the best chance of a complete backup vs incomplete backup with the least impact on the operate of your site/forums.
Hello eva2000,
I am really happy to see your comment here, I was searching for you to configure server for vBulletin 5 if it is still applicable, Thank you for your valuable reply.
I have successfully converted my forum to utf8mb4 and restored Arabic and Old eastern characters back in database, and this is a really big step I have achieved after a lot of sufferings.
Regarding the backup, I am using this command:
mysqldump --opt -Q -u root -p db_name > mysqldump.sql
But I have read the post about xenforo and your comment there, so do you suggest using this command? - by the way, I am using utf8mb4 as default charset for all databases
mysqldump -u user --hex-blob --default-character-set=utf8mb4 --single-transaction database > database.sql
Could you please share the command you use for back up? - Thank you
Quote Originally Posted by eva2000 View Post
Tip 1.
If you're messing/changing the default mysql server's character set and collation configuration, probably best to do it on a 2nd staging vps server with exact same mysql version, /etc/my.cnf and same default mysql server's character set and collation configuration - spin up a cheap hourly billed VPS server for testing. Especially if you have many mysql databases with different underlying character set and collations which differ from default mysql server's character set and collation. As you may fix one database only to screw up all the other databases on the server.
A very valuable tip, Done! I have purchased a cheap vps account and did all the trials on it and it was an important step to understand and convert databases before i do on the real VPS with the online site.
Quote Originally Posted by eva2000 View Post
Tip 2.
Always do conversions/upgrades or anything that alters your mysql database on a copy of the live database and never the live database. The successfully manipulated copy of live database then becomes the new live database and previous live database is untouched always - ready for making future copies if you have issues requiring re-doing conversions/upgrades. I've used this method since vBulletin 1.x way back in 2000 and do same for xenforo. There's no fear of messing up as live database is never touched at all so always available for making another a good copy from to redo/retry other methods if needed.
It is an expensive learnt tip that i have learned after a very tough and bad experience costed me a lot of time, money and effort for losing the previous untouched databases!
Thank you Again Eva 2000
Hello All,
As I have made this post under pain and pressure with very difficult time I experienced with database. But Daniel and other staff of Knownhost really are beyond my expectation. Here is the message I have sent them and important to leave it here also as An expression of my Apology along with gratitude and appreciation.
=========
Hello Daniel, Tommy, James, Luke, Jeremy and all great staff who helped me and taught me a lot
I would like to express my gratitude and appreciation for your laudable and tireless efforts. Your support is always the best. Although, i got upset sometimes or feel lost in others, but i really admit you are very very professional team. I know I was a some sort of headache for you with the problem of my databases encoding issues. But really if you were in my shoes you would have felt the pain and big headache i had !. I apologize for any misunderstanding. I am really thankful for all of you and special Thanks to Daniel who is very clever in dealing with difficult situations.
To update you, I have completely solved the encoding problem for all my databases. After many failed conversions and incomplete results, trying all possible scripts or commands there over the internet or hiring professional DBA who found it difficult to restore Arabic. Finally, I have been able to restore Arabic 4 days ago in my Database and to convert fully to utf8mb4. Also, All other problems of upgrade, have been solved after this last conversion.
At any time you have faced any problems with any customer or with yours regarding database tabees and columns encoding, when all solutions are failing then I am here : )), my pleasure to help. I think i have got a very good experience in converting them now.
As my headache and nightmare came to end, I think i had to complete this great end for me with a big thank you to all of you and to repeat my Apology and confirm my trust in Knownhost and its support team. Again, I admit, I have started ZERO knowledge with you here as I am a physician and had no experience in internet before, i just used to pay and leave sites for others to manage, But now i have grown in your school with more solid understanding Although it is still in the beginning but it was rich enough in very short period.
Thank you Again
Hello eva2000,
I am really happy to see your comment here, I was searching for you to configure server for vBulletin 5 if it is still applicable, Thank you for your valuable reply.
I have successfully converted my forum to utf8mb4 and restored Arabic and Old eastern characters back in database, and this is a really big step I have achieved after a lot of sufferings.
Regarding the backup, I am using this command:
mysqldump --opt -Q -u root -p db_name > mysqldump.sql
But I have read the post about xenforo and your comment there, so do you suggest using this command? - by the way, I am using utf8mb4 as default charset for all databases
mysqldump -u user --hex-blob --default-character-set=utf8mb4 --single-transaction database > database.sql
Could you please share the command you use for back up? - Thank you
A very valuable tip, Done! I have purchased a cheap vps account and did all the trials on it and it was an important step to understand and convert databases before i do on the real VPS with the online site.
It is an expensive learnt tip that i have learned after a very tough and bad experience costed me a lot of time, money and effort for losing the previous untouched databases!
Thank you Again Eva 2000
Glad to hear the tips helped. As to help configuring/optimising server for vB5, I still do consulting work but mainly for vB 3/4 and xenforo based servers. While vB5 i have worked on, it's been a few years since I touched a vB5 based server though optimising server would usually involve the same know-how anyway. Probably best to contact me via pm.
As to backup options the base options I use for mysqldump are -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob with adjusting max_allowed_packet to match server side setting and then my backup scripts have additional logic to append additional parameters.
If my script detects non-innodb database tables in the database, it will not use --single-transaction database. But if script detects all database tables are innodb table based only, then my script will append --single-transaction to the base options.
If my script detects any utf8mb4 database tables in database, it will append --default-character-set=utf8mb4 to the base options. If script detects utf8, it will append --default-character-set=utf8 to the base options. Same goes for any non utf8 character set detected, it will append --default-character-set=XXX where XXX is the character set of the database table detected but only when there is no presence of any utf8 or utf8mb4 character database tables at all. Logic isn't perfect but suits my needs as I am mainly dealing with either utf8 or uft8mb4. Not required if you use utf8mb4 default in my.cnf and all your databases are uft8mb4 based.
Example of backing up 3 databases named, testdb, sbt, sbt_uft8mb4
Code:
mysql -e "SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;"
+--------------------+---------+--------------------+
| database | charset | collation |
+--------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
| performance_schema | utf8 | utf8_general_ci |
| sbt | utf8 | utf8_general_ci |
| sbt_uft8mb4 | utf8mb4 | utf8mb4_general_ci |
| testdb | utf8mb4 | utf8mb4_general_ci |
+--------------------+---------+--------------------+
backups dynamically use the correct characterset detected for each individual database and as all databases are 100% innodb only, --single-transaction database is auto appended
Code:
backup /etc/my.cnf
[backup data + schema: testdb ]
mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql
[real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3684 KB cswaits: 23]
[backup schema only: testdb ]
mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql
[real:0.01s user:0.00s sys:0.00s cpu: 68% maxmem: 3684 KB cswaits: 24]
[compression routine: testdb ]
pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql
[real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1004 KB cswaits: 5]
515 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql.gz
pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql
[real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1004 KB cswaits: 5]
522 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql.gz
[backup data + schema: sbt ]
mysqldump --single-transaction --default-character-set=utf8 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql
[real:0.01s user:0.00s sys:0.00s cpu: 58% maxmem: 3724 KB cswaits: 37]
[backup schema only: sbt ]
mysqldump --single-transaction --default-character-set=utf8 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql
[real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3716 KB cswaits: 37]
[compression routine: sbt ]
pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql
[real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1140 KB cswaits: 5]
5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql.gz
pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql
[real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 6]
748 /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql.gz
[backup data + schema: sbt_uft8mb4 ]
mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt_uft8mb4 > /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql
[real:0.01s user:0.00s sys:0.00s cpu: 60% maxmem: 3728 KB cswaits: 36]
[backup schema only: sbt_uft8mb4 ]
mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt_uft8mb4 > /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql
[real:0.01s user:0.00s sys:0.00s cpu: 57% maxmem: 3720 KB cswaits: 36]
[compression routine: sbt_uft8mb4 ]
pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql
[real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1140 KB cswaits: 5]
5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql.gz
pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql
[real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1004 KB cswaits: 5]
768 /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql.gz
------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/testdb.com/backup/mysql]
-rw-r--r-- 1 root nginx 522 Aug 27 13:57 testdb-backup-schema-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 515 Aug 27 13:57 testdb-backup-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 768 Aug 27 13:57 sbt_uft8mb4-backup-schema-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 sbt_uft8mb4-backup-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 748 Aug 27 13:57 sbt-backup-schema-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 sbt-backup-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 my.cnf-backup-270818-135734
Last edited by eva2000; 04-20-2019 at 07:19 PM.
: CentminMod.com Nginx Installer (Nginx 1.17, PHP-FPM 5.4-5.6, 7.0-7.3 (7.4 ready), MariaDB 10 + optional ngx_pagespeed/lua) for CentOS
: Centmin Mod Latest Beta Nginx HTTP/2 HTTPS supports TLS 1.3 via OpenSSL 1.1.1 or BoringSSL
: Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack
Hello TrentaHost,
I have aws account but never used for backups, I will read more and give Jetbackup with aws a try.
Thank you
If it is database we are talking about which we know it is critical, you should go for Acronis.
We are using it with our own personal server and database is restored flawlessly.
