preface
Tip: Here you can add the general contents to be recorded in this article:
For example, with the continuous development of artificial intelligence, machine learning technology is becoming more and more important. Many people have started learning machine learning. This paper introduces the basic content of machine learning.
Tip: the following is the main content of this article. The following cases can be used for reference
1. Directory environment
Installation directory: / usr/local/mysql
Data directory: / mysql/data
2. Download the installation package (*. tar.gz)
> cd /usr/local > wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
3. Unzip the file and rename it
> tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz > mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql/
4. Add to user and user group
3.1 add users and user groups and check
> groupadd mysql > useradd -r -g mysql mysql > groups mysql
3.2 add installation directory to user group
> chown -R mysql:mysql /usr/local/mysql/
Note: the end of the last '/' in the writing method of: / usr/local/mysql / indicates all files in the changed directory
5. Initialize mysql
> cd /usr/local/mysql/ > bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data/
Note: after initialization, an error datadir=/mysql/data / will be reported that this directory does not exist. You need to create this directory file manually
> mkdir -p /mysql/data > mkdir -p /mysql/log #In subsequent configuration files: specified log directory > touch -p /mysql/log/slow.log #Configuration file: slow log file > chmod 644 /mysql/log/slow.log #read-write permission > > mkdir -p /mysql/logs #In subsequent configuration files: specified error log directory > touch -p /mysql/logs/error.log #Error log directory in configuration file > chmod 644 /mysql/logs/error.log #read-write permission > > chown -R mysql:mysql /mysql/ #This command must be executed on the last line, otherwise the file will be unreadable or writable, and the startup will fail
Note: 1. Initialize again
> cd /usr/local/mysql/ > bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data/
Note: 2. Check whether the temporary password is generated during each initialization (look carefully at the last line of the console)
(borrow others' drawings)
[important]: if there are other errors (check the error file / mysql/logs/error.log), modify the error location, cycle this operation, and then initialize until the initialization is successful
6. The most important part [my.cnf] is interpreted. The personal test is available. Read each line once
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] ###############################Basic settings##################################### #Unique number of MySQL service. Each MySQL service Id must be unique server-id = 37 #The default service port number is 3306 port = 3306 #mysql installation root directory basedir = /usr/local/mysql #Location of mysql data file datadir = /mysql/data #Temporary directories such as load data infile will be used tmpdir = /tmp #Set the directory where the socke file is located socket = /tmp/mysql.sock #It is mainly used for MyISAM storage engine. If multiple servers are connected to a database, it is recommended to note the following contents #skip-external-locking #Only the IP address can be used to check the login of the client without using the host name skip_name_resolve = 1 #Transaction isolation level: the default is repeatable read, and mysql is the default repeatable read level (there may be many gap locks under this level, which may affect performance) #transaction_isolation = READ-COMMITTED transaction_isolation = REPEATABLE-READ #The default character set of the database. The mainstream character set supports some special emoticons (special emoticons occupy 4 bytes) #character-set-server = utf8mb4 character-set-server = utf8 #The database character set corresponds to some sorting rules. Note that it corresponds to character set server #collation-server = utf8mb4_general_ci collation-server = utf8_general_ci #Set the character set when the client connects to mysql to prevent garbled code #init_connect='SET NAMES utf8mb4' init_connect='SET NAMES utf8' #Whether it is case sensitive to sql statements. 1 means insensitive lower_case_table_names = 1 #maximum connection max_connections = 400 #Maximum number of wrong connections max_connect_errors = 1000 #TIMESTAMP if the declaration NOT NULL is not displayed, NULL value is allowed explicit_defaults_for_timestamp = true #The size of the SQL packet sent. If there is a BLOB object, it is recommended to modify it to 1G max_allowed_packet = 128M #The MySQL connection will be forcibly closed after it is idle for more than a certain time (unit: seconds) #MySQL default wait_ The timeout value is 8 hours, interactive_ The timeout parameter needs to be configured at the same time to take effect #interactive_timeout = 1800 interactive_timeout = 300 #wait_timeout = 1800 wait_timeout = 300 #The maximum value of the internal memory temporary table is set to 128M. #For example, a temporary table may be used when a large amount of data is group by and order by, #If this value is exceeded, it will be written to disk and the system IO pressure will increase #tmp_table_size = 134217728 tmp_table_size = 1G #max_heap_table_size = 134217728 max_heap_table_size = 1G #Disable the query result set caching function of mysql #In the later stage, test and decide whether to open it according to the business situation #In most cases, close the following two items #query_cache_size = 0 query_cache_size = 512M #query_cache_type = 0 query_cache_type = 1 #####################Memory settings allocated to user processes BEGIN############################# ##Each session will allocate the memory size set by the parameter #It is used for sequential scanning of tables, and the read data is temporarily stored in read_ buffer_ In size, when the buff is full or read, the data is returned to the upper caller #Generally 128kb ~ 256kb, used for MyISAM #read_buffer_size = 131072 #It is used for random reading of tables. It is used when reading by sorting according to a non indexed field, #Generally 128kb ~ 256kb, used for MyISAM #read_rnd_buffer_size = 262144 #Used when order by or group by #It is recommended to adjust it to 2M first and observe it later #sort_buffer_size = 2097152 sort_buffer_size = 4M #Generally, there are no major transactions in the database. It is set to 1~2M, and the default is 32kb #binlog_cache_size = 524288 binlog_cache_size = 16M max_binlog_cache_size = 256M ########################Memory settings allocated to user processes END############################ #How many requests can be stored in the stack in a short time before MySQL temporarily stops responding to new requests #Official recommendation back_log = 50 + (max_connections / 5), and the number of capping is 900 back_log = 100 ############################log setting########################################## #Database error log file #log_error = error.log log_error = /mysql/logs/error.log #Slow query sql log settings slow_query_log = 1 #slow_query_log_file = slow.log slow_query_log_file = /mysql/log/slow.log #Check sql that is not used to index log_queries_not_using_indexes = 1 #For log_ queries_ not_ using_ After indexes is enabled, record the frequency of slow sql and the number of records per minute log_throttle_queries_not_using_indexes = 5 #As the slave database takes effect, how slow sql is copied from the database will also be recorded log_slow_slave_statements = 1 #The number of seconds for slow query execution. This value must be reached to be recorded #long_query_time = 8 long_query_time = 0.5 #The number of rows retrieved must reach this value to be recorded as a slow query #min_examined_row_limit = 100 #The expiration time of the mysql binlog log file, which is automatically deleted after expiration #expire_logs_days = 5 expire_logs_days = 14 ############################Master slave copy settings##################################### #Enable mysql binlog function log-bin=mysql-bin #binlog records the contents and records each line operated binlog_format = ROW #For binlog_ Format = in row mode, the contents of the log are reduced and only the affected columns are recorded binlog_row_image = minimal #master status and connection information is output to the mysql.slave table_ master_ Info master_info_repository = TABLE #the slave's position in the relay logs is output to the mysql.slave table_ relay_ log_ Info relay_log_info_repository = TABLE #This parameter is required for cascading replication when it takes effect from the library log_slave_updates #The relay log can be self-healing when it takes effect from the library relay_log_recovery = 1 #Errors ignored during master-slave replication when it takes effect as a slave library slave_skip_errors = ddl_exist_errors #####################redo log and binlog Relationship settings for BEGIN######################### #(step 1) prepare dml related SQL operations, and then persist the cache in the redo log buff to disk #(step 2) if the previous preparation is successful, continue to persist the transaction log to binlog #(step 3) if the previous is successful, write a commit record in the redo log #When innodb_flush_log_at_trx_commit and sync_ When binlog is 1, it is the safest, #When the mysqld service crashes or the server host crashes, the binary log can only lose at most one statement or transaction. #However, when both are set to 1, frequent io operations will occur, so this mode is also the slowest. #When InnoDB_ flush_ log_ at_ trx_ If commit is set to 0, the crash of mysqld process will result in the loss of all transaction data in the last second. #When InnoDB_ flush_ log_ at_ trx_ If commit is set to 2, all transaction data in the last second may be lost only when the operating system crashes or the system loses power. #When commit ting a transaction, the mode of controlling the redo log buff persistent disk is 1 by default #innodb_flush_log_at_trx_commit = 2 innodb_flush_log_at_trx_commit = 1 #During commit transaction, the mode of writing mysql binlog log is controlled. The default is 0 #innodb_flush_log_at_trx_commit and sync_ When binlog is 1, mysql is the most secure, but the performance pressure is also the greatest sync_binlog = 1 ####################redo log and binlog Relationship settings for END############################ ############################Innodb set up##################################### #The unit of data block is 8k, and the default is 16k. 16k CPU pressure is slightly lower, and 8k has a large throughput for select #innodb_ page_ The parameter value of size also affects the maximum index length. 8k is smaller than 16k #innodb_page_size = 8192 #Generally, 60% - 70% of physical storage is set innodb_buffer_pool_size = 1G #After 5.7.6, the default is 16M #innodb_log_buffer_size = 16777216 innodb_log_buffer_size = 128M #This parameter is specific to unix and linux. It is directly annotated on the window. The default value is NULL #O_DIRECT reduces conflicts between the operating system level VFS cache and Innodb's own buffer cache innodb_flush_method = O_DIRECT #This format supports compression and is the default after 5.7.7 innodb_file_format = Barracuda #CPU multi-core processing capacity setting, assuming that the CPU is 2 4-core, the settings are as follows #More reading and less writing can be set to a ratio of 2:6 innodb_write_io_threads = 4 innodb_read_io_threads = 4 #Increase the number of dirty pages refreshed and merged inserts, and improve disk I/O processing capacity #Default value 200 (unit: page) #This value can be determined based on the recent IOPS of the disk innodb_io_capacity = 500 #In order to obtain the maximum waiting time of locked resources, the default is 50 seconds. If this time is exceeded, the following error will be reported: # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction innodb_lock_wait_timeout = 30 #Adjust the percentage of recently used pages in the buffer pool read and dump. By setting this parameter, you can reduce the number of pages dumped innodb_buffer_pool_dump_pct = 40 #Set the directory where the redoLog file is located, and the redoLog records the specific operation contents of the transaction #innodb_log_group_home_dir = /opt/mysql/redolog/ innodb_log_group_home_dir = /mysql/redolog/ #Set the directory where the undo log file is located. Undo log is used for transaction rollback #innodb_undo_directory = /opt/mysql/undolog/ innodb_undo_directory = /mysql/undolog/ #In InnoDB_ log_ group_ home_ The number of redoLog files in dir. The contents of redoLog files are written by cyclic overwrite. innodb_log_files_in_group = 3 #MySQL 5.7 officials suggest setting it as large as possible, which can be close to innodb_buffer_pool_size of size #Previously, setting this value to a large value may lead to mysql downtime, and the recovery time is too long. Now the recovery is much faster #This value reduces the frequency with which dirty data is flushed to disk #Maximum InnoDB_ log_ file_ size * innodb_ log_ files_ in_ Group < = 512gb, single file < = 256gb innodb_log_file_size = 1024M #Set that the space occupied by the undoLog file can be recycled #The undo log file of MySql before 5.7 has been increasing and cannot be recycled #innodb_undo_log_truncate = 1 #innodb_undo_tablespaces = 3 innodb_undo_tablespaces = 0 #innodb_undo_logs = 128 #5.7.7 this parameter is enabled by default to control the maximum length of single column index to 3072 #innodb_large_prefix = 1 #5.7.8 the default is 4, and the number of threads of Inodb background cleaning work #innodb_purge_threads = 4 #By setting the configuration parameter innodb_thread_concurrency to limit the number of concurrent threads, #Once the number of execution threads reaches this limit, additional threads will sleep for microseconds before being placed in the pair queue, #You can set the parameter innodb_thread_sleep_delay to configure sleep time #The default value is 0. On the official doc, for InnoDB_ thread_ Some suggestions are also given for the use of Concurrency: #(1) If the number of concurrent user threads in a workload is less than 64, it is recommended to set innodb_thread_concurrency=0； #(2) If the workload is always serious or even reaches the peak occasionally, it is recommended to set InnoDB first_ thread_ concurrency=128, ###And continuously reduce this parameter, 96, 80, 64, etc., until the number of threads that can provide the best performance is found #innodb_thread_concurrency = 0 #Strong all deadlock error messages are recorded in error.log. Previously, only the latest deadlock information can be viewed through the command line innodb_print_all_deadlocks = 1 ############################Other settings######################################## [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 256k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #Increase the number of openable files per process open-files-limit = 28192
7. Startup file configuration
> cd /usr/local/mysql/support-files > cp mysql.server /etc/init.d/mysqld
Edit this file:
> vim /etc/init.d/mysqld
Modify these two lines:
Line 46: basedir=/usr/local/mysql
Line 47: datadir=/mysql/data
8. Start and test
> /etc/init.d/mysqld Starting MySQL.. SUCCESS! > telnet 127.0.0.1 3306 Trying 127.0.0.1... Connected to 127.0.0.1. ..................................................
Note: Connected to 127.0.0.1 appears, indicating that the startup is successful.
Sometimes Starting MySQL... SUCCESS appears! It may also fail.
9. Reset password for initial login
> mysql -uroot -p enter password: mysql> alter user user() identified by "wms_10088"; ..................................................
10. Continued ------------ configure automatic startup after startup
[summary]:
I've been doing this for three days. I have to say I'm a real dish~~
Before I made my own decisions, when I copied, it was estimated that one set would take only 1-2 days. In actual combat, it is much more complicated than when you test and write a demo;