[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copy selected records from one MySQL host/server to another.

Posted on 2011-10-16
14
Medium Priority
?
340 Views
Last Modified: 2012-05-12
We are using MySQL 5.1.39.

We need to synchronize some project data from one database to the other.
The databases reside on different servers.

We need to copy the records for exactly one selected 'project' from ALL the tables in one database to another. For example, we need the following for all tables in a database:

SELECT * FROM table WHERE projectid=123;

There are so many tables, so we'd hate to do this one by one.

mysqldump doesn't seem to have a "SELECT" option where you can select records from each table.

What's the easiest way to do this?
0
Comment
Question by:mph23
  • 7
  • 6
14 Comments
 
LVL 3

Expert Comment

by:trackstudio10
ID: 36976535
As I know copying tables from database on one server to database on another server whould in each case mean exporting/importing. What you need to do is to select needed data range (just as in example that you provided, using SELECT query). Then you need to export it to .sql file. If you are using phpMyAdmin tool, you should have 'export' tab in phpMyAdmin menu or 'export' button in the bottom of the page when browsing tables. Use it to create .sql file.

In any case, you will get .sql file containing queries:

CREATE TABLE IF NOT EXISTS `table` ();

and

INSERT INTO 'table' VALUES ();

After that you will either need to import .sql file using 'import' button in phpMyAdmin on your new database on another server. Or simply by executing queries from your new file.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36976537
Check the manual for the SELECT ... INTO OUTFILE syntax, as it may help you.
http://dev.mysql.com/doc/refman/5.1/en/select.html

You can additionally use the INFORMATION_SCHEMA to help you.
For example:
SELECT table_name
FROM information_schema.columns
WHERE column_name = 'projectid'
;

Open in new window


With this, you can use prepared statements to run dynamic SQL you generate from the results of the INFORMATION_SCHEMA query above. Manual page for prepared statements: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
Example solution I just provided: http://www.experts-exchange.com/Q_27397709.html#a36972117

In your case, I would have the SQL generated with the table_name as file name. Then you can use INFORMATION_SCHEMA on the other side to generate matching LOAD DATA INFILE statements. Thought there is predicated on the table names matching, so you can take advantage of finding file names dynamically. If not, then you will just have to manually execute your LOAD DATA INFILE statements associating the proper table to each file.
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope that helps!
0
 

Author Comment

by:mph23
ID: 36976630
Trackstudio10: I don't have phpmyadmin. I'm using mysql administrator 1.2.17 and MySQL Query Browser.

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:mph23
ID: 36976648
mwvisa1:
Can you explain a little more about information_schema.columns in your example?

I looked it up and does information_schema show all tables ?
or does the ".columns" show all the columns for each table?

So does the one SELECT statement you provided above output for all table without the need to loop thru and name each?

So the following would do what I need:

SELECT table_name INTO OUTFILE = table_name.txt
FROM information_schema.columns
WHERE column_name = 'projectid'
;
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36976767
INFORMATION_SCHEMA.COLUMNS contains columns for each table. INFORMATION_SCHEMA.TABLES would show all the tables. Since you want to only grab tables that have projectid since it is required in the filter, I used the COLUMNS view. You do NOT want to use OUTFILE on that query unless of course all you want is a list of tables. You would instead create dynamic SQL using that table and then used prepared statements to execute. That is what the linked EE questions shows you how to do.

An example would be:
SELECT
GROUP_CONCAT(
   CONCAT('SELECT * 
    FROM `', table_name, '`
    WHERE `', column_name, '` = 123
    INTO OUTFILE ''c:/temp/', table_name, '.csv''
    FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
    LINES TERMINATED BY ''\\n'';')
   SEPARATOR '\n'
)
FROM information_schema.columns
WHERE column_name = 'projectid'
INTO @sqlstring
;

-- you can open value in viewer, then execute scripts
SELECT @sqlstring;

Open in new window


As suggested in the comment, you can grab the output SQL string which will have each command separated by a semi-colon and a new line, e.g.:
SELECT * 
    FROM `example table 1`
    WHERE `projectid` = 123
    INTO OUTFILE 'c:/temp/example table 1.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
SELECT * 
    FROM `example table 2`
    WHERE `projectid` = 123
    INTO OUTFILE 'c:/temp/example table 2.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

Open in new window


Note: if the file already exists, then it will throw and error.

Additionally, to move from the example to final code, you may need to use a WHILE loop grabbing LIMIT 1 each time as prepared statements can only do one sql statement at a time as stated in documentation, which gives me a better idea on how to structure this. I will post back, but hopefully that helps for now.

Kevin
0
 

Author Comment

by:mph23
ID: 36976898
Please elaborate more on steps 17.

Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36977351
You mean "you can open value in viewer, then execute scripts?" For that step, I am sorry I am using MySQL Workbench, so keep thinking in those terms. When results come back in MySQL Workbench, you can right-click on the contents in a cell and use "Open Value in Viewer" -- I cannot recall if MySQL Query Browser had that. I remember getting the results back and typically saving results as. If you can get the results out to a .sql file that you then run through MySQL Query Browser then great. The point of that step is SELECT @sqlstring brings back the content compiled by the INFORMATION_SCHEMA query and so is a long set of multiple SQL statements that you need to copy and paste into a script tab and execute.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36977532
Actually with that in mind, you can even forget about the GROUP_CONCAT().

SELECT
CONCAT('SELECT * 
    FROM `', table_name, '`
    WHERE `', column_name, '` = 123
    INTO OUTFILE ''c:/temp/', table_name, '.csv''
    FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
    LINES TERMINATED BY ''\\n'';') AS OutfileScripts
FROM information_schema.columns
WHERE column_name = 'projectid'
;

Open in new window


Run this, and you will get a row with OUTFILE script per table name.
0
 

Author Comment

by:mph23
ID: 36978007
I  tried the SELECT above and it didn't return anything.
Could it be the "WHERE" clause?

The column "projectid" is a text field so does 123 need to be in quotes?
I tried adding single quotes but got an error.

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36981146
Yes, 123 should be in quotes if projectid is a text field. It would be:
SELECT
CONCAT('SELECT * 
    FROM `', table_name, '`
    WHERE `', column_name, '` = ''123''
    INTO OUTFILE ''c:/temp/', table_name, '.csv''
    FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
    LINES TERMINATED BY ''\\n'';') AS OutfileScripts
FROM information_schema.columns
WHERE column_name = 'projectid'
;

Open in new window


The bigger issue is if you get no results, then it means it is not finding any tables with a column named projectid therefore please change that to the real column name if that was just a fictitious one as an example.
0
 

Author Comment

by:mph23
ID: 36982172
Still not returning anything. I even tried different values, columns and tables.

Could it be I have to specify the database name somewhere?

Any ideas?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36983048
What happens when you just run SELECT * FROM INFORMATION_SCHEMA.COLUMNS?
0
 

Author Comment

by:mph23
ID: 37092234
Sorry, got smashed with another emergency project. Will try your suggestion above.

Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37095624
Wonderful. Just post back when you have results and want to continue (if still necessary).
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question