Production environment Linux binary installation configuration MySQL
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.
