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

Chapter II Mysql Database installation

Declare the database and instance:

It is easy to confuse two words in the database field: instance and database.

Database: on a physical operating system, a collection of file or other file types. In Mysql, database files can end with files such as frm,myd,myi, etc.

Database instance: consists of database background processes / threads and a shared memory area, which can be shared by running background threads and processes. It is important to keep in mind that the data instance is really used to manipulate database files.

The usual relationship between an instance and a database is-1 to 1, that is, an instance to a database, or a database to an instance. In the case of a cluster, there may be one database corresponding to multiple instances.

How the database system works:

(1) the user initiates a connection to the database

(2) Mysql database verification permissions. If you don't have enough authority. Directly deny, permission OK, proceed to the next step

(3) the user starts to initiate SQL statements

(4) Mysql database parser starts parsing SQL statements

(5) Mysql starts querying its own local cache, if any. Feedback directly to the user. If not, proceed to the next step.

(6) pass through the parser. Locate the SQL statement, finally find the needed data, cache a copy locally, and then feedback directly to the user.

(7) recent Mysql version 5. 0 à 5. 1 à 5. 2 à 5. 3 à 5. 4 à 5. 5 à 5. 6 à 5. 7

2.1. Install dependency packages

# yum install gcc gcc-c++ ncurses-devel perl camke bison

2.2. Create a user

# groupadd mysql

# useradd mysql-g mysql-s / sbin/nologin

# mkdir-p / usr/local/mysq

# mkdir-p / data/mysqldbl

2.3.Install Mysql

× × ×

Http://120.52.72.19/cdn.mysql.com/c3pr90ntc0td//Downloads/MySQL-5.6/mysql-5.6.31.tar.gz

# tar-xvf mysql-5.6.31.tar.gz

# cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock\

-DSYSCONFDIR=/etc\

-DDEFAULT_CHARSET=gbk\

-DDEFAULT_COLLATION=gbk_chinese_ci\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_ARCHIVE_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DMYSQL_DATADIR=/data/mysqldb\

-DMYSQL_TCP_PORT=3306

Note: to rerun the configuration, you need to delete the CMakeCache.txt file

# make & & make install is installed after compilation

Explanation:

# cmake compilation command

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql sets the mysql installation directory

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock sets the listening socket path, which must be an absolute pathname. Default is / tmp/mysql.sock

-the DSYSCONFDIR=/etc configuration file is placed under / etc/

-DDEFAULT_CHARSET=gbk sets the character set of the server.

By default, MySQL uses latin1's (CP1252 Western European) character set. The cmake/character_setsNaNake file contains a list of allowed character set names.

-DDEFAULT_COLLATION=gbk_chinese_ci sets the collation of the server.

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

Storage engine options:

MyISAM,MERGE,MEMORY, and the CSV engine are compiled to the server by default and do not need to be explicitly installed.

Statically compile a storage engine to the server, using-DWITH_engine_STORAGE_ENGINE= 1

Available storage engine values are: ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), PARTITION (partitioning support), and PERFSCHEMA (Performance Schema)

-DMYSQL_DATADIR=/data/mysqldb

Set up the mysql database file directory

-DMYSQL_TCP_PORT=3306

Set the listening port of mysql server. Default is 3306.

-DENABLE_DOWNLOADS=1

Whether you want to download optional files. For example, if this option is enabled (set to 1), cmake will download the test suite used by Google to run unit tests

2.4. Initialization

Establish a data storage directory for Mysql

# mkdir-p / data/mysqldb/

Authorize the Mysql installation directory

# chown-R mysql:mysql / usr/local/mysql

Authorize the Mysql data store directory

# chown-R mysql:mysql / data/mysqldb

Initiate initialization

/ usr/local/services/mysql/bin/mysql_install_db-user=mysql-basedir=/usr/local/mysql-datadir=/data/mysqldb

Copy the Mysql service startup script

# cp / usr/local/services/mysql/share/mysql/mysql.server / etc/init.d/mysqld

# chmod 755 / etc/init.d/mysqld

Modify the mysql service startup script

# vim / etc/init.d/mysqld

Basedir=/usr/local/services/mysql

Datadir=/data/dbdata/

2.5, configuration file

# cp support-files/my-medium.cnf / etc/my.cnf

# vim / etc/my.cnf

[client]

Port = 3306 / / the port number to which the client is connected

Socket = / tmp/mysql.sock / / location of the sock file connected by the client

[mysqld]

# base

Default port number of port = 3306 / / mysql, which can be modified

User = mysql / / mysql user specified

Socket = / tmp/mysql.sock / / the port number used for the connection

Pid-file = / tmp/mysql.pid / / the main PID file that the process is running

Basedir = / usr/local/mysql / / Mysql installation directory

Datadir = / data/mysqldb / / Mysql data directory

Tmpdir = / opt/mysqltmp / / Mysql temporary table directory

Open_files_limit = 10240 / / number of open file handles

Explicit_defaults_for_timestamp

Sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Federated / / supports sql syntax and data validation. SQL _ mode three modes

ANSI mode, loose mode, check the inserted data, if it does not meet the defined type or length, adjust the data type or truncate and save, report the "warning" warning.

TRADITIONAL mode, strict mode, when inserting data into mysql data, strictly checking the data to ensure that the data can not be inserted, reporting error errors, when used in things, things will be rolled back.

STRICT_TRANS_TABLES mode. Strict mode, strict verification of data, error data can not be inserted, report error error.

Server_id = 706 / / A unique service identification number for master-slave replication, with values ranging from 1 to

# replicate-do-db = posp / / define unique replicated libraries

# replicate-ignore-db = mysql / / define unique replicated libraries

# log-slave-updates = 1 / / the master-slave copy of this option. Open the replication master's information from the server

Event_scheduler=1 / / Open time Scheduler

Max_connections = 2000 / / # maximum number of concurrent connections. Increasing this value requires a corresponding increase in the number of file descriptors allowed to be opened.

Max_connect_errors = 10000 / / if the connection error initiated by a user exceeds this value, the user's next connection will be blocked

Interactive_timeout = 600 / / the number of seconds the server waits for activity before closing the interactive connection

Wait_timeout = 600 / / the number of seconds the server waits for activity before closing a non-interactive connection

When skip-name-resolve / / # garnt, ip must be used, hostname cannot be used, DNS parsing is disabled

Sync_binlog=0 / / ensures the security of the transaction. The default is 0

Log_bin_trust_function_creators = 1 / / Open mysql customizable function

Character-set-server = utf8 / / set character set

Default_storage_engine = InnoDB / / define the default engine

# log

Log-bin = / data/mysqldb/binlog/mysql-bin / / specifies the file name of the binlog binary log

Binlog_cache_size = 32m / / cache size of binlog

Max_binlog_cache_size = 10g / / set the maximum binlog cache

Binlog_stmt_cache_size = 32m / / set the minimum binlo cache

Table_open_cache = 2048 / / Table descriptor cache size to reduce the number of file opening / closing

Max_binlog_size = 1024m / / set the maximum binlog log file to 1G

Binlog_format = mixed / / binlog log format.

Log_output = FILE / / confirm output to log file

Log-error = / data/mysqldb/log/mysql-error.log / / mysql system error log output

Slow_query_log = 1 / / turn on slow logging

Slow_query_log_file = / data/mysqldb/log/mysql-slow_query.log / / defines the path of slow log output

General_log = 0 / / define normal log

General_log_file = / data/mysqldb/log/mysql-general-query.log / / defines the path to normal log output

Expire-logs-days = 30 / / the log is retained for 30 days

Relay-log = / data/mysqldb/binlog/relay-bin / / defines the address of reloa_log replication

Relay-log-index= / data/mysqldb/binlog/relay-bin.index / / define reloa_log index

# buffer

Sort_buffer_size = 2m / / # the buffer required to reorder when the MyISAM table changes. General 64m is enough.

Read_buffer_size = 2m / / the buffer size used for full table scan of MyISAM table.

Read_rnd_buffer_size = 2m / / # when rows are read from an already sorted sequence after sorting, row data will be read from this buffer to prevent disk seek

Join_buffer_size = 2m / / # InnoDB is used to cache data and

Net_buffer_length = 16k / / when executing mysqldump, the maximum upper limit of net buffer length is 16Mb, and the default value is 1Mb

Max_allowed_packet = 512m / / the independent size of each connection. Dynamic increase in size

Bulk_insert_buffer_size = 32m / / # this buffer will be allocated for myisam when burst inserts are detected

Max_heap_table_size = the size of the 512m//# memory table

Tmp_table_size = the maximum size of the 512m//# internal (in memory) temporary table

Thread_cache_size = 100 / / # the number of reusable threads in the cache, which can be adjusted appropriately

Query_cache_size = 256m / / # specifies the size of the MySQL query result buffer. Tuning can be adjusted appropriately

Query_cache_limit = 10m / / # cache the upper limit of the result set of a single SQL. Default 4KB. Tuning can be adjusted appropriately

Query_cache_min_res_unit = 4k

Key_buffer_size = 16m / / # keyword buffer size, which is generally used to buffer the index block of the MyISAM table

Myisam_sort_buffer_size = 64m / / # this is allocated in each thread. So you need to be careful when setting large values

Myisam_max_sort_file_size = 10g / / maximum temporary file size allowed when re-indexing. If the file size is larger than this value, the index is created through key-value buffering (slower)

Myisam_repair_threads = 1 / / # if a table has more than one index, MyISAM

# innodb

Innodb_file_per_table = 1 # / / # you can modify InnoDB to a separate tablespace schema, and each table in each database generates a data space

Innodb_data_file_path = ibdata1:2048M:autoextend / / # if you only have a single logical driver to save your data, a single self-adding file is good enough

Innodb_log_file_size = 128m / / # the size of each log file in the log group

Innodb_log_files_in_group = 3 / / # Total number of files in the log group. Generally speaking, 2-3 is better.

Innodb_buffer_pool_size = 1g / / innodb cache pool size

Innodb_buffer_pool_instances =-1

Innodb_max_dirty_pages_pct = 70 / / # the maximum percentage of dirty pages allowed in the InnoDB buffer pool. 60-90 is fine.

# innodb_thread_concurrency = 8

Innodb_flush_method = O_DIRECT

Innodb_log_buffer_size = 16m # the size of the buffer used to buffer log data. When this value is almost full, InnoDB will have to refresh the data to disk

Innodb_flush_log_at_trx_commit = 2

0 means that the log is only written to the log file about every second and the log file is flushed to disk # 2 means that the log is written to the log file after each commit, but the log file is only flushed to disk about every second

[mysql]

No-auto-rehash # can be completed using the tab key in the command

Prompt = (\ u @\ h) [\ d]\ _ # display the host name in the Mysql command

Default-character-set = utf8 / / set character set

2.6. Start the service and set environment variables

#: vim / etc/profile

MYSQL=/usr/local/services/mysql/bin

PATH=$PATH:$MYSQL

Export PATH

#: source / etc/profile

Start the service

# / etc/init.d/mysqld start

Note:

Log_slave_updates

When the master and slave copy. Turn off this option on the master (master) server and turn it on on the slave (slave)

2.7. Activity topics in this chapter

(1) Mysql database version number and package name are required to be installed: mysql-5.1.50.tar.gz

(2) the installation directory is / usr/local/services

(3) data storage directory is / dbdata

(4). The configuration file is stored as / etc/my.cnf

(5) user name is mysql

(6) the environment variable is required to be set, and the service is added to boot automatically.

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.