Mysql5.6 quick installation and parameter explanation, mysql5.6 parameter explanation

Mysql5.6 quick installation and parameter explanation, mysql5.6 parameter explanation
I. Required Software

Software name	Version		Current Environment	Administrator account/Password
Mysql	5.6	Install yum	CentOS6.7 System	Zxfly/zxfly

Ii. Installation instructions

Database directory	/Database/mysql	Create directory mysql
Sock file path	/Var/lib/mysql. sock	Automatic Generation
Configuration File Path	/Etc/my. cnf
Slow query Log Path	/Var/log/mysql/mysql_slow_query.log	Create a mysql Log directory
Error Log Path	/Var/log/mysql/mysqld. log
Pid File	/Var/run/mysqld. pid	Automatic Generation
Binlog Log File	/Database/mysql-bin/mysql_bin *. log	Create directory mysql-bin

3. Install 1. Download the yum source.

Address: http://dev.mysql.com/downloads/repo/yum/

Centos7 system:

Wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

Centos6 system:

Wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

2. Install rpm

Rpm-Uvh mysql57-community-release-el6-7.noarch.rpm

3. Modify yum source configuration

Vim/etc/yum. repos. d/mysql-community.repo

Install mysql5.6

1. Change enabled = 1 to enabled = 02, enabled = 0 to enabled = 1

 

4. Install mysql

Yum install mysql-community-server

5. Replace the configuration file

Master database configuration file:

1 [mysqld] 2 user = mysql 3 datadir =/database/mysql 4 socket =/var/lib/mysql. sock 5 6 skip-name-resolve 7 8 # Disabling symbolic-links is recommended to prevent assorted security risks 9 symbolic-links = 010 11 # slow_queries12 logs =/var/log/mysql/ export long_query_time = 114 slow_query_log = 115 # binlog16 server-id = 7717 log_bin =/database/mysql-bin/mysql_bin18 binlog_format = mixed19 Container = 720 21 # innodb22 Container = export G23 Container = 512M24 container = 8M25 bytes = 226 innodb_file_per_table = 127 bytes = 428 innodb_flush_method = O_DIRECT29 bytes = 200030 bytes = 600031 bytes = 200032 bytes = 033 34 # cache35 # maximum value of a temporary internal memory table 36 tmp_table_size = 2G37 character-set-server = utf838 collation-server = utf8_general_ci39 # Skip external lock 40 skip-external-locking41 # Number of connections that MySQL can save (based on actual settings) 42 back_log = 102443 # specify the size of the index buffer, which only applies to the MyISAM table, it doesn't matter if I write it here. 44 key_buffer_size = 1024M45 # This command limits the stack size of each database thread to 46 thread_stack = 256k47 # When a query continuously scans a table, mySQL will allocate a memory buffer for it 48 read_buffer_size = 8M49 # thread cache 50 thread_cache_size = 6451 # query the cache size 52 query_cache_size = 128M53 # maximum value of the temporary internal memory table, 54 max_heap_table_size = 256M55 # Put the query result into the query cache 56 query_cache_type = 157 # indicates the cache size of SQL statements containing binary logs during the transaction process 58 binlog_cache_size = 2M59 # cache table size 60 table_open_cache = 12861 # cache thread 62 thread_cache = 102463 64 wait_timeout = 1800065 # buffer size of table and table join 66 join_buffer_size = 1024M67 ## is a connection-level parameter, when this buffer is required for each connection for the first time, one-time allocation setting of memory 68 sort_buffer_size = 8M69 # random read data buffer using memory 70 read_rnd_buffer_size = 8M71 72 # connect73 # Is a security-related counter value in MySQL, it is responsible for blocking clients that fail too many attempts to prevent brute force password cracking 74 max-connect-errors = 10000075 # connections 76 max-connections = 300077 # enable query cache 78 explicit_defaults_for_timestamp = true79 # # The mysql server can work in different modes, and can apply these modes to different clients in different ways: 80 SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES81 82 [mysqldump] 83 quick84 85 [mysqld_safe] 86 log-error =/var/log/mysql/mysqld. log87 pid-file =/var/run/mysqld. pid

Mysql master database configuration file

Slave database configuration file:

1 # For advice on how to change settings please see 2 # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html 3 4 [mysqld] 5 #6 # Remove leading # and set to the amount of RAM for the most important data 7 # cache in MySQL. start at 70% of total RAM for dedicated server, else 10%. 8 # innodb_buffer_pool_size = 128 M 9 #10 # Remove lead/ing # to turn on a very important data integrity option: logging 11 # changes to the binary log between backups. 12 # log_bin 13 #14 # Remove leading # to set options mainly useful for reporting servers. 15 # The server defaults are faster for transactions and fast SELECTs. 16 # Adjust sizes as needed, experiment to find the optimal values. 17 # join_buffer_size = 128 M 18 # sort_buffer_size = 2 M 19 # read_rnd_buffer_size = 2 M 20 user = mysql 21 datadir =/database/mysql 22 socket =/var/lib/mysql. sock 23 server-id = 214 # mysql server-id 24 skip-name-resolve 25 26 # Disabling symbolic-links is recommended to prevent assorted security risks 27 symbolic-links = 0 28 29 # slow_queries 30 slow_query_log_file =/var/log/mysql/48731 long_query_time = 1 32 slow_query_log = 1 33 34 # innodb 35 bytes = 16G 36 innodb_log_file_size = 512 M 37 rows = 8 M 38 rows = 2 39 innodb_file_per_table = 1 40 rows = 4 41 innodb_flush_method = O_DIRECT 42 innodb_io_capacity = 2000 43 rows = 6000 44 rows = 2000 45 innodb_thread_concurrency = 0 46 47 #48 ## maximum value of the temporary internal memory table 49 tmp_table_size = 2G 50 character-set-server = utf8 51 collation-server = utf8_general_ci 52 # skip external lock 53 skip-external- locking 54 # Number of connections that MySQL can store temporarily (based on actual settings) 55 back_log = 1024 56 # specify the size of the index buffer, which only applies to the MyISAM table, it doesn't matter if I write it here. 57 key_buffer_size = 1024 M 58 # This command limits the stack size for each database thread to 59 thread_stack = 256 k 60 # When a query continuously scans a table, mySQL will allocate a memory buffer for it 61 read_buffer_size = 8 M 62 # thread cache 63 thread_cache_size = 64 64 # query cache size 65 query_cache_size = 128 M 66 # temporary internal memory table, each thread needs to allocate 67 max_heap_table_size = 256 M 68 # Put the query result into the query cache 69 query_cache_type = 1 70 # represents the cache size of SQL statements containing binary logs during the transaction process 71 binlog_cache_size = 2 M 72 # cache table size 73 table_open_cache = 128 74 # cache thread 75 thread_cache = 1024 76 77 wait_timeout = 18000 78 # buffer zone for table and table join the size of 79 join_buffer_size = 1024 M 80 # Is a connection-level parameter, when this buffer is required for each connection for the first time, one-time allocation setting of memory 81 sort_buffer_size = 8 M 82 # random read data buffer using memory 83 read_rnd_buffer_size = 8 M 84 85 # connect 86 # Is a MySQL security-related counter value, it blocks clients from too many failed attempts to prevent brute force password cracking 87 max-connect-errors = 100000 88 # connections 89 max-connections = 3000 90 # enable query cache 91 explicit_defaults_for_timestamp = true 92 # the mysql server can work in different modes, and can apply these modes to different clients in different ways. 93 SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES 94 95 [mysqldump] 96 quick 97 98 [mysqld_safe] 99 log-error =/var/log/mysql/mysqld. log100 pid-file =/var/run/mysqld. pid

Mysql slave Database Configuration File

Copy the configuration file to/etc/my. cnf

Master Database: cp my. cnf/etc/my. cnf

Slave Database: cp my_web_slave.cnf/etc/my. cnf

6. Edit the configuration file:

Vim/etc/my. cnf

# Replace server-id = 171

Server-id = [ip address of the server where the database is installed]

Note: If the deployed mysql service is used as a slave database, comment out the bin_log entry.

7. Create a directory

Directory where logs are created

Mkdir-p/var/log/mysql

Chown-R mysql. mysql/var/log/mysql

Directory where data is created

Mkdir-p/database/mysql

Chown-R mysql. mysql/database/mysql

Create the directory where binlog is located (skip this step if it is used as the slave database)

Mkdir-p/database/mysql-bin

Chown-R mysql. mysql/database/mysql-bin

8. Initialization and startup

Initialize Database

Mysql_install_db -- user = mysql -- datadir =/database/mysql

Start

Service mysqld start # (the first time is relatively long and needs to be initialized. Please be patient)

9. initialize the configuration

Command mysql_secure_installation

Enter current password for root (enter for none): [Press Enter to skip]

Et root password? [Y/n] y [whether to set the root password]
New password: [input: zxfly]
Re-enter new password: [input: zxfly]
Remove anonymous users? [Y/n] y [Delete Anonymous Users]
Disallow root login remotely? [Y/n] y [disable root remote logon]
Remove test database and access to it? [Y/n] y [Delete the test database]
Reload privilege tables now? [Y/n] y [refresh permission]

10. Create an Administrator Account

# Log on to mysql

Mysql-uroot-pzxfly

# Create an account grant all on *. * to 'zxfly '@' 192. 168.0.% 'identified by 'zxfly '; [authorize management user database] flush privileges; [refresh permission, write to disk] 4. Master-slave Replication

MySQL Master/Slave deployment document

5. configuration file description 1. Basic configuration [mysqld]

#	Configuration item	Description
1	User = mysql	Start user
2	Datadir =/database/mysql	Database path
3	Socket =/var/lib/mysql. sock	Sock file directory
4	Skip-name-resolve	Disable DNS resolution. Add this optimization when access is too slow
5	Symbolic-links = 0	Disable symbolic connection
6	Slow_query_log_file =/var/log/mysql_slow_query.log	Path of slow query log
7	Long_query_time = 1	Record slow queries over 1 second
8	Slow_query_log = 1	Enable slow Query
9	Servers-id = 171	Each mysql instance sets an independent server-id, which is generally defined as an IP address.
10	Log_bin =/database/mysql/mysql_bin	Path and format of the binary file
11	Binlog_format = mixed	Defines the format (Mixed Type) of binary output) Definable type: 1. row A large number of logs are generated when all logs are recorded in detail. 2. statement (default) Records SQL statements and their locations. Complex statements may be incorrectly recorded. 3. mixed The combination of the above two types is automatically selected.
12	Sync-binlog = 1	The default value 0 1 is the safest. 1. When an error occurs, set it to 1, and at most one transaction will be lost. 2. He is the slowest choice. 3. If recovery is fast, the dual-power cache mechanism should exist.
13	Expire_logs_days = 14	Retention days of binary files

[Mysqld_safe]

#	Configuration item	Description
1	Log-error =/var/log/mysqld. log	Error Log Path
2	Pid-file =/var/run/mysqld. pid	Pid file path

2. Optimization item configuration # innodb Engine Optimization

#	Configuration item	Description
1	Innodb_buffer_pool_size = 104G	Cache innodb table indexes, data, and buffer when inserting data, 1. We recommend that you set it to 80% of the memory size, 160 GB for online mounting, and 130 GB for available, so it is set to 104 GB. 2,
2	Innodb_log_file_size = 512 M	Transaction Log Size Officially recommended: Log Size * log group size (2 by default) cannot exceed 512 GB 1. The larger the value, the smaller the I/O to write to the disk, 2. The larger the value, the more difficult it is to restore an error. 3. The default value is 48 MB, and the value is changed to 512 MB, that is, 1 GB of logs are flushed in high concurrency. 4. If the value is increased, and the innodb_flush_log_at_trx_commit of main should be changed to 1 to reduce crash recovery.
3	Innodb_log_buffer_size = 8 M	Buffer size of transactions in memory
4	Innodb_flush_log_at_trx_commit = 2	Fl transaction logs to the disk every second (0: fl once per second 1: fl transactions into the disk after committing 2: transactions are executed, and then fl once per second)
5	Innodb_file_per_table = 1	Enable automatic shrinking of shared tablespace
6	Innodb_file_io_threads = 4	Number of available I/O threads
7	Innodb_flush_method = O_DIRECT	Defining the flushing mode (fdatasync by default) O_DIRECT minimizes the impact of buffering on io,
8	Innodb_io_capacity = 2000	The ability to define read/write IO depends on the disk speed and size.
9	Innodb_io_capacity_max = 6000	Maximum IO capacity per second
10	Innodb_lru_scan_depth = 2000	Generally, the value is the same as that of innodb_io_capacity.
11	Innodb_thread_concurrency = 0	No limit on the number of concurrent threads

# Cache Optimization

#	Configuration item	Description
1	Tmp_table_size = 2G	Maximum size of temporary internal (in memory) tables
2	Character-set-server = utf8	Change default Character Set
3	Collation-server = utf8_general_ci	Matching rules for specified strings
4	Skip-external-locking	Skip external lock
5	Back_log = 1024	Number of connections in the listener queue
6	Key_buffer_size = 1024 M	Keyword buffer size
7	Thread_stack = 256 k	The heap size used by the thread. This capacity exists and is reserved for each connection.
8	Read_buffer_size = 8 M	Buffer size for full table Scan
9	Thread_cache_size = 64	Retain the number of threads used for reuse in the cache
10	Query_cache_size = 128 M	Query buffer size, retain the select query, and the results can be directly returned next time in the same query
11	Max_heap_table_size = 256 M	Maximum memory capacity allowed by each table (to prevent the memory from being directly occupied by large tables)
13	Query_cache_type = 1	Enable query Cache
14	Binlog_cache_size = 2 M	The cache size reserved by the binlog record service.
15	Table_open_cache = 128	Number of tables allowed to be opened by each thread
16	Thread_caching = 1024	Number of threads reserved for Reuse
17	Thread_concurrency = 24	Number of threads to be used (generally two to four times the number of CPUs)
18	Wait_timeout = 18000	Sleep operation maximum waiting time (that is, the maximum time of a command running in the background)
19	Join_buffer_size = 1024 M	Optimized full Union. This cache is used when table Union operations are performed.
20	Sort_buffer_size = 8 M	Sort operation Cache
21	Read_rnd_buffer_size = 8 M	This cache will be placed after sorting is completed, which can reduce disk IO
22	Max-connect-errorrs = 100000	Blocks clients from too many failed attempts to prevent brute force password cracking.
23	Max-connections = 3000	Max connections
24	Explicit_defaults_for_timestamp = true	Enable query Cache
25	SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES	Define the working mode. mysql automatically selects the defined mode.

Vi. Check 1. Check whether the process exists

Ps-ef | grep mysql

2. Check whether you can log on.

Mysql-uttedudb-pttedudb

VII. Monitoring 1. zabbix-mysql monitoring 2. zabbix-mysql Master/Slave monitoring 8. Start, close, and log on 1. Start

Service mysqld start

2. Disable

Service mysqld stop

3. Restart

Service mysqld restart

4. log on

Mysql-uttedudb-pttedudb

9. Stress Testing 10. Others

Mysql backup & restoration