标签归档:mysql

Laravel查询在死锁发生时返回空集合

想象一下,您有两个进程同时执行以下PHP代码,将显示什么结果?

\DB::transaction(function (){
   dump(User::where('id',1)->lockForUpdate()->first());
});

令人惊讶的是,一个进程打印正常的查询结果,而另一个进程打印一个空集合(数组)。 返回空集合的代码并未按照我们想的那样触发死锁异常!然而,如果您查阅了transaction方法的源代码,则会发现在发现死锁之后,该方法会自动重试事务。 而且当死锁的数量达到设定值时,最终死锁将被作为异常抛出。 但是,当前这个实际发生死锁的查询看起来却很正常,因为它返回了一个空集合!

这个意外返回的空数组和未抛出的死锁异常实际上是一个古老且棘手的PHP-PDO bug。 在7.4.13发布之前,它已经广泛存在于许多PHP版本中。 有许多与此相关的讨论,例如:

  1. https://bugs.php.net/bug.php?id=76742
  2. https://github.com/php/php-src/pull/5937
  3. https://github.com/php/php-src/pull/6203
  4. https://github.com/php/php-src/commit/b03776adb5bbb9b54731a44377632fcc94a59d2f

在PHP7.4.13之前,你几乎没有办法直接检测到此错误。 切勿尝试打开PDO :: ATTR_EMULATE_PREPARES来解决此问题! 因为此选项将使您所有的PDO查询结果都变成字符串,数据类型的丢失将会导致更严重的后果。

因此,唯一可行的方法是手动编译已修复的PHP源代码,或安装新的预编译版本的PHP7.4.13。

更改MySQL的数据存储(datadir)目录

步骤很简单,在开始之前,不要忘了先停止mysql服务。

service mysql stop

 

然后,拷贝之前旧的数据文件夹到新的位置

cp -rp /data/lib/mysql /mnt

上面的命令会把/data/lib/mysql这个文件夹的所有内容(包括权限),拷贝到/mnt/mysql

 

然后,修改/etc/mysql/my.cnf ,如果是Ubuntu使用mysql的apt源安装的,应该修改/etc/mysql/mysql.conf.d/mysqld.cnf

把datadir改成你需要的位置

 

下一步我们一般不会注意到,但是如果我们不修改这步,会造成mysql无法启动

编辑/etc/apparmor.d/usr.sbin.mysqld ,找到# Allow data dir access ,把他下面的旧数据目录替换成你的新的

然后,执行下面的命令即可

/etc/init.d/apparmor restart
service mysql start

 

参考:http://www.jianshu.com/p/5fb55e313f8c

Ubuntu 配置Node.js+Nginx+PHP+MySQL最新版本

配置环境

#有的vps需要解决php源乱码的问题
sudo apt-get install -y language-pack-en-base
vi /etc/profile
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
source /etc/profile
#解决php源乱码的问题 ending

#安装add-apt-repository
apt-get install software-properties-common

#安装nodejs源
curl -sL https://deb.nodesource.com/setup_6.x | sudo -E bash -

#新版本Ubuntu16.04默认支持openssl 1.0.2h
add-apt-repository ppa:nginx/stable

#老版本Ubuntu 14.04建议使用 PPA for NGINX with HTTP/2 on Ubuntu 12.04 LTS and higher,使用下面的源可以同时升级openssl,可以开启http2
#https://launchpad.net/~ondrej/+archive/ubuntu/nginx/
add-apt-repository ppa:ondrej/nginx

#下面安装php7 mysql5.7源
add-apt-repository ppa:ondrej/php
add-apt-repository ppa:ondrej/mysql-5.7

apt-get update
apt-get install nodejs openssl nginx mysql-server php7.3 php7.3-gd php7.3-mbstring php7.3-xml php7.3-zip php7.3-curl php7.3-fpm php7.3-mysql php7.3-bcmath php7.3-dev

#查看openssl版本
openssl version

nginx.conf配置

user www-data;
worker_processes auto;
pid /run/nginx.pid;
include /etc/nginx/modules-enabled/*.conf;

events {
	worker_connections 768;
	# multi_accept on;
}

http {

	##
	# Basic Settings
	##

	sendfile on;
	tcp_nopush on;
	tcp_nodelay on;
	keepalive_timeout 65;
	types_hash_max_size 2048;
	# server_tokens off;

	# server_names_hash_bucket_size 64;
	# server_name_in_redirect off;

	include /etc/nginx/mime.types;
	default_type application/octet-stream;

	##
	# SSL Settings
	##

	ssl_protocols               TLSv1 TLSv1.1 TLSv1.2;
	ssl_ciphers                 EECDH+CHACHA20:EECDH+CHACHA20-draft:EECDH+AES128:RSA+AES128:EECDH+AES256:RSA+AES256:EECDH+3DES:RSA+3DES:!MD5;
	ssl_prefer_server_ciphers   on;

	client_max_body_size 2m;

	##
	# Logging Settings
	##

	access_log /var/log/nginx/access.log;
	error_log /var/log/nginx/error.log;

	##
	# Gzip Settings
	##

	gzip on;
	gzip_disable "msie6";
	gzip_min_length 1k;
	gzip_buffers 4 16k;
	gzip_comp_level 2;
	gzip_types text/plain application/javascript application/x-javascript text/css application/xml text/javascript application/x-httpd-php image/jpeg image/gif image/png font/ttf font/otf image/svg+xml;
	gzip_vary on;

	##
	# Virtual Host Configs
	##

	include /etc/nginx/conf.d/*.conf;
	include /etc/nginx/sites-enabled/*;
}


#mail {
#	# See sample authentication script at:
#	# http://wiki.nginx.org/ImapAuthenticateWithApachePhpScript
# 
#	# auth_http localhost/auth.php;
#	# pop3_capabilities "TOP" "USER";
#	# imap_capabilities "IMAP4rev1" "UIDPLUS";
# 
#	server {
#		listen     localhost:110;
#		protocol   pop3;
#		proxy      on;
#	}
# 
#	server {
#		listen     localhost:143;
#		protocol   imap;
#		proxy      on;
#	}
#}

默认服务器,ip返回403配置

server_tokens off;
proxy_hide_header X-Powered-By;


server {
	listen 80 default_server;
	server_name _;
	return      403;
}

server {
	listen 443 ssl http2 default_server;
	server_name _;
	ssl			on;
	ssl_certificate		/etc/letsencrypt/live/xxx/fullchain.pem;
	ssl_certificate_key	/etc/letsencrypt/live/xxx/privkey.pem;
	return 403;
}

fastcgi_params限制PHP脚本执行目录

#PHP Prohibit cross-Hosting
fastcgi_param  PHP_VALUE  "open_basedir=$document_root:/tmp/";

纯静态配置

server {
	listen		80;
	server_name	xxxx;
	root		/home/nginx/xxxx;
	index		index.html index.htm index.php;
	location ~ .*\.(gif|jpg|jpeg|png|bmp)$
	{
		expires 30d;
	}
	location ~ .*\.(woff|ttf|svg)$
	{
		expires 180d;
	}
	location ~ .*\.(js|css)?$
	{
		expires 12h;
	}
	location /
	{
 	 	 try_files $uri $uri/ =404;
	}
}

PHP一般通用配置

server {
	listen			80;
	server_name		xxxx;
	index			index.php index.html index.htm;
	root			/home/nginx/xxx;
	
	location ~ .*\.(gif|jpg|jpeg|png|bmp)$
	{
		expires 30d;
	}
	location ~ .*\.(woff|ttf|svg)$
	{
		expires 180d;
	}
	location ~ .*\.(js|css)?$
	{
		expires 12h;
	}
	location ~ \.php($|/)
	{
		try_files $uri = 404;
		fastcgi_pass unix:/run/php/php7.0-fpm.sock;
		fastcgi_index index.php;
		fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
		include fastcgi_params;
	}
}

开启fix_pathinfo(一般默认已开启)

#确认php.ini中的如下配置,其实默认已经为1,当框架出现问题时可以来检查一下
cgi.fix_pathinfo = 1

PHP通用框架配置

server {
	listen		80;
	server_name	xxxx;
	index		index.html index.htm index.php;
	root		/home/nginx/xxxx;
	
	location ~ .*\.(gif|jpg|jpeg|png|bmp|ico|webp)$
	{
		expires 30d;
	}
	location ~ .*\.(woff|ttf|svg|otf|eot)$
	{
		expires 180d;
	}
	location ~ .*\.(js|css)?$
	{
		expires 12h;
	}
	location ^~ /.svn
	{
		deny all;
	}
	location ^~ /.git
	{
		deny all;
	}
	location /
	{
		if (!-e $request_filename) { 
			rewrite ^(.*)$ /index.php/$1 last;
			break;
		}
	}
	location ~ \.php($|/)
	{
		fastcgi_split_path_info ^((?U).+.php)(/?.+)$;
		fastcgi_param PATH_INFO $fastcgi_path_info;
		fastcgi_pass unix:/run/php/php7.0-fpm.sock;
		fastcgi_param SCRIPT_FILENAME  $document_root$fastcgi_script_name;
		include	fastcgi_params;
	}
}

SSL+HTTP2的PHP框架配置

server {
	listen				443 ssl http2;
	server_name			xxx;
	index				index.html index.htm index.php;
	root				/home/nginx/xxx;

	ssl_session_cache		shared:SSL:10m;
	ssl_session_timeout		60m;

	ssl_session_tickets		on;

	ssl_certificate			/etc/letsencrypt/live/xxx/fullchain.pem;
	ssl_certificate_key		/etc/letsencrypt/live/xxx/privkey.pem;

	location ~ .*\.(gif|jpg|jpeg|png|bmp|ico|webp)$
	{
		expires 30d;
	}
	location ~ .*\.(woff|ttf|svg|otf|eot)$
	{
		expires 180d;
	}
	location ~ .*\.(js|css)?$
	{
		expires 12h;
	}
	location ^~ /.svn
	{
		deny all;
	}
	location ^~ /.git
	{
		deny all;
	}
	location /
	{
		if (!-e $request_filename) { 
			rewrite ^(.*)$ /index.php/$1 last;
			break;
		}
	}
	location ~ \.php($|/)
	{
		fastcgi_split_path_info ^((?U).+.php)(/?.+)$;
		fastcgi_param PATH_INFO $fastcgi_path_info;
		fastcgi_pass unix:/run/php/php7.0-fpm.sock;
		fastcgi_param SCRIPT_FILENAME  $document_root$fastcgi_script_name;
		include	fastcgi_params;
	}
}

 

MySQL5.6.30低配机器的配置文件,最小资源占用

[mysqld]

innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

引用文章:http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html

linux下彻底卸载mysql

停止Mysql服务

service mysqld stop

查询已安装的mysql包并删除

rpm -qa|grep -i mysql

QQ截图20160310151708

然后复制上面的包名,分别执行

rpm -e --nodeps 包名

查找并删除mysql相关目录

find / -name mysql

20160310152135

把上面的目录(注意分辨一下用途),分别执行

rm -rf 目录

最后删除my.cnf

 rm -rf /etc/my.cnf

 

在CentOS6.5上用rpm方法安装mysql最新版本5.7.10

首先,下载mysql-5.7.10-1.el6.x86_64.rpm-bundle.tar

wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.10-1.el6.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.10-1.el6.x86_64.rpm-bundle.tar

你也可以到mysql的官方网站下载:http://dev.mysql.com/downloads/

QQ截图20151216190001

 

安装依赖

yum install numactl

下载完了之后进入到相应目录,执行下面的命令

rpm -ivh mysql-community-common-5.7.10-1.el6.x86_64.rpm mysql-community-libs-5.7.10-1.el6.x86_64.rpm mysql-community-client-5.7.10-1.el6.x86_64.rpm mysql-community-server-5.7.10-1.el6.x86_64.rpm mysql-community-devel-5.7.10-1.el6.x86_64.rpm

然后执行下面命令初始化,执行生会在/var/log/mysqld.log生成随机密码,最后一行可以看到

mysqld --initialize

最后别忘了启动mysqld服务,更改root密码,设置开机启动和映射管道文件(php将默认使用/tmp/mysql.sock)

chown -R mysql.mysql /var/lib/mysql
service mysqld start
/usr/bin/mysqladmin -u root -p'你的初始化密码' password '你的新密码'
chkconfig mysqld on
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock