How to move mysql data to another server?

[darkod]

OK, the mysqldump works with --where='calldate<=2007-12-31' for example, but i can't make it work with two dates to make a time period. I'm trying with:
--where='calldate=>2007-01-01 AND calldate<=2007-12-31'

and replacing the AND with &&. Both failed. I'm sure you can do a time period statement, I just don't know how yet.

[SeijiSensei]

If you have a Windows machine around with Office installed, you might consider installing the ODBC Connector for MySQL and using Excel or Access to extract data, especially if you want to create CSV files. The MySQL server configuration will have to permit external connections, and you'll need a 'user'@'windows_box_ip' entry, too, I suspect, or a 'user'@'%' entry.

I find this method much easier when I want to import or export tables between and SQL server and spreadsheets. I've used LibreOffice Base on occasion for this, but the Microsoft tools just have more features.

[darkod]

I spoke too fast.

That --where condition I specified doesn't work as intended. It only creates empty 4K file. I'll play around with it.

I already have root@windows_box user to use it with Workbench, and both Office and LibreOffice. I can give that a shot too.

But I'm using the ocassion to learn little bit about mysql commands too, so I'm not jumping into windows right away. Not that we mentioned it, I can probably do the csv export in Workbench.

Right now I have the test machine set with a user for Workbench (the root@windows_box mentioned above), but I can try granting privileges to the same user on the servers too.

[GordThompson]

OK, the mysqldump works with --where='calldate<=2007-12-31' for example, but i can't make it work with two dates to make a time period. I'm trying with:
--where='calldate=>2007-01-01 AND calldate<=2007-12-31'

and replacing the AND with &&. Both failed. I'm sure you can do a time period statement, I just don't know how yet.

Once again, referring to the man page for `mysqldump`, one of the examples they give is

--where="user='jimf'"

Given that MySQL normally likes to see quotes around date literals, your option might work better if it was something like

--where="calldate=>'2007-01-01' AND calldate<='2007-12-31'"

[darkod]

Yeah, I tried that too but it failed. After a bit trial and error, this is what worked (note that on the old server the mysql is 4.1.22 so it might have something to do with options syntax):

For single date:

--where="calldate<='2007-12-31'"

to get all records before 2008.

For time period it actually worked with two separate options:

--where="calldate=>'2008-01-01'" --where="calldate<='2008-12-31'"

That gave me a 103MB file as opposed to empty 4KB file.

[jonobr]

I wish I could help more here, but as said originally
My knowledge is limited to mysql backup restore, query tables and items etc,.....You know , the stuff you show your boss that looks impressive but is really basic stuff


best of luck

[darkod]

You all helped a lot, don't worry.

As I said, after trial and error I found how to dump the data only for a specific period, using the --where option.

I will play more with export to csv, both on the command line and using something like Workbench/Office/LibreOffice.

I will close the thread since the main thing is answered. Thanks all. Further comments are welcomed still.

[darkod]

I have to add to my post #25. The --where condition in it is not correct, and I found out what was bothering the syntax using only one --where condition to specify time period. It was the = sign.

So, to use mysqldump for only a period, what worked at the end is:

--where="calldate=>'2008-01-01 00:00:00' AND calldate<='2008-12-31 23:59:59'"

Of course, the above is only the condition that worked for me, you still have to add the full mysqldump syntax.