MySQL DB - MyISAM + Innodb - deleted ibdata1

[inthegenes]

Hi,

I'm slightly confused. I was under the impression that VICIdial uses Innodb by default because of my large ibdata1 file. I accidentally deleted my ibdata1 file and lost my database as a cause of this.

I am now looking into rebuilding my dialer system this time I am doing some further study into VICI's database structure to ensure that I have a better grasp of it and a better backup procedure. During my research I realized that VICI uses both MyISAM and Innodb, with MyISAM being the default. So my question is, how come I lost my DB after deleting the ibdata1 if VICI uses both DB types? The ibdata1 file is documented as being a Innodb file. What am I missing?

My VICI configuration was/is:
Version: 2.10-455a
Build: 140623-2147
6 X Dual Quad Core 2.66 GHz, 3GHz, 4 - 8 GB RAM Telephony Servers
1 X Dual Quad Core 2.66 GHz, 8GHz RAM Web Server + 2 Dual Core 3.06GHz, 4GHz RAM Web Server
1 X Dual Quad Core 2.66 GHz, 32GHz RAM DB Server

Since I am in the rebuilding stage, any advice in regards to clustering and backup solutions for both the telephony, web servers and most importantly the DB?

Many thanks

[mflorell]

Vicidial does NOT use Innodb. It is strongly recommended that you do NOT use InnoDB as your database storage engine for Vicidial use.

At this point we recommend using MariaDB 5.5 with MyISAM data tables.

[inthegenes]

Hi Matt,

I did not change the structure of the installation. The build I am using uses MySQL. Our first VICIdial servers were installed with the build above, so we continued with that build for our cluster. This was before VICIdial migrated to Maria DB.

The installation was done via a VICIbox preload ISO.

So some of the tables by default seem to be Innodb. My question was in regards to the fact that I deleted the ibdata1 file, which is a Innodb database file. I lost me DB due to this deletion.

Probably the wrong place to ask about restoring my DB, considering some of the tables were MyISAM. And how the mixed table works given my deleted file.

So is it advisable to migrate to Maria DB? Is it a simple conversion?

[mflorell]

What version of Vicibox did you use?

newer versions of MySQL use innodb to store mysql configuration information, which may have caused your problem.

[inthegenes]

Hi Matt,

I believe we used ViciBox5.x86_64-5.0.3.preload on all of our boxes. MySQl version 5.5.28.

[geoff3dmg]

That's not right. ViciBox v5 comes with MariaDB.

Code: Select all

vicidial-db:~ # mysqladmin version
mysqladmin  Ver 9.0 Distrib 5.5.33-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Server version      5.5.33-MariaDB-log
Protocol version   10
Connection      Localhost via UNIX socket
UNIX socket      /var/run/mysql/mysql.sock
Uptime:         15 days 22 hours 32 min 37 sec

Threads: 20  Questions: 664788550  Slow queries: 1765  Opens: 88777  Flush tables: 2  Open tables: 2031  Queries per second avg: 482.725

[mflorell]

Yes, we standardized on MariaDB over a year ago on all new installations, including Vicibox.

MySQL 5.5 has untraceable random locking problems running at higher capacities. Those problems don't exist in MySQL 5.1 or MariaDB 5.5.

[inthegenes]

Do you then recommend transitioning to the newer VICIdial distribution?
Can MySQL tables be pushed to MariaDB, given they are MyISAM?

[mflorell]

You should be able to drop in the MariaDB 5.5 engine on top of a MySQL 5.5 installation. We have done it several times.

[inthegenes]

Cool,

The posts I found online stated I would have to install MySQL. The dialer is not in production so I will definitely drop it in.

In regards to upgrading the VICI version, do I need to ensure that I am on the same MariaDB version as the SVN?

It seems the SVN trunk upgrade is the only viable option. The manual on ViciBox website mentions a upgrade application, but I can't find any trace of such.

Thanks again Matt! Really appreciate the assistance.

[mflorell]

It might actually be easier if you just started from scratch with the Vicibox 6.0 ISO as a new install. You can do a mysqldump of your existing database if you want and just import that into the new installation if you have done a lot of configuration already, but that is probably your least hassle option.

[inthegenes]

(Thought I responded to this)

The servers are in a remote environment so starting from scratch with the new Vicibox ISO is not a feasible option.

[nysander]

why innodb is not recommended for vicidial? It keeps better database consistency than MyISAM

[mflorell]

No, InnoDB is NOT recommended for Vicidial, and no it does NOT keep better consistency than MyISAM for Vicidial use. I say this confidently after more than 11 years of Vicidial MySQL database management on systems with extremely high query rates and very large table sizes.

[inthegenes]

Hey Matt,

We have 11 boxes in a co-location. It would be very time consuming to reload via ISO. Is there any documentation as to the best way to upgrade the boxes remotely via the CLI. I would love to get out of the world of innodb and use the MariaDB as well as gain the benefit from any patches in the newest version.

[mflorell]

Depending on the version of OpenSuSE you have on those machines, you may just be able to upgrade to MariaDB by using yast. You may have to do an export and install the MariaDB binary install manually though if you are using an older version of OpenSuSE.

[inthegenes]

Thanks for the quick response,

We are using the 64bit version of OpenSUSE 12.1 (Asparagus). And we are using VICIdial 4 not 5. So we are still on MySQL, not MariaDB.
If I install MariaDB, what is the best method and or practice for the upgrade of the VICIdial suite on all of the boxes?

[geoff3dmg]

You *should* be able to install MariaDB as a drop in replacement for MySQL (that is what it's intended to do after all). I would do a backup and test (on a dev machine ideally) first.

Doing a SVN upgrade is the recommended way to upgrade the ViciDIAL system. You have quite a lot of releases to go through though so make sure you read the upgrade documentation thoroughly.

[williamconley]

Thanks for the quick response,

We are using the 64bit version of OpenSUSE 12.1 (Asparagus). And we are using VICIdial 4 not 5. So we are still on MySQL, not MariaDB.
If I install MariaDB, what is the best method and or practice for the upgrade of the VICIdial suite on all of the boxes?

You do not have to upgrade Vicidial to make this change.

And note that you "used" (past tense) ViciBOX 4 (not 5). ViciBOX is the installer. You're done with it. You are now using Vicidial 2.X.X (the dialer software). This software does not need to be upgraded/downgraded/modified in any way to switch from Mysql to MariaDB. In fact, you could leave mysql installed locally and use a different MariaDB server (external) as long as you time-sync the two servers (so php and mysql have the same time).

The mysql installation would merely be ignored (and the service could be turned off, but that won't make a noticeable difference in CPU usage when you are not using mysql anyway). This would allow you to make the jump without any actual changes locally except to the astguiclient.conf file on each server. And you'd be able to "go back!" at the drop of a hat.

[inthegenes]

Thanks for the responses,

The dialer is not in production at the moment so I will attempt the MariaDB installation and turn the mysql service off.

Is a SVN trunk upgrade recommended? Have there been major updates that would be beneficial to our production? We run up to 300 agents during peak seasons.

[geoff3dmg]

There's always nice things in the latest SVN, I'd do a checkout and read the commit logs and see if there's anything you need.

It's worth upgrading from asterisk 1.4 to 1.8 on your telephony servers (assuming they are running vicibox v4 like your db). The performance is a lot better I find.

It really doesn't matter too much as long as your SVN versions are in sync (I have 1x v5 DB, 2x v5 Web, 3x v5 Telephony, 1x v6 Telephony and 1x v6 Slave DB).

[inthegenes]

Ok,

I have successfully installed MariaDB using zypper. But for some reason the install command replaced MySQL 5.5.28 with MariaDB 5.2.13
When I tried to install MariaDB 5.5.28 using an rpm the service would not start. The error log stated that their was a conflict with the performance schema table.

So I removed version 5.5.28a and reinstalled 5.2.13. I am able to access the DB and create the asterisk DB using the vicibox-install script. Will the MariaDB version be an issue given my VICIdial version and build? Somehow the two DBs have different build versions, does that need to be rectified as well?

Master:
version-2.10b0.5
build-140817-0928
revision-2165

Slave:
version-2.10b0.5
build-140623-2147
revision-2165

I am guessing I am on the latest VICI version 2.10.

[mflorell]

If one of your databases has different values then the slave replication is not functioning properly, or you didn't see it properly before the replication was enabled.

[inthegenes]

I am in the process of redoing the replication with the MariaDB.

I was more concerned with the MariaDB version versus my previous MySQL version and the VICIdial version/build. My telephony and web servers are not on the same version as my DBs either.

5 x version 2.8b0.5 | 1983 revision
2 x version 2.10b0.5 | 2138 revision