标签 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的amoeba实现主从和读写分离

1.主从

主从数据库是通过mysql自带的功能完成的,mysql版本是5.7.7
主数据库:192.168.1.121
从数据库:192.168.1.122
统一主从数据的远程登陆账户密码,因为后面amoeba完成读写分离的需要这样
我就用root了,密码为123456
在主数据的配置文件的[mysqld]下面添加

#主数据库配置
log-bin=mysql-bin       #打开mysql二进制日志
server-id=1             #设置mysql_id主从不能相同,不能和其他的服务器相同
binlog-do-db=test       #设置二进制日志记录的库,多个数据库像这样复制一份就好了,所有数据库都需要记录,就去掉这一项
#binlog-do-db=test1        
binlog-ignore-db=mysql  #设置二进制日志不记录的库
sync_binlog=1

从数据库配置
在从数据库的配置文件的

#mysql主从配置 从数据库配置
log-bin=mysql-bin
server-id=2
replicate-do-db=test          #设置同步的库
replicate-ignore-db=mysql     #设置不同步的库
log-slave-updates             #同步后记录二进制日志
slave-skip-errors=all
sync_binlog=1
slave-net-timeout=60

在主数据库里面获得master的数据

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002  #这个会用到
         Position: 913               #这个会用到
     Binlog_Do_DB: test
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

在从数据库里面设置主数据库

mysql>stop slave;
mysql>change master to master_host='192.168.1.121',master_user='root',
master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=913;
mysql>start slave;

在从数据库查看是否成功,标记的两个都是yes就是好了

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.121
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 375
               Relay_Log_File: 192-relay-bin.000004
                Relay_Log_Pos: 541
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes       #这个是yes
            Slave_SQL_Running: Yes       #这个是yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
...后面太长,省略了

我在这里碰到一个问题

mysql> show slave status\G;
*************************** 1. row ***************************
...前面太长省略
             Slave_IO_Running: No
...省略
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
...省略

因为我是直接复制的虚拟机,所以mysql的uuid相同了,把数据目录的auto.conf文件删除就ok了

2.读写分离

下载jdk我是下载地址
根据版本下载,我下载的是 jdk-6u45-linux-x64.bin
安装

安装
#mkdir/usr/java/
//新建一个文件夹,把安装包给拷贝到这个文件夹下面
#chmod755jdk-6u25-linux-i586.bin
//给文件授权#
./jdk-6u25-linux-i586.bin//解压文件
配置jdk环境变量
#vim/etc/profile
//打开文件
在文件最后添加如下内容
######forjava
export JAVA_HOME=/usr/java/jdk1.6.0_45
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/jre/lib:$JAVA_HOME/lib:$JAVA_HOME/lib/tools.jar
#######
#source/etc/profile//重新执行一遍(有必要)
=============================================================================
测试JDK和AMOEBA_HOME是否设置成功
#java-version
//查看jdk版本验证java环境变量是否配置成功
javaversion"1.6.0_45"

安装amoeba

下载Amoeba并安装
#mkdir/usr/local/amoeba///创建文件夹
#cd/usr/local/amoeba///打开说创建的文件夹
#wget
http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz/download
//下载Amoeba文件
#md5sumamoeba-mysql-binary-2.1.0-RC5.tar.gz//校验MD5一般不需要
44f6708652a46f28a95338f35f5d4083amoeba-mysql-binary-2.1.0-RC5.tar.gz
#chmod755amoeba-mysql-binary-2.1.0-RC5.tar.gz//文件授权
#tar-zxvfamoeba-mysql-binary-2.1.0-RC5.tar.gz/解压文件
配置Amoeba环境变量
#vim/etc/profile
//打开文件
在结尾添加如下内容###amoeba
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin#######

配置 AMOEBA配置文件
配置mysql服务器的信息

vi /usr/local/amoeba/conf/dbServers.xml

...这一部分不需要改省略掉...
 <dbServer name="abstractServer" abstractive="true">
                <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
                        <property name="manager">${defaultManager}</property>
                        <property name="sendBufferSize">64</property>
                        <property name="receiveBufferSize">128</property>
                        #配置这部分的数据库服务器的mysql的账号和密码,端口
                        <!-- mysql port -->
                        <property name="port">3306</property>

                        <!-- mysql schema -->
                        <property name="schema">test</property>

                        <!-- mysql user -->
                        <property name="user">root</property>


                        <property name="password">123456</property>

                </factoryConfig>
...这一部分不需要改省略掉...
#以下面的格式设置好mysql服务器的ip,设置好别名,别名不能一样,在其他地方会用到
<dbServer name="Master"  parent="abstractServer">
               <factoryConfig>
                       <!-- mysql ip -->
                       <property name="ipAddress">192.168.1.121</property>
               </factoryConfig>
</dbServer>

<dbServer name="Server1"  parent="abstractServer">
        <factoryConfig>
                <!-- mysql ip -->
                <property name="ipAddress">192.168.1.122</property>
        </factoryConfig>
</dbServer>

<dbServer name="Server2"  parent="abstractServer">
        <factoryConfig>
                <!-- mysql ip -->
                <property name="ipAddress">192.168.1.123</property>
        </factoryConfig>
</dbServer>
#这个比较特殊,用作轮询
<dbServer name="multiPool" virtual="true">
       <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
               <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
               #这个属性设置成1,代表的就是轮询,
               <property name="loadbalance">1</property>
               <!-- Separated by commas,such as: server1,server2,server1 -->
                #这个选项里面填上面几个设置的名字,会依次轮询填进去的服务器
               <property name="poolNames">Server1,Server2</property>
       </poolConfig>
</dbServer>
...这一部分不需要改省略掉...

配置amoeba的信息

...这一部分不需要改省略掉...
#下面这部分设置连接amoeba的账号密码,通过mysql连接mysql的时候就填这里设置的账号的密码
<property name="authenticator">
        <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                <property name="user">root</property>

                <property name="password">123456</property>

                <property name="filter">
                        <bean class="com.meidusa.amoeba.server.IPAccessController">
                                <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                        </bean>
                </property>
        </bean>
</property>
...这一部分不需要改省略掉...
下面这部分就是设置读写分离和负载均衡的
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
        <property name="ruleLoader">
                <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
                        <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
                        <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
                </bean>
        </property>
        <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
        <property name="LRUMapSize">1500</property>
        #默认连接的数据库
        <property name="defaultPool">Master</property>
        #DML操作的数据库,填主数据库就行了
        <property name="writePool">Master</property>
        #填设置了轮询的dbServer的名称
        <property name="readPool">multiPool</property>

        <property name="needParse">true</property>
</queryRouter>

这样就ok了,在这一步碰到问题

#amoeba start
The stack size specified is too small, Specify at least 160k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

解决方法:

# vim amoeba
#修改58行的Xss参数:
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss128k"
#修改为:
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k"

参考:http://wenku.baidu.com/view/848d692e2e3f5727a4e96218.html?qq-pf-to=pcqq.c2c
http://blog.itpub.net/90618/viewspace-774894/