linux安装mysql 及 用户创建 授权操作文档

sudo apt-get install mysql-server

sudo mysql_secure_installation

密码难度等级
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
进制远程登录？
Disallow root login remotely?
删除测试数据库并访问它？
Remove test database and access to it?
现在重新加载特权表？
Reload privilege tables now?

systemctl status mysql.service

sudo mysql -uroot -p

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456";

# 下载
shell> wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# 安装 mysql 源
shell> yum localinstall mysql57-community-release-el7-11.noarch.rpm

yum repolist enabled | grep "mysql.*-community.*"

yum install -y mysql-community-server

systemctl start mysqld
systemctl enable mysqld

systemctl status mysqld

systemctl daemon-reload

rpm -qa |grep -i mysql

yum remove mysql-community-common-5.7.40-1.el7.x86_64
yum remove mysql-community-libs-5.7.40-1.el7.x86_64
yum remove mysql-community-server-5.7.40-1.el7.x86_64
yum remove mysql57-community-release-el7-11.noarch
yum remove mysql-community-client-5.7.40-1.el7.x86_64
yum remove mysql-community-libs-compat-5.7.40-1.el7.x86_64

docker run -d \
-p 3306:3306 \
-v /usr/local/docker/mysql/hive/conf:/etc/mysql/conf.d \
-v /usr/local/docker/mysql/hive/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root123456 \
--name mysql_master mysql:5.7

grep 'temporary password' /var/log/mysqld.log

alter user 'root'@'localhost' IDENTIFIED BY 'root'; 

或者

set password for 'root'@'localhost'=password('root'); 

设置密码有效程度 4 为及以上
set global validate_password_length=4;
设置密码策略最低级别
set global validate_password_policy=0;

mysql -uroot -p

show databases;

show global variables like 'port';

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.01 sec)

CREATE DATABASE csdb;

create user cs identified by 'cslogin';

select user, host from mysql.user;

drop user cs@'%';

grant all privileges on csdb.* to cs@'%' identified by 'cslogin';
flush privileges;

show grants for 'cs';

select host,user from mysql.user;

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

# 查看路由是否打开，1表示打开、0表示未打开
sysctl net.ipv4.ip_forward

# 开启路由
echo "net.ipv4.ip_forward = 1" >>/etc/sysctl.conf
#加载内核
sysctl -p

<p><font face="楷体,华文行楷,隶书,黑体" color="red" size="4"><strong>哈喽，大家好，我是有勇气的牛排（全网同名）🐮</strong></font></p> <p><font face="楷体,华文行楷,隶书,黑体" color="blue" size="4"><strong>有问题的小伙伴欢迎在文末评论，点赞、收藏是对我最大的支持！！！。</strong></font></p> <h2><a id="1__4"></a>1 安装</h2> <h3><a id="11_ubuntu_5"></a>1.1 ubuntu安装</h3> <p>安装 MySql</p> <pre><div class="hljs"><code class="lang-shell">sudo apt-get install mysql-server </code></div></pre> <p>初始化配置</p> <pre><div class="hljs"><code class="lang-shell">sudo mysql_secure_installation </code></div></pre> <p>学习用途统一设置密码为：mysql</p> <pre><div class="hljs"><code class="lang-shell">密码难度等级 Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0 进制远程登录？ Disallow root login remotely? 删除测试数据库并访问它？ Remove test database and access to it? 现在重新加载特权表？ Reload privilege tables now? </code></div></pre> <p>查看服务的状态信息</p> <pre><div class="hljs"><code class="lang-shell">systemctl status mysql.service </code></div></pre> <p><img src="https://static.couragesteak.com/article/949affbf08040f83b4225dd75320d929.png" alt="image.png" /></p> <p>配置远程访问</p> <p>根用户权限进入</p> <pre><div class="hljs"><code class="lang-shell">sudo mysql -uroot -p </code></div></pre> <p><img src="https://static.couragesteak.com/article/c155730650ae823aa191947b341b8720.png" alt="image.png" /></p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> PRIVILEGES <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> root<span class="hljs-variable">@localhost</span> IDENTIFIED <span class="hljs-keyword">BY</span> &quot;123456&quot;; </code></div></pre> <p><code>localhost</code>:表示仅本地访问，改为<code>%</code>就是所有主机可以链接。<br /> <code>123456</code>:为新增权限用户设置的密码，<code>%</code>表示所有主机</p> <h3><a id="12_CentoOS_51"></a>1.2 CentoOS安装</h3> <p>在 https://dev.mysql.com/downloads/repo/yum/ 找到 yum 源 rpm 安装包</p> <p>安装 MySQL源</p> <pre><div class="hljs"><code class="lang-shell"><span class="hljs-meta"># </span><span class="language-bash">下载</span> <span class="hljs-meta">shell&gt; </span><span class="language-bash">wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm</span> <span class="hljs-meta"># </span><span class="language-bash">安装 mysql 源</span> <span class="hljs-meta">shell&gt; </span><span class="language-bash">yum localinstall mysql57-community-release-el7-11.noarch.rpm</span> </code></div></pre> <p>用下面的命令检查 mysql 源是否安装成功</p> <pre><div class="hljs"><code class="lang-shell">yum repolist enabled | grep &quot;mysql.*-community.*&quot; </code></div></pre> <h4><a id="121__67"></a>1.2.1 开始安装</h4> <pre><div class="hljs"><code class="lang-shell">yum install -y mysql-community-server </code></div></pre> <p>启动MySQL服务器和MySQL的自动启动</p> <pre><div class="hljs"><code class="lang-shell">systemctl start mysqld systemctl enable mysqld </code></div></pre> <p>查看安装状态</p> <pre><div class="hljs"><code class="lang-shell">systemctl status mysqld </code></div></pre> <p>重载修改过配置的文件</p> <pre><div class="hljs"><code class="lang-shell">systemctl daemon-reload </code></div></pre> <h4><a id="122__89"></a>1.2.2 卸载</h4> <p>查看安装了哪些</p> <pre><div class="hljs"><code class="lang-shell">rpm -qa |grep -i mysql </code></div></pre> <pre><div class="hljs"><code class="lang-shell">yum remove mysql-community-common-5.7.40-1.el7.x86_64 yum remove mysql-community-libs-5.7.40-1.el7.x86_64 yum remove mysql-community-server-5.7.40-1.el7.x86_64 yum remove mysql57-community-release-el7-11.noarch yum remove mysql-community-client-5.7.40-1.el7.x86_64 yum remove mysql-community-libs-compat-5.7.40-1.el7.x86_64 </code></div></pre> <h3><a id="13_docker_105"></a>1.3 docker安装</h3> <pre><div class="hljs"><code class="lang-c">docker run -d \ -p <span class="hljs-number">3306</span>:<span class="hljs-number">3306</span> \ -v /usr/local/docker/mysql/hive/conf:/etc/mysql/conf.d \ -v /usr/local/docker/mysql/hive/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root123456 \ --name mysql_master mysql:<span class="hljs-number">5.7</span> </code></div></pre> <h2><a id="2___117"></a>2 首次安装 修改密码</h2> <p>查询临时密码</p> <pre><div class="hljs"><code class="lang-shell">grep &#x27;temporary password&#x27; /var/log/mysqld.log </code></div></pre> <p>修改密码</p> <pre><div class="hljs"><code class="lang-shell">alter user &#x27;root&#x27;@&#x27;localhost&#x27; IDENTIFIED BY &#x27;root&#x27;; 或者 set password for &#x27;root&#x27;@&#x27;localhost&#x27;=password(&#x27;root&#x27;); </code></div></pre> <p>配置允许简单密码</p> <pre><div class="hljs"><code class="lang-shell">设置密码有效程度 4 为及以上 set global validate_password_length=4; 设置密码策略最低级别 set global validate_password_policy=0; </code></div></pre> <h2><a id="3__141"></a>3 查看数据库服务</h2> <h3><a id="31__142"></a>3.1 登录</h3> <p>登录</p> <pre><div class="hljs"><code class="lang-shell">mysql -uroot -p </code></div></pre> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">show</span> databases; </code></div></pre> <h3><a id="32__152"></a>3.2 查看服务端口</h3> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">show</span> <span class="hljs-keyword">global</span> variables <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;port&#x27;</span>; </code></div></pre> <pre><div class="hljs"><code class="lang-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">show</span> <span class="hljs-keyword">global</span> variables <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;port&#x27;</span>; <span class="hljs-operator">+</span><span class="hljs-comment">---------------+-------+</span> <span class="hljs-operator">|</span> Variable_name <span class="hljs-operator">|</span> <span class="hljs-keyword">Value</span> <span class="hljs-operator">|</span> <span class="hljs-operator">+</span><span class="hljs-comment">---------------+-------+</span> <span class="hljs-operator">|</span> port <span class="hljs-operator">|</span> <span class="hljs-number">3306</span> <span class="hljs-operator">|</span> <span class="hljs-operator">+</span><span class="hljs-comment">---------------+-------+</span> <span class="hljs-number">1</span> <span class="hljs-type">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec) </code></div></pre> <h2><a id="4_____168"></a>4 新建 数据库 和 用户</h2> <p>此操作需要root用户来进行</p> <p><code>创建数据库</code>：csdb</p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">CREATE</span> DATABASE csdb; </code></div></pre> <h3><a id="41__175"></a>4.1 用户操作</h3> <p><strong>1、创建用户</strong></p> <p>创建用户：cs<br /> 密码：cslogin</p> <pre><div class="hljs"><code class="lang-shell">create user cs identified by &#x27;cslogin&#x27;; </code></div></pre> <p><strong>2、修改密码</strong></p> <pre><div class="hljs"><code class="lang-shell"> </code></div></pre> <p>3、查看用户</p> <pre><div class="hljs"><code class="lang-shell">select user, host from mysql.user; </code></div></pre> <p>4、删除用户</p> <pre><div class="hljs"><code class="lang-shell">drop user cs@&#x27;%&#x27;; </code></div></pre> <h3><a id="42__201"></a>4.2 授权操作</h3> <p>将<code>csdb数据库</code>的<strong>所有操作权限</strong>都授权给<code>用户cs</code>，密码：cslogin</p> <pre><div class="hljs"><code class="lang-shell">grant all privileges on csdb.* to cs@&#x27;%&#x27; identified by &#x27;cslogin&#x27;; flush privileges; </code></div></pre> <h3><a id="43_cs_209"></a>4.3 查看用户cs，权限授予执行的命令</h3> <pre><div class="hljs"><code class="lang-shell">show grants for &#x27;cs&#x27;; </code></div></pre> <p>查看mysql连接的授权信息</p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">select</span> host,<span class="hljs-keyword">user</span> <span class="hljs-keyword">from</span> mysql.user; </code></div></pre> <pre><div class="hljs"><code class="lang-shell"><span class="hljs-meta">mysql&gt; </span><span class="language-bash">select host,user from mysql.user;</span> +-----------+------------------+ | host | user | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 4 rows in set (0.00 sec) </code></div></pre> <h2><a id="5__232"></a>5 报错</h2> <h3><a id="51_Failing_package_is_mysqlcommunityclient57381el7x86_64_GPG_Keys_are_configured_as_file_233"></a>5.1 Failing package is: mysql-community-client-5.7.38-1.el7.x86_64 GPG Keys are configured as: file://</h3> <p>原因：MySQL GPG 密钥已过期导致</p> <p>解决办法：执行一下命令，解决</p> <pre><div class="hljs"><code class="lang-shell">rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 </code></div></pre> <h3><a id="52_navicat_245"></a>5.2 navicat无法连接、防火墙已关闭，仍无法连接</h3> <pre><div class="hljs"><code class="lang-c"># 查看路由是否打开，<span class="hljs-number">1</span>表示打开、<span class="hljs-number">0</span>表示未打开 sysctl net.ipv4.ip_forward # 开启路由 echo <span class="hljs-string">&quot;net.ipv4.ip_forward = 1&quot;</span> &gt;&gt;/etc/sysctl.conf #加载内核 sysctl -p </code></div></pre>