local or remote mysql

[phirestalker]

I have a "server" set up now with nzedb. It only has 2 cores at 2.8GHZ and 4GB of RAM. nzedb is all it is doing and it can barely handle 20 groups. It is much faster since I converted to cinnodb tables.

Here is my question. Will it benefit me to move just the mysql server to a machine on my local 100MBs network if it has 4 cores at 3.3GHZ and 16 GB of RAM? or will the network be too much of a bottleneck to see improvement? Or is mysql even 50% of the resources of running nzedb?
Would your answer be different if I upgraded to 1GBs network?

Thanks

[PREngineer]

MySQL is handling MOST of the CPU load.  I'm planning on switching my SQL instance to an outer server to improve the response times from the webserver.

If you try this, please post your results.

[brumsky]

It would help if you posted more information regarding your DB size, number of releases et cetera.  I'd say start by increasing the RAM 4 Gigs is a bit on the light side.  At least 8 gigs if not 16gigs then try again.  The more you can fit in RAM the better.

[phirestalker]

I am thinking of tackling this today. What is the easiest way to move the mysql over to another server? Can I shut down mysql dump it and load the dump on the new server, then change the config file to point to the new server? Is there a better way?

[ThePeePs]

You could do that, or just copy the files (usually located in /var/lib/mysql/<db_name>).  I would use rsync for this, and it should work, if not, you can always do a dump, and then import onto your new box.

[bobtongue]

rsync is excellent for that. I just moved my server to a new box and rsync'ed the whole thing over. Worked like a charm

[phirestalker]

ya after 2 days of frustration and googling, I realize I left out some important information....

I am using Innodb file per table with mariadb

Innodb cannot be imported from just moving files, and as yet I still don't have a working answer on how you DO move them. I tried percona-xtrabackup which is supposed to be made to move Innodb, yet it didn't work.

There are inconsistencies in the log even though I did the backup with the server stopped, and somehow the mysql database is missing all it's tables.

Does anyone know how to move a database with Innodb tables???

[kaibosh]

A simple copy of the DB files will generally work for a single database if both the target and destination DB instances are configured identically and the ibdata1 file is copied along with the DB directory.

A "cleaner" way to migrate the database is to export/dump to an SQL file and import that into the new instance.

Edit: nzedb has recently started using stored procedures. Use the --routines option to ensure these are copied as well.

[phirestalker]

OK, competed mysqldump and imported. Everything is working GREAT!!!