Expert times and information on MySQL
Updated: 1 day 6 hours ago
CORRECTION: MySQL Idiosyncrasies that BITE Webinar
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
3 webinars on Upgrading MySQL
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.
Installing Wordpress on Oracle Enterprise Linux LAMP stack
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 Software Installationsu - 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.
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
Short URL for this post rb42.com/oel-install-wordpress
Reviewing your MySQL installation on Oracle Enterprise Linux
After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.
User ManagementBy 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 LogThe 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.
ProcessesMySQL 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.sockMemory 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.sockDisk 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 MySQLThe 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/mysqldConfiguration
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.pidAudit
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
Installing a LAMP stack on Oracle Enterprise Linux
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
Installing MySQL on Oracle Enterprise Linux
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 PostsSpeaking at Oracle Open World 2010
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.
ReferencesOptimizing SQL Performance – The Art of Elimination
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 15 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 2SELECT 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 3SELECT * 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.
What do MySQL Consultants do?
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.
- Review Server architecture, OS, Memory, Disks (including raid and partition type), Network etc
- Review server load and identify physical bottleneck
- Look at all running processes
- Look specifically at MySQL processes
- Review MySQL Error Log
- Determine MySQL version
- Look at MySQL configuration (e.g. /etc/my.cnf)
- Look at running MySQL Variables
- Look at running MySQL status (x n times)
- Look at running MySQL INNODB status (x n times) if used
- Get Database and Schema Sizes
- Get Database Schema
- Review Slow Query Log
- Capture query sample via SHOW FULL PROCESSLIST (locked and long running)
- Analyze Binary Log file
- 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`.txt4. 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/null8. Look at running MySQL Variables
$ mysqladmin -uroot -p variables9. 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 SizesCheck out my scripts on my MySQL DBA page
14. Capture Locked statementsCheck out my script for Capturing MySQL sessions.
15. Analyze Binary Log fileCheck out my post on using mk-query-digest.
16. Capture all SQLCheck out my post on DML Stats per table
Moving forwardOf course the commands I run exceeds this initial list, and gathering this information is only
Timing your SQL queries
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.
Getting started with Ruby and Sinatra
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 MongrelEnvironment
$ 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
Upcoming Conferences with dedicated MySQL content
We recently held a dedicated MySQL Track at ODTUG Kaleidoscope 2010 conference for 4 days. This is the first of many Oracle events that will begin to include dedicated MySQL content.
If your attending OSCON 2010 in the next few weeks you will see a number of MySQL presentations.
MySQL will be represented at Open World 2010 in September with MySQL Sunday. Giuseppe has created a great one page summary of speakers. This event is described as technical sessions, an un-conference and an fireside chat with Edward Screven. I’ve seen tickets listed at $50 or $75 for the day.
Open SQL Camp will be held in Germany in August, and Boston in October. This is a great FREE event that includes technical content not just on MySQL but other open source databases and data stores.
You will also find dedicated MySQL tracks in Europe at the German Oracle Users Group (DOAG) conference in November and the United Kingdom Oracle Users Group (UKOUG) in November that I am planning on attending.
In 2011 there is already a lineup of events that will all contain multiple tracks of MySQL content.
- April – Collaborate 11 in Orlando, FL. (no landing page 2010 site)
- April – O’Reilly MySQL Conference in Santa Clara, CA (no landing page 2010 site)
- June – Kaleidoscope 2011 in Long Beach, CA
For the MySQL community the introduction of various large Oracle conferences may be confusing. From my perspective I describe the big three as.
- Oracle Open World is targeted towards marketing. This includes product announcements, case studies and first class events.
- Collaborate is targeted towards deployment and includes 3 different user groups, the IOUG representing the Oracle Database, the Oracle Applications User Group, and the Quest Group.
- ODTUG Kaleidoscope is targeted towards development. This includes the tools and technologies for developers and DBA’s to do your job.
Having just attended Kaleidoscope 2010, and being a relative unknown I left with a great impression of an open, technical and welcoming event. There was a great atmosphere, great events with excellent food for breakfast, lunch and dinner and I now have a long list of new friends. This conference very much reflected being part of a greater extended family, the experience I have enjoyed at previous MySQL conferences. I’ve already committed to being involved next year.
Improving MySQL Productivity – From Design to Implementation
My closing presentation at the dedicated MySQL track at ODTUG Kaleidoscope 2010 discussed various techniques and best practices for improving the ROI of developer resources using MySQL. Included in the sections on Design, Security, Development, Testing, Implementation, Instrumentation and Support were also a number of horror stories of not what to do, combined with practical examples of improving productivity.
Increasing MySQL Productivity View more presentations from Ronald Bradford.MySQL Idiosyncrasies That Bite
The following are my slides that I presented at ODTUG Kaleidoscope 2010. This presentation talks about the MySQL defaults including a non-transactional state, silent data truncations, date management, and transaction isolation options. These are all critical for data integrity and consistency. I also cover in-depth topics including SQL_MODE, character sets and collations.
MySQL Idiosyncrasies That Bite View more presentations from Ronald Bradford.Getting Nokogiri working under Mac OS X
The official Installation documentation states:
sudo port install libxml2 libxslt sudo gem install nokogiri
however I found this not to work for me. The following did work.
$ sudo port install libxml2 libxslt $ sudo gem install nokogiri ERROR: could not find nokogiri locally or in a repository $ sudo gem sources -a http://gems.github.com Bulk updating Gem source index for: http://gems.github.com Updating metadata for 1 gems from http://gems.rubyforge.org $ sudo gem install nokogiri ERROR: could not find nokogiri locally or in a repository $ sudo gem update --system Updating RubyGems... Attempting remote update of rubygems-update Successfully installed rubygems-update-1.3.6 1 gem installed Installing ri documentation for rubygems-update-1.3.6... Installing RDoc documentation for rubygems-update-1.3.6... Updating version of RubyGems to 1.3.6 Installing RubyGems 1.3.6 RubyGems 1.3.6 installed $ sudo gem install nokogiri Building native extensions. This could take a while... Successfully installed nokogiri-1.4.2 1 gem installed Installing ri documentation for nokogiri-1.4.2... No definition for parse_memory No definition for parse_file No definition for parse_with No definition for get_options No definition for set_options Installing RDoc documentation for nokogiri-1.4.2... No definition for parse_memory No definition for parse_file No definition for parse_with No definition for get_options No definition for set_options
I’m not sure why I got those additional errors, however I was able to confirm a valid installation with.
$ irb
>> require ‘nokogiri’
=> true
>> quit
Still room at Kaleidoscope for MySQL attendees
Today I received notice that next week’s Velocity conference is at maximum capacity. With just under 2 weeks before the start of ODTUG Kaleidoscope in Washington DC we still have room for late registrations. There is 4 days of MySQL content, free events and also a Sunday Symposium that includes talks on performance and high availability.
Contact any of the MySQL speakers directly and you can receive a special 50% discount code. This is only for MySQL attendees.
If you live in the DC area and only want the FREE option then come along and join use on Monday night for a free session and reception.
ODTUG Kaleidoscope 2010
July 27 – July 1
Marriott Wardman Part Hotel
2660 Woodley Road NW
Washington, District Of Columbia 20008
www.odtugkaleidoscope.com
Community Service Day – Saturday, June 26, 8:00 a.m. – 1:00 p.m.
Join ODTUG volunteers and help refurbish a school in D.C. Under the guidance of Greater DC Cares (GDCC), the leading and largest nonprofit coordinator of volunteerism in the D.C. region, ODTUGgers will: Sort books, beautify school grounds, and paint games on blacktop outside of hte school.
There is still time to sign up!
Four Full-day Symposia – Sunday, June 27, 8:30 a.m. – 4:00 p.m.
Application Express; Oracle EPM and Essbase; Security, Scalability, and Performance; SOA and BPM. One-day registration available.
Welcome Reception/Battle of the Rock Bands – Sunday, June 27, 6:15 – 8:00 p.m.
Meet the exhibitors and compete in the “Battle of the Rock Bands.” Sign up to play.
Opening General Session – Monday, June 28, 8:30 – 10:00 a.m.
Awards for Best Technical Paper and Best 2009 Presentations
Keynote – “Future of the Internet and its Social Impact” by Lee Rainie, Director of the PEW Research Center’s Internet & American Life Project.
Sundown Sessions with Oracle ACE Directors – Monday, June 28, 5:45 – 6:45 p.m.
Reception to meet the Oracle ACE Directors immediately follows – 6:45 – 7:45 p.m.
Special Event – Wednesday, June 30, 6:30 – 10:00 p.m.
Featuring comedian John Heffron, 2nd season champion of the hit TV show, Last Comic Standing.
Music by live cover band, Right Foot Red
MongoDB Experience: Server logging
By default the mongod process sends all output to stdout. You can also specify the daemon to log to file which is necessary for any production implementation. For example:
$ mongod --logpath=`pwd`/mongo.log & all output going to: /home/rbradfor/projects/mongo/mongo.log ^C
As you can see there is still a message to stdout, that should be cleaned up for a GA release. The output you will see for a clean startup/shutdown is:
Fri Jun 11 14:05:29 Mongo DB : starting : pid = 7990 port = 27017 dbpath = /home/rbradfor/projects/mongo/data/cu rrent master = 0 slave = 0 64-bit Fri Jun 11 14:05:29 db version v1.4.3, pdfile version 4.5 Fri Jun 11 14:05:29 git version: 47ffbdfd53f46edeb6ff54bbb734783db7abc8ca Fri Jun 11 14:05:29 sys info: Linux domU-12-31-39-06-79-A1 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_41 Fri Jun 11 14:05:29 waiting for connections on port 27017 Fri Jun 11 14:05:29 web admin interface listening on port 28017 Fri Jun 11 14:05:31 got kill or ctrl c signal 2 (Interrupt), will terminate after current cmd ends Fri Jun 11 14:05:31 now exiting Fri Jun 11 14:05:31 dbexit: Fri Jun 11 14:05:31 shutdown: going to close listening sockets... Fri Jun 11 14:05:31 going to close listening socket: 5 Fri Jun 11 14:05:31 going to close listening socket: 6 Fri Jun 11 14:05:31 shutdown: going to flush oplog... Fri Jun 11 14:05:31 shutdown: going to close sockets... Fri Jun 11 14:05:31 shutdown: waiting for fs preallocator... Fri Jun 11 14:05:31 shutdown: closing all files... Fri Jun 11 14:05:31 closeAllFiles() finished Fri Jun 11 14:05:31 shutdown: removing fs lock... Fri Jun 11 14:05:31 dbexit: really exiting now
MongoDB logging does not give an option to format the date/time appropriately. The format does not match the syslog of Ubuntu/CentOS
Jun 9 10:05:46 barney kernel: [1025968.983209] SGI XFS with ACLs, security attributes, realtime, large block/in ode numbers, no debug enabled Jun 9 10:05:46 barney kernel: [1025968.984518] SGI XFS Quota Management subsystem Jun 9 10:05:46 barney kernel: [1025968.990183] JFS: nTxBlock = 8192, nTxLock = 65536 Jun 9 10:05:46 barney kernel: [1025969.007624] NTFS driver 2.1.29 [Flags: R/O MODULE]. Jun 9 10:05:46 barney kernel: [1025969.020995] QNX4 filesystem 0.2.3 registered. Jun 9 10:05:46 barney kernel: [1025969.039264] Btrfs loaded
Jun 8 00:00:00 dc1 nagios: CURRENT HOST STATE: localhost;UP;HARD;1;PING OK - Packet loss = 0%, RTA = 0.01 ms Jun 8 00:00:00 dc1 nagios: CURRENT SERVICE STATE: localhost;Current Load;OK;HARD;1;OK - load average: 0.00, 0.00, 0.00 Jun 8 00:00:00 dc1 nagios: CURRENT SERVICE STATE: localhost;Current Users;OK;HARD;1;USERS OK - 2 users currently logged in Jun 8 00:00:00 dc1 nagios: CURRENT SERVICE STATE: localhost;HTTP;CRITICAL;HARD;4;Connection refused Jun 8 00:00:00 dc1 nagios: CURRENT SERVICE STATE: localhost;PING;OK;HARD;1;PING OK - Packet loss = 0%, RTA = 0.01 ms Jun 8 00:00:00 dc1 nagios: CURRENT SERVICE STATE: localhost;Root Partition;OK;HARD;1;DISK OK - free space: / 107259 MB (49% inode=98%): Jun 8 00:00:00 dc1 nagios: CURRENT SERVICE STATE: localhost;SSH;OK;HARD;1;SSH OK - OpenSSH_4.3 (protocol 2.0)
And for reference here is the mysql format, which is also not configurable.
100605 16:43:38 mysqld_safe Starting mysqld daemon with databases from /opt/mysql51/data 100605 16:43:38 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_fi le'' instead. 100605 16:43:38 [Warning] '--log' is deprecated and will be removed in a future release. Please use ''--general_log'/'--general_log_file'' instead. 100605 16:43:38 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts a s a master and has his hostname changed!! Please use '--log-bin=dc1-bin' to avoid this problem. /opt/mysql51/bin/mysqld: File './dc1-bin.index' not found (Errcode: 13) 100605 16:43:38 [ERROR] Aborting
However unlike other products including MySQL the next execution of the mongod process overwrites the log file. This will catch some administrators out. You need to remember to also add –logappend. Personally I’d prefer to see this is the default
$ mongod --logpath=`pwd`/mongo.log --logappend
I did observe some confusion on messaging. Using the mongo shell you get a jumble of logging messages during a shutdown.
$ mongo
MongoDB shell version: 1.4.3
url: test
connecting to: test
type "help" for help
> use admin
switched to db admin
> db.shutdownServer();
Fri Jun 11 13:54:08 query failed : admin.$cmd { shutdown: 1.0 } to: 127.0.0.1
server should be down...
Fri Jun 11 13:54:08 trying reconnect to 127.0.0.1
Fri Jun 11 13:54:08 reconnect 127.0.0.1 ok
Fri Jun 11 13:54:08 query failed : admin.$cmd { getlasterror: 1.0 } to: 127.0.0.1
Fri Jun 11 13:54:08 JS Error: Error: error doing query: failed (anon):1284
> exit
bye
This also results in an unformatted message in the log file for some reason.
$ tail mongo.log Fri Jun 11 13:54:08 shutdown: removing fs lock... Fri Jun 11 13:54:08 dbexit: really exiting now Fri Jun 11 13:54:08 got request after shutdown() ERROR: Client::~Client _context should be NULL: conn
Nothing of any critical nature however all important for system administrators that have monitoring scripts or using monitoring products.
MongoDB Experience: Key/Value Store
MongoDB is categorized as a schema-less, schema-free or a document orientated data store. Another category of NoSQL product is the key/value store. It had not dawned on me until a discussion with some of the 10gen employees that MongoDB is also a key/value store, this is just a subset of features.
How would you consider the design of a key/value store? Using the memached model, there are 4 primary attributes to consider:
- The Key to store/retrieve
- The Value for the given key
- An auto expiry of the cached data
- A key scope enabling multiple namespaces
There are three primary functions:
- Put a given Key/Value pair
- Get a given Key
- Delete a given Key
Let’s explore the options. The first is to create a new collection for each key. That way there is only one row per key,
> use keystore
> var d = new Date();
> var id = "key1";
> var kv = { key: id,val: "Hello World",expires: d}
> db.key1.save(kv);
> db.key1.find();
{ "_id" : ObjectId("4c126095c68fcaf3b0e07a2b"), "key" : "key1", "val" : "Hello World", "expires" : "Fri Jun 11 2010 12:09:51 GMT-0400 (EDT)" }
However when we start loading we run into a problem.
> db.key99999.save({key: "key99999", val: "Hello World", expires: new Date()})
too many namespaces/collections
> show collections;
Fri Jun 11 12:49:02 JS Error: uncaught exception: error: {
"$err" : "too much key data for sort() with no index. add an index or specify a smaller limit"
}
> db.stats()
{
"collections" : 13661,
"objects" : 26118,
"dataSize" : 2479352,
"storageSize" : 93138688,
"numExtents" : 13665,
"indexes" : 13053,
"indexSize" : 106930176,
"ok" : 1
}
I did read there was a limit on the number of collections at Using a Large Number of Collections.
Also for reference, I look at the underlying data files shows the ^2 increment of data files.
$ ls -lh data/current total 2.2G -rw------- 1 rbradfor rbradfor 64M 2010-06-11 12:45 keystore.0 -rw------- 1 rbradfor rbradfor 128M 2010-06-11 12:45 keystore.1 -rw------- 1 rbradfor rbradfor 256M 2010-06-11 12:46 keystore.2 -rw------- 1 rbradfor rbradfor 512M 2010-06-11 12:48 keystore.3 -rw------- 1 rbradfor rbradfor 1.0G 2010-06-11 12:48 keystore.4 -rw------- 1 rbradfor rbradfor 16M 2010-06-11 12:48 keystore.ns
> db.dropDatabase();
{ "dropped" : "keystore.$cmd", "ok" : 1 }
In my next test I’ll repeat by adding the key as a row or document for just one collection.
MongoDB Experience: Stats Example App
The best way to learn any new product is to a) read the manual, and b) start using the product.
I created a simple sample application so I could understand the various functions including adding data, searching as well as management functions etc. As with any good sample application using a source of data that already exists always makes life easier. For this example I’m going to use the Operating System output so I will have an ever increasing amount of output for no additional work.
I will be starting with a database called ’stats’. For this database my first collection is going to be called ’system’ and this is going to record the most basic of information including date/time, host and cpu (user,sys,idle) stats. I have a simple shell script that creates an appropriate JSON string and I use mongoimport to load the data. Here is my Version 0.1 architectural structure.
mongo> use stats;
mongo> db.system.findOne();
{
"_id" : ObjectId("4c11183580399ad2db4f503b"),
"host" : "barney",
"epoch" : 1276188725,
"date" : "Thu Jun 10 12:52:05 EDT 2010",
"cpu" : {
"user" : 2,
"sys" : 2,
"idle" : 95
},
"raw" : " 11435699 1379565 9072198 423130352 2024835 238766 2938641 0 0"
}
I made some initial design decisions before I understand the full strengths/limitation of MongoDB as well as what my actual access paths to data will be.
While I’m using a seconds since epoch for simple range searching, I’m adding a presentation date for user readability. I’ve created a different sub element for cpu, because it a) this element has a number of individual attributes I will want to report and search on, and b) this collection should be extended to include other information like load average, running processes, memory etc.
If my shell script runs in debug mode, I also record the raw data used to determine the end result. This makes debugging easier.
Here is my first query.
Find all statistics between two dates. It took a bit of getting the correct construct syntax correct, $le and $ge didn’t work so RTFM highlighted the correct syntax. I also first included elements for epoch, which resulted in a OR condition, I see you can add multiple comparison operators to a single element to get an AND operation.
mongo> db.system.find({epoch: { $gte: 1276188725, $lte: 1276188754}});
{ "_id" : ObjectId("4c11183580399ad2db4f503b"), "host" : "barney", "epoch" : 1276188725, "date" : "Thu Jun 10 12:52:05 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 }, "raw" : " 11435699 1379565 9072198 423130352 2024835 238766 2938641 0 0" }
{ "_id" : ObjectId("4c11184c80399ad2db4f503c"), "host" : "barney", "epoch" : 1276188748, "date" : "Thu Jun 10 12:52:28 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 }, "raw" : " 11436605 1379565 9072320 423138450 2024862 238770 2938641 0 0" }
{ "_id" : ObjectId("4c11185080399ad2db4f503d"), "host" : "barney", "epoch" : 1276188752, "date" : "Thu Jun 10 12:52:32 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 }, "raw" : " 11437005 1379565 9072330 423139527 2024862 238770 2938641 0 0" }
{ "_id" : ObjectId("4c11185180399ad2db4f503e"), "host" : "barney", "epoch" : 1276188753, "date" : "Thu Jun 10 12:52:33 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 }, "raw" : " 11437130 1379565 9072334 423139862 2024862 238770 2938641 0 0" }
{ "_id" : ObjectId("4c11185280399ad2db4f503f"), "host" : "barney", "epoch" : 1276188754, "date" : "Thu Jun 10 12:52:34 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 }, "raw" : " 11437316 1379565 9072338 423140325 2024910 238770 2938641 0 0" }
Assuming I’m going to have stats from more then one server in my data, we should always filter by hostname, and then for given period.
mongo> db.system.find({host: "barney", epoch: { $gte: 1276188725, $lte: 1276188754}});
If I only want to see the Date/Time and CPU stats, I can show a subset of the elements found.
mongo> db.system.find({epoch: { $gte: 1276188725, $lte: 1276188754}}, {date:1,cpu:1});
{ "_id" : ObjectId("4c11183580399ad2db4f503b"), "date" : "Thu Jun 10 12:52:05 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 } }
{ "_id" : ObjectId("4c11184c80399ad2db4f503c"), "date" : "Thu Jun 10 12:52:28 EDT 2010", "cpu" : { "user" : 2, "sys" : 2, "idle" : 95 } }
...
Filtering on a sub-element is also possible however I found that the representation of strings and numbers does not do an implied conversion. In the following example “2″ does not match any results, while 2 does.
mongo> db.system.findOne({host: "barney", "cpu.user": "2"})
null
mongo> db.system.findOne({host: "barney", "cpu.user": 2})
{
"_id" : ObjectId("4c11161680399ad2db4f5033"),
"host" : "barney",
"epoch" : 1276188182,
"date" : "Thu Jun 10 12:43:02 EDT 2010",
"cpu" : {
"user" : 2,
"sys" : 2,
"idle" : 95
}
}
Given the collection and load process works, data is being recorded and I can perform some searching I now have the basis for adding additional rich data elements, learning about the internal DBA operations possible after I fix the bug with all my values being 2/2/95.
MongoDB Experience: Replication 101
After successfully installing and testing mongoDB it’s very easy to create a replication environment.
$ mkdir -p data/{master,slave}
$ mongod --dbpath=`pwd`/data/master --master --port 28011 > master.log 2>&1 &
# Always check your log file
$ cat master.log
$ mongod --dbpath=`pwd`/data/slave --slave --source localhost:28011 --port 28022 > slave.log 2>&1 &
$ cat slave.log
The options are relatively descriptive and straightforward.
- –dbpath – The directory for data (we set because we are running master/slave on same server)
- –port – Likewise we are running multiple instances on same machine
- –master – I’m the master
- –slave – I’m a slave
- –source – For slaves, tell them were the source (i.e. master is)
What I found under the covers was a difference from the single instance version. There is a series of ‘local’ files for the namespace, where in the single instance version there were ‘test’ files.
$ ls -ltR data total 0 drwxr-xr-x 6 rbradfor staff 204 Jun 10 10:24 slave drwxr-xr-x 5 rbradfor staff 170 Jun 10 10:22 master data/slave: total 163848 drwxr-xr-x 2 rbradfor staff 68 Jun 10 10:24 _tmp -rw------- 1 rbradfor staff 67108864 Jun 10 10:24 local.0 -rw------- 1 rbradfor staff 16777216 Jun 10 10:24 local.ns -rwxr-xr-x 1 rbradfor staff 6 Jun 10 10:24 mongod.lock data/slave/_tmp: data/master: total 163848 -rw------- 1 rbradfor staff 67108864 Jun 10 10:22 local.0 -rw------- 1 rbradfor staff 16777216 Jun 10 10:22 local.ns -rwxr-xr-x 1 rbradfor staff 6 Jun 10 10:22 mongod.lock
A quick replication test.
$ mongo --port 28011
MongoDB shell version: 1.4.3
url: test
connecting to: 127.0.0.1:28011/test
type "help" for help
> db.foo.save({s:"Hello world"});
> db.foo.find();
{ "_id" : ObjectId("4c10f7904a30c35548b0af06"), "s" : "Hello world" }
> exit
bye
$ mongo --port 28022
MongoDB shell version: 1.4.3
url: test
connecting to: 127.0.0.1:28022/test
type "help" for help
> db.foo.find();
{ "_id" : ObjectId("4c10f7904a30c35548b0af06"), "s" : "Hello world" }
> exit
A look now at the underlying data shows a ‘test’ namespace which confirms the lazy instantiation approach. The ‘local’ namespace files are obviously a reflection of the –master/–slave operation.
$ ls -ltR data total 0 drwxr-xr-x 9 rbradfor staff 306 Jun 10 10:32 slave drwxr-xr-x 8 rbradfor staff 272 Jun 10 10:32 master data/slave: total 589832 -rw------- 1 rbradfor staff 134217728 Jun 10 10:33 test.1 drwxr-xr-x 2 rbradfor staff 68 Jun 10 10:32 _tmp -rw------- 1 rbradfor staff 67108864 Jun 10 10:32 test.0 -rw------- 1 rbradfor staff 16777216 Jun 10 10:32 test.ns -rw------- 1 rbradfor staff 67108864 Jun 10 10:24 local.0 -rw------- 1 rbradfor staff 16777216 Jun 10 10:24 local.ns -rwxr-xr-x 1 rbradfor staff 6 Jun 10 10:24 mongod.lock data/master: total 327688 drwxr-xr-x 2 rbradfor staff 68 Jun 10 10:32 _tmp -rw------- 1 rbradfor staff 67108864 Jun 10 10:32 test.0 -rw------- 1 rbradfor staff 16777216 Jun 10 10:32 test.ns -rw------- 1 rbradfor staff 67108864 Jun 10 10:22 local.0 -rw------- 1 rbradfor staff 16777216 Jun 10 10:22 local.ns -rwxr-xr-x 1 rbradfor staff 6 Jun 10 10:22 mongod.lock
By default there appears to be no read-only default state for a slave. I was able to add new data to the slave.
$ mongo --port 28022
MongoDB shell version: 1.4.3
url: test
connecting to: 127.0.0.1:28022/test
type "help" for help
> db.foo.save({s:"Hello New York"});
> db.foo.find();
{ "_id" : ObjectId("4c10f7904a30c35548b0af06"), "s" : "Hello world" }
{ "_id" : ObjectId("4c10f864d8e80f1a1ad305cf"), "s" : "Hello New York" }
>
A closer look at this ‘local’ namespace and a check via the docs gives us details of the slave configuration.
$ mongo --port 28022
MongoDB shell version: 1.4.3
url: test
connecting to: 127.0.0.1:28022/test
type "help" for help
> show dbs;
admin
local
test
> use local;
switched to db local
> show collections;
oplog.$main
pair.sync
sources
system.indexes
> db.sources.find();
{ "_id" : ObjectId("4c10f5b633308f7c3d7afc45"), "host" : "localhost:28011", "source" : "main", "syncedTo" : { "t" : 1276180895000, "i" : 1 }, "localLogTs" : { "t" : 1276180898000, "i" : 1 } }
You can also with the mongo client connect directly to a collection via the command line.
$ mongo localhost:28022/local
MongoDB shell version: 1.4.3
url: localhost:28022/local
connecting to: localhost:28022/local
type "help" for help
> db.sources.find();
{ "_id" : ObjectId("4c10f5b633308f7c3d7afc45"), "host" : "localhost:28011", "source" : "main", "syncedTo" : { "t" : 1276180775000, "i" : 1 }, "localLogTs" : { "t" : 1276180778000, "i" : 1 } }
> exit
bye
The shell gives 3 convenience commands for showing replication state.
On the Slave
$ mongo --port 28022
> db.getReplicationInfo();
{
"logSizeMB" : 50,
"timeDiff" : 1444,
"timeDiffHours" : 0.4,
"tFirst" : "Thu Jun 10 2010 10:24:54 GMT-0400 (EDT)",
"tLast" : "Thu Jun 10 2010 10:48:58 GMT-0400 (EDT)",
"now" : "Thu Jun 10 2010 10:48:59 GMT-0400 (EDT)"
}
> db.printReplicationInfo();
configured oplog size: 50MB
log length start to end: 1444secs (0.4hrs)
oplog first event time: Thu Jun 10 2010 10:24:54 GMT-0400 (EDT)
oplog last event time: Thu Jun 10 2010 10:48:58 GMT-0400 (EDT)
now: Thu Jun 10 2010 10:49:07 GMT-0400 (EDT)
> db.printSlaveReplicationInfo();
source: localhost:28011
syncedTo: Thu Jun 10 2010 10:49:25 GMT-0400 (EDT)
= 1secs ago (0hrs)
On the master, the same commands are applicable, output basically the same.
$ mongo --port 28011
> db.getReplicationInfo();
{
"logSizeMB" : 50,
"timeDiff" : 1714,
"timeDiffHours" : 0.48,
"tFirst" : "Thu Jun 10 2010 10:22:01 GMT-0400 (EDT)",
"tLast" : "Thu Jun 10 2010 10:50:35 GMT-0400 (EDT)",
"now" : "Thu Jun 10 2010 10:50:40 GMT-0400 (EDT)"
}
> db.printReplicationInfo();
configured oplog size: 50MB
log length start to end: 1714secs (0.48hrs)
oplog first event time: Thu Jun 10 2010 10:22:01 GMT-0400 (EDT)
oplog last event time: Thu Jun 10 2010 10:50:35 GMT-0400 (EDT)
now: Thu Jun 10 2010 10:50:45 GMT-0400 (EDT)
> db.printSlaveReplicationInfo();
local.sources is empty; is this db a --slave?
>
From these commands there seems no obvious way to easily identify if an instance is a master or not.
ReferencesDBA operations from shell
Replication
Master/Slave Replication