Mysql – Select statement on tables with the same name and schema but in different databases

MySQL

I have a table that lists the names of the databases that have tables with the same schema. Each customer has a different database.

Is there a way to run a select on same table in multiple databases?

The first query I wrote was to get a list of databases concatenated the table name.

Select concat(database_name,'.','TableA') from dbnamelist

This results in output like

database1.TableA
database2.TableA

Now I want to select a value in the column type but across different tables.

Select type from database1.TableA,database2,TableA

Is it possible to do this in a single SQL statement?

Best Answer

SELECT  GROUP_CONCAT(CONCAT(table_schema, '.', tableA))
    FROM information_schema.tables
    WHERE table_name = 'tableA';

will generate the list you need.

Figure out what the query needs to look like (and I do not think your example is correct), and modify this SELECT to generate the desired query.

Then copy and paste it into mysql commandline tool to run it.

Related Solutions

MYSQL – ‘Select distinct’ – different results on two exact versions of Mysql, databases synced

This is just a hunch, but make sure your collation settings are the same between the dbs. I know you said they're synced, but check.

If you can't find anything there run each select into their own outfile

select bdtag3_.* into outfile '/tmp/db1.data' ....

and

select bdtag3_.* into outfile '/tmp/db2.data' ....

You have an order by so running a basic commandline diff should quickly highlight differences.

The pt-table-checksum tool is also handy for finding differences.

Mysql – php/thesql database redesign and migration: changing databases to tables

Instead of Migration to another System, try doing it in place, then mysqldump DATA_ONE and import that mysqldump into the new server.

First, let's create DATA_ONE

DROP DATABASE IF EXISTS DATA_ONE;
CREATE DATABASE DATA_ONE;

Next, Create DATA_ONE tables using the template from database aa123

CREATE TABLE DATA_ONE.personal LIKE aa123.personal;
CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;
CREATE TABLE DATA_ONE.games    LIKE aa123.games;

Now, Create a UserInfo Table

CREATE TABLE DATA_ONE.UserInfo
(
    userid INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    last_update DATETIME NOT NULL,
    PRIMARY KEY (userid),
    KEY (username)
);
INSERT INTO DATA_ONE.UserInfo (username,last_update)
SELECT database_name,last_update FROM users.userinfo;

Then, add the database_name column (and index it) to the DATA_ONE tables

ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);
ALTER TABLE DATA_ONE.personal ADD userid INT;
ALTER TABLE DATA_ONE.accounts ADD userid INT;
ALTER TABLE DATA_ONE.games    ADD userid INT;
ALTER TABLE DATA_ONE.personal ADD INDEX (username);
ALTER TABLE DATA_ONE.personal ADD INDEX (userid);
ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);
ALTER TABLE DATA_ONE.games    ADD INDEX (userid);

OK, that makes the new DATA_ONE database.

Now comes the adventurous part: Loading all those tables into DATA_ONE.

Run these three queries to formulate the SQL needed for migration

SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;

Here is everything mentioned above shell-scripted and executed:

SQLFILE=/root/MakeDATA_ONE_Database.sql
echo "DROP DATABASE IF EXISTS DATA_ONE;" > ${SQLFILE}
echo "CREATE DATABASE DATA_ONE;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.personal LIKE aa123.personal;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.games    LIKE aa123.games;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.UserInfo (userid INT NOT NULL AUTO_INCREMENT,username VARCHAR(32),last_update DATETIME NOT NULL,PRIMARY KEY (userid),KEY (username));" >> ${SQLFILE}
echo "INSERT INTO DATA_ONE.UserInfo (username,last_update) SELECT database_name,last_update FROM users.userinfo;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (username);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD INDEX (userid);" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -A < ${SQLFILE}

Now open mysql-workbench or phpmyadmin or whatever you browse data with and look through the DATA_ONE database.

If you are satisfied with what is there, just do this:

mysqldump -u... -p... --databases DATA_ONE > /root/DATA_ONE.sql

Move the DATA_ONE.sql file over to the new server and execute the script.

That's it. Pure SQL solution. No PHP. If anything does not work, it is probably because I do not know the layout and current contents of the personal, account, & games tables and made assumptions on data. Please post the following in your question:

SHOW CREATE TABLE aa123.personal\G
SHOW CREATE TABLE aa123.accounts\G
SHOW CREATE TABLE aa123.games\G

Give it a Try !!!

UPDATE 2012-05-23 13:57 EDT

Oh, you have 2500 databases? My above solution should still work.

I do have an alternative suggestion: Try to mysqldump all databases into individual files.

Here is a shell script that will dump all 2500 databases into separate files and gzipped

MYSQL_CONN="-uusername -ppassword"
SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt
MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
COMMIT_COUNT=0
COMMIT_LIMIT=50
for DB in `cat /tmp/ListOfDatabases.txt`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Once you execute this, all databases will be mysqldump'd. For example, the database aa123 will be dumped and gzipped to aa123.sql.gz. This and all the other 2499 mysqldumps can be copied over to the new DB server. Place the dumps in /root/mysqldata on the new DB server. Once they are on the new DB server, you can then unzip all files like this:

cd /root/mysqldata
for X in `ls *.sql.gz` ; do echo ${X} ; gzip -d ${X} ; done

OK this will unzip all the files.

Now, run this to load the SQL scripts into mysql on the new server

cd /root/mysqldata
for X in `ls *.sql` ; do echo ${X} ; mysql -u... -p... -A < ${X} ; done

That's it. No PHP involved. All iteration is handled in the shell script I provided. You can then combine them in the new DB Server as DATA_ONE like I mentioned before.