分类 mysql 下的文章

centos6.6搭建LANP环境(yum)

nginx
自带的yum源的nginx版本比较低,到nginx官网下载版本对应的源http://nginx.org/en/linux_packages.html#stable

然后安装

rpm -ivf nginx-release-centos-6-0.el6.ngx.noarch.rpm

再安装nginx

yum install nginx

安装成功,启动nginx

service nginx start 

mysql
去mysql的官网下载源,http://dev.mysql.com/downloads/repo/yum/
这个页面有安装步骤,http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
安装下载的源

rpm -ivf mysql-community-release-el6-5.noarch.rpm

安装mysql

yum install mysql-community-server

启动mysql

service mysqld start

会报错,启动不成功

初始化 MySQL 数据库: 2015-06-08T13:15:59.011125Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-06-08T13:15:59.013681Z 0 [ERROR] Can't change data directory owner to mysql
2015-06-08T13:15:59.013693Z 0 [ERROR] Aborting

关闭下selinux

setenforce 0

再启动下,然后进行运行mysql的命令进行一些初始化工作(具体设置内容可以看centos7安装LANP)
运行这个命令需要的密码可以用这个命令看到

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

php
安装php的源,比较新

yum install epel-release
rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

安装php(这里安装了一些php的扩展,有些不知道是干嘛的~~~可以根据需求去掉,但是php-fpm和php-mysqlnd最好别去掉,一个是之后和nginx整合需要用到的,一个连接mysql的)

yum install --enablerepo=remi --enablerepo=remi-php56 php php-fpm php-opcache php-devel php-mbstring php-mcrypt php-mysqlnd php-phpunit-PHPUnit php-pecl-xdebug php-pecl-xhprof

php和nginx
先启动php-fpm

service php-fpm start

修改nginx配置文件(配置在这里点击我)
然后就ok了

php和mysql
先打开selinux的一个选项

setsebool -P httpd_can_network_connect=1

再测试下,应该就ok了~~~~

设置下服务在开机的时候自启动

chkconfig --level 235 php-fpm on
chkconfig --level 235 mysqld on
chkconfig --level 235 nginx on

这样设置之后因为防火墙的关系,只有本机可以访问,查看防火墙的设定防火墙设定

centos7安装搭建LNMP

nginx

    rpm -Uvh http://nginx.org/packages/centos/7/noarch/RPMS/nginx-release-centos-7-0.el7.ngx.noarch.rpm
   yum install nginx
   systemctl start nginx
   yum install links
   links 127.0.0.1
   links 192.168.208.6
   firewall-cmd --get-active-zones 
   firewall-cmd --zone=public --list-services 
   firewall-cmd --permanent --zone=public --add-service=http
   firewall-cmd --reload

安装安装成功,可以测试,出现nginx欢迎页面就ok

mysql

   rpm -ivf mysql-community-release-el7-5.noarch.rpm 
   yum search yum-utils
   yum install yum-utils
   yum-config-manager --disable mysql56-community
   yum-config-manager --enable mysql57-community-dmr
   yum search mysql-server
   yum repolist | grep 'mysql'
   yum repolist enabled | grep 'mysql'
   yum search mysql-community-server
   yum install mysql-community-server
   [root@localhost log]# systemctl start mysqld.service 
   [root@localhost log]# grep 'temporary password' /var/log/mysqld.log
2015-06-08T01:44:44.713032Z 1 [Warning] A temporary password is generated for root@localhost: ln3hb?;Jkk6w

    [root@localhost log]# mysql_secure_installation 

    Securing the MySQL server deployment.

    Enter password for root user: 这里填grep 'temporary password' /var/log/mysqld.log命令显示出的密码

    The existing password for the user account 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
    Using existing root password.

    Estimated strength of the password: 100 
    Change the root password? (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.
    删除anonymous用户,
    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.
    是都禁止root远程连接
    Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

     ... skipping.
    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.

    删除test数据库
    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
     - 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!

php

yum install epel-release
   rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
   yum install --enablerepo=remi --enablerepo=remi-php56 php php-fpm php-opcache php-devel php-mbstring php-mcrypt php-mysqlnd php-phpunit-PHPUnit php-pecl-xdebug php-pecl-xhprof

整合nginx和php
修改vi /etc/php.ini
cgi.fix_pathinfo=0
修改nginx配置文件

vim /etc/nginx/conf.d/default.conf
server {
    listen       80;
    server_name  localhost;

    #charset koi8-r;
    #access_log  /var/log/nginx/log/host.access.log  main;

    root   /usr/share/nginx/html;
    location / {
        #root   /usr/share/nginx/html;
        index  index.html index.htm;
    }

    #error_page  404              /404.html;

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        #root   /usr/share/nginx/html;
    }

    # proxy the PHP scripts to Apache listening on 127.0.0.1:80
    #
    #location ~ \.php$ {
    #    proxy_pass   http://127.0.0.1;
    #}

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    location ~ \.php$ {
    #    root           html;
        fastcgi_pass   127.0.0.1:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        include        fastcgi_params;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    #    deny  all;
    #}
}

[root@localhost log]# systemctl start php-fpm.service
[root@localhost log]# systemctl restart nginx.service 

ok
php和mysql
新建一个文件连接mysql试试

<?php
$con = mysql_connect("192.168.208.6:3306","root","testTEST!@#123");
if($con){
echo "ok";
}else{
echo mysql_error();
}
?>

但是连接报错
Permission denied
于是用远程连接mysql试试看
进入mysql

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'testTEST!@#123' WITH GRANT OPTION;
FLUSH PRIVILEGES;

然后再本机上面用远程连接时ok的,于是试着关掉selinux,

setenforce 0

就ok了
于是再开启selinux,查看http相关的selinux选项

[root@localhost html]# getsebool -a|grep -i httpd | grep net
httpd_can_network_connect --> off
httpd_can_network_connect_cobbler --> off
httpd_can_network_connect_db --> off
httpd_can_network_memcache --> off
httpd_can_network_relay --> off

修改selinux的选项

[root@localhost html]# setsebool -P httpd_can_network_connect=1

再就ok了~~~

centos7.0安装mysql5.7

下载mysql源的安装包
mysql源地址
安装源

rpm -ivf mysql-community-release-el7-5.noarch.rpm

安装

yum install mysql-server

然后按下Y就能安装成功了
但是我安装之后不能启动,运行了一下

mysql_secure_installation

会出现一些设置,root密码设置什么的,设置完成之后mysql自己就启动了
但是这个安装的是5.6版本的mysql,我想安装5.7的

yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community-dmr

然后更新一下·

yum update

就是5.7了

php安装5.6

来自于http://www.zabbix.cc/technic/1420/
配置yum源
追加CentOS 6.5的epel及remi源。

# rpm -Uvh http://ftp.iij.ad.jp/pub/linux/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
# rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

以下是CentOS 7.0的源。

yum install epel-release
rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

使用yum list命令查看可安装的包(Packege)。

# yum list --enablerepo=remi --enablerepo=remi-php56 | grep php

安装PHP5.6
yum源配置好了,下一步就安装PHP5.6。

# yum install --enablerepo=remi --enablerepo=remi-php56 php php-opcache php-devel php-mbstring php-mcrypt php-mysqlnd php-phpunit-PHPUnit php-pecl-xdebug php-pecl-xhprof

用PHP命令查看版本。

[root@www ~]# php --version
PHP 5.6.9 (cli) (built: May 15 2015 09:31:38) 
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
    with Zend OPcache v7.0.4-dev, Copyright (c) 1999-2015, by Zend Technologies
    with Xdebug v2.3.2, Copyright (c) 2002-2015, by Derick Rethans

在这里安装的版本是PHP5.6.0,细心的用户可能已经发现ZendGuardLoader变成Zend OPcahe了。

对从PHP5.5开始PHP代码缓存从APC变成了Zend OPcache了。

mysql触发器记录

一张表t1,两个字段,id和name字段,要实现一个功能,在往这张表里面插入数据的时候,如果表中有与插入的数据name字段相同的字段,则更新id字段,
之前没写过,不过大概记得以前学习mssql的时候有个东西可以早对表进行inser update delete的时候触发一些动作
下面是代码:

DELIMITER |
CREATE TRIGGER test1 BEFORE INSERT on `test1` FOR EACH ROW 
BEGIN 
DELETE FROM `test1` WHERE t1_name=new.t1_name;
END
|
delimiter ;

做到这个需求,需要用到了一个临时表。。
开始用的是

DELIMITER |
CREATE TRIGGER insert_updata BEFORE INSERT on `key_temp` FOR EACH ROW 
BEGIN 
DECLARE temp INT(3);
SET temp=(SELECT COUNT(*) FROM `key_gjc` WHERE key_name=new.key_name);
IF temp>0 THEN 
delete from `key_gjc` WHERE f=new.f;
INSERT INTO `key_gjc` (a,b,c,d,e,f) VALUES (new.a,new.b,new.c,new.d,new.e,new.f);
END IF;
END
|
delimiter ;

这种方法就实现了需要的效果,但是后来出现了一个问题,因为后来表又加了一个字段g,开始插入的时候,这个字段是空的,譬如该条记录如下

a         b       c      d       e           f             g
张三   22    男     111   30kg     3000    null

但是可能会对这条记录的g字段进行更改,譬如更改之后的记录为
```

a         b       c      d       e           f             g
张三   22    男     111   30kg     3000    小说

那么,如果再对key_temp表插入数据的时候,会触发触发器,根据触发器的逻辑,会实现更新,但是g字段的值就丢失了,所以进行了改进,于是又稍稍接触了下mysql的变量定义和流程控制语句~~~~,如下,有个问题,在if里面的sql语句,一定要记得结尾的分号啊,因为这个调了半天。。。。。。

DELIMITER |
CREATE TRIGGER insert_updata BEFORE INSERT on `key_temp` FOR EACH ROW 
BEGIN 
DECLARE temp INT(3);
SET temp=(SELECT COUNT(*) FROM `key_gjc` WHERE key_name=new.key_name);
IF temp>0 THEN 
UPDATE `key_gjc` SET a=new.a,b=new.b,c=new.c,d=new.d,e=new.e WHERE f=new.f;
ELSE 
INSERT INTO `key_gjc` (a,b,c,d,e,f) VALUES (new.a,new.b,new.c,new.d,new.e,new.f);
END IF;
END
|
delimiter ;

mysql不能远程登录

1、改表法

可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "user" 表里的 "host" 项,从"localhost"改称"%"

  mysql -u root -p

mysql>use mysql;

  mysql>update user set host = '%' where user = 'root';

  mysql>select host, user from user;

2、授权法

在安装mysql的机器上运行:

1,登陆mysql

d:\mysql\bin\>mysql -h localhost -u root

//这样应该可以进入MySQL服务器

2,设置权限

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION

//赋予任何主机访问数据的权限
例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。

  GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码

  GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

3,刷新权限

mysql>FLUSH PRIVILEGES

//修改生效

4

  mysql>EXIT

退出MySQL服务器,这样就可以在其它任何的主机上以root身份登录

转载自:http://wanwentao.blog.51cto.com/2406488/584075