Basic mysql cluster replication (master-slave) created via puppet.
- OS: Scientific linux 6
- master vm: mammoth01.farm
- slave vm: mammoth02.farm
- create SL6 box using veewee-boxarium
- copy ssh_keys from ssh-gerwazy
vagrant up
ssh root@77.77.77.111 #mammoth01
ssh root@77.77.77.112 #mammoth02
vagrant destroy
- install mysql-server (
yum install mysql-server
) - set a password for mysql root user:
/usr/bin/mysqladmin -u root password "password"
- configure firewall (default port 3306)
log-bin=mysql-bin
server-id=1
and restart mysql master (/etc/init.d/mysqld restart
)
grant replication slave, replication client on *.* to 'repl'@'%' identified by "password"; flush provoleges;
SHOW MASTER STATUS;
Example output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | | |
+------------------+----------+--------------+------------------+
FLUSH TABLES WITH READ LOCK;
mysqldump --all-databases --master-data -p"password" > mysql_master.dmp
UNLOCK TABLES;
--master-data
automatically appends the CHANGE MASTER TO
statement required on the slave to start the replication process
- install mysql-server (
yum install mysql-server
) - set a password for mysql root user:
/usr/bin/mysqladmin -u root password "password"
- configure firewall (default port 3306)
server-id=2
log-bin=mysql-bin
report-host=77.77.77.112
master-host=77.77.77.111
master-user=repl
master-password=password
master-connect-retry=60
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
SLAVE STOP;
mysql -uroot -p"password" < mysql_master.dmp
SLAVE START;
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='77.77.77.111', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
SLAVE START;
on slave machine:
SHOW SLAVE STATUS\G
on master machine:
SHOW SLAVE HOSTS;
Problem: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log
Solution:
Slave: stop slave;
Master: flush logs
Master: show master status; #take note of the master log file and master log position
Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=YYY;
Slave: start slave;
- toplink: http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
- step by step: http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/
- step by step: http://www.codefutures.com/mysql-replication-howto/
- http://erlycoder.com/43/mysql-master-slave-and-master-master-replication-step-by-step-configuration-instructions-
- http://www.bitbull.ch/wiki/index.php/MySQL_Replication_HowTo
- http://xorl.wordpress.com/2011/03/13/how-to-mysql-masterslave-replication/
- http://studioshorts.com/blog/2010/03/mysql-master-slave-replication-on-centos-rhel/
- http://www.woblag.com/2012/03/setting-up-master-slave-replication-on.html
- look to file: manifests/mammoth-master.pp (demo_enabled)
- create new database
- show databases on slave and master
- insert some data
- select data on slave and master
Copyright 2012-2013, Kamil Wilas (wilas.pl)
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this work except in compliance with the License. You may obtain a copy of the License in the LICENSE file, or at:
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.