Browny's Grotto :: http://www.clan-elite.info/

 » Home-page › Forum › Announc ... and News › Configure MySQL replication with existing data Back to Top « 

Configure MySQL replication with existing data

1 pages: [ 1 ] [ View newest post ] Post reply.
Mr.Brownstone
Administrator.
“He won’t leave me alone…”
Avatar.
Posts: 1,546
View profile
Send message
Configure MySQL replication with existing data Edit this post. Quote this post in a reply.
After browsing the MySQL documentation and various how-tos, I've come up with this simple procedure to get up and running with MySQL replication on Ubuntu Server with as few steps as possible.

That means that no, you don't have to lock the master database for ages while you mess about with the command-line. Neither do you have to use SHOW MASTER STATUS; and write down the names of binlogs or their indexes. mysqldump can do that for you.

This guide assumes the reader is familiar with using the -u and -p switches to access the root MySQL user when running the mysql and mysqldump commands, and that the reader is able to use sudo to elevate their privileges appropriately.

MASTER

1. Create a user for replication:
Bash Script:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave.domain' IDENTIFIED BY 'password';
mysql> FLUSH HOSTS;
mysql> FLUSH PRIVILEGES;

2. Configure MySQL binary-logging:

sudo nano -w /etc/mysql/conf.d/master.cnf
Plain-Text:
[mysqld]
bind-address=0.0.0.0
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
sync_binlog=1
innodb_flush_log_at_trx_commit=1

3. Restart MySQL server:
Bash Script:
sudo /etc/init.d/mysql restart

4. Take a snapshot of the MASTER database, flushing logs and saving binlog position, locking tables only for the duration of the dump, and doing so in a single transaction:
Bash Script:
shell> mysqldump -u root -p --databases db1 db2 --master-data --flush-logs --single-transaction > snap.db

# Transfer the dump to the SLAVE
shell> tar zcvf snap.tgz snap.db
shell> sftp user@slave.domain
put snap.tgz
exit

SLAVE

1. Configure and restart MySQL:

sudo nano -w /etc/mysql/conf.d/slave.cnf
Plain-Text:
[mysqld]
server-id=2
replicate-do-db=db1
replicate-do-db=db2
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test

2. Restart MySQL server:
Bash Script:
shell> sudo /etc/init.d/mysql restart

3. Finish SLAVE configuration and import database:
Bash Script:
# Extract snap.db from gzipped tarball
shell> tar zxvf snap.tgz

# Finalise config, import data, start slave operations
shell> mysql -u root
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO
MASTER_HOST='master.domain',
MASTER_USER='repl',
MASTER_PASSWORD='password';
mysql> SOURCE snap.db;
mysql> SLAVE START;

You're done.
Originally posted: 11th of Sep 2009, 3:19 pm
Date of last edit: 11th of Sep 2009, 5:32 pm
Last edited by: Mr.Brownstone
1 pages: [ 1 ] [ View newest post ] Post reply.
 
 » Home-page › Forum › Announc ... and News › Configure MySQL replication with existing data Back to Top «