Mysql introduction and compilation and installation, Mysql introduction compilation and Installation

Mysql introduction and compilation and installation, Mysql introduction compilation and Installation

=========== MYSQL Working principle:

1. database Introduction: Simply put, a database is a data storage warehouse, which stores data on disks according to specific rules and uses the database management system, effectively manage data stored in the database;
SQL language: DBMS manages databases through the SQL language. SQL is a query and design language used to store data, query data updates, and manage relational databases;
The SQL language is divided into three parts.

DDL (Data Definition Language) statements are used to define database tables, index attempts, and triggers.
Create
Alter
Drop
Rename
Truncate
Comment
The DML (Data Maniplulation Language) statement is used to insert Data, query Data, update Data, and delete Data.
Select
Insert
Update
Delete
DCL (Data Control Language) Statement, used to Control User Access Permissions
Grant
Revoke

Common Database Systems:
1. Oracle
2. IBM DB2
3. Sybase
4. Microsoft Access and SQL Server
5. Open-source PostgreSQL
6. Open-source Mysql

Mysql features:
1. cross-platform, supporting a variety of operating systems, AIX FreeBSD HP-UX Linux MacOS NovellNetware OpenBSD Solaris windows, etc.
2. multithreading is supported to make full use of hardware resources (CPU resources)
3. supports large databases and can process tens of millions of large databases.
4. Support for multiple plug-in storage engines

Mysql-server-5.6

1) InnoDB can now limit the excessive memory usage when a large number of tables are opened (as mentioned here) (a third-party patch already exists)
2) Enhanced InnoDB performance. Such as splitting kernel mutex, flush operations from the main thread, multiple perge threads, and large memory optimization.
3) InnoDB deadlock information can be recorded in the error log for easy analysis
4) MySQL5.6 supports delayed replication, allowing the slave and master to control a time interval to facilitate data recovery in special circumstances.
5) Enhanced Table Partitioning
6) The Row-level replication function of MySQL is enhanced to reduce the overhead of disk, memory, network, and other resources (only record fields that can be determined by rows)
7) Implement crash-safe using Binlog
8) the replication event adopts crc32 verification to enhance data consistency during master/slave replication.
9) added log_bin_basename (previously, there was no binlog location information in variables and it was inconvenient to monitor the database)

2. Compile and install MYSQL:
1> install the dependency package:
Yum install gcc-c ++ ncurses-devel perl cmake bison
2> create a user:
# Groupadd mysql
# Useradd mysql-g mysql-s/sbin/nologin
# Mkdir-p/usr/local/mysql // Mysql installation directory
# Mkdir-p/data/mysqldb // Mysql data DIRECTORY
# Mkdir-p/data/mysqldb/binlog // create a BINLOG directory
# Mkdir-p/data/mysqldb/log // create the MYSQL daily LOG directory
3> install Mysql:
# 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 \

* ** Note: delete the cmakecache.txt file after re-configuring the configuration.

=========== Compile and parse:

# Cmake compilation command

-DCMAKE_INSTALL_PREFIX =/usr/local/mysql: Set the mysql installation directory.

-DMYSQL_UNIX_ADDR =/tmp/mysql. sock: sets the listening socket path, which must be an absolute path name. The default value is/tmp/mysql. sock.
-DSYSCONFDIR =/etc: the configuration file is placed under/etc /.
-DDEFAULT_CHARSET = gbk: Set the server's character set.
By default, MySQL uses the latin1 (CP1252 Western Europe) Character Set. The cmake/character_sets.cmake file contains the allowed Character Set Name columns.
-DDEFAULT_COLLATION = gbk_chinese_ci: sets the server's sorting rules.
-DWITH_INNOBASE_STORAGE_ENGINE = 1
-DWITH_ARCHIVE_STORAGE_ENGINE = 1
-DWITH_BLACKHOLE_STORAGE_ENGINE = 1
Storage engine options:
MyISAM, MERGE, MEMORY, and CSV engines are compiled to the server by default and do not need to be explicitly installed.
Statically compile a storage engine to the server and use-DWITH_engine_STORAGE_ENGINE = 1
Available storage engine values: 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 value is 3306.

-DENABLE_DOWNLOADS = 1
Whether to download optional files. For example, if this option is enabled (set to 1), cmake downloads 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
Authorize the Mysql data storage directory
# Chown mysql: mysql/data/mysqldb-R
Authorize the Mysl log directory
# Chown mysql: mysql/data/mysqldb/binlog/
# Chown mysql: mysql/data/mysqldb/log/
Start initialization:
/Usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/data/mysqldb

Copy the Mysql Service Startup Script
# Cp/usr/local/mysql/support-files/mysql. server/mysql. server/etc/init. d/mysqld
# Chmod 755/etc/init. d/mysqld

Modify the mysql Service Startup Script (required for multiple instances)
# Vim/etc/init. d/mysqld
Basedir =/usr/local/mysql
Datadir =/data/mysqldb

6> Configuration File
There will be no ready-made products in the enterprise in the future. cnf. If my is not found in/etc/after compilation and installation. cnf so please go to the original compilation directory, that is, extract the package directory to find the support-files directory, copy the my-default.cnf to/etc/my. cnf;

# Cd mysql-5.6.31
# Cp support-files/my-default.cnf/etc/my. cnf

========= Detailed description of the configuration file:
# Vim/etc/my. cnf
[Client]
Port = 3306 // port number connected to the client
Socket =/tmp/mysql. sock // location of the sock file connected to the client

[Mysqld]
# Base
Port = 3306 // default mysql port number, which can be modified
User = mysql // specified by the mysql user
Socket =/tmp/mysql. sock // port used for connection
Pid-file =/tmp/mysql. pid // master PID file for process running
Basedir =/usr/local/mysql // Mysql installation directory
Datadir =/data/mysqldb // Mysql data DIRECTORY
Tmpdir =/opt/mysqltmp // temporary Mysql table directory
Open_files_limit = 10240 // Number of opened file handles
Explicit_defaults_for_timestamp
SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
Federated // supports three modes: SQL syntax and data validation. SQL _mode
The ANSI mode and loose mode verify the inserted data. If the data type does not conform to the defined type or length, adjust the data type or save the data with truncation, and report the "warning" warning.

TRADITIONAL mode and strict mode: when data is inserted into mysql, strict data verification is performed to ensure that data cannot be inserted and an error is reported. When used for transactions, transaction rollback is performed.

STRICT_TRANS_TABLES mode. In strict mode, the data is strictly verified. The error data cannot be inserted and an error is reported.

Server_id = 706 // unique service identification number for master-slave replication. The value ranges from 1
# Replicate-do-db = posp // define the database for unique Replication
# Replicate-ignore-db = mysql // defines the database for unique Replication
# Log-slave-updates = 1 // This option is followed by master-slave replication. Open the master copy information on the server
Event_scheduler = 1 // enable the Time Scheduler

Max_connections = 2000 // # maximum number of concurrent connections. To increase this value, you must increase the number of file descriptors that can be opened.
Max_connect_errors = 10000 // if a user initiates a connection error that exceeds this value, the next connection of the user will be blocked,
Interactive_timeout = 600 // number of seconds before the server closes the interactive connection
Wait_timeout = 600 // number of seconds waiting for activity before the server closes the non-interactive connection
Skip-name-resolve // # garnt, you must use an ip address. You cannot use a host name. disable DNS resolution.
Sync_binlog = 0 // ensures transaction security. The default value is 0.
Log_bin_trust_function_creators = 1 // open the mysql UDF

Character-set-server = utf8 // sets 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 = 32 m // binlog cache size
Max_binlog_cache_size = 10 Gb // set the maximum binlog cache.
Binlog_stmt_cache_size = 32 m // sets the minimum binlo cache.
Table_open_cache = 2048 // table descriptor cache size, which can reduce the number of times files are opened/closed
Max_binlog_size = 1024 m // set the maximum binlog log file size to 1 GB.
Binlog_format = mixed // binlog log format.
Log_output = FILE // confirm the output to the log FILE
Log-error =/data/mysqldb/log/mysql-error.log // mysql system error log output
Slow_query_log = 1 // open slow log records
Slow_query_log_file =/data/mysqldb/log/mysql-slow_query.log // define the path of slow log output
General_log = 0 // define common logs
General_log_file =/data/mysqldb/log/mysql-general-query.log // define the path of the normal log output
Expire-logs-days = 30 // Log retention time is 30 days
Relay-log =/data/mysqldb/binlog/relay-bin // define the reloa_log copy address
Relay-log-index =/data/mysqldb/binlog/relay-bin.index // define reloa_log index

# Buffer
Sort_buffer_size = 2 m // # buffer required for sorting again when the MyISAM table changes. Usually 64 m enough
Read_buffer_size = 2 m // the buffer size used for full table scan in the MyISAM table.
Read_rnd_buffer_size = 2 m // # When a row is read from a sorted sequence, the row data is read from the buffer to prevent disk seek
Join_buffer_size = 2 m // # InnoDB is used to cache data and

Net_buffer_length = 16 k // when mysqldump is executed, the maximum length of net buffer is 16 Mb, and the default value is 1 Mb.
Max_allowed_packet = 512 m // The Independent size of each connection. The size is increased dynamically.
Bulk_insert_buffer_size = 32 m // # When burst insert is detected, the buffer will be allocated for myisam
Max_heap_table_size = 512 m // # memory table size
Tmp_table_size = 512 m // # maximum size of the temporary table in the internal memory
Thread_cache_size = 100 // # Number of reusable cache threads, which can be adjusted as appropriate
Query_cache_size = 256 m // # specify the buffer size of the MySQL query result. Tuning can be adjusted as appropriate
Query_cache_limit = 10 m // # maximum number of result sets of a single SQL statement cached. The default value is 4 kb. Tuning can be adjusted as appropriate
Query_cache_min_res_unit = 4 k
Key_buffer_size = 16 m // # keyword buffer size, which is generally used to Buffer Index blocks of the MyISAM table
Myisam_sort_buffer_size = 64 m // # This is allocated in each thread. Therefore, be careful when setting the callback.
Myisam_max_sort_file_size = 10g // The maximum temporary file size allowed when the MySQL index is re-built. If the file size is larger than this value, the index will be created through the key value buffer (slower)
Myisam_repair_threads = 1 // # If a table has more than one index, MyISAM

# Innodb
Innodb_file_per_table = 1 # // # You can change InnoDB to an independent tablespace mode. Each table in each database generates a data space.
Innodb_data_file_path = ibdata1: 2048 M: autoextend // # If you only have a single logic driver to save your data, a single auto-increment file is enough.
Innodb_log_file_size = 128 m // # size of each log file in the log group,
Innodb_log_files_in_group = 3 // # Total number of files in the log group. Generally, 2 ~ 3 is better
Innodb_buffer_pool_size = 1 GB // innodb cache pool size
Innodb_buffer_pool_instances =-1
Innodb_max_dirty_pages_pct = 70 // # maximum allowable ratio of dirty pages in the InnoDB buffer pool. All values can be 60-90.
# Innodb_thread_concurrency = 8
Innodb_flush_method = O_DIRECT
Innodb_log_buffer_size = 16 m # buffer size used to buffer log data. When this value is full, InnoDB must refresh the data to the disk.
Innodb_flush_log_at_trx_commit = 2
0 indicates that logs are only written to the log file every second and the log file is refreshed to the disk #2 indicates that the log file written to the log file is refreshed to the disk only after each submission.

[Mysql]
No-auto-rehash # You can use the tab key in the command to complete
Prompt = (\ u @ \ h) [\ d] \ _ # display the host name in the Mysql Command
Default-character-set = utf8 // sets the character set combination.

7> start the service and set environment variables:
#: The last part of the vim/etc/profile file.
MYSQL =/usr/local/mysql/bin
PATH = $ PATH: $ MYSQL
Export PATH
#: Source/etc/profile // takes effect

Start the service
#/Etc/init. d/mysqld start

Note:
Log_slave_updates. Disable this option on the master server and enable this option on the slave server.

=============================== FAQs:
Errors encountered after starting the database:

Solution:
1. Comment out a line about innodb_data_file_path in the configuration file;
2. Delete ibdata1 ibprofile0 ibprofile1

Error 2:
1. Create a folder that is missing;
2. Set permissions for the previous directory of the folder to be created:
Chown mysql: mysql/tmpopt/
3. You may need to delete the following files in your directory:
Ibdata1 ibprofile0 ibprofile1