Home arrow Guides arrow Configure MySQL Replication in FreeBSD
Configure MySQL Replication in FreeBSD PDF
Tuesday, 26 April 2011
This tutorial shows you step by step configuration process for a Master/Slave MySQL Replication.

We will install FreeBSD and MySQL on two machines.
Let's say one of them is 10.0.0.76 and the other 10.0.0.77.

Step 1. Install FreeBSD and MySQL
---------------------------------------
We will install on both machines FreeBSD and MySQL. We'll also install Midnight commander, for ease of configuration which will install Perl that might be needed later.

After installing FreeBSD we will build from ports MySQL:

  cd /usr/ports/databases/mysql51-server
  make install clean

We will repeat this process for second server.


Step 2. Configure MySQL Master
---------------------------------------
On 10.0.0.76
(our master) we add in /etc/rc.conf the following line:

  mysql_enable="YES"

Then we start mysql server:

  /usr/local/etc/rc.d/mysql-server start

We add a root password for our MySQL server (please note that this password is different from FreeBSD's system root account, and is specific only to mysql):

  mysqladmin password mypass

(where mypass is our password)

To change root password on a mysql server use:

  mysqladmin -u root -p password newpass


If you get an error when issuing previous command: mysqladmin: Command not found. then probably you forgot to run rehash after you've installed mysql.

Now we will grant replicate mysql account to use replication on master:

  mysql -u root -p
(we input mypass)

and then at mysql client command prompt we run:

mysql>   grant replication slave, replication client on *.* to 'replicate'@'%' identified by 'mypass';

If we want the grant to work only for systems from our subnet, lets's say 10.0.0.0/24 we could issue instead:

mysql>   grant replication slave, replication client on *.* to 'replicate'@'10.0.0.0/255.255.255.0' identified by 'mypass';

Then we check if we have that grant with:

mysql> show grants for replicate;

We now must copy a my.cnf file to /usr/local/etc:

cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc/my.cnf

We will edit /usr/local/etc/my.cnf file and add there at section [mysqld] the following 2 lines:

# [mysqld]
log-bin
server-id=1

Then we restart MySQL Server:

/usr/local/etc/rc.d/mysql-server restart

After that master is configured. To check the status of master we use mysql client and we run:

  mysql> show master status;

We will get something like this:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Step 3. Configure MySQL Slave
-----------------------------------
We repeat commands from Step 2 until the part where we edit my.cnf file.

So we will also add here the same grant rights:

  grant replication slave, replication client on *.* to 'replicate'@'%' identified by 'mypass';


Then we edit /etc/my.cnf file:

  cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf


We edit /etc/my.cnf file and add at section [mysqld]:

#  [mysqld]
server-id=2
master-host=10.0.0.76
master-user=replicate
master-password=mypass
master-port=3306

Then we restart MySQL server on Slave:

  /usr/local/etc/rc.d/mysql-server restart


Step 4. Copy databases from master to slave
----------------------------------------------------
Dump databases with mysqldump on master and import them on slave. Then start the slave.


Problems that might appear
--------------------------------
a. Changing IP of master

If you change master or slave ip from my.cnf you might notice that when you start mysql on both master and slave systems slave will try to connect to the old IP of master. To solve this problem you must stop mysql on slave and then:
- delete master.info file
- delete relay-log.info
- delete any binary relay log   *relay-bin*

b. Solve an master/slave error
If for some reason slave is not in sync with master and querries are not executed on slave because of an error it is easy to solve the problem by skipping querries with error on slave:

mysql>   stop slave;
mysql>   set global sql_slave_skip_counter = 1;
mysql>   start slave;

If you have multiple errors you can use bigger values for counter depending on the number of querry with errors you have.


Replicating only some databases
-------------------------------------
It is possible to replicate only some databases from master. To do that you have to add in my.cnf the following lines that will ignore mysql database and will only replicate database1 and database2.

On master:
  binlog-do-db = database1
  binlog-do-db = database2
  binlog-ignore-db = mysql

On Slave:
  replicate-do-db = database1
  replicate-do-db = database2


MySQL Replication Tips
------------------------
If replication is not working look for a .err file usualy located in /var/db/mysql and you will probably identify the problem.

To check if replication is working on slave use:
mysql>  show slave status;

or(better formatted):
mysql>  show slave status\G;

If you use mysql in jail edit /etc/hosts and add there:
127.0.0.1  sqlhostname
so the mysql server will use hostname instead of 127.0.0.1 which is not working in jail environment.

If you put show-slave-auth-info in my.cnf file from master you will be able to see auth info from slaves.

Use command master reset; from mysql client shell to reset the master.

To clear log run flush master.

To see querries and other commands from mysql console use: show processlist;

To see InnoDB status run from mysql console: show innodb status; This is useful for benchmark/optimisation.


Update: Changes in MySQL 5.5, related to replication
------------------------------------------------------------------------------------

It seems as to MySQL 5.5 slave is not configured from my.cnf file. So all option added to my.cnf file for replication will return an error and MySQL slave will not start.

To configure replication in Slave node go to mysql prompt, stop slave (if is running):

  mysql>  stop slave;

then run the following MySQL statement:

MYSQL statement for conecting to a master
change master to   
  MASTER_HOST='master.example.com',   
  MASTER_USER='root',
  MASTER_PASSWORD='mypass',   
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master1-bin.001',
  MASTER_LOG_POS=1,
  MASTER_CONNECT_RETRY=60;
 ;

and then start the slave:

  mysql>  start slave;

To modify MASTER_CONNECT_RETRY variable:

  mysql>  stop slave;
  mysql> change master to MASTER_CONNECT_RETRY=700;
  mysql>  start slave;

Notes:
If you get Replication error 1045:  Last_IO_Errno: 1045, check your password and also check replication grants on master and slave. It might be an authentication problem.

Note2: If you want to completely disable replication run:
  mysql> stop slave;
  mysql> reset slave;

and if you are using MySQL 5.5 run instead:
  mysql> reset slave all;

Note3: When seting up the slave you must use values from master for log file and for position:
Run this on master:
  mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000021 |      641 | dev       | mysql            |
+------------------+----------+--------------+------------------+
Then, on slave run:
  mysql> change master to MASTER_LOG_FILE='mysql-bin.000021', MASTER_LOG_POS=641;

Note4: Before doing dump of the database on master to use it on slave, just run the following mysql command (to lock your tables):
  mysql> flush tables with read lock;
Then after you've exported the database, remove the lock with:
  mysql> unlock tables;

Note5: If you get the following error in /var/db/mysql/*.err file:
[ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master;
then, edit your my.cnf file and increase max_allowed_packet, which could be 1M by default, for example you could use:
  max_allowed_packet = 16M

Last Updated ( Wednesday, 15 February 2012 )
 
< Prev   Next >

Other BSD Systems

OpenBSD

Misc

Solaris

Polls

Best BSD firewall?