Re: [gentoo-user] Master - Slave MySQL Database Server

[kashani]

Kaushal Shriyan wrote:
> hi
>
> is this a correct documentation
> *http://howtoforge.com/mysql_master_master_replication* for Master Slave
> Replication and is there a test case to test this setup
>
> Thanks and Regards
>
> Kaushal

That how-to is passable, but leaves out a number is considerations.

1. Install Mysql on the master and slave. Make sure the slave version is 
the same or NEWER than the master. Master/Slave will break if the master 
is running a later version than the slave. In the future you will always 
update you slave first.

2. Get your master running and get your my.cnf setup. If you using 
innodb you'll need to increase memory settings for it and possible tweak 
your ibdata log file sizes. Do this first before even thinking about the 
slave.

3. add replication user to master.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'db02.yourdomain.com' 
IDENTIFIED BY 'slavepass';

4. Make your slave config exactly the same as the master with two 
exceptions. You can use smaller memory if you must, but do not change 
the ibdata and iblog file sizes unless you're going to import from a 
mysqldump.

master
# REPLICATION ===================================================
log-bin                         = /var/lib/mysql/db01-bin
expire_logs_days                = 7
server-id                       = 101

slave
# REPLICATION ===================================================
log-bin                         = /var/lib/mysql/db02-bin
expire_logs_days                = 7
server-id                       = 201
skip-slave-start
read-only

5. Now shutdown your master cleanly and delete the logs. You should 
really, really be sure you shut down cleanly before deleting your bin-logs.
sudo /etc/init.d/mysql stop
cd /var/lib/mysql/
sudo rm -rf db01-bin.*
cd ../
sudo rsync -av mysql/ mysql-slave/
sudo /etc/init.d/mysql start

6. Copy your slave mysql dir over to the slave. Mysql should be down on 
the slave before doing this.
rsync -av /var/lib/mysql-slave/ user@db02:/var/lib/mysql-slave/
ssh db02
cd /var/lib/
sudo chown -R mysql: mysql-slave/
sudo rsync -av mysql-slave/ mysql/

This way you don't have to start from scratch you screw it up. Any you 
will screw it up at least once.

7. Start up the slave and tell it where to start.
sudo /etc/init.d/mysql start
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='db01.yourdomain.com', MASTER_USER='repl', 
MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='db01-bin.000001', 
MASTER_LOG_POS=4;
start slave;
show slave status;

The starting log position is always 4 when Mysql starts up fresh with no 
logs, which is why we deleted them. Plus why copy around a lot of 1GB 
log files when you rsync. If you have the option to shut your master 
down, it's a nice short cut to avoid looking up the log position when 
you dump and what not. Also rsync is much faster than doing a 
master-dump mysqldump in most cases which makes for less production 
downtime.

kashani
-- 
gentoo-user@l.g.o mailing list