InnoDB, MyISAM, MySQL and MariaDB

[LiquidFractal]

Ok, so my Invision DB is about 100MB.  I've converted from mySQL to MariaDB, but I'm understanding from this thread that I should convert from myISAM to InnoDB.  I have a fully managed VPS; is this something I can just ask them to do?  I'm obviously leery about doing this without downloading a backup of course.

[Makoto]

58 minutes ago, liquidfractal said:

I'm obviously leery about doing this without downloading a backup of course.

 

Never do anything to your database without first making a backup.

Don't even breathe on it without making a backup.

1 hour ago, liquidfractal said:

I have a fully managed VPS; is this something I can just ask them to do?

 

Probably. Maybe. Depends on their specific policies; "managed" could just cover basic installation and operation of software, not performance tuning. Just be sure you make a backup before you ask them to do anything.

[ABGenc]

1 hour ago, liquidfractal said:

Ok, so my Invision DB is about 100MB.  I've converted from mySQL to MariaDB, but I'm understanding from this thread that I should convert from myISAM to InnoDB.  I have a fully managed VPS; is this something I can just ask them to do?  I'm obviously leery about doing this without downloading a backup of course.

As @Makoto said never do anything without a backup. Actually you should have backups daily to be on safe side.

You dont need to download it . Since it is a VPS you can keep it on server and if anything fails it will be instantly available.

It is very likely that they will refuse to help you convert your tables cause it is a VPS but you can never know without asking

By the way there are other issues . When you convert tables from MyISAM to Innodb, SQL Server requirements change as well. You should be editing my.cnf file accordingly.

[RObiN-HoOD]

2 hours ago, ABGenc said:

By the way there are other issues . When you convert tables from MyISAM to Innodb, SQL Server requirements change as well. You should be editing my.cnf file accordingly.

Can you please elaborate on this? what and why?

[ABGenc]

3 minutes ago, RObiN-HoOD said:

Can you please elaborate on this? what and why?

I am not an expert but I had to review my my.cnf file for every setting as some were not suitable and even unnecessary for InnoDB and some were missing. There are articles on the internet and here is one I have found by googling.

https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html

And since every installation is unique to itself there is not a straight answer to what and why..

[LiquidFractal]

@Makoto @ABGenc Thanks for your replies.  My VPS providers are great (they took care of everything when switching me from mySQL to MariaDB), but in case they can't do this for reasons of policy this info is very useful.

[AlexWright]

16 hours ago, liquidfractal said:

@Makoto @ABGenc Thanks for your replies.  My VPS providers are great (they took care of everything when switching me from mySQL to MariaDB), but in case they can't do this for reasons of policy this info is very useful.

Hi there, I'm on a managed VPS as well, using mariaDB 10.1. We used the following to convert our tables all at once. (Backup first!)

Log into your phpmyadmin (through either the cpanel running the site or the WHM). Select query, and run the following script:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB row_format=dynamic;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

 

This will print out a list of all your IPS tables that are still using MyISAM, and the list will include the functions necessary to convert all your tables over to InnoDB. You will need to open the options dialogue on the output page and select the "full text" option.

 

Select all of these individual querries (we did this all at once without issues, but we are a newer community). Run them like you did the first query above. If you run into issues, it'll likely be from the my.cnf (your MySQL config file). Once I get home, I'll post the one we're using here.

 

Cheers.

[AlexWright]

On 5/18/2017 at 5:28 PM, liquidfractal said:

@Makoto @ABGenc Thanks for your replies.  My VPS providers are great (they took care of everything when switching me from mySQL to MariaDB), but in case they can't do this for reasons of policy this info is very useful.

As promised:

[mysqld]
innodb_file_per_table=1
default-storage-engine=InnoDB
performance-schema=0
max_allowed_packet=268435456
open_files_limit=10000
bind-address=127.0.0.1
innodb_file_format=BARRACUDA
innodb_large_prefix=1

Cheers.