Slave Server – Backup the Backup – More Uptime

I’m almost at the point of having a completely duplicated slave server. Currently mysql database replication is working. I have apache php5 and mysql fully working. The server synchronizes the apache root directory, with the server, every minute. Therefore for all practical purposes any file changes on the master are propogated to the slave. The mysql database is propogated in real time. I installed dovecot, sendmail, spamassassin, and clamav therefore the email environment is setup. I’m going to sync the entire /etc directory.

I’m stuck, for a little, on the users inboxes. I’m trying to rsync /var/mail but I think some permissions are missing. After I get the /var/mail directory to synchronize, as per crontab, the systems will be fully synchronized.

Now when server2 is backed up using vboxtool the main server will remain completely online! This will save approximately 5 minutes per day, therefore I will be saving 30.42 hours of downtime per year. By backing up the server as such I will be adding 30.42 hours of uptime; a rather big number.

Server and Backup Layout »« Get Data from Master – Not Working As Expected

Get Data from Master – Not Working As Expected

While doing mysql database replication, to a slave server, I gave up trying to get the data directly from the master. I just copied the master database manually and things worked out smoothly. First dump the database from the master:

mysqldump -u root -p --opt exampledb > exampledb.sql

Clearly replace exampledb with the database that you want to dump. The command will also primpt you for the root mysql password. Then scp the sql file to the slave server:

scp exampledb.sql server2:exampledb.sql

Notice that the hostname of my slave server is conveniently server2. The command will also assume that the user is the same username that you are logged in with on the master.

On the slave server create a database with the same name as the master. Login to mysql:

mysql -u root -p

Issue the command to create the database:

CREATE DATABASE exampledb;

Then dump the sql file to the slave database:

mysql -u root -p exampledb < exampledb.sql

The command will dump exampledb.sql to the newly create exampledb database. Then login to mysql on the slave:

mysql -u root -p

Issue:

STOP SLAVE;
RESET SLAVE;
QUIT;

As per notes in my previous post issue:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='replicate', MASTER_PASSWORD='replicate', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

As states in the previous post the log file and the master log pos are determined by running on the master mysql server in a mysql shell:

SHOW MASTER STATUS;

Now log back into the slave mysql shell. Issue:

START SLAVE;

Thats should do it.

Slave Server – Backup the Backup – More Uptime »« Mysql Database Replication

Mysql Database Replication

Howtoforge.com does it well, but it took me some time, and in particular I feel one command was left out. Start by removing the bind address in /etc/mysql/my.cnf:

#skip-networking
#bind-address            = 127.0.0.1

Then add the following to /etc/mysql/my.cnf. Particularly to the mysqld section!!:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1

Exampledb is the database that you intend to replicate. Server-id should be 1 being that we are dealing with the master server. Make sure that you have the log directory /var/log/mysql. On the server login as root:

mysql -u root -p

Enter the password you configure when you setup mysql. If you don’t know the password you can reset it by skipping the grant tables:

sudo /etc/init.d/mysql stop
mysql --skip-grant-tables

Then login as root:

mysql -u root

Then change the root password:

UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';

Then restart mysql:

sudo /etc/init.d/mysql restart

Then login as root:

mysql -u root -p

The latter, resetting the root password, was a slight digression that may be helpful. Now add a new user with replication privileges:

GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' IDENTIFIED BY 'replicate';

The previous command will create a user replicate with password replicate. Your slave server will connect to the master as user replicate.

Then flush privileges:

FLUSH PRIVILEGES;
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
UNLOCK TABLES;
quit;

Show master status displays information about the log file mysql generates. It will give you the log position which is necessary to synchronize the slave.

Now onto the salve. Login to the slave mysql server:

mysql -u root -p

Create a database of the same name that is on the master:

CREATE DATABASE exampledb;
quit;

Edit the slave’s /etc/mysql/my.cnf file, add:

server-id=2
master-host=192.168.0.100
master-user=replicate
master-password=replicate
master-connect-retry=60
replicate-do-db=exampledb

Set the master host ip address to that of the master; kinda logical, don’t ya think… Log back into mysql on the slave:

Login to mysql:

mysql -u root -p

Issue the following:

STOP SLAVE;
RESET SLAVE;
LOAD DATA FROM MASTER;


The following will set the slave accordingly:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='replicate', MASTER_PASSWORD='replicate', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

Make sure the master log file is what is printer on the slave when you issue master status. The same goes for the position of the log!!!!! If you don’t have that screen up anymore issue on the master mysql server:

SHOW MASTER STATUS;

Then on the slave issue:

START SLAVE;

Quit mysql and your good to go…. Give me an email and I’ll help you out…

Get Data from Master – Not Working As Expected »« Hostnames – Strategic and Secure Naming