Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Administrator Backups: "Compatibility Mode", What Exactly is this doing?

Tags:

mysql

backup

In Mysql Administrator, when doing backups, what exactly is "Compatibility Mode"?

I'm trying to bridge backups generated by webmin with the upload tool available inside mysql administrator. My data already has a couple of inconsistencies (ticks, commas, etc, I think) I just wont try to kink out (they might just reappear in the future anyways). These kinks generate errors when I try to restore out of my backups.

Now, if I generate backups from webmin, and then use MySQL administrator to restore them, they fail. But if I generate the backups using MySQL Administrator AND tick "Compatibility Mode", then head over to MySQL administrator (another instance) and restore... it works!

According to MySQL, "Compatibility Mode" is;

Compatibility mode creates backup files that are compatible with older versions of MySQL Administrator.

Webmin, on the other hand, gives me the following options for compatibility:

  • ANSI
  • MySQL 3.2.3
  • MySQL 4.0
  • PostgreSQL
  • Oracle
  • Microsoft SQL
  • DB2
  • MaxDB

Which would you say is a best fit? My data set is very large, so it would take quite some time to experiment one by one (specially whence thinking might beat brute-forcing it).

Edit: seems like it's doing ANSI, but i'm not 100% on it.

like image 859
mauriciopastrana Avatar asked Aug 12 '08 00:08

mauriciopastrana


People also ask

How does MySQL backup work?

MySQL supports incremental backups using the binary log. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup.

What are different tasks that can be performed by MySQL administration?

MySQL administration offers many tools which help to serve for server maintenance, server configuration, database backup, Security, user roles, initialize/shutdown, duplication supervision, repair, restore, resource managing, performance check, status report, access records, CRUD operations SQL, granting permissions, ...

What are the different backup types available in MySQL?

There are two backup types: physical and logical. Physical (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup), and also you can use cp or rsync command lines to copy the datadir as long as mysql is down/stopped.


1 Answers

Compatibility mode - the mode that helps you create exports compabible with different versions of MYSQL or other databases.

You see, some versions of MySQL had different commands that were used in various versions. So what compatibility mode allows you to do is take a database and export the SQL to be compatible with another version of MySQL. Thus, you may want to upgrade your MySQL 3 server to 4 - this compatibility mode allows for the export your database or individual tables to create a SQL file that can import into a MySQL 4 version server (should work in 5 also).

I use webmin, also, and run MySQL 5. I use compatibility mode for MySQL 4.... I steer clear of any of the other ones, because I'm not running those other databases.

As far as the MySQL commands that were different between MySQL 3.x and 4.x, I believe there were changes in regards to how CURRENT_TIMESTAMP is translated from MySQL 3 to 4, and also MySQL 3 doesn't support charsets, according to this forum post here: http://www.phpbuilder.com/board/showthread.php?t=10330692

like image 179
JasonMichael Avatar answered Nov 14 '22 13:11

JasonMichael