 Good morning everyone. My name is Nisha. I'm part of the MySQL team for over 7 years now. And today I'll be discussing about upgrading to A.dobe and how we have tried to make it an automated experience. A moment for the safe harbor statement. In this session I plan to complete, first discuss about the importance of upgrade and what are the upgrade options that is available for the users and how a straightforward upgrade to A.dobe would look like. And then get into the details of the process of upgrading to A.dobe and also I will discuss how we have tried to improve the upgrade experience overall in A.dobe. So why do we have to upgrade our MySQL installations? Generally the newer releases address some of the security concerns reported and also the major releases do come with a major performance improvement as well as the scalability. And yeah, the major releases again has a host of new functionality. And also it is important to reduce the technical debt that occurs with your MySQL installation. Consider you are on 5.6 now and you have to upgrade to A.dobe. The general guidelines is that you have to upgrade to 5.7 and then to A.dobe. Now you would have a lot of deprecated functionality within 5.7 which are then removed in A.dobe. This would mean complex upgrade procedure for you to get on to A.dobe. So why are the upgrades generally postponed? The users, the general apprehensions that the users have is do they have the sufficient knowledge in order to do the upgrade? Or do we have enough resources who can contribute to the upgrade? And of course the time taken and the cost incurred is in two cases. One is the cost incurred for the DBAs and consultants who will be doing the upgrade and as well as the cost for the lost business during the downtime when you do a switchover. At some point in time you do have to upgrade and the general input from the DBA is that they have to reduce the risk and the cost involved during the upgrade and also the total duration for the upgrade should be short. And for the customer application it is important to retain the older or my still behavior and then slowly go towards the newer behavior. And yeah, they do want to test the new version gradually and when switching the downtime it should be minimal. So what are the upgrade options available? There are two options. You can either do a dump upgrade or you can do an inflate upgrade. In case of dump upgrade the difficult thing would be to back up your data directory and then you dump the data from the existing MySQL dump using the MySQL dump utility and load the particular dump file using the newer instance of the MySQL and then subsequently run the MySQL upgrade. In case you do not want the system schema and you are interested only in the user schema you will back up your data directory. Dump only the user schema again using the MySQL dump utility and load this particular dump file using the newer MySQL instance and then run the MySQL upgrade. The other option available is the inflate upgrade where you again back up your data directory stop your old MySQL instance and then you change the binaries to the newer MySQL version you will have to adjust your config file to set the correct defaults and then you start your new MySQL server on the old data directory and run the MySQL upgrade. So inflate upgrade versus a dump upgrade what is recommended is the inflate upgrade. Why? Because inflate is generally faster than the dump upgrade and in case of dump upgrade as I mentioned previously you do have deputations and rules and there are chances there are certain syntaxes to turn on in case that you will have to go in and modify a dump file for you actually it will come to a newer MySQL instance in order to avoid that it is better to go ahead with the inflate upgrade. In the straightforward case you would have backed up your data directory and then started a newer MySQL instance on the old data directory you would run your MySQL upgrade and chase all your system tables and the user tables and generally it should be through the target issues and you restart your server, look at the messages and as you see there are no errors and then check if your apps and services are working fine. Prior to 5.7 the metadata was spread across in different formats that's your files, your system tables and as well as some amount of metadata was stored within the Unodb dictionary and the system tables use the MySQL storage engine and you might have come across these files like rm-driven and .opt in a.do it's all collated and it's stored in the dd tables which uses the Unodb storage engine and it's called the data dictionary so as I mentioned based on the inputs you need the upgrade to be faster and should be with lower risk so what we have done is try to eliminate the legacy issues with the metadata and we have transition from the legacy metadata handling to a transactional data dictionary and the upgrade process will produce a consistent data dictionary and also we have tried to help the TVAs who are applying to upgrade to a.do what we have done is we added a new utility file I mean as part of the shell we have a real checker called as the upgrade checker which helps you prepare for the upgrade to a.do and also during the upgrade we have enforced certain checks when the server comes up so that you don't have legacy issues getting into your new metadata store first there are a lot of course of new features you could look at the release notes, you could look at the blocks as well as the MySQL documentation to see what's gone in and couple of them being the transactional data dictionary, the atomic details the GIs, enhanced GIs support, roles persistent runtime configuration and much more as I said there are deputations and removals and the features that are removed are the credit cash and the support for non-native partitioning and the options in the variables that have been removed is related to error longer errors, longer earnings, it's replaced by a log error a verbosity which determines the kind of warnings or errors or loads that gets logged onto your L log and as well as the option related to secure all has been removed and so the list of remotes have been removed like create user or the brand in terms of the God management the password function has been removed previously you could create a user using the brand statement which is no longer supported and the syntaxes that are affected are extended and partitioned keywords it's been removed from the explain and slash n was used as a synonym for null then it's no longer supported we'll have to explicitly use null there has been a lot of changes in defaults and I'll highlight a few of them here the default character set and collation has changed to UTF-8.84 and UTF-8.84, 900, absent, insensitive and case insensitive the default preferred authentication is changed from native to the cache in shard and password and the ANO-DV ANU tablespaces has changed from zero to four so also it's no longer the MySQL system tablespace it's a separate system tablespace I'm sorry, a separate tablespace which helps in you could truncate your ANU or the log tablespace also like Narendra mentioned the log web has changed from off to on that's a new default as well as in order to work well when you upgrade from 5.7 you'll have to do a distance relation on EV master key this re-encrips your master key and stores it back to your tablespace I mentioned the most important has been the cassettes in the collation default changes so when you're upgrading your schemas from 5.7 to 8.2 what we do is we do have the log of 5 which does retain the the cache that was used and is persistent and when you upgrade you don't have to really do any additional step it still has the same character set now in terms of rolling upgrade with your if you have fun, I mean the difficult thing where you would upgrade your slave and then you would do your master's say your master is on 5.7 and you have a slave which is on 8.2 for the new schemas that get added on your master it would still follow the 5.7 defaults of Latin and you wouldn't really run an issue on the slave though it is on 8.2 and also the new tables which get added to the existing scheme up except the schemas character set so again you wouldn't really run into any issues when you get on to 8.2 on your slave then you have a rich checkup which is very useful what does it do? it actually checks whether your 5.7 installation it is ready for upgrade it is part of the 5.7 releases what you can do is you can run that particular tool on your 5.7 installation and look for what are the legacy issues that would cause an issue during the upgrade clean up all of that at your own pace and then proceed with the upgrade so as I mentioned it is part of 5.7 it is an active development and we are continuously adding more checks to it to improve the upgrade preparedness for your 5.7 and in the subsequent slides I have done a list of all possible issues that you might run into a great of course not all of your environments will run into these issues but it is just for the knowledge purpose that have listed all the issues so the usage of the old temporal amitypes we no longer supported on 8.2 so if you are 5.7 installation during your binary upgrade if at all it has been redeemed you need to clean up if you have conflicted in the object means and with the reserve keywords you need to rename all those and the usage of unit of 8 and unit of 3 this has been deprecated in 8.2 so we would throw their free checker catches these reserve table names in mysql schema we have the DD table names created and the DD table is created in the mysql schema if you have any user tables in the mysql schema which conflicts with these you would be getting errors when you run the tool and the foreign key names which are longer than 64 characters this is generally the table names which are 64 characters where each character utilizes say 3 bytes and then the foreign key name is not explicitly specified but is auto generated and you would run issues where those more than 64 characters which is all supported in 8.2 and the usage of obsolete SEO modes and if you have enough set call and definitions containing elements longer than 55 characters it's more supported in 8.2 usage of partition table and shared table spaces not supported in 8.2 usage of remote functions this is most likely like the GIS functions which are removed and usage of remote group by ascending and descending all the issues that are reported by a check table for a great command also gets listed when you run a great checker tool let's consider an example as I said if you have you do have user tables in the mysql schema which conflicts with your needy table names which are going to be created in 8.2 the checker would actually throw the warning so it can be detected with the mysql query by looking up with the mysql schema name and the list of the needy table names and what you'll have to do is rename the tables which have the conflicting names with respect to the needy table names I mentioned there are certain functions which have been removed for example point from text and you can look up from the information schema in order to figure out which are these functions what you'll have to do as part of the cleanup will be to use the ST or MVR alternative for the remote functions and that's the example during the binary upgrade it's possible that all the old data types retained in your system but you are no longer supporting it in 8.2 and it is essential that you clean up before you proceed upgrading to 8.2 what you can do is check table for a grid on 5.7 will be reported also the mysql shell upgrade checker would be both the same and what you need to do is to repair tables so that your tables get rebuilt and you no longer have the old data type but you'll have your new data type so once you run your upgrade checker on 5.7 installation and it runs clean you are all good to upgrade to 8.2 so what you do is pack up you are doing it directly you solve the version run mysql upgrade restart the server and you inspect the error log reconnect that and it should work fine and if at all as I mentioned we have enforced checks during the upgrade in 8.2 so if at all your upgrade checker does not run clean and you attempt to do an upgrade the upgrade is going to stop we revert back all the changes that's made so far and we revert back to the original 5.7 data directory you can still start your 5.7 data I mean server on it continue with your cleanup and then start with the upgrade else it will be refuse the upgrade our great plans are you want to continue improving the upgrade process you want to reduce the time and the risk that you can run it to when you are doing the upgrade by enforcing more checks ensuring no legs issues get into your new metadata store and the path of the time for increase of greed as I said we have the 5 base metadata which we have moved into transaction data dictionary so we do harvest the metadata for analysis then also store it in the duty to have the example in our stores all the metadata you need to be without changing with newer releases with the 8.2 server is self aware from which version it's upgrading so it handles the upgrade of your DD tables in itself so you don't have to worry about that yeah it's good for security reasons previously your mice can use the tables you can go ahead and actually do a couple of inserts on to your system tables and the integrity was just gone and we have hidden these certain system tables the DD tables are hidden so you can't do anything there and this enforces the integrity and remove the need for mice to upgrade client or work with progress and should be available soon and it's more to the mice to the server itself it's no longer an external tool that you'll have to run at the end of every upgrade it's part of the mice to the server and of course it's longer and will be an upgrade so the traditional I had explained the traditional way of doing an upgrade now what you do is stop the old mice to the server change binaries to the new version adjust your content files for the new server version and then start your mice to the server as I said it transitions from your old way of storing the metadata to the DD tables so it analyzes and does an automatic upgrade and this makes it faster and with the work in progress when we have this remote you no longer have to run the mice to upgrade explicitly you don't have to restart the server because you perform the upgrade and the server is up and running so what we've done lower risk in terms of lower risk we have introduced the upgrade checker which identifies all sort of issues that you could run into which you can run on your 5.7 installation itself and there's an active development where continuously adding more checks enforcing more checks to it of course the metadata integrity as I mentioned we have it in the system tables and some of the DD tables and you no longer can insert explicitly into it and that way we are enforce the integrity of the system tables faster upgrade process because we do the metadata analysis as well as the metadata upgrade and with the removal of the disk and upgrade you lose out on an additional step you don't have to do the additional step and let's take care of the server step the simplified upgrade process because it's fewer steps now and it does all the metadata upgrade as well so you prepare for the upgrade and the other resources in order to figure out what are the changes that's gone into the version and run the mask in the upgrade checkbook and fix all your issues and run it until it's clean fix it and then run until it's clean and test your applications on New York 8.2 and do the backup and upgrade to 8.2 these are the resources there's a documentation and also also you have the blocks that can be downloaded