This guide is provided for users who want to test these in testing environment. Please use this guide at your own risk.
Part 4 will focus on setting up a secondary server as a backup server.
Configure MySQL replication
* For detailed information, please refer to MySQL manual
Create a replication user in primary MySQL server
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'password'
Assign Server ID and enable binary log in primary – phpmyadmin DB will not be replicated (you may want to exclude mysql DB depending on your need)
emacs /etc/mysql/conf.d/my.cnf
Example file content
[mysqld] server-id=1 log-bin=mysql-bin expire-logs-days=0 binlog-ignore-db=phpmyadmin innodb-flush-log-at-trx-commit=1 sync-binlog=1 log-slave-updates
Assign Server ID and insert master connection information in secondary
emacs /etc/mysql/conf.d/my.cnf
Example file content
[mysqld] server-id=2 master-host=192.168.0.10 master-user=replication master-password=password
Lock tables; in primary
mysql> flush tables with read lock;
Get master status information in primary
mysql> show master status;
Get all databases dump from primary
mysqldump -u root -p --all-databases --lock-all-tables --master-data > alldb.sql
Unlock tables in primary
mysql> unlock tables;
Login to secondary server
Copy dumped file from primary to secondary
scp 192.168.0.10:/root/alldb.sql .
Import dumped file to mysql in secondary
mysql -u root -p < alldb.sql
Start slave;
mysql> start slave;
Notes. If you are replicating mysql database which contains user table, make sure it does not break any application.
1. mysqladmin user: /etc/mysql/debian.cnf
2. phpmyadmin controluser: /etc/phpmyadmin/config-db.php
Configure rsync daemon
emacs /etc/default/rsync
Change RSYNC_ENABLE=false to true
RSYNC_ENABLE=true
Create rsyncd.conf file
[root] path = / comment = Backup Purpose uid = root gid = root read only = true auth users = backup secrets file = /etc/rsyncd.secret
Create rsyncd.secret file
user:password
Make sure its permission is set to 600, otherwise, it may not work
chmod 600 /etc/rsyncd.secret
Start rsyncd
/etc/init.d/rsync start