记录一次服务器搭建MySQL

技术坑:Linux centos7 mysql navicate docker

Centos7 系统安装 docker，并配置阿里云镜像加速器

1. 先在服务器上安装一个docker 根据官方文档

   ​ docker pull MySQL:8.0

2. whereis mysql 查看安装位置

3. 通过下面命令创建一个镜像

   1. ```dockerfile
      docker run -p 3306:3306 –name zxhmysql -v /mydata/mysql/log:/var/log/mysql -v /mydata/mysql/data:/var/lib64/mysql -v /mydata/mysql/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0

4. docker ps -a查看是否启动成功

   • 这里有个坑 ，网上 旧版lib后面没有64 新版要添加
   • 

5. 启动成功 改配置文件

   cd /mydata/mysql/conf
   vi my.cnf

6. 配置文件参考

   1. ```
      [mysqld]
      ####: for global
      user =mysql
      federated
      basedir =/usr/local/mysql
      datadir =/data/mysql/data
      server_id = 1250
      port =3306
      mysqlx_port = 33070
      character_set_server =utf8mb4
      collation_server=utf8mb4_bin
      log_timestamps =system
      default_time_zone =’+8:00’
      socket =/data/mysql/mysql.sock
      read_only = 0
      super_read_only = 0
      skip_name_resolve =on
      lower_case_table_names =1
      #secure_file_priv =/data/mysql/data
      open_files_limit =65536
      max_connections =2000
      thread_cache_size =256
      table_open_cache =4096 # 2000
      table_definition_cache =4096
      table_open_cache_instances =64
      wait_timeout =28800
      interactive_timeout =28800
      event_scheduler = 1
      max_allowed_packet =1024M
      init_connect =’SET AUTOCOMMIT=1’
      transaction_isolation =READ-COMMITTED
      lock_wait_timeout=3600
      default_authentication_plugin=mysql_native_password###: cache
      key_buffer_size = 64M
      sort_buffer_size = 4M
      join_buffer_size = 4M####: for binlog
      binlog_format =row
      log_bin =/data/mysql/logs/mysql-bin
      binlog_rows_query_log_events =off
      log_slave_updates =on
      #expire_logs_days =7
      binlog_expire_logs_seconds=604800
      binlog_cache_size =1G
      max_binlog_size =1G
      binlog_checksum =none
      sync_binlog =1
      slave-preserve-commit-order =ON
      log_bin_trust_function_creators=on ####:for error-log
      log_error =/data/mysql/logs/mysql.err
      general_log =off
      general_log_file =/data/mysql/logs/general.log####: for slow query log
      slow_query_log =on
      slow_query_log_file =/data/mysql/logs/slowquery.log
      long_query_time =1.000000
      log_queries_not_using_indexes =0####: for gtid
      #gtid_executed_compression_period =1000
      gtid_mode =on
      enforce_gtid_consistency =on####: for replication
      relay-log-index = /data/mysql/logs/relay-bin.index
      relay-log-info-file = /data/mysql/logs/relay-log.info
      relay-log = /data/mysql/logs/relay-bin
      log-slave-updates =1
      skip_slave_start =1
      master_info_repository =table
      relay_log_info_repository =table
      slave_parallel_type =logical_clock
      slave_parallel_workers =2####: for performance_schema
      performance_schema = 1
      performance_schema_instrument = ‘%memory%=on’
      performance_schema_instrument = ‘%lock%=on’
      performance_schema_digests_size =100000####: for innodb
      innodb_data_file_path =ibdata1:1024M:autoextend
      innodb_temp_data_file_path =ibtmp1:12M:autoextend
      innodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_pool
      innodb_log_files_in_group =3
      innodb_log_file_size =256M # 50331648(48M)
      innodb_max_undo_log_size =256M
      innodb_undo_log_truncate =1
      innodb_undo_tablespaces =2
      innodb_file_per_table =on
      innodb_open_files =65535
      innodb_thread_concurrency =0 # 0
      innodb_read_io_threads =2
      innodb_write_io_threads =2
      innodb_purge_threads =1
      innodb_page_cleaners =1
      innodb_print_all_deadlocks =off
      innodb_lock_wait_timeout =20
      innodb_autoinc_lock_mode =2
      innodb_io_capacity =500
      innodb_io_capacity_max =1000#########################
      innodb_flush_method =O_DIRECT
      innodb_log_buffer_size =128M
      innodb_flush_log_at_trx_commit =1
      innodb_buffer_pool_size = 128M
      innodb_buffer_pool_instances = 2
      #innodb_numa_interleave=on
      #####################################
      #######################
      [client]
      socket=/data/mysql/mysql.sock

      7. docker restart 容器ID
      
         测试连接 
      
      #### MySQL允许远程连接
      
      项目中需要连接虚拟机上面的 MySQL 数据库，但是总是出错，怀疑本机是否有连接远程数据库的权限。
      
      执行命令：
      
      ```sql
      mysql> use mysql;
      mysql> select host,user from user;

查看结果是不是 root 用户仅允许本地（localhost）登录，下面这个截图就是这种情况：

mysql-root-state

是的话，就要修改它的 host 为 %，表示任意 IP 地址都可以登录。

mysql> update user set host = '%' where user = 'root';

执行完后可能提示 error。再 mysql> select host,user from user; 查看下吧。

root 对应的 host 成了 %，表示可以任意 IP 地址登录了。

mysql-change-root-state

mysql> flush privileges;

把缓存 flush 掉，在使用 update 语句修改用户记录后，需要 FLUSH 语句告诉服务器重载授权表。

记录一些遇到的bug

1. 1130 - Host ‘118.114.228.223’ is not allowed to connect to this MySQL server
2. MySQL 报错：ERROR 1396 (HY000): Operation ALTER USER failed for root@localhost
3. MySQL 登录时出现 Access denied for user ‘root‘@‘localhost‘ (using password: YES) 无法打开的解决方法
4. ❗docker 下 mysql 的密码设置
5. [ERROR] [MY-010095] [Server] Failed to access directory for –secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
6. docker 实战之挂载方式部署 MySQL8 （公司实战）

Others：