Posts tagged master
Server and Backup Layout
Dec 22nd
Both the master and slave servers reside on a host machine. Clearly the servers are virtual machines. Its strategic to use virtual machines because the host machine remains useful for a variety of other tasks. The virtual server compartmentalize the functions to a discretely backupable file. In my particular arrangement the master server is synchronized with the slave server. The slave server has the same applications installed as the master. A fully functional apache web server with php and mysql support. The mysql database is synchronized as a replicate in real time, and rsync is used to sync the apache web root. In the rsync function I skip the wordpress and wiki config files, in which I reference the mysql server on the slave machine. Doing so allows server2 to be a drop in replacement for the master just by switching the port forwarding settings in the router. I would have to reconfigure my.cnf as the master in such a transplanation. The mail server has spamassassin and clamav installed on master and slave and /var/mail are synchronized to keep a relatively real time backup.
Then once a day the slave server is backed up. Vboxtool handled this seamlessly. Vboxtool stops the virtual machine, transfers it, and automatically restarts it. The initial backup is done to backup1. Then rsync is used to backup the home folder on my host system. I also use the computer for my own personal things, which are effectively backed up to backup1. Then, subsequently, the host initiates an rsync of backup1 to backup2, then to backup3. I have a rsync duplicate of backup1 on backup2 and backup3.
The host also is a MythTV server, and backup3 is the client that is subsequently connected to the TV. I have my various hosts relatively diversified and multitasked.
Get Data from Master – Not Working As Expected
Dec 21st
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.
Mysql Database Replication
Dec 20th
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…
