MYSQL MARIADB CONVERSION

Here are some steps for MYSQL to MARIADB conversion



 MariaDB is a binary drop in replacement of the same MySQL version.

• MySQL 5.1 -> MariaDB 5.1 MariaDB 5.2 & MariaDB 5.3 are compatible.
• MySQL 5.5 >> MariaDB 5.5

Which means:

• Data and table definition files (.frm) files are binary compatible.
• All client APIs, protocols and structs are identical.
• All file names, binaries, paths, ports, sockets, and etc… should be the same.
• All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.
• The mysql-client package also works with MariaDB server.
• The shared client library is binary compatible with MySQL’s client library.

We can just uninstall MySQL and install MariaDB easly. (No need to convert any data files if you use same main version, like 5.1). We will have to [perform mysql_upgrade after conversion. This is to ensure that the mysql privilege and event tables are updated with the new fields MariaDB uses. MariaDb is highly adaptable with mysql.

• • There are some installation issues with PHP5 that you should be aware of ( REF:https://mariadb.com/kb/en/installation-issues-with-php5 ).

ADVANTAGES:

• Faster and safer replication: Group commit for the binary log. This makes many setups that uses replication and lot’s of updates more than 2x times faster.
• Indexes for the MEMORY(HEAP) engine are faster. According to a simple test, 24% faster on INSERT for integer index and 60% faster for index on a CHAR(20) column.
• CHECKSUM TABLE is faster.
• MariaDB improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvement is 1-5 % (according to sql-bench) but can be higher for big results sets with all characters between 0×00-0x7f.
• Pool of Threads in MariaDB 5.1 and even better in MariaDB 5.5. This allows MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
• There are some improvements to the DBUG code to make its execution faster when debug is compiled in but not used.
• Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The Aria storage engine is used for internal temporary tables, which should give a speed up when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn’t have to write the temporary rows to disk.
• The test suite has been extended and now runs much faster than before, even though it tests more things.

Now we are planning to implement mariaDb in vps servers of eleven2.

Steps to install mysql :

A. Backup mysql

1. mysqldump --all-databases --routines --triggers > /home/alldb-`date +%F`.sql

2. service mysql stop

3. cp -p -r /var/lib/mysql  /var/lib/mysql_mysql-`date +%F`

4. service mysql start

5. mysql -V  >> /root/mysqlversion.txt-`date +%F`

6. cp -p -r /etc/my.cnf /etc/my.cnf-`date +%F`

B. Disable the targets so cPanel no longer handles MySQL updates #ONLY FOR cPanel 11.36+

1. # /scripts/update_local_rpm_versions --edit target_settings.MySQL50 uninstalled
2. # /scripts/update_local_rpm_versions --edit target_settings.MySQL51 uninstalled
3. # /scripts/update_local_rpm_versions --edit target_settings.MySQL55 uninstalled

C. Remove existing MySQL RPM’s so theres a clean slate for MariaDB

1.  /scripts/check_cpanel_rpms --fix --targets=MySQL50,MySQL51,MySQL55

D. Create a yum repository for MariaDB

1. nano /etc/yum.repos.d/MariaDB.repo

2. paste

---------

# MariaDB 5.5 CentOS repository list - created 2013-06-23 21:13 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

--------

E. Install MariaDB

1. yum install MariaDB-server MariaDB-client MariaDB-devel

{If you are facign any issues while installing , remove php from yum.comf exclude and revert it after installation}

2. /etc/init.d/mysql start

3.  mysql_upgrade

4.  /etc/init.d/mysql restart

F. Rebuild Easyapache/PHP

1. /scripts/easyapache --build

Notes:

1. Please change mysql root password from whm if you are facing login faliures while accessing mysql.

2. Check logs for any error and change mariadb parameters as required.

IMPORTANT

Make sure mysqltuner is run in the server and the parameters are optimized after converting to mariadb.

DONE!!!!!

==-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=-0=0 )(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)(*)*()(*)*()(* &^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%&^%

WARNING: Only for debugging . DO NOT PROCEED FURTHER !!!!!!

Switching back to mysql {ONLY FOR SWITCHING BACK TO MYSQL IF MARIADB FAILS}

1.  Removing mariaDB 

 #yum remove MariaDB*

{Please check the dependencies which are getting removed}

2. Enable targets

 # /scripts/update_local_rpm_versions --edit target_settings.MySQL50 installed
 # /scripts/update_local_rpm_versions --edit target_settings.MySQL51 installed
 # /scripts/update_local_rpm_versions --edit target_settings.MySQL55 installed

3. Installing mysql

 #/scripts/check_cpanel_rpms --fix --targets=MySQL50,MySQL51,MySQL55

4. Restoring mysql 



 #mysql < /home/db_dump/alldb.sql  backup in (/home/alldb-`date +%F`.sql)

    Or
 #/etc/init.d/mysql stop
 #mv /var/lib/mysql /var/lib/mysql_mariadb-`date +%F`
 #cp -p -r /var/lib/mysql_mysql_date /var/lib/mysql
 #/etc/init.d/mysql start
 #mysql_upgrade