MySQL installation and configuration

Software environment

Operating system

MySQL server runs on Linux, Windows or Mac OS X platforms with a default port of 3306. It stores the data in [Installation Path]/data.

Software version

SuperMap GIS server supports MySQL version 5.6.16 or higher.

Installing MySQL on Windows systems

MySQL provides msi and zip archives for installation. msi installation is simple, you can follow the wizard to complete the installation process. The following part takes MySQL 5.6.31 zip package as an example to introduce the installation and configuration methods:

Go to the MySQL official website to dowmload the zip package, then decompress it.

Install MySQL

Open the command line window in the bin directory of the MySQL package path, input the following command to install MySQL:

mysqld -install

When "service successfully installed" appeares, it means installation was successful.

Start MySQL

Input the following command to start MySQL service:

net start mysql

Log into MySQL

After start successfully, enter MySQL to manage and configure. Log into MySQL with the following command:

mysql -u root -p

If this is the first time you use MySQL, there is no password, just press Enter.

To modify the login password, for example, set the password to iserver, using the command:

set password =password('iserver');

After finish the setting, when logging into MySQL again, you need to input this password.

Modify encoding format

To use MySQL in iServer/iPortal/iEdge, the encoding format should be set to utf-8 to support Chinese. After entered MySQL, run the following commands:

set character_set_server=utf8;

set character_set_database=utf8;

You can also modify the encoding format directly in the MySQL configuration file. Copy the my-default.ini file in the MySQL installation directory to the same directory, rename it as my.ini. Then edit the file, copy the following codes directly into the my.ini file to overwrite the existing Mysqld] line:

[client]

default_character_set=utf8

[mysqld]

character_set_server=utf8

After finish modifying, you can check whether the encoding format is modified successfully:

show variables like 'character%';

Note

If you are using a MySQL database version of 5.7 or above, you need to add the following code to the [mysqld] entry in the my.ini configuration file:

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Installing MySQL on Linux systems

This section takes ubuntu 15.10.1 as an example to introduce the installation method of MySQL in Linux.

Get and install MySQL

You can execute the following command to get the MySQL package and install it:

sudo apt-get install mysql-server

During the installation process, you can set the password of root. Here uses iserver.

After finish installation, execute the following command to check if mysql is working properly:

sudo netstat -tap|grep mysql

If MySQL does not start properly, run the following command to restart it:

sudo /etc/init.d/mysql restart

Modify encoding format

In Linux, you can modify the encoding format in the configuration file. Copy the MySQL configuration file to the /etc/ directory:

cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/my.cnf

Modify encoding format in my.cnf:

vi /etc/my.cnf

Add the following setting in [mysqld]:

default-character-set=utf8

Input the ":wq!" at the end of the file. Save the modification and exit.

After finish modifying, you can check whether the encoding format is successful in MySQL:

mysql -u root -p

show variables like 'character%';

Note

The installation of MySQL in a Linux environment defaults to distinguish the case of the table name. You can set it as case-insensitive by using the following setting before starting the MySQL service for the first time. The specific step is in the MySQL configuration file my.cnf, add the following code at the end of [mysqld]:

lower_case_table_names=1

If you are using a MySQL database version of 5.7 or above, you need to add the following code to the [mysqld] entry in the my.ini configuration file:

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Using MySQL

The commands for using MySQL on Windows or Linux are the same. Use root to log in to MySQL:

mysql -u root -p

Create datasource

You can create a database to store the information with the following command:

create database [database name];

For example:

create database iserver;

Enable remoete access for a database

Switch to the database to be set:

use iserver

Input the following command to allow the specified users in other machine to operate the database remotely:

grant all privileges on *.* to 'root'@'%' identified by 'iserver' with grant option;

The 'root' is the specified user name, and 'iserver' is the password. '%' represent allowing remote connections. You can also specify the IPs that need access remotely.

After modifying, run the following command to refresh the configuration to make it work:

flush privileges;

After the configurations of the above steps, you can use the MySQL database to store security information in iServer/iPortal/iEdge. For details on how to configure it, see: Security information storage. In addition, you can use MySQL to store portal data in iPortal. For details, see: Portal data storage configuration.