Shulou(Shulou.com)06/01 Report--

Infobright is a very powerful column storage database and an efficient data warehouse based on MySQL.

The reason for using the data warehouse is that at present, the data in the MySQL database grows rapidly, and some historical tables are cleared regularly, but these historical data are also used in the later statistical analysis. With the increase of the amount of data, the query becomes slower and slower, while the unique storage format of the database warehouse can reduce the occupation of disk space, and the query speed is greatly improved. Infobright is chosen because it locks more data types, closer to mysql, and saves disk space. The main statistical query is not on the data warehouse, and the occasional query speed is not required to be optimal. As the community version (ICE version), the most × × annoyance cannot do DML operation. The actual test is also the case. If there is something inappropriate to insert data, you can only drop table. Then create a new table and import data.

Advantages of infobright:

1. Data compression: suitable for storing a large amount of data and saving disk storage

two。 Query speed: basic summary statement, sum avg min max count () groupby is faster than oracle, no indexing, no partitioning of large tables, saving a lot of work, suitable for data summary, report statistics

Limitations of infobright ICE:

1. Infobright does not support DML (only select is supported)

Only select can support, update/insert/deltete and truncate table can not be used, insert table data: use laod data infile

two。 Only single click and single core are supported

Since the rpm package is officially provided by Infobright, it is relatively easy to install:

Rpm-ivh infobright-4.0.7-0-x86_64-ice.rpm-prefix=/usr/local/infobright

This will install to / usr/local/infobright/infobright-4.0.7-0-x86_64

For the whole installation process, it is quite simple, but the more tedious thing is to set the relevant parameters:

A. configure memory size

Vim / usr/local/infobright-4.0.7-x86_64/data/brighthouse.ini

Modify the configuration of memory to participate in its recommended value to set:

# Critical MemorySettings #

# System Memory Server Main HeapSize ServerCompressed HeapSize Loader Main HeapSize

# 32GB 24000 4000 800

# 16GB 10000 1000 800

# 8GB 4000 500 800

# 4GB 1300 400 400

# 2GB 600 250 320

B. the system has its own configuration function

Sh / usr/local/infobright-4.0.7-x86_64/postconfig.sh

This script can change the configuration of datadir,cachedir,socket,port, etc., and needs to be executed by root. The information returned after execution is as follows: (if you do not need to modify, all N can be done)

Infobright post configuration

-

Using postconfig you can:

-

(1) Move existing data directory to other location

(2) Move existing cachedirectoryto other location

(3) Configure server socket

(4) Configure server port

(5) Relocate datadir pathto an existing data directory.

Please type'y'foroption that you want or press ctrl+c for exit.

Current configuration:

-

Current config file: [/ etc/my-ib.cnf]

Current brighthouse.ini file: [/ usr/local/infobright-4.0.7-x86_64/data/brighthouse.ini]

Current datadir: [/ usr/local/infobright-4.0.7-x86_64/data]

Current CacheFolder in brighthouse.ini file: [/ usr/local/infobright-4.0.7-x86_64/cache]

Current socket: [/ tmp/mysql-ib.sock]

Current port: [5029]

-

(1) Do you want to copy current datadir [/ usr/local/infobright-4.0.7-x86_64/data] to a new location? [y/n]: n

(2) Do you want tomovecurrent CacheFolder [/ usr/local/infobright-4.0.7-x86_64/cache] to a new location? [y/n]: n

(3) Do you want tochangecurrent socket [/ tmp/mysql-ib.sock]? [y/n]: n

(4) Do you want tochangecurrent port [5029]? [y/n]: n

(5) Do you want torelocateto an existing datadir? Current datadir is [/ usr/local/infobright-4.0.7-x86_64/data]. [y/n]: n

-

-

No changes has been made.

-

C, set character set

Infobright does not support Chinese by default. In order to better support Chinese, you need to set the default character set.

Vim / etc/my-ib.cnf

Find the following

Collation_server=latin1_bin

Character_set_server=latin1

Modify it to:

Collation_server=utf8_bin

Character_set_server=utf8

D, install startup script

Cp / usr/local/infobright-4.0.7-x86_64/share/mysql/mysql.server / etc/init.d/mysqld-ib

Vim / etc/init.d/mysqld-ib

Find the following two lines of code:

Conf=@BH_CONF@

User=@BH_USER@

Modified to:

Conf=/etc/my-ib.cnf

User=root## can only start the service with root. Other users need to study how to start it.

Other related instructions:

/ etc/init.d/mysqld-ib stop

/ etc/init.d/mysqld-ib restart

Add boot up:

Chkconfig-add mysqld-ib

E, Mysql security settings

PATH=$PATH:/usr/local/infobright-4.0.7-x86_64/bin

Mysql_secure_installation

Add a remotely connected account to mysql after completion, and just want to enter mysql client with the following command:

Mysql-uroot-p

The method to add a remote user is as follows:

GRANT ALL PRIVILEGESON *. * TO'infobright'@'%'IDENTIFIEDBY'password'WITHGRANTOPTION;FLUSHPRIVILEGES

Import mysql data into infobright

CREATE TABLE `ricci_ var` (

`id`int (11) DEFAULT NULL

`name` varchar (20) DEFAULT NULL

`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

) ENGINE=InnoDB

Select * from ricci_var into outfile'/tmp/var.csv' fields terminated by', 'optionally enclosed by' "'lines terminated by'\ n'

# the information such as the delimiter set in the red part of the imported data should also be the same for import

# when exporting data, you need to store it in the database directory or / tmp directory. MySQL5.7 does not have the permission to export and needs to be configured.

Impact of secure_file_priv configuration on data import and export:

Secure_file_priv mysqld uses this configuration item to complete the restrictions on data import and export

1. Restrictions on mysqld do not allow import | Export

Mysqld-secure_file_prive=null

2. Restrict the import of mysqld | Export can only occur in the / tmp/ directory.

Mysqld-secure_file_priv=/tmp/

3. No restrictions on import / export of mysqld

/ etc/my.cnf

[mysqld]

Secure_file_priv

Import data into infobright database

Add the same type of table to the inf library before importing data:

Load data infile "/ tmp/var.csv" into table var fields terminated by', 'optionally enclosed by' "'linesterminated by'\ n'

Import text data into inf:

[root@localhost home] # cat aa.txt

1, "noe,two or three", 2222

2,3,4

Create table aa (id int,textfiedl varchar (40), number int)

Load data infile "/ home/aa.txt" into table aa fields terminated by', 'enclosed by' "'

Mysql > select * from aa

+-+

| | id | textfiedl | number | |

+-+

| | 1 | noe,two or three | 2222 | |

| | 2 | 3 | 4 |

+-+

(1) "" is to separate the columns.

(2) enter after each line is written, otherwise it will not enter.

# # verify the correctness by yourself

When guiding the database, it is not recommended to use client-side tools to do it, it always feels like a lot of holes.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.