Category Archives: MySQL

Bei MySQL Umlaut Problemen

title: “Bei MySQL Umlaut Problemen”
date: 2014-09-09T19:57:20
slug: bei-mysql-umlaut-problemen

Folgender Eintrag in die my.cnf:

init-connect='SET NAMES utf8'
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8\_unicode\_ci

Debian 7.0 MySQL 5.6 Master / Master Replication Setup

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';