分类 系统运维 下的文章

dz install


yum -y install make openssl openssl-devel pcre pcre-devel libpng libpng-devel libjpeg-6b libjpeg-devel-6b freetype freetype-devel gd gd-devel zlib zlib-devel gcc gcc-c++ libXpm libXpm-devel ncurses ncurses-devel libmcrypt libmcrypt-devel libxml2 libxml2-devel imake autoconf automake screen sysstat compat-libstdc++-33 curl curl-devel

wget http://nginx.org/download/nginx-1.3.0.tar.gz
tar -zxvf nginx-1.3.0.tar.gz
cd nginx-1.3.0
mkdir -p /usr/local/webserver/nginx
./configure --prefix=/usr/local/webserver/nginx --with-http_stub_status_module --with-http_ssl_module --with-http_realip_module --with-http_addition_module --with-http_gzip_static_module --with-http_random_index_module --with-http_stub_status_module --with-http_sub_module --with-http_dav_module

make  & make install

Ngnix 作为系统服务:

vi /etc/init.d/nginx
#输也以下内容:http://itboren.com/?p=9
chmod +x /etc/init.d/nginx
/sbin/chkconfig nginx on
/sbin/chkconfig --list nginx
yum install cmake  bison pcre* libxml2-devel libxml2-static  zlib-devel openssl* libcurl*  libjpeg-devel libpng-devel  gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers cmake bison ncurses-devel gcc gcc-c++ ncurses-devel
wget http://cn2.php.net/distributions/php-5.6.30.tar.bz2

cp -frp /usr/lib64/libldap* /usr/lib/
./configure --prefix=/usr/local/webserver/php --with-config-file-path=/usr/local/webserver/php/etc --with-mysql=/opt/mysql --with-mysqli=/opt/mysql/bin/mysql_config --with-iconv-dir=/usr/local --with-freetype-dir --with-jpeg-dir --with-png-dir --with-zlib --with-libxml-dir=/usr --enable-xml --disable-rpath --enable-safe-mode --enable-bcmath --enable-shmop --enable-sysvsem --enable-inline-optimization --with-curl --with-curlwrappers --enable-mbregex --enable-fpm --enable-mbstring --with-mcrypt --with-gd --enable-gd-native-ttf --with-openssl --with-mhash --enable-pcntl --enable-sockets --with-ldap --with-ldap-sasl --with-xmlrpc --enable-zip --enable-soap --without-pear
export LD_LIBRARY_PATH="/opt/mysql/lib:$LD_LIBRARY_PATH"
make
make install

Mysql 常见流水账


1.导入:

mysql –default-character-set=UTF8 -uroot -p123456 -P3306 pics < imgconfig.sql

rds 中:

mysql -h rds.host  –default-character-set=UTF8 -uroot -p123456 mjzlweb<mjzlweb.sql

如何让 mysqldump/mysql 导入数据时忽略错误继续执行

mysql -uroot -p123456 -f database < filename.sql
#-f 参数,就是force的意思,忽略错误,强制继续运行

2.导出 :

mysqldump –default-character-set=UTF8 -uroot -p123456 taobao2 –table userinfo|gzip > taobao2_userinfo_20110506.sql.zip
#表的备务,带压缩哦

rds 中:

mysqldump -h rds,host  –default-character-set=UTF8 -uroot -p123456  mjzlweb>mjzlweb.sql
mysqldump –default-character-set=UTF8 -uroot -p123456 taobao2 –database taobao2|gzip > taoba2.sql.zip 
#数据库的备务,带压缩哦 

//解压这个包:

gunzip -c taobao2.sql.zip > taobao2.sql

常见问题:
问题1: mysqldump: Got error: 1066: Not unique table/alias: ‘c3p0testtable’ when using LOCK TABLES
解决办法: dump 里加一个参数 –skip-lock-tables 就可以

导出一条记录(这里是nick 等于 dress 的记录),并且是 insert :

mysqldump -uroot -p111111 databasename –extended-insert=false –table userinfo -w ”nick=’dress’”>in.sql

导出一给定条件的记录(这里是id>100的记录),并且是insert:

mysqldump -uroot -p111111  databaseneame –table tablename -w”id>100″>new.sql

导出 表结构

mysqldump –default-character-set=UTF8 -uroot -p111111 -d database  tables> tables.sql

3.统计:

select sum(payprice) as tail from payrecord;

4.执行指令

mysql -uroot -p123456 -e”use taobao2;select * from userinfo;” >> temp.log

5.创建索引 :

create index collname on tablename(collname);

删除

Drop indexname on tablename;

6.显示当前运行的Query

mysql> show processlist

小记:
用命令可显示出条种状态的sql 和 idnember
show processlist
可用 kill idnember 终止执行。

设置mysql 自动断开连接的时间

set global  wait_timeout=600;set  global interactive_timeout=600;

查看:

show global variables like ‘%timeout%’;

7.查看当前最大连接数:

mysqladmin -uusername -ppassword variables |find “max_con”

8.显示当前状态:

mysql> show status

9.修复表

repair table tablename;

表的数据去重,保留id 最小的一条,其余的删掉:

mysql  –default-character-set=UTF8 -uroot -pxxxxx -e”use tablename;select  CONCAT(‘delete from xx_table where id<>’,min(id),’ and nick=\”‘ ,nick,’\”;’) from xx_table    group  by  nick    having  count(nick) > 1;”>>del.sql

查看 MySQL 数据库中每个表占用的空间大小
如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表,这个表主要字段分别是:
TABLE_SCHEMA : 数据库名
TABLE_NAME:表名
ENGINE:所使用的存储引擎
TABLES_ROWS:记录数
DATA_LENGTH:数据大小
INDEX_LENGTH:索引大小

其他字段请参考MySQL的手册,我们只需要了解这几个就足够了。
所以要知道一个表占用空间的大小,那就相当于是 数据大小 + 索引大小 即可。
SQL:

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA=’数据库名’ AND TABLE_NAME=’表名’

如何动态开启mysql的慢查询日志记录
在线开启慢查询日志:

mysql > set global slow_query_log=1;
mysql > set global long_query_time=2;
mysql > set global slow_query_log_file=’mysql-slow.log’;

如果不需要记录慢查询了,可以很方便的关闭:

mysql > set global slow_query_log=0;

注意:在5.1.29以前用的是log_slow_queries变量,5.1.29及之后的版本log_slow_queries变量就用slow_query_log变量代替了。slow_query_log指定开启还是关闭,slow_query_log_file指定日志文件filename,long_query_time指定慢查询的阈值。
注意2:long_query_time变量从MySQL 5.1.21开始,可以被设置的最小值为0,且精度可以被设置到微秒级(例如值可以被设置为 1.000002,即1秒又2微秒);而在5.1.21之前的版本,这个变量可以被设置的最小值为1,且只能为整型。

PT 的使用和安装:

wget http://www.percona.com/downloads/percona-toolkit/2.2.4/RPM/percona-toolkit-2.2.4-1.noarch.rpm
yum install -y percona-toolkit-2.2.4-1.noarch.rpm

查看mysql 的慢日志:

pt-query-digest /var/mysql/data/mysql-slow.log

查看test数据库的重复索引和外键使用情况使用如下命令

pt-duplicate-key-checker  –host=localhost –user=root –password=123456  –databases=zzbtrade -S /var/tmp/mysql/mysql.sock

sql 注入特定值

“”()))”()
”))(”)’(
AnD
aND
AnD
aND
AND
/etc/
windows/win
md5(

centos nginx php mysql 环境安装


如果系统是 CentOS 5.8

yum -y install gcc gcc-c++ cmake ncurses-devel
yum install -y autoconf automake zlib libxml2 libgcrypt libtool bison

如果系统是 centos 6 首先执行:

    yum install cmake  bison pcre* libxml2-devel libxml2-static  zlib-devel openssl* libcurl*  libjpeg-devel libpng-devel  gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers cmake bison ncurses-devel gcc gcc-c++ ncurses-devel

下载软软包:

wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz
wget http://cn2.php.net/distributions/php-5.6.31.tar.bz2
wget https://nchc.dl.sourceforge.net/project/mcrypt/Libmcrypt/2.5.8/libmcrypt-2.5.8.tar.bz2

Install MySQL

mkdir -p /usr/local/webserver/mysql
tar -zxvf mysql-5.5.24.tar.gz
cd mysql-5.5.24
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/var/mysql/data -DWITH_EXTRA_CHARSETS:STRING=all
make
make install
ln -s  /usr/local/webserver/mysql/lib/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16

设置libmysqlclient的软链,防止编译其它依赖Mysql软件时出现libmysqlclient.so.18: cannot open shared object file 的错误
  32位系统下的MYSQL客户端软链接

ln -s  /usr/local/webserver/mysql/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18

  64位系统下的MYSQL客户端软链接

ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.18 /usr/lib64/libmysqlclient.so.18

参考:http://hi.baidu.com/mucunzhishu/blog/item/d05574636268e2ca8cb10d59.html

Install  libmcrypt
tar -jxvf libmcrypt-2.5.8.tar.bz2
cd libmcrypt-2.5.8
./configure
make
make install
ln -s /usr/local/lib/libmcrypt.so /usr/lib64/libmcrypt.so
ln -s /usr/local/lib/libmcrypt.so /usr/lib/libmcrypt.so

Install PHP

tar -jxvf  php-5.3.13.tar.bz2
cd php-5.3.13
cp -frp /usr/lib64/libldap* /usr/lib/
./configure --prefix=/usr/local/webserver/php --with-config-file-path=/usr/local/webserver/php/etc --with-mysql=/usr/local/webserver/mysql --with-mysqli=/usr/local/webserver/mysql/bin/mysql_config --with-iconv-dir=/usr/local --with-freetype-dir --with-jpeg-dir --with-png-dir --with-zlib --with-libxml-dir=/usr --enable-xml --disable-rpath  --enable-safe-mode --enable-bcmath --enable-shmop --enable-sysvsem --enable-inline-optimization --with-curl --with-curlwrappers --enable-mbregex  --enable-fpm  --enable-mbstring --with-mcrypt --with-gd --enable-gd-native-ttf --with-openssl --with-mhash --enable-pcntl --enable-sockets --with-ldap --with-ldap-sasl --with-xmlrpc --enable-zip --enable-soap --without-pear
#ln -s /usr/lib64/libldap.so  /usr/lib/libldap.so
export LD_LIBRARY_PATH="/usr/local/webserver/mysql/lib:$LD_LIBRARY_PATH"
make

注:如报错 /usr/bin/ld: cannot find -lltdl
解决办法:yum install libtool-ltdl-devel
如报错 collect2: ld returned 1 exit status
解决方法:

make ZEND_EXTRA_LIBS='-liconv'
make install
cp php.ini-production /usr/local/webserver/php/etc/php.ini

修改php的配置文件,设置时间

vi   /usr/local/webserver/php/etc/php.ini
#改更: date.timezone =  Asia/Shanghai

参考:http://hi.baidu.com/mucunzhishu/blog/item/206c4605c530b46b0308817a.html
php-fpm 自动启动:

vi /etc/init.d/php-fpm
#写入以下内容:http://itboren.com/?p=5
chmod +x /etc/init.d/php-fpm
/sbin/chkconfig php-fpm on
/sbin/chkconfig --list php-fpm

打开慢日志:
slowlog = log/$pool.log.slow
request_slowlog_timeout = 10s
Install Nginx

wget http://nginx.org/download/nginx-1.3.0.tar.gz
tar -zxvf nginx-1.3.0.tar.gz
cd nginx-1.3.0
mkdir -p /usr/local/webserver/nginx
./configure --prefix=/usr/local/webserver/nginx --with-http_stub_status_module --with-http_ssl_module
make  & make install

Ngnix 作为系统服务:

vi /etc/init.d/nginx
#输也以下内容:http://itboren.com/?p=9
chmod +x /etc/init.d/nginx
/sbin/chkconfig nginx on
/sbin/chkconfig --list nginx

centos 6.x 源码安装 mysql 5.6


卸载旧版本(第一次安装不用此步)

使用下面的命令检查是否安装有MySQL Server

rpm -qa | grep mysql

有的话通过下面的命令来卸载掉

rpm -e mysql   #普通删除模式
rpm -e --nodeps mysql    #强力删除模式

如果使用上面命令删除时,提示有依赖的其它文件,则用该命令可以对其进行强力删除

1、安装前先装依赖包


yum install cmake bison pcre* libxml2-devel libxml2-static zlib-devel openssl* libcurl* libjpeg-devel libpng-devel gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers cmake bison ncurses-devel gcc gcc-c++ ncurses-devel

2、下载


wget  http://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.14.tar.gz

3、创建mysql 安装目录和数据存放目录

 mkdir -p /opt/mysql/data

4、cmake

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/opt/mysql/data -DWITH_EXTRA_CHARSETS:STRING=all

5、

make #(时间有点长哦,慢慢等吧)
make install

6、加个软链(x64)

ln -s /opt/mysql/lib/libmysqlclient.so.18 /usr/lib64/libmysqlclient.so.18

7、配置mysql

cat /etc/passwd  #查看用户列表
cat /etc/group #查看用户组列表

groupadd mysql
useradd -g  mysql mysql

cd /opt/mysql
#给权限
chown -R mysql:mysql /opt/mysql
#初始化
scripts/mysql_install_db --basedir=/opt/mysql --datadir=/opt/mysql/data --user=mysql

注:在启动MySQL服务时,会按照一定次序搜索my.cnf,先在/etc目录下找,找不到则会搜索"$basedir/my.cnf",在本例中就是 /opt/mysql/my.cnf,这是新版MySQL的配置文件的默认位置!

vi /opt/mysql/my.cnf 
#更改如下值:
datadir = /opt/mysql/data
socket = /tmp/mysql.sock

8、启动MYSQL
添加服务,拷贝服务脚本到init.d目录,并设置开机启动

cp support-files/mysql.server /etc/init.d/mysql
chkconfig mysql on
service mysql start

9、配置用户

vi /etc/profile
#输入:
###mysql
export PATH=/opt/mysql/bin:$PATH
:wq

source /etc/profile

现在,我们可以在终端内直接输入mysql进入,mysql的环境了
执行下面的命令修改root密码

mysql -uroot 
mysql> SET PASSWORD = PASSWORD(‘123456'); #设置密码
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ‘123456' WITH GRANT OPTION; #允许远程访问

10、配置防火墙

防火墙的3306端口默认没有开启,若要远程访问,需要开启这个端口

vi  /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p -dport 3306 -j ACCEPT
:wq

service iptables restart #重启