Slitaz project – Part 4 – Mysql replication

So this post will focus on creating a mysql master server and a slave replicating this master. Mysql replication is a good way to have realtime backups.

First let’s start with the master server. I will use the IP address 192.168.1 231 for the master and 192.168.1 232 for the slave.
Create a copy of our base and add a 1GB IDE disk drive to this. This disk will be where our database will be written to. (Still using the 512MB main disk seems wrong for me.)

Fire up our script, and reboot (as usual)

/home/base/ip.sh mysqlmaster 192.168.1 231 1
reboot

So now let’s add our disk. I noticed that in the starup script, Slitaz uses ‘/var/lib/mysql’ a lot. To avoid any problems later on, I will just mount the disk to the place where the mysql database is kept.

fdisk /dev/hdb

Press: o, n, p, 1, enter, enter, w

mkfs.ext2 -b 4096 /dev/hdb1
mkdir /var/lib/mysql
nano /etc/fstab
dev/hdb1	/var/lib/mysql	ext2	defaults	0	0

Now our storage is prepped, it’s time to install the mysql server. On the master server I will install the package ‘php-mysqli’ too because it is required for phpmyadmin. (Note: also agree to any additional required packages)

tazpkg get-install mysql
tazpkg get-install php-mysqli 
tazpkg clean-cache

Now let’s add it as a service (easy peasy stuff, you should be quite familiar with this by now.)

nano /etc/rcS.conf
RUN_DAEMONS="dbus hald slim firewall dropbear lighttpd mysql"

For our convenience, the default Slitaz installation comes with a configuration for machines with a low amount of memory. So I will be deleting the default configuration and using this configuration instead.

rm /etc/mysql/my.cnf
mv /etc/mysql/my-small.cnf /etc/mysql/my.cnf

Now before we can start mysql, the config file needs a little bit of tweaking. The ‘bind-address’ setting is used to allow external machines to make a connection. This is needed because we want our webnodes to connect to this database. Also the setting ‘log-bin’ needs to be enabled. This allows our master mysql server to keep logs for our slave.

nano /etc/mysql/my.cnf
[mysqld]
bind-address = 192.168.1.231
log-bin=mysql-bin

Now reboot the server. This will cause Slitaz/MySQL to generate the needed files for the MySQL database. These are generated the first time the service is started.

Now let’s login to our newly created server. Normally the password is left empty (just press ENTER).

mysql -u root -p

Now this server needs a few extra accounts: One account for our slave server which is located at 192.168.1.232, and should be restricted to that IP. One account for phpMyAdmin (called myadmin here). One account to distribute to our web servers so they can connect to the database (Optional: restricted to this subnet).

GRANT ALL ON *.* TO slave@'192.168.1.232' IDENTIFIED BY 'slave';
GRANT ALL ON *.* TO myadmin@'localhost' IDENTIFIED BY 'myadmin';
GRANT ALL ON *.* TO web@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'web';
exit

So now as promised, the installation of phpMyAdmin. I like this little piece of software, and because there is already a web server installed it’s easy to include. So just download it and unpack it to the admin domain.

cd /home/base
wget http://downloads.sourceforge.net/project/phpmyadmin/phpMyAdmin/3.5.1/phpMyAdmin-3.5.1-all-languages.tar.gz
tar xzvf /home/base/phpMyAdmin-3.5.1-all-languages.tar.gz
mkdir /var/domains/admin/phpMyAdmin
mv /home/base/phpMyAdmin-3.5.1-all-languages/* /var/domains/admin/phpMyAdmin
rm -rf /home/base/phpMyAdmin-3.5.1-all-languages
rm /home/base/phpMyAdmin-3.5.1-all-languages.tar.gz

So now let’s configure this instance. A configuration example can be found as ‘config.sample.inc.php’. We will use this to configure our phpMyAdmin.

mkdir /var/domains/admin/phpMyAdmin/config
cp /var/domains/admin/phpMyAdmin/config.sample.inc.php /var/domains/admin/phpMyAdmin/config/config.inc.php

Just edit the newly created config file and change the blowfisch secret. (Else phpMyAdmin will complain.)

nano /var/domains/admin/phpMyAdmin/config/config.inc.php
$cfg['blowfish_secret'] = 'slitazsecret';

Done, easy no? Now you can use the link ‘http://192.168.1.231:81/phpMyAdmin/‘ to access phpMyAdmin. Use the username/password combination: myadmin/myadmin.

Part one of our master server is done. Time to work on our slave. This slave will replicate all changes made in the master database. This is a great backup solution. If our master server fails the slave can be reconfigured to a master server. And bring the systems up and running again in no time.

Like with the master, we will continue from our base system and also add a 1GB IDE disk. I will be using the ip 192.168.1.232 for the slave.

/home/base/ip.sh mysqlslave 192.168.1 232 1
reboot
fdisk /dev/hdb

Press: o, n, p, 1, enter, enter, w

Also this disk needs to be mounted on ‘/var/lib/mysql’ like the master.

mkfs.ext2 -b 4096 /dev/hdb1
mkdir /var/lib/mysql
nano /etc/fstab
dev/hdb1	/var/lib/mysql	ext2	defaults	0	0

On our slave I will not install phpMyAdmin. (Installing this is optional, install instructions are described in the part of the mysqlmaster.)

tazpkg get-install mysql
tazpkg clean-cache
nano /etc/rcS.conf
RUN_DAEMONS="dbus hald slim firewall dropbear lighttpd mysql"

Now let’s use our configuration file for small memory servers.

rm /etc/mysql/my.cnf
mv /etc/mysql/my-small.cnf /etc/mysql/my.cnf 
 
nano /etc/mysql/my.cnf

Off course, this file also needs some changes. These include adding the slave options. The ‘server-id’ must be different and the server instance must know how to connect to the master server.

[mysqld]
bind-address = 192.168.1.232
server-id = 2
master-host=192.168.1.231
master-port=3306
master-user=slave
master-password=slave
master-connect-retry=60

Now let’s reboot this server too and let it generate all needed files.

Now the difficult part: creating a data snapshot. This is done to make the servers synchronize. First the master tables have to be locked, a data dump has to be made. Then this data dump needs to be uploaded to the slave. The slave needs to be started and all tables will need to be unlocked on the master database.

This requires two putty sessions to each machine. One on the master to lock the database and one to dump the database. One on the slave to stop the slave instance (and restart it) and one to restore the backup to the slave.

session1:mysqlmaster

mysql -u root -p
flush tables with read lock;

session2:mysqlmaster

mysqldump --all-databases --master-data > /var/domains/admin/dbdump.db

Now on our slave we need to reset the state of our slave to accept the data dump.
session1:mysqlslave

mysql -u root -p
stop slave;
reset slave;

Now let’s download the dump on on mysqlslave and dump it into the database.
session2:mysqlslave

wget -O /home/base/dbdump.db http://192.168.1.231:81/dbdump.db
mysql -u root -p < /home/base/dbdump.db

Now start our slave again in session1 (which is still connected to the mysql database.)
session1:mysqlslave

slave start;

Now unlock our tables again in the master.
session2:mysqlmaster

unlock tables;

And delete the database dumps on our slave server.

rm /home/base/dbdump.db

And on our master server.

rm /var/domains/admin/dbdump.db

All done, our tables should now perfectly synchronize. Open a MySQL Workbench on both databases and watch them synchronize! (I am not going to explain this program to you right now. Look it up if you don’t know how to use it.)

Finished!

And here are the files (contains mysqlmaster and mysqlslave): mysql.7z (20.7 MB)

Leave a Reply