Build Ubuntu Server 9.10 64 bit All-in-One LAMP server – Linux Apache MySQL PHP Subversion Trac and Samba – Part 4

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

Leave a Reply

Your email address will not be published. Required fields are marked *