一、准备工作

1. 解压

• 如果没有要求，建议MySQL解压至/usr/local/目录
• 创建软连接,方便后续版本迭代：ln -s mysql-XX mysql
• 创建文件夹：mkdir mysql/arch mysql/data mysql/tmp
  • arch：存放归档、二进制数据
  • data：存放表结构、业务数据
  • tmp：临时文件

2. 创建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
  
  #根据自身实际需要，自定义调整pool size 建议8G以内
  innodb_buffer_pool_size = 4G
  #innodb_additional_mem_pool_size = 50M #deprecated in 5.6
  tmpdir = /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 = 2M
  

3. 配置用户（可选）

3.1 创建用户组及用户

• 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)
• 通常不设置mysqladmin的密码，直接从root或者LDAP用户sudo切换
• 如果mysqladmin存在：
  • usermod -u 514 -g dba -G root -d /usr/local/mysql mysqladmin

3.2 配置个人环境变量

• 拷贝环境变量配置文件至mysqladmin用户的home目录：
  • cp /etc/skel/.* /usr/local/mysql
• 配置环境变量:
  • 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
    
    # 修改命令头样式
    PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1
    

3.3 赋权限和用户组

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

二、配置服务及开机自启动

• 将服务文件拷贝到init.d下，并重命名为mysql：
  • cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
• 赋予可执行权限：
  • chmod +x /etc/rc.d/init.d/mysql
• 删除服务：
  • chkconfig --del mysql
• 添加服务：
  • chkconfig --add mysql
  • chkconfig --level 345 mysql on
• 保证开机自启：
  • vi /etc/rc.local
  • su - mysqladmin -c "/etc/init.d/mysql start --federated"

三、安装libaio

• yum -y install libaio

四、初始化MySQL

• su - mysqladmin
• 初始化：

  • bin/mysqld \
    --defaults-file=/etc/my.cnf \
    --user=mysqladmin \
    --basedir=/usr/local/mysql/ \
    --datadir=/usr/local/mysql/data/ \
    --initialize
    

• 检查是否成功：
  • cat data/hostname.err （无报错）
• 说明：
  • 在初始化时如果加上 –initial-insecure，则会创建空密码的 root@localhost 账号，否则会创建带密码的 root@localhost 账号，密码直接写在 log-error 日志文件中
  • 在5.6版本中是放在 ~/.mysql_secret 文件里，更加隐蔽，不熟悉的话可能会无所适从
• 查看临时密码：
  • cat hostname.err |grep password
    • [Note] A temporary password is generated for root@localhost: XXXXXXXXXXX

五、启动MySQL

• mysqld服务：
  • mysqld_safe --defaults-file=/etc/my.cnf &
• 登录及修改用户密码：
  • mysql -uroot -p'XXXXXXXXXXX'
• 修改root密码：
  • alter user root@localhost identified by 'YYYYYY';
• 赋予权限：
  • GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YYYYYY';
• 刷新权限：
  • flush privileges;
• 重启服务：
  • service mysql restart

常见问题

• 问题1：

  • mysqld: File '/usr/local/mysql/arch/mysql-bin.index' not found (Errcode: 13 - Permission denied)
    2021-02-21T06:41:07.123746Z 0 [ERROR] Aborting
    

  • 解决：
    • chown mysql:mysql -R /usr/local/mysql/arch/
• 问题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
    

  • 解决：
    • chmod 777 /urs/local/mysql/tmp/
• 问题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 ?
    2021-02-21T06:47:56.995609Z 0 [ERROR] Aborting
    

  • 解决：修改3306端口或者kill相关进程