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 for large attachments. To conserve memory, ExtraView constrains the size of MySQL attachments to 16 MBytes. Therefore, if you believe that you are likely to store and retrieve attachments larger than this size with the MySQL database, you should configure the attachments to work with external storage, where only the limitations of the operating system apply.