mysql的部署和使用
mysql是一种关系型数据库，说到关系型数据库，那肯定有非关系型数据库啊，那么什么是关系型数据库，什么是非关系型数据库呢，这个下次我们有时间在讲。今天我们主要来讲如何进行mysql的部署和使用，接下来我们废话少说，直入主题。
讲到部署，mysql有多种安装方法，接下来我来介绍一下几种。
1.yum方式安装
从CentOS 7.0发布以来，yum源中开始使用Mariadb来代替MySQL的安装(这里什么是mariadb,和mysql有什么区别，有兴趣的小伙伴可以自行查阅）。即使你输入的是yum install -y mysql , 显示的也是Mariadb的安装内容。使用源代码进行编译安装又太麻烦。因此，如果想使用yum安装MySQL的话，就需要去下载官方指定的yum源.1.配置yum源
由于这次我们采用的系统环境是centos7.6，所以我们需要去下载官方指定的yum源，接下来我们进行演示。
找到Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package，单击后面的Download，在新的页面中单击最下面的No thanks, just start my download.就可以下载到yum源了。 1.安装Mysql yum资源库 [root@centos7 ~]#wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm [root@centos7 ~]# yum localinstall mysql80-community-release-el7-3.noarch.rpm -y 2.检查mysql源是否安装成功 [root@centos7 ~]# yum repolist all |grep mysql 输出省略，从输出中我们看出mysql源种有多种版本，默认最高版本是开启的，其余版本处于禁用状态。
2.yum安装的方式如何指定版本
如需通过yum的方式安装指定版本的mysql,可以通过对yum配置文件进行修改。enabled=1则为安装的版本，将需要安装的版本enable改为1，其他改为0.
[root@centos7 ~]# vim /etc/yum.repos.d/mysql-community.repo 多余输出省略 # Enable to use MySQL 5.7 [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ enabled=1 #这里我模拟安装5.7版本的，所以这里改为了 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql80-community] name=MySQL 8.0 Community Server baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/ enabled=0 #这里默认是1，这里我改成0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
这里也可以通过命令切换需要安装的版本。
[root@centos7 ~]# yum-config-manager --enable mysql80-community #等同于enabled=1 [root@centos7 ~]# yum-config-manager --disable mysql57-community #等同于enabled=0 在检查上面的配置文件，会发现刚刚修改的又改回来了 这里有时候会报没有yum-config-manager这个命令 只需要提前安装下面的包 [root@centos7 ~]# yum -y install yum-utils
这个时候我们在把安装版本改回来，我们这次演示mysql5.7版本的。
3.安装并初始化
[root@centos7 ~]# yum install mysql-community-server -y
#重载systemctl守护进程 [root@centos7 ~]# systemctl daemon-reload
#启动mysql [root@centos7 ~]# systemctl start mysqld
#设为开机启动，这个按自己的需求来 [root@centos7 ~]# systemctl enable mysqld
#由于MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用，这个密码记录在/var/log/mysqld.log文件中，使用下面的命令可以查看此密码：[root@centos7 ~]# grep 'temporary password' /var/log/mysqld.log 2020-03-09T14:11:35.253149Z 1 [Note] A temporary password is generated for root@localhost: wo<(C>U8ogCg#最后一行冒号后面的部分wo<(C>U8ogCg就是初始密码。使用此密码登陆mysql[root@centos7 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.29 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.#有两种方法解决上面的报错（如下的123456是修改后的密码）：mysql> set password=password("123456");
或者 mysql> alter user 'root'@'localhost' identified by '123456';
#刷新权限 mysql> flush privileges;#如果上面在执行setpassword=password("123456");命令后出现下面的报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决办法：这个与Mysql 密码安全策略validate_password_policy的值有关，validate_password_policy可以取0、1、2三个值：0 or LOW Length1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary默认的数值是1，符合长度，且必须含有数字，小写或大写字母，特殊字符。所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。
有时候，只是为了自己测试，不想密码设置得那么复杂，譬如说，我只想设置root的密码为123456。 必须修改两个全局参数： mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
修改上面两个参数后，就可以解决这个报错了。
注意一点：mysql5.7之后的数据库里mysql.user表里已经没有password这个字段了，password字段改成了authentication_string。所以修改密码的命令如下：这里需要注意的是这里只是修改密码的方式变了，但是初始化新密码还是按上面的方式。
mysql> update mysql.user set authentication_string=password('123456789') where user='root';
Query OK, 1 row affected, 1 warning (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 1
4.版本的查看和字符编码的修改
查看版本： mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.29 | +-----------+ 1 row in set (0.02 sec) 查看默认编码: mysql> show variables like "%character%";show variables like "%collation%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.08 sec) +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)
修改编码操作：
[root@centos7 ~]# grep -v "^#" /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8 #这里做了修改
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client] #这里和下面一行是我们自己加的
default-character-set=utf8
[root@centos7 ~]# systemctl restart mysqld #重启mysql
查看字符编码是否修改：
mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci | #这里我们看出字符已修改成功
+----------------------+-----------------+
3 rows in set (0.01 sec)
2.rpm包的方式安装
yum的方式安装看着挺简单的，但是这个方式有局限性：1.不能指定安装到具体的小版本 2.受网络的影响较大
下面为了解决这边局限性问题，我们来介绍一种rpm包的方式。
1.卸载系统自带的mysql和mariadb-lib
[root@centos7 ~]# /bin/rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps #这里面xargs的作用是，给rpm -e传递参数的一个过滤器,讲通俗点就是Mysql的 包比较多，通过这个命令，就可以把包名以参数的方式，一个个传给xargs进行删除。--nodeps的作用是在卸载包的时候，可能会有很多其他依赖包，导致卸载失败，这个就是忽略依赖。 [root@centos7 ~]# /bin/rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps
2.下载mysql5.7.26 rpm安装包
下载地址：http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.7/
[root@centos7 ~]# wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar [root@centos7 ~]# tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar [root@centos7 ~]# ll total 1036912 -rw-------. 1 root root 6706 Sep 13 2017 anaconda-ks.cfg -rw-r--r--. 1 root root 0 Apr 9 2018 dd -rw-r--r-- 1 root root 530882560 Apr 15 2019 mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar -rw-r--r-- 1 7155 31415 25381952 Apr 15 2019 mysql-community-client-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 280904 Apr 15 2019 mysql-community-common-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 3838100 Apr 15 2019 mysql-community-devel-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 47076368 Apr 15 2019 mysql-community-embedded-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 24086952 Apr 15 2019 mysql-community-embedded-compat-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 130023844 Apr 15 2019 mysql-community-embedded-devel-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 2274268 Apr 15 2019 mysql-community-libs-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 2118444 Apr 15 2019 mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 173541272 Apr 15 2019 mysql-community-server-5.7.26-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 122249684 Apr 15 2019 mysql-community-test-5.7.26-1.el7.x86_64.rpm -rw-------. 1 root root 6556 Sep 13 2017 original-ks.cfg drwxr-xr-x 2 root root 18 Dec 12 10:57 test
3.安装步骤
依次执行（几个包有依赖关系，所以这里要注意执行有先后）下面命令安装 [root@centos7 ~]# rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm --force #这里--force参数的作用是强制安装 [root@centos7 ~]# rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm --force [root@centos7 ~]# rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm --force [root@centos7 ~]# rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm --force 这里需要注意的是，在安装mysql-community-server-5.7.26-1.el7.x86_64.rpm的时候可能会报下面的错误， warning: mysql-community-server-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY error: Failed dependencies: libaio.so.1()(64bit) is needed by mysql-community-server-5.7.21-1.el7.x86_64 libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.21-1.el7.x86_64 libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.21-1.el7.x86_64 net-tools is needed by mysql-community-server-5.7.21-1.el7.x86_64 这个报错的意思就是依赖有问题，安装libaio包和net-tools包就可以了： 安装libaio-0.3.107-10.el6.x86_64.rpm wget http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm --force 安装net-tools yum install net-tools
使用rpm安装方式安装mysql，安装的路径如下：
数据库目录：/var/lib/mysql/
配置文件：/usr/share/mysql(mysql.server命令及配置文件） /etc/my.cnf
相关命令：/usr/bin/(mysqladmin mysqldump等命令）
启动脚本：/etc/rc.d/init.d/(启动脚本文件mysql的目录）
4.数据库初始化
[root@centos7 ~]# mysql_install_db --datadir=/var/lib/mysql #必须指定datadir,执行后会生成~/.mysql_secret密码文件[root@centos7 ~]# mysqld --initialize --user=mysql #新版的推荐此方法，执行生会在/var/log/mysqld.log生成随机密码。如果是以mysql身份运行，则可以去掉--user选项。
另外--initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码，而使用--initialize-insecure命令则不使用安全模式，则不会为 root 用户生成一个密码。 5.更改mysql数据库目录的所属用户及其所属组，然后启动mysql数据库
[root@centos7 ~]# chown mysql:mysql /var/lib/mysql -R [root@centos7 ~]# systemctl start mysqld
6.查看初始化密码，和更改初始化密码
[root@centos7 ~]# grep "temporary password" /var/log/mysqld.log [root@centos7 ~]# mysql -uroot -p mysql> set password=password('liu123456'); mysql> flush privileges
3.编译方式安装：
1.卸载系统自带的 mysql和mariadb-lib
[root@centos7 ~]# /bin/rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps [root@centos7 ~]# /bin/rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps
2.安装编译代码需要的包
[root@centos7 ~]# yum install -y gcc gcc-c++ pcre pcre-devel openssl openssl-devel zlib zlib-devel cmake ncurses ncurses-devel bison bison-devel perl perl-devel autoconf
3.编译安装mysql5.7.28(在MySQL5.7中，编译安装方式跟5.6有些不同，需要boost源码参与编译安装)
[root@centos7 ~]#cd /usr/local/[root@centos7 local]# wget http://www.sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
[root@centos7 local]#tar -zxvf boost_1_59_0.tar.gz
[root@centos7 local]# mvboost_1_59_0.tar.gzboost
[root@centos7 local]#useradd-s/sbin/nologin-M mysql[root@centos7 local]# wget https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-5.7/mysql-5.7.26.tar.gz
[root@centos7 local]# tar -zxvf mysql-5.7.26.tar.gz [root@centos7 local]# cd mysql-5.7.26 [root@centos7 mysql-5.7.26]# [root@centos7 mysql-5.7.26]#
[root@centos7 mysql-5.7.26]#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.26 \#mysql安装目录
>-DMYSQL_DATADIR=/usr/local/mysql-5.7.26/data\#数据库文件目录
>-DWITH_BOOST=/usr/local/boost\>#boost库，不带boost的源码包只需要这样即可，带boost源码包只需后面跟boost
上面是生成预编译环境-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.7.26/tmp/mysql.sock \>-DEXTRA_CHARSETS=all\>-DDEFAULT_CHARSET=utf8mb4 \#默认字符集
>-DDEFAULT_COLLATION=utf8mb4_general_ci \#默认校对规则> -DWITH_EXTRA_CHARSETS=all \> -DWITH_MYISAM_STORAGE_ENGINE=1 \> -DWITH_INNOBASE_STORAGE_ENGINE=1 \> -DWITH_MEMORY_STORAGE_ENGINE=1 \> -DWITH_READLINE=1 \> -DWITH_INNODB_MEMCACHED=1 \> -DWITH_DEBUG=OFF \> -DWITH_ZLIB=bundled \> -DENABLED_LOCAL_INFILE=1 \> -DENABLED_PROFILING=ON \> -DMYSQL_MAINTAINER_MODE=OFF \> -DMYSQL_TCP_PORT=3306
[root@centos7 mysql-5.7.26]make && make install
4.配置和初始化
#创建软连接
[root@centos7 ~]# cd /usr/local
[root@centos7 local]#ln-s mysql-5.7.26 mysql[root@centos7 local]#
#添加到环境变量vim/etc/profileexportPATH=/usr/local/mysql/bin:$PATHexportPATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH[root@centos7 local]#source/etc/profile[root@centos7 local]#cd/usr/local/mysql-5.7.26[root@centos7mysql-5.7.26]#mkdir-p/usr/local/mysql-5.7.26/{data,tmp,logs,pids}[root@centos7mysql-5.7.26]chownmysql.mysql -R/usr/local/mysql-5.7.26/
#修改/etc/my.cnf文件，编辑配置文件如下
[mysqld]character-set-server=utf8mb4collation-server=utf8mb4_general_cidatadir=/usr/local/mysql/datasocket=/usr/local/mysql/tmp/mysql.sock[mysqld_safe]log-error=/usr/local/mysql/logs/mysqld.logpid-file=/usr/local/mysql/pids/mysqld.pid[client]default-character-set=utf8mb4# 创建mysqld.log 和 mysqld.pid文件touch /usr/local/mysql/logs/mysqld.logtouch /usr/local/mysql/pids/mysqld.pidchown mysql.mysql -R /usr/local/mysql/logs/chown mysql.mysql -R /usr/local/mysql/pids/# 加入守护进程cd /usr/local/mysqlcp support-files/mysql.server /etc/init.d/mysqldchmod a+x /etc/init.d/mysqldchkconfig --add mysqld #增加httpd服务
chkconfig mysqld on #设定mysqld在各等级为on，“各等级”包括2、3、4、5等级。
# 初始化数据库， –initialize 表示默认生成一个安全的密码，–initialize-insecure 表示不生成密码mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data# 启动MySQLservice mysqld start#登录MySQL，修改密码
mysql -u root -p #第一次登陆不需要密码，回车即可set password for root@localhost = password('root'); #修改密码4.二进制包的方式安装
1.安装依赖包
[root@test-no ~]# yum -y install libaio
2.卸载系统自带的mysql和mariadb
[root@test-no ~]# rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps [root@test-no ~]# rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps
3.下载安装mysql二进制包
[root@test-no ~]# useradd -s /sbin/nologin -M mysql [root@test-no ~]# mkdir /app/
[root@test-no ~]#cd /app/
[root@test-no ~]# wget https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz #这里需要注意的是，很多把mysql的二进制包以为是mysql-5.7.26-el7-x86_64.tar.gz ，实际不是，这里感兴趣的可以百度。
[root@test-no ~]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@test-no ~]# mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql5.7
[root@test-no ~]#mkdir-p/app/mysql5.7/{etc,logs,tmp}
4.初始化数据库
[root@test-no ~]# cd mysql5.7/
[root@centos7 mysql5.7]# chown -R mysql /app/mysql5.7/ [root@test-no mysql5.7]# bin/mysqld --initialize --basedir=/app/mysql5.7/ --datadir=/app/mysql5.7/data --user=mysql
#这里--basedir mysql基础目录,也就是安装目录 --datadir mysql数据存放目录，这里建议做成逻辑卷。需要注意是，--basedir后面填二进制压缩包解压之后的目录，不然可能会报下面的错误
[ERROR] Can't find error-message file '/app/mysql5.7/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.--defaults-file是可以指定配置文件目录的。
[root@centos7 mysql5.7]# vim /app/mysql5.7/etc/my.cnf #编辑配置文件
[mysqld]daemonize = onuser = mysqlport = 3306basedir = /app/mysql5.7datadir = /app/mysql5.7/datasocket = /tmp/mysql.sockbind-address = 0.0.0.0pid-file = /app/mysql5.7/tmp/mysqld.pidcharacter-set-server = utf8collation-server = utf8_general_cimax_connections = 2408log-error = /app/mysql5.7/logs/mysqld.log5.配置systemd启动MYSQL服务和修改密码
vim /lib/systemd/system/mysqld.service [Unit] Description=MySQL Server #服务的简单描述 Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html #服务文档 After=network.target #Before、After:定义启动顺序。Before=xxx.service,代表本服务在xxx.service启动之前启动。After=xxx.service,代表本服务在xxx.service之后启动。 After=syslog.target #这里可能会有疑问，服务不是.service结尾的吗？以target的结尾，这些target 服务可以视作一批服务的集合，里面预定义了哪些服务运行，哪些不运行 [Install] WantedBy=multi-user.target #被哪些units所依赖，弱依赖 [Service] #与特定类型相关的专用选项；此处为Service类型 User=mysql Group=mysql Type=forking #systemd认为当该服务进程fork，且父进程退出后服务启动成功。对于常规的守护进程（daemon），除非你确定此启动方式无法满足需求，使用此类型启动即可。使用此启动类型应同时指定 PIDFile=，以便systemd能够跟踪服务的主进程。 TimeoutSec=0 PermissionsStartOnly=true ExecStart=/app/mysql5.7/bin/mysqld --defaults-file=/app/mysql5.7/etc/my.cnf #指定启动单元的命令或者脚本，ExecStartPre和ExecStartPost节指定在ExecStart之前或者之后用户自定义执行的脚本。Type=oneshot允许指定多个希望顺序执行的用户自定义命令。 LimitNOFILE = 5000 Restart=on-failure #这个选项如果被允许，服务重启的时候进程会退出，会通过systemctl命令执行清除并重启的操作。 RestartPreventExitStartus=1 PrivateTmp=false #True表示给服务分配独立的临时空间 #启动服务 systemctl restart mysqld systemctl enable mysqld #配置环境变量，修改mysql密码 vim /etc/profile export PATH=$PATH:/app/mysql5.7/bin source /etc/profile mysql -uroot -p #这填的密码，就是上面初始化后生成的密码 alter user 'root'@'localhost' IDENTIFIED BY 'Devops@2018'; #这里修改用户root，密码为Devops@2018.
5.生产环境安装完之后建议马上做的操作
1.安全加固
--为root用户设置密码 --删除匿名账号 --取消root用户远程登录 --删除test库和对test库的访问权限 --刷新授权表使修改生效
[root@no mysql5.7]# mysql_secure_installation #执行这条命令进行安全基础加固 Securing the MySQL server deployment. Enter password for user root: #这里输入初始化的时候生成的密码 The existing password for the user account root has expired. Please set a new password. New password: #这里输入新的密码 Re-enter new password: VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? Press y|Y for Yes, any other key for No: y #这里问是否安装验证密码强度的插件，可以提高我们的密码强度 There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 #这里问我们要设置的的密码强度，0简单，1中度，2强密码 Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : n #问你是否改密码，因为上面我设了，这里就暂时不改了 ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #是否删除匿名用户，生产环境建议删除 Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y #是否禁止root远程登录，这里一般根据自己的需求来，建议禁止 Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #是否删除test数据库 - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y #是否重新加载权限表 Success. All done!
2.修改默认编码（修改字符集）
查看未修改之前默认编码
mysql> show variables like "%character%";show variables like "%collation%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | #从这里和下面的server我们看出默认的字符集是latin1,这个是不支持中文的 | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.01 sec)
调整操作：
[root@linux-no ~]# cat /etc/my.cnf |grep -v ^# |grep -v ^$ [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock character-set-server=utf8 #在mysqld下面添加这一行 symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] default-character-set=utf8 #在client下面添加这一行 [root@linux-no ~]# systemctl restart mysqld [root@linux-no ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.30 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> show variables like "%character%";show variables like "%collation%"; #验证 +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | #这里我们看出默认字符集已经更改 | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec)
