====== AIX RPMs: MySQL, percona-ganglia-mysql and phpMyAdmin ======

Following up to the previous posts, regarding the topic of [[http://www.bityard.org/blog/?btng%5Bpost%5D%5Btags%5D=RPM|AIX RPM packages]], here are three new AIX RPM packages related to the MySQL database:

  * **MySQL**: A fast, stable, multi-user, multi-threaded, open source SQL database system. Unfortunately at the moment only MySQL versions 5.1.x or lower can be successfully build on AIX. More recent versions of MySQL were developed with a strong focus on Linux rather than a focus on protability, which is why their build fails on AIX.

During the first startup of the MySQL server via the ''/etc/rc.d/init.d/mysql'' init script, several initial configuration tasks are performed. The command ''/opt/freeware/bin/mysql_install_db'' is called to initialize the MySQL data directory ''/srv/mysql/'' with system databases and tables. Also, the MySQL user ''procadm'' is added. This user is used for maintainance, startup and shutdown tasks, mainly triggered by the init script ''/etc/rc.d/init.d/mysql''.

In addition to the stock MySQL files, the simple custom backup script ''/opt/freeware/libexec/mysql/mysql_backup.sh'' is provided. After defining a database user with the appropriate permissions to backup all databases, the user and its password should be added to the configuration file ''/opt/freeware/etc/mysql/mysql_backup.conf'' of the backup script. With the run_parts wrapper scripts:
<code>
/opt/freeware/etc/run_parts/daily.d/050_mysql.sh
/opt/freeware/etc/run_parts/monthly.d/050_mysql.sh
/opt/freeware/etc/run_parts/weekly.d/050_mysql.sh
</code>

simple daily, weekly and monthly full database backups are done to the filesystem (''/srv/backup/mysql/''). From there they can be picked up by a regular filesystem backup with e.g. the TSM BA client.

[[/pub/rpms/mysql/|RPM packages]]
{{filelist>/srv/www/vhosts/bityard.de/pub/rpms/mysql/mysql*&style=table&tableheader=1&tableshowdate=1&tableshowsize=1}}

[[/pub/srpms/mysql/|Source RPM packages]]
{{filelist>/srv/www/vhosts/bityard.de/pub/srpms/mysql/mysql*&style=table&tableheader=1&tableshowdate=1&tableshowsize=1}}
<
  * **percona-ganglia-mysql**: A Ganglia addon provided by [[http://www.percona.com/|Percona]], designed to retrieve performance metrics from a MySQL database system and insert them into a Ganglia monitoring system. The package ''percona-ganglia-mysql-scripts'' is to be installed on the host running the MySQL database. It depends on the ''ganglia-addons-base'' package, specifically on the cronjob (''/opt/freeware/etc/run_parts/conf.d/ganglia-addons.sh'') defined by this package. In the context of this cronjob all avaliable scripts in the directory ''/opt/freeware/libexec/ganglia-addons/'' are executed. For this specific Ganglia addon, a MySQL user with the necessary permissions to access the MySQL performance metrics and the MySQL users password need to be configured:

<code config /opt/freeware/etc/ganglia-addons/ganglia-addons-mysql.cfg>
ENABLED="yes"
#SS_GET_MYSQL_STATS="/opt/freeware/libexec/percona-ganglia-mysql/ss_get_mysql_stats.php"
MY_USER="root"
MY_PASS="insert your password here"
#MY_HOST="127.0.0.1"
#MY_PORT="3306"
</code>

The package ''percona-ganglia-mysql'' is to be installed on the host running the Ganglia webinterface. It contains templates for the customization of the MySQL metrics within the Ganglia Web 2 interface. To use the templates, install the RPM and copy the Ganglia Web 2 (gweb2) templates from ''/opt/freeware/share/percona-ganglia-mysql/graph.d/*.json'' to the Ganglia Web 2 graph directory. Usually this is ''/ganglia/graph.d/'' in the DocumentRoot of the webserver, but may be different in the individual Ganglia Web 2 installation, depending on the setting of the:
<code>
$conf['gweb_root']
$conf['graphdir']
</code>

configuration parameters.

To include the MySQL templates in all host definitions, copy the report view definition ''/opt/freeware/share/percona-ganglia-mysql/conf/default.json'' to the Ganglia Web 2 configuration directory. Usually this is ''/ganglia/conf/'' in the DocumentRoot of the webserver, but may be different in the individual Ganglia Web 2 installation, depending on the setting of the:
<code>
$conf['views_dir']
</code>

configuration parameter.

To include the MySQL templates only in individual host view configurations for specific hosts, copy the report view definition ''/opt/freeware/share/percona-ganglia-mysql/conf/default.json'' to the Ganglia Web 2 configuration directory with the file name ''host_<hostname>.json''.

[[/pub/rpms/ganglia-addons/|RPM packages]]
{{filelist>/srv/www/vhosts/bityard.de/pub/rpms/ganglia-addons/percona-ganglia-mysql*&style=table&tableheader=1&tableshowdate=1&tableshowsize=1}}

[[/pub/srpms/ganglia-addons/|Source RPM packages]]
{{filelist>/srv/www/vhosts/bityard.de/pub/srpms/ganglia-addons/percona-ganglia-mysql*&style=table&tableheader=1&tableshowdate=1&tableshowsize=1}}
<
  * **phpMyAdmin**: A web based administration tool for MySQL, MariaDB and Drizzle database systems. After the installation of the RPM package, phpMyAdmin can be used by directing a browser to ''<nowiki>http://<hostname>/phpmyadmin/</nowiki>''. To enable more advanced phpMyAdmin functions, several additional configuration steps are necessary:

    - Import the phpMyAdmin database schema:
<cli>
$ mysql -u root -p < /opt/freeware/doc/phpmyadmin-<version>/examples/create_tables.sql
</cli> <
    - Create a database user for phpMyAdmin and grant the necessary MySQL permissions:
<cli>
$ mysql -u root -p
mysql> GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT (
  Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv,
  Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
  Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
  Execute_priv, Repl_slave_priv, Repl_client_priv ) ON mysql.user TO 'pma'@'localhost';
mysql> GRANT SELECT ON mysql.db TO 'pma'@'localhost';
mysql> GRANT SELECT ON mysql.host TO 'pma'@'localhost';
mysql> GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
  ON mysql.tables_priv TO 'pma'@'localhost';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
</cli> <
    - Configure phpMyAdmin to use the MySQL user and password:
<code php /opt/freeware/etc/phpmyadmin/config-db.php>
<?php
$dbname = 'phpmyadmin';
$dbuser = 'pma';
$dbpass = 'password';
?>
</code> <
    - Logout and login from phpMyAdmin or restart your browser. <
[[/pub/rpms/phpmyadmin/|RPM packages]]
{{filelist>/srv/www/vhosts/bityard.de/pub/rpms/phpmyadmin/phpmyadmin*&style=table&tableheader=1&tableshowdate=1&tableshowsize=1}}

[[/pub/srpms/phpmyadmin/|Source RPM packages]]
{{filelist>/srv/www/vhosts/bityard.de/pub/srpms/phpmyadmin/phpmyadmin*&style=table&tableheader=1&tableshowdate=1&tableshowsize=1}}
<