mysql 8.0 installation

surroundings

  • CentOS 7.5
  • Docker 1.13.1
  • MySQL 8.0.16

installation

Pull mirror

Pull the latest version of the mirror by default

 

$ docker pull mysql

If you want to specify the version, use the following command

 

$ docker pull mysql is currently version 8.0.25

Create data directory and configuration files

Create a directory and data directory for placing mysql configuration files on the host, and authorize

 

$ mkdir -p /usr/mysql/conf /usr/mysql/data $ chmod -R 755 /usr/mysql/

Create a configuration file

Create the MySQL configuration file my.cnf under the configuration file directory /usr/mysql/conf created above

 

$ vim /usr/mysql/conf/my.cnf

Add the following to the configuration file created above

[cient] #socket = /usr/mysql/mysqld.sockdefault-character-set = utf8mb4  [mysqld] #pid-file        = /var/run/mysqld/mysqld.pid #socket          = /var/run/mysqld/mysqld.sock #datadir         = /var/lib/mysql #socket = /usr/mysql/mysqld.sock #pid-file = /usr/mysql/mysqld.pid datadir = /usr/mysql/data character_set_server = utf8mb4 default-character-set=utf8mb4 collation_server = utf8mb4_bin secure-file-priv= NULL lower_case_table_names=1default_authentication_plugin= mysql_native_password# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Custom config should go here !includedir /etc/mysql/conf.d/
  • /etc/localtime:/etc/localtime:ro is to synchronize the container's clock with the host's clock to avoid time zone problems. ro means read only, which means read-only.

Start to create a container

$ docker run --restart=unless-stopped -d --name mysql -v /mnt/my.cnf:/etc/mysql/my.cnf -v /usr/mysql/data:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
Parameter explanation:
-v: mount the host directory and the directory in the docker container, the front is the host directory, and the back is the internal directory of the container
-d: run the container in the background
-p mapping container port number and host port number
-e environment parameter, MYSQL_ROOT_PASSWORD sets the password of the root user

After executing the above command, execute the query container command to see the created mysql container

$ docker ps -a

The result is as follows:

problem

Although mysql is installed above, an error is prompted when using the remote Navicat connection, and mysql cannot be connected correctly. At this time, you need to modify the password mode and host of mysql according to the steps described below.

Modify mysql password and accessible host

  • Go inside the container

$ docker exec -it mysql /bin/bash

  • Connect to mysql

$ mysql -uroot -p

  • Use mysql library

$ mysql> use mysql

  • Modify the access host and password, etc., and set it to be accessible to all hosts

View encryption method

select host,user,plugin,authentication_string from mysql.user;

3. Modify the database

ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
      mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '新密码';

4. After changing the password, the connection can be successful
      mysql> alter user'root'@'%' identified by '123456';

note:
mysql_native_password, mysql8.x version must use this mode, otherwise navicate cannot connect correctly
  • Refresh
 

$ mysql> flush privileges

After the above steps, you can connect normally when you use Navicat to connect to the database remotely again.

Modify the configuration file:

vim /usr/lib/sysctl.d/00-system.conf

add to

net.ipv4.ip_forward=1

Then restart the network

[[email protected] mytomcat]# systemctl restart network

Other configuration

[mysqld] default_authentication_plugin=mysql_native_password //修改密码加密方式 log-bin-trust-function-creators=1 //防止function函数无法创建 binlog_expire_logs_seconds=604800 //7天日志会被清除,保证日志文件不会过大 innodb_file_per_table //每个表一个文件存储 innodb_flush_log_at_trx_commit = 2 //bin日志写入方式,尽量保证性能优先 lower_case_table_names=1 //表名等信息大小写不敏感 group_concat_max_len=1M //group_concat可以拼接的最大长度 log_output=table,file //将慢查询的SQL写入文件和表(mysql.slow.log) long_query_time = 2 //指定慢查询的阀值 port=3306 //端口号 basedir=D:/zhaoyi/abiz_aeps/etc/mysql //mysql安装目录(需根据实际情况修改) datadir=D:/zhaoyi/abiz_aeps/etc/mysql/data //data目录的路径(需根据实际情况修改) character_set_server=utf8mb4 //指定编码格式 default-storage-engine=INNODB //设置默认引擎 explicit_defaults_for_timestamp=true //解决时间戳不能为00-00 00:00:00的问题 connect_timeout=600 wait_timeout=600 max_connections=3000 //最大连接数 sql_mode= //sql的语法模式 ngram_token_size=1//ngram的最小分词 ft_min_word_len=1//mysql的最小索引长度 [mysql] default-character-set=utf8mb4 [client] default-character-set=utf8mb4

Quote:

https://blog.csdn.net/csdnxufei/article/details/88800559

https://bbs.huaweicloud.com/blogs/158656

https://www.drimwai.com/article.php?id=72