tag:blogger.com,1999:blog-68577433826617952192024-03-19T07:21:38.412-04:00MySQL Database StuffRandom information on MySQLCraighttp://www.blogger.com/profile/18259297452579234900noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-6857743382661795219.post-63150542334304541162013-01-25T16:48:00.002-05:002021-03-24T09:49:54.504-04:00MySQL 5.6: Testing innodb_read_only with multiple instancesThere are many good things in the upcoming MySQL 5.6 release. One thing that caught my eye early on was the ability to start the server with InnoDB set to a read only mode.<br />
<br />
A few months ago Todd Farmer wrote about this ability from the perspective of setting up an instance on read-only media (<a href="http://mysqlblog.fivefarmers.com/2012/11/15/innodb-now-works-with-read-only-media/">InnoDB now works with read-only media</a>). And I encourage you to read that post first.<br />
I decided to test this from a data warehouse perspective (as Sunny Bains points out in a comment to Todd's post). I used machines I have available: 1 OL6 desktop running 5.6.9 and 1 MacBook Pro running 10.8.<br />
<br />
First thing to try was adding the innodb_read_only parameter to the config file on an instance I already had running on the Linux box. My setting:<br />
<br />
<div style="color: purple; font-family: "Courier New", Courier, monospace;">
[mysqld]<br />
user = mysql<br />
port = 5602<br />
basedir = /opt/mysql/5.6.9rc<br />
datadir = /data/5.6.ro<br />
socket = /tmp/mysql5602.sock<br />
tmpdir = /tmp<br />
pid-file = /tmp/mysql_5602.pid<br />
log-error = /tmp/mysql_5602.err<br />
#</div>
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb-read-only</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">lower_case_table_names = 1</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb-log-file-size = 256M</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb_buffer_pool_size = 2G</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb_change_buffering = none</span><br />
<br />
<br />
One thing you may notice is that I have the log file size set to 256M, which is really a waste since there will be no logging taking place. I'll explain why towards the end of this post.<br />
<br />
So, nothing unexpected after starting the instance with the settings above. SELECTs work fine but DML operations fail:<br />
<br style="color: purple; font-family: "Courier New", Courier, monospace;" />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">craig@localhost[5.6_r02] (employees) > select * from departments where dept_no = 'd001';<br />+---------+-----------+<br />| dept_no | dept_name |<br />+---------+-----------+<br />| d001 | Marketing |<br />+---------+-----------+<br />1 row in set (0.00 sec)<br /><br />craig@localhost[5.6_r02] (employees) > delete from departments where dept_no = 'd001';<br />ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only)<br />craig@localhost[5.6_r02] (employees) ></span><span style="color: purple; font-family: "Courier New",Courier,monospace;"> </span><br />
<br />
OK, now to set up a separate instance and point it to the same data directory.<br />
The my.cnf file for the second instance:<br />
<div style="color: purple; font-family: "Courier New", Courier, monospace;">
<br /></div>
<span style="color: purple; font-family: "Courier New",Courier,monospace;">[mysqld]</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">user = mysql</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">port = 5601</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">socket = /tmp/mysql5601.sock</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">basedir = /opt/mysql/5.6.9rc</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">datadir = /data/5.6.ro</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">tmpdir = /tmp</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">pid-file = /tmp/mysql_5601.pid</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">log-error = /tmp/mysql_5601.err</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">#</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb-read-only</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb-log-file-size = 256M</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb_buffer_pool_size = 2G</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">innodb_change_buffering = none</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">lower_case_table_names = 1</span><br />
<br />
Again, try to DELETE from the employees.departments table:<br />
<br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">craig@localhost[5.6_r01] (employees) > select * from departments where dept_no = 'd001';</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">+---------+-----------+</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">| dept_no | dept_name |</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">+---------+-----------+</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">| d001 | Marketing |</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">+---------+-----------+</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">1 row in set (0.00 sec)</span><br />
<br style="color: purple; font-family: "Courier New", Courier, monospace;" />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">craig@localhost[5.6_r01] (employees) > delete from departments where dept_no = 'd001';</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only)</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">craig@localhost[5.6_r01] (employees) > </span><br />
<br />
So, two instances using the same InnoDB data. Now to try setting up an instance on a separate machine accessing the same datadir on my Linux server. After learning enough of NFS to be dangerous and flailing around for half a day, I was finally able the get the exported NFS file system mounted on my laptop. On the Linux side (NFS server), the /etc/exports file has the following entry:<br />
<br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">[craig@craighp msb_56_ro2]$ cat /etc/exports</span><br />
<span style="color: purple; font-family: "Courier New",Courier,monospace;">/data craig-mbp(rw,sync,insecure)</span><br />
<br />
A screenshot from the laptop of mounting this directory:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFUJIB6iADajGFJFiSh7hbsWDqZiMLh01hF1_K8Si8w3w9maqKtbKwg1hVgOdMeAqnVUvOLyR4gellChtcgpARJ05tQp6d5FNoVnnGna3O7LMrHEm8gIYMp_2n2JIvDDg5kLWqzn5HjpY/s1600/SCREENSHOT_2013-01-25+03.53.28+pm.png" style="margin-left: 1em; margin-right: 1em;"><img alt="mount -t nfs -o noowners,rw craighp:/data/5.6.ro /Volumes/data_56" border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFUJIB6iADajGFJFiSh7hbsWDqZiMLh01hF1_K8Si8w3w9maqKtbKwg1hVgOdMeAqnVUvOLyR4gellChtcgpARJ05tQp6d5FNoVnnGna3O7LMrHEm8gIYMp_2n2JIvDDg5kLWqzn5HjpY/s640/SCREENSHOT_2013-01-25+03.53.28+pm.png" title="Mounting NFS filesystem" width="579" /></a></div>
And here is the my.cnf file I used, starting of the server, and SQL commands showing I can read the data but changes are not allowed (at least to InnoDB data).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH0g3yIqlSsiQNu7YUxIhn1GOesaWI02Qw0DFKJlIlImT87xyKdyS6e3H9X0rgGIAp-g2byeR15QRLeXGlcOypyl_E7h02kMBvjBcEjT67eim7RT7GAUePm28I9azfqH3Y6F9dJrl-1fI/s1600/SCREENSHOT_2013-01-25+03.57.38+pm.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH0g3yIqlSsiQNu7YUxIhn1GOesaWI02Qw0DFKJlIlImT87xyKdyS6e3H9X0rgGIAp-g2byeR15QRLeXGlcOypyl_E7h02kMBvjBcEjT67eim7RT7GAUePm28I9azfqH3Y6F9dJrl-1fI/s640/SCREENSHOT_2013-01-25+03.57.38+pm.png" width="504" /></a></div>
So, multiple instances across multiple machines accessing one data directory of InnoDB data. May have some uses in a data warehouse, operational data store, or reporting environment by spreading processing across servers and saving disk space.<br />
<br />
Now, back to why I set innodb_log_file_size = 256M. I wouldn't necessarily recommend this but I wanted to see I could restart one of my instances as a RW instance (commenting out the innodb_read_only parameter). And I could. And I could make changes, load new data, delete old data, etc. And after you make your changes, you can restart the instance as read only. While this is going on, the other instances are blissfully unaware and will see none of it until they are restarted. I will reiterate that this worked in my limited testing. Production is an entirely different beast.<br />
<br />
The MySQL server and InnoDB has done an amazing job with the 5.6 release. Kudos. And thanks to Giuseppe Maxia for his MySQL Sandbox toolkit. Spinning up test instances is fast and easy.Craighttp://www.blogger.com/profile/18259297452579234900noreply@blogger.com13tag:blogger.com,1999:blog-6857743382661795219.post-44317882461490612842009-07-01T10:34:00.004-04:002009-07-01T10:56:20.549-04:00Status 96 error on svc.startdI've been working with SMF on and off for a few weeks now and still get stung by this error. If you see something like this in the SVC log after enabling a service (i.e., <i>svcadm enable mysql</i>):<br /><br /><blockquote><span class="Apple-style-span" style="font-family:'courier new';">bash-3.00# cat /var/svc/log/application-database-mysql:v51.log<br />[ Jul 1 07:36:37 Enabled. ]<br />[ Jul 1 07:36:37 Executing start method ("/lib/svc/method/mysql start") ]<br />svc.startd could not set context for method: chdir: No such file or directory<br />[ Jul 1 07:36:37 Method "start" exited with status 96 ]</span></blockquote><br /><br />head over to the manifest file for the service and check the <span style="font-weight:bold;">method_context</span> section. In my case, I had no working_directory element set so adding this element solves the problem.<br /><br />Before:<br /><blockquote><span class="Apple-style-span" style="font-family:'courier new';"><br /><pre><method_context><br /> <method_credential user='mysql' group='mysql' /><br /></method_context></pre></span></blockquote><br />After:<br /><blockquote><span class="Apple-style-span" style="font-family:'courier new';"><br /><pre><method_context <span style="font-weight:bold;">working_directory='/usr/local/mysql'</span>><br /> <method_credential user='mysql' group='mysql' /><br /></method_context></pre></span></blockquote><br />After making this change, don't forget to import the manifest:<br /><blockquote><span class="Apple-style-span" style="font-family:'courier new';">svccfg import /var/svc/manifest/application/database/mysql.xml</span></blockquote><br /><br />-- CraigCraighttp://www.blogger.com/profile/18259297452579234900noreply@blogger.com0tag:blogger.com,1999:blog-6857743382661795219.post-14568829172843788062009-06-29T17:13:00.000-04:002013-01-04T15:23:05.171-05:00Dynamically add Cluster data nodes using Solaris SMFAs a follow on to my previous post on <a href="http://csylvester.blogspot.com/2009/06/using-opensolaris-smf-for-managing.html" linkindex="55">using Solaris SMF to manage Cluster</a>, I'll expand my setup and show how to dynamically add data nodes to Cluster and control the new nodes with SMF.<br />
<br />
First, the setup. Since I wanted something that would run completely on my laptop, I decided to clone my current OpenSolaris VM (hostname = <i>craigOS_0609</i>) to create a second "machine" (hostname = <i>craigOS_0609_vm2</i>). Thought this would be easy and it was, except for the networking piece. When I just had a single VM, NAT worked fine. However, trying to use two VMs of OpenSolaris with NAT turned out to be problematic. They both ended up with a 10.0.2.15 IP address. Researched it a bit and didn't find anything definitive but did see that bridged networking might be the way to go. Well, after futzing with it for a day or so, it turned out that simply setting both VMs to use bridged networking allowed each to obtain an IP address from my router and have both VMs communicating with no problems.<br />
<br />
One thing I was hoping I could do is control the startup of the data nodes on the second VM from the SMF services on the primary VM. I was also hoping I could define a dependency that spanned machines so the new data nodes would require the Management Server on the primary machine to be running before starting. Found out that that is currently not supported but is a planned feature.<br />
<br />
OK, first things first. Modify the current Cluster config file to have entries for the two new data nodes we will add:<br />
<br />
<pre>[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentOperations= 10000
DataMemory= 80M
IndexMemory= 24M
TimeBetweenWatchDogCheck= 30000
DataDir= /usr/local/mysql/data_cluster
MaxNoOfOrderedIndexes= 512
[ndb_mgmd default]
DataDir= /usr/local/mysql/data_cluster
[ndb_mgmd]
Id=10
HostName= craigOS_0609
[ndbd]
Id= 1
HostName= craigOS_0609
[ndbd]
Id= 2
HostName= craigOS_0609
<b>[ndbd]
Id= 3
HostName= 192.168.1.10
[ndbd]
Id= 4
HostName= 192.168.1.10</b>
[mysqld]
Id= 20
[mysqld]
Id= 21
[mysqld]
Id= 22
[mysqld]
Id= 23
</pre>
<br />
Restart the management server and wait a bit for it to load the new configuration:<br />
<br />
<pre># svcadm restart mysql_ndb_mgmd
# ndb_mgm -e show
Connected to Management Server at: craigOS_0609:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5, Nodegroup: 0, Master)
id=2 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.1.10)
id=4 (not connected, accepting connect from 192.168.1.10)
[ndb_mgmd(MGM)] 1 node(s)
id=10 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5)
[mysqld(API)] 4 node(s)
id=20 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5)
id=21 (not connected, accepting connect from any host)
id=22 (not connected, accepting connect from any host)
id=23 (not connected, accepting connect from any host)
#
</pre>
Now we need to create a new nodegroup with our newly added nodes:<br />
<pre># ndb_mgm -e "create nodegroup 3,4"
Connected to Management Server at: craigOS_0609:1186
Nodegroup 1 created
#
</pre>
Checking memory usage in Cluster shows that the new nodes are recognized but currently have no data:<br />
<br />
<pre># ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> all report memory
Connected to Management Server at: craigOS_0609:1186
ndb_mgm> Node 1: Data usage is 2%(58 32K pages of total 2560)
Node 1: Index usage is 1%(36 8K pages of total 3104)
Node 2: Data usage is 2%(58 32K pages of total 2560)
Node 2: Index usage is 1%(36 8K pages of total 3104)
Node 3: Data usage is 0%(16 32K pages of total 2560)
Node 3: Index usage is 0%(0 8K pages of total 3104)
Node 4: Data usage is 0%(16 32K pages of total 2560)
Node 4: Index usage is 0%(0 8K pages of total 3104)
ndb_mgm> quit
#
</pre>
<br />
Also, checking the statistics for one of our Cluster tables confirms that we are still only using the original two data nodes:<br />
<pre># ndb_desc -c craigOS_0609 -d world City -p
-- City --
Version: 1
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 5
Number of primary keys: 1
Length of frm data: 324
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
TableStatus: Retrieved
-- Attributes --
ID Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
Name Char(35;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
CountryCode Char(3;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
District Char(20;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
Population Int NOT NULL AT=FIXED ST=MEMORY
-- Indexes --
PRIMARY KEY(ID) - UniqueHashIndex
PRIMARY(ID) - OrderedIndex
<b>-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory
0 2084 2084 196608 0
1 1995 1995 196608 0 </b>
NDBT_ProgramExit: 0 - OK
</pre>
OK, now it is time to add our new data nodes. Since I cloned the primary VM, all the software I needed was already installed and ready to go in the new VM.<br />
<br />
Step 0: Make sure the following services are disabled ("svcadm disable <service>"): <b>mysql_ndb_mgmd, mysql_ndbd:node1, mysql_ndbd:node2, mysql</b><br />
<br />
Also, make sure the connect string parameters in the /etc/mysql/my.cnf file is set to the primary VM (the one running our management server):<br />
<pre>...
[mysqld]
ndbcluster
ndb-connectstring = craigOS_0609
...
[ndbd]
connect-string = craigOS_0609
[ndb_mgm]
connect-string = craigOS_0609
...
</pre>
Step 1: Remove the SMF service for the Management node since I would only be using one:<br />
<blockquote>
# svccfg delete mysql_ndb_mgmd</blockquote>
Step 2: Since our new nodes with be 3 and 4, let's just remove the SMF services for node1 and node2. In the next step we will modify the manifest and rename the nodes to node3 and node4.<br />
<blockquote>
# svccfg delete mysql_ndbd:node1<br />
# svccfg delete mysql_ndbd:node2</blockquote>
Step 3: Change the manifest for the data nodes and import into the SMF repository:<br />
<blockquote>
# cd /var/svc/manifest/application/database<br />
# vi mysql_ndbd.xml</blockquote>
For each of the two instances defined, make sure the mgmd_hosts and node_id properties are set correctly:<br />
<br />
<pre>...
<instance enabled="false" name="node3">
<method_context>
<method_credential group="mysql" user="mysql">
</method_context>
<property_group name="cluster" type="application">
<propval name="bin" type="astring" value="/usr/local/mysql/bin">
<propval name="data" type="astring" value="/usr/local/mysql/data_cluster">
<propval name="mgmd_hosts" type="astring" value="craigOS_0609">
<propval name="node_id" type="integer" value="3">
</property_group>
</instance>
<instance enabled="false" name="node4">
<method_context>
<method_credential group="mysql" user="mysql">
</method_context>
<property_group name="cluster" type="application">
<propval name="bin" type="astring" value="/usr/local/mysql/bin">
<propval name="data" type="astring" value="/usr/local/mysql/data_cluster">
<propval name="mgmd_hosts" type="astring" value="craigOS_0609">
<propval name="node_id" type="integer" value="4">
</property_group>
</instance>
...
</pre>
Step 4: We can now import our settings for our new data nodes and enable them:<br />
<blockquote>
# svccfg import mysql_ndbd.xml<br />
# svcadm enable mysql_ndbd:node3<br />
# svcadm enable mysql_ndbd:node4</blockquote>
Step 5: Now let's check if our new nodes have joined the Cluster (this can be done from either VM):<br />
<pre># ndb_mgm -e show
Connected to Management Server at: craigOS_0609:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5, Nodegroup: 0, Master)
id=2 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5, Nodegroup: 0)
id=3 @192.168.1.10 (mysql-5.1.32 ndb-7.0.5, Nodegroup: 1)
id=4 @192.168.1.10 (mysql-5.1.32 ndb-7.0.5, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=10 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5)
[mysqld(API)] 4 node(s)
id=20 @192.168.1.8 (mysql-5.1.32 ndb-7.0.5)
id=21 @192.168.1.10 (mysql-5.1.32 ndb-7.0.5)
id=22 (not connected, accepting connect from any host)
id=23 (not connected, accepting connect from any host)
</pre>
<br />
So, things look good so far. And you can see that I actually started a second MySQL Server from the new VM.<br />
<br />
For the last step, we will redistribute the existing Cluster tables to take advantage of the new nodes. Otherwise, only data added after the new nodes go online will be placed on the nodes.<br />
<br />
Our sample "world" database contains the clustered tables, so it's easy enough just to type in the three commands:<br />
<br />
<pre># mysql world
mysql> alter table online table City reorganize partition;
mysql> alter table online table Country reorganize partition;
mysql> alter table online table CountryLanguage reorganize partition;
mysql> quit
#
</pre>
<br />
Now that that's done, let's recheck our table distribution using <b>ndb_desc</b>:<br />
<pre># ndb_desc -c craigOS_0609 -d world City -p
-- City --
Version: 16777217
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 5
Number of primary keys: 1
Length of frm data: 324
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
TableStatus: Retrieved
-- Attributes --
ID Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
Name Char(35;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
CountryCode Char(3;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
District Char(20;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
Population Int NOT NULL AT=FIXED ST=MEMORY
-- Indexes --
PRIMARY KEY(ID) - UniqueHashIndex
PRIMARY(ID) - OrderedIndex
<b>-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory
0 1058 4136 196608 0
1 1018 3949 196608 0
2 1026 1026 98304 0
3 977 977 98304 </b> 0
NDBT_ProgramExit: 0 - OK
</pre>
<br />
That's it!Craighttp://www.blogger.com/profile/18259297452579234900noreply@blogger.com0tag:blogger.com,1999:blog-6857743382661795219.post-19158571383797572042009-06-18T10:05:00.006-04:002009-06-18T20:23:55.656-04:00Convert single row INSERTs to multi-row INSERTRonald's <a href="http://ronaldbradford.com/blog/the-value-of-multi-insert-values-2009-06-16/">post</a> regarding multi-row INSERTs with MySQL reminded me of a quick utility I wrote about a year ago. I wanted to test for myself how much performance can be gained over singleton INSERTs. The perl script below just takes the output from <span style="font-family: 'courier new';">mysqldump</span> and produces a file for each table, with the file containing the table DDL and the associated INSERT statements. Thought others may find it useful so here it is<br />
<br />
<div>-------- cut here ---------</div><br />
<pre>#!/usr/bin/perl -W
#
##
##------------------------------------------------------------------------
## multi_inserts.pl
## ------------
##
## Take singular INSERT statements and create multi-row INSERT statements.
##------------------------------------------------------------------------
##
# Modification History
# --------------------
# C Sylvester, 3 July 2008, Initial release
use strict;
my $VALUES_PER_INSERT = 50;
my $MAX_ROW_LENGTH = $VALUES_PER_INSERT * 1000;
my $initial_comments = "";
my $table = "";
my $cnt = 0;
my $multi_values = "";
my $multi_row_length = 0;
my $total_row_count = 0;
my $t = "";
my $v = "";
open( PF, $ARGV[0] ) or die "\"$ARGV[0]\": $!\nUsage $0 <mysqldump file="">\n";
LINE: while (<pf>) {
if ( /^INSERT INTO `(\w+)` VALUES (.*);/ ) {
$t = $1;
$v = $2;
if ( $table eq "" ) {
$table = lc ($t).".sql";
open (DDL, '>>', $table) or die "Cannot open file for appending\n$!\n";
}
$multi_values .= $v . ",";
$multi_row_length += length($v);
if ( ++$cnt == $VALUES_PER_INSERT || $multi_row_length >= $MAX_ROW_SIZE) {
$multi_values =~ s/,$/\;/;
print DDL "INSERT INTO ", $t, " VALUES ", $multi_values, "\n";
$total_row_count += $cnt;
$cnt = 0;
$multi_values = "";
$multi_row_length = 0;
}
next LINE;
}
if ( $cnt != 0 ) {
$multi_values =~ s/,$/\;/;
print DDL "INSERT INTO ", $t, " VALUES ", $multi_values, "\n";
$total_row_count += $cnt;
$cnt = 0;
$multi_values = "";
$multi_row_length = 0;
}
if (/-- MySQL dump .*/ ) {
do {
$initial_comments .= $_;
$_ = <pf>;
} until ( /-- Table structure for table .*/ )
}
if ( /-- Table structure for table `(\w+)`/ ) {
$table = lc ($+).".sql";
close(DDL);
open (DDL, '+>', $table) or die "Cannot open file for writing\n$!\n";
print DDL $initial_comments, "\n";
print STDOUT "Printing to ", $table, "\n";
}
print DDL if fileno DDL;
}
if ( $cnt != 0 ) {
$multi_values =~ s/,$/\;/;
$total_row_count += $cnt;
print DDL "INSERT INTO ", $t, " VALUES ", $multi_values, "\n";
}
print STDOUT "Table \"", $t, "\" = ", $total_row_count;
close(DDL);
close(PF);
exit;
</pf></pf></mysqldump></pre><br />
<div>-------- cut here ---------</div><br />
<div>Part of my motivation for doing this was to learn PERL a little better so I'm sure this could be improved :-)</div>Craighttp://www.blogger.com/profile/18259297452579234900noreply@blogger.com0Virginia 20165, USA39.0527088 -77.384119739.0193823 -77.4424847 39.0860353 -77.3257547tag:blogger.com,1999:blog-6857743382661795219.post-35499579907864859152009-06-12T15:39:00.030-04:002013-01-04T15:18:12.547-05:00Using OpenSolaris SMF for managing MySQL Cluster<div>
One of the things I really like about Solaris (and OpenSolaris) is the Services Management Framework (<a href="http://en.wikipedia.org/wiki/Service_Management_Facility">SMF</a>). And one of the things I like about my job is the opportunity to learn and work with new stuff. And, of course, the best way to learn something "non-lethal" is to try it yourself and do something with it.</div>
<div>
<br /></div>
<div>
So, how about using SMF for managing a MySQL Cluster setup? Something simple like two data nodes, one mgmt node, and one MySQL server node running on one OS instance? Sounds like a good exercise and, from a quick search, I couldn't find where it had been done before. Loaded up the latest release of OpenSolaris (<a href="http://www.opensolaris.org/os/downloads/">2009.06</a>) into a <a href="http://www.virtualbox.org/wiki/Downloads">Virtual Box</a> VM, downloaded the latest tar ball of <a href="http://dev.mysql.com/downloads/cluster/7.0.html#Solaris">MySQL Cluster</a> (7.0.5) for Solaris x86 32-bit, kicked off Pandora (gotta have music), and got started.</div>
<div>
<br /></div>
<div>
OK, OS installed and running, time to install Cluster. For simplicity, I put all MySQL related files and data in <span class="Apple-style-span" style="font-family: 'courier new';">/usr/local/mysql/</span>. This included creating the Cluster data directory at <span class="Apple-style-span" style="font-family: 'courier new';">/usr/local/mysql/data_cluster</span>. The only exceptions were the <i>my.cnf</i> and <i>config.ini </i>files, which were put in <span class="Apple-style-span" style="font-family: 'courier new';">/etc/mysql/</span>. All the files I reference are provided in the <a href="ftp://ftp.mysql.com/pub/mysql/download/ndb_smf_files.tar.gz">ndb_smf_files.tar.gz</a> compressed tar file. (If you are already familiar with SMF and are just looking for the files, just grab the archive file above and you can go on your merry way.)</div>
<div style="text-align: center;">
<br /></div>
<div>
Rather than include the full text of the <i>my.cnf</i> file, I will just list the relevant changes:</div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new';"></span></div>
<blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new';">[mysqld]</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new';">...</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new';">ndbcluster</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new';">ndb-connectstring = localhost</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new';">skip-innodb # not needed for my test system</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', fantasy;">...</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"></span><br />
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;">[ndbd]</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;">connect-string = localhost</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;">[ndb_mgm]</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;">connect-string = localhost</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;">[ndb_mgmd]</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;">config-file = /etc/mysql/config.ini</span></div>
<div style="text-align: center;">
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><br /></span></div>
</div>
</blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"></span><br />
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;">For the Cluster configuration file, I just copied the</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">$MYSQL_HOME/support-files/ndb-config-2-node.ini </span>file to <span class="Apple-style-span" style="font-family: 'courier new';">config.ini</span> and changed the data directory parameters:</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';"></span></span></div>
<blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">[ndbd default]</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">...</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">DataDir= /usr/local/mysql/data_cluster</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">...</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">[ndb_mgmd default]</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">DataDir= /usr/local/mysql/data_cluster</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">...</span></span></div>
</blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';"></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;">Now, onto the SMF setup. There are a pair of files for each service we want to manage: a service configuration file and a "scripts" file. For example, the Cluster management service (the <span class="Apple-style-span" style="font-family: 'courier new';">ndb_mgmd</span><i> </i>process) configuration is described in the<b> mysql_ndb_mgmd.xml</b> </span><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;">file and the methods for starting and stopping the service are provided in the <b>mysql_ndb_mgmd</b> script. So, let's download the tar <span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;">file, unpack, and move the configuration and script files to their respective directories. (For the steps below I prefer to open a root shell instead of continually invoking </span><span class="Apple-style-span" style="font-family: 'courier new';">sudo</span><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;"> but do whatever works for you.)</span></span></span></span></div>
<div>
<ol>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;">Grab the <span class="Apple-style-span" style="font-family: Georgia, fantasy;"><i>ndb_smf_files.tar.gz</i> and extract the files:<br /><span class="Apple-style-span" style="font-family: 'courier new';"><blockquote>
root# cd /tmproot# wget https://dl.dropbox.com/u/1880934/ndb_smf_files.tar.gz<br />
root# tar xzf ndb_smf_files.tar.gz</blockquote>
</span></span></span></span></li>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy;">Create the <i>/var/svc/manifest/application/database</i> directory if it does not exist.<span class="Apple-style-span" style="font-family: 'courier new';"><br /><blockquote>
root# mkdir /var/svc/manifest/application/database</blockquote>
</span></span></span></li>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia;">Copy the three service configuration files to <span class="Apple-style-span" style="font-family: Georgia, -webkit-fantasy; font-style: italic;">/var/svc/manifest/application/database</span>:<span class="Apple-style-span" style="font-family: 'courier new';"><br /><blockquote>
root# cd /tmp/ndb_smf_files/svcmanifests<br />
root# cp mysql*.xml /var/svc/manifest/application/database</blockquote>
</span></span></span></li>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">A "request for action" script is similar to an "rc" script and is used for starting/stopping/restarting a service. Copy the three scripts to the <i>/lib/svc/method</i> directory and make sure they are executable:<span class="Apple-style-span" style="font-family: 'courier new';"><br /><blockquote>
root# cp /tmp/ndb_smf_files/svcmethods/mysql* /lib/svc/method<br />
root# chmod +x /lib/svc/method/mysql*</blockquote>
</span></span></span></li>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia;">N</span><span class="Apple-style-span" style="font-family: georgia;">o</span><span class="Apple-style-span" style="font-family: georgia;">w time to get the manifests into the SMF repository. For this step, we use the <b><span class="Apple-style-span" style="font-family: 'courier new';">svccfg</span></b> utility to import the XML files:</span>:<span class="Apple-style-span" style="font-family: 'courier new';"><br /><blockquote>
root# cd /var/svc/manifest/application/database<br />
root# svccfg import mysql.xml<br />
root# svccfg import mysql_ndb_mgmd.xml<br />
root# svccfg import mysql_ndbd.xml</blockquote>
</span></span></li>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">Create the Cluster data directory.</span><span class="Apple-style-span" style="font-family: 'courier new';"><br /><blockquote>
root# mkdir /usr/local/mysql/data_cluster<br />
root# chown mysql:mysql /usr/local/mysql/data_cluster<br />
root# chmod 750 /usr/local/mysql/data_cluster</blockquote>
</span></span></li>
<li><span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">If you are not familiar with SMF, this is a good time to take a look at the xml files. A few things to note are the dependencies defined, the methods that will be invoked to start and stop the service (which shows the relationship to the files we moved to </span><span class="Apple-style-span" style="font-family: 'courier new';">/lib/svc/method</span><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">), the method_credential (which user to use to start the service), and the properties defined (which can be referenced from the methods script).</span></span></li>
</ol>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">For Cluster, there is a defined order of the processes that need to be started. Si<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">nce the NDB Mgmt server controls the configuration of the Cluster, it must be started before the NDBD (data node) processes. And if you are using the MySQL server to access the Cluster, the data nodes need to be up and running before starting the </span><span class="Apple-style-span" style="font-family: 'courier new';">mysqld</span><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"> process(es). So, for the data nodes (</span><span class="Apple-style-span" style="font-family: 'courier new';">mysql_ndbd.xml</span><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"> file), we see:</span></span></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><br /></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhp1pshEjU7Qca0FBpHmpRJWp9eMmwPpthdk0Q7ZE8f3ED8_x8nxWNbeOHkmskZcy3b8sgNuh21FhjkTJ3CfJGXRzm_7exC8JtRceqj4xYPV_yq8LU455njVkVjUTw2hGqM2fX7neDLGfM/s1600-h/ndbd.jpg" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5347998094714625362" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhp1pshEjU7Qca0FBpHmpRJWp9eMmwPpthdk0Q7ZE8f3ED8_x8nxWNbeOHkmskZcy3b8sgNuh21FhjkTJ3CfJGXRzm_7exC8JtRceqj4xYPV_yq8LU455njVkVjUTw2hGqM2fX7neDLGfM/s400/ndbd.jpg" style="cursor: hand; cursor: pointer; display: block; height: 103px; margin: 0px auto 10px; text-align: center; width: 400px;" /></a></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">which means that before the data nodes can start, the mgmt server must be up and running. In the MySQL server service configuration file, the dependencies are the network, file system, and the Cluster data nodes. From looking at the data node dependencies, just make sure that at least one of the two nodes is running:</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><br /></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAgyLZYB3S7LNyzh3CWGZp4aEarwkGBViIyMDgw4aN-LcMFWO-xdfjXG8ZR99XcUwb5gp9fFiWE8J1-cPVKd2Pup1CC58RrZd1y6PKzFOjzor0Ux7K0vuJNXJMkCmSiYIOIH8yXkrNPpU/s1600-h/mysql.jpg" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5347996748148145490" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAgyLZYB3S7LNyzh3CWGZp4aEarwkGBViIyMDgw4aN-LcMFWO-xdfjXG8ZR99XcUwb5gp9fFiWE8J1-cPVKd2Pup1CC58RrZd1y6PKzFOjzor0Ux7K0vuJNXJMkCmSiYIOIH8yXkrNPpU/s400/mysql.jpg" style="cursor: hand; cursor: pointer; display: block; height: 111px; margin: 0px auto 10px; text-align: center; width: 400px;" /></a></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">Ok, everything should be ready to go so let's start things up. Start the Cluster mgmt server and verify it is up and running:</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"></span></span></div>
<blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcadm enable mysql_ndb_mgmd</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcs -l mysql_ndb_mgmd</span></span></div>
</blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">In the svcs listing, you should see "enabled true" and "state online". If not, run "svcs -x mysql_ndb_mgmd" and check the log listed for the reason why (logs are in the </span><span class="Apple-style-span" style="font-family: 'courier new';">/var/svc/log</span><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"> directory).</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><br /></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">If everything looks good, start the data nodes and the MySQL server:</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"></span></span></div>
<blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcadm enable mysql_ndbd:node1</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcadm enable mysql_ndbd:node2</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcs -l mysql_ndbd</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcadm enable mysql</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: 'courier new';">root# svcs -l mysql</span></span></div>
</blockquote>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">That's it! Hopefully everything worked (and if not, that's what man pages and cursing are for). To stop a service, just "disable" instead of "enable".</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><br /></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">Next thing to try is dynamically adding nodes to Cluster and seeing how SMF handles services on other servers.</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"><br /></span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;">-- Craig</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'courier new', -webkit-fantasy;"><span class="Apple-style-span" style="font-family: georgia, -webkit-fantasy;"></span></span></div>
</div>
Craighttp://www.blogger.com/profile/18259297452579234900noreply@blogger.com3