MySQL – How to Replicate Only One Database

MySQLreplication

I just wanted to check on the replication setup I've got working. The plan was to replicate just one database, so per the MySQL documentation I used the replicate-do-db flag in my MySQL configuration. However per the documentation it says it won't work if your code uses "db.table" format of inserts…

Note that this does not replicate
cross-database statements such as
UPDATE some_db.some_table SET
foo='bar' while having selected a
different database or no database.

So, does my combination of the replicate-do-db flag AND the replicate-wild-do-table flag fix this?

replicate-do-db = replicate_me
replicate-wild-do-table = replicate_me.%

I was just wondering, as the replication works as needed.

Best Answer

It's not that all db.table INSERTs won't work, it's that cross-database ones won't (see the example in the doc page you link to). Your replication probably works because you're not doing any cross-database INSERTs; most people don't.

replicate-wild-do-table won't change anything for this purpose.

Related Solutions

Linux – PostgreSQL Replication

Short answer - there's no such solution yet for PostgreSQL if you need online readonly slaves.

There're two major development projects currently going on in this area which are included in PostgreSQL 9.0 (Spring/Summer 2010), namely:

Synchronous Replication:

http://wiki.postgresql.org/wiki/NTT's_Development_Projects

Read only hot standby slaves:

http://wiki.postgresql.org/wiki/Hot_Standby

which in combination aim to achieve the ease of use of MySQL-style replication minus the bugs/issues MySQL has plus the reliability users know from PostgreSQL.

All of this was kicked off by a manifest from the PostgreSQL Core Team in 2008:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php

The PostgreSQL replication solutions to this day with the largest user base are Slony-I (more expensive for writes, makes schema changes fiddly), WAL shipping/walmgr (Slaves can't be used online) and pgQ/londiste from Skype/Skytools (more tools/building blocks than a finished solution).

I've written a few things on Log Shipping, walmgr and Slony-I, see

http://blogs.amd.co.at/mt/mt-search.cgi?blog_id=1&tag=pgrep&limit=20 for more information.

MySQL – Alter Charset and Collation in All Columns and Tables

First of all, don't just take my word for it! Test my suggestion out with this:

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;

If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this:

mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret

When you start thinking about using valid SQL to generate valid SQL, it changes the whole game. You will be amazed by how many uses you find for it.

Best Answer

Related Solutions

Linux – PostgreSQL Replication

MySQL – Alter Charset and Collation in All Columns and Tables

Related Topic