Mysql DB server OS change:
MySQL Oracle
Mysql DB server OS change might be required in different situations, but for those who do not know what I am talking about, let me start by explaining what this is and why you might need it in some situations. Just to serve as an example; let say you are renting a few Virtial Machine Server from X IDC or cloud provider. This cloud provider gave you an a distribution that in 2013 had 4 years of support and everything was cool, you installed you systems. Now is 2017 and you need an upgrade. Let me give more details.
You are running a web environment with load balancers, web-server, and 2- Mysql DB servers Master and slave replication, etc. Your application needs High availability and you can not afford to have a lot of downtime. Your system is running fine, but last month you got a notification that the EOL (End of life) of your distribution is in 2 months. You also you noticed that your Percona MySQL DB 5.5 has a few limitations that you want to resolve by upgrading toPercona MySQL DB 5.6 or 5.7. So this is the perfect opportunity to upgrade the system and not remain with a legacy OS that will lose support soon. So now you know all you need to know and you are decided to go with progress and make a difference with the change.
MySQL: MariaDB community logo
You contact your provider about upgrading your system. They reply with a link (URL) to their FAQ saying that they do not support system upgrade. Then you start reading about it and notice that many virtualization services providers do not really support the upgrade. So you can not just du apt-get dist-upgrade. Not that you can not try, but you will likely not succeed, own experience here long ago when I was young and stupid. Maybe your provider supports it, but you might have grown fond of some other distribution over time and you want to change let say from Debian to Redhat or vise-versa, change of company policy, etc. So reason for wanting to change are many.
At this point some people decide to just live inside their own shell and keep CentOS 5.4 running FOREVER. I do not think is a god decision, but life is complicated. I am not one of those, so I have decide to move on. At this point thee are normally 2 options sometime only one.:
- Some cloud providers support Disk OS change and you have 2 DB servers, you you can upgrade one while the other takes the load, have if upgraded and working, move everything to this server and then upgrade the other server. This is a vey simplified version of the whole procedure the we will be exploring further.
- Create a separate machine exactly the same as production while production is working and then swap when you are ready. This procedure is far more simple, so I will not talk about it. I will just point out some facts so it can help you decide, but this port covers useful issues to keep present in both cases:
MySQL Percona
- You might have IP address constrains depending on the provider you have and the agreement with them. normally option 1 supports keeping the same IP addresses so your application/DNS will not be lost with who is the DB server.
- Cost constrains you might not get approval to spin another system like production at the same time as some migrations take time and money.
- If you have many partitions without amounts of data copying from old server to new server might consume amounts of time that you just can not afford to use. while if you have the OS in a separate partition, you just replace that disk and all the other disks reman the same.
There are other reasons, but those are the more important ones. In my case the more suitable option was option one, but you might have a different situation. I am going to post as much exact information as possible but this is not intended to be a step by step procedure, more like a general guide and explanation of reasons. So here we go with the Mysql DB server OS change:
Further details about Mysql DB server OS change and upgrae:
In our case we will have 2 Percona MySQL DB 5.5 servers (DB1- master and Db2 slave). The replication is working. if you want to learn about MySQL replication there are many good how tos out there, I will try to post something in the future, but the truth is that I do not thin is really required. We will start by upgrading the slave and that is what is mainly covered on this study case. Then the next study case will be switch mater to slave and upgrade master wit a procedure like this. the FINAL result of this should be DB2 with a different OS and upgraded slave to Percona MySQL DB 5.6. which is about almost half of the work to get both servers upgraded without downtime.
1- First as always we backup everything we need. This is for my particular case:
# mysqldump -u root -p --max_allowed_packet=512M --opt --routines\ --triggers --events --flush-privileges --skip-add-drop-table --dump-date\ --all-databases | /bin/gzip > /var/backups/alldb_backup_31052017.sql.gz tar -cvpzf ~/etc.tar.gz /etc tar -cvpzf opt.tar.gz /opt/ --exclude "/opt/backup" tar -cvpzf zabbix-home.tar.gz /home/zabbix tar -cvpzf var.tar.gz /var/ --exclude "/var/backups" --exclude "/var/log" scp *.tar.gz yser@A.B.C.D:~/ <cloudtool> --upload /var/backups/alldb_backup_31052017.sql.gz
Options for backup with vary depending on the situation. Many cloud providers support snapshots, for MySQL particularly, keep present that if you do not stop the service it will likely cause data corruption. I used Mysql dump which also works, in this case I passed a few parameters specific to my situation, you can run “man mysqldump” to know what they do, just to explain one. max_allowed_packet is the maximum size of the buffer for client/server communication. The default is 24MB, the maximum is 1GB. This is required when you have big DBs to dump. if you are using MyISAM, innoDB or mixed environment you ill need to change the options.
2- You need to have the a master log position while the two server are still synchronized. That was the case at the moment of the works. To get it done for that there are a few techniques: one of them is to just get get a the master’s bin-log position(Db1) and then stop slave (Db2). you can flush the logs before, etc, but it works the same. In my case I have a separate partition for MySQL so once I stop the replication I can freely change the OS and my data will still be the same. Check that the binary log of DB1 is at least a few days in case you get in trouble you logs will still be available on all the upgrades are done. so that is simple. On master:
mysql> show master status; | File | Position | Binlog_Do_DB (...) +------------------+-----------+ | mysql-bin.006862 | 101134248 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.006858 | 268764501 | | mysql-bin.006859 | 270999411 | | mysql-bin.006860 | 272798282 | | mysql-bin.006861 | 268495314 | | mysql-bin.006862 | 101134248 | +------------------+-----------+ >5 rows in set (0.00 sec)
3- On slave:
stop slave;
Keep present that at this point you just have the master. If you mess up the master you have to rolback plan for latest data.
4- Stop MySQL and other services, step3 is not even required here, but I like to have the things done in order, and is better for understanding sake. I am stoping mysql now because I want to umount the mysql partition before anything happens. So my data is secure.
# service mysql stop
5- (Optional) This is a good moment to take cloud snapshots if you have the option as you data is consistent and you system is still intact. This ensure you can rollback.
6- Unmount the MySQL Drive :
# umount /var/lib/mysql
MySQL DB CentOS Chinese Cloud provider
7- In my case I am using one of the could providers that gives the option of replacing OS Disk replacement. My requirement specifically was to go from ubuntu to CentOS. Not really important. The important part is that at this point you will the able to replace you system with another system; could even be from Debian 7 to Debian 8. It depends on what your cloud provider supports. I am not going to say many details about this, let me just port a picture of the options I had at the moment.
8- (Optional, but recommended) At this point you have a new Fresh OS so you can login remotely and configure all the basics before installing the new version of MySQL DB:
- Backup setup
- Monitoring
- Firewall (FW) setup
- pass/keys updates, etc.
9- Install MySQL
# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm # yum install Percona-Server-server-56 Percona-Server-client-56.x86_64 Percona-Server-shared-56.x86_64 Percona-Server-devel-56.x86_64 Percona-Server-shared-compat.x86_64 percona-toolkit
– Make sure msyql is stopped.
# service mysql stop
if your install was successful it should have created the folder that will hold the MySQL data in /var/lib/mysql. Consider that you already have that folder in the drive that you kept from old server. So now need to move the file from that folder and replace with mysql
10- Attach and mount MySQL drive again to the right place:
# mount -t ext4 /dev/mapper/domuvg-mysql /var/lib/mysql ** make sure mysql user own the folder and can write.
11- At this point you need to make configuration changes to my.cnf. I mean if you have a relatively busy Db your config will not the default mysql configuration for sure. Keep present that some things change from MySQL version to version and the Db likely will not start, keep calmed and be pragmatic. On this regard you can try to start and if it does not start then check the logs. if there are now logs is much easier, just check what the error is a look for the config option and act on it depending on the problem. If there are no logs, then you have it a bit harder, but in general what I do is start with a very simple config that allows me to start mysql and then add the options in small batches so and stop and start the server. Then can start disabling options depending on the error until you have all the options you need. For the ones that do not work it might be because the option is deprecated or the acceptable values changed.
I am not going to talk much more about this as the configs are well documented on Percona and MySQL official website. Let me just give an example:
12- Create temp dir for mysql with proper ownership, this file is in the config file options as “tmpdir”, soetimes depending on your actual load you might want to make this directory a partition so mysql does not write too much.
# mkdir /tmp/mysql
# chown mysql:mysql /tmp/mysql/
# chmod 700 /tmp/mysql/
19- Start Mysql:
# service mysql start
*if any problem then check the logs and fix. feel fre to post questions if you have any.
20- run: mysql_upgrade -u root -p
21- Connect to MySQL to test :
# mysql -u root -p
22- Import DB1 dump into DB2 :
mysql -u username -p < alldb_backup_31052017.sql.gz
23- At this point you need to rebuild replication. Rebuild replication I will talk about this in a further post, but meanwhile check out this post where the replication is setup for a different sitiation. It is basically a standard change master to new master to the recorded position at the beginning on step 2.