Difference between revisions of "Configuring the MySQL Server"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 40: Line 40:
::The character set defines how records can be alphanumerically ordered (or grouped, sorted, filtered, indexed). Because the list of supported languages is determined by the character set available at the server level, the <tt>UTF-8</tt> Unicode character set configuration is required as part of MySQL configuration.  
::The character set defines how records can be alphanumerically ordered (or grouped, sorted, filtered, indexed). Because the list of supported languages is determined by the character set available at the server level, the <tt>UTF-8</tt> Unicode character set configuration is required as part of MySQL configuration.  
:::''Learn more:''
:::''Learn more:''
:::: Specify character settings at MySQL configuration time<br>http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/charset-applications.html
:::: Specify character settings at MySQL configuration time<br>For MySQL Version 5.5, see http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/charset-applications.html<br> For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
;max_allowed_packet Configuration:
;max_allowed_packet Configuration:
:*Add the following variable to <tt>my.cnf</tt> or <tt>my.ini</tt>
:*Add the following variable to <tt>my.cnf</tt> or <tt>my.ini</tt>
::<tt>max_allowed_packet = 8M</tt>.  
::<tt>max_allowed_packet = 8M</tt>.  
::''Learn more:''
::: For MySQL Version 5.5, see http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/charset-applications.html
::: http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/charset-applications.html
::: For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
:*Restart the MySQL server to implement these changes
:*Restart the MySQL server to implement these changes
|}
|}

Revision as of 07:00, 14 March 2017

These instructions apply to the version of MySQL specified in the Software Requirements.

Terminology:

MySQL Server
The database server, which has MySQL Server software installed, configured and running
MySQL Client
Any other computer which connects to the database server and has the MySQL Client software installed, configured and running
  • The client connects to the database server for purposes of accessing data
  • For example, a webserver may be such a client
Configuration file
my.cnf, a MySQL configuration file that may be edited and used in the MySQL client or MySQL Server installation
MySQL Settings
Storage Engine
default-storage-engine = innodb
Server System Variables Configuration
It has been observed that READ_UNCOMMITTED does not work well with Row-Based Replication. Use one of the following configurations:
a) Either STATEMENT-BASED or MIXED replication, with the Transaction Isolation level dictated by business needs.
b) ROW-BASED replication with transaction_isolation = READ-COMMITTED
User Configuration
  • Create a user account with password in MySQL to be used by the Application Server
  • The user account should have all MySQL privileges enabled on all databases
  • The default root account that is created during the installation of MySQL can be used
sql-mode Configuration
  • For a First-Time installation:
  • Add the following code to the my.cnf file located in the MySQL Client:
sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"
  • Add the following code to the my.cnf file located in the MySQL Server:
sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"
MySQL Configuration for UTF-8 Unicode character set
  • Add the following code to the my.cnf file, located in the MySQL Client:
default-character-set=utf8
  • Add the following code to the my.cnf file, located in MySQL Server:
character-set-server=utf8
collation-server=utf8_general_ci
The character set defines how records can be alphanumerically ordered (or grouped, sorted, filtered, indexed). Because the list of supported languages is determined by the character set available at the server level, the UTF-8 Unicode character set configuration is required as part of MySQL configuration.
Learn more:
Specify character settings at MySQL configuration time
For MySQL Version 5.5, see http://dev.mysql.com/doc/refman/5.5/en/charset-applications.html
For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
max_allowed_packet Configuration
  • Add the following variable to my.cnf or my.ini
max_allowed_packet = 8M.
For MySQL Version 5.5, see http://dev.mysql.com/doc/refman/5.5/en/charset-applications.html
For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
  • Restart the MySQL server to implement these changes
Populate the Timezone Tables
The platform uses MySQL's timezone tables to do timezone conversions. Those tables are not automatically populated when MySQL is installed, so it necessary to do so after installation.
Run the following program to initialize the MySQL timezone tables:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
where /usr/share/zoneinfo is the standard Linux location for the time zone files. (Your system may differ.)
Learn more: https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html