cnblogs/dcrenl/Centos安装mysql.html

<p>卸载mysql的命令：</p>
<div class="cnblogs_code">
<pre>yum remove mysql-community-server</pre>
</div>
<p>&nbsp;</p>
<p>安装前的清理工作：</p>
<p>使用 rpm -qa | grep mysql命令查看，如果有查询结果，则使用yum remove&nbsp;名称清理掉。</p>
<div class="cnblogs_code">
<pre>yum remove mysql-xxx-xxx-</pre>
</div>
<p>删除mysql的配置文件，卸载不会自动删除配置文件，首先使用如下命令查找出所用的配置文件；</p>
<div class="cnblogs_code">
<pre>find / -name mysql</pre>
</div>
<p>centos的yum 源中默认是没有mysql,执行命令下载YUM源rpm安装包：</p>
<div class="cnblogs_code">
<pre>wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm</pre>
</div>
<p>安装 yum repo文件；</p>
<div class="cnblogs_code">
<pre>rpm -ivh mysql80-community-release-el7-11.noarch.rpm

或

yum localinstall mysql80-community-release-el7-1.noarch.rpm</pre>
</div>
<p>会在/etc/yum.repos.d/目录下生成两个repo文件mysql-community.repo&nbsp;mysql-community-source.repo</p>
<p>&nbsp;更新yum源：</p>
<div class="cnblogs_code">
<pre>yum clean all
yum makecache</pre>
</div>
<p>&nbsp;</p>
<p>&nbsp;查看mysql yum仓库中mysql版本，使用如下命令：</p>
<div class="cnblogs_code">
<pre>yum repolist all | grep mysql</pre>
</div>
<p>&nbsp;</p>
<p>可以看到 MySQL 5.5 5.6 5.7为禁用状态 而MySQL 8.0为启用状态；</p>
<p>如果您不想安装mysql8.0的版本我们可以按下面操作去做，要是安装的mysql8.0版本这步可以略过。</p>
<p>使用<span class="command">&nbsp;yum-config-manager 命令修改相应的版本为启用状态最新版本为禁用状态：</span></p>
<div class="cnblogs_code">
<pre>yum-config-manager --disable mysql80-community
 
yum-config-manager --enable mysql57-community</pre>
</div>
<p>&nbsp;或者可以编辑 mysql repo文件，将相应版本下的enabled改成 1&nbsp;即可：</p>
<div class="cnblogs_code">
<pre>cat /etc/yum.repos.d/mysql-community.repo </pre>
</div>
<p>&nbsp;</p>
<p>&nbsp;安装mysql 命令：</p>
<div class="cnblogs_code">
<pre>yum install mysql-community-server</pre>
</div>
<p>&nbsp;</p>
<p>&nbsp;开启mysql服务：</p>
<div class="cnblogs_code">
<pre>systemctl start mysqld.service</pre>
</div>
<p>&nbsp;</p>
<p>设置开机自启：</p>
<div class="cnblogs_code">
<pre>systemctl enable mysqld.service

-- 如果开机没有启动的话就使用下面这个命令吧，反正我是没使用
systemctl daemon-reload</pre>
</div>
<p>数据库的服务操作：</p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>#启动mysql
systemctl start mysqld.service
 
#结束
systemctl stop mysqld.service
 
#重启
systemctl restart mysqld.service
 
#开机自启
systemctl enable mysqld.service</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p>&nbsp;</p>
<p>获取初始密码登录mysql</p>
<p>mysql在安装后会创建一个root@locahost账户，并且把初始的密码放到了/var/log/mysqld.log文件中；</p>
<p>&nbsp;</p>
<div class="cnblogs_code">
<pre>cat /var/log/mysqld.log | grep password</pre>
</div>
<p>&nbsp;&nbsp;</p>
<p>使用初始密码登录mysql</p>
<div class="cnblogs_code">
<pre>mysql -uroot -p</pre>
</div>
<p>&nbsp;</p>
<p>mysql8.0版本必须符合长度（默认是8位），且必须含有数字，小写或大写字母，特殊字符。因为实在我本地自己的库，所以我修改了密码最小长度和密码的策略</p>
<h3 class="postTitle">mysql 8.0以上 新版本账号密码策略修改和密码修改&nbsp;</h3>
<div class="postBody">
<div id="cnblogs_post_body" class="blogpost-body">
<p>&nbsp;&nbsp;&nbsp;&nbsp; 老版本的密码策略变量：</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; validate_password_policy 这个参数用于控制validate_password的验证策略 0--&gt;low &nbsp;1--&gt;MEDIUM &nbsp;2--&gt;strong。</p>
<p>　　validate_password_length密码长度的最小值(这个值最小要是4)。</p>
<p>　　validate_password_number_count 密码中数字的最小个数。</p>
<p>　　validate_password_mixed_case_count大小写的最小个数。</p>
<p>　　validate_password_special_char_count 特殊字符的最小个数。</p>
<p>　　validate_password_dictionary_file 字典文件</p>
<p>&nbsp;</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8.0后要注意密码策略变量变化&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.check_user_name ON&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.dictionary_file&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.length&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.mixed_case_count&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.number_count&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.policy&nbsp;</p>
<p class="hljs-ln-line">　　　　validate_password.special_char_count&nbsp;</p>
<p>&nbsp; 如:validate_password_policy 变为validate_password.policy.</p>
<p>&nbsp; 所以</p>
<p>&nbsp; set global validate_password_policy=0;变为 set global validate_password.policy=0 ;</p>
<p>&nbsp; 密码最小长度（4）：</p>
<p>&nbsp; set global validate_password_length=6; 变为 set global validate_password.length=4</p>
<p>&nbsp;</p>
<hr />
<p>&nbsp;</p>
<p>&nbsp;修改root的密码：</p>
<div class="cnblogs_code">
<pre>ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';</pre>
</div>
<p>&nbsp;</p>
<p>远程设置：</p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>#远程设置
mysql&gt; use mysql;
mysql&gt; update user set host='%' where user='root';
#授权用户名的权限，赋予任何主机访问数据的权限<br />#本人没有执行这步
mysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;<br />
mysql&gt; FLUSH PRIVILEGES;</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p>&nbsp;</p>
<p>开放3306端口：</p>
<div class="cnblogs_code">
<pre>-- 查看已经开放的端口
firewall-cmd --list-ports  

-- 开启端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent  </pre>
</div>
<p>命令含义：&nbsp;</p>
<p>　　&ndash;zone #作用域&nbsp;</p>
<p>　　&ndash;add-port=80/tcp #添加端口，格式为：端口/通讯协议&nbsp;</p>
<p>　　&ndash;permanent #永久生效，没有此参数重启后失效</p>
<p>&nbsp;重启防火墙：</p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>#重启firewall  
firewall-cmd --reload  
#停止firewall  
systemctl stop firewalld.service  
#禁止firewall开机启动  
systemctl disable firewalld.service</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p>&nbsp;</p>
<p>&nbsp;端口开放后就可以进行数据库连接操作了，在使用Navicat连接 Mysql 8.0.15可能会出现问题 Client does not support authentication protocol 错误解决方法：</p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>#修改加密规则 
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
#更新一下用户的密码 
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; 
#刷新权限
FLUSH PRIVILEGES;</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p>&nbsp;</p>
<p>mysql的其他操作：</p>
<p>　　查看数据库编码：</p>
<div class="cnblogs_code">
<pre>show variables like 'character%';<br /><br />或<br /><br /><span class="hljs-keyword">SHOW <span class="hljs-keyword">VARIABLES <span class="hljs-keyword">WHERE Variable_name <span class="hljs-keyword">LIKE <span class="hljs-string">'character_set_%' <span class="hljs-keyword">OR Variable_name <span class="hljs-keyword">LIKE <span class="hljs-string">'collation%'</span></span></span></span></span></span></span></span></pre>
</div>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>查看单个数据库的编码设置</p>
<div class="cnblogs_code">
<pre>#方法一：
select * from information_schema.schemata where schema_name="test_character";

#方法二：
show crate database test_character;</pre>
</div>
<p>&nbsp;</p>
<p>查看单个表的编码设置</p>
<div class="cnblogs_code">
<pre>show crate table test_character.test;</pre>
</div>
<p>&nbsp;</p>
<p>如果编码不统一，很容易出现中文乱码，下面介绍在多个层面上修改编码设置</p>
<p>创建数据库时直接指定编码</p>
<div class="cnblogs_code">
<pre>create database &lt;数据库名&gt; character set utf8;</pre>
</div>
<p>&nbsp;</p>
<p>修改数据库的编码</p>
<div class="cnblogs_code">
<pre>alter database &lt;数据库名&gt; character set utf8;</pre>
</div>
<p>&nbsp;</p>
<p>创建表时指定编码</p>
<div class="cnblogs_code">
<pre>create table &lt;表名&gt; （
id int(10),
name varchar(20)
）default charset=utf8;</pre>
</div>
<p>&nbsp;</p>
<p>修改表的编码</p>
<div class="cnblogs_code">
<pre>alter table &lt;表名&gt; character set utf8;</pre>
</div>
<p>&nbsp;</p>
<p>数据库中下面的几个变量必须是uft8或是utf8mb4：</p>
<div class="cnblogs_code">
<pre>character_set_client&nbsp; (客户端来源数据使用的字符集)
character_set_connection&nbsp;&nbsp;&nbsp;&nbsp; (连接层字符集)
character_set_database&nbsp;&nbsp; (当前选中数据库的默认字符集)
character_set_results (查询结果字符集)
character_set_server (默认的内部操作字符集)</pre>
</div>
<p>&nbsp;</p>
<p>数据库连接参数中:</p>
<p>characterEncoding=utf8会被自动识别为utf8mb4，也可以不加这个参数，会自动检测。</p>
<p>而autoReconnect=true是必须加上的。</p>
<h2>部分参数配置查询命令：</h2>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>#查询mysql最大连接数设置

show global variables like 'max_conn%';

SELECT @@MAX_CONNECTIONS AS 'Max Connections';

&nbsp;

# 查看最大链接数

show global status like 'Max_used_connections';

&nbsp;

# 查看慢查询日志是否开启以及日志位置

show variables like 'slow_query%';

&nbsp;

# 查看慢查询日志超时记录时间

show variables like 'long_query_time';

&nbsp;

# 查看链接创建以及现在正在链接数

show status like 'Threads%';

&nbsp;

# 查看数据库当前链接

show processlist;

&nbsp;

# 查看数据库配置

show variables like '%quer%';</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p>&nbsp;</p>
<p>查看mysql版本：</p>
<div class="cnblogs_code">
<pre>select version();</pre>
</div>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;转自：https://www.cnblogs.com/yichenscc/articles/10663844.html</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</div>
</div>