martes, 30 de octubre de 2012

MySQL Replication

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:

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:

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

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.

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:

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.

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

From now on I add the use of 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 file and the account should be used only for slaves connecting to this master.

Create User

Go to "user and privileges" on MySQL Workbench and create a new user with the "Add Account" button.

In the "Login" section set the "Login Name" (replicador) in my case, then de ip of the Slave server (if you don't want to limit the user to a specific server then use "%" instead the IP). Finally set the password for the account.

Then go to "Administrative Roles" Section and select de recently created user and from the middle panel chose "ReplicationAdmin" and "Custom" role:

Finally move from "Server Access Management" to "Schema Privileges".

Choose the recently created user (replicador in my case) and push the Add Entry button. Select "Selected host" and write the Slave IP (or choose "Any Host") and the Schema that you will replicate (or leave "Any Schema".

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.

If the master has been running previously without binary logging enabled, the log file name and position values displayed by SHOW MASTER STATUS or mysqldump --master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ('') and 4.

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.

4- 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 /private/var/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 /private/var/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:

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

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


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_log_file='<binary log file name>',

<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 and and enter the correct data:

$ cd /private/var/mysql
$ sudo vi

$ sudo vi


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 \G;


If "Slave_IO_Running" and "SLAVE_SQL_Running" say Yes, then you are OK.

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.

1 comentario:

  1. You made my day! this was the first task assigned on my day and thanks for the wonderful share. Informatica Online Training


Enabling File Sharing Applications like BitTorrent and Emule

This page explains how to configure Comodo Firewall for file sharing applications like Shareaza/Emule and BitTorrent/UTor...