The title is actually misleading just because I wanted to keep it short.
What I wanted to achieve was to replicate 2 databases on another mysql server. Furthermore the replicated database should have different names from the sources and one of the 2 databases needed to be replicated only on a subset of tables.
I couldn’t really find any article with the right combination of options so I had to merge several papers and decided to write a new one that could be useful to someone else.
Before starting: I’m using mariadb with a version above 5.5.22. I decided not to use Percona’s xtrabackup because I wanted to “sync” only a 2 databases in a server containing hundreds.
Here my steps:
1. Configure the master
On the “master” you need to change you configuration file:
vi /etc/mysql/my.cnf
add/change under [mysqld]
server-id=1
bind-address = 0.0.0.0
If necessary enable the bilog with something like:
log-bin = /var/log/mysql/mariadb-bin.log
then restart your mysql
service mysql restart
2. Dump the databases you need to replicate
I had to dump the original databases and import them on the new server.
mysqldump -u root -p -e -q –single-transaction –master-data –databases magento_ecommerce magento_wordpress > rep_dump.sql
this command is going to dump my two databases inside rep_dump.sql. It is going to perform it “in a single transaction” preserving the consistency of my databases.
3. Get the log position
Inside the dump you can find the transaction information that you’ll need to start the mysql slave:
grep MASTER_LOG_FILE rep_dump.sql
should give you something like:
CHANGE MASTER TO MASTER_LOG_FILE=’mariadb-bin.000026′, MASTER_LOG_POS=2714;
We will use this later.
4. Create the replication user
On the master create a new user like this one:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO ‘repuser’@’%’ IDENTIFIED BY ‘yourpassword’;
Check if you can connect fro slave to master with the command:
mysql -h masterip -u repuser -p’yourpassword’
issued from the slave server
5. import the databases into the slave:
from the master:
transfer the dump file:
scp rep_dump.sql youruser@slaveip:/home/youruser
chenge the databases names inside the dumpfile:
sed -i ‘s/magento_ecommerce/magento_ecommerce_sync/g’ rep_dump.sql
sed -i ‘s/magento_wordpress/magento_wordpress_sync/g’ rep_dump.sql
import the dumpfile:
mysql -u root -p’rootpassword’ < rep_dump.sql
now you should find the magento_wordpress_sync and magento_ecommerce_sync in your slave.
6. Configure the slave:
under [mysqld] add:
server-id=2
replicate-rewrite-db=”magento_ecommerce->magento_ecommerce_sync”
replicate-rewrite-db=”magento_wordpress->magento_wordpress_sync”
replicate-wild-do-table=magento_wordpress_sync.%
replicate-wild-do-table=magento_ecommerce_sync.catalog_category_entity_%
replicate-wild-do-table=magento_ecommerce_sync.catalog_product_entity_%
replicate-wild-do-table=magento_ecommerce_sync.catalog_product_link_%
The replicate-rewrite-db parameters are going to rename the source databases.
replicate-wild-do-table is used to filter only the tables you really want to replicate. They can contain wildcards and the database name should be the “renamed” one.
Restart mysql:
service mysql restart
7. start the slave
Connect to your slave database with the root account and give the following commands:
CHANGE MASTER TO
MASTER_HOST=’masterip’,
MASTER_PORT=3306,
MASTER_USER=’repuser’,
MASTER_PASSWORD=’yourpassword’,
MASTER_LOG_FILE=’mariadb-bin.000026′,
MASTER_LOG_POS=2714;
START SLAVE;
show slave status\G
should show any error.
That’s all.
I suggest you to use a monitoring system (or a script) to keep you informed if the replication stops.