How To Set Up Selective Master Slave Replication in MySQL

mysqlThere are a number of tutorials out there for setting up Replication in MySQL. However, I couldn’t find one that fully addressed setting up selective master-slave replication in MySQL.

By selective, I am referring to only having one or a few databases that are replicated from the master database to the slave database. Any other databases on the master server are not copied/replicated.

Master-slave replication for a MySQL database refers to having a secondary MySQL server where any changes made to the main database are replicated (copied) to the secondary MySQL database. It becomes a copy of the main database. This secondary database can be used as a “hot” backup database, or used to run queries against that you don’t want to run on the live database, or used to allow for backups to be made without affecting performance of the live database.

Tutorials out there now

There is a great tutorial for setting up Master Slave Replication for all databases. It is well documented.

Another tutorial that is quite good and is almost complete (with a few typos) is One database set up for master-slave replication.

Then there is the ancient HowToForge MySQL Replication tutorial. It is thorough, but is very out of date.

Each of these tutorials is missing one or more items, or is not clear on some steps, and you can run into issues when setting it up.

Gotchas

There are 4 gotchas when setting up MySQL Master-Slave replication:

  • 1. Using the setting “replicate-do-db” on the slave instance can cause issues with not all queries being replicated. Instead, I recommend using “replicate-wild-do-table” so that all queries (regardless of construct) will be replicated in all scenarios.
  • 2. Only using one “binlog-do-db” line for multiple databases will cause replication to fail. Instead, if replicating multiple databases, have multiple “binlog-do-db” lines, one for each database.
  • 3. Don’t put master settings in the my.cnf configuration file. These settings won’t work in MySQL 5.5 or higher.
  • 4. Using a second window / session correctly for the initial database dump file creation to prevent locking from expiring. Get this wrong and your slave instance will be corrupt.

Steps to set up MySQL master slave replication

ON THE MASTER DATABASE SERVER:

1. The first step is to set up the master database for replication. This can be done while the database server is running. You would edit the my.cnf file on the master database:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=DATABASE_NAME1
binlog-do-db=DATABASE_NAME2
server-id=1

where “DATABASE_NAME1″ and “DATABASE_NAME2″ are the names of the databases you plan to replicate.

2. Next, restart MySQL on the master server.

3. Then, on the master database, log into MySQL on the command line (mysql -p) and issue the following SQL queries:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

4. When you are ready to create the database dump of the master database(s), you will run the following commands. You *must* stay logged into this session, and do *not* quit or even issue another command:

USE DATABASE_NAME1;
FLUSH TABLES WITH READ LOCK;

If you have more than one database to replicate, you will need a separate window/session for each database that you stayed logged into. You would repeat these commands in each window for each database, staying logged in after issuing the commands.

5. In a second ssh window, you will log into MySQL again and run the following query and record the values:

SHOW MASTER STATUS;

It should look something like this (You want to record the File value and Position value):

mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------------+------------------+
| File             | Position  | Binlog_Do_DB       | Binlog_Ignore_DB |
+------------------+-----------+--------------------+------------------+
| mysql-bin.000013 | 250789445 | DB_NAME1,DB_NAME2  |                  |
+------------------+-----------+--------------------+------------------+
1 row in set (0.00 sec)

6. The next step is to dump the database(s) from the master server:

mysqldump -p --opt DATABASE_NAME1 >DATABASE_NAME1.sql
mysqldump -p --opt DATABASE_NAME2 >DATABASE_NAME2.sql

You will then want to transfer these files to the slave server, as you’ll use them to seed the slave databases later on.

7. Once you have the dump files, and you recorded the values of the master status, you can unlock the database by going back to the first window (and other window(s) for each database) that is still logged into MySQL and running:

UNLOCK TABLES;
quit;

ON THE SLAVE DATABASE SERVER:

1. Log into MySQL (mysql -p) and set up your databases:

CREATE DATABASE DATABASE_NAME1;
CREATE DATABASE DATABASE_NAME2;
quit;

2. In the my.cnf file on the slave server, add the following lines:

server-id=2
relay-log=/var/log/mysql/mysql-relay-bin.log
replicate-wild-do-table=DATABASE_NAME1.%
replicate-wild-do-table=DATABASE_NAME2.%

This will match any type of query run against the databases, and ensure they are fully replicated to the slave server.

3. Restart MySQL on the slave server.

4. Log back into MySQL (mysql -p) on the slave server and run the following queries. Make sure you stay logged into this session. You’ll need the values you recorded from the master database, as well as IP addresses and usernames/passwords:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='1.2.3.4', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.XX', MASTER_LOG_POS=XX;

Where “1.2.3.4″ is the IP of the master server, and “slave_user” and “password” are the username and password of the MySQL user for replication. The “XX” values in the query are those you recorded in step 5 on the master server.

5. In a second window, import the databases:

mysql -p DATABASE_NAME1

6. Back in the first window (that is still logged into MySQL), you may now run the following MySQL queries:

START SLAVE;
quit;

That’s it. The slave server is now running, and should be replicating with the master server.

How to verify the slave server is in sync with the master

This is not an easy thing to do manually. There is no simple command that tells you everything is in sync.

Fortunately, the good folks at Percona have a toolkit that makes this easy to verify. You install it on the master server (simple perl Makefile.PL, make, and make install).

Once installed, you use a separate database (that should also be replicated) to track the sync status. I called ours “percona” and set it up on the master with:

create database percona;
CREATE TABLE checksums (
db             char(64)     NOT NULL,
tbl            char(64)     NOT NULL,
chunk          int          NOT NULL,
chunk_time     float            NULL,
chunk_index    varchar(200)     NULL,
lower_boundary text             NULL,
upper_boundary text             NULL,
this_crc       char(40)     NOT NULL,
this_cnt       int          NOT NULL,
master_crc     char(40)         NULL,
master_cnt     int              NULL,
ts             timestamp    NOT NULL,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

I set this table up to be replicated along with our other databases.

Then, you simply run a command on the master server (via ssh, cron, etc…) to verify all is in sync:

pt-table-checksum --user=XXX --password=YYY --databases DATABASE_NAME1 --nocheck-replication-filters

This will give you an output and indicate if there are any differences between the databases. The column you are most concerned with is “DIFF”, which should be all zeroes if everything is in sync. You may see a number greater than zero in the “ERRORS” column. Many times this can be ignored depending on the details of the error.

—-

Hopefully this helps you set up selective master-slave replication in MySQL without running into corruption or missing queries / data on the slave instance. I’ve found this process to work quite well for the replication set ups we have put in place. I’m sure there are many other ways to skin the cat, but the “gotchas” I listed above are items to consider no matter which plan you implement.

ShopSite Shipping With ShipStation

Your store is online, you’re taking orders, but is shipping them taking too long?  We have a time-saver for you:

ShipStation - Unshipped Orders

Dashboard Showing Orders To Ship

LexiConn has recently released a module to integrate your ShopSite store with ShipStation, a hosted order management system which can print all your shipping labels and even notify your customers of their tracking numbers.

ShipStation - Ship Sample

Printing a Priority Mail Shipping Label

The combination of ShopSite and ShipStation is seamless and allows you to easily print your shipping labels using a variety of carriers.  It can even print your packing slips for you when it prints the labels.

All of your orders will be imported into a dashboard where you can change shipping methods, update customer’s addresses, and even split orders if you want to ship just part of an order.

ShipStation has a free 30 day trial available which you can sign up for here.  For more information about our module, or to have it installed on your LexiConn account, please view the details here.

Not a hosted client? Check out our easy, pain-free transfer process to have your ShopSite store and website moved over to LexiConn with no downtime.

Heartbleed – All LexiConn Servers Patched

heartbleed If you haven’t already seen or heard about Heartbleed, the large vulnerability that affected over half a million trusted websites, here is a synopsis, the status of our servers being patched, and my take on why the sky is NOT falling due to this issue.

LexiConn is Safe

Once this vulnerability was announced, we had all of our affected servers patched within a few hours. Note that the large majority of our servers were not vulnerable to this attack, as they run versions of the OpenSSL software that did not have the bug in them.

What is Heartbleed?

Heartbleed is a bug in the very popular OpenSSL cryptographic library used by many modern servers throughout the world. OpenSSL provides the backbone of the encryption used for SSL (i.e. secure) communications over the web.

The bug allows a would be attacker to access random “chunks” of memory from the server (64 Kb at a time). Over time, an attacker *could* get the secret key for the SSL security, and then use that key to go back and decrypt data they had collected.

The attack was discovered and published on April 7th. The severity of this exploit stems from a random attacker being able to request sensitive memory data without it triggering anything unusual in the server log files. The attacker does not need to be on the server, and it does not require a more complicated “man in the middle” attack vector.

How Real is this Threat?

The media sure likes to jump on a story like this. The “sky is falling” doomsday articles are a bit overblown.

That’s not to say this isn’t a big deal. It is. It needs to be taken seriously, and all internet providers should already be patched.

Once the exploit was released to the public (along with simple code anyone could run to take advantage of this bug), the threat became *VERY* real. Getting patched quickly was the best defense against Heartbleed.

However, here is my take on the odds of a hacker being able to find this vulnerability on his or her own, and then successfully use this information to exploit servers and data…

First, the hacker would have needed to be smart enough to find this vulnerability on their own. It took a team of researchers from a security company and Google to discover the flaw. It likely isn’t something the average hacker would have discovered on their own.

For the sake of argument, let’s say one of the top hackers out there found out about this flaw. They would have needed to keep it quiet (which is certainly possible), as no one else had heard about it before the team announced it a few days ago.

Next, they would have needed to launch a targeted attack against a site or group of sites that they wanted to try and compromise (and were running software vulnerable to this attack). This attack is not easy to exploit fully, as it takes time, skill, and patience to collect 64 Kb of random data, one connection at a time. Each fragment would need to be saved, and each one would need to be examined trying to obtain the secret private key that encrypts the data.

If this secret key were to be pieced back together, then the process of assembling these random fragments into usable chunks and then decrypting them begins. It’s not something you can just write a few lines of code for and hit the jackpot. It requires a great deal of resources and know-how to pull off.

If all of the above did happen, the hacker would probably want to target something big, like a bank or a large site like Yahoo. It’s highly unlikely they would target small business websites, as the effort expended would far outweigh the potential data they might recover.

As you can see, the odds of this being pulled off *BEFORE* the bug was announced to the public are probably quite low. In the neighborhood of being struck by lightning twice in the same spot on the same day kinda odds.

However, *AFTER* the bug was announced, the threat became much more real. With available code, the “script kiddies” could launch these attacks easily, and the potential for sensitive data to be found becomes quite high.

To reiterate, all LexiConn servers and accounts were fully patched against Heartbleed a few hours after the release was made public. The vast majority of our servers were not vulnerable to this attack at any time, so there was only a tiny chance it could have even been exploited in the past.

If you have any questions about this vulnerability as it relates to your account with us, just drop us an email, or give us a call.

ShopSite Tip: Order Notification Blocking May Affect You!

If your store is currently configured to send order notifications with the customer as the ‘From’ address (default setting), you may soon stop receiving these notifications.  This will apply to any merchant currently forwarding order notifications to GMail, Yahoo and other 3rd party mail servers.

This is true even if your store email address is at your domain.  Any action that would in turn forward that email off the server, would result in the message being marked as spam or rejected entirely.

Order notification settings can be verified under Commerce > Order System > E-Mail:

(click to enlarge)

(click to enlarge)

Why The Change?

Yahoo recently upgraded their DNS to include a signature that tells ISP’s and other mail servers an email from their domain is valid.  This signature, also known as a DMARC record, is only included on emails originating from Yahoo servers.  An order notification email originates on the server your store is hosted on and does not contain the required signature.  Email providers like GMail will see your hosting account as the origin for these Yahoo, AOL, GMail, and other customer email addresses and subsequently block the email from being delivered.

What Options Exist?

Luckily, for most, this is a simple fix.  By setting the ‘from:’ address on email order notifications to an address that matches your domain.  This way the emails originate from a domain that is expected, and should help with avoiding them being marked as spam.

To change the default setting, navigate to Commerce > Order System > E-Mail.  Next, select the option to use a specific email address, then enter one at your domain.

(click to enlarge)

(click to enlarge)

The only drawback is that you will not be able to directly reply to your customer from this order notification.  You would have to manually change the address.  It is worth it though if you forward your email.

It’s also *VERY* important that your store’s default email address is set to your domain, and not a GMail or Yahoo address. If not, customers may not get their receipts either.

Five Hidden Pitfalls Of Remotely Hosted Shopping Carts

pitfallsThere are two broad types of ecommerce solutions:

1. Software that is installed in a hosting account (like ShopSite or Magento)
-or-
2. Software as a service [SaaS] (no installation or ownership of files like Volusion, Shopify, BigCommerce, etc…) (Remotely hosted shopping cart)

Although the idea of not having to install or maintain any software may sound appealing, there are five drawbacks that you will want to consider…

1. Hidden Fees

At a quick glance, a remotely hosted solution may seem like a great deal. Some have low monthly fees. Others offer a ton of features at no additional cost. It can seem like a “no-brainer” to start with this type of solution.

hidden_fees

However, the devil is in the details, and there are two areas where costs can skyrocket:

Low bandwidth limits

Many of these ecommerce as a service solutions only include a few Gigabytes of monthly bandwidth (one even has a plan with just ONE gigabyte). If you have a handful of products, and use some hi-res images, it’s easy to exceed a few Gigabytes in just a few days.

If you read the fine print, the additional bandwidth fees can be quite high. Ranging from $5 per Gigabyte to $10 per Gigabyte! So, if your plan comes with 10 GB of bandwidth, and you have a few small videos and images that get downloaded/viewed a bunch of times, it’s not abnormal to see 50 Gigabytes in a month. At $10 per GB, that would be an additional $400 per month in fees!

Percentage of sales

Another tactic used by some of these providers is to take a percentage of your sales as an additional fee. When you’re starting out, this may be appealing, as your sales are low, and you can save money in the beginning.

However, let’s say you run a popular sale at low margin as a way to get your name out there. Add in an additional 1-2 percent that goes to your provider, combined with credit card fees, and you can quickly eat away any profit.

Keep this in mind when looking at other solutions. I don’t think your sales volume should be something that a provider gets to piggy-back on to make money themselves.

2. Moving away is very hard to do

moving_truck

Since the software is not something you own or even have access to, it makes these type of solutions non-portable. That means if you are not pleased with their level of service, or they have too much downtime, or you just want to take your existing store somewhere else, you can’t. You cannot use their solution unless it’s also hosted with them.

Contrast this with ecommerce software that is installed in your account. Something like ShopSite or Magento. With software that you do control, it’s easy to take your site and move it to a different host.

Software as a service solutions often make it difficult to migrate to another platform. Sometimes they restrict exports, or limit FTP downloads speeds. They don’t want you to leave, and make doing so a not so pleasant experience.

With other ecommerce software that is under your control, it’s often quite easy to switch platforms. Full exports are allowed, and you have access to all the files right in your account.

Always keep this in mind when choosing an ecommerce platform. Things may not always go smoothly, and it can be an eye-opening experience when you need to move and find many obstacles in your way.

3. Hosting is not their specialty

Many of these ecommerce solutions providers did not start out as web hosts. In fact, many of them used to sell their ecommerce software for use with other web hosts. They don’t have a background in hosting.

Hosting is more of an add-on than a core competency.

Does it matter? Yes, it can matter quite a bit.

Many of the features you may come to expect from a web host such as advanced email hosting, advanced access to your account via SSH or SFTP, and the ability to install other applications like a blog, are many times missing or severely lacking at a remote ecommerce provider.

Some don’t allow email at all at their service. Most will not allow ssh access. Many won’t let you install software such as WordPress, or custom scripts.

One SaaS provider even removed all FTP access in trying to deal with PCI issues.

These type of roadblocks when trying to build out your site can be frustrating, and may even impact your ability to operate efficiently. Take this into account before making a decision.

4. Updates are not under your control

This can be one of the worst issues to deal with for some online merchants. Since the software is under the provider’s direct control, and it’s often deployed in a shared / centralized fashion, updates are pushed out on the provider’s schedule.

You have no control over when and how updates are made to the software. On the surface, this may sound great. You don’t have to worry about upgrading the software.

But what if an upgrade breaks a feature you rely on? Or they remove a feature that is paramount to what you do? What happens if they perform this upgrade while you’re on vacation, and your store stops working?

With SaaS providers, they dictate when updates are performed. Since the software is centralized, you cannot run a different version, or opt not to upgrade.

However, if the software is under your direct control, you can decide when and if an upgrade will occur. You can work with your web host to pick a date to perform the upgrade.

If your web host is a hands-on managed provider, they’ll let you know when critical updates are needed, and can make the process painless. But at least you can have the final say when it comes to whether an update is applied to YOUR store. Giving up that control can be costly when things don’t go smoothly.

5. Customization can be a problem

custom_designSince you don’t have access to the source code, and the software is not even installed in your account, when you need a custom feature or solution, implementing it may be quite difficult if not impossible.

Things such as custom scripts, advanced design elements, or deeper API integration are often not available. This can force you to abandon desired features, or have odd workarounds that don’t fully accomplish your goals.

Remember, these type of services are not full hosting accounts, so adding in custom scripts, cron jobs, additional databases, or getting under the hood are not normally allowed.

If you go with ecommerce software that is installed in your web hosting account, then you have the freedom to add-in custom features. A developer or designer will have more options at their disposal to implement your desired features.

….

As you can see, SaaS ecommerce providers come with their share of issues. Many times these issues are not brought to light until it’s too late. From hidden fees to a lack of control, these solutions may not be the best choice for your online store.

Make sure you do your homework, and look into the hidden details that could become a roadblock to your store’s success.

Often times, choosing ecommerce software that you “own” can lead to a better outcome. It give *YOU*, the merchant, direct control over your store, and allows you to make decisions that best suit your needs. Combine this with choosing a web hosting company that can be your partner and provide you with personal support and outstanding service, and you will be well on your way to being a successful merchant, free to make your own choices when it comes to *YOUR* store.

photo credit