Difference between revisions of "Configuring the MySQL Server"

From AgileApps Support Wiki
imported>Aeric
 
(32 intermediate revisions by 3 users not shown)
Line 1: Line 1:
<includeonly>==== Configuring the MySQL Server ====</includeonly>
<includeonly>==== Configuring the MySQL Server ====</includeonly>
These instructions apply to the version of MySQL specified in the [[Software Requirements]].
These instructions apply to the version of MySQL specified in the [[Software Requirements]].
{{Note| MySQL configuration is a pre-requisite for the AgileApps installation.}}


'''Terminology:'''
'''Terminology:'''
:;MySQL Server:The database server, which has MySQL Server software installed, configured and running
:;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
:;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
::*The client connects to the database server to access data, for example, webserver.
::*For example, a webserver may be such a client
:;Configuration file:<tt>my.cnf</tt>, a MySQL configuration file that may be edited and used in the MySQL client or MySQL Server installation.
:;Configuration file:<tt>my.cnf</tt>, a MySQL configuration file that may be edited and used in the MySQL client or MySQL Server installation


=====MySQL Settings=====
=====MySQL Settings=====
Line 17: Line 17:


;Server System Variables Configuration:
;Server System Variables Configuration:
: It has been observed that <tt>READ_UNCOMMITTED</tt> does not work well with Row-Based Replication. Use one of the following configurations:
: It is observed that <tt>READ_UNCOMMITTED</tt> 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.
:: a) Either STATEMENT-BASED or MIXED replication with the [[Transaction Isolation]] level dictated by business needs.
:: b) ROW-BASED replication with <tt>transaction_isolation = READ-COMMITTED</tt>
:: b) ROW-BASED replication with <tt>transaction_isolation = READ-COMMITTED</tt>


;User Configuration:
;User Configuration:
:*Create a user account with password in MySQL to be used by the Application Server
:*Create a user account with password in MySQL<br> The Application Server will use the user details.
::*The user account should have all MySQL privileges enabled on all databases
::*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
::*Use the default root account that is created during the installation of MySQL. You can also create a non-root MySQL user to run Agile Apps. For more information, see [[Creating a non-root MySQL User to Run Agile Apps]].
;''sql-mode'' Configuration:
;''sql-mode'' Configuration:
:*For a First-Time installation:
::*Add the following code to the <tt>my.cnf</tt> file located in the MySQL Client:
::*Add the following code to the <tt>my.cnf</tt> file located in the MySQL Client:
:::<tt>sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"</tt>
:::<tt>sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"</tt>
Line 32: Line 31:
:::<tt>sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"</tt>
:::<tt>sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"</tt>


;MySQL Configuration for <tt>UTF-8</tt> Unicode character set:
;MySQL Configuration for <tt>UTF-8</tt> Unicode character set for 5.7 Version:
:*Add the following code to the <tt>my.cnf</tt> file, located in the MySQL Client:  
:*Add the following code to the <tt>my.cnf</tt> file, located in the MySQL Client:  
::<tt>default-character-set=utf8</tt>
::<tt>default-character-set=utf8</tt>
Line 38: Line 37:
::<tt>character-set-server=utf8</tt>
::<tt>character-set-server=utf8</tt>
::<tt>collation-server=utf8_general_ci</tt>
::<tt>collation-server=utf8_general_ci</tt>
::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). The list of supported languages is determined by the character set available at the server level. Hence, the <tt>UTF-8</tt> Unicode character set configuration is required as part of MySQL configuration.  
:::''Learn more:''
:::: To specify character settings at MySQL configuration time:<br>For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
:::: Specify character settings at MySQL configuration time<br>http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/charset-applications.html
 
;MySQL Configuration for <tt>UTF-8mb4</tt> Unicode character set for MySQL 8.0 Version:
{{Note| For MySQL configuration v5.7 to v8 upgrade, refer [[Upgrading to MySQL v8]].}}
:*Add the following code to the <tt>my.cnf</tt> file, located in the MySQL Client:
::<tt>default-character-set=utf8mb4</tt>
:*Add the following code to the <tt>my.cnf</tt> file, located in MySQL Server:
::<tt>character-set-server=utf8mb4</tt>
::<tt>collation-server=utf8mb4_0900_ai_ci</tt>
::The character set defines how records can be alphanumerically ordered (or grouped, sorted, filtered, indexed). The list of supported languages is determined by the character set available at the server level. Hence, the <tt>UTF-8</tt> Unicode character set configuration is required as part of MySQL configuration.
{{Note| If user does not specific in '''my.cnf''' or '''my.ini''' file then default MySQL 8 character set and collation are '''utf8mb4''' and '''utf8mb4_0900_ai_ci''' taken by server. }}
 
:::: To specify character settings at MySQL configuration time:<br>For MySQL Version 8.0, see http://dev.mysql.com/doc/refman//8.0/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 <tt>max_allowed_packet = 64M</tt> to <tt>my.cnf</tt> or <tt>my.ini</tt>
::<tt>max_allowed_packet = 8M</tt>.  
::: For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
::''Learn more:''
::: For MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html
::: http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/charset-applications.html
* Restart the MySQL server to implement the changes
:*Restart the MySQL server to implement these changes
|}
 
;regexp_time_limit Configuration:
The regular expression limit is expressed as the maximum permitted number of steps performed by the match engine, and thus affects execution time only indirectly. It is defined in milliseconds.
* Add <tt>regexp_time_limit=2048</tt> to <tt>my.cnf</tt> or <tt>my.ini</tt>
::: For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
::: For MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html
* Restart the MySQL server to implement the changes
 
=====Populate the Timezone Tables=====
=====Populate the Timezone Tables=====
Run the following program to initialize the MySQL timezone tables:
:The platform uses MySQL's timezone tables for timezone conversions. These tables are not automatically populated when MySQL is installed, so it necessary to do so after installation.
:<tt> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql</tt>
::where <tt>/usr/share/zoneinfo</tt> is the standard Linux location for the time zone files. (Your system may differ.)
''Learn more:'' https://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/mysql-tzinfo-to-sql.html


:Run the following program to initialize the MySQL timezone tables:
::<tt> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql</tt>
:::where <tt>/usr/share/zoneinfo</tt> is the standard Linux location for the time zone files. (Your system may differ.)
::For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html
::For MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html
<noinclude>
<noinclude>
====Configuring MySQL Version 5.7 root password====
# Start the mysql service using the command: <tt>service mysqld start</tt>
# Execute mysql_secure_installation script <br>A prompt appears to input the root password.<br> ''Info:'' To get temporary root password for MySQL Version 5.7 Community edition, see ''/root/.mysql_secret'' file. <br>For Commercial installation temporary password will be generated at at ''/var/log/mysqld.log''. <br>''Note:'' MySQL user password should contain at least one special character.


[[Category:Installation]]
[[Category:Installation]]
</noinclude>
</noinclude>

Latest revision as of 10:16, 28 August 2024

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

Notepad.png

Note: MySQL configuration is a pre-requisite for the AgileApps installation.

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 to access data, for example, webserver.
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 is 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
    The Application Server will use the user details.
  • The user account should have all MySQL privileges enabled on all databases
  • Use the default root account that is created during the installation of MySQL. You can also create a non-root MySQL user to run Agile Apps. For more information, see Creating a non-root MySQL User to Run Agile Apps.
sql-mode Configuration
  • 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 for 5.7 Version
  • 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). The list of supported languages is determined by the character set available at the server level. Hence, the UTF-8 Unicode character set configuration is required as part of MySQL configuration.
To specify character settings at MySQL configuration time:
For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
MySQL Configuration for UTF-8mb4 Unicode character set for MySQL 8.0 Version

Notepad.png

Note: For MySQL configuration v5.7 to v8 upgrade, refer Upgrading to MySQL v8.

  • Add the following code to the my.cnf file, located in the MySQL Client:
default-character-set=utf8mb4
  • Add the following code to the my.cnf file, located in MySQL Server:
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
The character set defines how records can be alphanumerically ordered (or grouped, sorted, filtered, indexed). The list of supported languages is determined by the character set available at the server level. Hence, the UTF-8 Unicode character set configuration is required as part of MySQL configuration.

Notepad.png

Note: If user does not specific in my.cnf or my.ini file then default MySQL 8 character set and collation are utf8mb4 and utf8mb4_0900_ai_ci taken by server.

To specify character settings at MySQL configuration time:
For MySQL Version 8.0, see http://dev.mysql.com/doc/refman//8.0/en/charset-applications.html
max_allowed_packet Configuration
  • Add max_allowed_packet = 64M to my.cnf or my.ini
For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
For MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html
  • Restart the MySQL server to implement the changes
regexp_time_limit Configuration

The regular expression limit is expressed as the maximum permitted number of steps performed by the match engine, and thus affects execution time only indirectly. It is defined in milliseconds.

  • Add regexp_time_limit=2048 to my.cnf or my.ini
For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
For MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html
  • Restart the MySQL server to implement the changes
Populate the Timezone Tables
The platform uses MySQL's timezone tables for timezone conversions. These 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.)
For MySQL Version 5.7, see https://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html
For MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html


Configuring MySQL Version 5.7 root password

  1. Start the mysql service using the command: service mysqld start
  2. Execute mysql_secure_installation script
    A prompt appears to input the root password.
    Info: To get temporary root password for MySQL Version 5.7 Community edition, see /root/.mysql_secret file.
    For Commercial installation temporary password will be generated at at /var/log/mysqld.log.
    Note: MySQL user password should contain at least one special character.