Pomelo Lee workspace

	show variables like '%time_zone%';
 
	set global time_zone='Asia/Shanghai';
 
	set  time_zone='Asia/Shanghai';
 
	flush privileges;

	修改my.cnf
 
	default-time-zone ='Asia/Shanghai'

mysql> show global variables like '%general%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                         |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)
 
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global general_log_file='/data/logs/mysql/mysql_general_log.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/data/logs/mysql/mysql_general_log.log'

chown -R mysql:mysql /data/logs/mysql/
chomd 777 /data/logs/mysql/mysql_general_log.log

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘name’ at row 1 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) 
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) 
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662) 
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)

[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'
 
[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
 
Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8mb4
character_set_system	utf8
collation_connection	utf8mb4_unicode_ci
collation_database	utf8mb4_unicode_ci
collation_server	utf8mb4_unicode_ci
collation_connection 、collation_database 、collation_server是什么没关系。
 
但必须保证
 
系统变量	描述
character_set_client	(客户端来源数据使用的字符集)
character_set_connection	(连接层字符集)
character_set_database	(当前选中数据库的默认字符集)
character_set_results	(查询结果字符集)
character_set_server	(默认的内部操作字符集)
这几个变量必须是utf8mb4。

set character_set_client      = utf8mb4;                       
set character_set_connection  = utf8mb4;                       
set character_set_database    = utf8mb4;                       
set character_set_results     = utf8mb4;                       
set character_set_server      = utf8mb4;

ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS `event`;
CREATE TABLE `event` (
  `db` CHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` CHAR(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` CHAR(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` INT(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` SET('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` CHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` INT(10) UNSIGNED NOT NULL,
  `time_zone` CHAR(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` CHAR(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` CHAR(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` CHAR(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';

1.系统预先配置
yum install wget
yum install pcre
yum install openssl*
yum -y install 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 make
yum -y install gd gd2 gd-devel gd2-devel
/usr/sbin/groupadd www
/usr/sbin/useradd -g www www
ulimit -SHn 65535
mkdir -p /ia/data/
cd /ia/data/
wget http://ftp.exim.llorien.org/pcre/pcre-8.32.tar.gz
tar -zxvf pcre-8.32.tar.gz 
mkdir tgz
mv pcre-8.32* tgz/
mkdir installsoft
cd installsoft/
wget http://nginx.org/download/nginx-1.6.2.tar.gz
tar xzvf nginx-1.6.2.tar.gz 
cd nginx-1.6.2
./configure --user=www --group=www --prefix=/usr/local/webserver/nginx --with-http_stub_status_module --with-http_ssl_module --with-pcre=/ia/data/tgz/pcre-8.32 --with-http_realip_module --with-http_image_filter_module
make
make install
/usr/local/webserver/nginx/sbin/nginx -V
 
　2、安装 MySQL：
wget http://downloads.mysql.com/archives/mysql-5.6/mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz
tar zxvf mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.10-linux-glibc2.5-x86_64 /usr/local/webserver/mysql
/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql
mkdir -p /Data/data/mysql/data
yum install libaio
 
 
/usr/local/webserver/mysql/scripts/mysql_install_db --basedir=/usr/local/webserver/mysql --datadir=/ia/data/mysql/data --user=mysql
 
##mysql 5.7更新了初始化方式会有默认密码生成 使用 --initialize-insecure 会不生成root密码否则注意看密码
/usr/local/webserver/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/webserver/mysql --datadir=/ia/data/mysql/data 
 
sed -i "s#/usr/local/mysql#/usr/local/webserver/mysql#g" /usr/local/webserver/mysql/bin/mysqld_safe
GRANT ALL PRIVILEGES ON *.* TO 'ia_admin'@'localhost' IDENTIFIED BY '12345678';
GRANT ALL PRIVILEGES ON *.* TO 'ia_admin'@'127.0.0.1' IDENTIFIED BY '12345678';
GRANT ALL PRIVILEGES ON *.* TO 'ia_admin'@'%' IDENTIFIED BY '12345678';
##忘记密码重置的配置 my.cnf 添加
#skip-grant-tables
#skip-networking
 
#后续mysql5.7登录mysql执行
ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
update user set authentication_string=PASSWORD("12345678") where User='root';
##修改成功后 可以启用GRANT去新增用户，会让强制改下root密码再执行
 
3、安装PHP依赖库
mkdir -p /usr/local/webserver/libs/
wget http://www.ijg.org/files/jpegsrc.v9.tar.gz     
tar zxvf jpegsrc.v9.tar.gz
cd jpeg-9/
./configure --prefix=/usr/local/webserver/libs --enable-shared --enable-static --prefix=/usr/local/webserver/libs
make
make install
cd ../
 
wget http://prdownloads.sourceforge.net/libpng/libpng-1.6.2.tar.gz
tar zxvf libpng-1.6.2.tar.gz
cd libpng-1.6.2/
./configure --prefix=/usr/local/webserver/libs
make
make install
cd ../
 
wget http://download.savannah.gnu.org/releases/freetype/freetype-2.4.12.tar.gz
tar zxvf freetype-2.4.12.tar.gz
cd freetype-2.4.12/
./configure --prefix=/usr/local/webserver/libs
make
make install
cd ../
 
 
wget "http://downloads.sourceforge.net/mhash/mhash-0.9.9.9.tar.gz"
wget "http://downloads.sourceforge.net/mcrypt/libmcrypt-2.5.8.tar.gz"
wget "http://downloads.sourceforge.net/mcrypt/mcrypt-2.6.8.tar.gz"
 
 
tar zxvf libmcrypt-2.5.8.tar.gz
cd libmcrypt-2.5.8/
./configure --prefix=/usr/local/webserver/libs
make
make install
cd libltdl/
./configure --prefix=/usr/local/webserver/libs --enable-ltdl-install
make
make install
cd ../../
 
 
tar zxvf mhash-0.9.9.9.tar.gz
cd mhash-0.9.9.9/
./configure --prefix=/usr/local/webserver/libs
make
make install
cd ../
 
vi /etc/ld.so.conf
 
添加：
/usr/local/webserver/libs/lib
 
然后：
ldconfig
 
 
tar zxvf mcrypt-2.6.8.tar.gz
cd mcrypt-2.6.8/
export LDFLAGS="-L/usr/local/webserver/libs/lib -L/usr/lib"
export CFLAGS="-I/usr/local/webserver/libs/include -I/usr/include"
touch malloc.h
./configure --prefix=/usr/local/webserver/libs --with-libmcrypt-prefix=/usr/local/webserver/libs
make
make install
cd ../
 
4、编译安装PHP 5.5
wget http://cl1.php.net/get/php-5.5.20.tar.gz/from/this/mirror
tar zxvf php-5.5.20.tar.gz
cd php-5.5.20/
export LIBS="-lm -ltermcap -lresolv"
export DYLD_LIBRARY_PATH="/usr/local/webserver/mysql/lib/:/lib/:/usr/lib/:/usr/local/lib:/lib64/:/usr/lib64/:/usr/local/lib64"
export LD_LIBRARY_PATH="/usr/local/webserver/mysql/lib/:/lib/:/usr/lib/:/usr/local/lib:/lib64/:/usr/lib64/:/usr/local/lib64"
 
ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.20.3.4 /usr/local/webserver/mysql/lib/libmysqlclient_r.so
 
./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 --with-freetype-dir=/usr/local/webserver/libs --with-jpeg-dir=/usr/local/webserver/libs --with-png-dir=/usr/local/webserver/libs --with-zlib --with-libxml-dir=/usr --enable-xml --disable-rpath --enable-bcmath --enable-shmop --enable-sysvsem --enable-inline-optimization --with-curl --enable-mbregex --enable-fpm --enable-mbstring --with-mcrypt=/usr/local/webserver/libs --with-gd --enable-gd-native-ttf --with-openssl --with-mhash --enable-pcntl --enable-sockets --with-xmlrpc --enable-zip --enable-soap --enable-opcache --with-pdo-mysql --enable-maintainer-zts
 
make
make install
cp php.ini-development /usr/local/webserver/php/etc/php.ini
 
##生产环境推荐
cp php.ini-production /usr/local/webserver/php/etc/php.ini
cd ../
ln -s /usr/local/webserver/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
如果是5.7的mysql做软链接
ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.20 /usr/lib/libmysqlclient.so.20
 
mv /usr/local/webserver/php/etc/php-fpm.conf.default /usr/local/webserver/php/etc/php-fpm.conf
 
##一定记得修改 php-fpm.conf 的启动组为www(把对应的nobody修改下,否则php文件无法接收)
 
　5、编译安装PHP扩展
wget http://ftp.gnu.org/gnu/autoconf/autoconf-latest.tar.gz
tar zxvf autoconf-latest.tar.gz
cd autoconf-2.69/
./configure --prefix=/usr/local/webserver/libs
make
make install
cd ../
 
wget http://pecl.php.net/get/memcache-2.2.7.tgz
tar zxvf memcache-2.2.7.tgz
cd memcache-2.2.7/
export PHP_AUTOCONF="/usr/local/webserver/libs/bin/autoconf"
export PHP_AUTOHEADER="/usr/local/webserver/libs/bin/autoheader"
/usr/local/webserver/php/bin/phpize
./configure --with-php-config=/usr/local/webserver/php/bin/php-config
make
make install
cd ../
 
 
　　打开 /usr/local/webserver/php/etc/php.ini 查找 ; extension_dir = "ext"
　　在其后增加一行：
extension = "memcache.so"

mysql> SHOW VARIABLES LIKE "character_set%";

mysqldump -uroot -p123456 --default-character-set=latin1  i5a6  i5a6_data --where=" id < 100" > bki5a6.sql

INSERT INTO Table2(field1,field2,...) SELECT value1,value2,... FROM Table1