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.
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:
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.
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.
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)
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.
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:
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
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.
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
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
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.
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
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
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
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.
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.
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.
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.
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)
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.
Related articles include: The RAT and the CAT and We need more CAT’s
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.
Here are some of the commands I would run.
$ vmstat 5 720 > vmstat.`date +%y%m%d.%H%M%S`.txt
$ 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
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.
$ [ -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
$ mysqladmin -uroot -p variables
$ 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.
Check out my scripts on my MySQL DBA page
Check out my script for Capturing MySQL sessions.
Check out my post on using mk-query-digest.
Check out my post on DML Stats per table
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.
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.
For the MySQL community the introduction of various large Oracle conferences may be confusing. From my perspective I describe the big three as.
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.
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.
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.
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
While I have spent a lot of time recently helping the MySQL community interact with and integrate with various Oracle User Groups including ODTUG, IOUG, NoCOUG, NYOUG, DAOG I thought I’d share some resources for the MySQL Community that wanted to know more about Oracle.
The Oracle family of products is huge. You only have to look at the acquisitions via Wikipedia to get an idea. The first thing is to narrow your search, e.g. Database, APEX, Middleware, BI, Hyperion, Financials, development via Java, PHP or Oracle Forms etc.
While Oracle is a commercial product you can download all software for FREE via Oracle Technology Network. There is also documentation, forums, blogs and events.
Some Oracle bloggers I have already been reading however I’m expanding my list. People you may want to consider include:
Cary Millsap,Lewis Cunningham, Debra Lilley, Dimitri Gielis,Duncan Mills, Edward Roske, Mark Rittman, Scott Spendolini, Tim Tow, Tom Kyte
If you want a comparison of the Oracle and MySQL community, be sure to also check out Sheeri Cabral’s keynote address at the 2010 MySQL User Conference for reference.
I just don’t get this I don not know what the technology is behind the search box at MySQL Documentation but it annoys me when I want to see the syntax of a command and Search can’t find the page, when a dedicated page exists and I’m using the exact syntax of both the command the title of the page. In this example the search was for SHOW WARNINGS.
Google find the page as first link!
Wednesday night of the MySQL track of ODTUG Kaleidoscope will include an evening with Last Comic Standing comedian, John Heffron. It should be great way to unwind after day 3 of the conference. Black vodka anybody. 
Check out the MySQL Schedule for more information of presentations for the 4 days. More details is also available here.
We have all been caught out with using SET and not realizing that the default GLOBAL Scope (since 5.0.2) does not change the current SESSION scope.
I was not aware until today that changing GLOBAL scope has some exceptions that also automatically affect SESSION scope.
What I expected with a change in the GLOBAL scope is no affect SESSION scope. For example.
mysql> SHOW GLOBAL VARIABLES LIKE 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 131072 | +------------------+--------+ 1 row in set (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 131072 | +------------------+--------+ 1 row in set (0.00 sec) mysql> SET GLOBAL read_buffer_size=1024*256; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 131072 | +------------------+--------+ 1 row in set (0.00 sec)
However I was no prepared for this when changing an important variable for transaction management.
mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> SET autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec)
However even more perplexing was the following message:
mysql> SET GLOBAL autocommit=0; ERROR 1228 (HY000): Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
So this is another case were the definition of variables is not applicable in a GLOBAL level, yet the tools of the trade represent in some manner misleading information.
To prove my point, here is another new concurrent session started after the above.
mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec) mysql> SHOW SESSION VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec)
The MySQL Documentation also had an incorrect specification with description regarding this scope, Bug #54215
By default MySQL allows you to create user accounts and privileges with no password. In my earlier MySQL Best Practices: User Security I describe how to address the default installation empty passwords.
For new user accounts, you can improve this default behavior using the SQL_MODE variable, with a value of NO_AUTO_CREATE_USER. As detailed via the 5.1 Reference Manual
NO_AUTO_CREATE_USER
Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.
Having set this variable I attempted to show the error of operation to demonstrate in my upcoming “MySQL Idiosyncrasies that bite” presentation.
mysql> show global variables like 'sql_mode'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | sql_mode | NO_AUTO_CREATE_USER | +---------------+---------------------+ 1 row in set (0.00 sec) mysql> show session variables like 'sql_mode'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | sql_mode | NO_AUTO_CREATE_USER | +---------------+---------------------+ 1 row in set (0.00 sec)
mysql> CREATE USER superuser@localhost; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON *.* TO superuser@localhost; Query OK, 0 rows affected (0.00 sec) mysql> exit
What the? Surely this isn’t right.
$ mysql -usuperuser mysql> SHOW GRANTS; +--------------------------------------------------------+ | Grants for superuser@localhost | +--------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'localhost' | +--------------------------------------------------------+ mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.1.39 | +-----------+
Well that’s broken functionality.
What should happen as described in Bug #43938 is a cryptic message as reproduced below.
mysql> GRANT SELECT ON foo.* TO 'geert12'@'localhost'; ERROR 1133 (42000): Can't find any matching row in the user table mysql> GRANT SELECT ON *.* TO geert12@localhost IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.00 sec)
It seems however that the user of CREATE USER first nullifies this expected behavior, hence new Bug #54208.
Having just written an interview response about NoSQL concepts for a RDBMS audience it was poetic that an inconspicuous title “(4 of 3)” highlights that both a MySQL read scalable implementation via replication and a NoSQL solution can share a common lack of timely consistency of data. For the sake of Group Commit I hope my data is always consistent at some location at some point in time as soon as possible.
In attempting to comment to Kristian Nielsen’s Fixing MySQL group commit (part 4 of 3) I was forced to watch an ad before I could even add a comment. Go jump Live Journal, it’s quicker to write my own blog post.
And if anybody is still reading, I had just written the following.
“There is clearly a place for NoSQL solutions. The two primary types of products are a key/value store and a schema-less solution. You need to learn the strengths, benefits and weaknesses of both. For a RDBMS resource the lack of transactions, the lack of joins and the concept of eventually consistent can take some time to accept.”
The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.
Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.
The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?
One of those lines is the list of hosts that connected to MySQL for a query, for example.
# Hosts 4 192.168.40... (2), 192.168.40... (2)... 2 more # Hosts 3 99.99.245.14 (12), 999.106.206.167 (6)... 1 more
The problem is I want to know what that 1 more is so I can gather a complete list of IP addresses that connect to this server. You do that with the –show-all=host argument.
Without
$ cat external.tcpdump | ./mk-query-digest --type tcpdump | grep Hosts | uniq -c
#
1 # Hosts 3 99.99.245.14 (12), 999.106.206.167 (6)... 1 more
1 # Hosts 1 99.99.139.140
With
$ cat external.tcpdump | ./mk-query-digest --type tcpdump --show-all=host | grep Hosts | uniq -c
1 # Hosts 3 99.99.245.14 (12), 999.106.206.167 (6), 99.99.139.140 (2)
1 # Hosts 1 99.99.139.140
You can apply the same principle to the Users as well with –show-all=user
$ cat external.tcpdump | ./mk-query-digest --type tcpdump --show-all=user | grep Users | uniq -c
1 # Users 2 xxx (13), phpmysqlmo... (5)
49 # Users 1 xxx
The problem is a still gett a truncation of the name ‘phpmysqlmo…’ That’s the one thing I’m trying to uncover, because that IP and usernme are not valid permissions for this system.
While I use this tcpdump command for MySQL query analysis with mk-query-digest, I found recently that it didn’t work on FreeBSD
$ tcpdump -i bge0 port 3306 -s 65535 -x -n -q -tttt -c 5 tcpdump: syntax error
It left me perplexed and reading the man page seemed to indicate my options were valid. I tried a few variances just to be sure without success.
$ tcpdump -i bge0 -c 5 port 3306 -x tcpdump: syntax error $ tcpdump -i bge0 -c 5 port 3306 -q tcpdump: syntax error $ tcpdump -i bge0 -c 5 port 3306 -tttt tcpdump: syntax error
The solution was actually quite simple in the end, it had nothing to do with the commands, it had everything to do with the order of them. Placing port as the last option solved the problem.
$ tcpdump -i bge0 -s 65535 -x -n -q -tttt -c 5 port 3306
$ uname -a FreeBSD db4.example.com 6.3-RELEASE-p3 FreeBSD 6.3-RELEASE-p3 #0: Wed Jul 16 05:13:50 EDT 200
It is critical that you do not use the default MySQL installation security, it’s simply insecure.
When installed, MySQL enables any user with physical permissions to the server to connect to the MySQL via unauthenticated users. MySQL also provides complete access to all super user privileges via the ‘root’ user with no default password.
$ mysql -uroot mysql> SELECT host,user,password FROM mysql.user; +--------------+------+-------------------------------------------+ | host | user | password | +--------------+------+-------------------------------------------+ | localhost | root | | | server.local | root | | | 127.0.0.1 | root | | | localhost | | | | server.local | | | +--------------+------+-------------------------------------------+
What you see here are two types of users.
The absolute minimum you should do, is run the provided optional command for immediate improvements mysql_secure_installation. When running this command, you’re prompted for the following
options — the output has been trimmed for presentations purposes.
$ mysql_secure_installation Enter current password for root (enter for none): Set root password? [Y/n] y New password: Re-enter new password: Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] Y Remove test database and access to it? [Y/n] Y Reload privilege tables now? [Y/n] Y
If you revisit permissions now, you’ll see what you would expect from a more initially secure installation.
mysql> SELECT host,user,password FROM mysql.user; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *FDAF706717E70DB8DDAD0C5214B13770E1A80B0E | +-----------+------+-------------------------------------------+
This is only the first step to hardening your MySQL instance and server.
The following are my recommendations for the minimum MySQL security permissions:
There is a lot more information about physical Operating System security and the MySQL permission/privilege model to be discussed. One product I know of that help is SecuRich – The MySQL Security Package featuring roles, password history and many other cool functionalities.
A recent post by Lance Miller quoted the following.
I cant tell you how many times in the past 18 months that I’ve found real enterprises running vulnerable databases with default passwords, weak passwords and no real permissions management. It’s bad enough that the stats right now are this (so I guess I can tell you):
– 9 out of 10 organizations have a Microsoft SQL Database with a blank “sa” password (or an sa password of “sa”, “sql” or “password”)
– 9 out of 10 organizations have a Postgres Database with a default password
– 9 out of 10 organizations have a Sybase Database with a default password
The article didn’t include MySQL however some organizations don’t change the default password, probably not 9 of 10 in my experience.
More Information
The implementation of MySQL Monitoring is critical for any organization that uses a database and wants to avoid the inevitable disaster. There are 3 important components that all serve a key purpose to “MySQL Monitoring” in general:
There is no one option for Monitoring that is significantly better then another. A short list of what’s on offer can be found at http://monitoring-mysql.com/monitoring-products. What’s important is you have monitoring in place so historically you can review situations and compare across your servers and enabling the better identification of physical or database bottlenecks. My recommendations for products are Cacti which is packaged with most popular Linux distrubtions, so can be installed via a single apt-get/yum/rpm command, and the MySQL Cacti Templates. This is not the best product solution or combination, it’s in my opinion the most common and covers all the essential bases.
It’s best to define a different web server (i.e. publically accessible) to be the monitoring server rather then installing the web interface on any single DB server.
Alerting is key to notify you of a problem without the need for somebody to be viewing a screen and see it happening via your monitoring. The identification of high CPU load, a disk nearing capacity, database locking etc often helps avoid a current problem before it becomes some level of disaster. Almost all companies use Nagios or a derivative such as the main fork Icigna or products that include Nagios like Opsview or Groundworks.
While both Monitoring and Alerting are necessary, they both however lack a key component necessary for successful administration. That is timing. Both of these earlier options sample, e.g. 1 minute or generally 5 minutes (by default), but problems can happen quickly. This is why each organization needs to have a Dashboard. I don’t know of any products here unless you try and adjust a monitoring product, but want’s needed is a very lightweight and very business centric single page of Green/Yellow/Red status’s of your environment including databases, webservers, response time and traffic etc. This is for the state of NOW. A Dashboard should sample every 5 to 10 seconds. I have seen larger and more successful companies have various home grown implementations. I developed a product for one company and it included the following on a single page. You can see the screen output in a presentation at http://www.slideshare.net/ronaldbradford/10x-performance-improvements. This included
It is often important to be able to identify a key problem and then drill down to this more quickly rather then the usual “the website is slow” question and having to investigate the same repetitive tasks. You need to automate, and be more pro-active in response especially to load and locking issues.
Information is only have the requirement, it is what you do with this information that determines how to be proactive rather then reactive. If I was the DBA of a company I’d do even more then these initial 3 steps which are a necessary base. I would also monitor for example:
While these are important if you have only limited resources, too much information can be just as much of a burden then people just start ignoring the information and miss what’s important.
We have secured approval for our final two speakers and now have a full schedule for the 4 day MySQL track at ODTUG Kaleidoscope conference. The conference is in Washington DC from Monday June 28th to Thursday July 1st. Welcome to Josh Sled and Craig Sylvester that will be joining our existing list of speakers.
This conference will include 19 sessions of dedicated MySQL content from Monday thru Thursday by well qualified MySQL community members, as well a forums discussion and reception on Monday night. You don’t need to be an Oracle developer to get the benefit of this conference. We will offering a discount code for MySQL attendees in the upcoming days.
If you are in the DC area, the Monday night forum (known as the sundown sessions) as well as the reception are FREE for the MySQL community. This was a great jesture of the Oracle Developer Tools Users Group to openly invite the MySQL community to meet and interact. We ask that you register your name and email for confirmation of numbers.
Not part of my Don’t Assume series, but when a client states “Why is my database slow””, you need to determine if indeed the database is slow.
Some simple tools come to the rescue here, one is Firebug. If a web page takes 5 seconds to load, but the .htm file takes 400ms, and the 100+ assets being downloaded from one base url, then is the database actually slow? Tuning the database will only improve the 400ms portion of 5,000ms download.
There some very simple tips here. MySQL is my domain expertise and I will not profess to improving the entire stack however perception is everything to a user and you can often do a lot. Some simple points include:
Like tuning a database, some things work better then others, some require more testing then others, and consultants never tell you all the tricks.
As with everything in tuning, do your research and also determine what works in your environment and what doesn’t. Two excellent resources to start with are Steve Souders and Best Practices for Speeding Up Your Web Site by Yahoo.
There have been a few posts by Sheeri and Baron today on the MySQL sort_buffer_size variable. I wanted to add some more information about this buffer, what is impacted when it is changed, and what to do about it?
The first thing you need to know is the sort_buffer_size is a per session buffer. That is this memory is assigned per connection/thread. I’ve seen clients that set this assuming it’s a global buffer Don’t Assume – Per Session Buffers.
Second, internally in the OS usage independently of MySQL, there is a threshold > 256K. From Monty Taylor “if buffer is set to over 256K, it uses mmap() instead of malloc() for memory allocation. Actually – this is a libc malloc thing and is tunable, but defaults to 256k. From the manual:” . He goes on in a further to shows that impact > 256K for a buffer is 37x slower. This applies to all per session buffers, not just sort buffer. Now I have heard recently about this limit being 512K. I wasn’t able to nail down the specific speaker to see if this was a newer library or kernel or OS.
With MySQL instrumentation and the sort_buffer_size we are lucky, there is the Sort_merge_passes status variable. While it’s not perfect, it does indicate if the size of the buffer is in-sufficient, however even if we use a sort_buffer_size of say 256K, and you see Sort_merge_passes increasing slowly, does not indicate you have to increase the buffer.
So, all this does not tell you how to tune the buffer? Unfortunately with MySQL there is no actual easy answer. You do need to monitor the mysqld memory usage overall, especially if you are using persistent connections. A connection/thread will not release the memory assigned until it is closed, so it’s important to monitor for memory creep of the PGA, knowing what your initial SGA is. Morgan Tocker wrote a patch in Bug #33540 to create a RESET CONNECTION type command.
You do need to look for memory as a bottleneck. You need to learn how MySQL use memory, not just the sort_buffer_size. I actually started many years ago to write global/session variables to indicate when buffers were used, and how much and I started with the sort_buffer_size which was buried down in some very old filesort code. When I sought the input of an expert C coder around this, they wondered how the code, especially a loop handler even actually worked.
Nobody knows what the optimal setting is, and that’s the problem. In certain areas especially memory usage the MySQL instrumentation is simply non-existent, and I’d like to see this as something that is fixed.
In conclusion, if I ever see a sort_buffer_size above 256K, e.g. 1M or 2M, I always reset it to 256K. My reasoning is simple. Until you have evidence in your specific environment increasing the buffer makes performance better, it’s better to use a smaller value. There are bigger wins, like not using sorting, or better design, or even better simplifying or eliminating SQL.
sort_buffer_size and Knowing Why
How to tune MySQL’s sort_buffer_size
Read Buffer performance hit
more on malloc() speed
As the program chair for the recently announced MySQL Track at the ODTUG Kaleidoscope conference located in Washington DC we are also looking into an associated free community event for MySQL locals in addition to a dedicated track for 4 days.
Please let us know your name and email via the form at http://ronaldbradford.com/ODTUG/free-event/ so we can provide more details in the coming week as we try to finalize logistics.
Registration will be necessary for attendance however for now we just want to know who is local so we can provide more details soon!
Updated. Full details of the free Monday night sundown sessions and reception can be found at MySQL track with free event at Kaleidoscope 2010
I’m happy to announce that the MySQL community has been given the opportunity to speak at the upcoming Oracle Developer Tools User Group (ODTUG) Kaleidoscope conference in Washington DC. We will be releasing more details this week of the MySQL presentations and topics and we are finalizing details of possible options to include the local MySQL community during the event.
The various independent Oracle User Groups in North America that embody “by the community and for the community” have been very positive with including the MySQL community. With the Sun/MySQL now Oracle community team of Giuseppe Maxia, Lenz Grimmer, Kaj Arnö and Oracle ACE Directors Sheeri K Cabral and myself we have been happy with the openness and willingness to include us in the larger Oracle ecosystem.
We’ll announce the schedule when we finalize it, but we have had a great response from an impressive list of speakers.