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. 2

right now 3

before cPanel 4

boring 5

boring 6

enough about me, though 7

Migrating your data safely

STOP Migrating your data safely

Step 0: Backup! 10

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. 11

The Environment(s) Let’s get on the same page

The Environment(s) Env{1} Env{2} * CentOS 6.9 * CentOS 6.9 * Installed MySQL 5.5 * Installed cPanel & WHM * Installed Apache & PHP * installed w/MySQL 5.5 * Added WordPress Site * Added WordPress Site 13

MySQL à MariaDB Fo’ reals this time.

MySQL to MariaDB; Env{1} Great Success 6 easy steps Things to keep in mind • Install the MariaDB repos • Migrate to the same version - MySQL 5.1 -> MariaDB 5.1 https://mariadb.com/kb/en/library/yum/ - MySQL 5.5 -> MariaDB 5.5 • Stop MySQL and remove it service mysqld stop; yum remove mysql-community-server - MySQL 5.6 -> MariaDB 10.0 • Install MariaDB with your package manager - MySQL 5.7 -> MariaDB 10.2 yum install MariaDB-server MariaDB-client • Ensure MariaDB is running service mysql start mysql -V service mysql status • Run mysql_upgrade Finish the conversion Env{1}: Success 15

MySQL to MariaDB; Env{2} Great Success 3 easy steps • Navigate to MySQL/MariaDB Upgrade • Acknowledge the warnings • Click continue Env{2}: Success 16

MariaDB à MySQL under 5.6 Easy peasy

MariaDB to MySQL, when < 5.6 Env{1} Great Success 6 easy steps Things to keep in mind • Stop MariaDB • Migrate to the same version - MariaDB 5.1 -> MySQL 5.1 service mysql stop - MariaDB 5.5 -> MySQL 5.5 • Remove MariaDB and its repos yum remove MariaDB-server MariaDB-client • Install MySQL • Ensure MySQL is running • mysql_upgrade Env{1}: Success 18

MariaDB to MySQL, when < 5.6 Env{2} Great 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 19

MariaDB à MySQL over 5.6 ugh

MariaDB to MySQL, when ≥ 5.6 Warning: Env{1} eh Success swapping in 7 easy steps • DUMP. YOUR. DATABASES. And privileges. place WILL • Stop MariaDB service mysql stop corrupt your • Remove your databases cp -a /var/lib/mysql{,.bak}; rm -rf /var/lib/mysql; chown mysql. /var/lib/mysql databases • 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 Env{1}: Success? service mysql status • Restore your databases 21 - If you’ve done good, beer.

MariaDB to MySQL, when ≥ 5.6 Env{2} eh Success Warning: ”downgrading” in place WILL corrupt your databases, and 6 easy steps WILL increase • DUMP. YOUR. DATABASES. And privileges. headaches. • 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. Env{2}: Success? 22

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. 24

25

https://go.cpanel.net/pl18 LAMP installation, to build your env mysqldump, for your backups Install MySQL 5.7 on your server Thank You! cPanel docs for upgrade scheme optional benny@cpanel.net

Rate My Session 27