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

Production environment Linux binary installation configuration MySQL

I. Environmental inspection

Rpm-qa | grep libaio # if libaio is not installed, it needs to be installed.

Df-h

Mount / dev/sr0 / mnt

II. Installation process

1) upload the installer

Upload the installation package through FTP/SFTP

2) create users, groups, and directories

Groupadd mysql

Useradd-r-g mysql-s / bin/false mysql

Mkdir-p / mysql/data/3306/data

3306 here is the default port number of MySQL, the / mysql/data directory where mysql is installed

/ mysql/data/3306/data is the mysql data directory.

Mkdir-p / mysql/log/3306

3) extract the installation package

Cd / mysql/app

Tar zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

Ln-s / mysql/app/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz / mysql/app/mysql

4) modify permissions

Chown-R mysql:mysql / mysql

5) configure environment variables

Vi / .bash_profile

PATH=$PATH:/mysql/app/mysql/bin:$HOME/bin

test

[root@db1] # mysql-- version

Uninstall the mysql that comes with the system

Yum remove mysql

[root@db1 ~] # which mysql

/ mysql/app/mysql/bin/mysql

6) prepare the parameter file

Mysql reads the / etc/my.cnf configuration file for this path by default.

Cd / mysql/data/3306/

Touch / mysql/log/3306/itpuxdb-error.err

Chown-R mysql:mysql / mysql/log/3306/itpuxdb-error.err

Vi / mysql/data/3306//my.cnf

[client]

Port=3306

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

[mysql]

No-beep

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

# no-auto-rehash

Auto-rehash

Default-character-set=utf8

[mysqld]

# basic settings#

Server-id=3306

Port=3306

User = mysql

Bind_address= 192.168.1.51

Basedir=/mysql/app/mysql

Datadir=/mysql/data/3306/data

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

Pid-file=/mysql/data/3306/mysql.pid

Character-set-server=utf8

Skip-character-set-client-handshake=1

Autocommit = 0

# skip_name_resolve = 1

Max_connections = 800,

Max_connect_errors = 1000

Default-storage-engine=INNODB

Transaction_isolation = READ-COMMITTED

Explicit_defaults_for_timestamp = 1

Sort_buffer_size = 32m

Join_buffer_size = 128m

Tmp_table_size = 72m

Max_allowed_packet = 16m

Sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

Interactive_timeout = 1800

Wait_timeout = 1800

Read_buffer_size = 16m

Read_rnd_buffer_size = 32m

Query_cache_type = 1

Query_cache_size=1M

Table_open_cache=2000

Thread_cache_size=768

Myisam_max_sort_file_size=10G

Myisam_sort_buffer_size=135M

Key_buffer_size=32M

Read_buffer_size=8M

Read_rnd_buffer_size=4M

Back_log=1024

# flush_time=0

Open_files_limit=65536

Table_definition_cache=1400

# binlog_row_event_max_size=8K

# sync_master_info=10000

# sync_relay_log=10000

# sync_relay_log_info=10000

# log settings#

Log-output=FILE

General_log = 0

General_log_file=/mysql/log/3306/db1.err

Slow_query_log = ON

Slow_query_log_file=/mysql/log/3306/db1.err

Long_query_time=10

Log-error=/mysql/log/3306/db1-error.err

Log_queries_not_using_indexes = 1

Log_slow_admin_statements = 1

Log_slow_slave_statements = 1

Log_throttle_queries_not_using_indexes = 10

Expire_logs_days = 90

Min_examined_row_limit = 100

# log_bin = "/ log/bin_log/binlog"

# replication settings#

# master_info_repository = TABLE

# relay_log_info_repository = TABLE

# log_bin = bin.log

# sync_binlog = 1

# gtid_mode = on

# enforce_gtid_consistency = 1

# log_slave_updates

# binlog_format = row

# relay_log = relay.log

# relay_log_recovery = 1

# binlog_gtid_simple_recovery = 1

# slave_skip_errors = ddl_exist_errors

# innodb settings#

Innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

Innodb_buffer_pool_size = 500m

Innodb_buffer_pool_instances = 8

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_lru_scan_depth = 2000

Innodb_lock_wait_timeout = 5

# innodb_flush_method = O_DIRECT

Innodb_log_file_size = 200m

Innodb_log_files_in_group = 2

Innodb_log_buffer_size = 16m

Innodb_undo_logs = 128,

Innodb_undo_tablespaces = 3

Innodb_undo_log_truncate = 1

Innodb_max_undo_log_size = 2G

Innodb_flush_neighbors = 1

Innodb_purge_threads = 4

Innodb_large_prefix = 1

Innodb_thread_concurrency = 64

Innodb_print_all_deadlocks = 1

Innodb_strict_mode = 1

Innodb_sort_buffer_size = 64m

Innodb_flush_log_at_trx_commit=1

Innodb_autoextend_increment=64

Innodb_concurrency_tickets=5000

Innodb_old_blocks_time=1000

Innodb_open_files=65536

Innodb_stats_on_metadata=0

Innodb_file_per_table=1

Innodb_checksum_algorithm=0

Innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G

Innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

Innodb_buffer_pool_dump_pct = 40

Innodb_page_cleaners = 4

Innodb_purge_rseg_truncate_frequency = 128,

Binlog_gtid_simple_recovery=1

Log_timestamps=system

# transaction_write_set_extraction=MURMUR32

Show_compatibility_56=on

7) initialize MySQL

Mysqld-defaults-file=/mysql/data/3306/my.cnf-initialize-datadir=/mysql/data/3306/data-basedir=/mysql/app/mysql-user=mysql

Cd / mysql/log/3306

More db1-error.err

Initialized random password

IfyicOu0au+y

8) configure startup script

Cd / mysql/app/mysql/support-files

Cp mysql.server mysql

Vi mysql

The first thing needs to be changed:

# modify all previous basedir,datadir,bindir,sbindir

Basedir=/mysql/app/mysql

Datadir=/mysql/data/3306/data

Bindir=/mysql/app/mysql/bin

Sbindir=/mysql/app/mysql/bin

Libexecdir=/mysql/app/mysql/bin

The second point needs to be changed:

Mysqld_pid_file_path=/mysql/data/3306/mysql.pid

The third area needs to be changed:

# start the start entry of the script and add-- defaults-file=/mysql/data/3306/my.cnf

Bindir/mysqld_safe-- defaults-file=/mysql/data/3306/my.cnf-- datadir= "$datadir"-- pid-file= "$mysqld_pid_file_path" $other_args > / dev/null &

Check order of my.cnf

[root@itpux support-files] # mysql-- help | grep my.cnf

Order of preference, my.cnf, $MYSQL_TCP_PORT

/ etc/my.cnf / etc/mysql/my.cnf / usr/local/mysql/etc/my.cnf ~ / .my.cnf

# rename / etc/my.cnf

Finally, put the modified mysql script into the / etc/init.d/ directory.

Mv / mysql/app/mysql/support-files/mysql / etc/init.d/

9) start and stop verification, log view

Start

/ etc/init.d/mysql start or service mysql start

Stop it

/ etc/init.d/mysql stop or service mysql stop

10) Post-configuration (change password, start remote access)

Log in

Mysql-uroot-pxjl61l/J_tp9-S / mysql/data/3306/mysql.sock

Modify the password

Set password = password ("rootroot")

11) remote connection testing

Root users log in remotely

Grant all privileges on. To 'root'@'%' identified by' rootroot' with grant option

Flush privileges

Create a database

Create database db1

Create a user

Create user dbuser identified by "dbuser"

Mysql > grant all privileges on dbuser. To 'dbuser'@'localhost' identified by' dbuser'

Mysql > grant all privileges on. To 'dbuser'@'%' identified by' dbuser' with grant option

Mysql > grant all privileges on dbuser. To 'dbuser'@'%' identified by' dbuser'

Mysql > flush privileges

Create tables and add test data

Use db1

Create table dept (

Deptno int auto_increment primary key

Dname varchar (15)

Loc varchar (50)

) engine=innodb

Insert into dept values (1 recordation, recording, recording, gz')

Insert into dept values (2) CWP (2)

Insert into dept values (3 recordings hrsquares, pencils sz')

Commit

Select * from dept

Desc dept

Question 1:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ var/lib/mysql/mysql.sock' (2)

Resolve the problem:

The above error indicates that MySQL is installed and the existing installation package needs to be uninstalled.

Rpm-qa | grep mysql

Yum remove mysql

Just delete the mysql,mysql-devel package.

Question 2:

Mysqld: [ERROR] Could not open required defaults file: / mysql/data/3306/my.cnf

Mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

The solution is as follows:

The my.cnf file was not found. Please check that the path of the initialization command is the same as the path where my.cnf is stored.

Question 3: [root@itpuxdb 3306] # / etc/init.d/mysql start

Starting MySQL..The server quit without updating PID file ([FAILED] ata/3306/mysql.pid)

Such a problem can cause the pid,sock file not to be generated.

Solution:

Rename the / etc/my.cnf file to my.cnf.bak so that the corresponding file can be generated in the / mysql/data/3306/ directory.

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.