First, preparation

1. Unpack the

  • If not, decompress MySQL to/usr/local/directory
  • Create soft connections for subsequent iterations:ln -s mysql-XX mysql
  • Create folder:mkdir mysql/arch mysql/data mysql/tmp
    • Arch: Stores archived and binary data
    • Data: stores table structure and service data
    • TMP: temporary file

2. Create my. CNF

  • vi /etc/my.cnf
  • [client] port = 3306 socket = /usr/local/mysql/data/mysql.sock default-character-set=utf8mb4 [mysqld] port = 3306 socket  = /usr/local/mysql/data/mysql.sock skip-slave-start skip-external-locking key_buffer_size = 256M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M query_cache_size= 32M max_allowed_packet = 16M myisam_sort_buffer_size=128M tmp_table_size=32M table_open_cache = 512 thread_cache_size = 8 wait_timeout = 86400 interactive_timeout = 86400 max_connections = 600 # Try number of CPU's*2 for thread_concurrency #thread_concurrency = 32 #isolation level and default engine default-storage-engine = INNODB transaction-isolation = READ-COMMITTED server-id = 1739 basedir = /usr/local/mysql datadir = /usr/local/mysql/data pid-file = /usr/local/mysql/data/hostname.pid #open performance schema log-warnings sysdate-is-now binlog_format = ROW log_bin_trust_function_creators=1 log-error = /usr/local/mysql/data/hostname.err log-bin = /usr/local/mysql/arch/mysql-bin expire_logs_days = 7 innodb_write_io_threads=16 relay-log = /usr/local/mysql/relay_log/relay-log relay-log-index = /usr/local/mysql/relay_log/relay-log.index relay_log_info_file= /usr/local/mysql/relay_log/relay-log.info log_slave_updates=1 gtid_mode=OFF enforce_gtid_consistency=OFF # slave slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=4 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON #other logs #general_log =1 #general_log_file = /usr/local/mysql/data/general_log.err #slow_query_log=1 #slow_query_log_file=/usr/local/mysql/data/slow_log.err #for replication slave sync_binlog = 500 #for innodb options innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1G; ibdata2:1G:autoextend innodb_log_group_home_dir = /usr/local/mysql/arch innodb_log_files_in_group = 4 Innodb_log_file_size = 1G Innodb_log_buffer_size = 200M Innodb_buffer_pool_size = 4G #innodb_additional_mem_pool_size = 50M #deprecated in 5.6tmpdir = /usr/local/mysql/tmp innodb_lock_wait_timeout = 1000 #innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 2 innodb_locks_unsafe_for_binlog=1 #innodb io features: Add for mysql5.5.8 Performance_schema Innodb_read_io_threads =4 Innodb-write-io -threads=4 Innodb-io -capacity=200 #purge threads change default(0) to 1 for purge innodb_purge_threads=1 innodb_use_native_aio=on #case-sensitive file names and separate tablespace innodb_file_per_table = 1 lower_case_table_names=1 [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash default-character-set=utf8mb4 [mysqlhotcopy] interactive-timeout [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2MCopy the code

3. (Optional) Configure users.

3.1 Creating User Groups and users

  • groupadd -g 101 dba
  • useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
  • id mysqladmin
    • uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)
  • Normally, you do not set mysqladmin password and switch directly from root or LDAP user sudo
  • If mysqladmin exists:
    • usermod -u 514 -g dba -G root -d /usr/local/mysql mysqladmin

3.2 Configuring Personal Environment Variables

  • Copy the environment variable configuration file to mysqladmin’s home directory:
    • cp /etc/skel/.* /usr/local/mysql
  • Configure environment variables:
    • vi mysql/.bashrc
    • export MYSQL_BASE=/usr/local/mysql export PATH=${MYSQL_BASE}/bin:$PATH unset USERNAME #stty erase ^H set umask to 022 Umask 022 # modify command head style PS1 = ` uname -n ` ":" '$USER' ":" '$PWD' ": >"; export PS1Copy the code

3.3 Assigning Rights and User Groups

  • chown mysqladmin:dba /etc/my.cnf
  • chmod 640 /etc/my.cnf
  • chown -R mysqladmin:dba /usr/local/mysql
  • chmod -R 755 /usr/local/mysql

Two, the configuration of services and startup

  • Copy the service file to init.d and rename it mysql:
    • cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
  • Grant executable permissions:
    • chmod +x /etc/rc.d/init.d/mysql
  • Delete service:
    • chkconfig --del mysql
  • Adding a service:
    • chkconfig --add mysql
    • chkconfig --level 345 mysql on
  • Ensure that the machine starts automatically:
    • vi /etc/rc.local
    • su - mysqladmin -c "/etc/init.d/mysql start --federated"

Three, install libaio

  • yum -y install libaio

MySQL > initialize MySQL

  • su - mysqladmin
  • Initialization: 
    • bin/mysqld \
--defaults-file=/etc/my.cnf \
--user=mysqladmin \
--basedir=/usr/local/mysql/ \
--datadir=/usr/local/mysql/data/ \
--initialize
Copy the code
  • Check whether:
    • cat data/hostname.err(No error)
  • Description:
    • If -initial-insecure is added during initialization, an root@localhost account with an empty password will be created; otherwise, an root@localhost account with a password will be created. The password will be directly written in the log-error log file
    • In version 5.6, it is placed in the ~/.mysql_secret file, which is more hidden and can be confusing if not familiar with it
  • To view temporary passwords:
    • cat hostname.err |grep password
      • [Note] A temporary password is generated for root@localhost: XXXXXXXXXXX

MySQL > start MySQL

  • Mysqld services:
    • mysqld_safe --defaults-file=/etc/my.cnf &
  • Login and password change:
    • mysql -uroot -p'XXXXXXXXXXX'
  • Change the root password:
    • alter user root@localhost identified by 'YYYYYY';
  • Grant permissions:
    • GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YYYYYY';
  • Refresh permission:
    • flush privileges;
  • Restart the service:
    • service mysql restart

Q&A

  • Question 1: 
    • mysqld: File '/usr/local/mysql/arch/mysql-bin.index' not found (Errcode: 2021-02-21t06:41:07.123746z 0 [ERROR] AbortingCopy the code
    • Solution:
      • chown mysql:mysql -R /usr/local/mysql/arch/
  • Question 2: 
    • 2021-02-21T06:44:03.371967Z 0 [ERROR] InnoDB: Unable to create temporary file; errno: 13
2021-02-21T06:44:03.371982Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-02-21T06:44:03.371990Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-02-21T06:44:03.371994Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-02-21T06:44:03.371997Z 0 [ERROR] Failed to initialize plugins.
2021-02-21T06:44:03.372000Z 0 [ERROR] Aborting
Copy the code
    • Solution:
      • chmod 777 /urs/local/mysql/tmp/
  • Question 3: 
    • [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 2021-02-21T06:47:56.995602z 0 [ERROR] Do you already have another mysqld server running on port: 3306? The 2021-02-21 T06:47:56. 995609 z 0 [ERROR] AbortingCopy the code
    • Solution: Modify port 3306 or kill related processes