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

The following brings you source code installation mysql5.6.38 testing and tutorials, hoping to give you some help in practical application, MYSQL involves more things, the theory is not much, there are many books online, today we use the accumulated experience in the industry to do an answer.

1. Environment centos7 x64

CentOS Linux release 7.4.1708 (Core) shuts down selinux

Download https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.38.tar.gz

Yum install gcc gcc-c++ ncurses-devel cmake libaio bison perl-Module-Install.noarch-y

2. Permissions

Establish a user

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

The database storage directory is set to mysql user mysql group

Mkdir-p / data/mysqldata/3306 is used to store the database and grant permissions

Chown-R mysql.mysql / data/mysqldata/3306

3. Compile mysql5.6.38.tar.gz and decompress tar zxvf mysql5.6.38.tar.gz

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql3306-DMYSQL_DATADIR=/data/mysqldata/3306-DSYSCONFDIR=/usr/local/mysql3306/etc-DMYSQL_UNIX_ADDR=/usr/local/mysql3306/mysql.sock-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DWITH_READLINE=1-DMYSQL_TCP_PORT=3306-DENABLED_LOCAL_INFILE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DEXTRA_CHARSETS=all-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci

And then execute

Make & & make install

4. Complete compilation and configure mysql service permissions

# cd / usr/local/mysql3306

# chown-R mysql:mysql.

# mkdir-p / data/mysql

# mkdir-p / usr/local/mysql3306/etc

# chown mysql.mysql-R / data

# chmod 775-R / data

Scripts/mysql_install_db-user=mysql-basedir=/usr/local/mysql3306-datadir=/data/mysqldata/3306/

Consistent with configuration my.cnf

# chown-R root:mysql. (set permissions to root users and mysql groups, cancel read and write execution permissions for other users, and leave only mysql "rx" read execution permissions, other users do not have any permissions)

# # chown-R mysql:mysql. / data (database storage directory is set to mysql user mysql group)

# # chmod-R ug+rwx. (read and write execution permissions are granted, and all other user permissions are deleted, only for mysql users.)

# cp support-files/my-default.cnf / usr/local/mysql3306/etc/my.cnf (can be placed in / etc/my.cnf)

Modify the my.cnf configuration, first use the simple configuration, test the database is normal, and then add the optimized configuration

# vi / etc/my.cnf

# [mysqld] add the following:

Vi / etc/my.cnf

[mysqld]

Basedir=/usr/local/mysql3306

Datadir=/data/mysqldata/3306

Port=3306

Socket=/data/mysqldata/3306/mysql.sock

Server_id=1

[mysqld_safe]

Log-error=/data/mysqldata/3306/log/mysqld.log

Note to modify datadir=/data/mysqldata/3306, you need to add-- datadir=/data/mysqldata/3306 in scripts/mysql_install_db-- user=mysql

5. Start mysql

# bin/mysqld_safe-- user=mysql &

See if it succeeds.

# ps-ef | grep mysql**

Add mysql startup service to the system service

# cp support-files/mysql.server / etc/init.d/mysql3306

You can now start mysql using the following command

# service mysql3306 start

Stop the mysql service

# service mysql3306 stop

Restart the mysql service

# service mysql3306 restart

6. Modify the password of root user

# cd / usr/local/mysql3306

#. / bin/mysqladmin-u root password

New password:

Confirm new password:

Enter and set the new password in the following prompts.

# service mysql3306 restart; (restart mysql CVM)

7. Log in to mysql test

# cd

# vim .bash _ profile

Modify .bash _ profile

PATH=$PATH:$HOME/bin:/usr/local/mysql3306/bin

Then source .bash _ profile

# mysql-uroot-p

Mysql > use mysql

Mysql > select host,user from mysql.user

8. My.cnf configuration in our production environment

#

# # mysql configure file my.cnf

#

[client]

Port = 3306

Socket = / usr/local/mysql3306/mysql.sock

[mysql]

Prompt= "\ u@mysqldb\ R:\ m:\ s [\ d] >"

No-auto-rehash

[mysqld]

User = mysql

Port = 3306

Basedir = / usr/local/mysql3306

Datadir = / data/mysqldata/3306

Socket = / usr/local/mysql3306/mysql.sock

Pid-file = / data/mysqldata/datasql-2.pid

Character-set-server = utf8

Lower_case_table_names=1

Skip_name_resolve = 1

Open_files_limit = 65535

Back_log = 1024

Max_connections = 2000

Max_connect_errors = 1000000

Table_definition_cache = 1024

Table_open_cache_instances = 64

Thread_stack = 512K

External-locking = FALSE

Max_allowed_packet = 32m

Sort_buffer_size = 4m

Join_buffer_size = 4m

Thread_cache_size = 3000

Query_cache_size = 0

Query_cache_type = 0

Interactive_timeout = 600,

Wait_timeout = 600,

Tmp_table_size = 32m

Max_heap_table_size = 32m

Slow_query_log = 1

Slow_query_log_file = / data/mysqldata/3306/log/slow.log

Log-error = / data/mysqldata/3306/log/mysqld.log

Long_query_time = 0.1

Log_queries_not_using_indexes = 1

Log_throttle_queries_not_using_indexes = 60

Min_examined_row_limit = 100

Log_slow_admin_statements = 1

Log_slow_slave_statements = 1

Server-id = 43 # Master-Slave copy plus secondary parameters, values can be IP Mantissa or other values

Log-bin = mysql-bin # Master-Slave replication plus secondary parameters

Sync_binlog = 1

Binlog_cache_size = 4m

Max_binlog_cache_size = 2G

Max_binlog_size = 1G

Expire_logs_days = 7

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

Gtid_mode = on

Enforce_gtid_consistency = 1

Log_slave_updates

Binlog_format = row

Relay_log_recovery = 1

Relay-log-purge = 1

Key_buffer_size = 32m

Read_buffer_size = 8m

Read_rnd_buffer_size = 4m

Bulk_insert_buffer_size = 64m

Lock_wait_timeout = 3600

Explicit_defaults_for_timestamp = 1

Innodb_thread_concurrency = 0

Innodb_sync_spin_loops = 100

Innodb_spin_wait_delay = 30

Log_bin_trust_function_creators = 1

Transaction_isolation = REPEATABLE-READ

Innodb_buffer_pool_size = 91750M # reference server memory, fill in 80% of memory

Innodb_buffer_pool_instances = 8

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 32m

Innodb_log_file_size = 2G

Innodb_log_files_in_group = 2

# adjust according to the IOPS capability of your server

# if you use an ordinary SSD disk, you can adjust it to 10000-20000

# for high-end PCIe SSD cards, adjust them higher, such as 50000-80000

Innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

Innodb_flush_neighbors = 0

Innodb_write_io_threads = 8

Innodb_read_io_threads = 8

Innodb_purge_threads = 4

Innodb_open_files = 65535

Innodb_max_dirty_pages_pct = 50

Innodb_flush_method = O_DIRECT

Innodb_lru_scan_depth = 4000

Innodb_checksum_algorithm = crc32

Innodb_lock_wait_timeout = 10

Innodb_rollback_on_timeout = 1

Innodb_print_all_deadlocks = 1

Innodb_file_per_table = 1

Innodb_online_alter_log_max_size = 4G

Innodb_stats_on_metadata = 0

Innodb_status_file = 1

# enabling innodb_status_output & innodb_status_output_locks may result in faster growth of log-error files

Innodb_status_output = 0

Innodb_status_output_locks = 0

# performance_schema

Performance_schema = 1

Performance_schema_instrument ='% = on'

# innodb monitor

Innodb_monitor_enable= "module_innodb"

Innodb_monitor_enable= "module_server"

Innodb_monitor_enable= "module_dml"

Innodb_monitor_enable= "module_ddl"

Innodb_monitor_enable= "module_trx"

Innodb_monitor_enable= "module_os"

Innodb_monitor_enable= "module_purge"

Innodb_monitor_enable= "module_log"

Innodb_monitor_enable= "module_lock"

Innodb_monitor_enable= "module_buffer"

Innodb_monitor_enable= "module_index"

Innodb_monitor_enable= "module_ibuf_system"

Innodb_monitor_enable= "module_buffer_page"

Innodb_monitor_enable= "module_adaptive_hash"

[mysqldump]

Quick

Max_allowed_packet = 32m

[mysqld_safe]

Log-error = / data/mysqldata/3306/log/mysqld.log

Sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Read the above on the source code installation mysql5.6.38 testing and tutorials, if there is anything else you need to know, you can find what you are interested in in the industry information or find our professional and technical engineers to answer, technical engineers have more than ten years of experience in the industry.

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.