Software development, photography, jokes, ....

Sites by me

 
tas-logoTransportation Administration System
snoezelkussen-logo-kleinstSnoezelkussens voor verstandelijk gehandicapten
ikzoekeenbegeleider-logoBegeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
logo 50x50Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
nettylogo2Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen
Professionele opvang bij Gastouderbureau
Kind-Zijn
Salarisadministratie en belastingadvies bij
De Zaak Loont
Zutphense Bomenstichting

Hosting Favorites

 
ANU Internet Services
XelMedia .internet Services
register.com

Blogroll

 
Bomenstichting
LassoSoft
MacFreak
Quality that computes
The Economy of Motion
Wheel 2.0
IntrAktv



Website Hosting bij Xel Media

Marc's Place


 

 Setup MySQL Replication

  On Mac OS X Server
Thanks go to Bob Vos for helping sorting this out.


1.  Introduction


We setup two MySQL instances for use as slaves for one master. Read this on how to setup multiple MySQL instances. The workflow presented below is for one slave instance, but is the same for every instance - only the config file and port are different. We divided all the databases into two groups, those that belong together and those that are independent. Each slave replicates one group.

Let’s start!

2. Stop all interfering processes


Stop as much interfaces as possible that modify data in the MySQL databases you are going to replicate: external servers, JDBC applications, Lasso, PHP, etc..

Make a note of every service you shut down so you will not forget to turn it back on again afterwards.

3. Setup the Master


MySQL Workbench

Start a remote desktop session with the remote server and start MySQL Workbench. Click Manage Security in the bottom right corner:

FCSPR4mwb1a
and select the server instance you are going to use as the master, which is probably localhost, because you cannot use MySQL Workbench to edit a configuration file on a remote server:

FCSPR4mwb2a-1
Click Options File under the Configuration-section in the side bar:

FCSPR1mwb3a

Server ID

Select the Replication-pane and choose a unique Server ID. The ID is needed by the replication server instance. The ID must be a number between 1 and whatever. In our setup we used the number 14.

FCSPR4serverid

Binary Log

The only way a slave can replicate what is happening on the master, is to read out the log file on the master. This log file is the binary log, which is switched off by default.

Select the Log Files-pane. Check the log-bin option and enter the name for the log file. Name it mysql-bin or so:

FCSPR4logbin

Binary Log Expiration

A bit further down is another option to switch on: Expire Log Days. This option makes MySQL delete older bin-log files.

I set it to 30 days. If I would choose a shorter period, it could mean that when the slave fails while I am on vacation and are unable to repair the situation in due time, I have to start all over again, because the bin-logs I would need are purged!

Concurrent Insert

The last option for the master is concurrent_insert. Select the MyISAM-pane and set its value to 2 - ALWAYS.
--> http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html

Apply all changes and close MySQL Workbench. Restart the MySQL instance.

From now on I used Navicat and Terminal, all on the hosting server itself, to manage the MySQL server instance(s), because the MySQL Workbench has its limitations - you cannot configure everything with it.

Replication User

The next step is to create a user with replication permissions. Do not use an existing account because the account details will be stored in plain text in the master.info file and the account should be used only for slaves connecting to this master.

Create User

Start Navicat, and if you have not already done so, add a new connection to the master database server. Double-click the connection, then click the user-icon in the toolbar to list all users:

FCSPR4user

To add the new user, click the Add User-button in the toolbar of the users window. We named the user replicator and gave it a hard-to-guess password.

FCSPR4adduser

If you already know the IP-address or domain name of your slave server, enter that in the Host-field. If not, you can use a wildcard (%) character.
For example, enter the first 3 digits of the network the slave will be located in and add a .% to allow for any IP-address originating from that network, like 80.% or if you know more digits, add them to narrow down the possible clients: 80.187.%.

When you know the complete IP-address or domain name, you should come back here later and change this into the complete IP-address of the slave.

Permissions

When the replicator-user has been created, it shows up in the list of users. Select it and create global preferences setting s for this user and select the following privileges: File and Replication Slave:

FCSPR4privs

Save the settings and if you have not restarted MySQL since you modified the options file, do that now.

Binlog and position

With the binary log switched on, the slave must know where to pick up the transactions and start replicating. You can get the current position of the binlog as follows, in Terminal:

$ mysql -u root -p

Enter the correct password at the prompt and then enter the following command:

mysql> flush tables with read lock;

This will flush all tables and block write statements.
Important: Leave this session open and start a new Terminal session.

In the new session, log in to MySQL too and enter the following statement:

mysql> show master status;

This outputs a table that looks something like this:
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000161 | 710177030 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0,00 sec)
Both these columns contain values which are important for setting up the slave(s), so write these two values down because you need them later on.

Switch to the first Terminal session and enter the following commands:

mysql> unlock tables;
mysql> exit;

to release the lock and then exit the MySQL session. The master MySQL instance is now available for use again and while it runs, the bin-log position will of course change. Therefore, do not wait too long with setting up the slave.

Slave(s) Server(s) Setup


With the master configured and its bin-log position noted, it is time to configure and start the slave instance(s). Read here for a way to setup multiple MySQL instances on one server. If you do this, and I did, then the setup below is the same for both instances, apart from the mysql folder and port number. I have my second instance running: /private/var/mysql2 and port 3307.

Transfer Initial Data

First we make an exact copy of the master's databases we want to replicate onto the slave, so both master and slave initially have the same data.

Transfer the master's databases you want to replicate to the slave instance. You can do this in various ways:
  1. Using mysqldump and zip + ftp
  2. Zip the database itself + ftp (you might need to repair the tables after unzipping)
  3. Use Navicat (not good for tables with millions of records or blob data)
I used option 2, like this, in Terminal:

$ cd /var/mysql/ (or /var/lib/mysql/)
$ sudo zip -r ~/downloads/[database].zip [database]

Do this for each database that you want replicated. Then FTP all zip's to the slave server, also in your downloads folder for example.

Start a remote desktop or SSH session with the remote server and there, in Terminal, enter the following commands:

$ cd /var/mysql (or /var/lib/mysql/)
$ sudo unzip ~/downloads/[database].zip
$ sudo chown -R _mysql:admin [database]

For the above chown, check first with ls -l if _mysql:admin are the right owners. Then do this for each unzipped database.

Next, start Navicat and now you should see your new databases in the slave-connection. If not, you probably forgot to either do a Refresh Connection or the chown-command.

If you can access the tables and view data, good! If not, right click the table and choose Maintain->Repair Table->Quick or ->Extended and then try again.

Configure MySQL

If not still open, start a remote desktop session with the slave server and start MySQL Workbench there. Click Manage Security in the bottom right corner:

FCSPR1mwb1
and from the popup-window, select the server instance you are going to use as the slave, which is probably localhost:

FCSPR1sm1
Click Options File under the Configuration-section in the side bar.

FCSPR1mwb2

Logging

Select the Log Files-pane. Switch on the option log-bin by entering a name for the binary log, for example, mysql-bin.

Scroll to Advanced log options and set expire_logs_days to 7.

Replication settings

Select the Replication-pane.

As with the master instance, here too you need to set a unique server ID. Check the option server-id and enter a unique number. I set it to 11.

In General slave, switch log-slave-updates on and set slave_transaction_retries to 10.

In Slave replication objects, switch on replicate-do-db and enter all database names you want replicated, separated by a semicolon and no spaces before or after the semicolon! And no semicolon after the last entry. For example, customers;orders;orderlines

In Slave Identification, switch report-host on and enter the IP-address or the hostname of the slave. I prefer to use IP-addresses because then you do not have to worry about slow or unreachable DNS-servers, which, in my experience, quite often happens inside corporate networks. If your slave instance runs on a non-standard port, switch on report-port and enter the port number the slave listens to. I chose to specify 3306 explicitly, instead of relying on the defaults.

And last, in Relay Log, switch on relay_log_purge.

Save the settings, quit the MySQL Workbench, open a Terminal session and log into a MySQL slave-instance with the following command:

$ mysql -u root -p --protocol=TCP [--port=port of another instance]

Enter the following commands, but replace the text in italics with the obtained from the master:

$ change master to
master_host='<IP-address or domain name>',
master_user='replicator',
master_password='<password>',
master_port=3306,
master_log_file='<binary log file name>',
master_log_pos=<position>;


Replace <binary log file name> and <position> with the values obtained while setting up the master.

When you get a Query ok message, you're good. If not, make sure there's a working connection between the two servers.

If you previously have configured an instance as a slave (for testing purposes, for example) and are reconfiguring the instance, edit the files master.info and relay-log.info and enter the correct data:

$ cd /private/var/mysql
$ sudo vi master.info
14
mysql-bin.000162
391030594
192.168.1.200
replicator
password
3306
60
0







$ sudo vi relay-log.info
./FCSPR1-relay-bin.000038
4136037
mysql-bin.000162
391030594
4

$


Do not remove those empty lines inside these files!

Ready, Set, Go!


If the master is not already running normally, now is the time to release all locks and start it. Then, switch to the slave-server, start a Terminal session, log into MySQL and start the slave:

mysql> start slave;

The replication should now work. Check this by using the command:

mysql> show slave status;
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+----------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+----------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.1.200 | replicator | 3306 | 60 | mysql-bin.000162 | 392027903 | FCSPR1-relay-bin.000038 | 5133346 | mysql-bin.000162 | Yes | Yes | db1,db2,db3,db4,db5 | | | | | db5.REP% | 0 | | 0 | 392027903 | 5133346 | None | | 0 | No | | | | | | 0 |
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+----------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0,00 sec)


Check if Slave_IO_State has a sensible value, Slave_IO_running = yes and Slave_SQL_running = yes.

If you need to edit any settings in the slave's configuration at a later moment, make sure you stop your replication process first by using the command:

mysql> stop slave;

Tested: the setup even survives a sudo reboot -q on the master server. After that, you can follow the Seconds_Behind_Master counting down.



Note: if you’re on CentOS or other Linux flavor, also check out Marc Pope’s page on setting up replication.
© 1997- Marc Vos (and others) Contact Me