MySQL to MariaDB and Back Again benny Vasquez -- cPanel, Inc. - - PowerPoint PPT Presentation

MySQL to MariaDB and Back Again

benny Vasquez -- cPanel, Inc. Santa Clara, California | April 23th – 25th, 2018

hi, I'm benny.

right now

before cPanel

boring

boring

enough about me, though

Migrating your data safely

Migrating your data safely

STOP

Step 0: Backup!

Step 0: Backup!

Step -1: Check yo self

• Partition space is important

df ; df –I

Step 0.1: Full dumps of your database (scheme optional)

• Use mysqldump

[root: ~]# mysqldump -u root -p mysql user > `date +%Y%m%d`.user_table_dump.sql [root: ~]# mysqldump -u root -p --all-databases --skip-lock-tables > `date +%Y%m%d`.alldb.sql

Step 0.2: backup of the folder(s) and configurations

• Use cp, rsync, etc
• cp -a /var/lib/mysql{,.back. `date +%Y%m%d`} ; cp -a /etc/my.cnf{,.back.`date +%Y%m%d`}

Step 0.3: full server backups

• Wherever possible, hot-spare is your best bet.

The Environment(s)

Let’s get on the same page

Env{1} * CentOS 6.9 * Installed MySQL 5.5 * Installed Apache & PHP * Added WordPress Site

The Environment(s)

Env{2} * CentOS 6.9 * Installed cPanel & WHM * installed w/MySQL 5.5 * Added WordPress Site

MySQL à MariaDB

Fo’ reals this time.

6 easy steps

• Install the MariaDB repos

https://mariadb.com/kb/en/library/yum/

• Stop MySQL and remove it

service mysqld stop; yum remove mysql-community-server

• Install MariaDB with your package manager

yum install MariaDB-server MariaDB-client

• Ensure MariaDB is running

service mysql start mysql -V service mysql status

• Run mysql_upgrade

Finish the conversion

Things to keep in mind

• Migrate to the same version
• MySQL 5.1 -> MariaDB 5.1
• MySQL 5.5 -> MariaDB 5.5
• MySQL 5.6 -> MariaDB 10.0
• MySQL 5.7 -> MariaDB 10.2

MySQL to MariaDB; Env{1} Great Success

Env{1}: Success

3 easy steps

• Navigate to MySQL/MariaDB Upgrade
• Acknowledge the warnings
• Click continue

MySQL to MariaDB; Env{2} Great Success

Env{2}: Success

MariaDB à MySQL under 5.6

Easy peasy

6 easy steps

• Stop MariaDB

service mysql stop

• Remove MariaDB and its repos

yum remove MariaDB-server MariaDB-client

• Install MySQL
• Ensure MySQL is running
• mysql_upgrade

Things to keep in mind

• Migrate to the same version
• MariaDB 5.1 -> MySQL 5.1
• MariaDB 5.5 -> MySQL 5.5

MariaDB to MySQL, when < 5.6 Env{1} Great Success

Env{1}: Success

2 easy steps

• Manually adjust the mysql-version in /var/cpanel/cpanel.config

[root: ~]# grep mysql-version /var/cpanel/cpanel.config mysql-version=5.5

• Tell cPanel to check the installed RPMs and fix any problems it encounters

/scripts/check_cpanel_rpms targets=MySQL55,MySQL56,MySQL57,MariaDB100 --fix

Env{2}: Success

MariaDB to MySQL, when < 5.6 Env{2} Great Success

MariaDB à MySQL over 5.6

ugh

7 easy steps

• DUMP. YOUR. DATABASES. And privileges.
• Stop MariaDB

service mysql stop

• Remove your databases

cp -a /var/lib/mysql{,.bak}; rm -rf /var/lib/mysql; chown mysql. /var/lib/mysql

• Remove MariaDB and its repos

yum remove MariaDB-server MariaDB-client

• Install MySQL
• Grab the community repo, and then yum install it

https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/linux-installation- yum-repo.html

• Ensure MySQL is running

service mysql status

• Restore your databases
• If you’ve done good, beer.

Warning: swapping in place WILL corrupt your databases

MariaDB to MySQL, when ≥ 5.6 Env{1} eh Success

Env{1}: Success?

6 easy steps

• DUMP. YOUR. DATABASES. And privileges.
• Remove your database files

cp -a /var/lib/mysql{,.bak}; rm -rf /var/lib/mysql; chown mysql. /var/lib/mysql

• Manually adjust the mysql-version in /var/cpanel/cpanel.config

[root: ~]# grep mysql-version /var/cpanel/cpanel.config mysql-version=5.7

• Tell cPanel to check the installed RPMs and fix any problems it encounters

/scripts/check_cpanel_rpms –fix

• Ensure MySQL is running

service mysql status

• Restore your databases
• Also earns beer.

Warning: ”downgrading” in place WILL corrupt your databases, and WILL increase headaches.

MariaDB to MySQL, when ≥ 5.6 Env{2} eh Success

Env{2}: Success?

Conclusions and take-aways

ugh

ugh.

• If you can, avoid changes after 5.7/10.2
• Test, tinker, make notes
• Be prepared for the suck, and maybe you’ll have over estimated.
• Always reward yourself.

Thank You!

benny@cpanel.net

LAMP installation, to build your env mysqldump, for your backups Install MySQL 5.7 on your server cPanel docs for upgrade scheme optional

https://go.cpanel.net/pl18

Rate My Session