MySQL questions

[Songstuff]

Hi

I am working on a move from MySQL to MariaDB. At the same time I want to tidy my database. I hope you can help?

My DB is 5.5.54. I plan to upgrade to MariaDB 10.1 in stages (Mysql to 5.5.54 to MariaDB 5.5.54, then to MariaDB 10.1). I will also be starting on Amazon Linux and ending on CentOS.

In my current conf_global.php I have these lines:

1. My database is a mix of MyISAM and InnoDB tables. I don't know how it happened, but it did.

I know I need to be aware of full text indexes. I know I need to convert each table via MySQL to InnoDB, but before I run the conversion queries....

From my conf_global.php;

$INFO['mysql_tbl_type']                 =       'MyISAM';

Can I safely change $INFO['mysql_tbl_type']                 =       'MyISAM'; to $INFO['mysql_tbl_type']                 =       'InnoDB';?

2. My database appears to be utf8mb4. I ran the conversion. All tables seem to say utf8mb4. So do all appropriate columns. but...

Do I need to convert performance_schema, mysql, information_schema databases to utf8mb4 before changing the server defaults for character set and collation within my.cnf?

Additionally my conf_global.php has these lines:

$INFO['sql_charset']                    =       'utf8mb4';
$INFO['use_utf8mb4']                    =       'true';
$INFO['sql_utf8mb4']                    =       'true';

I believe two are legacy from old versions of the boards (my board has been running for more than 10 years). Which should I keep and which can safely be removed?

3. I have a number of old tables, some from 3rd party plug ins, but some from old IPS modules, like subscriptions, IPS tracker etc. I don't use any 3rd party plug ins just now.... so, if I compare with the raw mysql tables from IPS, can I safely remove IPS subscriptions and tracker etc tables? My guess is yes, but carefully and with back ups.

 

Not my area of expertise. Big changes and I am trying not to break anything, so some help would be much appreciated.

Cheers

John

 

 

 

[Songstuff]

Can anyone help here?

 

Can I just add... never use AWS Amazon Linux. Never. What a pain in the ass.

[Morgin]

Can't help but would love if someone else could as I will be doing same MySQL to Maria migration and have same myisam to inno questions

[sudo]

20 hours ago, Songstuff said:

Can I just add... never use AWS Amazon Linux. Never. What a pain in the ass.

AMI is all I use now for cloud, what issues were you having? The easiest thing to do is to switch to Mariadb, you just add the repo, remove MySQL and install MariaDB and you're done. AWS RDS is also pretty good.

[Songstuff]

When I try to do that on Amazon Linux I get conflict errors. Amazon linux is based on a Red Hat / Centos build but isn't exactly either. it is currently version 5.5.54 mysql and OS Amazon Linux AMI release 2016.09. Perhaps I am using the wrong mariaDB?

 

[Songstuff]

So when I yum install MariaDB-server and MariaDB-client I get these errors:

Error: mysql55 conflicts with MariaDB-server-5.5.54-1.el7.centos.x86_64
Error: mysql55-server conflicts with MariaDB-server-5.5.54-1.el7.centos.x86_64

 

I get same errors with el6

 

my understanding is that maria should install over the top of mysql... without removing anything

[sudo]

Yeah remove those packages, you DO NOT want to install over the top like you think, that would mean every time AMI updates their version it would override mariadb.

When you remove the mysql packages your database files are left behind. You just install Mariadb after. I would strongly recommend you backup your my.cnf for mysql just so you can compare and set it up again in case for some bizarre reason the paths are different for mysql over mariadb.

Use MariaDB's 10.1 repo as well. I do however use RDS Mariadb with it managing the databases and backups and I would recommend it.

[Songstuff]

Upgrade to PHP 7 and MariaDB 10.1 went smoothly. Just to do the MyISAM to InnoDB table upgrade and sort out the conf_global stuff.

Can anyone advise about the conf_global variables?