mysql master-slave-replication 1

In a master-slave replication on the slave are the databases of the master 1:1 available. Changes performerd on the master are immediately available on the slave (as long as you don´t user master_delay). Thus, it is not a true backup solution.

Master setup:
Change the section [mysqld] in /etc/my.cnf. Make sure to comment “skip-networking”:

#skip-networking
#some additional settings for the replication:
server-id = 1
log-bin = mysql-bin
max_binlog_size = 500M
binlog_format = mixed
sync_binlog = 1
expire_logs_days = 5
relay-log = slave-relay.log
relay-log-index = slave-relay-log.index


To ignore the database nagios during the replication, use
binlog-ignore-db = nagios

To skip single tables, you can set
binlog-ignore-table = DATENBANK.TABELLE verwendet.

Create the mysql slave-User:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user01'@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

Now restart mysql; the master is ready for replication and the slave can connect as slave_user01.

Next we setup the slave:

#skip-networking
server-id = 2
expire_logs_days = 5
max_binlog_size = 500M
# settings for the master
master-connect-retry = 60
report-host = sql-slave.mydomain.de

Now export the databases in the master
mysqldump --add-drop-table --master-data --quick --all-databases > dump.sql

send them using i.e. scp to the slave-server:
scp dump.sql root@slave.mydomain.de:/

Import the dump on the slave. Because of –master-data all settings for the replication should be automatically set to the right values. Before importing the dump you must stop the slave:
stop slave;

Import by simple running mysql < dump.sql

next
start slave;

The success you see in through mysql
show slave status \G

If the dump using --master-data doesn´t work as expected, you must set the log-values manually. To make sure the database are in sync, create a new dump.
Log in to master-sql und block the write-access and mark the relevant values:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;


*************************** 1. row ***************************
File: mysql-bin.000153
Position: 506858058

Create the dump in a new shell as described above. Then unlock the databases
UNLOCK TABLES;

Import the dump on the slave and then change the master-values (replace the values with your own).

CHANGE MASTER TO MASTER_HOST='master.mydomain.de', MASTER_USER='slave_user02', MASTER_PASSWORD='some_password',

MASTER_LOG_FILE='mysql-bin.000153, MASTER_LOG_POS=506858058;

Type start slave;. The slave should now be running. Check it out with show slave status \G.