Mysql-master-slave-replication

mysql-master-slave-replication

1)on the master and the slave a new "replication" user has to be added

mysql> create user repuser@kurs007 identified by 'xxxxx';
Query OK, 0 rows affected (0.00 sec)

2) set some rights

mysql> create user repuser@kurs007 identified by 'xxxxx';
Query OK, 0 rows affected (0.00 sec)

now we have to edit the my.ini on the master and the slave

master:

server_id=1

(there are loads of options, it is also possible to encrypt the connection with ssl.)

slave:

server-id=2
master-host=<ip_address or fqdn>
master-port=3306
master-user=repuer #username
master-passwort=xxxxx #can be given here, or later, if mysqld is started
master-connect-retry=60 #in seconds

then we start the master, and check with the "show master status" command, which bin-log file is acutally used:

mysql> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| KURS006-bin.000006 |      106 | testdbsm     | mysql            |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

then the slave has to be started and execute following command:

change master to
master_host='<hostname>',
master_user='<repuser>',
master_password='
master_log_file='KURS006-bin.000006'
<pre>

test, if connection exists
<pre>
mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host           | db   | Command | Time | State | Info             |
+----+------+----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:1224 | NULL | Sleep   |    0 |       | NULL             |
|  3 | root | localhost:1226 | NULL | Query   |    0 | NULL  | show processlist |
+----+------+----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

if it works you should see a funny messages like this:

mysql> show processlist;
| 12 | repuser | KURS007:1394   | NULL     | Binlog Dump |    9 | Has sent all binlog to slave; waiting for binlog to be updated