Which MySQL InnoDb files to move when transfering to a new server?2019 Community Moderator ElectionMove MySQL database to a new serverWhich MySQL data type to use for storing boolean valuesHost 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL serverCreate new user in MySQL and give it full access to one databaseMySQL table charset issues when moving serversHow to import an SQL file using the command line in MySQL?Migrate old mySQL/RH to new mySQL/UbuntuTransfering mysql data from wamp enviroment to centOSNew MySQL Server slower than the oldRestore the mySQL database from files - Windows serverWhat is the best way of moving a 150GB MySQL Server to a new Server in the same datacenter?
Would those living in a "perfect society" not understand satire
Volume of hyperbola revolved about the y -axis
Insult for someone who "doesn't know anything"
How do I increase the number of TTY consoles?
Converting from "matrix" data into "coordinate" data
How do we create new idioms and use them in a novel?
Rationale to prefer local variables over instance variables?
Trocar background-image com delay via jQuery
Is there a way to make cleveref distinguish two environments with the same counter?
ESPP--any reason not to go all in?
Use Mercury as quenching liquid for swords?
Is this Paypal Github SDK reference really a dangerous site?
Difference between `nmap local-IP-address` and `nmap localhost`
Is it possible to clone a polymorphic object without manually adding overridden clone method into each derived class in C++?
How to install round brake pads
Too soon for a plot twist?
What do you call someone who likes to pick fights?
Writing text next to a table
Did Amazon pay $0 in taxes last year?
How to educate team mate to take screenshots for bugs with out unwanted stuff
Are these two graphs isomorphic? Why/Why not?
Is there stress on two letters on the word стоят
Is it appropriate to ask a former professor to order a book for me through an inter-library loan?
Finding the minimum value of a function without using Calculus
Which MySQL InnoDb files to move when transfering to a new server?
2019 Community Moderator ElectionMove MySQL database to a new serverWhich MySQL data type to use for storing boolean valuesHost 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL serverCreate new user in MySQL and give it full access to one databaseMySQL table charset issues when moving serversHow to import an SQL file using the command line in MySQL?Migrate old mySQL/RH to new mySQL/UbuntuTransfering mysql data from wamp enviroment to centOSNew MySQL Server slower than the oldRestore the mySQL database from files - Windows serverWhat is the best way of moving a 150GB MySQL Server to a new Server in the same datacenter?
I'm moving a 150GB MySQL Server to a brand new machine on the same network, both of them run Windows Server 2012.
The new server runs MySQL 8.0.15 while the old one runs on MySQL 5.7.14
All of my tables are in the 'roat' folder.
This is my rough plan
- Shut down both MySQL Servers
- Start a Filezilla transfer, moving all the MySQL files over
- Start the new MySQL Server.
Now my question is, which of these files below will I need to transfer to the new server in order for the new server to work and startup?
edit: I am not interested in using mysqldump since it's at least 10x slower with 150GB of data.
mysql
asked Mar 6 at 11:21
Phillip JPhillip J
245
|
show 4 more comments
I'm moving a 150GB MySQL Server to a brand new machine on the same network, both of them run Windows Server 2012.
The new server runs MySQL 8.0.15 while the old one runs on MySQL 5.7.14
All of my tables are in the 'roat' folder.
This is my rough plan
- Shut down both MySQL Servers
- Start a Filezilla transfer, moving all the MySQL files over
- Start the new MySQL Server.
Now my question is, which of these files below will I need to transfer to the new server in order for the new server to work and startup?
edit: I am not interested in using mysqldump since it's at least 10x slower with 150GB of data.
mysql
asked Mar 6 at 11:21
Phillip JPhillip J
245
1
Possible duplicate of Move MySQL database to a new server
– Nico Haase
Mar 6 at 11:24
I am not using 'mysqldump' to transfer the database, since it'd be super slow (up to 20 hours total, while moving the raw 150GB of files over would be max 1 hour)
– Phillip J
Mar 6 at 11:26
@PhillipJ that's an inherent problem of MySQL and dumps. If you have the Enterprise edition, either bought or compiled from source, you can take real backups and restore them on the new server. Check Moving or Copying InnoDB Tables for other options. Copying/moving files is possible but requires care - you can't just copy data files while transactions are still active. You'd end up with partial or corrupted data
– Panagiotis Kanavos
Mar 6 at 11:34
@PanagiotisKanavos I'm okay with an hour or 2 downtime (shutting down the mysql server), would that not be possible?
– Phillip J
Mar 6 at 11:37
One option is to use the transferable tablespaces feature, although that requires that the tables were created with theinnodb_file_per_tableoption. In that case you need to flush the tables and copy the files. Another option is described as a "cold backup" but in reality it means that you shut down the server and copy the files over
– Panagiotis Kanavos
Mar 6 at 11:37
|
show 4 more comments
I'm moving a 150GB MySQL Server to a brand new machine on the same network, both of them run Windows Server 2012.
The new server runs MySQL 8.0.15 while the old one runs on MySQL 5.7.14
All of my tables are in the 'roat' folder.
This is my rough plan
- Shut down both MySQL Servers
- Start a Filezilla transfer, moving all the MySQL files over
- Start the new MySQL Server.
Now my question is, which of these files below will I need to transfer to the new server in order for the new server to work and startup?
edit: I am not interested in using mysqldump since it's at least 10x slower with 150GB of data.
mysql
asked Mar 6 at 11:21
Phillip JPhillip J
245
I'm moving a 150GB MySQL Server to a brand new machine on the same network, both of them run Windows Server 2012.
The new server runs MySQL 8.0.15 while the old one runs on MySQL 5.7.14
All of my tables are in the 'roat' folder.
This is my rough plan
- Shut down both MySQL Servers
- Start a Filezilla transfer, moving all the MySQL files over
- Start the new MySQL Server.
Now my question is, which of these files below will I need to transfer to the new server in order for the new server to work and startup?
edit: I am not interested in using mysqldump since it's at least 10x slower with 150GB of data.
mysql
mysql
asked Mar 6 at 11:21
Phillip JPhillip J
245
asked Mar 6 at 11:21
Phillip JPhillip J
245
edited Mar 6 at 11:29
Phillip J
asked Mar 6 at 11:21
Phillip JPhillip J
245
asked Mar 6 at 11:21
Phillip JPhillip J
245
asked Mar 6 at 11:21
Phillip JPhillip J
245
245
1
Possible duplicate of Move MySQL database to a new server
– Nico Haase
Mar 6 at 11:24
I am not using 'mysqldump' to transfer the database, since it'd be super slow (up to 20 hours total, while moving the raw 150GB of files over would be max 1 hour)
– Phillip J
Mar 6 at 11:26
@PhillipJ that's an inherent problem of MySQL and dumps. If you have the Enterprise edition, either bought or compiled from source, you can take real backups and restore them on the new server. Check Moving or Copying InnoDB Tables for other options. Copying/moving files is possible but requires care - you can't just copy data files while transactions are still active. You'd end up with partial or corrupted data
– Panagiotis Kanavos
Mar 6 at 11:34
@PanagiotisKanavos I'm okay with an hour or 2 downtime (shutting down the mysql server), would that not be possible?
– Phillip J
Mar 6 at 11:37
One option is to use the transferable tablespaces feature, although that requires that the tables were created with theinnodb_file_per_tableoption. In that case you need to flush the tables and copy the files. Another option is described as a "cold backup" but in reality it means that you shut down the server and copy the files over
– Panagiotis Kanavos
Mar 6 at 11:37
|
show 4 more comments
1
Possible duplicate of Move MySQL database to a new server
– Nico Haase
Mar 6 at 11:24
I am not using 'mysqldump' to transfer the database, since it'd be super slow (up to 20 hours total, while moving the raw 150GB of files over would be max 1 hour)
– Phillip J
Mar 6 at 11:26
@PhillipJ that's an inherent problem of MySQL and dumps. If you have the Enterprise edition, either bought or compiled from source, you can take real backups and restore them on the new server. Check Moving or Copying InnoDB Tables for other options. Copying/moving files is possible but requires care - you can't just copy data files while transactions are still active. You'd end up with partial or corrupted data
– Panagiotis Kanavos
Mar 6 at 11:34
@PanagiotisKanavos I'm okay with an hour or 2 downtime (shutting down the mysql server), would that not be possible?
– Phillip J
Mar 6 at 11:37
One option is to use the transferable tablespaces feature, although that requires that the tables were created with theinnodb_file_per_tableoption. In that case you need to flush the tables and copy the files. Another option is described as a "cold backup" but in reality it means that you shut down the server and copy the files over
– Panagiotis Kanavos
Mar 6 at 11:37
1
1
Possible duplicate of Move MySQL database to a new server
– Nico Haase
Mar 6 at 11:24
Possible duplicate of Move MySQL database to a new server
– Nico Haase
Mar 6 at 11:24
I am not using 'mysqldump' to transfer the database, since it'd be super slow (up to 20 hours total, while moving the raw 150GB of files over would be max 1 hour)
– Phillip J
Mar 6 at 11:26
I am not using 'mysqldump' to transfer the database, since it'd be super slow (up to 20 hours total, while moving the raw 150GB of files over would be max 1 hour)
– Phillip J
Mar 6 at 11:26
@PhillipJ that's an inherent problem of MySQL and dumps. If you have the Enterprise edition, either bought or compiled from source, you can take real backups and restore them on the new server. Check Moving or Copying InnoDB Tables for other options. Copying/moving files is possible but requires care - you can't just copy data files while transactions are still active. You'd end up with partial or corrupted data
– Panagiotis Kanavos
Mar 6 at 11:34
@PhillipJ that's an inherent problem of MySQL and dumps. If you have the Enterprise edition, either bought or compiled from source, you can take real backups and restore them on the new server. Check Moving or Copying InnoDB Tables for other options. Copying/moving files is possible but requires care - you can't just copy data files while transactions are still active. You'd end up with partial or corrupted data
– Panagiotis Kanavos
Mar 6 at 11:34
@PanagiotisKanavos I'm okay with an hour or 2 downtime (shutting down the mysql server), would that not be possible?
– Phillip J
Mar 6 at 11:37
@PanagiotisKanavos I'm okay with an hour or 2 downtime (shutting down the mysql server), would that not be possible?
– Phillip J
Mar 6 at 11:37
One option is to use the transferable tablespaces feature, although that requires that the tables were created with the
innodb_file_per_table option. In that case you need to flush the tables and copy the files. Another option is described as a "cold backup" but in reality it means that you shut down the server and copy the files over– Panagiotis Kanavos
Mar 6 at 11:37
One option is to use the transferable tablespaces feature, although that requires that the tables were created with the
innodb_file_per_table option. In that case you need to flush the tables and copy the files. Another option is described as a "cold backup" but in reality it means that you shut down the server and copy the files over– Panagiotis Kanavos
Mar 6 at 11:37
|
show 4 more comments
1 Answer
1
active
oldest
votes
if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
Here is step
- Stop the database (or lock it)
- Go to the directory where the mysql data files are.
- Transfer over the folder (and its contents) over to the new server's mysql data directory
- Start back up the database
- On the new server, issue a 'create database' command.'
- Re-create the users & grant permissions.
But you can face issue if your my.cnf both server is different and also you have to check other configuration changes(like 8 support Roles,caching_sha2_password plugin in that,also does not support query cache )
answered Mar 6 at 13:33
vishalvishal
268
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55021875%2fwhich-mysql-innodb-files-to-move-when-transfering-to-a-new-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
Here is step
- Stop the database (or lock it)
- Go to the directory where the mysql data files are.
- Transfer over the folder (and its contents) over to the new server's mysql data directory
- Start back up the database
- On the new server, issue a 'create database' command.'
- Re-create the users & grant permissions.
But you can face issue if your my.cnf both server is different and also you have to check other configuration changes(like 8 support Roles,caching_sha2_password plugin in that,also does not support query cache )
answered Mar 6 at 13:33
vishalvishal
268
add a comment |
if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
Here is step
- Stop the database (or lock it)
- Go to the directory where the mysql data files are.
- Transfer over the folder (and its contents) over to the new server's mysql data directory
- Start back up the database
- On the new server, issue a 'create database' command.'
- Re-create the users & grant permissions.
But you can face issue if your my.cnf both server is different and also you have to check other configuration changes(like 8 support Roles,caching_sha2_password plugin in that,also does not support query cache )
answered Mar 6 at 13:33
vishalvishal
268
add a comment |
if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
Here is step
- Stop the database (or lock it)
- Go to the directory where the mysql data files are.
- Transfer over the folder (and its contents) over to the new server's mysql data directory
- Start back up the database
- On the new server, issue a 'create database' command.'
- Re-create the users & grant permissions.
But you can face issue if your my.cnf both server is different and also you have to check other configuration changes(like 8 support Roles,caching_sha2_password plugin in that,also does not support query cache )
answered Mar 6 at 13:33
vishalvishal
268
if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
Here is step
- Stop the database (or lock it)
- Go to the directory where the mysql data files are.
- Transfer over the folder (and its contents) over to the new server's mysql data directory
- Start back up the database
- On the new server, issue a 'create database' command.'
- Re-create the users & grant permissions.
But you can face issue if your my.cnf both server is different and also you have to check other configuration changes(like 8 support Roles,caching_sha2_password plugin in that,also does not support query cache )
answered Mar 6 at 13:33
vishalvishal
268
answered Mar 6 at 13:33
vishalvishal
268
answered Mar 6 at 13:33
vishalvishal
268
answered Mar 6 at 13:33
vishalvishal
268
268
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55021875%2fwhich-mysql-innodb-files-to-move-when-transfering-to-a-new-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Possible duplicate of Move MySQL database to a new server
– Nico Haase
Mar 6 at 11:24
I am not using 'mysqldump' to transfer the database, since it'd be super slow (up to 20 hours total, while moving the raw 150GB of files over would be max 1 hour)
– Phillip J
Mar 6 at 11:26
@PhillipJ that's an inherent problem of MySQL and dumps. If you have the Enterprise edition, either bought or compiled from source, you can take real backups and restore them on the new server. Check Moving or Copying InnoDB Tables for other options. Copying/moving files is possible but requires care - you can't just copy data files while transactions are still active. You'd end up with partial or corrupted data
– Panagiotis Kanavos
Mar 6 at 11:34
@PanagiotisKanavos I'm okay with an hour or 2 downtime (shutting down the mysql server), would that not be possible?
– Phillip J
Mar 6 at 11:37
One option is to use the transferable tablespaces feature, although that requires that the tables were created with the
innodb_file_per_tableoption. In that case you need to flush the tables and copy the files. Another option is described as a "cold backup" but in reality it means that you shut down the server and copy the files over– Panagiotis Kanavos
Mar 6 at 11:37