Mysql compilation, installation and brief introduction

==========MYSQL working principle diagram:

1》Introduction to database: Simply put, a database is a place where data is stored. Warehouse, which stores data on disk according to specific rules, can effectively manage the data stored in the database through the database management system;
SQL language: DBMS uses SQL language for database management. SQL language is a query and design language, mainly used to store data, query data, update and manage relational databases;
SQL language is divided into three parts

DDL (Data Definition Language) statements, using Used to define database tables, view indexes, triggers, etc. ML (Data Manipulation Language) statements are used to insert data , Query data, update data, and delete data
SELECT
Insert
UPDATE
Delete
DCL (Data Control Language) statement, used to control the user's access permissions
Grant

revoke

What are the common database systems:
                1. Oracle Oracle
            2. IBM’s DB2
              3. Sybase
                                ‐     4. Microsoft Access and SQL Server

​ ​ 6. Open source Mysql

Mysql features:
​ ​ 1. Cross-platform, supports multiple operating systems, AIX FreeBSD HP-UX Linux MacOS NovellNetware OpenBSD Solaris windows, etc.
​ ​ 2. Supports multi-threading and can make full use of hardware resources (CPU resources)
3. Supports large databases and can handle large databases with tens of millions of entries.
4. Supports multiple plug-in storage engines

mysql-server-5.6

1) InnoDB can now limit the problem of excessive memory usage when a large number of tables are opened (such as mentioned here (Arrived) (The third party has a patch)
　　2) InnoDB performance enhancement. Such as splitting the kernel mutex; separating the flush operation from the main thread; multiple perge threads; large memory optimization, etc.
3) InnoDB deadlock information can be recorded to the error log for easy analysis

　5) Enhanced table partition function

　　7)Binlog implementation crash-safe
8) Replication events use crc32 verification to enhance master/slave replication data consistency
9) Added log_bin_basename (there was no binlog location information in the variables before, which was very inconvenient for database supervision)

2》Compile and install MYSQL:
　　1>Install dependent packages:
　　　　

yum install gcc gcc-c++ ncurses-devel perl cmake bison

# groupadd mysql
       　　　　　　# useradd mysql –g mysql –s /sbin/nologin
       　　　　　　#mkdir -p /usr/local/mysql //Mysql的安装目录
       　　　　　　#mkdir -p /data/mysqldb  //Mysql数据目录
       　　　　　　#mkdir -p /data/mysqldb/binlog //创建BINLOG日志目录   
       　　　　　　#mkdir -p /data/mysqldb/log   //创建MYSQL 日常LOG目录

#tar -xvf mysql-5.6.31.tar.gz       　　　　　　
#cd mysql-5.6.31
                           
                           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  \

　　#cmake Compilation command

##　　　 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock sets the listening socket path, which must be an absolute path name. The default is /tmp/mysql.sock　　　 -DSYSCONFDIR=/etc The configuration file is placed under /etc/
　　　 -DDEFAULT_CHARSET=gbk Set the character set of the server.
                                                                                                                      　 　　 　　 　　　　　　　　　　　　　　　　　　　By default, MYSQL TO SELECT TO LATIN1 (CP1252 WESTERN EUROPEAN) CHARACTER SET TO BE USED. The cmake/character_sets.cmake file contains a list of allowed character set names.
　　　-DDEFAULT_COLLATION=gbk_chinese_ci Set the collation rules of the server.
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
Storage engine options:
The MyISAM, MERGE, MEMORY, and CSV engines are compiled into the server by default and are not required. Install explicitly.
To statically compile a storage engine to the server, use -DWITH_engine_STORAGE_ENGINE= 1
Available storage engine values ​​are: ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), PARTITION (partitioning support), and PERFSCHEM)                                        (Performance Schema)

#　　　-DMYSQL_DATADIR=/data/mysqldb Set the mysql database file directory

## -DMYSQL_TCP_PORT=3306 Set the mysql server listening port, the default is 3306

# 　　　-DENABLE_DOWNLOADS=1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　    For example, with this option enabled (set to 1), cmake will download the test suite used by Google to run unit tests
## 4> make && make isntall 5>Initialization: Authorize the Mysql installation directory chown mysql:mysql /usr/local/mysql -R

chown mysql: mysql /data/mysqldb -R
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　uce in her in in Insts >> Authorizing the Mysl log directory
      #chown mysql:mysql /data/mysqldb/binlog/
             ization :
                                                                                  ’ ’ s ’s Script’s Script’s Script’s Script’s having Scripts’ ‐  --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldb

#                 # cp /usr/local/mysql/support-files/mysql.server       /mysql.server   /etc/init.d/mysqld
                      # chmod 755 /etc/init.d/mysqld

                       mysql service startup script (required for multiple instances)
　　　　　　 #vim /etc/init.d/mysqld
　　　　basedir=/usr/local/mysql
　　　　　　　datadir=/data/mysqldb

　　　　　6> ; Configure the file

　　　# cd mysql-5.6.31
　　　#cp support-files/my-default.cnf /etc/my. cnf

==========Configuration file details:
# vim /etc/my.cnf
[client]
port = 3306 //The port number to which the client is connected
socket = /tmp/mysql.sock //The storage location of the sock file connected by the client

[mysqld]
#base
port = 3306 //The default port number of mysql can be modified
user = mysql //mysql user specified
socket = /tmp/mysql.sock //Port number used for connection
pid-file = /tmp/mysql.pid //Main PID of process running File
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 verification, etc. sql_mode three modes
ANSI mode, relaxed mode, for Insert the data for verification. If it does not meet the defined type or length, adjust the data type or truncate and save it, and report a "warning" warning

TRADITIONAL mode, strict mode, when inserting data into mysql data, perform data processing Strict verification ensures that data cannot be inserted and an error is reported. When used for transactions, transactions will be rolled back.

STRICT_TRANS_TABLES mode. Strict mode performs strict verification of data. Wrong data cannot be inserted and an error error is reported.

server_id = 706 //The unique service identification number of master-slave replication, the value ranges from 1 to
#replicate-do -db = posp //Define the only replicated library
#replicate-ignore-db = mysql //Define the only replicated library
#log-slave-updates = 1 //This option is used for master-slave replication. Open the replication master information from the server
event_scheduler=1 //Open the time scheduler

max_connections = 2000 //#Maximum number of concurrent connections. Increasing this value requires correspondingly increasing the number of file descriptors allowed to be opened. Number
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
skip-name-resolve //#When garnt, ip must be used, hostname cannot be used, DNS resolution is disabled
sync_binlog= 0 //The security of the transaction can be guaranteed. The default is 0
log_bin_trust_function_creators = 1 //Open mysql customizable function

character-set-server = utf8 //Set the character set
default_storage_engine = InnoDB //Define the default engine

#log
log-bin = /data/mysqldb/binlog/mysql-bin //Specify the file name of the binlog binary log
binlog_cache_size = 32m //The cache size of the 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 can 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 the 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 //Define the path for slow log output
general_log = 0 //Define the general log
general_log_file = /data/mysqldb/log/mysql-general-query.log //Define the path for the general log output
expire-logs-days = 30 //The time for log retention For 30 days
relay-log = /data/mysqldb/binlog/relay-bin //Define the address for reloa_log replication
relay-log-index= /data/mysqldb/binlog/relay-bin.index // Define reloa_log index

#buffer
sort_buffer_size = 2m //#The buffer required for reordering when the MyISAM table changes. Generally 64M is enough
read_buffer_size = 2m //The buffer size used for full table scan of MyISAM table.
read_rnd_buffer_size = 2m //#When reading rows from an already sorted sequence after sorting , row data will be read from this buffer to prevent disk seeking
join_buffer_size = 2m //# InnoDB is used to cache data and

net_buffer_length = 16k //When executing mysqldump, the maximum net buffer length The upper limit is 16Mb, the default value is 1Mb
max_allowed_packet = 512m //Independent size for each connection. The size increases dynamically
bulk_insert_buffer_size = 32m //#This buffer will be allocated when a burst insert is detected myisam
max_heap_table_size = 512m//#The size of the memory table
tmp_table_size = 512m//#The maximum size of the internal (in-memory) temporary table
thread_cache_size = 100 //#Cache the number of reusable threads, you can Appropriate adjustment
query_cache_size = 256m //#Specify the size of the MySQL query result buffer. Tuning can be appropriately adjusted
query_cache_limit = 10m //#The upper limit of the result set for caching a single SQL statement. Default is 4KB. Tuning can be adjusted appropriately
query_cache_min_res_unit = 4k
key_buffer_size = 16m //#The size of the keyword buffer, 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 //The maximum temporary file size allowed when MySQL rebuilds the index. If the file size is larger than this value Large, the index will be 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 #//#Yes Modify InnoDB to independent table space mode, each table in each database will generate a data space
innodb_data_file_path = ibdata1:2048M:autoextend //#If you only have a single logical driver to save your data, a single auto-increment file It's good enough
innodb_log_file_size = 128m //#The size of each log file in the log group,
innodb_log_files_in_group = 3 //#The total number of files in the log group. Generally speaking, 2~3 is better
innodb_buffer_pool_size = 1g //innodb buffer pool size
innodb_buffer_pool_instances = -1
innodb_max_dirty_pages_pct = 70 //#The maximum allowed dirty page ratio in the InnoDB buffer pool. 60-90 is OK
#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 flush the data to the disk
innodb_flush_log_at_trx_commit = 2
0 means that the log is written to the log file only approximately 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 approximately every second

[mysql]
no-auto-rehash #You can use the tab key to complete the command
prompt = (\u@\h) [\d]\_ #Display the host name in the Mysql command
default-character-set = utf8 //Set the character set

    7>Start the service and set the environment variables:
              #: Vim /etc/profile The last part of the file.
MySQL =/USR/LOCAL/MySQL/BIN
Path = $ PATH: $ MySQL
Export Path
#: Source/ETC/Profile // Effective

## 　　　　　#/etc/init.d/mysqld start

　　Note:
　　　　log_slave_updates 　　　When master-slave replication occurs. Turn off this option on the master server and enable this option on the slave

==================== Analysis of common problems:
After launching the database, the error encountered:

## This solution:

2" Delete ibdata1 ibprofile0 ibprofile1

Error two:
1》Need to create a missing folder;
2》Set permissions on the previous directory of the folder to be created:
chown mysql:mysql /tmpopt/
3》You may need to delete some files. Find the following files in your own directory and delete them:
ibdata1 ibprofile0 ibprofile1

The above is the detailed content of Mysql compilation, installation and brief introduction. For more information, please follow other related articles on the PHP Chinese website!