https://www.civo.com/docs/assets/js/f55161fb.e7ab4edc.js

1"use strict";(self.webpackChunkcivo_documentation=self.webpackChunkcivo_documentation||[]).push([[8869],{6429:(e,a,n)=>{n.r(a),n.d(a,{assets:()=>d,contentTitle:()=>r,default:()=>h,frontMatter:()=>o,metadata:()=>t,toc:()=>l});const t=JSON.parse('{"id":"database/mysql/migration","title":"MySQL Database migration to Civo","description":"Migrate your MySQL database to Civo with this step-by-step guide. Ensure data accuracy and minimize downtime with our backup and restore method.","source":"@site/content/docs/database/mysql/migration.md","sourceDirName":"database/mysql","slug":"/database/mysql/migration","permalink":"/docs/database/mysql/migration","draft":false,"unlisted":false,"tags":[],"version":"current","lastUpdatedAt":null,"frontMatter":{"title":"MySQL Database migration to Civo","description":"Migrate your MySQL database to Civo with this step-by-step guide. Ensure data accuracy and minimize downtime with our backup and restore method."},"sidebar":"mainSidebar","previous":{"title":"Creating a MySQL Database Dump","permalink":"/docs/database/mysql/dump-mysql"},"next":{"title":"PostgreSQL","permalink":"/docs/database/postgresql"}}');var i=n(4848),s=n(8453);const o={title:"MySQL Database migration to Civo",description:"Migrate your MySQL database to Civo with this step-by-step guide. Ensure data accuracy and minimize downtime with our backup and restore method."},r=void 0,d={},l=[{value:"Pre-Migration Considerations",id:"pre-migration-considerations",level:2},{value:"Prerequisites",id:"prerequisites",level:2},{value:"Details Needed for Migration",id:"details-needed-for-migration",level:3},{value:"If External Connections Are Prohibited",id:"if-external-connections-are-prohibited",level:3},{value:"MySQL Database Migration Steps",id:"mysql-database-migration-steps",level:2},{value:"1. Backup the Data",id:"1-backup-the-data",level:3},{value:"2. Transfer the Backup (Optional)",id:"2-transfer-the-backup-optional",level:3},{value:"3. Restore the Dump",id:"3-restore-the-dump",level:3},{value:"Key Points to Remember",id:"key-points-to-remember",level:2},{value:"Post-Migration Validation",id:"post-migration-validation",level:2},{value:"Data Integrity and Consistency",id:"data-integrity-and-consistency",level:3},{value:"Update Application Configurations",id:"update-application-configurations",level:3}];function c(e){const a={code:"code",h2:"h2",h3:"h3",li:"li",p:"p",pre:"pre",ul:"ul",...(0,s.R)(),...e.components},{Head:n}=a;return n||function(e,a){throw new Error("Expected "+(a?"component":"object")+" `"+e+"` to be defined: you likely forgot to import, pass, or provide it.")}("Head",!0),(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(n,{children:(0,i.jsx)("title",{children:"MySQL Database Migration to Civo | Civo Documentation"})}),"\n",(0,i.jsx)(a.p,{children:"Migrating a MySQL database to Civo is a process that needs meticulous planning to ensure data integrity and minimal downtime. This guide outlines the backup and restore method for migrating your MySQL database to a Civo MySQL database."}),"\n",(0,i.jsx)(a.h2,{id:"pre-migration-considerations",children:"Pre-Migration Considerations"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"Halt or limit database operations, especially write actions, to prevent data discrepancies during the migration."}),"\n",(0,i.jsx)(a.li,{children:"Schedule the migration at a time that will least affect business operations, taking into account the migration duration and data volume."}),"\n"]}),"\n",(0,i.jsx)(a.h2,{id:"prerequisites",children:"Prerequisites"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"A Civo MySQL database instance should already be in place with connection details on hand. To create a new database you can follow our getting started guide."}),"\n",(0,i.jsxs)(a.li,{children:["Confirm the availability of the ",(0,i.jsx)(a.code,{children:"mysqldump"})," utility on your system for database copying."]}),"\n"]}),"\n",(0,i.jsx)(a.h3,{id:"details-needed-for-migration",children:"Details Needed for Migration"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"Source MySQL host IP address or DNS FQDN."}),"\n",(0,i.jsx)(a.li,{children:"Source MySQL username and password."}),"\n",(0,i.jsx)(a.li,{children:"Target Civo database IP address or DNS FQDN."}),"\n",(0,i.jsx)(a.li,{children:"Target Civo database username and password."}),"\n",(0,i.jsx)(a.li,{children:"Names of the databases to migrate, if not moving all."}),"\n"]}),"\n",(0,i.jsx)(a.h3,{id:"if-external-connections-are-prohibited",children:"If External Connections Are Prohibited"}),"\n",(0,i.jsx)(a.p,{children:"An additional step to transfer the backup to a host that can connect to Civo is required if your MySQL server restricts external 

1connections."}),"\n",(0,i.jsx)(a.h2,{id:"mysql-database-migration-steps",children:"MySQL Database Migration Steps"}),"\n",(0,i.jsx)(a.h3,{id:"1-backup-the-data",children:"1. Backup the Data"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsxs)(a.li,{children:["Use ",(0,i.jsx)(a.code,{children:"mysqldump"})," to create a backup of the existing database:"]}),"\n"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"mysqldump -u [username] -p[password] [database] > backup.sql\n"})}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"For external connections, include host and port details:"}),"\n"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"mysqldump -h [host] -P [port] -u [username] -p[password] [database] > backup.sql\n"})}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsxs)(a.li,{children:["To back up all databases, use ",(0,i.jsx)(a.code,{children:"--all-databases"}),":"]}),"\n"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"mysqldump -u [username] -p[password] --all-databases > backup.sql\n"})}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsxs)(a.li,{children:["For specific databases, use ",(0,i.jsx)(a.code,{children:"--databases"})," with the database names:"]}),"\n"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"mysqldump -u [username] -p[password] --databases [database1] [database2] > backup.sql\n"})}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsxs)(a.li,{children:["Options for schema-only or data-only backups:","\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsxs)(a.li,{children:["Schema only (no data): ",(0,i.jsx)(a.code,{children:"--no-data"})]}),"\n",(0,i.jsxs)(a.li,{children:["Data only (no schema information): ",(0,i.jsx)(a.code,{children:"--no-create-info"})]}),"\n"]}),"\n"]}),"\n"]}),"\n",(0,i.jsx)(a.h3,{id:"2-transfer-the-backup-optional",children:"2. Transfer the Backup (Optional)"}),"\n",(0,i.jsxs)(a.p,{children:["Securely move the backup file using ",(0,i.jsx)(a.code,{children:"scp"}),":"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"scp [email protected]:/path/to/backup.sql /tmp/\n"})}),"\n",(0,i.jsx)(a.h3,{id:"3-restore-the-dump",children:"3. Restore the Dump"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsxs)(a.li,{children:["On a system that can connect to Civo, use the ",(0,i.jsx)(a.code,{children:"mysql"})," command to restore the backup:"]}),"\n"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"mysql -h [civo_host] -u [civo_username] -p[password] [civo_database] < backup.sql\n"})}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"Example for restoring to a Civo instance:"}),"\n"]}),"\n",(0,i.jsx)(a.pre,{children:(0,i.jsx)(a.code,{className:"language-bash",children:"mysql -h 74.220.31.116 -u root -p < backup.sql\n"})}),"\n",(0,i.jsx)(a.h2,{id:"key-points-to-remember",children:"Key Points to Remember"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"The -u flag is used to specify the MySQL username, and you should replace the bracketed placeholders with actual values."}),"\n",(0,i.jsx)(a.li,{children:"The correct port for MySQL is typically 3306, unless the MySQL instance is configured to use a different one. Civo MySQL databases use 3306."}),"\n",(0,i.jsx)(a.li,{children:"Replace [civo_host], [civo_username] and [civo_database] with the corresponding details for the Civo MySQL database."}),"\n"]}),"\n",(0,i.jsx)(a.h2,{id:"post-migration-validation",children:"Post-Migration Validation"}),"\n",(0,i.jsx)(a.h3,{id:"data-integrity-and-consistency",children:"Data Integrity and Consistency"}),"\n",(0,i.jsx)(a.p,{children:"After the database restoration process, it's essential to verify that all the data has been migrated correctly. This can involve:"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"Checking the counts and consistency of records in tables."}),"\n",(0,i.jsx)(a.li,{children:"Running integrity checks or using built-in database validation tools."}),"\n",(0,i.jsx)(a.li,{children:"Comparing data samples between the old and new databases."}),"\n"]}),"\n",(0,i.jsx)(a.h3,{id:"update-application-configurations",children:"Update Application Configurations"}),"\n",(0,i.jsx)(a.p,{children:"Applications that were connected to the old database instance will need to be updated to connect to the new database on Civo. This typically involves:"}),"\n",(0,i.jsxs)(a.ul,{children:["\n",(0,i.jsx)(a.li,{children:"Updating the database connection strings in application configuration files or environment variables."}),"\n",(0,i.jsx)(a.li,{children:"Ensuring that any user accounts and permissions required by the applications are correctly set up on the new Civo database."}),"\n",(0,i.jsx)(a.li,{children:"Testing the application to ensure it can connect to and interact with the new database without issues."}),"\n"]}),"\n",(0,i.jsx)(a.p,{children:"By following these detailed steps, your MySQL database migration to Civo should be executed smoothly with data integrity preserved."})]})}function h(e={}){const{wrapper:a}={...(0,s.R)(),...e.components};return a?(0,i.jsx)(a,{...e,children:(0,i.jsx)(c,{...e})}):c(e)}},8453:(e,a,n)=>{n.d(a,{R:()=>o,x:()=>r});var t=n(6540);const i={},s=t.createContext(i);function o(e){const a=t.useContext(s);return t.useMemo((function(){return"function"==typeof e?e(a):{...a,...e}}),[a,e])}function r(e){let a;return a=e.disableParentContext?"function"==typeof e.components?e.components(i):e.components||i:o(e.components),t.createElement(s.Provider,{value:a},e.children)}}}]);