Support Wissensdatenbank

Set up Mysql replication server

Here is a small example of how to set up a replication server. In this example, we want to replicate the DNS servers that are managed via PowerDNS.

The status of a mysql replication can be called up using “SHOW SLAVE STATUS \G”.
Slave_IO_Running and Slave_SQL_Running must be set to “Yes” if replication is not possible.

Server1 is the master DNS server.

# On the master server (Server1):

1. The “server-id” 1 is entered in the file /etc/mysql/my.cnf.
2. In the file /etc/mysql/my.cnf log-bin is set to “mysql-bin”.
3. # systemctl restart mysqld
4. Create Replication User:

   CREATE USER 'repl'@'%' IDENTIFIED BY 'CzIkcN3vReD'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; FLUSH PRIVILEGES;

5. Then execute the following commands in MySQL. IMPORTANT: Please write down the issue:

6. FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;
   

    

7.  Download the following file via FTP

   mysqldump admin_powerdns > /root/pdns.sql

    

8. Execute the following command in MYSQL

   unlock tables;

    

# On the new DNS server

1.  Upload the file pdns.sql to /root

2. mysql < /root/pdns.sql

    

3. Any server-id is entered in the file /etc/mysql/my.cnf, WHICH IS NOT USED BY ANOTHER SERVER.
4. Then in MySql:

   CHANGE MASTER TO
   MASTER_HOST='1.1.1.1',
   MASTER_USER='repl',
   MASTER_PASSWORD='password',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='mysql-bin.000002',             // Gemäss Ausgabe von (SHOW MASTER STATUS) auf Server1
   MASTER_LOG_POS=326,                                       // Gemäss Ausgabe von (SHOW MASTER STATUS) auf Server1
   MASTER_CONNECT_RETRY=10;
   START SLAVE;
   SHOW SLAVE STATUS;