Migrating MySQL to a New Server - Percona Community MySQL Live Stream & Chat - August 5th

Our experts run a demo and took us through all steps to migrate MySQL database to a new server. Get more tips to improve your database skill with Dave Stokes and Marcos Albe.

Video

Transcript

Dave Stokes:
Amazing right that’s so cool. That’s it. Good morning, good afternoon. Good evening, depending where you are. I’m Dave Stokes. I’m a Technology Evangelist with Percona. And I have Marcos Albe with me. And he’s going to talk about migrating to a new server migrating MySQL to a new server. Are we upgrading at the same time? Are we doing anything different from the old machine?

Marcos Albe:
Well, you know, I lived I left that as a surprise, you will give me, you know, if you want we can go to rds, we could go to a different version. For example, I’m running Percona Server eight, zero 28. We could go ahead and do an upgrade, for example, or to eight zero 30. So that that is for that that will showcase one migration, but I don’t think we have an RDS machine ready. In essence, you know, that are two big migration paths. One is take an extra backup and use that to perform some form of in place replacement of your database. That usually works well. You know, within the same family, or for example, if you have if you’re migrating to RDS, you can certainly use extra back as well, so that that is actually, in the RDS manual from Amazon, you’re going to find that they describe how you can take a backup with extra backup, put it in an s3 bucket. And from there just, you know, prime, an RDS setup. So that could be one way to do it. Another way is like I am running Percona Server eight, zero 28. For, you know, imagine, for whatever reason, bad reason, probably, I decided to move to MySQL, from Oracle, and you know, like, I could take the backup from Percona Server. And as long as I have not use it any theater that will modify the binary format on disk, I should be able to go ahead and use the same backup on an eight zero 30 from community version. And the last will be if I wanted to migrate to something like let’s say, MariaDB, you know, then in that case, what I will do is, I will produce logical backup of the database, which is a text representation of the data. So it will dump to a text file, all the create tables, the create indexes, and the insert statements that are necessary to fully recreate all my tables and schema. And with that, you can actually go to very much any database, as long as it supports the data types and the type of indexes. Or for example, if you use spiritual columns in MySQL, then, you know, your target database should support these columns. So as long as it’s syntax compatible, it should be doable, the logical dump. So what would you like me to show you send your data, Stokes?

Dave Stokes:
In the past, when I’ve done migrations, it’s usually to a much bigger machine that needs a different configuration. And I’ve always used something like MySQL dump, or RCP you know, one system and all that. I really love extra backup because I find this functionality is amazing. What general, for moving stuff over B, I my first rule always was first thing get all the MySQL schema stuff working first, make sure the Logins work first, and then I did a schema by schema, is that the easiest way to do it? Or is that the hard way to do it?

Marcos Albe:
So yeah, what I personally will do many times and what I suggest to customers is take a dump of only the schema without data and that’s very easy, you can do MySQL dump dash dash, no data. And with that, you can actually have a very easy way to make sure your schema is compatible in the target. Because if the schema is compatible, then the data inside the schema should be compatible and should bring no issues. So certainly, that will be my first step. Let me share my screen very quickly. Of Sorry, what is my terminal huh? Sure screen. I’m going to share this one how do you share only a window here this I’m gonna share only my replica here this share. So you see me there Right. So I have my main DB here and I have a replica here not sure I’m gonna be able to access just like that.

Ah, okay. So I need To reset up replication, but anyway, I’m gonna take the backup usually I will tell you take the backups on the replica, you know, because it’s, there’s always the risk that you have some long running transaction that is gonna, you know, screw you up with your backups. So it’s best to always, whenever you can, it’s best to take backups on the replica, it’s not that you cannot take backups on the source but it’s generally a good practice to do it on the RIP. The routines because you want to recreate every object in the database that is the end you want all databases and actually you don’t truly want all databases I normally do something else here I’ll show you in a minute but they see and most straightforward is to do this and then you can remove manually the MySQL schema. I don’t want MySQL schema I don’t want the reformer and I don’t want information see and so you get a quick list of all your tables and so with these you can do my sequel there’s no data cleans three years. I’m just going to remove my old installation and then we’re going to do your search. Are you ready? Because you know I got crazy today and I’m just gonna run MariaDB. So now I got system control stored in a SQL we now. All right, guess we’re going to learn how to run MariaDB. I’m gonna let’s pretend that was never there. Let’s reinstall the server controls Okay, so there you go. I got my dump there. And I can MySQL. Okay, move to just say, okay, oh, I got the route there you go. I’m just going to import SQL. Okay there you go. Some like population. Alright. So this is kind of things in the migrations that you saw with this exercise Precisely right? Like you get the incompatible beats. I never remembered when Maria DB wants top of my head I’m not gonna lie to you. So how do we solve the collation with MySQL? There are actually more compatibility settings with MySQL dump. So again, depending on where you are migrating you might need create database okay how do you do that? Let me think for a second. Well, there’s always a solution. We just have to look for it. Now the folds I assume there is a solution like I am pretty sure brings comments to us skip comments. That’s one compact pose. Keep creative just. Steal still insists on using my collation here. And this is what my day looks like many times I look for solutions to suspected versus which ones okay, I can try with a different character set compress. And you know there is this convertible option which might be what we need to do in the end in there is let’s set the default character sets the full character set you really want to add the character set. Without my SQL only stuff because that’s how you migrate to other databases. Like imagine I wanted to migrate to Postgres, you know like the character sets and the collation names will never be the same. This thing should allow you to just jump dump without those that buffer length and it’s really no option to remove those that’s weird please, no, no wait, that’s funny. Okay, so what I will do in this case is I will set the heck out of it if needed. I’m just going to try one more time with comfortable. Let’s try company mode NC feel the same thing. At least it doesn’t add it.

So it only does it on the Create Table. And look, if you look at other databases like if I was migrating to Postgres, it will not keep me trouble. Because these type of comments in my SQL are only executed conditionally. If your version for example is large is greater than 4.1. And then, in my sequel, things will get executed in Postgres they want. Now the problem is, I might try to migrate to Maria dB. So Maria DB understands this type of conditional comments, and it will execute them all the same. It’s just that silly enough, it doesn’t support this character set. Dreadful character set. So what I’m just going to do is the ugliest thing you can imagine, which is this, just gonna go here.

So there you go, I told you that many times, you will have generate, like incompatible stuff, like literally incompatible stuff. So, at this point, you know, you will have to go back to the design board with your developers. And, you know, find what you want to do, because Maria DB is not compatible with filter columns that like doesn’t support this syntax for virtual columns. Let’s see what this is.

Dave Stokes:
Now are the implementations of JSON between MariaDB and MySQL compatible at some level?

Marcos Albe:
I got me on that one, I assume mostly. Because those functions were introduced it I think five, six. And Maria DB was still, you know, quite close in terms of source code. But I truly, truly will test it. You know, like anything you asked me about MariaDB at this time of, at this point in life, I will test it because I no longer trust is compatible in most ways. So yeah, you know,

Dave Stokes:
MySQL has a JSON data type. And Maria kind of emulates it. And the MySQL basically sits on top of blob, but they’ve done some other tricks to it.

Marcos Albe:
Yeah, I know, you can do like in place replacement of a core of field, right? That is the major trick, which I think is great, because otherwise you’re pushing this big JSON blob into the binlog every time you update something. So let me see if I can, you know, at this point, it’s a job of trying to understand how to migrate the schema right. And honestly, I don’t even remember what returning means in this context. I will have to look up the particularities of returning if there is any, otherwise, just Oh, it’s just saying, chop it down to 512 You are the JSON guy. So is this guy telling me chop it down to 512 characters length, they pass a book on JSON. So I am hoping he will be able to tell me this. So do you remember that bit? Dave? Like dude, do you can cut out the result length with this returning char fight off.

Okay, so it’s casting disgusting it is I will be writing it creates a decimal and it generated always with JSON valid JSON column, I ended with turning churn. So if one would want to get rid of all of it, it will be like this. And then I will find some work around either I will use left 512 If it’s trimming to 512 characters, or I will cast as char and then trim to 512.

So what I did is I added this to the definition of this vertical column. So to in an attempt to replace this, right, but again, this is the type of things you find in migrations, right? Like the difficulties come with these small details, the big things usually work, okay. The small details are the pain character sets, you know, definition of functionality that came into rather recent versions of the database, and stuff like that. So that’s it, now I can create my database. So if I had this issue in real life, I will finally you know, opt to prepare this table on the side and just add manually or again use CEP like I did before, or use some form of automated addition to replace this single line and make it compatible with the target database. So that that will be my approach. In this case, just to simplify things, what I’m going to do is I am going to set the hell out of it again.

Another option you have is and which might be preferred by many is create an intermediate MySQL instance, you know, like a temporary dummy MySQL instance. Just bring it, bring it up, load the schema into that instance. and do any alters you want, like you could alter the collation with one compatible with valid dB, you could alter this column and make it compatible with Maria dB. So you could actually do all the alters you want to the schema, then dump the schema again. And finally, importantly to be but the way to find what things don’t work with Maria dB, is what we’re doing now, which looks silly, but I don’t know of any better way unless you have some infinite memory to remember every detail that might be incompatible. With which it’s not the kind of things I do because I know memorizing things in databases is not a good idea.

Dave Stokes:
Definitely memorizing is not my strong forte, are there any tools let you compare character sets between the two implementations that you know, because I know UTF eight used to be kind of open discussion and then Oracle came out with the UTF eight MB four which is the Unicode nine style.

Marcos Albe:
Set and you can find out what you have and you can do the same for correlations. Correlations so you know, what was it so correlation okay just one.

Dave Stokes:
20 years ago a lot of us were running the Latin one character set with the Swedish case-insensitive accent sensitive coalition or whatever the equivalent was back then and now with all these other coalition’s and character sets it’s a lot more complex.

Marcos Albe:
You know the correlation that still exist so and I can tell because it didn’t print out anything here. I can probably use performance schema or information I think information schema has it as well in a way that I can actually count show.

I’ll show you can do the proper thing which is select count star relation word relation equals like Patience, Jesus. Hey guys, so there is cedar, you have those collections, meaning do need to figure out how to do. So you can build these kinds of one-liners, you know, to help yourself, find out if you have like a very large collection of tables and databases with a diversity of correlations. Like, if you have that you actually have some other problem, because you shouldn’t have such a mix of correlations and character sets, because it’s gonna cost you in performance, because when you compare the strings of different conditions, they have to be casted. And it’s gonna probably give you incorrect results once in a while, it’s going to give you an expected order bias, you know, you could have the same content in two tables with different correlations. And if you order by two, you might get different orders through those collections. So you know, like, ideally, you will have a single character set and a single collection through all your database. But again, that’s up to you. And I was asking you before, Dave, do you know exactly what, where’s that thing? What is the thing is here? Returning Char 512, is that casting to char?

Dave Stokes:
Yeah, that’s, they have a generated column that’s pulling out the data and casting it to.

Marcos Albe:
So it’s a cast, basically discussing the chart 512. Okay. So what I was telling people is normally, you know, like, when I find myself in this situation, I will go ahead and do the cast outside. So I will just use the cast function, and put it here, and I will cast this whole thing. You know, like, if this was the column, I will cast this whole thing to Char 512. And that way, I would get rid of the incompatible very convenient, but incompatible syntax of returning char photos. Because this is not comfortable familiar?

Dave Stokes:
For the purposes of the port from last COVID. Maria, would it be worthwhile just to take the table, convert it from a generated column to a static column? And then after you get everything in place, then reconvert it? Repeat the question, please? Well, you have the MySQL version, which is a generated column, do an alter table, change that from your generated column to a static column, a normal column? And then after the port, and you have things working under Maria dB, change it back to a generated column and then start adding new data again?

Marcos Albe:
No, you know, I, what I was saying is the way to find out what is not compatible. Like you cannot go ahead like imagine you have, I don’t know 1000 tables, like we work with people that has applications, multiple applications running on the same instance. And they have perhaps, you know, 5000 8000 12,000 tables, so you cannot manually review that. Right? Like it’s not human. Like, it’s not human. So, the way to find out incompatibilities, or the easiest way, in my humble opinion, to find incompatibilities is to go ahead and run this exercise, right? Like, go ahead, do the dump of the schema, only bring it to Maria DB instance, and start facing the errors like I, you know, got this error. And now, I went find out that returning, returning is not part of Maria dBs, virtual columns. So I need to find a workaround. Okay. I learned that returning is what it’s doing is casting this. So I know cast, I can go ahead and rewrite this webcast. So what I was saying is, you can have an intermediate dummy, MySQL instance, or Percona Server instance, where you load the schema, and you do the altars in there, so that you’re finally going to have a Percona version or a MySQL version of the schema that is 100% compatible with Maria and then you read dump from there. So that’s one option. The other option is just delete the SQL file. That’s up to you, right? Like, it’s a matter of preference and how you like automating. I don’t mind doing set. So you know, I just set the heck out of it. If you prefer to, you know, automate through alters on the schema and then dump again, that’s absolutely fine as well. Again, it’s a matter of preferences at this point. The important bit is, in my opinion, how do you discover the incompatibilities, and I believe, easiest way, is by attempting the load taking note of the errors, and tackling them one by one. Okay, does that make sense to you, you have more?

Dave Stokes:
I’d say the only way you’re gonna find these sorts of problems is by smacking into them head on.

Marcos Albe:
Like, I don’t know any other, like, scientific way of doing this. Right.

Dave Stokes:
Now that you’re out there, and you have an ETL tool that does this sort of thing, please let us know what Percona we’d love to add it to the Percona Toolkit.

Marcos Albe:
Yeah, it has to be open source, it has to be free. Because that’s our little beautiful word. I actually like it, believe it or not. People coming from Oracle asked me how do you guys bear with this, right? Like, how do you guys don’t perish with this database and is like, it’s because it’s free. Like, we buy the time to cope with it by not paying licenses. So I’m happy. Now, sorry, I have my schema. So perfect. Now I can go here, all the changes, like I took a mental note of the changes I did, normally I would have documented the changes how we fixed them one by one. so other people can understand what changes happening during the migration, because later we might find weird stuff. But right now, I only change it the collation. So I changed this collation to one compatible with Maria dB. And I remove it that returning statement from the filter column. Those two changes should not do nothing. With my data collection is only about sorting and comparing. And the returning was only about a cast, I fix it in an I fix it it I use left instead of cast out well. But the point is, I have a fix for those. That doesn’t require me to change nothing in the data. Right.

And with this, no create the info I’m skipping all the create some just destructing the inserts. And again, we do this because we want to do it in two steps. First, we verify the schema, we make it ready and we verify that we can have a schema in Maria DB that will be compatible with the data. Now that we are over that step, I will have this other dump with only the data. It’s a big database and forgot that your beloved friend Marty Jacobi made it quite large, so he could break my performance. Also, I’m using mysqldump. But of course, there are other more modern solutions that increase complexity, but you could use my damper on my loader. You could use my SQL pump, which comes bundled with MySQL, and it supports parallel dump. And you could use the MySQL shell. Utilities. What’s the name of the function? Okay, but let me It show you one single shot

I removed the Percona repo. Okay, I’ll show you here in a minute. Basically MySQL shell has a dump utility that allows you to dump and reload and it also supports parallel and a bunch of other things. And you know if you have to do a large migration you probably want to look into one of those other solutions which allow for purlin dump or reload just to save yourself some time let me show you let me try to show you the MySQL shell one as soon as this guy finishes let me do this is what is it recall now release rules is it cedar? Is it it’s you know like that. Yeah, of course. Yes that it’s like sit the corner release is 800. Right and, you know, I just realized that in the rush, I did something very silly. I forgot to add two critical parameters to MySQL dump, which are, in this case, I needed this because if I want to replicate, I need either the GT ad sequence or the binlog file and position. In this case, because GTA ad between MySQL and Maria are not compatible, I will need to use the binlog file position coordinates for which you have to do dash dash master data. And also if you want to take a consistent backup with mysqldump, you have to use single transaction. Again, in the rush of the demo, I forgot those but that simply add those options, dash dash mustard, that’s data. And that’s that single data transaction. And if you do util dot, very good data schema, stamp tables, export table, import table load up. So by default is four. So this is going to produce parallel dump should be faster, etc. I just can’t imagine doing this between MySQL and Maria dB, is going to take you a bit of back and forth to clear out all the nuances. But it should work out this is one big, very big dump. Wrong with you, oh, all the generated columns. I never figured that will be something like that, you know, MySQL dump, does simple. Select star from the tables. And it knows nothing about fearful generated columns. And I think it’s dumping the heck out of them. That’s why normally a dump should be smaller than your and your data set, because in your data set, you have all the overhead of InnoDB, which has to be the B tree, and it has all the headers for the rows, and it has all the indexes and everything else, right. It’s a it’s a heavy binary format. And so normally, the dump will be smaller than the actual than the actual data set on disk. But in this case, we have a lot of biltwell generated columns. Those are not stored, they’re always generated on the fly. And they are now being generated on the fly and then save to the dump. Which makes me wonder, how will I you know, I have to tell you something. This is the first time I faced this situation. Like I had several customers asking me how to migrate tables with Pivotal generated columns into a non-compatible database. So I’m a bit like perplex it and I will have to think through this.

Dave Stokes:
Author would have to try this if this was a virtual-generated column, instead of a stored generated column. If it was stored, if it wouldn’t have to do that computation on the fly, because virtuals don’t actually do anything to access the data. So the stored the raw data is there the virtual it gets done on the fly.

Marcos Albe:
Yeah, you know, in this case, I think my approach to this will be three steps. So I will take a backup on the original with extra backup. I will bring that extra backup into an intermediate server. In the Intermediate server, I will drop all the Before columns will dump the schema, load the schema and the data into Maria dB, and then recreate the will for columns in Maria DB manually. Does that make sense to you? Like, did you see like, what’s the sequence? Because if we go ahead and attempt to dump to load this dump, the dump is going to have Look, let me open a new connection there.

Dave Stokes:
We had a question is MySQL shell compatible to Maria and Percona? Maria I’m not sure about but definitely with the Percona.

Marcos Albe:
Yep, yeah. So MariaDB has diverting more and more and more as versions go by. And you know, like, it already has incompatible redo log format. It has incompatible GTA D. You can you just saw a moment ago, it has incompatible syntax beats, and it’s just really diverting from my sequel, which, you know, if you ask me, it makes sense. Because they, it’s a diff, they set themselves to be a different database. They have different Galera libraries, they have different system tables, like they use ARIA for many system tables, you know, MySQL uses in the DB. And believe it or not, that actually makes a difference for many operations. So that kind of things, make Maria DB literally are not a drop in replacement for MySQL, and vice versa. So Percona server with the defaults, like if you don’t use any fancy configuration, it’s a drop in replacement for MySQL, and vice versa, like you can bring clean data set from MySQL interpret on a server and you can bring a clean data set from Percona server into an SQL doing that with Maria DB it’s unlikely to work beyond 5556 You know, Maria DB 10 already started having noticeably complicated this so nope, is not comfortable.

Dave Stokes:
Yeah, it’s easier to bring something from db to or big Oracle database over to a totally different type of database and it is to try to get these two cousins to interact the way exactly not sure if

Marcos Albe:
See, let me select star from columns and yeah, excellent, beautiful generator. So, again, you can use the generation expression and the extra to find out what are the beautiful columns and automate the removal of those in an intermediate instance because again. So there you go. And you can see it actually has all the generated columns right there right.

Dave Stokes:
Now, first do not like the JSON data type, you know, it should be either char var char integer decimal period. JSON has a lot of interesting uses. But this is one of the cases where you use it at your own risk.

Marcos Albe:
Robert Lucas actually did the right thing. Yes, only don’t you totally dump it to columns. All right. You know, compress the file data. How did that gruesome? So this tells you how effective JSON must be inside? You know that the internal representation of that JSON must be pretty different. Because the difference in size is wild. Yes. I truly, truly wonder what manipulations like, I’m gonna take a look at one of our support labs, to see what it’s actually doing. Hey, let’s give this one more minute. I know we are over time, I hope it’s only going to take a couple more minutes. But these are the type of things you find in turn migrations, right, like incompatibilities of all types. And that needs to be resolved many, many times, you can just automate tricks to cope with the differences. Some other times, it might require application changes. So whenever you’re migrating, you should actually do this, before deciding that the migration is something so whenever the idea that migration is something you want to do, someone should go ahead and spend the time to probe and, you know, to to verify how compatible the next database will be.

Dave Stokes:
By the way, if you’re a consultant and you’re saying, yeah, it should only take me five hours to do a, a port from one machine to another. This is why you have to test it, otherwise you’re going to work yourself out of out of your job.

Marcos Albe:
Yeah, no, no, I will never say it’s gonna take me. Like, I’m amazed I could do so much in one hour, I’m going to tell you that it’s a small database, right? It’s like a small, constrained schema. Like, again, if you have 8000 tables, like imagine the number of nuances you’re gonna face, you know, so really, it can take days like if it takes you days don’t feel bad, you’re actually doing very well.

Dave Stokes:
And by the way, if we’re going from like MySQL eight, oh, 28 to Amazon, Aurora. Yeah, basically, they’re both MySQL but there’s little differences that pop up either for the same version.

Marcos Albe:
Yeah, and you know, you’re losing money. Jokes aside, we have, well this goes on. I’m gonna put on my sales hat. And let me tell you, we have studies about the cost, the total cost of running out ora versus running EC two instances and hiding Percona managed services to do all the management for you so you can just put the boxes the easy two boxes there and give us the credentials and we’re going to do the job for you. So it for you, it will feel like our Yes, because we’re going to take the backups, monitoring, upgrades and everything off your hands, and we’re going to do it ourselves. And on top of that, if you want to do you know, schema deployments, we can do it for you as well. So you can really take away all of the database jobs from out of your hands. And row by row. In Frogfoot terms, we can deliver more throughput on the EC two instance than we can ever see now, not sure there are some read workloads that on a very beefy aldora instance might do better. But you know, we don’t see that being like a constant. And when performance problems happen, it’s very, very hard to diagnose, because we don’t have access to low level tools. And you know, when assaults happen, and I’ve seen them many times, many, many times, then you will not have a way to tell what’s causing the stall. Because a many times you cannot even access the database anymore. It’s just it seems to me, like an expensive option that doesn’t truly the lever for most workloads, you know, it probably does for the workload that Amazon Marketing Department ran on them. Absolutely, yes. But you know, like, the real thing is that I don’t see customers to saying, oh, yeah, I was running, you know, 10 my sequels, and now I have one or two outdoors, and everything runs fine, they have the same thing. Now notice, it’s just like, 3x more expensive. So really save yourself some money, and, you know, run easy to and get a good VBA it’s going to be far cheaper. I mean, at the scale, right, like, if you have, like, two terabytes database, instance, with the amount of memory and performance storage is going to be very expensive there.

Dave Stokes:
Yeah. And by the way, Percona is equal to good data. DBA. So if you need help, please contact us.

Marcos Albe:
Melissa services is great, like they’re doing a great job. And more and more. Like, we are now building Percona platform. And the Managed Services team is slowly adapting the Percona platform for their own needs. And it’s gonna be a very positive experience, I believe, you know, like, having a single place where you can access all your databases, whether they are MongoDB, Postgres, MySQL, and have them managed by the same team monitored with the same monitoring tool. So it is a real advantage. If you asked me like, I would like to have that if I was doing a website, like I did 20 years ago, if I, if I was doing web development, or if I was doing development of insert, I would really enjoy that kind of things.

Dave Stokes:
Percona monitoring and management is a tool that lets you monitor your databases on-prem in the cloud, wherever you have them. Now, another factor past that is Percona Platform, which is based on PMM. But it has a lot more a lot of interesting features, a lot of

Marcos Albe:
advice, source, C,

Dave Stokes:
Double check everything to make sure that you know, everything’s configured right, that you’re not missing a CVE that the performance is what you expect. And that’s fairly new. I think it came out in April or February.

Marcos Albe:
Yep. Around the conference around the conference,

Dave Stokes:
And it is an amazing tool.

Marcos Albe:
It is a still young tool. So you know, as anything that is emergent. You should thoroughly test it We are here also to help like so if you have questions about that you can approach us you can approach us on the forums if you’re still particular customer this is going to take lifetime Good lord.

Dave Stokes:
Brother if you try to haven’t tried PMM go to PMM demo.percona.com our forums you can find under the Community tab on the percona.com website ask questions there should we just kind of let that keep going in perpetuity and

Marcos Albe:
Yeah, I’m just thinking what alternative I could do I could do a smaller dump and just dump one or two tables and with that, do the import just to show how I will complete so again today. So I’m just gonna dump one table to showcase the rest of the procedure. So again, before I said I made a mistake I missed some parameters here again you want to do master data steward and you want to do single transaction this is gonna give us pinlock coordinates so we can set up replication from the new instance and this is gonna guarantee consistency through MVCC so we know that whatever we have in the dump is going to be fully consistent with the positions we have on the below positions we have small and I want to do just one database is is the Jason asked and I want to do normalize the vector that was first is and again this has no create because we already created all this and it’s going to have just the insert so our complaints to file. And I want to remind me because I go and if I do extracts it fine small All right. And I should be able to do MySQL dash you. I know what’s wrong with me. Okay, so I have this file, which contain it my password, so it was entering a password that is not valid because I reinstall it, Maria DB with an empty password right now. All right, I will use source data. Yes, sir. That’s more politically correct, I admit. So I would fail this. Less to them pay the small but let’s see progress you wrote. And the name of the thing is movie JSON test. And, of course, like, I’m going to do it again, just to give you reasonable advice, which is I’ll do it like this and said that the table we dump it was this one. Tricky. Right. So I’m gonna do it again.

Always send your outputs somewhere, right? Like, if you’re, like, I’m importing one table, who cares. But if you’re importing 1000 tables, you want to keep record of any errors that happen. The error is going to stop that perhaps warnings are not going to stop the process. And you are also want to see those warnings. And for those who are not very Linux friendly, this is like the important bit. So I just do cat basically, I’m just doing cat and skipping one line. So I get rid of that warning line that was at the very beginning. And I am passing that to my sequel, who is going to run those commands in the movie JSON test database. And then I’m going to send all the output of this command, I’m going to send it to my home directory to the Import log. And I’m going to send all the error output into the standard output. So that it will end up here because otherwise you will not see the arrows. So with that, I can that is the proper way to import stuff like always this silly, you know, small bit is going to help you immensely when you’re doing large projects. And if you have to contact a consultant or an external DBA he is going to ask you for the log you know he’s gonna tell you Okay, can you show me the log of what you’re doing? And you know, you have to have it for to give it to him. And this is the way to generate so whenever you’re loading stuff, always do this. And then more trickery. Let me show you more trickery.

Dave Stokes:
Okay. I’ll show you more important trickery, which might be interesting to see with Jason, what’s the name of the table for

Marcos Albe:
When you are importing data what you want to do is avoid a few checks that are expensive and avoid generating beam looks. So to do that, you can do the following route and you can do it oh man equals said SQL log in consider that is going to inhibit binary log creation, which is expensive and unnecessary because if you want attempt to take this data to another server, just take the dump and load the dump is going to be faster than replicating. And then you want to do, you don’t want to use unique checks, because those were already done on the source. And you can trust this data to be unique. And the same for foreign key checks for And this in particular, it’s gonna save you from a lot of headaches. Because when you’re dumping tables and loading tables, they are not dumped up and loaded in any particular order. And so foreign key dependencies are going to give you a nightmare. This will simply avoid that nightmare. And it’s also going to make the loading of the data much faster. So this simple init command is, you know, a way to configure the session where you’re loading the data, for better performance and to avoid unnecessary work. And that’s it. True and I don’t have much more to show well at this point. So at this point, you will set up replication you will go ahead and sorry. So we have our replication coordinates, right. And we can go ahead and use this to set up replication to the original instance let’s do it or not, I mean if you have time, I will go ahead and do it. While do table do table replicate the table that’s what it wants. Alright, and I’m gonna do my sequel version and I would do this stuff. I wonder what password we set for ripple but I can do this ALTER USER ripple. I can be fired by plugins I’m just gonna do this. I don’t think MariaDB supports caching SHA two password, I don’t remember, I’m just gonna do it easier. You can be sure this is not your best interest getting replicas is dangerous.

Dave Stokes:
Especially in environments where you’re adding tables all the time and we get to have the new ones.

Marcos Albe:
Then it’s just do you think you have a replica, but you actually don’t, and then it comes wow oh, oh Maria DB doesn’t support it. I bet let me. Сheck if I need to read Maria dB change replication filter that doesn’t exist with the gathered with change master or something like that? Let me see. Is there going to be clicking books. I can do it for a channel. You know, this is kind of things you will face also when migrating if you already have a set up a script that will set up your filters that is going to fill oh what you like to set all right it’s not a compiled character set. Well you know, you will have to have an intermediate server I guess how you want to fix this so for whoever was asking about compatibility between MariaDB and MySQL here’s your answer like no. This not Wow. character set is not a character set is not as specific. Can I compile in that character set like I never in my life face this one like I will have to research to fix this. So let me see if I can fix it very easily stop the slave He said, slaves, say equals even. That is not skipping it. Well I could just skip this statement, which is for a table that I am not intended to replicate this just for the user. But again, I will rather instead of skipping things, which is also not good for your health, I will figure out a proper way to do it. Like if you want, we can do it in our next chapter, troubleshooting weird replication issues. I don’t think that’s our next chapter. But

Dave Stokes:
We’ll schedule for a future one.

Marcos Albe:
We can do a future where like, really, I never face it. This one like we, in all honesty, like everybody in their fields, we have experience with what the customers, the problems with customers bring to us. Migrations to MariaDB, is not something we have faced many. So in all honesty, I never saw this one before. But it’s probably there is probably a workaround of some sort, I just don’t know to the top of my head. But you know, at this point, once replication is running, you should be able to verify you have the same data on both sides, using a tool called PT table checksum. So with big table checksum, you can verify that the data on both sides of replication is the same. And that will be the last step in our migration, right? Like making sure you have equal data to give you peace of mind that when you shut down the old database, everything’s in the new in the new database. And with that, I don’t have much more to share today.

Dave Stokes:
Well, thank you, Marcos, and thanks for the folks who watch this while we’re doing live and the folks who will watch it in the future. This is Percona. And we do these bi-weekly to talk about stuff. If you have questions, please ping me, I’m at Stoker on Twitter. Also read the questions that pop up on these things on YouTube. And we’ll be back in two weeks. And with that, like to thank Marcos again for showing us the way we should be doing stuff. Yeah, we go the way we expect. But I’ve learned a lot today.

Marcos Albe:
Yeah, you know, I’ve learned quite a bit as well. You know, like we do this truly live, like I don’t prepare nothing. And I think it’s funnier that way. Like it shows you what the real experience will look like, you know, if I prepare everything and everything was moved, that’s not going to be like that. Like databases are complex. They have 1000s of variables and settings. And just doing these kinds of migrations, it is something that is bound to face issues of some sort. So I hope you found it interesting as well. And I will see you in two weeks. Have a good one, everybody.

Dave Stokes:
Take care folks. Bye. ∎