MySQL Database Configuration

The following changes to the my configuration file must be made. On Linux systems this file is named my.cnf and on Windows systems this file is named my.ini.

  • Change the default packet size for the server section [mysqld] and the mysqlsump [mysqldump] section.

    # change default packet size
    max_allowed_packet=16M

    The max_allowed_packet size corresponds to the maximum attachment size that ExtraView will store in the database. You should change the default of 16M as appropriate for your installation, to allow larger attachments.

  • Also in the [mysqld] section we need:

    lower_case_table_names=1

  • Example completed [mysqld] section tuned for ExtraView:

    [mysqld] section
    #must have lower case table names
    lower_case_table_names=1
    #must have a larger packet size
    max_allowed_packet=16M
    #name the transaction log file
    log-bin=mysql-bin
    #need to use ROW logging to binary log, default STATEMENT does not work
    binlog_format=ROW
    #turn off DNS lookup for client connections. Can slow things down
    skip_name_resolve

  • Example completed [mysqldump] section tuned for ExtraView:

    [mysqldump] section.
    # change default packet size
    max_allowed_packet=16M

  • innodb support must be available. Make sure that the skip configuration is commented out.

    #skip-innodb

  • Typically you will move the database files to a location of your preference:

    #*** INNODB Specific options ***
    innodb_data_home_dir="F:/MySQL Datafiles/"
    datadir="F:/MySQL Datafiles/"

  • You should make the innodb buffer pool as large as possible on your system.

    innodb_buffer_pool_size=200M

  • You should now create a database user and import an initial database. This database is supplied by ExtraView Corporation. The following script will create the MySQL database user and will perform this import. Note that if any database with the given name exists, it will be dropped before the import of the new one.

    #!/bin/sh
    # Import a MySQL ExtraView database from a backup
    # Any database by the given name will be dropped first.
    # A default user for this database will be created with the database name.
    #
    if [ "$#" -ne 5 ]
    then
      echo "usage: ImportExtraview.sh adminUser adminPassword dbname password backupname"
    exit 1
    fi
    MYSQL_ADMIN=$1
    MYSQL_ADMIN_CRED=$2
    dbname=$3
    dbuser=$3
    password=$4
    backupname=$5
    if [ ! -f $backupname.sql ]
    then
      echo "database file does not exist"
      echo "terminating..."   
      exit 1
    fi
    lowdbname="$(echo ${dbname} | tr 'A-Z' 'a-z')"
    if [ $lowdbname = mysql ]
    then
      echo "Cannot import to the mysql system database"
      echo "terminating..."
      exit 1
    fi
    cat >/tmp/imp_one$$.sql <<EOF
    drop database if exists $dbname;
    create database $dbname default character set utf8 DEFAULT COLLATE utf8_bin;
    grant all on $dbname.* to '$dbuser'@'localhost' identified by '$password';
    grant all on $dbname.* to '$dbuser'@'%' identified by '$password';
    grant trigger on $dbname.* to '$dbuser'@'localhost';
    grant trigger on $dbname.* to '$dbuser'@'%';
    grant select on mysql.* to '$dbuser'@'localhost';
    grant select on mysql.* to '$dbuser'@'%';
    grant super on *.* to '$dbuser'@'localhost';
    grant super on *.* to '$dbuser'@'%';
    EOF
    mysql --user=$MYSQL_ADMIN --password=$MYSQL_ADMIN_CRED mysql </tmp/imp_one$$.sql
    mysql --user=$dbuser --password=$password $dbname <$backupname.sql rm /tmp/imp_one$$.sql

    Attachments and MySQL Databases

    MySQL has some special considerations regarding file attachments. MySQL is very inefficient in the way it stores attachments in the database, and the Java driver software that accesses these requires a huge amount of memory when processing large attachments. To conserve memory, the maximum size of MySQL attachments is 16 MBytes. Therefore, if you believe that you are likely to store and retrieve attachments larger than this size with the MySQL database frequently, you should configure the attachments to work with external storage, where only the limitations of the operating system apply. Alternatively, you can modify the MySQL configuration file to change the default setting, although this is more expensive in terms of memory usage.

    The configuration line is:


    max_allowed_packet=16M

    You can change the 16M value as required.

    Timeout Setting

    There is a MySQL default setting for wait_timeout that is likely set to 288000 (480 minutes). It is advised that this value be increased to 31536000 (365 days). ExtraView handles its own timeouts to the database.