$ cat /etc/my.cnf.d/client.cnf # ## These two groups are read by the client library ## Use it for options that affect all clients, but not the server #
## This group is not read by mysql client library, ## If you use the same .cnf file for MySQL and MariaDB, ## use it for MariaDB-only client options [client-mariadb]
bash
1 2 3 4 5 6 7 8 9 10 11
$ cat /etc/my.cnf.d/mysql-clients.cnf # ## These groups are read by MariaDB command-line tools ## Use it for options that affect only one utility #
$ cat /etc/my.cnf.d/mariadb-server.cnf # ## These groups are read by MariaDB server. ## Use it for options that only the server (but not clients) should see # ## See the examples of server my.cnf files in /usr/share/mysql/ #
## this is read by the standalone daemon and embedded servers [server]
## this is only for the mysqld standalone daemon ## Settings user and group are ignored when systemd is used. ## If you need to run mysqld under a different user or group, ## customize your systemd unit file for mysqld/mariadb according to the ## instructions in http://fedoraproject.org/wiki/Systemd [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid ## 新增以下字符集的配置 collation-server = utf8_general_ci init-connect = 'SET NAMES utf8' character-set-server = utf8 sql-mode = TRADITIONAL
3. 测试
3.1 启动 / 关闭
plaintext
1
systemctl start mariadb
看看状态
plaintext
1
systemctl status mariadb
显示如下，那就是正常运行中的
plaintext
1 2 3 4 5 6 7 8 9 10 11 12 13 14
● mariadb.service - MariaDB 10.3 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) Active: active (running) since Tue 2023-04-04 14:10:00 CST; 2 days ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 633453 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS) Process: 633289 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS) Process: 633265 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS) Main PID: 633422 (mysqld) Status: "Taking your SQL requests now..." Tasks: 31 (limit: 75691) Memory: 67.4M CGroup: /system.slice/mariadb.service └─633422 /usr/libexec/mysqld --basedir=/usr
$ systemctl enable mariadb Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
取消开机自启，将 enable 改成 disable 就可以了
plaintext
1
systemctl disable mariadb
3.2 进入 mysql 命令行
因为我们没有设置 root 用户的密码，所以不需要指定密码就能进入数据库
plaintext
1
mysql -uroot
会显示出如下信息，包括 mariadb 的版本
plaintext
1 2 3 4 5 6 7 8 9
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
3.3 看看字符编码是否修改成功
输入如下命令 (记得要输入末尾的分号)
plaintext
1
show variables like "%chara%";
看看是否都配置为了 utf-8，如下图，除了 filesystem 是二进制，其他都是 utf8，那就是 ok 的；
编码集修改之前的是这样的，有一部分的编码是 latin1。
修改后，除了 filesystem 外其他编码集都成功修改为了 utf8
到这里，就 ok 菈！
3.4 端口
mysql 服务的默认端口为 3306，::: 代表它是采用 ipv6 协议在本地进行访问的。但这不影响我们的使用；
在 Ubuntu 系统上该命令执行结果如下
bash
1 2 3 4
❯ netstat -nltp | grep 3306 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
可能有人会建议你修改 mysql 的默认端口，以避免被攻击。个人认为：这完全没有必要！一台主机的可用端口也就 65535 个，真要攻击你，写个脚本遍历完毕你主机上的所有端口都用不了 1 秒种的，很容易就能识别出哪一个端口的服务是什么（不同服务协议都有不同的握手手段，挨个去试就能知道）。
## ls -al /etc/yum.repos.d/ total 100 drwxr-xr-x 1 root root 4096 Apr 7 16:12 . drwxr-xr-x 1 root root 4096 Apr 6 19:14 .. -rw-r--r-- 1 root root 2590 Feb 24 08:08 CentOS-Base.repo -rw-r--r-- 1 root root 966 Feb 24 08:00 CentOS-Linux-AppStream.repo -rw-r--r-- 1 root root 710 Feb 24 07:56 CentOS-Linux-BaseOS.repo -rw-r--r-- 1 root root 1136 Feb 24 07:56 CentOS-Linux-ContinuousRelease.repo -rw-r--r-- 1 root root 318 Feb 24 07:56 CentOS-Linux-Debuginfo.repo -rw-r--r-- 1 root root 738 Feb 24 07:56 CentOS-Linux-Devel.repo -rw-r--r-- 1 root root 710 Feb 24 07:56 CentOS-Linux-Extras.repo -rw-r--r-- 1 root root 725 Feb 24 07:56 CentOS-Linux-FastTrack.repo -rw-r--r-- 1 root root 746 Feb 24 07:56 CentOS-Linux-HighAvailability.repo -rw-r--r-- 1 root root 693 Feb 24 07:56 CentOS-Linux-Media.repo -rw-r--r-- 1 root root 712 Feb 24 07:56 CentOS-Linux-Plus.repo -rw-r--r-- 1 root root 730 Feb 24 07:56 CentOS-Linux-PowerTools.repo -rw-r--r-- 1 root root 1124 Feb 24 07:56 CentOS-Linux-Sources.repo -rw-r--r-- 1 root root 0 Feb 24 08:05 epel-8.repo -rw-r--r-- 1 root root 1698 Oct 4 2022 epel-modular.repo -rw-r--r-- 1 root root 1332 Oct 4 2022 epel.repo -rw-r--r-- 1 root root 1417 Jun 8 2021 epel.repo.rpmnew -rw-r--r-- 1 root root 2318 Feb 24 08:08 epel.repo.rpmsave -rw-r--r-- 1 root root 1797 Oct 4 2022 epel-testing-modular.repo -rw-r--r-- 1 root root 1431 Oct 4 2022 epel-testing.repo -rw-r--r-- 1 root root 1265 Jan 10 2022 mysql-community.repo -rw-r--r-- 1 root root 1321 Jan 10 2022 mysql-community-source.repo -rw-r--r-- 1 root root 164 Feb 27 12:21 vscode.repo
这时候，yum 源就已经安装成功了
plaintext
1
yum list | grep mysql
执行后，可以看到，列在最前面的就是 el8-3，即为刚刚我们添加的的 yum 源中的 mysql 版本
plaintext
1 2 3 4 5 6
Repository AppStream is listed more than once in the configuration Repository extras is listed more than once in the configuration mysql80-community-release.noarch el8-3 @System anope-mysql.x86_64 2.0.12-1.el8 epel ansible-collection-community-mysql.noarch 3.5.1-1.el8 epel apr-util-mysql.x86_64 1.6.1-6.el8 AppStream
## systemctl start mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe"for details.
查看报错信息也看不出个所以然，看上去 MySQL5.7 版本在自动升级为 MySQL8？
bash
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
## systemctl status mysqld.service ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Fri 2023-04-07 16:39:51 CST; 7s ago Process: 648073 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS) Process: 648059 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE) Process: 648023 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS) Process: 647999 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS) Main PID: 648059 (code=exited, status=1/FAILURE) Status: "Data Dictionary upgrade from MySQL 5.7 in progress"
Apr 07 16:39:50 1c2261732150 systemd[1]: Starting MySQL 8.0 database server... Apr 07 16:39:51 1c2261732150 systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE Apr 07 16:39:51 1c2261732150 systemd[1]: mysqld.service: Failed with result 'exit-code'. Apr 07 16:39:51 1c2261732150 systemd[1]: Failed to start MySQL 8.0 database server.
❯ sudo docker exec -it mysql57 mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement.
mysql>
6.3 修改字符集和终端配置
问题说明
docker 安装的 MySQL 默认字符集都是 latin1，而且命令行中文输入都不允许！我们需要将 MySQL 的字符集修改为 utf8，同时还需要修改 MySQL 所在 docker 环境内终端的字符集，否则中文输入不被允许。
bash-4.2# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> insert into stu values (4,'小明'); ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column 'name' at row 1
但是这里弹了一个 ERROR，说我们的 string 变量不正确，不支持插入。
这是因为这个 test 数据库和 stu 表都是在修改 MySQL 字符集之前创建的，它们的字符集还没有被修改，依旧是 latin1 字符集！
plaintext
1 2 3 4 5 6 7
mysql> SHOW VARIABLES LIKE 'character_set_database'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | character_set_database | latin1 | +------------------------+--------+ 1 row in set (0.00 sec)
使用如下命令设置这个数据库 test 和表 stu 的字符集为 uft8；
sql
1 2
ALTER DATABASE 数据库名字 CHARACTERSET utf8mb4; ALTERTABLE 表名字 CONVERTTOCHARACTERSET utf8mb4;
效果如下
plaintext
1 2 3 4 5 6
mysql> ALTER DATABASE test CHARACTER SET utf8mb4; Query OK, 1 row affected (0.00 sec)
mysql> ALTER TABLE stu CONVERT TO CHARACTER SET utf8mb4; Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0
此时就能正常插入中文了
plaintext
1 2 3 4 5 6 7 8 9 10 11 12 13
mysql> insert into stu values (4,'小明'); Query OK, 1 row affected (0.03 sec)
mysql> select * from stu; +------+--------+ | id | name | +------+--------+ | 1 | 1235 | | 2 | 125 | | 3 | | | 4 | 小明 | +------+--------+ 4 rows in set (0.00 sec)
The end
本文讲述了安装 mariadb 和 docker 安装 MySQL 数据库以及字符集修改的方式，希望对你有帮助。
爱发电
