Member Avatar for riahc3

Hey

I want to make a backup and restore of a MySQL SERVER to another machine. Please note the SERVER part; I want to backup/restore datebases, tables, procedures, users, configuration, etc....EVERYTHING

What is the best way to do this? Thank you

  • 5 Contributors
  • 6 Replies
  • 258 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by Manish02

Dani AI

Generated

Three practical ways to move an entire MySQL server (data, routines, privileges, config and everything) are: a logical export/import, a physical (file-level or hot) copy, or a replication-based clone-and-promote. Choose by size and downtime requirements: logical exports are simplest and most portable; physical or snapshot-based methods are faster for large datasets; replication lets you cut over with minimal downtime.

Building on and : do not assume the server’s internal system tables can be blindly copied. The structure and authentication fields in the system schema change across MySQL releases and distributions. Rather than copying system tables verbatim, extract account definitions as explicit account-and-privilege statements (so host parts, authentication plugin and password format are preserved), or use a backup tool that understands users. If you must copy mysql files, ensure the source and target are the same MySQL version and test thoroughly in isolation.

For large or live servers, prefer physical hot-backup tools or snapshots (for example, image + consistent InnoDB snapshot tools) or set up replication and let the target catch up before promoting. After a physical copy, remove or regenerate server-unique identifiers (server-id/UUID) before enabling replication to avoid identity conflicts. Don’t forget non-database artifacts: my.cnf, TLS keys/certs, crontabs, timezone tables, plugin libraries and any custom startup scripts.

Quick checklist before cutover: confirm version/architecture parity, capture binlog position or GTID for a consistent point-in-time, export account/privilege statements, copy config and certs, test restore in a sandbox, verify application connectivity and privileges, and validate scheduled events/triggers/procedures. GUI tools like SQLyog or Workbench can help for interactive tasks (as and noted), but they don’t remove the need to plan consistency, version compatibility, and post-restore validation.

Recommended Answers

All 6 Replies

Member Avatar for pritaeas

You can use mysqldump to export everything to an sql script file. On the other server you can use mysql to import it again. Both are command line tools. Note that users are never exported, you will have to recreate these manually.

Edited by pritaeas
Member Avatar for smantscheff

You can export the system database (users, rights etc.) with mysqldump mysql and import it again, but this may lead to all sort of trouble.

Member Avatar for riahc3

Note that users are never exported, you will have to recreate these manually.

No way to do this? Third party tool?

Member Avatar for smantscheff

What you can do:
Identify all tables in the database mysql which have a field called User. Export those tables with mysqldump. Feed them into the new database. Make sure that you run mysql with admin privileges (usually as user root). Restart the mysql server with the new database after the import. And hope for the best.

Member Avatar for sathish_nadu

I use for this where we can connect to the remote server and take a back up with this tool.

Member Avatar for Manish02

i think SQLYOg is best option for him

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.