How to move mysql data to another server?

[darkod]

I was put into the position a few months ago of having to create a bug tracking system for the company.

I have two machines like you and I use one for the back (an old piece of c) and the other for the production system.

I have used mysqldump for backing up on one and restoring on the other.
I have also found it useful when people ask for changes on the system, to make changes on the backup, allow myself and others to test and roll put to production.

So, when It comes to mysql I am no expert but have found the copy from one to the other went fine and without a hitch.

Since installing bugzilla3, I installed mediawiki and mantis and all work fine with the backup and restore.
I found it copied everything I needed

Just to clarify, we are talking about the pair of commands from post #5 right?

Everything goes into place without the need to worry about any indexes, primary keys, etc?

[jonobr]

Yep


That worked for me with Bugzilla3, mediawiki and mantis.

Im no genius and I had two systems that had all the exact same info after the restore.

Can you not do a dummy run first importing to your second machine to make sure its good?

There should be no harm in exporting from production and importing to the second machine.
Here is the nice quick tutorial I keep going back to


One big word of warning
Watch out for which machine your working on at any one time ,
and which way your '><' is facing.

If you get into the habit of leaving your backup in the same directory as your restore and have the >< in the wrong direction then you could end up restoring to an older point.

When you get into doing backups regularly , you need to ensure its backedup and moved off for storage elsewhere and most likely automate the whole thing so it does it itself.

Actually, I think I have gone off topic a bit here....

[LHammonds]

I have done this very same thing a couple of times.

I made an automated backup script which uses mysqldump and exports all the database in 3 different ways. One creates a file containing everything which would be used in a full server restore. It also creates individual database files in case you only want to restore one single database and the 3rd level backs up individual tables to file in case you need to restore a corrupted table.

The link in my sig will take you to how I configured my MySQL server as well as the backup script I have scheduled and there is even a section on the steps I used to migrate the database to a brand new server using my backup method.

If you are new to this, I recommend making a test run of the entire process without actually taking down the database. Simply put the backup on the new server and restore the databases and verify that your user accounts, databases, tables and data (row counts) are there. Then drop all the databases in preparation for restoring it again. When ready, stop your applications from writing to the database, make a backup, transfer the backup to the new machine and do the restore. Depending on your environment, you will either need to update the address of the new database server in your applications or shutdown the old server and configure the new server with the same IP (which is what I did). Then start up your applications and make sure they connect.

LHammonds

[JCC955i]

Nothing to add here really except a +1 on the advice LHammonds and jonobr are giving. I've used the same sequence for backing up, moving and testing MySQL databases numerous times.

It's really very worthwhile to replicate in a test environment before you go ahead on your live systems.

[darkod]

Update: I used the mysqldump on the older server and then restored the file with the mysql command to a test VM (third machine for quick test). I'm impressed. It seems great.

It spooked me a little first when doing a quick query in MySQL Workbench until I realised it limited the output to only 1000 entries.

Everything seems to be there.

On another topic, when I tried exporting into CSV I got this:

mysqldump: Got error: 1045: Access denied for user 'asteriskuser'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'

I was under the impression this user has GRANT ALL PRIVILEGES on this DB. What is missing?

[GordThompson]

Make sure that the 'mysql' user has permission to write to the folder where you want to save the output file(s). From the `mysqldump` man page:

[The '-T'] option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.

[darkod]

Make sure that the 'mysql' user has permission to write to the folder where you want to save the output file(s). From the `mysqldump` man page:

Thanks, I think you are spot on. I specified the /root as destination by the mysql user I am using is not root and can't write there.

I´ll give it another go later.

PS. If I don't use the -T option, what is the alternative? On the right side of the command to use something like > filename? So, it would be like:

mysqldump -u username -p -t database --fields-terminated-by=, > filename

In this case do I need the -t too?

[SeijiSensei]

The MySQL username is irrelevant to file permissions. What matters is the user the mysqld daemon runs as which is "mysql" by default. (Same issue as Apache running as the "www-data" user.) Try writing to /tmp and see if that works.

Even if you log in as 'root'@'localhost' in MySQL, you won't be the root user from the perspective of the operating system. You'll still be executing processes as the "mysql" user.

PostgreSQL works the same way.

The solution you give at the end is the best workaround. Run the command as root and pipe the results to a file. From my reading of the "-t" option, it suppresses CREATE TABLE statements which would seem to make sense in a CSV file.

Don't you also need for text fields to be wrapped in quotation marks for CSV, or is

,this is a string,another string,37

acceptable CSV syntax? What about fields with values that include commas?

If you want to migrate all the databases in a MySQL instance, you can use the "--all-databases" option to mysqldump when creating the backup file. For PostgreSQL users like me, the equivalent commands are pg_dump for individual databases and pg_dumpall for all databases.

[darkod]

The MySQL username is irrelevant to file permissions. What matters is the user the mysqld daemon runs as which is "mysql" by default. (Same issue as Apache running as the "www-data" user.) Try writing to /tmp and see if that works.

Even if you log in as 'root'@'localhost' in MySQL, you won't be the root user from the perspective of the operating system. You'll still be executing processes as the "mysql" user.

PostgreSQL works the same way.

Yeah, slipped my mind we are only talking about the mysql user and not the linux user. I'll give it a go to a folder where everyone can write. Thanks.

[darkod]

The export into CSV didn't work, but I'm quite satisfied how the rest works.

If I wanted to start showing off ( ) and use conditions with the mysqldump, would something like this be correct:

mysqldump -u username -p database --where='calldate=>2007-01-01 AND calldate<=2007-12-31' > sqldump.sql

Or the column calldate can't be expressed like that, it needs to be the "full" expression like database.table.calldate?

I'm attaching my latest export to CSV try, I made a new folder and gave read/write permissions to all, so the error is definitely not because the mysql user can't write to the folder. It might be that the mysql user running the dump doesn't have as high permissions as I think.