WEBVTT 00:00.000 --> 00:19.460 So hello everyone. Thanks for sticking by. It's the last talk of the day I promise. I'm 00:19.460 --> 00:25.100 in the Andrew. I'm a database engineer at Bolt and today we're going to be talking a bit 00:25.100 --> 00:32.500 about replication in time to be and how to move data into and out of time to be with some 00:32.500 --> 00:37.580 sample cases that we've used at Bolt. You probably heard of us, we're a right-hand 00:37.580 --> 00:44.860 company. We also do scooters and e-bike rentals and food delivery and we've been growing 00:44.860 --> 00:50.860 massively over the last few years. It brings about some challenges in terms of database 00:50.860 --> 00:58.980 scalability. So I'll probably skip this. You probably got enough of TIDB today. The main 00:58.980 --> 01:05.020 thing about this is that it's my SQL compatible and where are my SQL shops. So that was 01:05.020 --> 01:10.940 one of the main reasons why we chose to migrate from my SQL to TIDB for the compatibility 01:10.940 --> 01:17.140 that means that our applications needed no or little read writing. Another thing that helps 01:17.140 --> 01:23.900 us was it's quite easier to run at scale than my SQL. For example, if you want to do 01:23.900 --> 01:29.820 DDL on five terabyte tables, TIDB is your friends, where's my SQL can do it with ghost, 01:29.820 --> 01:36.540 but it's painful. So these are the scenarios that we're going to be looking at. The first 01:36.540 --> 01:45.260 scenario which most people run into first is migrant and data from my SQL into TIDB. 01:45.260 --> 01:54.620 For this we use a tool called DM. Surprisingly, it stands for data migrator. It's a separate 01:54.620 --> 02:04.300 cluster from your TIDB instances. It has mostly two parts, DM master and DM worker. The 02:04.300 --> 02:11.500 DM master is basically the brains of the cluster. It receives commands from the user, task 02:11.500 --> 02:21.180 management, and monitors the workers. Basically, the DM workers are basically the BZBs of 02:21.180 --> 02:28.740 the system where they connect to my SQL, dump data, and also read the binwalk to provide 02:28.740 --> 02:34.380 ongoing replication. The reason that you see multiple nodes, for example, multiple DM master 02:34.380 --> 02:39.780 nodes is for high availability. So if the DM master goes down you have to other nodes 02:39.820 --> 02:47.940 to fall back on and your replication doesn't break. Some instructions on how to set up 02:47.940 --> 02:54.340 the DM cluster. I wanted to much into it. It's basically you define the nodes that you want 02:54.340 --> 02:59.420 and you deploy it and start it up. Once you have your cluster running, you need to define 02:59.420 --> 03:05.460 some sources. Basically a source is just a connection to my SQL instance and you can have 03:05.460 --> 03:12.460 multiple sources on your cluster. A SQL cluster can read from multiple my scales and write 03:12.460 --> 03:18.460 into multiple TIDB clusters. You don't need an individual cluster per migration. 03:18.460 --> 03:25.460 Here are some examples of how to do this. Once you define your source, you don't actually 03:25.460 --> 03:32.780 have any data replication moving for that. You need to start a task. Basically, all a 03:32.780 --> 03:40.620 task is telling it, I'm going to read from one or more my SQLs and write into a TIDB 03:40.620 --> 03:48.340 cluster. It's also a powerful filtering system which you can do funky stuff with, for example, 03:48.340 --> 03:54.220 merging multiple shards into a single TIDB cluster. You can ignore SQL commands, for example, 03:54.220 --> 03:58.820 you can ignore the weeds so that if you want to archive all your data, then you can do 03:58.860 --> 04:06.180 it with this. You can do filtering, so just replicating part of your data or renaming 04:06.180 --> 04:12.540 of tables, so it's quite powerful filtering system. The task mode, yeah, it's basically 04:12.540 --> 04:18.620 you can do a dump on reload with no replication. Have that end replication or have just 04:18.620 --> 04:25.620 replication from a specific point in time. It's a very flexible tool. This is an example 04:25.620 --> 04:32.140 of how to monitor your task from the command line and you have the usual stuff. If you 04:32.140 --> 04:37.620 familiar with my SQL, you know what seconds behind master is, it's a replication way. You 04:37.620 --> 04:43.300 can also do this from the built in graphana monitoring. We didn't need to set up any of 04:43.300 --> 04:51.980 this. It comes built in with TIDB, so it's quite a good monitoring stack there. So some 04:51.980 --> 04:58.820 tips after you start your migration or your replication, you want to do usually one of the 04:58.820 --> 05:04.660 data consistent check. There's this tool called sync diff inspector, which basically compares 05:04.660 --> 05:10.780 the table definitions and also the data at a specific point in time. This also works with 05:10.780 --> 05:17.660 TIDC which we will see next. Another thing that sometimes happens is my SQL will purge 05:17.700 --> 05:22.740 some binwalls that you need in order to get replication running. So there's an option called 05:22.740 --> 05:30.900 re-way log in DM to copy the SQL binwalls locally so that you don't run into this problem. 05:30.900 --> 05:36.780 Another very useful feature is this ignoring ghost tables. Anybody that's done largely 05:36.780 --> 05:43.620 the else on my SQL using ghosts. I wish for a new thank you. You'll know that it 05:43.620 --> 05:50.300 creates a temporary table, it duplicates everything. So DM is actually smart enough to ignore 05:50.300 --> 05:56.820 that and apply the changes downstream without meeting any of the intermediate stuff. And 05:56.820 --> 06:03.500 the filtering I've talked about, one use case that we've done is we had a very large table 06:03.500 --> 06:10.340 in my SQL with int primary key and we're running out of addressing space. So this was a 06:10.340 --> 06:16.460 good candidate to migrate to TIDC. What we decided to do is actually change the primary 06:16.460 --> 06:23.420 key at migration time. So you can create the empty table schema with bigings, primary key 06:23.420 --> 06:28.060 and then use the filter to ignore the create table, start replication and it will write 06:28.060 --> 06:34.780 into this new table with the bigings primary key. So different scenario, now how to that 06:34.780 --> 06:41.780 was to get data into TIDV. This is how to get data out of TIDV. Now it uses a tool called 06:41.780 --> 06:49.580 TICDC and this tool is actually part of an existing TICDC cluster. It reads data from 06:49.580 --> 06:56.940 TICIV and it will send it to a downstream sync. It can be another TIDV cluster, it can 06:56.940 --> 07:04.740 be my SQL, it can be Kafka. So it's quite a flexible tool for change that capture. 07:04.740 --> 07:10.860 So yeah, you can add your TICDC nodes either to an existing TIDV cluster or you can add 07:10.860 --> 07:18.060 them when you're deploying a new TIDV cluster and some of the limitations or requirements 07:18.060 --> 07:24.100 is that your tables need a primary key or a unique, not null key in order to, you 07:24.100 --> 07:29.580 need to identify the rows that you're replicating. In terms of network latency, it's recommended 07:29.820 --> 07:36.540 under 100 milliseconds between your TICDC nodes and your downstream, mySQL, TIDV, wherever 07:36.540 --> 07:46.100 you're writing it to. So this is one scenario that we use at Bolt. When we do TIDV version 07:46.100 --> 07:51.540 upgrades, major version upgrades, the upgrade itself is online and rolling, there's no 07:51.540 --> 07:56.660 downtime. But if something goes wrong and we want to switch back to the previous version, 07:56.660 --> 08:02.740 there's no way to do an online rolling downgrade. So our strategy is the same old strategy 08:02.740 --> 08:08.940 of having a replica or a replica that tried to be a cluster in this case with the old version. 08:08.940 --> 08:14.300 And in that case, if something goes wrong, you can just switch a traffic over to the original 08:14.300 --> 08:19.980 version. And setting this up is quite simple, you just load your data, you do a USB 08:19.980 --> 08:26.900 R to do a restore. And then you start your change fees from a specific point in time, 08:26.900 --> 08:33.060 you might have heard the term TSL, a timestamp oracle. And replication start from that 08:33.060 --> 08:40.940 point in time. One gutcher is you might want to increase your garbage collection lifetime 08:40.940 --> 08:48.860 on your source TIDV. The default is 10 minutes and usually that's not enough. So after 08:48.940 --> 08:54.300 you load your data, if it takes more than 10 minutes, then replication doesn't have enough 08:54.300 --> 09:01.260 back data to fetch from. So we usually put 24 hours and then set it back to the original 09:01.260 --> 09:05.900 value. If you're from the MySQL world, this would be more or less how many binwalls 09:05.900 --> 09:14.900 how far back the binwall goes. So TIDV C has the same filtering capabilities that DM has 09:14.980 --> 09:21.620 and you can do all the same tricks there. To verify, you can check it from the command 09:21.620 --> 09:30.620 line and also from your final, has expected. And the next scenario is similar to the previous 09:30.620 --> 09:38.260 one, but using a different AWS region or another cloud region. The reason we use this is 09:38.260 --> 09:44.260 more for disaster recovery. In the unlikely event that the whole cloud region goes down, 09:44.260 --> 09:50.180 we want to have a running database system that we can switch to. It's basically the same 09:50.180 --> 09:54.740 thing as a previous scenario, but there's a few gotchas. Since we're running on different 09:54.740 --> 10:04.740 regions, latency is higher. So there's a possibility since 65 to put your CDC nodes in the 10:04.740 --> 10:11.620 downstream cloud so that the closer to the downstream TIDV cluster and you don't have problems 10:11.700 --> 10:20.980 with latency. We ran into this into infinitive lag because of this problem. And also another 10:20.980 --> 10:29.060 tip is rather than having one single change feed from source to target, try to have smaller 10:29.060 --> 10:36.100 change feeds. When we switch to first-key mid-change feeds, our latency problem will reduce 10:36.100 --> 10:44.420 almost nothing. So that's quite helpful. I won't be talking about TIDV to Kafka because 10:44.420 --> 10:50.020 that's used by a different team in the company, but you can also do that. And the last scenario 10:50.020 --> 10:55.940 is replicating from TIDV to my SQL. You might be wondering why would you need this? We're 10:55.940 --> 11:03.620 ready. I'm TIDV. Why do you want to get back to my SQL? The reason, again, is for insurance policy. 11:03.860 --> 11:09.060 So I'll show you a scenario that we've done. One of the migrations we are doing multiple 11:09.060 --> 11:15.060 shards into TIDV. So you can see we had multiple DM workers reading from separate shards 11:15.060 --> 11:21.700 and consolidating them into TIDV. But then you see we used TICDC to replicate back to my SQL 11:21.700 --> 11:28.580 cluster. The reason we did this is in case the my SQL to TIDV migration went south, 11:29.220 --> 11:36.660 we needed to fall back into a mySQL based system. So this is why we would use TICDC against 11:36.660 --> 11:44.580 the mySQL targets. And yeah, I wish I had a little bit more time to go in depth, but yeah, you can 11:44.580 --> 11:50.980 see some of the pink capsules that are available for replication. And I don't think we have any 11:50.980 --> 11:57.140 time for questions, but I'll be here tonight and tomorrow in the mySQL room. So if you have questions, 11:57.220 --> 12:00.340 feel free to grab me. Thank you very much. 12:08.740 --> 12:11.780 Yeah, I think all the end users are here. So if you have any questions, 12:11.780 --> 12:17.780 we'll stick around until they kick us out. And thanks for coming to our very first cloud native 12:17.780 --> 12:19.220 databases, everyone. Thank you.