Ronald Bradford

In some environments using a distro package management system may* provide benefits including handling dependencies and providing a simpler approach when there are no dedicated DBA or SA resources.

However, the incorrect use can result in pain and in this instance production downtime. Even with dedicated resources at an unnamed premium managed hosting provider, the simple mistake of assumption resulted in over 30 minutes of unplanned downtime during peak time.

One of the disadvantages of using a system such as rpm is the lack of control in managing the starting and stopping of your MySQL instance, and the second is unanticipated package dependency upgrades.

So what happened with this client. When attempting to use the MySQL client on the production server, I got the following error.

$ mysql -uxxx -p error while loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory

The server was running MySQL 5.0.27 via an rpm install.

$ rpm -qa | grep -i mysql MySQL-server-standard-5.0.27-0.rhel3 MySQL-shared-standard-5.0.27-0.rhel3 MySQL-devel-standard-5.0.27-0.rhel3 MySQL-shared-compat-5.0.27-0.rhel3 MySQL-client-standard-5.0.27-0.rhel3

With no access to this managed server the information was relayed to the hosting provider and some time later we found the production website down. Some 30 minutes later we found that to fix the rpm problem, a dependency upgrade has also caused an automatic upgrade from 5.0.27 to 5.0.88.

While upgrading is not necessarily a bad thing, the lack of planning including a backup, a scheduled window of downtime and any level of testing is simply a poor cowboy approach to DBA management.

Unlike previous years when the number of conferences with MySQL content diminishes after the O’Reilly MySQL and OSCON conferences (Open SQL Camp excluded), this year has a lot on offer.

This month:

• Open SQL Camp Europe Edition on Saturday 21st and Sunday 22nd August

Upcoming next month in September:

• MySQL Sunday at Oracle Open World on September 18 in San Francisco includes 4 tracks and around 15 quality speakers. (Big numbers of attendees also rumored but yet unconfirmed).
• The inaugural Surge Scalability conference in Baltimore will include presentations by myself and Baron Schwartz (Percona being sponsors) as well as talks from other popular sites using MySQL.

If your in SF for the MySQL Sunday you may also want to come for the SF MySQL Meetup on the preceeding Thursday night where I’ll be giving my talk on “Common MySQL Scalability problems, and how to fix them”.

In October:

• Open SQL Camp in Boston from Friday, Oct 15th in the evening, ending Sunday Oct 17th

Europeans will be busy in November where you will find dedicated MySQL tracks with multiple speakers at DOAG and UKOUG. Other MySQL talks can be found at SAPO Codebits 2010 and BGOUG.

And for South America, stay tuned. October will be your month!

There is also a great event calendar maintained by the MySQL community team on the Forge.

A common observation for many LAMP stack products is the use of poor MySQL security practices. Even for more established products such as Wordpress don’t always assume that the provided documentation does what it best for you. As per my earlier posts where I detailed installation instructions and optimal permissions for both WordPress and Mediawiki, and not just directed readers to online documentation.

In this post I will detail why GRANT ALL is bad.

Let’s start with what GRANT ALL [PRIVILEGES] provides. As per the MySQL 5.1 Reference Manual you get the following:

ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGE

I am going to focus on just one privilege that is included with ALL, and that is SUPER. This privilege can do the following which can be destructive for an application level user:

• Bypasses read_only
• Bypasses init_connect
• Can Disable binary logging
• Change configuration dynamically
• No reserved connection

User Permissions

This is how a user should be created, granting only the required permissions to a given schema.

CREATE USER goodguy@localhost IDENTIFIED BY 'sakila'; GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON odtug.* TO goodguy@localhost;

This is what is commonly seen.

CREATE USER superman@'%'; GRANT ALL ON *.* TO superman@'%'; Bypasses read_only

Many MySQL replication environments rely on ensuring the MySQL slave is consistent with the master. Did you know that an application can bypass this security when read_only=true is used?

$ mysql -ugoodguy -psakila odtug mysql> insert into test1(id) values(1); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement $ mysql -usuperman odtug mysql> insert into test1(id) values(1); Query OK, 1 row affected (0.01 sec)

GRANT ALL is bad for data consistency.

Bybasses init_connect

A common practices used for UTF8 communications is to use the init_connect configuration variable.

#my.cnf [client] init_connect=SET NAMES utf8 $ mysql -ugoodguy -psakila odtug mysql> SHOW SESSION VARIABLES LIKE 'ch%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | +--------------------------+----------+ $ mysql -usuperman odtug mysql> SHOW SESSION VARIABLES LIKE 'character%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | +--------------------------+----------+

GRANT ALL is bad for data integrity.

Disables Binary Logging. $ mysql -usuperman odtug mysql> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | binary-log.000001 | 354 | | | +-------------------+----------+--------------+------------------+ mysql> DROP TABLE time_zone_leap_second; mysql> SET SQL_LOG_BIN=0; mysql> DROP TABLE time_zone_name; mysql> SET SQL_LOG_BIN=1; mysql> DROP TABLE time_zone_transition; mysql> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | binary-log.000001 | 674 | | | +-------------------+----------+--------------+------------------+ $ mysqlbinlog binary-log.000001 --start-position=354 --stop-position=674 # at 354 #100604 18:00:08 server id 1 end_log_pos 450 Query thread_id=1 exec_time=0 error_code=0 use mysql/*!*/; SET TIMESTAMP=1275688808/*!*/; DROP TABLE time_zone_leap_second /*!*/; # at 579 #100604 18:04:31 server id 1 end_log_pos 674 Query thread_id=2 exec_time=0 error_code=0 use mysql/*!*/; SET TIMESTAMP=1275689071/*!*/; DROP TABLE time_zone_transition /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */;

Should that statement be run on MySQL Slaves?
Is the binary log used for any level of auditing?

GRANT ALL is bad for slave consistency.

The reserved connection

MySQL reserved one connection for an administrator to be able to login to a server. For example.

$ mysql -uroot mysql> show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 3 | +-----------------+-------+ 1 row in set (0.07 sec) mysql> show global status like 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 4 | +-------------------+-------+ mysql> SHOW PROCESSLIST; +----+------+-----------+-------+---------+------+------------+--------------- | Id | User | Host | db | Command | Time | State | Info +----+------+-----------+-------+---------+------+------------+--------------- | 13 | good | localhost | odtug | Query | 144 | User sleep | UPDATE test1 ... | 14 | good | localhost | odtug | Query | 116 | Locked | select * from test1 | 15 | good | localhost | odtug | Query | 89 | Locked | select * from test1 | 15 | root | localhost | odtug | Query | 89 | Locked | SHOW PROCESSLIST

However if all application users are already using the SUPER privilege, the administrator will get.

$ mysql -uroot ERROR 1040 (HY000): Too many connections

There is no way to be able to login and see what’s happening, or kill threads for example. In this case you either wait, or you are required to kill the mysqld process, or fine the client threads to kill. The result of the former may lead to a corrupt database requiring additional recovery.

GRANT ALL is bad for system administration and monitoring.

Conclusion

Don’t use GRANT ALL for application users. For more information, including why I only listed just 5 issues, check out my MySQL Idiosyncrasies that BITE presentation. I will also be presenting this talk at MySQL Sunday at Oracle Open World in September.

Within a few hours my post Installing Mediawiki on Oracle Enterprise Linux LAMP stack got 5 negative votes.

Wow, I’d be glad if these people could felt so passionately about all the other CRUD on Planet MySQL that has ZERO to do actually do with MySQL.

Using a LAMP product, and providing instructions for operation can’t be a negative voting offense. So it can only be the words “Oracle Enterprise Linux”.

For those negative people out there that care enough to physically mark blogs let me share some facts with you. RedHat Enterprise Linux (RHEL) is the most widely used and support platform for production MySQL environments. CentOS and Oracle Enterprise Linux (OEL) provide via the freedom of Open Source, their own offerings of RHEL with various other features including support or specific additional features for other products. Oracle also provides and commits work to the Linux Kernel, so they are just as much an allies to open source as other companies.

So are you bagging the operating system or just the word Oracle. Oracle is hear to stay, in fact those attending the MySQL Sunday event at Oracle Open World may get a welcome wake up shock. And for reference these articles are being written for a published Oracle Magazine article which meets the comfort level of the reader and introduces MySQL in a positive way. Your actions show just how much you are unwilling to embrace the larger community.

A company wiki can be easily configured in under 10 minutes using Mediawiki the open source LAMP software that powers the top 10 website Wikipedia.

A company wiki is an ideal means for a centralized and user contributed documentation system. The following steps show you how to download, configure and get your Mediawiki site operational.

Software Pre-Requisites

• Installing MySQL on Oracle Enterprise Linux
• Installing a LAMP Stack on Oracle Enterprise Linux

Software Installation su - cd /tmp wget http://download.wikimedia.org/mediawiki/1.16/mediawiki-1.16.0.tar.gz cd /var/www/html tar xfz /tmp/mediawiki*.tar.gz mv mediawiki* wiki chmod 777 wiki/config

NOTE: You should check the Mediawiki Downloads page for the latest version.

You can now visit http://localhost/wiki and you will be presented with a message of an un-configured Mediawiki environment. You can streamline the MySQL portion of this configuration with the following commands.

mysql -uroot -p -e "DROP SCHEMA IF EXISTS wikidb;CREATE SCHEMA wikidb" mysql -uroot -p -e "CREATE USER wikiuser @localhost IDENTIFIED BY 'sakila'" mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX on wikidb.* TO wikiuser@localhost"

You can now complete the configuration on the Configuration Page. You will need to enter the following information.

• Wiki name = Example Wiki
• Contact e-mail = your email address
• Admin user password = enter password
• DB password = sakila

After successful installation, one additional step is needed.

mv wiki/config/LocalSettings.php wiki/ chmod 400 wiki/LocalSettings.php chmod 500 wiki/config

You can now start using your new Wiki at http://localhost/wiki. You will find documentation at the links provided on the displayed home page and also at www.mediawiki.org.

MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -uwikiuser -psakila blog mysql> show tables; +-------------------+ | Tables_in_wikidb | +-------------------+ | archive | | category | | categorylinks | | change_tag | | external_user | | externallinks | | filearchive | | hitcounter | | image | | imagelinks | | interwiki | | ipblocks | | job | | l10n_cache | | langlinks | | log_search | | logging | | math | | objectcache | | oldimage | | page | | page_props | | page_restrictions | | pagelinks | | protected_titles | | querycache | | querycache_info | | querycachetwo | | recentchanges | | redirect | | revision | | searchindex | | site_stats | | tag_summary | | templatelinks | | text | | trackbacks | | transcache | | updatelog | | user | | user_groups | | user_newtalk | | user_properties | | valid_tag | | watchlist | +-------------------+ 45 rows in set (0.00 sec) mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'wikidb'; +-------------------+--------+------------+ | table_name | engine | table_rows | +-------------------+--------+------------+ | archive | InnoDB | 0 | | category | InnoDB | 0 | | categorylinks | InnoDB | 0 | | change_tag | InnoDB | 0 | | external_user | InnoDB | 0 | | externallinks | InnoDB | 0 | | filearchive | InnoDB | 0 | | hitcounter | MEMORY | 0 | | image | InnoDB | 0 | | imagelinks | InnoDB | 0 | | interwiki | InnoDB | 95 | | ipblocks | InnoDB | 0 | | job | InnoDB | 0 | | l10n_cache | InnoDB | 3686 | | langlinks | InnoDB | 0 | | log_search | InnoDB | 0 | | logging | InnoDB | 0 | | math | InnoDB | 0 | | objectcache | InnoDB | 2 | | oldimage | InnoDB | 0 | | page | InnoDB | 1 | | page_props | InnoDB | 0 | | page_restrictions | InnoDB | 0 | | pagelinks | InnoDB | 0 | | protected_titles | InnoDB | 0 | | querycache | InnoDB | 0 | | querycache_info | InnoDB | 0 | | querycachetwo | InnoDB | 0 | | recentchanges | InnoDB | 0 | | redirect | InnoDB | 0 | | revision | InnoDB | 1 | | searchindex | MyISAM | 0 | | site_stats | InnoDB | 1 | | tag_summary | InnoDB | 0 | | templatelinks | InnoDB | 0 | | text | InnoDB | 1 | | trackbacks | InnoDB | 0 | | transcache | InnoDB | 0 | | updatelog | InnoDB | 0 | | user | InnoDB | 1 | | user_groups | InnoDB | 2 | | user_newtalk | InnoDB | 0 | | user_properties | InnoDB | 0 | | valid_tag | InnoDB | 0 | | watchlist | InnoDB | 0 | +-------------------+--------+------------+ 45 rows in set (0.01 sec)

Sort URL for this post rb42.com/oel-install-mediawiki

In the last few days there has been some press of the Augen gentouch 7″ Tablet. A new cheap tablet that is running Android 2.1.

Image from http://android-devices.net

There were a few primary motivations for getting one, the first being price, at $150 I consider cheap for a small tablet, and second it runs Android, something I’m wanting to play more with. I have an original Google G1, and I also purchased one recently for my fiance.

So what are my first impressions.

1. First it was difficult to get. It is only available at KMart it seems. My local KMart in New York City didn’t have any. Apparently they sold out very quickly with stock on Wednesday. 10 KMart stores later (via phoning them) I found just 1 more that had even received stock, and they had 1 left. Lucky last. It was only that we had a car on Saturday it was even possible to get to this store. (-1 for ease of acquisition via company distribution)
2. KMart claimed the price was $165, but if you checked the online magazine it was $149.99. I had to prove that first to get the price via using the web on my phone. (-1 for Kmart customer service)
3. I wanted to buy and additional memory card as I knew from online review this was available however the box (which was still unopened before purchase only mentions Expansion Memory Card Slot, it doesn’t mention the actually size format. Even reading the manual after purchase talks about a SD card or a TF card, but is not specific. I ended up purchasing a Sandisk Mobile microSDHC 16GB card. Even that packaging was confusing as it clearly states “For Mobile Phones” twice on the package. (-1 for ease of information/packaging)
4. Turning on the first time was a pleasant surprise because the item was fully charged. (+2 there). With my MiFi I was immediately able to connect to the web while still in the car (as a passenger). (+1 for access to primary use)
5. The touch screen is clearly not as responsive as an Apple or smart phone, it’s mixed sometimes it only requires a light touch, sometimes a heavy touch. Early reports mentioned this, so it was not unexpected. Again for the price and proposed uses I have for it, it wasn’t a deal breaker. I saw one online video that included a stylus, and another mention online, however the manual gives no indication, and I don’t seem to have one.
6. Downloading the 2012 HD trailer via YouTube worked promptly and without issues and picture was good (+1) but there is no external controls for audio. (-1 for that).
7. There is a headphone jack however I find out later that this is a 2.5mm jack, and standard headphones used by everybody on the planet is 3.5mm. (-1 for that). The website claims they will supply people at no cost a set of 2.5mm headphones. Again useless as I don’t want to have to carry those around as well. What I want is a 2.5mm to 3.5mm converter, actually two because I’m sure I’ll lose it easily. (-1 for that)
8. The main buttons for “back, menu, home” are actually on the back of the device. Once you know that it’s not that bad, but it is a little odd. Of course for right handed person. If they were on the side it’s possible any type of “death grip” may accidentally press them.
9. Unit comes with a handy and practical leather case (+1) with adequate access for side controls, however it’s only good for holding not using because the buttons are on the back (-1).
10. By purchasing a 16GB at $99, the price is now $250, it’s not as impressive a cost product.
11. The purchased SDCard was not easy to install, there is no clear instruction on right side up and certainly nothing in the docs, and there are reports online that people misplace in the slot and the card gets lost inside of unit. I didn’t have that problem and I don’t really see that would happen for me. The manual indicates you will get an icon about it, however that wasn’t the case so I really didn’t know if it worked or not. (-1) Using the AndExplorer indicates a /sdcard so I assume it’s operational.
12. There is reference to a U Disk (who knows what that is). with a supplied cable I assume it’s a means of adding a USB thumbdrive to the mini USB slot. Trying this however didn’t seem to work so I don’t know if that’s the intended use.
13. The power supply is yet another plug I have to now carry (along with one for laptop, phone, mifi). I was really hoping the power adapter would have been a mini or micro USB. (-1)
14. The first real use after web, video, google maps, email is to download other stuff however it seems the Android Market is broken. The official website (which I now can’t find, -1 for poor SEO and google searchability) makes a note this is broken and is expecting a patch (-2 for poor testing there.
15. The manual was not proof read by an English person. The title cover states “Table” not “Tablet” however the funniest part was reading some the manual. I will not type what I found, but you can see the image below. It so made me laugh.
16. There is no video output, which is really annoying because the side panel actually states HDMI in printing, but nothing physical.
17. The screen res is 800×480. Ok, so it’s small but of the sites I’ve initially visited the horizontal bar has not been an issue. I suspect it may be in the future, but this is not my primary development machine.
18. No webcam
19. I wanted to download some of my ebooks, however this seems to be broken and related to the market problem. Will see after patch.

I’ve yet to really test it out, these are just my first impressions. For the price I consider it a worthwhile investment for the purposes I want it for. That is some browsing, (ideal for bedroom), I can see it a wicked 7″ GPS unit with turn by turn controls for driving, but without GPS it will be a bit manual. Reading email, or an ebook, and even use as a large digital frame, especially for my photos.

This is a gen 1 product, so you have to accept the shortcomings. You are either an alpha adopter that is willing to accept limitations and accept the benefits it does have or your not.

Other References:

Augen’s $150 Android tablet hits Kmart circular, coming to stores later this week (what first caught my eye). KMart update. First impressions of the Augen GenTouch78 Android tablet (with second mention of a stylus).

I get asked this question often. It was mentioned again recently in a NYTECH executive breakfast with RedHat CIO Lee Congdon.

The short answer is No.

There is clear evidence that in the short to medium term Oracle will continue to promote and enhance MySQL. Some of these indicators include:

• EU 10 point commitment in December 2009 – See Oracle Makes Commitments to Customers, Developers and Users of MySQL
• MySQL Conference April 2010 – Opening keynote by Edward Screven State of the Dolphin
• Oracle Magazine Jul/Aug 2010 – Interview with Edward Screven Open for Business.

It is clear from these sources that Oracle intends to incorporate MySQL into Oracle Backup and Security Vault products. Both a practical and necessary step. There is also a clear mention of focusing on the Microsoft platform, a clear indicator that SQL Server is in their sights without actually saying it.

What is unknown is exact how and when features will be implemented. Also important is how much these may cost the end user. Oracle is in the business of selling, now an entire H/W and S/W stack. They also have a complicated pricing model of different components with product offerings. I assume this will continue. There are already two indications, InnoDBbackup included for Enterprise Backup (from April Keynote) and 5.1 enterprise split. (Note: while this split may have existed prior to Oracle, it is now more clearly obvious).

MySQL can never be seen as drawing away from any Oracle sales of the core entry level database product. It is likely Oracle will provide a SQL Syntax compatibility layer for MySQL within 2 years, however it will I’m sure be a commercial add-on. Likewise, I would suspect a PL/SQL lite layer within 5 years, but again at a significant cost to offset the potential loss of sales in the low end of the server market. There continues to be active development in the MySQL Enterprise Monitor, MySQL Workbench and MySQL Connectors which is all excellent news for users.

Moving forward, how long will this ancillary development of free tools continue? What will happen to the commercial storage engine, OEM and licensing model after the 5 year commitment? How will the MySQL ecosystem survive.? There is active development in Percona, MariaDB and Drizzle forks, however unless all players that want to provide a close MySQL compatible solution work together, progress will continue to be a disappointing disjointed approach. The 2011 conference season will also see a clear line with competing MySQL conferences in April scheduled at the same time, the O’Reilly MySQL conference in Santa Clara California and the Oracle supported(*) Collaborate 2011 in Orlando, Florida.

I have a number of predictions on what Oracle ME MySQL may look like in 5 years however this is a topic for a personal discussion.

I will be joining a great list of quality speakers including John Allspaw, Theo Schlossnagle, Rasmus Lerdorf and Tom Cook at Surge 2010 in Baltimore, Maryland on Thu 30 Sep, and Fri Oct 1st 2010.

My presentation on “The most common MySQL scalability mistakes, and how to avoid them.” will include discussing various experiences observed in the field as a MySQL Consultant and MySQL Performance Tuning expert.

Abstract:

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environments to provide a suitable architecture and application design to support these essential needs.

Overview:

Some details of the presentation would include:

• The different types of accessible data  (e.g. R/W, R, none)
• What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
• The three components of scalability – Read Scalability/Write Scalability/Caching
• Design practices for increasing scalability and not physical resources
• Disaster is inevitable. Having a tested and functional failover strategy
• When other products are better (e.g. Static files, Session management via Key/Value store)
• What a lack of accurate monitoring causes
• What a lack of breakability testing causes
• What does “No Downtime” mean to your organization.
• Implementing a successful “failed whale” approach with preemptive analysis
• Identifying when MySQL is not your bottleneck

I recently gave a webinar to the LAOUC and NZOUG user groups on MySQL Idiosyncrasies that BITE.

For the benefit of many viewers that do not use English as a first language my slides include some additional information from my ODTUG Kaleidoscope presentation in June.

Thanks to Francisco Munoz Alvarez for organizing.

MySQL Idiosyncrasies That Bite 2010.07 View more presentations from Ronald Bradford.

If you have not looked at my recent presentation that I presented at ODTUG Kaleidoscope 2010, then feel free to join me on Tuesday, July 27, 2010 6:00 PM – 7:00 PM EDT tomorrow at 5pm EDT when I will giving a webinar on this talk. You can register online at https://www1.gotomeeting.com/register/730452824

The IOUG Online Education Series: Get Real with Upgrades will include next week 3 different MySQL webinars. These are:

• MySQL 5.1: Why and How to Upgrade by Sheeri Cabral on Tuesday, July 27, 12:00 p.m. – 1:00 p.m. CT
• MySQL Upgrades With No Downtime by Sean Hull on Wednesday, July 28, 12:00 p.m. – 1:00 p.m. CT
• MySQL Upgrade Best Practices by Matt Yonkovit on Thursday, July 29, 12:00 p.m. – 1:00 p.m. CT

Each speaker will be covering different areas and reasons for considering and implementing a successful MySQL Upgrade.

A company blog can be easily configured in under 10 minutes using Wordpress, a popular open source LAMP product that runs a reported 12+ million blogs including those found at CNN, NY Times, Wall Street Journal (WSJ), ZDNet, MTV, People Magazine, Playstation and eBay.

A company blog is a great way for the dissemination of information to your user base as well as enabling a means of user feedback via comments.

The following steps show you how to download, configure and get your Wordpress blog operational.

Software Pre-Requisites

• Installing MySQL on Oracle Enterprise Linux
• Installing a LAMP Stack on Oracle Enterprise Linux

Software Installation su - cd /tmp wget http://wordpress.org/latest.tar.gz cd /var/www/html tar xfz /tmp/latest.tar.gz mv wordpress blog

You can now visit http://localhost/blog and you will be presented with a message of an un-configured Wordpress environment. You can streamline the MySQL portion of this configuration with the following commands.

cd blog sed -e "s/database_name_here/blog/;s/username_here/blog_user/;s/password_here/sakila/" wp-config-sample.php > wp-config.php mysql -uroot -p -e "CREATE SCHEMA blog" mysql -uroot -p -e "CREATE USER blog_user @localhost IDENTIFIED BY 'sakila'" mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE on blog.* TO blog_user@localhost"

Returning now to http://localhost/blog you simply only need to specify a Title, password and email address, click Save and your Blog at http://localhost/blog is complete and operational.

MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -ublog_user -psakila blog mysql> show tables; +-----------------------+ | Tables_in_blog | +-----------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_taxonomy | | wp_terms | | wp_usermeta | | wp_users | +-----------------------+ 11 rows in set (0.00 sec) mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'blog'; +-----------------------+--------+------------+ | table_name | engine | table_rows | +-----------------------+--------+------------+ | wp_commentmeta | MyISAM | 0 | | wp_comments | MyISAM | 1 | | wp_links | MyISAM | 7 | | wp_options | MyISAM | 109 | | wp_postmeta | MyISAM | 1 | | wp_posts | MyISAM | 2 | | wp_term_relationships | MyISAM | 8 | | wp_term_taxonomy | MyISAM | 2 | | wp_terms | MyISAM | 2 | | wp_usermeta | MyISAM | 13 | | wp_users | MyISAM | 1 | +-----------------------+--------+------------+ 11 rows in set (0.00 sec) Additional References

• Installing Wordpress
• Wordpress Client Showcase

Short URL for this post rb42.com/oel-install-wordpress

After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.

User Management

By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.

$ grep mysql /etc/{passwd,shadow,group} /etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash /etc/shadow:mysql:!!:14796:::::: /etc/group:mysql:x:27: Binaries

MySQL binaries are found in /usr/bin.

$ ls -l /usr/bin/mysql* -rwxr-xr-x 1 root root 314568 Feb 16 17:45 /usr/bin/mysql -rwxr-xr-x 1 root root 110776 Feb 16 14:39 /usr/bin/mysqlaccess -rwxr-xr-x 1 root root 35144 Feb 16 17:45 /usr/bin/mysqladmin -rwxr-xr-x 1 root root 112944 Feb 16 17:45 /usr/bin/mysqlbinlog -rwxr-xr-x 1 root root 7632 Feb 16 17:45 /usr/bin/mysqlbug -rwxr-xr-x 1 root root 30576 Feb 16 17:45 /usr/bin/mysqlcheck -rwxr-xr-x 1 root root 7632 Feb 16 17:45 /usr/bin/mysql_config -rwxr-xr-x 1 root root 3670 Feb 16 17:44 /usr/bin/mysql_convert_table_format -rwxr-xr-x 1 root root 22522 Feb 16 17:44 /usr/bin/mysqld_multi -rwxr-xr-x 1 root root 13073 Feb 16 17:44 /usr/bin/mysqld_safe -rwxr-xr-x 1 root root 75184 Feb 16 17:45 /usr/bin/mysqldump -rwxr-xr-x 1 root root 6356 Feb 16 17:44 /usr/bin/mysqldumpslow -rwxr-xr-x 1 root root 11648 Feb 16 17:44 /usr/bin/mysql_explain_log -rwxr-xr-x 1 root root 3245 Feb 16 14:39 /usr/bin/mysql_find_rows -rwxr-xr-x 1 root root 483 Feb 16 17:44 /usr/bin/mysql_fix_extensions -rwxr-xr-x 1 root root 5834 Feb 16 17:44 /usr/bin/mysql_fix_privilege_tables -rwxr-xr-x 1 root root 31431 Feb 16 17:44 /usr/bin/mysqlhotcopy -rwxr-xr-x 1 root root 26160 Feb 16 17:45 /usr/bin/mysqlimport -rwxr-xr-x 1 root root 13659 Feb 16 17:44 /usr/bin/mysql_install_db -rwxr-xr-x 1 root root 6586 Feb 16 17:44 /usr/bin/mysql_secure_installation -rwxr-xr-x 1 root root 16687 Feb 16 17:44 /usr/bin/mysql_setpermission -rwxr-xr-x 1 root root 28224 Feb 16 17:45 /usr/bin/mysqlshow -rwxr-xr-x 1 root root 14473 Feb 16 14:39 /usr/bin/mysql_tableinfo -rwxr-xr-x 1 root root 158192 Feb 16 17:45 /usr/bin/mysqltest -rwxr-xr-x 1 root root 42360 Feb 16 17:45 /usr/bin/mysqltestmanager -rwxr-xr-x 1 root root 15464 Feb 16 17:45 /usr/bin/mysqltestmanagerc -rwxr-xr-x 1 root root 13448 Feb 16 17:45 /usr/bin/mysqltestmanager-pwgen -rwxr-xr-x 1 root root 1312064 Feb 16 17:45 /usr/bin/mysql_tzinfo_to_sql -rwxr-xr-x 1 root root 54160 Feb 16 17:45 /usr/bin/mysql_upgrade -rwxr-xr-x 1 root root 5753 Feb 16 17:44 /usr/bin/mysql_upgrade_shell -rwxr-xr-x 1 root root 112136 Feb 16 17:45 /usr/bin/mysql_waitpid -rwxr-xr-x 1 root root 3818 Feb 16 17:44 /usr/bin/mysql_zap

The mysqld binary is found in /usr/libexec

Error Log

The MySQL error log is found in /var/log/mysqld.log

The content after an initial start of MySQL will look similar to:

cat /var/log/mysqld.log 100705 22:09:03 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 100705 22:09:03 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 100705 22:09:03 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 100705 22:09:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 100705 22:09:03 InnoDB: Started; log sequence number 0 0 100705 22:09:03 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

On the first invocation of MySQL, the InnoDB storage engine will create a default tablespace and redo logs. This is the majority of messages in the above log.

Processes

MySQL is a multi-threaded single process called mysqld. A second wrapper process mysqld_safe is generally found. This process logs stderr and also will restart the mysqld process if not found.

ps -ef | grep mysql root 14733 1 0 Jul05 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql mysql 14783 14733 0 Jul05 pts/1 00:00:10 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock Memory Usage

MySQL can have a very low memory footprint. By default the mysqld process has a 175M virtual size.

$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql" PID COMMAND RSS VSZ USER COMMAND 14275 grep 720 61136 root grep -e RSS -e mysql 14733 mysqld_s 1192 63820 root /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql 14783 mysqld 27004 179496 mysql /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock Disk Usage

The MySQL data files will be stored on a default installation in /var/lib/mysql

$ du -sh /var/lib/mysql 22M /var/lib/mysql $ ls -ld /var/lib/mysql drwxr-xr-x 4 mysql mysql 4096 Jul 13 11:50 /var/lib/mysql $ ls -l /var/lib/mysql total 20552 -rw-rw---- 1 mysql mysql 10485760 Jul 5 22:09 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jul 5 22:09 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jul 5 22:09 ib_logfile1 drwx------ 2 mysql mysql 4096 Jul 5 22:09 mysql srwxrwxrwx 1 mysql mysql 0 Jul 5 22:09 mysql.sock

The MySQL data directory includes the InnoDB tablespace datafile (ibdata1), redo logs (ib_logfile?), and the mysql directory corresponding to the mysql schema containing instance meta data.

This directory also contains the socket file, which is actually a poor location as this opens the security of this directory for world access. This will be discussed later in securing your installation.

Running MySQL

The best means of controlling the starting and stopping of mysql is to use the provided service init script mysqld

$ ls -l /etc/init.d/mysqld -rwxr-xr-x 1 root root 4286 Feb 16 17:45 /etc/init.d/mysqld Configuration

For OEL the MySQL configuration can be found in /etc.
NOTE: MySQL can use multiple configuration files.

$ ls -l /etc/my.cnf -rw-r--r-- 1 root root 441 Feb 16 14:39 /etc/my.cnf

MySQL includes a minimalistic configuration file by default. The configuration file format is variable=value pairs for a given number of different sections, in this file [mysqld] and [mysqld_safe].

$ cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Audit

A full audit of all MySQL related files.

find / -name "*mysql*" /etc/rc.d/rc3.d/S64mysqld /etc/rc.d/rc5.d/S64mysqld /etc/rc.d/rc6.d/K36mysqld /etc/rc.d/init.d/mysqld /etc/rc.d/rc0.d/K36mysqld /etc/rc.d/rc4.d/S64mysqld /etc/rc.d/rc1.d/K36mysqld /etc/rc.d/rc2.d/S64mysqld /etc/php.d/pdo_mysql.ini /etc/php.d/mysql.ini /etc/php.d/mysqli.ini /etc/ld.so.conf.d/mysql-x86_64.conf /etc/ld.so.conf.d/mysql-i386.conf /usr/lib64/mysql /usr/lib64/mysql/mysqlbug /usr/lib64/mysql/libmysqlclient_r.so.15.0.0 /usr/lib64/mysql/libmysqlclient.so.15 /usr/lib64/mysql/libmysqlclient_r.so.15 /usr/lib64/mysql/mysql_config /usr/lib64/mysql/libmysqlclient.so.15.0.0 /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Bundle/DBD/mysql.pm /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm /usr/lib64/php/modules/mysql.so /usr/lib64/php/modules/pdo_mysql.so /usr/lib64/php/modules/mysqli.so /usr/libexec/mysqld /usr/libexec/mysqlmanager /usr/share/mysql /usr/share/mysql/mysql_system_tables.sql /usr/share/mysql/mysql_system_tables_data.sql /usr/share/mysql/mysql_fix_privilege_tables.sql /usr/share/mysql/mysql_test_data_timezone.sql /usr/share/vim/vim70/syntax/mysql.vim /usr/share/man/man8/mysqld.8.gz /usr/share/man/man8/mysqlmanager.8.gz /usr/share/man/man1/mysql.1.gz /usr/share/man/man1/mysql.server.1.gz /usr/share/man/man1/mysql_tableinfo.1.gz /usr/share/man/man1/mysql_upgrade.1.gz /usr/share/man/man1/mysqlaccess.1.gz /usr/share/man/man1/mysql_waitpid.1.gz /usr/share/man/man1/mysql_fix_extensions.1.gz /usr/share/man/man1/mysqlman.1.gz /usr/share/man/man1/mysqlbinlog.1.gz /usr/share/man/man1/mysql_install_db.1.gz /usr/share/man/man1/mysql_tzinfo_to_sql.1.gz /usr/share/man/man1/mysql_secure_installation.1.gz /usr/share/man/man1/mysqld_safe.1.gz /usr/share/man/man1/mysqladmin.1.gz /usr/share/man/man1/mysqlimport.1.gz /usr/share/man/man1/mysql_zap.1.gz /usr/share/man/man1/msql2mysql.1.gz /usr/share/man/man1/mysqlshow.1.gz /usr/share/man/man1/mysqldump.1.gz /usr/share/man/man1/safe_mysqld.1.gz /usr/share/man/man1/mysql_explain_log.1.gz /usr/share/man/man1/mysql_config.1.gz /usr/share/man/man1/mysqlbug.1.gz /usr/share/man/man1/mysqld_multi.1.gz /usr/share/man/man1/mysql_setpermission.1.gz /usr/share/man/man1/mysqlhotcopy.1.gz /usr/share/man/man1/mysql_find_rows.1.gz /usr/share/man/man1/mysql_convert_table_format.1.gz /usr/share/man/man1/mysql_fix_privilege_tables.1.gz /usr/share/man/man1/mysqldumpslow.1.gz /usr/share/man/man1/mysqltest.1.gz /usr/share/man/man1/mysqlcheck.1.gz /usr/share/man/man3/Bundle::DBD::mysql.3pm.gz /usr/share/man/man3/DBD::mysql.3pm.gz /usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz /usr/share/doc/mysql-server-5.0.77 /usr/share/doc/mysql-5.0.77 /usr/share/doc/selinux-policy-2.4.6/html/services_mysql.html /usr/share/pixmaps/comps/mysql.png /usr/share/info/mysql.info.gz /usr/share/selinux/devel/include/services/mysql.if /usr/bin/mysql_fix_extensions /usr/bin/mysql /usr/bin/mysqltestmanager /usr/bin/mysqldumpslow /usr/bin/mysql_upgrade_shell /usr/bin/mysql_convert_table_format /usr/bin/mysqlimport /usr/bin/mysqldump /usr/bin/mysqltestmanager-pwgen /usr/bin/mysql_tzinfo_to_sql /usr/bin/mysqlbug /usr/bin/mysqlhotcopy /usr/bin/mysqlaccess /usr/bin/mysqltest /usr/bin/mysqladmin /usr/bin/mysql_upgrade /usr/bin/mysqltestmanagerc /usr/bin/mysqld_safe /usr/bin/mysql_zap /usr/bin/mysql_waitpid /usr/bin/msql2mysql /usr/bin/mysql_secure_installation /usr/bin/mysql_fix_privilege_tables /usr/bin/mysqlshow /usr/bin/mysql_config /usr/bin/mysql_setpermission /usr/bin/mysql_tableinfo /usr/bin/mysql_find_rows /usr/bin/mysqld_multi /usr/bin/mysqlcheck /usr/bin/mysqlbinlog /usr/bin/mysql_install_db /usr/bin/mysql_explain_log /usr/lib/mysql /usr/lib/mysql/mysqlbug /usr/lib/mysql/libmysqlclient_r.so.15.0.0 /usr/lib/mysql/libmysqlclient.so.15 /usr/lib/mysql/libmysqlclient_r.so.15 /usr/lib/mysql/mysql_config /usr/lib/mysql/libmysqlclient.so.15.0.0 /usr/lib/python2.4/site-packages/sos/plugins/mysql.pyo /usr/lib/python2.4/site-packages/sos/plugins/mysql.pyc /usr/lib/python2.4/site-packages/sos/plugins/mysql.py /var/log/mysqld.log /var/run/mysqld /var/run/mysqld/mysqld.pid /var/lock/subsys/mysqld /var/lib/mysql /var/lib/mysql/mysql /var/lib/mysql/mysql.sock /root/.mysql_history /selinux/booleans/mysqld_disable_trans /selinux/booleans/allow_user_mysql_connect

After successfully installing MySQL on Oracle Enterprise Linux installing a LAMP (Linux/Apache/MySQL/PHP) stack can also be performed with a single command:

$ yum install -y httpd php php-mysql # Start the Apache Httpd Process $ /etc/init.d/httpd start

To test and confirm Apache Httpd and PHP, we can use the CLI browser lynx:

$ yum install -y lynx $ echo "<? phpinfo() ?>" > /var/www/html/phpinfo.php $ lynx http://localhost/phpinfo.php

If successful, you will find a web page that contains the following.

phpinfo() (p1 of 31) PHP Logo PHP Version 5.1.6 System Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 04:15:13 EDT 2009 x86_64 Build Date Feb 11 2010 19:07:36 Configure Command './configure' '--build=x86_64-redhat-linux-gnu' '--host=x86_64-redhat-linux-gnu' '--target=x86_64-redhat-linux-gnu' '--program-prefix=''--prefix=/usr''--exec-prefix=/usr''--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib64' '--with-config-file-path=/etc'

It is important to note that PHP is also a standalone scripting language that doesn’t require a web browser. You can use PHP on the command line, for example:

$ php --version PHP 5.1.6 (cli) (built: Feb 11 2010 19:06:40) Copyright (c) 1997-2006 The PHP Group Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies $ echo "<?phpinfo()?>" | php | grep -i mysql Configure Command => './configure' '--build=x86_64-redhat-linux-gnu' '--host=x86_64-redhat-linux-gnu' '--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib64' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--disable-rpath' '--without-pear' '--with-bz2' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-expat-dir=/usr' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-sysvmsg' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-kerberos' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--with-mime-magic=/usr/share/file/magic.mime' '--without-sqlite' '--with-libxml-dir=/usr' '--with-xml' '--with-system-tzdata' '--enable-force-cgi-redirect' '--enable-pcntl' '--with-imap=shared' '--with-imap-ssl' '--enable-mbstring=shared' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-ncurses=shared' '--with-gd=shared' '--enable-bcmath=shared' '--enable-dba=shared' '--with-db4=/usr' '--with-xmlrpc=shared' '--with-ldap=shared' '--with-ldap-sasl' '--with-mysql=shared,/usr' '--with-mysqli=shared,/usr/lib64/mysql/mysql_config' '--enable-dom=shared' '--with-dom-xslt=/usr' '--with-dom-exslt=/usr' '--with-pgsql=shared' '--with-snmp=shared,/usr' '--enable-soap=shared' '--with-xsl=shared,/usr' '--enable-xmlreader=shared' '--enable-xmlwriter=shared' '--enable-fastcgi' '--enable-pdo=shared' '--with-pdo-odbc=shared,unixODBC,/usr' '--with-pdo-mysql=shared,/usr/lib64/mysql/mysql_config' '--with-pdo-pgsql=shared,/usr' '--with-pdo-sqlite=shared,/usr' '--enable-dbase=shared' /etc/php.d/mysql.ini, /etc/php.d/mysqli.ini, /etc/php.d/pdo_mysql.ini, mysql MySQL Support => enabled MYSQL_MODULE_TYPE => external MYSQL_SOCKET => /var/lib/mysql/mysql.sock MYSQL_INCLUDE => -I/usr/include/mysql MYSQL_LIBS => -L/usr/lib64/mysql -lmysqlclient mysql.allow_persistent => On => On mysql.connect_timeout => 60 => 60 mysql.default_host => no value => no value mysql.default_password => no value => no value mysql.default_port => no value => no value mysql.default_socket => no value => no value mysql.default_user => no value => no value mysql.max_links => Unlimited => Unlimited mysql.max_persistent => Unlimited => Unlimited mysql.trace_mode => Off => Off mysqli MysqlI Support => enabled MYSQLI_SOCKET => /var/lib/mysql/mysql.sock mysqli.default_host => no value => no value mysqli.default_port => 3306 => 3306 mysqli.default_pw => no value => no value mysqli.default_socket => no value => no value mysqli.default_user => no value => no value mysqli.max_links => Unlimited => Unlimited mysqli.reconnect => Off => Off PDO drivers => mysql, sqlite pdo_mysql PDO Driver for MySQL, client library version => 5.0.77

Short URL: rb42.com/oel-install-lamp

One of the significant benefits of MySQL is it’s ease of use. Generally already installed on most Linux systems, MySQL can be installed by a single command if not yet present. On Oracle Enterprise Linux 5.4 you can use the following commands to check for MySQL, configure your yum repository and install MySQL.

# Check if already installed $ rpm -qa | grep -i mysql # Configure yum repository on new server $ su - $ cd /tmp $ wget http://public-yum.oracle.com/public-yum-el5.repo $ sed -e "s/enabled=0/enabled=1/" public-yum-el5.repo > /etc/yum.repos.d/public-yum-el5.repo # Install MySQL $ yum install -y mysql-server mysql # Start and test MySQL Instance $ /etc/init.d/mysqld start $ mysql -uroot -e "SELECT VERSION" +-----------+ | VERSION() | +-----------+ | 5.0.77 | +-----------+

You can find more information about the Oracle public yum repository at http://public-yum.oracle.com You will also note that the version installed is 5.0. The current GA version of MySQL is 5.1, however Red Hat is notorious for taking time to update repositories more regularly. You can always use more current rpm files available at the MySQL downloads page.

If you want MySQL to be configured to automatically start and stop on your server you need to run the following command.

$ chkconfig mysqld on $ chkconfig --list mysqld mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

You can link to this post with the short url rb42.com/oel-install-mysql

Related Posts

• Reviewing your MySQL Installation on Oracle Enterprise Linux
• Installing a LAMP stack on Oracle Enterprise Linux

I will be one of 18 MySQL speakers at Oracle Open World 2010 at the first ever MySQL Sunday. With a great diversity of technical, community and product talks this will be a great opportunity to get a cross section of MySQL content. Combined with Java One, this year’s Oracle Open World will include a lot more opportunity of technical and developer content then the more regular marketing material.

As the program chair for the first dedicated MySQL track at last month’s ODTUG Kaleidoscope 2010 our MySQL community now includes a larger number of target people. From the Oracle community come many highly technical and skilled resources, some with an understanding or appreciation of MySQL and many that are new to MySQL.

This is a great opportunity to share our knowledge and experience with MySQL.

References

• MySQL Sunday overview
• MySQL Sunday speakers/presentation matrix

The most efficient performance optimization of a SQL statement is to eliminate it. Cary Millsap’s recent Kaleidoscope presentation again highlighted that improving performance is function of code path. Removing code will improve performance.

You may think that it could be hard to eliminate SQL, however when you know every SQL statement that is executed in your code path obvious improvements may be possible. In the sequence SQL was implemented sometimes easy observations can lead to great gains. Let me provide some actual client examples that were discovered by using the MySQL General Log.

Example 1 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` WHERE (ArtistID = 196 ) 5 Query SELECT * FROM `artist` WHERE (ArtistID = 2188 ) 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist`

In this example, the following was executed for a single page load. Not only did I find a bug where full-table scans occurred rather then being qualified, there were many repeating and unnecessary occurrences.

Example 2 SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' SELECT option_value FROM wp_options WHERE option_name = 'aiosp_title_format' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_show_only_even' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_num_months' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_day_length' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_hide_event_box' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_advanced' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_navigation' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_disable_popups' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'sidebars_widgets' LIMIT 1

This is a stock Wordpress installation and highlights a classic Row at a Time (RAT) processing.

Example 3 SELECT * FROM activities_theme WHERE theme_parent_id=0 SELECT * FROM activities_theme WHERE theme_parent_id=1 SELECT * FROM activities_theme WHERE theme_parent_id=2 SELECT * FROM activities_theme WHERE theme_parent_id=11 SELECT * FROM activities_theme WHERE theme_parent_id=16

In this client example, again RAT processing, I provided a code improvement to run these multiple queries in a single statement, otherwise known as Chunk At a Time (CAT) processing. It’s not rocket science however the elimination of the network component of several SQL statements can greatly reduce page load time.

SELECT * FROM activities_theme WHERE theme_parent_id in (0,1,2,11,16)  Example 4

The following represents one of the best improvement. During capture, the following query was executed 6,000 times over a 5 minute period. While you make think this is acceptable, the value passed wae 0. The pages_id is an auto_increment column which by definition does not have a 0 value. In this instance, a simple boundary condition in the code would eliminate this query.

SELECT pages_id, pages_livestats_code, pages_title, pages_parent, pages_exhibid, pages_theme, pages_accession_num FROM pages WHERE pages_id = 0

There are many tips to improving and optimizing SQL. This is the simplest and often overlooked starting point.

One role of a MySQL consultant is to review an existing production system. Sometimes you have sufficient time and access, and other times you don’t. If I am given a limited time here is a general list of things I look at.

1. Review Server architecture, OS, Memory, Disks (including raid and partition type), Network etc
2. Review server load and identify physical bottleneck
3. Look at all running processes
4. Look specifically at MySQL processes
5. Review MySQL Error Log
6. Determine MySQL version
7. Look at MySQL configuration (e.g. /etc/my.cnf)
8. Look at running MySQL Variables
9. Look at running MySQL status (x n times)
10. Look at running MySQL INNODB status (x n times) if used
11. Get Database and Schema Sizes
12. Get Database Schema
13. Review Slow Query Log
14. Capture query sample via SHOW FULL PROCESSLIST (locked and long running)
15. Analyze Binary Log file
16. Capture all running SQL

Here are some of the commands I would run.

2. Review server load and identify physical bottleneck $ vmstat 5 720 > vmstat.`date +%y%m%d.%H%M%S`.txt 4. Look at MySQL processes $ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql" PID COMMAND RSS VSZ USER COMMAND 5463 grep 764 5204 ronald grep -e RSS -e mysql 13894 mysqld_s 596 3936 root /bin/sh /usr/bin/mysqld_safe 13933 mysqld 4787812 5127208 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/vol/mysql/mysqldata --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 13934 logger 608 3840 root logger -p daemon.err -t mysqld_safe -i -t mysqld $ ps -eopid,fname,rss,vsz,user,command | grep " mysqld " | grep -v grep | awk '{print $3,$4}' 4787820 5127208 5. Review MySQL Error Log

The error log can be found in various different places based on the operating system and configuration. It is important to find the right log, the SHOW GLOBAL VARIABLES LIKE ‘log_error’ will determine the location.

This is generally overlooked, however this can quickly identify some underlying problems with a MySQL environment.

7. Look at MySQL configuration $ [ -f /etc/my.cnf ] && cat /etc/my.cnf $ [ -f /etc/mysql/my.cnf ] && cat /etc/mysql/my.cnf $ find / -name "*my*cnf" 2>/dev/null 8. Look at running MySQL Variables $ mysqladmin -uroot -p variables 9. Look at running MySQL status (x n times) $ mysqladmin -uroot -p extended-status

It is important to run this several times at regular intervals, say 60 seconds, 60 minutes, or 24 hours.

I also have dedicated scripts that can perform this. Check out Log MySQL Stats.

11. Get Database and Schema Sizes

Check out my scripts on my MySQL DBA page

14. Capture Locked statements

Check out my script for Capturing MySQL sessions.

15. Analyze Binary Log file

Check out my post on using mk-query-digest.

16. Capture all SQL

Check out my post on DML Stats per table

Moving forward

Of course the commands I run exceeds this initial list, and gathering this information is only

When working interactively with the MySQL client, you receive feedback of the time the query took to complete to a granularity of 10 ms.

Enabling profiling is a simple way to get more a more accurate timing of running queries. In the following example you can see the time the kernel took to run an explain, the query, and alter, and repeat explain and query.

mysql> set profiling=1; mysql> EXPLAIN SELECT ... mysql> SELECT ... mysql> ALTER ... mysql> show profiles; +----------+------------+------------------------- | Query_ID | Duration | Query +----------+------------+------------------------- | 1 | 0.00036500 | EXPLAIN SELECT sbvi.id a | 2 | 0.00432700 | SELECT sbvi.id as sbvi_i | 3 | 2.83206100 | alter table sbvi drop in | 4 | 0.00047500 | explain SELECT sbvi.id a | 5 | 0.00367100 | SELECT sbvi.id as sbvi_i +----------+------------+-------------------------

More information at Show Profiles documentation page.

I’ve been doing a little work with Ruby, starting with some XHTML parsing with Nokogiri. I’ve just created my first web page using Sinatra.

While the instructions makes it look simple, it was a little more complex due a package dependency error.

Installation $ sudo gem install sinatra Password: Successfully installed rack-1.2.1 Successfully installed sinatra-1.0 2 gems installed Installing ri documentation for rack-1.2.1... Installing ri documentation for sinatra-1.0... Installing RDoc documentation for rack-1.2.1... Installing RDoc documentation for sinatra-1.0... Smoking it

Following the 5 line example on the home page, didn’t produce the result I expected.

ruby rb42.rb /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/utils.rb:138:in `union': can't convert Array into String (TypeError) from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/utils.rb:138 from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require' from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/request.rb:1 from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require' from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/showexceptions.rb:3 from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' ... 7 levels... from /Library/Ruby/Gems/1.8/gems/sinatra-1.0/lib/sinatra.rb:4 from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:36:in `gem_original_require' from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:36:in `require' from rb42.rb:2

Some Googling found a reference to a compatibility problem. While the syntax given on the post wasn’t correct, it was sufficient for me to find a correct solution.

$ sudo gem uninstall rack Remove executables: rackup in addition to the gem? [Yn] y Removing rackup You have requested to uninstall the gem: rack-1.2.1 sinatra-1.0 depends on [rack (>= 1.0)] If you remove this gems, one or more dependencies will not be met. Continue with Uninstall? [Yn] y Successfully uninstalled rack-1.2.1 $ sudo gem install rack --version '1.2.0' Successfully installed rack-1.2.0 1 gem installed Installing ri documentation for rack-1.2.0... Installing RDoc documentation for rack-1.2.0...

And now expected results starting then viewing http://localhost:4567/

$ ruby rb42.rb == Sinatra/1.0 has taken the stage on 4567 for development with backup from Mongrel Environment $ sw_vers ProductName: Mac OS X ProductVersion: 10.5.8 BuildVersion: 9L31a $ ruby --version ruby 1.8.6 (2009-06-08 patchlevel 369) [universal-darwin9.0] $ gem --version 1.3.6