[{"_1":2,"_950":-5,"_951":-5},"loaderData",{"_3":4,"_22":23},"root",{"_5":6},"current",{"_7":8,"_9":10,"_21":8},"development",false,"env",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20},"COMMIT_SHA","43bc3ac3cb2010d0fd142f00492153d21ea99844","IMAGE_CDN","https://planetscale-images.imgix.net","IMAGE_CDN_ENABLED","true","INTERNAL_API","https://api.planetscale.com","SENTRY_DSN","https://bd81903b44804e22a06bdc0c1a91b303@o499952.ingest.us.sentry.io/4504531942572032","userSignedIn","routes/blog.author.$slug",{"_24":25,"_30":31,"_940":941,"_3":949},"author",{"_26":27,"_28":29},"slug","phani","name","Phani Raju","blogs",[32,569],{"_26":33,"_34":35,"_531":532,"_533":-7,"_534":535,"_542":550,"_551":552,"_555":556,"_558":559,"_560":561,"_567":568},"behind-the-scenes-how-we-built-password-roles","body",[36,78,96,109,137,142,151,163,231,242,247,267,278,283,288,323,328,339,344,390,420,436,456,461,491,502,520],["SingleFetchClassInstance",37],{"_38":39,"_28":40,"_41":42,"_43":44},"$$mdtype","Tag","p","attributes",{},"children",[45,46,54,55,61,62,61,67,61,72,77],"We recently released a new feature that allows you to use ",["SingleFetchClassInstance",47],{"_38":39,"_28":48,"_41":49,"_43":52},"a",{"_50":51},"href","/docs/vitess/security/password-roles",[53],"granular roles for your database passwords",". When you generate a new password, you have the option to select from the following roles: ",["SingleFetchClassInstance",56],{"_38":39,"_28":57,"_41":58,"_43":59},"code",{},[60],"read-only",", ",["SingleFetchClassInstance",63],{"_38":39,"_28":57,"_41":64,"_43":65},{},[66],"write-only",["SingleFetchClassInstance",68],{"_38":39,"_28":57,"_41":69,"_43":70},{},[71],"read/write",["SingleFetchClassInstance",73],{"_38":39,"_28":57,"_41":74,"_43":75},{},[76],"admin",".",["SingleFetchClassInstance",79],{"_38":39,"_28":40,"_41":80,"_43":81},{},[82,83,89,90,95],"We implemented password roles using ",["SingleFetchClassInstance",84],{"_38":39,"_28":85,"_41":86,"_43":87},"strong",{},[88],"Vitess Access Control Lists"," and ",["SingleFetchClassInstance",91],{"_38":39,"_28":85,"_41":92,"_43":93},{},[94],"VTTablet",", but we ran into a couple of roadblocks on the way. In this post, we look at some of the issues we faced while implementing this, and how we were able to get around them.",["SingleFetchClassInstance",97],{"_38":39,"_28":98,"_41":99,"_43":102},"h2",{"_100":101},"id","how-vitess-handles-user-authorization",[103],["SingleFetchClassInstance",104],{"_38":39,"_28":48,"_41":105,"_43":107},{"_50":106},"#how-vitess-handles-user-authorization",[108],"How Vitess handles user authorization",["SingleFetchClassInstance",110],{"_38":39,"_28":40,"_41":111,"_43":112},{},[113,119,120,126,127,77],["SingleFetchClassInstance",114],{"_38":39,"_28":48,"_41":115,"_43":117},{"_50":116},"https://vitess.io",[118],"Vitess"," supports ",["SingleFetchClassInstance",121],{"_38":39,"_28":48,"_41":122,"_43":124},{"_50":123},"https://vitess.io/docs/user-guides/configuration-advanced/authorization/",[125],"authorization"," via a static configuration file supplied to its ",["SingleFetchClassInstance",128],{"_38":39,"_28":48,"_41":129,"_43":131},{"_50":130},"https://vitess.io/docs/reference/programs/vttablet/",[132],["SingleFetchClassInstance",133],{"_38":39,"_28":57,"_41":134,"_43":135},{},[136],"vttablet",["SingleFetchClassInstance",138],{"_38":39,"_28":40,"_41":139,"_43":140},{},[141],"You can see an example of what this file looks like below:",["SingleFetchClassInstance",143],{"_38":39,"_28":144,"_41":145,"_43":150},"CodeBlock",{"_146":147,"_148":149},"html","

{\n  \"table_groups\": [\n    {\n      \"name\": \"planetscale user groups\",\n      \"table_names_or_prefixes\": [\"%\"],\n      \"readers\": [\"planetscale-reader\", \"planetscale-writer\", \"planetscale-admin\"],\n      \"writers\": [\"planetscale-writer\", \"planetscale-writer-only\", \"planetscale-admin\"],\n      \"admins\": [\"planetscale-admin\"]\n    }\n  ]\n}\n

","language","json",[],["SingleFetchClassInstance",152],{"_38":39,"_28":153,"_41":154,"_43":156},"h3",{"_100":155},"dissecting-the-acl-configuration-file",[157],["SingleFetchClassInstance",158],{"_38":39,"_28":48,"_41":159,"_43":161},{"_50":160},"#dissecting-the-acl-configuration-file",[162],"Dissecting the ACL configuration file",["SingleFetchClassInstance",164],{"_38":39,"_28":165,"_41":166,"_43":167},"ul",{},[168,189,203,217],["SingleFetchClassInstance",169],{"_38":39,"_28":170,"_41":171,"_43":172},"li",{},[173],["SingleFetchClassInstance",174],{"_38":39,"_28":40,"_41":175,"_43":176},{},[177,182,183,188],["SingleFetchClassInstance",178],{"_38":39,"_28":57,"_41":179,"_43":180},{},[181],"table_names_or_prefixes"," — This is the list of tables that this policy applies to. ",["SingleFetchClassInstance",184],{"_38":39,"_28":57,"_41":185,"_43":186},{},[187],"%"," means all tables.",["SingleFetchClassInstance",190],{"_38":39,"_28":170,"_41":191,"_43":192},{},[193],["SingleFetchClassInstance",194],{"_38":39,"_28":40,"_41":195,"_43":196},{},[197,202],["SingleFetchClassInstance",198],{"_38":39,"_28":57,"_41":199,"_43":200},{},[201],"readers"," — This set of users can read data and schema from tables and views in the database.",["SingleFetchClassInstance",204],{"_38":39,"_28":170,"_41":205,"_43":206},{},[207],["SingleFetchClassInstance",208],{"_38":39,"_28":40,"_41":209,"_43":210},{},[211,216],["SingleFetchClassInstance",212],{"_38":39,"_28":57,"_41":213,"_43":214},{},[215],"writers"," — This set of users can write data to tables in the database.",["SingleFetchClassInstance",218],{"_38":39,"_28":170,"_41":219,"_43":220},{},[221],["SingleFetchClassInstance",222],{"_38":39,"_28":40,"_41":223,"_43":224},{},[225,230],["SingleFetchClassInstance",226],{"_38":39,"_28":57,"_41":227,"_43":228},{},[229],"admins"," — This set of users can read, update data, and alter schema in the database.",["SingleFetchClassInstance",232],{"_38":39,"_28":40,"_41":233,"_43":234},{},[235,236,241],"Although this configuration is static, Vitess allows you to customize this list over the runtime of a vttablet by reloading this file from disk at a specific interval using the ",["SingleFetchClassInstance",237],{"_38":39,"_28":57,"_41":238,"_43":239},{},[240],"--table-acl-config-reload-interval"," argument.",["SingleFetchClassInstance",243],{"_38":39,"_28":40,"_41":244,"_43":245},{},[246],"This static file approach is great for customers who:",["SingleFetchClassInstance",248],{"_38":39,"_28":249,"_41":250,"_43":251},"ol",{},[252,257,262],["SingleFetchClassInstance",253],{"_38":39,"_28":170,"_41":254,"_43":255},{},[256],"Have a pre-defined set of users that can access the database. Managing the various access groups in the above file is easier if the entire set of users is well-known.",["SingleFetchClassInstance",258],{"_38":39,"_28":170,"_41":259,"_43":260},{},[261],"Have a small set of ACL configuration files that are custom to each of their Vitess clusters.",["SingleFetchClassInstance",263],{"_38":39,"_28":170,"_41":264,"_43":265},{},[266],"Can plan for a maintenance schedule when updating this file on the pod/shared volume for their Vitess deployments.",["SingleFetchClassInstance",268],{"_38":39,"_28":98,"_41":269,"_43":271},{"_100":270},"why-doesnt-this-work-for-planetscale",[272],["SingleFetchClassInstance",273],{"_38":39,"_28":48,"_41":274,"_43":276},{"_50":275},"#why-doesnt-this-work-for-planetscale",[277],"Why doesn’t this work for PlanetScale?",["SingleFetchClassInstance",279],{"_38":39,"_28":40,"_41":280,"_43":281},{},[282],"None of the qualifiers above apply to PlanetScale customers. As a design principle, we try to avoid keeping authentication and authorization state on the actual vttablet pods themselves.",["SingleFetchClassInstance",284],{"_38":39,"_28":40,"_41":285,"_43":286},{},[287],"We can run into all kinds of issues depending on a refresh interval for a file on the vttablet pod. Let’s look at some of them.",["SingleFetchClassInstance",289],{"_38":39,"_28":249,"_41":290,"_43":291},{},[292,297,313,318],["SingleFetchClassInstance",293],{"_38":39,"_28":170,"_41":294,"_43":295},{},[296],"Our user story of “Customers can create passwords with roles and they work immediately” prevents the dependence on a timed refresh loop on the vttablets. Since we don’t know when the file will be refreshed next, we can’t guarantee that your credentials will work immediately.",["SingleFetchClassInstance",298],{"_38":39,"_28":170,"_41":299,"_43":300},{},[301,302,306,307,312],"The operator, responsible for managing the vttablet, will need to write to a file on the pod and might not be able to update all vttablet pods at once, leading to a race condition where a given credential might be ",["SingleFetchClassInstance",303],{"_38":39,"_28":57,"_41":304,"_43":305},{},[76]," on one pod and ",["SingleFetchClassInstance",308],{"_38":39,"_28":57,"_41":309,"_43":310},{},[311],"reader"," on another.",["SingleFetchClassInstance",314],{"_38":39,"_28":170,"_41":315,"_43":316},{},[317],"If a pod goes down and needs to be restarted, we don’t have an external ACL store to figure out what the ACL state for each database should be before bringing it up again.",["SingleFetchClassInstance",319],{"_38":39,"_28":170,"_41":320,"_43":321},{},[322],"We’d need to maintain a separate set of state for each customer database that cannot be common to all PlanetScale databases.",["SingleFetchClassInstance",324],{"_38":39,"_28":40,"_41":325,"_43":326},{},[327],"With these issues in mind, we were able to come up with a solution that gave our customers a seamless experience they have come to expect from PlanetScale.",["SingleFetchClassInstance",329],{"_38":39,"_28":98,"_41":330,"_43":332},{"_100":331},"how-we-use-the-static-acl-file-to-implement-password-roles",[333],["SingleFetchClassInstance",334],{"_38":39,"_28":48,"_41":335,"_43":337},{"_50":336},"#how-we-use-the-static-acl-file-to-implement-password-roles",[338],"How we use the static ACL file to implement password roles",["SingleFetchClassInstance",340],{"_38":39,"_28":40,"_41":341,"_43":342},{},[343],"For every password created by a user, we store the following bits of information in the credential database:",["SingleFetchClassInstance",345],{"_38":39,"_28":346,"_41":347,"_43":350},"div",{"_348":349},"class","table-overflow",[351],["SingleFetchClassInstance",352],{"_38":39,"_28":353,"_41":354,"_43":356},"table",{"_355":8},"collapse",[357],["SingleFetchClassInstance",358],{"_38":39,"_28":359,"_41":360,"_43":361},"thead",{},[362],["SingleFetchClassInstance",363],{"_38":39,"_28":364,"_41":365,"_43":366},"tr",{},[367,375,380,385],["SingleFetchClassInstance",368],{"_38":39,"_28":369,"_41":370,"_43":373},"th",{"_371":372},"align","left",[374],"Display name",["SingleFetchClassInstance",376],{"_38":39,"_28":369,"_41":377,"_43":378},{"_371":372},[379],"Role",["SingleFetchClassInstance",381],{"_38":39,"_28":369,"_41":382,"_43":383},{"_371":372},[384],"password_sha1_hash",["SingleFetchClassInstance",386],{"_38":39,"_28":369,"_41":387,"_43":388},{"_371":372},[389],"password_sha2_hash",["SingleFetchClassInstance",391],{"_38":39,"_28":40,"_41":392,"_43":393},{},[394,395,399,400,404,405,61,409,414,415,419],"The ",["SingleFetchClassInstance",396],{"_38":39,"_28":57,"_41":397,"_43":398},{},[379]," property determines which of the three ",["SingleFetchClassInstance",401],{"_38":39,"_28":57,"_41":402,"_43":403},{},[136]," ACL roles (",["SingleFetchClassInstance",406],{"_38":39,"_28":57,"_41":407,"_43":408},{},[201],["SingleFetchClassInstance",410],{"_38":39,"_28":57,"_41":411,"_43":412},{},[413],"writer",", or ",["SingleFetchClassInstance",416],{"_38":39,"_28":57,"_41":417,"_43":418},{},[229],") you’ll get mapped to.",["SingleFetchClassInstance",421],{"_38":39,"_28":40,"_41":422,"_43":423},{},[424,425,429,430,435],"If you create a ",["SingleFetchClassInstance",426],{"_38":39,"_28":57,"_41":427,"_43":428},{},[66]," password and connect to your PlanetScale database, the query hits the ",["SingleFetchClassInstance",431],{"_38":39,"_28":57,"_41":432,"_43":433},{},[434],"user query frontend",", which is a service responsible for all user-facing functionality for PlanetScale databases.",["SingleFetchClassInstance",437],{"_38":39,"_28":40,"_41":438,"_43":439},{},[440],["SingleFetchClassInstance",441],{"_38":39,"_28":442,"_41":443,"_43":444},"picture",{},[445],["SingleFetchClassInstance",446],{"_38":39,"_28":447,"_41":448,"_43":455},"img",{"_449":450,"_451":452,"_453":454},"alt","ACL conversion","loading","lazy","src","/assets/blog/content/behind-the-scenes-how-we-built-password-roles/fe2c9d024316a9cd5c7772531de685b1620ab5a2-978x345.png",[],["SingleFetchClassInstance",457],{"_38":39,"_28":40,"_41":458,"_43":459},{},[460],"As shown in the diagram above, this approach lets us solve all of the issues we discussed in the previous section.",["SingleFetchClassInstance",462],{"_38":39,"_28":249,"_41":463,"_43":464},{},[465,470,481,486],["SingleFetchClassInstance",466],{"_38":39,"_28":170,"_41":467,"_43":468},{},[469],"Having a dynamic user credential store allows us to create/delete user mappings to roles instantly, without the need for a refresh interval.",["SingleFetchClassInstance",471],{"_38":39,"_28":170,"_41":472,"_43":473},{},[474,475,480],"We have a predefined set of user names which describe the access grant for each of the roles we support, e.g. ",["SingleFetchClassInstance",476],{"_38":39,"_28":57,"_41":477,"_43":478},{},[479],"planetscale-reader"," can only read data and schema. By mapping all PlanetScale users’ roles to the username from the Vitess ACL configuration, we can do an “on the fly” rewrite of the security principal so that connections to the database get the right access levels.",["SingleFetchClassInstance",482],{"_38":39,"_28":170,"_41":483,"_43":484},{},[485],"Since all authentication and authorization data is stored on an external data store, all pods that we create for a database will have the same ACL state.",["SingleFetchClassInstance",487],{"_38":39,"_28":170,"_41":488,"_43":489},{},[490],"Since the base ACL configuration is the same across all PlanetScale databases, debugging and fixing any issues with ACL enforcement is simplified.",["SingleFetchClassInstance",492],{"_38":39,"_28":98,"_41":493,"_43":495},{"_100":494},"wrap-up",[496],["SingleFetchClassInstance",497],{"_38":39,"_28":48,"_41":498,"_43":500},{"_50":499},"#wrap-up",[501],"Wrap up",["SingleFetchClassInstance",503],{"_38":39,"_28":40,"_41":504,"_43":505},{},[506,507,512,513,519],"If you’d like to learn more about password roles, please check out our ",["SingleFetchClassInstance",508],{"_38":39,"_28":48,"_41":509,"_43":510},{"_50":51},[511],"Password roles documentation",". You can sign up for a ",["SingleFetchClassInstance",514],{"_38":39,"_28":48,"_41":515,"_43":517},{"_50":516},"https://auth.planetscale.com/sign-up",[518],"PlanetScale account"," and try it out today.",["SingleFetchClassInstance",521],{"_38":39,"_28":40,"_41":522,"_43":523},{},[524,525,77],"If you have any questions, make sure to find us on ",["SingleFetchClassInstance",526],{"_38":39,"_28":48,"_41":527,"_43":529},{"_50":528},"https://twitter.com/planetscale",[530],"Twitter","body_text","We recently released a new feature that allows you to use granular roles for your database passwords. When you generate a new password, you have the option to select from the following roles: read-only, write-only, read/write, admin.\nWe implemented password roles using Vitess Access Control Lists and VTTablet, but we ran into a couple of roadblocks on the way. In this post, we look at some of the issues we faced while implementing this, and how we were able to get around them.\nHow Vitess handles user authorization\nVitess supports authorization via a static configuration file supplied to its vttablet.\nYou can see an example of what this file looks like below:{\n \"table_groups\": [\n {\n \"name\": \"planetscale user groups\",\n \"table_names_or_prefixes\": [\"%\"],\n \"readers\": [\"planetscale-reader\", \"planetscale-writer\", \"planetscale-admin\"],\n \"writers\": [\"planetscale-writer\", \"planetscale-writer-only\", \"planetscale-admin\"],\n \"admins\": [\"planetscale-admin\"]\n }\n ]\n}\n\nDissecting the ACL configuration file\ntable_names_or_prefixes — This is the list of tables that this policy applies to. % means all tables.\nreaders — This set of users can read data and schema from tables and views in the database.\nwriters — This set of users can write data to tables in the database.\nadmins — This set of users can read, update data, and alter schema in the database.\nAlthough this configuration is static, Vitess allows you to customize this list over the runtime of a vttablet by reloading this file from disk at a specific interval using the --table-acl-config-reload-interval argument.\nThis static file approach is great for customers who:\nHave a pre-defined set of users that can access the database. Managing the various access groups in the above file is easier if the entire set of users is well-known.\nHave a small set of ACL configuration files that are custom to each of their Vitess clusters.\nCan plan for a maintenance schedule when updating this file on the pod/shared volume for their Vitess deployments.\nWhy doesn’t this work for PlanetScale?\nNone of the qualifiers above apply to PlanetScale customers. As a design principle, we try to avoid keeping authentication and authorization state on the actual vttablet pods themselves.\nWe can run into all kinds of issues depending on a refresh interval for a file on the vttablet pod. Let’s look at some of them.\nOur user story of “Customers can create passwords with roles and they work immediately” prevents the dependence on a timed refresh loop on the vttablets. Since we don’t know when the file will be refreshed next, we can’t guarantee that your credentials will work immediately.\nThe operator, responsible for managing the vttablet, will need to write to a file on the pod and might not be able to update all vttablet pods at once, leading to a race condition where a given credential might be admin on one pod and reader on another.\nIf a pod goes down and needs to be restarted, we don’t have an external ACL store to figure out what the ACL state for each database should be before bringing it up again.\nWe’d need to maintain a separate set of state for each customer database that cannot be common to all PlanetScale databases.\nWith these issues in mind, we were able to come up with a solution that gave our customers a seamless experience they have come to expect from PlanetScale.\nHow we use the static ACL file to implement password roles\nFor every password created by a user, we store the following bits of information in the credential database:\nDisplay name\nRole\npassword_sha1_hash\npassword_sha2_hash\nThe Role property determines which of the three vttablet ACL roles (readers, writer, or admins) you’ll get mapped to.\nIf you create a write-only password and connect to your PlanetScale database, the query hits the user query frontend, which is a service responsible for all user-facing functionality for PlanetScale databases.\n\nAs shown in the diagram above, this approach lets us solve all of the issues we discussed in the previous section.\nHaving a dynamic user credential store allows us to create/delete user mappings to roles instantly, without the need for a refresh interval.\nWe have a predefined set of user names which describe the access grant for each of the roles we support, e.g. planetscale-reader can only read data and schema. By mapping all PlanetScale users’ roles to the username from the Vitess ACL configuration, we can do an “on the fly” rewrite of the security principal so that connections to the database get the right access levels.\nSince all authentication and authorization data is stored on an external data store, all pods that we create for a database will have the same ACL state.\nSince the base ACL configuration is the same across all PlanetScale databases, debugging and fixing any issues with ACL enforcement is simplified.\nWrap up\nIf you’d like to learn more about password roles, please check out our Password roles documentation. You can sign up for a PlanetScale account and try it out today.\nIf you have any questions, make sure to find us on Twitter.","aside","toc",[536,544,546,548],{"_43":537,"_100":101,"_540":543,"_542":108},[538],{"_43":539,"_100":155,"_540":541,"_542":162},[],"level",3,"title",2,{"_43":545,"_100":270,"_540":543,"_542":277},[],{"_43":547,"_100":331,"_540":543,"_542":338},[],{"_43":549,"_100":494,"_540":543,"_542":501},[],"Behind the scenes: How we built Password Roles","authors",[553],{"_26":27,"_28":29,"_554":-7},"x","categories",[557],"engineering","excerpt","Learn how we leveraged Vitess ACLs and VTTablet to build our password roles functionality","meta",{"_562":563,"_564":559,"_565":566,"_542":550},"canonical","https://planetscale.com/blog/behind-the-scenes-how-we-built-password-roles","description","image","/assets/blog/content/behind-the-scenes-how-we-built-password-roles/338717a2386ba1328e1f752819f6d71eb7a4fbe5-2400x1348.jpg","createdAt","2022-07-27T14:00:00.000Z",{"_26":570,"_34":571,"_531":914,"_533":-7,"_534":915,"_542":930,"_551":931,"_555":933,"_558":935,"_567":936,"_560":937},"import-your-mysql-data-to-planetscale",[572,598,603,608,645,657,668,673,687,692,705,717,728,733,746,751,756,767,779,790,795,806,811,822,833,846,851,862,873,886,891,902],["SingleFetchClassInstance",573],{"_38":39,"_28":40,"_41":574,"_43":575},{},[576],["SingleFetchClassInstance",577],{"_38":39,"_28":578,"_41":579,"_43":582},"Callout",{"_580":581},"type","note",[583,584,590,591,597]," For full guidance on how to import your database to PlanetScale with no downtime, check out our ",["SingleFetchClassInstance",585],{"_38":39,"_28":48,"_41":586,"_43":588},{"_50":587},"/docs/vitess/imports/database-imports",[589],"Database Imports tutorial"," and our ",["SingleFetchClassInstance",592],{"_38":39,"_28":48,"_41":593,"_43":595},{"_50":594},"/docs/vitess/imports/aws-rds-migration-guide",[596],"Migration guides",". ",["SingleFetchClassInstance",599],{"_38":39,"_28":40,"_41":600,"_43":601},{},[602],"We’re happy to announce that PlanetScale now supports zero downtime data migrations from your existing MySQL database. We’re leveraging the power of Vitess to let you connect your database to PlanetScale, try us out as a replica and then switch over your database to PlanetScale completely. That’s right, no dumping your data, no restoring from backup. Just give us a connection and let us handle the rest.",["SingleFetchClassInstance",604],{"_38":39,"_28":40,"_41":605,"_43":606},{},[607],"When you connect your existing MySQL database to PlanetScale, you instantly get to experience the power of PlanetScale’s platform, which includes:",["SingleFetchClassInstance",609],{"_38":39,"_28":249,"_41":610,"_43":611},{},[612,623,634],["SingleFetchClassInstance",613],{"_38":39,"_28":170,"_41":614,"_43":615},{},[616,622],["SingleFetchClassInstance",617],{"_38":39,"_28":48,"_41":618,"_43":620},{"_50":619},"/docs/vitess/connecting/connection-strings",[621],"Secure Passwords"," that scale to thousands of simultaneous connections",["SingleFetchClassInstance",624],{"_38":39,"_28":170,"_41":625,"_43":626},{},[627,633],["SingleFetchClassInstance",628],{"_38":39,"_28":48,"_41":629,"_43":631},{"_50":630},"/docs/vitess/monitoring/query-insights",[632],"Query Insights",", start finding & optimizing slow queries",["SingleFetchClassInstance",635],{"_38":39,"_28":170,"_41":636,"_43":637},{},[638,644],["SingleFetchClassInstance",639],{"_38":39,"_28":48,"_41":640,"_43":642},{"_50":641},"/docs/vitess/web-console",[643],"Web Console"," to query your database directly from the PlanetScale web UI",["SingleFetchClassInstance",646],{"_38":39,"_28":40,"_41":647,"_43":648},{},[649,650,656],"PlanetScale’s data imports are powered by Vitess’s ",["SingleFetchClassInstance",651],{"_38":39,"_28":48,"_41":652,"_43":654},{"_50":653},"https://vitess.io/docs/user-guides/configuration-advanced/unmanaged-tablet/",[655],"Unmanaged Tablet"," technology. In this blog post, we will go over how to perform an import of your existing MySQL database into PlanetScale.",["SingleFetchClassInstance",658],{"_38":39,"_28":98,"_41":659,"_43":661},{"_100":660},"connect-planetscale-to-your-existing-mysql-database-server",[662],["SingleFetchClassInstance",663],{"_38":39,"_28":48,"_41":664,"_43":666},{"_50":665},"#connect-planetscale-to-your-existing-mysql-database-server",[667],"Connect PlanetScale to your existing MySQL database server",["SingleFetchClassInstance",669],{"_38":39,"_28":40,"_41":670,"_43":671},{},[672],"Let PlanetScale know how we can reach your MySQL server and which database you’d like to import. You’ll need credentials for a user who has read and write permissions to your database, and you will need to have binary logs enabled.",["SingleFetchClassInstance",674],{"_38":39,"_28":40,"_41":675,"_43":676},{},[677],["SingleFetchClassInstance",678],{"_38":39,"_28":442,"_41":679,"_43":680},{},[681],["SingleFetchClassInstance",682],{"_38":39,"_28":447,"_41":683,"_43":686},{"_449":684,"_451":452,"_453":685},"","/assets/blog/content/import-your-mysql-data-to-planetscale/e24d56a719bbedf69700e46b1fca62779582fdc7-786x988.png",[],["SingleFetchClassInstance",688],{"_38":39,"_28":40,"_41":689,"_43":690},{},[691],"We will notify you of any schema incompatibility or connection errors that might interfere with using PlanetScale:",["SingleFetchClassInstance",693],{"_38":39,"_28":40,"_41":694,"_43":695},{},[696],["SingleFetchClassInstance",697],{"_38":39,"_28":442,"_41":698,"_43":699},{},[700],["SingleFetchClassInstance",701],{"_38":39,"_28":447,"_41":702,"_43":704},{"_449":684,"_451":452,"_453":703},"/assets/blog/content/import-your-mysql-data-to-planetscale/ba5a338db1f5acbb7c46bdc2af028ff395c284ef-643x247.png",[],["SingleFetchClassInstance",706],{"_38":39,"_28":40,"_41":707,"_43":708},{},[709,710,716],"Head on over to our ",["SingleFetchClassInstance",711],{"_38":39,"_28":48,"_41":712,"_43":714},{"_50":713},"/docs/vitess/imports/database-imports#troubleshooting",[715],"documentation"," to see how to resolve these compatibility issues, and then try importing your database again.",["SingleFetchClassInstance",718],{"_38":39,"_28":98,"_41":719,"_43":721},{"_100":720},"initial-data-copy-from-your-database-to-planetscale",[722],["SingleFetchClassInstance",723],{"_38":39,"_28":48,"_41":724,"_43":726},{"_50":725},"#initial-data-copy-from-your-database-to-planetscale",[727],"Initial data copy from your database to PlanetScale",["SingleFetchClassInstance",729],{"_38":39,"_28":40,"_41":730,"_43":731},{},[732],"If there are no issues, you can get started with the import. The first step is an initial online data copy from your database.",["SingleFetchClassInstance",734],{"_38":39,"_28":40,"_41":735,"_43":736},{},[737],["SingleFetchClassInstance",738],{"_38":39,"_28":442,"_41":739,"_43":740},{},[741],["SingleFetchClassInstance",742],{"_38":39,"_28":447,"_41":743,"_43":745},{"_449":684,"_451":452,"_453":744},"/assets/blog/content/import-your-mysql-data-to-planetscale/150c54ac32f0ce8826c7a42fb71241df9af8ecb5-1999x667.png",[],["SingleFetchClassInstance",747],{"_38":39,"_28":40,"_41":748,"_43":749},{},[750],"In this phase, PlanetScale will connect to your database and begin copying over the schema and data from your target database automatically, writing it in chunks back to PlanetScale.",["SingleFetchClassInstance",752],{"_38":39,"_28":40,"_41":753,"_43":754},{},[755],"When this is done, you’ll have an up-to-date copy of your data securely stored and ready to be queried.",["SingleFetchClassInstance",757],{"_38":39,"_28":98,"_41":758,"_43":760},{"_100":759},"replicating-changes-from-your-database-to-planetscale",[761],["SingleFetchClassInstance",762],{"_38":39,"_28":48,"_41":763,"_43":765},{"_50":764},"#replicating-changes-from-your-database-to-planetscale",[766],"Replicating changes from your database to PlanetScale",["SingleFetchClassInstance",768],{"_38":39,"_28":40,"_41":769,"_43":770},{},[771,772,778],"After the initial data copy is complete, we will keep PlanetScale’s database in sync with your database by utilizing ",["SingleFetchClassInstance",773],{"_38":39,"_28":48,"_41":774,"_43":776},{"_50":775},"https://dev.mysql.com/doc/refman/5.7/en/binlog-replication-configuration-overview.html",[777],"Binary Log File replication",". Any changes made to your database, including inserts, deletes, and updates are replayed on PlanetScale.",["SingleFetchClassInstance",780],{"_38":39,"_28":98,"_41":781,"_43":783},{"_100":782},"connect-your-application-to-planetscales-copy-of-your-database",[784],["SingleFetchClassInstance",785],{"_38":39,"_28":48,"_41":786,"_43":788},{"_50":787},"#connect-your-application-to-planetscales-copy-of-your-database",[789],"Connect your application to PlanetScale’s copy of your database",["SingleFetchClassInstance",791],{"_38":39,"_28":40,"_41":792,"_43":793},{},[794],"Now that you’ve made it this far, you can deploy your application pointed to PlanetScale as the database.",["SingleFetchClassInstance",796],{"_38":39,"_28":40,"_41":797,"_43":798},{},[799,800,77],"This works because your new PlanetScale database is also acting as a “data router”. Queries will be served by PlanetScale with writes transparently routed to your existing database, and then replicated back to PlanetScale using Vitess’ powerful ",["SingleFetchClassInstance",801],{"_38":39,"_28":48,"_41":802,"_43":804},{"_50":803},"https://vitess.io/docs/reference/features/schema-routing-rules/",[805],"Routing Rules",["SingleFetchClassInstance",807],{"_38":39,"_28":40,"_41":808,"_43":809},{},[810],"This step of routing your application’s reads and writes through PlanetScale allows you to safely validate that your application is fully compatible with PlanetScale without taking downtime and without fragile application-level migration mechanisms like dual writes.",["SingleFetchClassInstance",812],{"_38":39,"_28":98,"_41":813,"_43":815},{"_100":814},"switching-over-to-planetscale-as-the-primary-database",[816],["SingleFetchClassInstance",817],{"_38":39,"_28":48,"_41":818,"_43":820},{"_50":819},"#switching-over-to-planetscale-as-the-primary-database",[821],"Switching over to PlanetScale as the Primary database",["SingleFetchClassInstance",823],{"_38":39,"_28":40,"_41":824,"_43":825},{},[826,827,832],"After you have ensured that your application is successful using PlanetScale, you can promote it as the primary or cancel the data import. You do this by clicking on the “",["SingleFetchClassInstance",828],{"_38":39,"_28":85,"_41":829,"_43":830},{},[831],"Enable primary mode","” button in the data import banner.",["SingleFetchClassInstance",834],{"_38":39,"_28":40,"_41":835,"_43":836},{},[837],["SingleFetchClassInstance",838],{"_38":39,"_28":442,"_41":839,"_43":840},{},[841],["SingleFetchClassInstance",842],{"_38":39,"_28":447,"_41":843,"_43":845},{"_449":684,"_451":452,"_453":844},"/assets/blog/content/import-your-mysql-data-to-planetscale/82f73f9a94820203397f7e91c8a364e51063c85b-2724x748.png",[],["SingleFetchClassInstance",847],{"_38":39,"_28":40,"_41":848,"_43":849},{},[850],"This will reverse the direction of the routing — reads and writes will now go directly to PlanetScale, and we’ll replicate any writes and updates back to your original database, to make sure it stays in sync. This allows you to cut traffic over without worrying about a difficult or unsafe migration process if anything goes wrong.",["SingleFetchClassInstance",852],{"_38":39,"_28":98,"_41":853,"_43":855},{"_100":854},"detaching-planetscale-from-your-database",[856],["SingleFetchClassInstance",857],{"_38":39,"_28":48,"_41":858,"_43":860},{"_50":859},"#detaching-planetscale-from-your-database",[861],"Detaching PlanetScale from your database",["SingleFetchClassInstance",863],{"_38":39,"_28":40,"_41":864,"_43":865},{},[866,867,872],"Once you’re fully migrated and ready to use PlanetScale forever, we’re going to detach your external database from PlanetScale. Click on the “",["SingleFetchClassInstance",868],{"_38":39,"_28":85,"_41":869,"_43":870},{},[871],"Detach external database","” button and follow the prompts to disconnect PlanetScale from your database. We will delete all connection details from PlanetScale and no longer read/write from/to your external database.",["SingleFetchClassInstance",874],{"_38":39,"_28":40,"_41":875,"_43":876},{},[877],["SingleFetchClassInstance",878],{"_38":39,"_28":442,"_41":879,"_43":880},{},[881],["SingleFetchClassInstance",882],{"_38":39,"_28":447,"_41":883,"_43":885},{"_449":684,"_451":452,"_453":884},"/assets/blog/content/import-your-mysql-data-to-planetscale/72e6649a98869268ebab9d2d07e6d6360b9e7165-1999x549.png",[],["SingleFetchClassInstance",887],{"_38":39,"_28":40,"_41":888,"_43":889},{},[890],"Now, you’re fully onboarded to PlanetScale and your database is at home here!",["SingleFetchClassInstance",892],{"_38":39,"_28":98,"_41":893,"_43":895},{"_100":894},"try-it-out",[896],["SingleFetchClassInstance",897],{"_38":39,"_28":48,"_41":898,"_43":900},{"_50":899},"#try-it-out",[901],"Try it out",["SingleFetchClassInstance",903],{"_38":39,"_28":40,"_41":904,"_43":905},{},[906,907,913],"Try importing your existing MySQL Database on PlanetScale and let us know how we can make this better. We’d love to ",["SingleFetchClassInstance",908],{"_38":39,"_28":48,"_41":909,"_43":911},{"_50":910},"https://github.com/planetscale/beta/discussions",[912],"hear from you"," on any ideas you have to improve onboarding. We are always looking for ways to polish the Planet.","For full guidance on how to import your database to PlanetScale with no downtime, check out our Database Imports tutorial and our Migration guides. \nWe’re happy to announce that PlanetScale now supports zero downtime data migrations from your existing MySQL database. We’re leveraging the power of Vitess to let you connect your database to PlanetScale, try us out as a replica and then switch over your database to PlanetScale completely. That’s right, no dumping your data, no restoring from backup. Just give us a connection and let us handle the rest.\nWhen you connect your existing MySQL database to PlanetScale, you instantly get to experience the power of PlanetScale’s platform, which includes:\nSecure Passwords that scale to thousands of simultaneous connections\nQuery Insights, start finding & optimizing slow queries\nWeb Console to query your database directly from the PlanetScale web UI\nPlanetScale’s data imports are powered by Vitess’s Unmanaged Tablet technology. In this blog post, we will go over how to perform an import of your existing MySQL database into PlanetScale.\nConnect PlanetScale to your existing MySQL database server\nLet PlanetScale know how we can reach your MySQL server and which database you’d like to import. You’ll need credentials for a user who has read and write permissions to your database, and you will need to have binary logs enabled.\n\nWe will notify you of any schema incompatibility or connection errors that might interfere with using PlanetScale:\n\nHead on over to our documentation to see how to resolve these compatibility issues, and then try importing your database again.\nInitial data copy from your database to PlanetScale\nIf there are no issues, you can get started with the import. The first step is an initial online data copy from your database.\n\nIn this phase, PlanetScale will connect to your database and begin copying over the schema and data from your target database automatically, writing it in chunks back to PlanetScale.\nWhen this is done, you’ll have an up-to-date copy of your data securely stored and ready to be queried.\nReplicating changes from your database to PlanetScale\nAfter the initial data copy is complete, we will keep PlanetScale’s database in sync with your database by utilizing Binary Log File replication. Any changes made to your database, including inserts, deletes, and updates are replayed on PlanetScale.\nConnect your application to PlanetScale’s copy of your database\nNow that you’ve made it this far, you can deploy your application pointed to PlanetScale as the database.\nThis works because your new PlanetScale database is also acting as a “data router”. Queries will be served by PlanetScale with writes transparently routed to your existing database, and then replicated back to PlanetScale using Vitess’ powerful Routing Rules.\nThis step of routing your application’s reads and writes through PlanetScale allows you to safely validate that your application is fully compatible with PlanetScale without taking downtime and without fragile application-level migration mechanisms like dual writes.\nSwitching over to PlanetScale as the Primary database\nAfter you have ensured that your application is successful using PlanetScale, you can promote it as the primary or cancel the data import. You do this by clicking on the “Enable primary mode” button in the data import banner.\n\nThis will reverse the direction of the routing — reads and writes will now go directly to PlanetScale, and we’ll replicate any writes and updates back to your original database, to make sure it stays in sync. This allows you to cut traffic over without worrying about a difficult or unsafe migration process if anything goes wrong.\nDetaching PlanetScale from your database\nOnce you’re fully migrated and ready to use PlanetScale forever, we’re going to detach your external database from PlanetScale. Click on the “Detach external database” button and follow the prompts to disconnect PlanetScale from your database. We will delete all connection details from PlanetScale and no longer read/write from/to your external database.\n\nNow, you’re fully onboarded to PlanetScale and your database is at home here!\nTry it out\nTry importing your existing MySQL Database on PlanetScale and let us know how we can make this better. We’d love to hear from you on any ideas you have to improve onboarding. We are always looking for ways to polish the Planet.",[916,918,920,922,924,926,928],{"_43":917,"_100":660,"_540":543,"_542":667},[],{"_43":919,"_100":720,"_540":543,"_542":727},[],{"_43":921,"_100":759,"_540":543,"_542":766},[],{"_43":923,"_100":782,"_540":543,"_542":789},[],{"_43":925,"_100":814,"_540":543,"_542":821},[],{"_43":927,"_100":854,"_540":543,"_542":861},[],{"_43":929,"_100":894,"_540":543,"_542":901},[],"Bring your data to PlanetScale",[932],{"_26":27,"_28":29,"_554":-7},[934],"product","PlanetScale now supports zero downtime data migrations from your existing MySQL Database","2021-11-17T15:30:00.000Z",{"_562":938,"_564":935,"_565":939,"_542":930},"https://planetscale.com/blog/import-your-mysql-data-to-planetscale","/assets/social-DtnN8vYG.png","pagination",{"_942":943,"_944":8,"_945":8,"_946":943,"_947":948},"page",1,"hasPrev","hasNext","totalPages","url","https://planetscale.com/blog/author/phani","https://planetscale.com","actionData","errors"]

Posts by Phani Raju

Behind the scenes: How we built Password Roles

Bring your data to PlanetScale