LNMP环境搭建MySQL篇_MySQL

1.Install MySQL

<code class="hljs ruby">root@kallen:~# apt-get install mysql-server-5.5 mysql-client-5.5</code>

<strong>2.Add Users</strong>

<strong>(1) 选择数据表</strong><br /> 语句如下：use mysql;<br /> <strong>(2) 在mysql的user表中增加连接用户帐号：</strong><br /> 这里不要直接使用INSERT语句添加user记录，使用INSERT可能出现错误：

ERROR 1364 (HY000): Field ‘ssl_cipher’ doesn’t have a default value

不过早期的MYSQL版本倒没出现这个错误，因为一直都是直接修改user表或直接使用INSERT语句完成，后来升级MYSQL到5.1的时候，发现可能会出现这个错误。<br /> 建议使用GRANT语句进行授权，语句如下：

<code class="hljs ruby"><code class="hljs sql">GRANT USAGE ON *.* TO &#39;username&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;password&#39; WITH GRANT OPTION;</code></code>

<code class="hljs sql">上句：

<code class="hljs sql">&ldquo;username&rdquo;替换为将要授权的用户名，比如clientusr；<br /> &ldquo;password&rdquo;替换为clientusr设置的密码；

<code class="hljs sql">(3) 可访问数据表授权

<code class="hljs sql">创建好帐户之后，就开始给上面的common user进行数据表授权，步骤3中增加的连接用户默认权限都是&ldquo;N&rdquo;的，必须在db表中为该帐户授权，允许其访问专用数据库，当然超级用户就不说了。

<code class="hljs sql">使用下面语句：

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql">GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON tablename.*  TO &#39;username&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;password&#39;;</code></code></code>

<code class="hljs sql"><code class="hljs sql">本语句中的权限根据实际需要确定：

<code class="hljs sql"><code class="hljs sql">&ldquo;tablename&rdquo;替换为授权访问的数据表table名<br /> &ldquo;username&rdquo; 是步骤2授权用户名<br /> &ldquo;password&rdquo; 是步骤2授权用户的设置密码

<code class="hljs sql"><code class="hljs sql">这样就为该用户授予了对某数据表的SELECT, INSERT, UPDATE, DELETE, CAREATE, DROP权限。

<code class="hljs sql"><code class="hljs sql">(4) 生效授权，创建完毕

<code class="hljs sql"><code class="hljs sql">一句话即可：FLUSH PRIVILEGES;

<code class="hljs sql"><code class="hljs sql"><strong>3.Install phpmyadmin</strong>

<code class="hljs sql"><code class="hljs sql">用随便一个支持PHP的web服务器（如Apache、Nginx、Lighttpd），下载phpmyadmin，装之。

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash">sudo apt-get install phpmyadmin   #注意这是安装到/usr/share/phpmyadmin</code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash">在ubuntu下，sudo apt-getinstall phpmyadmin后会有一些设置，如选择服务器、密码设定等等内容。安装完成后，访问http://localhost/phpmyadmin会出现404错误，这是因为没有将phpmyadmin目录映射到apache目录下面，运行下面命令即可：

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash">sudo ln -s /usr/share/phpmyadmin /var/www/html</code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><strong>[常见错误]</strong>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><strong>(1) 安装错误：</strong>

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http">Error: Package: php54w-tidy-5.4.41-1.w6.x86_64 (webtatic)

 Requires: libtidy-0.99.so.0()(64bit)

 You could try using --skip-broken to work around the problem

 You could try running: rpm -Va --nofiles --nodigest </code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http">需要安装libtidy-0.99.so：

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby">[root@amtt02 html]# rpm -ivh libtidy-0.99.0-19.20070615.1.el6.x86_64.rpm</code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><strong>(2) 访问错误：</strong><br /> 在http.conf文件中加入：

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><directory phpmyadmin="" share="" usr=""> 

AllowOverride None 

Order allow,deny 

Allow from all 

</directory> </code></code></code></code></code></code></code></code>

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs ">service httpd restart </code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs ">之后，就能成功启动httpd服务了，问题解决。

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs ">访问phpMyAdmin提示：

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs ">配置文件权限错误,无法写入!

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs ">解决办法：

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl">chmod -R 755 ./phpmyadmin</code></code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl">这样设置下phpMyAdmin目录权限属性为755就可以访问了。原来phpMyAdmin必须在755权限下才可以运行。

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl">注意：在777下也是错误的，估计是phpMyAdmim为安全着想。

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><strong>4.Can&rsquo;t connect to MySQL server on &lsquo;localhost&rsquo;(10038)</strong>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl">查一下你的MYSQL用户表里, 是否允许远程连接

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><strong>(1)给用户授权</strong>

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><code class="hljs lasso">mysql>grant all privileges on *.*to&#39;root&#39;@&#39;%&#39;  identified by &#39;youpassword&#39;  with grant option;</code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><code class="hljs lasso"><code class="hljs ">mysql>flush privileges;</code></code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><code class="hljs lasso"><code class="hljs "><strong>(2)修改/etc/mysql/my.conf</strong>

<code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><code class="hljs lasso"><code class="hljs ">找到bind-address =127.0.0.1这一行<br /> 改为bind-address =0.0.0.0 即可

<code class="hljs ruby"><code class="hljs sql"><code class="hljs sql"><code class="hljs bash"><code class="hljs bash"><code class="hljs http"><code class="hljs ruby"><code class="hljs apache"><code class="hljs "><code class="hljs perl"><code class="hljs lasso"><code class="hljs "><code class="hljs vhdl">root@kallen:/etc/selinux# vim /etc/mysql/
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
# Remote Access to MySQL on port 3306
# Kallen Ding, Jun 11 2015
bind-address = 0.0.0.0</code></code></code></code></code></code></code></code></code></code></code></code></code>