PlanetMysql.ru — информация о СУБД MySQL

A panel on “Future Perfect: The Road Ahead for MySQL” Brian Aker (HP), Paul Mikesell (Clustrix), Sundar Raghavan (Amazon), Slavik Markovich (McAffee), Ori Hernstadt (Akiban) If there’s one common theme to this panel, and indeed, this whole conference, it is “We’re hiring!” It is amazing how much talent there is at the conference this year [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

Mark Callaghan of Facebook: “What Comes Next for MySQL” focus on Large, sharded deployments Interesting numbers from their deployment (MySQL with Innodb): 60M QPS and 1.5B rows read/second in production MySQL with InnoDB is “web scale” scaled to 10x more data on the same servers by: Start with MySQL 5.1, flashcache, find and fix stalls, [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

An exciting and busy day yesterday – lots of good talks, good conversations and good beer! Back at the sessions this morning and the first keynote of the day by Sam Ghods of Box: “MySQL: Still the Best Choice for Mission-Critical Data” The usual story of a (file sharing) application that started out on one [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

Here it is finally – the MySQL conference 2012 starts with the Keynote Sessions. The first keynote speech is by Peter Zaitsev, founder of Percona and a very smart guy and also by Baron Schwartz (Percona), another very smart guy, the brains behind a number of toolkits for MySQL. They’re talking about the MySQL Evolution [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

A very well attended Pedro’s dinner – I didn’t count, but we had 9 tables of 8-10 people or so – dare I say almost a 100 people? Lots of beer, margaritas and good conversations! Here are a few pictures from the event
PlanetMySQL Voting: Vote UP / Vote DOWN

Wow what a lot has changed since the last MySQL conference I blogged about in 2007 MySQL has been acquired twice, once as MySQL by Sun and the second time around bundled with Sun when Oracle bought Sun. The conference is no longer organized by O’Reilly but by Percona. And the MySQL database itself has [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

Wow what a lot has changed since the last MySQL conference I blogged about in 2007 MySQL has been acquired twice, once as MySQL by Sun and the second time around bundled with Sun when Oracle bought Sun. The conference is no longer organized by O’Reilly but by Percona. And the MySQL database itself has [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

This release is just a quick bug fix release of an older 1.1.1 version of the plug-in. It’s long overdue but I’ve managed to fix “” problem only couple weeks ago. I’ve distributed the new version to the folks who have reached out to me by email of via blog reporting the issue in the [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

Isn’t that that time of the year again?

Yes, it is — it’s time for our annual Oracle Bloggers Meetup and of course Oracle is piggybacking OpenWorld with the meetup again! ;)

What: Oracle Bloggers Meetup 2011

When: Wed, 5-Oct-2011, 5:00pm

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103

View Larger Map.

View Larger Map

See the “Main Dining Room” on the floor plan below (we will actually use half of it – we are too big now to fit into a smaller “Lower Dining Room”) and ask where is the “Oracle Bloggers Meetup” booked under Pythian / Oracle name. These are the keywords to find us easily.

We have selected Wednesday again before the big event at Treasure Island. It seems to be the ideal timing because most of attendees don’t plan anything else on that day while the rest of the evenings are filled with customer meetings, sponsored events and etc. Easy choice now!

Compared to last year, we are shifting it 30 minutes earlier because the Treasure Island event is starting 30 minutes earlier. I don’t think it will be a problem — most of the participants showed up before 5:30pm last year – you will have had enough of OpenWorld by 5pm this time.

Traditionally, Oracle Technology Network joins Pythian sponsoring the venue and drinks.

As usual, vintage t-shirts from previous meetups will make you look cool — feel free to wear them. This year’s activity is still being planned — we have lots of cool ideas and couldn’t decide which one is the coolest but if you have something interesting in mind — let me know privately {last_name} at pythian.com.

For those of you who don’t know the history… The Bloggers Meetups during the Oracle Open World were started by Mark Rittman and continued by Eddie Awad and then I picked up the flag in 2009. The meetups have been great success so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and last year’s meetup blog post update from myself.

While the initial meetings was mostly around Oracle database folks, the latest meetups are joined buy guys and gals from lots of Oracle technologies – Oracle database, MySQL, Applications, Sun technologies, Java and more. All bloggers are welcome.

See the results of last year’s meetup here and here follow all the links.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us make sure we have the attendance numbers right. Make sure you provide your blog URL with your comment — it’s a Bloggers Meetup in the end! Make sure you comment here if you are attending so that we have enough room, food and (most important) drinks. Last year we barely fit. Again, we are reserving a bigger room but we want to make sure we can fit everyone!

Of course, do not under any circumstances forget to blog and tweet about this year’s bloggers meetup.

Last, but not least, you should know that the real organizer of the event and the one doing all the heavy-lifting here is Vanessa Simmons – give her a pat on the back when you see her.










Looking forward to seeing even more of you again this year!

PlanetMySQL Voting: Vote UP / Vote DOWN

I’ve been following the development of Tungsten Replicator for quiet some time now, and recently was fortunate enough to find the time to take a look at the product in more detail.

If you haven’t heard of Tungsten Replicator yet, it’s an open source database replication engine that can be used to complement or completely replace native MySQL Replication.  In addition to providing standard replication functionality, Tungsten Replicator introduces exciting new features such as global transaction IDs, heterogeneous replication from MySQL to Oracle and Postgres, parallel replication, and the ability to replicate from multiple masters to a single slave.

Giuseppe Maxia and Robert Hodges have been writing some excellent blog posts on Tungsten, providing great detail on both the architecture and functionality of the product.  One of Giuseppe’s recent posts (here) detailed a new simplified installation procedure so this seemed like a natural place to begin…

Just as an aside, I’m a big fan of VirtualBox when it comes to testing on my laptop.  I’ve created several base images which I select from depending on the task, and simply create a new virtual machine by cloning one of these images.  I used to do this using a combination of VBoxManage clonehd and the VirtualBox gui, but with the release of VirtualBox 4.1 this has been simplified with the introduction of the command clonevm, and it can now be achieved through a simple gui wizard or by executing the following command:

VBoxManage clonevm VirtualMachineName --name NewVMName --register

Additionally VirtualBox provides functionality to take a snapshot of a virtual machine, which makes testing different scenarios and rolling back changes very simple.

Anyway back to installing Tungsten.. ..I looked at Giuseppe’s instructions, and began working through the list of prerequisites.

The first prerequisite was to select a Unix-like operating system, using VirtualBox, I chose a 64bit install of CentOS 5.5 as my base image, and cloned and fired up three vm’s.  The image already had MySQL 5.5 installed, and to simplify testing I set SELinux to permissive and turned off iptables, also for ease, I assigned each vm a hostname (tungsten1, tungsten2, & tungsten3) and added the references to the /etc/hosts file.

The second and third prerequisites specify some required packages, I downloaded and installed the 64bit jre rpm from www.java.com/en/download/, and installed ruby and ruby-libs using the CentOS updates repository.

Next on the list was to create a user account to install and run Tungsten.  The prereq’s specify the user needs sudo access, ssh access to the other hosts involved, and read access to the MySQL binary logs.

For the purposes of the test, as the root user I created an additional user on each machine with access to the required group as follows:

[root@tungsten1 ~]# useradd tungsten -Gmysql
[root@tungsten1 ~]# passwd tungsten

I then edited /etc/sudoers and added:

tungsten ALL=(ALL)    NOPASSWD: ALL

Then logging in as the user “tungsten”, I setup the ssh access:

[tungsten@tungsten1 ~]$ ssh-keygen
[tungsten@tungsten1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub tungsten2
[tungsten@tungsten1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub tungsten3

Finally I created the mysql user and ensured the binary log was enabled.

mysql> create user tungstenmysql identified by 'tungsten';
mysql> grant all privileges on *.* to tungstenmysql with grant option;
mysql> flush privileges;
mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

With the prerequisites complete, I downloaded and extracted the Tungsten Replicator installation files.

[tungsten@tungsten1 ~]$ wget http://tungsten-replicator.googlecode.com/files/tungsten-replicator-2.0.3.tar.gz
[tungsten@tungsten1 ~]$ tar -xzvf tungsten-replicator-2.0.3.tar.gz

I then tried to complete the installation using the command mentioned in Giuseppe’s post:

[tungsten@tungsten1 ~]$ cd tungsten-replicator-2.0.3
[tungsten@tungsten1 tungsten-replicator-2.0.3]$ ./tools/tungsten-installer \
--master-slave \
--master-host=tungsten1 \
--datasource-user=tungstenmysql \
--datasource-password=tungsten \
--service-name=rep1 \
--home-directory=/opt/continuent/ \
--cluster-hosts=tungsten1,tungsten2,tungsten3 \
--start

Take 1: The moment of truth:

[tungsten@tungsten1 ~]$ cd /opt/continuent/
[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME              VALUE
----              -----
appliedLastSeqno: -1
appliedLatency  : -1.0
role            : master
serviceName     : rep1
serviceType     : unknown
started         : true
state           : OFFLINE:ERROR
Finished services command...

Hmmm time for some research.. ..the Tungsten Replicator project is hosted on Google code here, and documentation can be found here. By taking a look through the documentation and exploring the filesystem, I found there’s a replicator log called trepsvc.log that can be found in the <home-directory>/tungsten/tungsten-replicator/log/ folder. At first glance the log appears a bit noisy, but a simple grep on ERROR and then WARN showed there was an issue reading the binary log files. It seemed to be looking for mysql-bin.index even though I knew from the my.cnf I’d used, the binary logs took the format of mysqld-bin.

Taking a look at the tungsten-installer tool on the project wiki here, I found the extended list of options, and saw I could specify a –datasource-log-pattern option (the default for which was as expected, mysql-bin). Additionally I can see options available to specify the –master-log-file and –master-log-pos, which would obviously be useful if setting this up in an already running production environment.

Take 2: I removed the current installation and reran the install with the additional –datasource-log-pattern option.

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/replicator stop
[tungsten@tungsten1 continuent]$ rm -Rf /opt/continuent/*

mysql> drop database tungsten_rep1;
mysql> reset master;

[tungsten@tungsten1 continuent]$ ~/tungsten-replicator-2.0.3/tools/tungsten-installer \
--master-slave \
--master-host=tungsten1 \
--datasource-user=tungstenmysql \
--datasource-password=tungsten \
--datasource-log-pattern=mysqld-bin \
--service-name=rep1 \
--home-directory=/opt/continuent/ \
--cluster-hosts=tungsten1,tungsten2,tungsten3 \
--start

Checking the master:

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME VALUE
---- -----
appliedLastSeqno: 1
appliedLatency : 0.901
role : master
serviceName : rep1
serviceType : local
started : true
state : ONLINE
Finished services command...

Success!

Checking the slaves:

[tungsten@tungsten2 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME VALUE
---- -----
appliedLastSeqno: -1
appliedLatency : -1.0
role : slave
serviceName : rep1
serviceType : unknown
started : true
state : OFFLINE:ERROR
Finished services command...

Hmmm time for some more research.. ..checking the log I could see there was an issue with setting the NO_ENGINE_SUBSTITUTION sql_mode, it looked like it was actually trying to set it to MODE_NO_ENGINE_SUBSTITUTION, this sounded a little buggy. I decided to search all issues listed on the Google code project site, and sure enough I found issue number 112 which described the behavior exactly – it actually looks like there’s a fix been submitted already. For now, just so I could proceed with the installation, I decided to remove the NO_ENGINE_SUBSTITION sql-mode option from the my.cnf.

Take 3: I removed the current installation and reran the install with the additional –datasource-log-pattern option and without NO_ENGINE_SUBSTITUTION.

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/replicator stop
[tungsten@tungsten1 continuent]$ rm -Rf /opt/continuent/*

mysql> drop database tungsten_rep1;
mysql> reset master;

[tungsten@tungsten1 continuent]$ ~/tungsten-replicator-2.0.3/tools/tungsten-installer \
--master-slave \
--master-host=tungsten1 \
--datasource-user=tungstenmysql \
--datasource-password=tungsten \
--datasource-log-pattern=mysqld-bin \
--service-name=rep1 \
--home-directory=/opt/continuent/ \
--cluster-hosts=tungsten1,tungsten2,tungsten3 \
--start

Checking the master:

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME VALUE
---- -----
appliedLastSeqno: 1
appliedLatency : 0.602
role : master
serviceName : rep1
serviceType : local
started : true
state : ONLINE
Finished services command...

Success!

Checking the slaves:

NAME VALUE
---- -----
appliedLastSeqno: 1
appliedLatency : 0.0
role : slave
serviceName : rep1
serviceType : local
started : true
state : ONLINE
Finished services command...

Success!

Whilst it wasn’t totally seamless, the installation and troubleshooting were both pretty straightforward, especially considering this was literally the first time I’ve looked at Tungsten in any detail. I’m now able to create tables, and insert test data on the master, and see it replicate to the slaves. Watching replication in action and seeing data actually move from a to b, is truly one of those things that’s a joy to see!

Having explored the basics of setting up Tungsten Replicator, in the next post, I’ll look to explore some of the commands are available to administer the engine.

PlanetMySQL Voting: Vote UP / Vote DOWN