Mysql8安装与使用

1. 准备centos 虚拟机一台

2. 下载mysql8: wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

3. 解压缩.xz包：xz -d mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

4. 解压缩.gz包：tar xf mysql-8.0.15-linux-glibc2.12-x86_64.tar

5. 移动到: mv mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysql

6. 建立mysql用户：adduser mysql

7. 复制以下内容到 /etc/my.cnf 文件中

   [client]
   port            = 3306
   socket          = /usr/local/mysql/data/mysql.sock
   [mysqld]
   # Skip #
   skip_name_resolve              = 1
   skip_external_locking          = 1 
   skip_symbolic_links     = 1
   # GENERAL #
   user = mysql
   default_storage_engine = InnoDB
   character-set-server = utf8
   socket  = /usr/local/mysql/data/mysql.sock
   pid_file = /usr/local/mysql/data/mysqld.pid
   basedir = /usr/local/mysql
   port = 3306
   bind-address = 0.0.0.0
   explicit_defaults_for_timestamp = off
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   #read_only=on
   # MyISAM #
   key_buffer_size                = 32M
   #myisam_recover                 = FORCE,BACKUP
   
   # undo log #
   innodb_undo_directory = /usr/local/mysql/undo
   innodb_undo_tablespaces = 8
   
   # SAFETY #
   max_allowed_packet             = 100M
   max_connect_errors             = 1000000
   sysdate_is_now                 = 1
   #innodb = FORCE
   #innodb_strict_mode = 1
   secure-file-priv='/tmp'
   default_authentication_plugin='mysql_native_password'
   # Replice #
    server-id = 1001
    relay_log = mysqld-relay-bin
    gtid_mode = on
    enforce-gtid-consistency
    log-slave-updates = on
    master_info_repository =TABLE
    relay_log_info_repository =TABLE
   
   
   # DATA STORAGE #
    datadir = /usr/local/mysql/data/
    tmpdir = /tmp
    
   # BINARY LOGGING #
    log_bin = /usr/local/mysql/sql_log/mysql-bin
    max_binlog_size = 1000M
    binlog_format = row
    binlog_expire_logs_seconds=86400
   # sync_binlog = 1
   
    # CACHES AND LIMITS #
    tmp_table_size                 = 32M
    max_heap_table_size            = 32M
    max_connections                = 4000
    thread_cache_size              = 2048
    open_files_limit               = 65535
    table_definition_cache         = 4096
    table_open_cache               = 4096
    sort_buffer_size               = 2M
    read_buffer_size               = 2M
    read_rnd_buffer_size           = 2M
   # thread_concurrency             = 24
    join_buffer_size = 1M
   # table_cache = 32768
    thread_stack = 512k
    max_length_for_sort_data = 16k
   
   
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_flush_log_at_trx_commit = 2
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 256M
    #innodb_buffer_pool_instances = 8
    innodb_stats_on_metadata = off
    innodb_open_files = 8192
    innodb_read_io_threads = 16
    innodb_write_io_threads = 16
    innodb_io_capacity = 20000
    innodb_thread_concurrency = 0
    innodb_lock_wait_timeout = 60
    innodb_old_blocks_time=1000
    innodb_use_native_aio = 1
    innodb_purge_threads=1
    innodb_change_buffering=all
    innodb_log_file_size = 64M
    innodb_log_files_in_group = 2
    innodb_data_file_path  = ibdata1:256M:autoextend
    
    innodb_rollback_on_timeout=on
    # LOGGING #
    log_error                      = /usr/local/mysql/sql_log/mysql-error.log
    # log_queries_not_using_indexes  = 1
    # slow_query_log                 = 1
     slow_query_log_file            = /usr/local/mysql/sql_log/slowlog.log
   
    # TimeOut #
    #interactive_timeout = 30
    #wait_timeout        = 30
    #net_read_timeout = 60
   
   [mysqldump]
   quick
   max_allowed_packet = 100M
   
   [mysql]
   no-auto-rehash
   # Remove the next comment character if you are not familiar with SQL
   #safe-updates
   
   [myisamchk]
   key_buffer_size = 256M
   sort_buffer_size = 256M
   read_buffer = 2M
   write_buffer = 2M
   
   [mysqlhotcopy]
   interactive-timeout%
   

8. cd /usr/local/mysql 创建 mkdir data sql_log undo 修改权限 chown mysql:mysql -R data/ sql_log/ undo/

9. vi /etc/profile 末尾添加mysql启动路径 export PATH=$PATH:/usr/local/mysql/bin 然后 source /etc/profile

10. yum install -y libaio 以防止初始化报错：

    mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
    

11. 初始化mysql： mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

12. /usr/local/mysql/support-files 拷贝MySQL 启动脚本 cp mysql.server /etc/init.d/mysqld

13. 启动MySQL服务：/etc/init.d/mysqld start

14. 获取初始化密码 grep password /usr/local/mysql/sql_log/mysql-error.log

    2019-12-10T00:50:48.479337Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: NywiaLssD1_Z
    

15. 登录MySQL修改密码：mysql -uroot -pNywiaLssD1_Z 使用一下语句可以修改当前用户的密码：

    alter user user() identified by "123456"
    

mysql 登录

mysql -uroot -p -hlocalhost

mysql -uroot -p -hlocalhost -e "select user()"