title: “Debian 7.0 MySQL 5.6 Master / Master Replication Setup”
date: 2014-03-04T09:56:37
slug: mysql-master-master-replication-setup
Install MySQL dependency (kernel asynchronous I/O access library)
apt-get install libaio1
Download a MySQL Debian package from the official site.
On 32-bit system:
wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.17-debian6.0-i686.deb
On 64-bit system:
wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.17-debian6.0-x86\_64.deb
Install the downloaded MySQL package.
dpkg -i mysql-5.6.17-debian6.0-x86\_64.deb
Add the MySQL bin directory to the PATH variable system-wide.
sh -c 'echo "PATH=$PATH:/opt/mysql/server-5.6/bin" >> /etc/profile'
source /etc/profile
Open the MySQL config file with a text editor, and update the following entries.
Copy the MySQL startup script to /etc/init.d and install the script into the boot sequence, so that MySQL server starts up automatically upon boot.
cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql
update-rc.d mysql defaults
Create a symbolic link.
ln -s /opt/mysql/server-5.6/bin/mysqld /usr/sbin/mysqld
Create User mysql
useradd mysql
Install initial DB
/opt/mysql/server-5.6/scripts/mysql\_install\_db --user=mysql --basedir=/opt/mysql/server-5.6/ --datadir=/opt/mysql/server-5.6/data/
MySQL Server starten
/etc/init.d/mysql start
set root password
/opt/mysql/server-5.6//bin/mysqladmin -u root password 'new-password'
/opt/mysql/server-5.6//bin/mysqladmin -u root -h localhost password 'new-password'
create log directory for mysql
mkdir /var/log/mysql
chown mysql /var/log/mysql
Server1: my.cnf
server-id = 1
binlog-ignore-db = mysql
replicate-ignore-db = mysql
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
expire\_logs\_days = 10
max\_binlog\_size = 500M
log-error = /var/log/mysql/error.log
log-bin= /var/log/mysql/mysql-bin.log
MySQL Server restart
/etc/init.d/mysql restart
Replication User auf server1 anlegen
mysql -uroot -p
GRANT REPLICATION SLAVE ON \*.\* TO 'replication'@'%' IDENTIFIED BY 'PASSWORD';
FLUSH PRIVILEGES;
Server2: my.cnf
server-id = 2
binlog-ignore-db = mysql
replicate-ignore-db = mysql
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
expire\_logs\_days = 10
max\_binlog\_size = 500M
log-error = /var/log/mysql/error.log
log-bin= /var/log/mysql/mysql-bin.log
relay-log=mysqld-relay-bin
Datenbank Dump auf server1 erzeugen und auf server2 kopieren:
server1:mysqldump -p --master-data --all-databases --result-file=dbdata1.sql
server1:scp dbdata1.sql root@server2:/tmp
MySQL Community Server wie oben beschrieben auf server2 einrichten
Datenload auf server2
server2:cd /tmp
server2:mysql -uroot -p
source dbdata1.sql;
CHANGE MASTER TO MASTER\_HOST='server1', MASTER\_USER='replication', MASTER\_PASSWORD='PASSWORD';
start slave;
show slave status\G
SHOW MASTER STATUS\G;
Folgendes Outputformat sollte angezeigt werden:
File: <strong>db2-bin.000003</strong>
Position: <strong>107</strong>
Binlog\_Do\_DB:
Binlog\_Ignore\_DB: mysql
Replication User anlegen und Slave auf server1 starten (Platzhalter XX mit den Werten von oben ersetzen):
server1:mysql -uroot -p
CHANGE MASTER TO MASTER\_HOST='server2', MASTER\_USER='replication', MASTER\_PASSWORD='PASSWORD', MASTER\_LOG\_FILE='<strong>XX</strong>', MASTER\_LOG\_POS=<strong>XX</strong>;
Start slave;
show slave status\G
Datenbank auf server1 unlocken:
UNLOCK TABLES;
Ändern eines MAster Hosts
CHANGE MASTER TO MASTER\_HOST='server1|2', MASTER\_USER='replication', MASTER\_PASSWORD='PASSWORD';