MySQL replication one-slave-multi-master

Preface.

It was necessary to replicate with several master servers with mysql, so that the data from all of them were loaded onto one slave server. There was no ready solution by standard means. But since the problem remained relevant, with time, a little complicated, but workable version using the tools of mysql itself arrived.

Actually, the decision.
Master servers are configured as in normal replication. All witchcraft with slave.
Replication from each master is done by separate auxiliary mysqld processes. The tables in the target database run on the FEDERATED engine and are connected to the databases of the main mysqld process.
If you are familiar with mysql_multi and federated, then on this, in general, everything. Next, a few subtleties of implementation and utility.

Configured slave with ubuntu-server 10.04.

mysqld_multi

I will not describe the basic configuration of mysqld_multi, and indeed there is none. The /usr/share/mysql/mysqld_multi.server has an init.d script.
The mysqld_multi settings are best done in a separate file, as the script requires a username and password to access the servers to control the daemons.
Also, to have common settings for all replication processes, leave the mysqld section.
For each replicated database, create a mysqldN section with server and replication settings. And you need to remember to include federated.
')
$sudo cp /etc/mysql/my.cnf /etc/mysql/my_multi.cnf
$sudo chmod 600 /etc/mysql/my_multi.cnf

:
[mysqld_multi]
log = /var/log/mysql/mysqld_multi.log
mysqld = /usr/sbin/mysqld
mysqladmin = /usr/bin/mysqladmin
user = root
password = pass

[mysqld1]
datadir = /var/lib/mysql_multi/mysql1
#datadir = /var/lib/mysql_multi
socket = /var/run/mysqld/mysqld1.sock
port = 33061
tmpdir = /var/tmp/mysql/mysqld1
pid-file = /var/run/mysqld/mysqld1.pid
log_error = /var/log/mysql/error1.log
federated
#skip-innodb

server-id = 101
replicate-do-db = db
#replicate-do-db = my_db1
#replicate-rewrite-db = db->my_db1
master-info-file = mysql1-master.info
relay-log = mysql1-relay-bin
relay-log-index = mysql1-relay-bin.index
relay-log-info-file = mysql1-relay-log.info
replicate-wild-ignore-table = mysql.%

[mysqld2]
....


There are 2 options for how to store databases: all together in one directory (unstring the lines) or for each server to specify its own datadir.
The first will have fewer directories and general mysql system schemas (passwords in it), but innodb does not work.
The 2nd way is more flexible, stable and works with innodb. I settled on it. Yes, and query-browser, as it turned out, appends the full names of the tables in any case, and replication with rewrite does not understand this.

You must now start mysqld_multi with the --defaults-extra-file=/etc/mysql/my_multi.cnf
In the init.d script also added.

If apparmor works, then its settings need to be corrected:
/etc/apparmor.d/usr.sbin.mysqld:
....
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/lib/mysql_multi/ r,
/var/lib/mysql_multi/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/var/run/mysqld/mysqld.pid w,
/var/run/mysqld/mysqld.sock w,
/var/run/mysqld/mysqld?.pid w,
/var/run/mysqld/mysqld?.sock w,
/var/run/mysqld/mysqld??.pid w,
/var/run/mysqld/mysqld??.sock w,
....

$sudo service apparmor reload

Create directories under the database. If the main base is still clean, you can copy it, otherwise use mysql_install_db
$sudo mkdir -pm700 /var/lib/mysql_multi/mysqlN
$sudo chown -R mysql:mysql /var/lib/mysql_multi
$sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql_multi/mysqlN

We start and check.
$sudo service mysqld_multi start
$mysql -uroot -p -h127.0.0.1 -P33061


federated.

On the main server, create a user to connect to the tables by federated:
mysql>CREATE USER 'fdrt_local'@'localhost';
mysql>GRANT SELECT, UPDATE, DELETE ON 'rpl_%' TO 'fdrt_local'@'localhost';


Before running.

Expand the current database on the main server.

On the master
$mysqldump --opt bd | gzip > dbN_full.sql.gz
$mysqldump --opt -d bd | gzip > dbN_nodata.sql.gz

We transfer archives to the server, then
$zcat bd_full.sql.gz | mysql repl_dbN
I also added triggers to some tables in the target databases, to create summarizing tables.

Create a database with federated tables on the slave server.

Using the following regular, create the file dbN_fdrt.sql and upload it to the slave server.
#(CREATE[\s\w]*)`(\w*)`((.|[\n\r])*?ENGINE\s*=\s*)(\w)+((.|[\n\r])*?;)#iu

$1`$2`$3FEDERATED CONNECTION=\'mysql://fdrt_local@localhost/%db_name%/$2\'$6

You can supply the name of the base to the regulars, or you can change the sed before the restor.
$sed 's/%db_name%/repl_dbN/g' bd_fdrt.sql | mysql -h127.0.0.1 -P33061 dbN


Or even better and easier to use the CREATE SERVER command one per base.

UPD: with CREATE SERVER did not work in mysqld 5.1.41-3ubuntu12.7.

Next, set up the slave and start replication. Is done.

Corrected: several small scripts help me manage replications, check status, and more. If anyone is interested, I will gladly share it.