After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.
User Management
By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.
$ grep mysql /etc/{passwd,shadow,group}
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/shadow:mysql:!!:14796::::::
/etc/group:mysql:x:27:
Binaries
MySQL binaries are found in /usr/bin.
$ ls -l /usr/bin/mysql* -rwxr-xr-x 1 root root 314568 Feb 16 17:45 /usr/bin/mysql -rwxr-xr-x 1 root root 110776 Feb 16 14:39 /usr/bin/mysqlaccess -rwxr-xr-x 1 root root 35144 Feb 16 17:45 /usr/bin/mysqladmin -rwxr-xr-x 1 root root 112944 Feb 16 17:45 /usr/bin/mysqlbinlog -rwxr-xr-x 1 root root 7632 Feb 16 17:45 /usr/bin/mysqlbug -rwxr-xr-x 1 root root 30576 Feb 16 17:45 /usr/bin/mysqlcheck -rwxr-xr-x 1 root root 7632 Feb 16 17:45 /usr/bin/mysql_config -rwxr-xr-x 1 root root 3670 Feb 16 17:44 /usr/bin/mysql_convert_table_format -rwxr-xr-x 1 root root 22522 Feb 16 17:44 /usr/bin/mysqld_multi -rwxr-xr-x 1 root root 13073 Feb 16 17:44 /usr/bin/mysqld_safe -rwxr-xr-x 1 root root 75184 Feb 16 17:45 /usr/bin/mysqldump -rwxr-xr-x 1 root root 6356 Feb 16 17:44 /usr/bin/mysqldumpslow -rwxr-xr-x 1 root root 11648 Feb 16 17:44 /usr/bin/mysql_explain_log -rwxr-xr-x 1 root root 3245 Feb 16 14:39 /usr/bin/mysql_find_rows -rwxr-xr-x 1 root root 483 Feb 16 17:44 /usr/bin/mysql_fix_extensions -rwxr-xr-x 1 root root 5834 Feb 16 17:44 /usr/bin/mysql_fix_privilege_tables -rwxr-xr-x 1 root root 31431 Feb 16 17:44 /usr/bin/mysqlhotcopy -rwxr-xr-x 1 root root 26160 Feb 16 17:45 /usr/bin/mysqlimport -rwxr-xr-x 1 root root 13659 Feb 16 17:44 /usr/bin/mysql_install_db -rwxr-xr-x 1 root root 6586 Feb 16 17:44 /usr/bin/mysql_secure_installation -rwxr-xr-x 1 root root 16687 Feb 16 17:44 /usr/bin/mysql_setpermission -rwxr-xr-x 1 root root 28224 Feb 16 17:45 /usr/bin/mysqlshow -rwxr-xr-x 1 root root 14473 Feb 16 14:39 /usr/bin/mysql_tableinfo -rwxr-xr-x 1 root root 158192 Feb 16 17:45 /usr/bin/mysqltest -rwxr-xr-x 1 root root 42360 Feb 16 17:45 /usr/bin/mysqltestmanager -rwxr-xr-x 1 root root 15464 Feb 16 17:45 /usr/bin/mysqltestmanagerc -rwxr-xr-x 1 root root 13448 Feb 16 17:45 /usr/bin/mysqltestmanager-pwgen -rwxr-xr-x 1 root root 1312064 Feb 16 17:45 /usr/bin/mysql_tzinfo_to_sql -rwxr-xr-x 1 root root 54160 Feb 16 17:45 /usr/bin/mysql_upgrade -rwxr-xr-x 1 root root 5753 Feb 16 17:44 /usr/bin/mysql_upgrade_shell -rwxr-xr-x 1 root root 112136 Feb 16 17:45 /usr/bin/mysql_waitpid -rwxr-xr-x 1 root root 3818 Feb 16 17:44 /usr/bin/mysql_zap
The mysqld binary is found in /usr/libexec
Error Log
The MySQL error log is found in /var/log/mysqld.log
The content after an initial start of MySQL will look similar to:
cat /var/log/mysqld.log 100705 22:09:03 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 100705 22:09:03 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 100705 22:09:03 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 100705 22:09:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 100705 22:09:03 InnoDB: Started; log sequence number 0 0 100705 22:09:03 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
On the first invocation of MySQL, the InnoDB storage engine will create a default tablespace and redo logs. This is the majority of messages in the above log.
Processes
MySQL is a multi-threaded single process called mysqld. A second wrapper process mysqld_safe is generally found. This process logs stderr and also will restart the mysqld process if not found.
ps -ef | grep mysql root 14733 1 0 Jul05 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql mysql 14783 14733 0 Jul05 pts/1 00:00:10 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
Memory Usage
MySQL can have a very low memory footprint. By default the mysqld process has a 175M virtual size.
$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql" PID COMMAND RSS VSZ USER COMMAND 14275 grep 720 61136 root grep -e RSS -e mysql 14733 mysqld_s 1192 63820 root /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql 14783 mysqld 27004 179496 mysql /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
Disk Usage
The MySQL data files will be stored on a default installation in /var/lib/mysql
$ du -sh /var/lib/mysql 22M /var/lib/mysql $ ls -ld /var/lib/mysql drwxr-xr-x 4 mysql mysql 4096 Jul 13 11:50 /var/lib/mysql $ ls -l /var/lib/mysql total 20552 -rw-rw---- 1 mysql mysql 10485760 Jul 5 22:09 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jul 5 22:09 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jul 5 22:09 ib_logfile1 drwx------ 2 mysql mysql 4096 Jul 5 22:09 mysql srwxrwxrwx 1 mysql mysql 0 Jul 5 22:09 mysql.sock
The MySQL data directory includes the InnoDB tablespace datafile (ibdata1), redo logs (ib_logfile?), and the mysql directory corresponding to the mysql schema containing instance meta data.
This directory also contains the socket file, which is actually a poor location as this opens the security of this directory for world access. This will be discussed later in securing your installation.
Running MySQL
The best means of controlling the starting and stopping of mysql is to use the provided service init script mysqld
$ ls -l /etc/init.d/mysqld -rwxr-xr-x 1 root root 4286 Feb 16 17:45 /etc/init.d/mysqld
Configuration
For OEL the MySQL configuration can be found in /etc.
NOTE: MySQL can use multiple configuration files.
$ ls -l /etc/my.cnf -rw-r--r-- 1 root root 441 Feb 16 14:39 /etc/my.cnf
MySQL includes a minimalistic configuration file by default. The configuration file format is variable=value pairs for a given number of different sections, in this file [mysqld] and [mysqld_safe].
$ cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Audit
A full audit of all MySQL related files.
find / -name "*mysql*" /etc/rc.d/rc3.d/S64mysqld /etc/rc.d/rc5.d/S64mysqld /etc/rc.d/rc6.d/K36mysqld /etc/rc.d/init.d/mysqld /etc/rc.d/rc0.d/K36mysqld /etc/rc.d/rc4.d/S64mysqld /etc/rc.d/rc1.d/K36mysqld /etc/rc.d/rc2.d/S64mysqld /etc/php.d/pdo_mysql.ini /etc/php.d/mysql.ini /etc/php.d/mysqli.ini /etc/ld.so.conf.d/mysql-x86_64.conf /etc/ld.so.conf.d/mysql-i386.conf /usr/lib64/mysql /usr/lib64/mysql/mysqlbug /usr/lib64/mysql/libmysqlclient_r.so.15.0.0 /usr/lib64/mysql/libmysqlclient.so.15 /usr/lib64/mysql/libmysqlclient_r.so.15 /usr/lib64/mysql/mysql_config /usr/lib64/mysql/libmysqlclient.so.15.0.0 /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Bundle/DBD/mysql.pm /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm /usr/lib64/php/modules/mysql.so /usr/lib64/php/modules/pdo_mysql.so /usr/lib64/php/modules/mysqli.so /usr/libexec/mysqld /usr/libexec/mysqlmanager /usr/share/mysql /usr/share/mysql/mysql_system_tables.sql /usr/share/mysql/mysql_system_tables_data.sql /usr/share/mysql/mysql_fix_privilege_tables.sql /usr/share/mysql/mysql_test_data_timezone.sql /usr/share/vim/vim70/syntax/mysql.vim /usr/share/man/man8/mysqld.8.gz /usr/share/man/man8/mysqlmanager.8.gz /usr/share/man/man1/mysql.1.gz /usr/share/man/man1/mysql.server.1.gz /usr/share/man/man1/mysql_tableinfo.1.gz /usr/share/man/man1/mysql_upgrade.1.gz /usr/share/man/man1/mysqlaccess.1.gz /usr/share/man/man1/mysql_waitpid.1.gz /usr/share/man/man1/mysql_fix_extensions.1.gz /usr/share/man/man1/mysqlman.1.gz /usr/share/man/man1/mysqlbinlog.1.gz /usr/share/man/man1/mysql_install_db.1.gz /usr/share/man/man1/mysql_tzinfo_to_sql.1.gz /usr/share/man/man1/mysql_secure_installation.1.gz /usr/share/man/man1/mysqld_safe.1.gz /usr/share/man/man1/mysqladmin.1.gz /usr/share/man/man1/mysqlimport.1.gz /usr/share/man/man1/mysql_zap.1.gz /usr/share/man/man1/msql2mysql.1.gz /usr/share/man/man1/mysqlshow.1.gz /usr/share/man/man1/mysqldump.1.gz /usr/share/man/man1/safe_mysqld.1.gz /usr/share/man/man1/mysql_explain_log.1.gz /usr/share/man/man1/mysql_config.1.gz /usr/share/man/man1/mysqlbug.1.gz /usr/share/man/man1/mysqld_multi.1.gz /usr/share/man/man1/mysql_setpermission.1.gz /usr/share/man/man1/mysqlhotcopy.1.gz /usr/share/man/man1/mysql_find_rows.1.gz /usr/share/man/man1/mysql_convert_table_format.1.gz /usr/share/man/man1/mysql_fix_privilege_tables.1.gz /usr/share/man/man1/mysqldumpslow.1.gz /usr/share/man/man1/mysqltest.1.gz /usr/share/man/man1/mysqlcheck.1.gz /usr/share/man/man3/Bundle::DBD::mysql.3pm.gz /usr/share/man/man3/DBD::mysql.3pm.gz /usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz /usr/share/doc/mysql-server-5.0.77 /usr/share/doc/mysql-5.0.77 /usr/share/doc/selinux-policy-2.4.6/html/services_mysql.html /usr/share/pixmaps/comps/mysql.png /usr/share/info/mysql.info.gz /usr/share/selinux/devel/include/services/mysql.if /usr/bin/mysql_fix_extensions /usr/bin/mysql /usr/bin/mysqltestmanager /usr/bin/mysqldumpslow /usr/bin/mysql_upgrade_shell /usr/bin/mysql_convert_table_format /usr/bin/mysqlimport /usr/bin/mysqldump /usr/bin/mysqltestmanager-pwgen /usr/bin/mysql_tzinfo_to_sql /usr/bin/mysqlbug /usr/bin/mysqlhotcopy /usr/bin/mysqlaccess /usr/bin/mysqltest /usr/bin/mysqladmin /usr/bin/mysql_upgrade /usr/bin/mysqltestmanagerc /usr/bin/mysqld_safe /usr/bin/mysql_zap /usr/bin/mysql_waitpid /usr/bin/msql2mysql /usr/bin/mysql_secure_installation /usr/bin/mysql_fix_privilege_tables /usr/bin/mysqlshow /usr/bin/mysql_config /usr/bin/mysql_setpermission /usr/bin/mysql_tableinfo /usr/bin/mysql_find_rows /usr/bin/mysqld_multi /usr/bin/mysqlcheck /usr/bin/mysqlbinlog /usr/bin/mysql_install_db /usr/bin/mysql_explain_log /usr/lib/mysql /usr/lib/mysql/mysqlbug /usr/lib/mysql/libmysqlclient_r.so.15.0.0 /usr/lib/mysql/libmysqlclient.so.15 /usr/lib/mysql/libmysqlclient_r.so.15 /usr/lib/mysql/mysql_config /usr/lib/mysql/libmysqlclient.so.15.0.0 /usr/lib/python2.4/site-packages/sos/plugins/mysql.pyo /usr/lib/python2.4/site-packages/sos/plugins/mysql.pyc /usr/lib/python2.4/site-packages/sos/plugins/mysql.py /var/log/mysqld.log /var/run/mysqld /var/run/mysqld/mysqld.pid /var/lock/subsys/mysqld /var/lib/mysql /var/lib/mysql/mysql /var/lib/mysql/mysql.sock /root/.mysql_history /selinux/booleans/mysqld_disable_trans /selinux/booleans/allow_user_mysql_connect
[…] Reviewing your MySQL Installation on Oracle Enterprise Linux […]