Mar
30
2016
--

Docker MySQL Replication 101

Docker

Precona Server DockerIn this blog post, we’ll discuss some of the basics regarding Docker MySQL replication. Docker has gained widespread popularity in recent years as a lightweight alternative to virtualization. It is ideal for building virtual development and testing environments. The solution is flexible and seamlessly integrates with popular CI tools.

 

This post walks through the setup of MySQL replication with Docker using Percona Server 5.6 images. To keep things simple we’ll configure a pair of instances and override only the most important variables for replication. You can add whatever other variables you want to override in the configuration files for each instance.

Note: the configuration described here is suitable for development or testing. We’ve also used the operating system repository packages; for the latest version use the official Docker images. The steps described can be used to setup more slaves if required, as long as each slave has a different server-id.

First, install Docker and pull the Percona images (this will take some time and is only executed once):

# Docker install for Debian / Ubuntu
apt-get install docker.io
# Docker install for Red Hat / CentOS (requires EPEL repo)
yum install epel-release # If not installed already
yum install docker-io
# Pull docker repos
docker pull percona

Now create locally persisted directories for the:

  1. Instance configuration
  2. Data files
# Create local data directories
mkdir -p /opt/Docker/masterdb/data /opt/Docker/slavedb/data
# Create local my.cnf directories
mkdir -p /opt/Docker/masterdb/cnf /opt/Docker/slavedb/cnf
### Create configuration files for master and slave
vi /opt/Docker/masterdb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=1
binlog_format=ROW
log-bin
vi /opt/Docker/slavedb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=2

Great, now we’re ready start our instances and configure replication. Launch the master node, configure the replication user and get the initial replication co-ordinates:

# Launch master instance
docker run --name masterdb -v /opt/Docker/masterdb/cnf:/etc/mysql/conf.d -v /opt/Docker/masterdb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6
00a0231fb689d27afad2753e4350192bebc19ab4ff733c07da9c20ca4169759e
# Create replication user
docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -vvv -e"GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass'G"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
GRANT REPLICATION SLAVE ON *.* TO repl@"%"
--------------
Query OK, 0 rows affected (0.02 sec)
Bye
### Get master status
docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -e"SHOW MASTER STATUSG"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
             File: mysqld-bin.000004
         Position: 310
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

If you look carefully at the “docker run” command for masterdb, you’ll notice we’ve defined two paths to share from local storage:

/opt/Docker/masterdb/data:/var/lib/mysql

  • This maps the local “/opt/Docker/masterdb/data” to the masterdb’s container’s “/var/lib/mysql path”
  • All files within the datadir “/var/lib/mysql” persist locally on the host running docker rather than in the container
/opt/Docker/masterdb/cnf:/etc/mysql/conf.d

  • This maps the local “/opt/Docker/masterdb/cnf” directory to the container’s “/etc/mysql/conf.d” path
  • The configuration files for the masterdb instance persist locally as well
  • Remember these files augment or override the file in “/etc/mysql/my.cnf” within the container (i.e., defaults will be used for all other variables)

We’re done setting up the master, so let’s continue with the slave instance. For this instance the “docker run” command also includes the “–link masterdb:mysql” command, which links the slave instance to the master instance for replication.

After starting the instance, set the replication co-ordinates captured in the previous step:

docker run --name slavedb -d -v /opt/Docker/slavedb/cnf:/etc/mysql/conf.d -v /opt/Docker/slavedb/data:/var/lib/mysql --link masterdb:mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6
eb7141121300c104ccee0b2df018e33d4f7f10bf5d98445ed4a54e1316f41891
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e'change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310;"' -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310
--------------
Query OK, 0 rows affected, 2 warnings (0.23 sec)
Bye

Almost ready to go! The last step is to start replication and verify that replication running:

# Start replication
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"START SLAVE;" -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
START SLAVE
--------------
Query OK, 0 rows affected, 1 warning (0.00 sec)
Bye
# Verify replication is running OK
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"SHOW SLAVE STATUSG" -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SHOW SLAVE STATUS
--------------
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000004
          Read_Master_Log_Pos: 310
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: mysqld-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 310
              Relay_Log_Space: 458
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 230d005a-f1a6-11e5-b546-0242ac110004
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
Bye

Finally, we have a pair of dockerized Percona Server 5.6 master-slave servers replicating!

As mentioned before, this is suitable for a development or testing environment. Before going into production with this configuration, think carefully about the tuning of the “my.cnf” variables and the choice of disks used for the data/binlog directories. It is important to remember that newer versions of Docker recommend using “networks” rather than “linking” for communication between containers.

Aug
12
2015
--

Percona Live Amsterdam Discounted Pricing and Community Dinner!

The countdown is on for the annual Percona Live Data Performance Conference and Expo in Europe! This year the conference will be taking place in the great city of Amsterdam September 21-23rd. This three day conference will focus on the latest trends, news and best practices in the MySQL, NoSQL and Data in the Cloud markets, while looking forward to what’s on the long-term horizon within the global Data Performance industry. With 84 breakout sessions, 8 tutorial sessions and 6 keynotes, including a Data in the Cloud keynote panel, there will certainly be no lack of content.
Advanced Rate Registration ENDS August 16th so make sure to register now to secure the best price possible.

As it is a Percona Live Conference, there will certainly be no lack of FUN either!!!!Rembrandt-Square

As tradition holds, there will be a Community Dinner. Tuesday night, September 22, Percona Live Diamond Sponsor Booking.com will be hosting the Community Dinner of the year at their very own headquarters located in historic Rembrandt Square in the heart of the city. After breakout sessions conclude, attendees will be picked up right outside of the venue and taken to booking.com’s headquarters by canal boats! This gives all attendees the opportunity to play “tourist” while viewing the beauty of Amsterdam from the water. amsterdam-bus-and-canal-boat-tour-3bfd6Attendees will be dropped off right next to Booking.com’s office! Come and show your support for the community while enjoying dinner and drinks! Space is limited so make sure to sign up ASAP!

So don’t forgetregister for the conference and sign up for the community dinner before space is gone!
See you in Amsterdam!

The post Percona Live Amsterdam Discounted Pricing and Community Dinner! appeared first on MySQL Performance Blog.

Jul
20
2011
--

Percona Live London!

The first Percona Live Conferences in San Francisco and New York were such wonderful successes that we have been inspired to take it across the ocean into the UK. We are therefore proud to announce that our next Percona Live Conference will be held London October 24-25, 2011 . Registration is now open and we are offering an early-bird registration for eager attendees who appreciate a discount. Register here: http://www.eventbrite.com/event/1909670877/eorg/

Additionally, the Percona Live team is ready to accept submissions of talks and/or tutorials from individuals who wish to speak at this two day event. Please go here: http://www.percona.com/live/london-2011/call-for-speakers/ to submit your talks.

Whether you are a speaker or an attendee, there are a number of sponsorship opportunities available. Please contact Percona’s sales team for further information.

Jan
31
2011
--

MySQL & Friends Meetup @ FOSDEM

Kris Buytaert organized a MySQL Meetup at FOSDEM last year, and because of the success we’ll be doing it again this year, in the same restaurant: Sogno d’Italia.

Everybody is invited to come to the dinner. Just register on http://mysqlandfriendsfosdem2011.eventbrite.com/ so we have an idea how many reservations we should make. The maximum capacity is about 40 people.

We’ll plan to meet at 19:00 on Saturday, under the big tree in front of the AW building. Then we’ll walk to the restaurant, which is about 5 minutes walking distance.

 

Jan
05
2011
--

Announcing Percona Live: San Francisco February 16th

Today we’re announcing Percona Live – a one day event to be held at the Bently Reserve on February 16th in San Francisco.  Live is our way of showcasing some of the awesome work that has been going into MySQL recently – and the theme of this event is Beyond MySQL 5.1.

Our first guest speaker is none other than Jeremy Zawodny.  Jeremy is well known in the MySQL community having been the original author of High Performance MySQL 1st Ed.  He will be presenting on how Craigslist has already upgraded to MySQL 5.5 – and are running on Fusion-io SSDs in production.

Tickets are available for early bird registration at $50.  To signup, or for more information please visit the percona website.


Entry posted by Morgan Tocker |
One comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Jan
03
2011
--

Speaking on MySQL/PHP Meetup in Charlotte, NC

I’m speaking at MySQL/PHP Meetup at Charlotte,NC next week with topic being Scaling MySQL Applications. We will have a presentation probably for about an hour and the good amount of time for questions. So come prepared.

Great thanks to Red Ventures, LLC for help organizing and sponsoring this event. I’m hearing there are going to be a door prizes, some snacks and drinks available on this event.


Entry posted by Peter Zaitsev |
No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Dec
13
2010
--

Percona Training in London and Frankfurt

For those that missed it – last week we added training in Frankfurt and London to our website.  This includes our new Operations Training announced in September.

(While in Europe, we’d love to speak at your meetup group on Percona Server and/or MySQL Optimization.  If you’re a meetup organizer in London or Paris, please feel free to get in touch.)


Entry posted by Morgan Tocker |
No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Dec
09
2010
--

Recordings for webinars are now online

We’ve just published the recordings for our webinars; Migrating MyISAM to InnoDB and Introduction to Percona Server, XtraDB and Xtrabackup.  To listen, click on (Watch: Recorded Webinar) on each of these links.

We’re planning for future webinars next year.  If you have any suggestions, feel free to leave a comment.


Entry posted by Morgan Tocker |
5 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Nov
26
2010
--

Webinar: Introduction to Percona Server, XtraDB and Xtrabackup

We’ve had such a large number of signups to our Migrating MyISAM to InnoDB webinar, that we decided to hold one more before the year is out:

December, 8th 9AM PST: Introduction to Percona Server, XtraDB and Xtrabackup.

As the title suggests: this presentation is an introduction.  We will be running through the main aspects that change, and where you can expect to get the most benefit.

This will be a technical talk, but perhaps less technical than some of our other posts. I recommend inviting a few friends & colleagues that are already familiar with MySQL, but perhaps not regular readers of our blog.

(Time for Q&A will follow the main presentation).


Entry posted by Morgan Tocker |
2 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Nov
05
2010
--

Percona’s Commitments to MySQL Users

You probably saw the Twitter storm over Oracle’s pricing changes and InnoDB in the last few days. The fear about Oracle removing InnoDB from the free version of MySQL was baseless — it was just a misunderstanding. Still, in the years since MySQL has been acquired by Sun, and then by Oracle, many MySQL users have been uncertain about the future. We think it’s important for us to make our own position and plans clear and public, so that there is no fear or doubt about what Percona will do.

We want to begin by saying “thank you” to those who built and maintained MySQL over the years. Many of us at Percona came from the original MySQL team. We know that it took a decade-long group effort from Monty, David, Marten and many others to develop it into the world’s leading open-source database. Much of their work is never publicly recognized, but it is the foundation of our own work, and we’re indebted to it. MySQL development suffered some detours and slowdowns over the last few years. This era is over. MySQL has never progressed so rapidly towards better features, better performance, and better quality. We applaud the great work coming from Oracle.

Always business-focused

We know that statements such as these can be filled with corporate double-speak. Let there be no doubt: Percona is in business to make money, not to make flowers grow taller and the sun shine brighter. Likewise, our customers are in business to make money. We write software that contributes to our business goals, and to our customers’ goals. And we think the best way to do that is by adhering to open-source principles.

Always improving

Our flagship database server Percona Server with XtraDB powers critical portions of the U.S. economy, applications you rely on every day, utility services, and governments. Thousands of people use it. We take that very seriously.

Our roadmap is to continue what we are doing: diagnose and solve the root problems our customers experience, and build new features to overcome limitations in the server. We have a dedicated team of engineers making sustained and rapid progress on improving it. We plan to continue releasing new versions frequently.

Always free

Our software is 100% free. We are a pure-play open-source company. We do not practice dual-licensing. We hold nothing back. Our strategy is to be a services company, pure and simple. The value we provide to customers is our depth of knowledge about how to solve their problems. The software we produce is evidence of our abilities.

Always independent and objective

Percona Server with XtraDB is an enhanced drop-in replacement for MySQL, for those whose needs are not met by the official MySQL server from Oracle. It offers more performance, more features, and more diagnostic capabilities. If it meets your needs, we’re happy, but we make our money from services, not software. Our job is to tell our customers what we think is best for them. That’s partly why we are a pure-play open-source company. If we earned money directly from our software, that would compromise our core principle of objectivity, because we’d have a financial incentive to push customers towards our software instead of helping them make the right decisions for themselves.

Always open-source

We will always work to create and improve open-source software. When there’s a need, we respond to closed-source software by providing open-source alternatives. An example of this is Percona XtraBackup. We have also worked to create open-source tools for monitoring, query analysis, replication management, and other capabilities.

If you’re worried about Oracle making MySQL closed-source, stop worrying. It can’t be done. Oracle cannot take back what is already in the community’s hands; at worst, they can only stop updating it with new versions. And we can and will continue to provide an open-source database server that delivers improved performance and functionality to our customers.

Always fairly priced

Percona is the oldest independent support provider for MySQL. We opened in mid-2006. Since then we’ve grown to over 40 full-time staff and over 900 customers. We think this success is partially because we’re fair to our customers. Our pricing model has always been to match value for the customer with Percona’s costs as closely as possible. This is why our consulting is billed by the hour in a pay-as-you-go model: you pay only for what you use. Our support and maintenance offering covers unlimited servers and CPUs, because the cost of providing support is not related to how many servers you’re running. Per-unit pricing would also compromise our objectivity — we’d have an incentive to increase your server count, instead of helping you make your systems more efficient.

Conclusion

We’re dedicated to advancing the state of the art in MySQL performance and functionality, because our customers pay us to do it. You can count on us continuing to provide an outstanding open-source database server and other software tools, with accompanying world-class services and support. We salute everyone who’s helping advance MySQL and open-source software, including Oracle, and look forward to a bright and prosperous future for our businesses.

Sincerely,
The Percona Executive Management Team:

Peter Zaitsev, Chief Executive Officer
Vadim Tkachenko, Chief Technology Officer
Baron Schwartz, Chief Performance Architect
Tom Basil, Chief Operating Officer
Bill Schuler, Vice President of Sales
Espen Braekken, Vice President of Consulting and Support


Entry posted by Baron Schwartz |
10 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com