分类目录归档:Database

Mysql不锁表增加字段和索引方法

Mysql 8.0.12 新增 instant 算法,秒级添加字段

ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=instant

看了网上很多回答,基本都是过时和错误的。

5.6 以后增加了ONLINE DDL,

实现不锁表增加字段和索引非常简单。

解决办法

ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE

ALGORITHM表示算法:default默认(根据具体操作类型自动选择),inplace(不影响DML),copy创建临时表(锁表),INSTANT只修改元数据(8.0新增,在修改名字等极少数情况可用)

LOCK表示是否锁表:default默认,none,shared共享锁,exclusive

什么情况下会inplace,什么情况下会copy?

什么情况下会inplace,什么情况下会copy,具体参考:Online DDL Operations

5.6以前版本解决办法

参考

精品数据库(省市区县带拼音行政区划、大学、IP地址、农历、等)

行政区划-数据库

全国行政区划,省市区镇四级,包含名称、完整名称、经纬度、区号、邮编、行政区划代码、拼音。

使用说明

area.xlsx Excel文件数据
area.sql MySQL脚本
area.psc Navicat for MySQL备份文件
area.nb3 Navicat Premium备份文件

数据来源

高德API
民政部官网
中国邮政官网

postgresql 常用命令

创建用户

CREATE USER davide WITH PASSWORD 'jw8s0F4';
CREATE ROLE admin WITH CREATEDB CREATEROLE;

修改用户密码

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
ALTER ROLE davide WITH PASSWORD NULL;

创建数据库

CREATE DATABASE sales OWNER salesapp

设置数据库只读权限

create user test_readonly with password 'password';
grant connect on DATABASE test to test_readonly;
grant select on all tables in schema public to test_readonly;

导出导入

pg_dump -F t -h 127.0.0.1 -U postgres dbname > dbname.tar
pg_restore -d dbname dbname.tar

解决nginx+php/java/go/python+mysql下time_wait连接数过多问题

公司服务器连接数超过10K了,查了下大多数是没有即时回收,采用tcp复用容易后程序出现了故障,以前一直看到说PHP持久连接有问题,所以没怎么用,最近有机会试了下。

非常香,连接数直接降低到几百了,目前观察数周没有问题。

查看连接数

netstat -n |  wc -l # 总连接数
netstat -n | grep -i time_wait | wc -l # time_wait 连接数
netstat -anp # 查看占用端口过多的程序

tcp复用解决方案

网上大部分解决方案是修改sysctl.conf回收重用ipv4连接,但是可能带来其他问题

net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=30
net.ipv4.tcp_keepalive_time = 600

nginx fastcgi(php) 解决方案

修改nginx.conf

upstream fastcgi_backend {
    server 127.0.0.1:9000;
    keepalive 60;
}

location ~ \.php$ {
        fastcgi_pass  fastcgi_backend;
        fastcgi_keep_conn on;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include        fastcgi_params;
}

主要是 fastcgi_keep_conn onupstreamkeepalive

nginx + proxy (python/go/java) 解决方案

upstream wxpic {
    keepalive 60;
    server 127.0.0.1:xxx;
}

server {
    keepalive_requests 10000; # 默认100
    location / {
        proxy_http_version 1.1;
        proxy_set_header Connection "";
    }
}

主要是 proxy_http_version, proxy_set_header Connection ""以及keepalive_requests

mysql数据库连接解决方案

修改yii数据库配置采用长链接

'db' => [ 
    'class' => 'yii\db\Connection',
    'dsn' => '*', 'username' => '*', 
    'password' => '*', 
    'charset' => 'utf8mb4', 
    'attributes' => [ 
       PDO::ATTR_PERSISTENT => true 
    ]
],

redis 连接数

php redis连接数过多解决办法(Yii,predis,phpredis等)

CentOS7通过yum安装PostgreSQL10

发现最新的postgresql版本

https://www.postgresql.org/download/linux/redhat/

下载

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

安装

yum install postgresql10-server

设置数据目录

创建数据目录并修改权限

mkdir -p /data/postgresql/
chown postgres:postgres /data/postgresql/

复制开机启动文件

sudo cp /usr/lib/systemd/system/postgresql-10.service /etc/systemd/system/

修改开机启动文件

vim /etc/systemd/system/postgresql-10.service

找到

Environment=PGDATA=/var/lib/pgsql/10/data/

修改为

Environment=PGDATA=/data/postgresql/

初始化数据库

/usr/pgsql-10/bin/postgresql-10-setup initdb

开机自启动

systemctl enable postgresql-10
systemctl start postgresql-10
systemctl status postgresql-10

修改本地帐户权限

vim /data/postgresql/pg_hba.conf

host    all             all             127.0.0.1/32            ident

修改为

host    all             all             127.0.0.1/32            md5

重新加载配置

systemctl restart postgresql-10

新增用户和数据库

su - postgres
createuser dbuser
createdb -e -O dbuser dbname

设定密码

su - postgres
psql
\password dbuser (输入两次密码)

新用户登录数据库

psql -U dbuser -d dbname -h 127.0.0.1 (输入之前的密码)

允许非本机ip登录

  • 编辑data/postgresql.conf修改listen_addresses = 'localhost'listen_addresses = 'localhost'
  • 编辑data/pg_hba.conf

    host all all 192.168.1.0/24 md5

fedora27(centos)下用过dnf(yum)安装postgresql10

安装postgresql10-server

所有repo包都在 https://yum.postgresql.org/下,这里通过一级一级,先是postgresql10版本,然后选择fedora 10,然后下载安装

dnf install https://download.postgresql.org/pub/repos/yum/10/fedora/fedora-27-x86_64/pgdg-fedora10-10-3.noarch.rpm dnf install postgresql10-server

安装 pgadmin4

dnf install pgadmin4-v2

初始化数据库

sudo /usr/pgsql-10/bin/postgresql-10-setup initdb

运行数据库

系统服务被安装在/usr/lib/systemd/system/postgresql-10.service

systemctl start postgresql-10
systemctl enable postgresql-10

修改本地登录无需密码

sudo vim /var/lib/pgsql/10/data/pg_hba.conf

#host    all             all             127.0.0.1/32            ident
host    all             all             127.0.0.1/32            trust

使用Vagrant+CentOS 7搭建PHP7开发环境(含centos7.box直接下载地址)

Vagrant是一款基于命令行的虚拟机管理软件,可以用来快速部署统一的开发环境。

下载Vagrant

https://www.vagrantup.com/downloads.html

下载CentOS 7 Box

官方box下载地址

https://app.vagrantup.com/boxes/search

第三方box下载地址

http://www.vagrantbox.es/

使用原生下载

https://app.vagrantup.com/centos/boxes/7

vagrant init centos/7
vagrant up

vagrant 配置

  config.vm.network "public_network", ip: "192.168.31.245"
  config.vm.synced_folder "d:/data", "/data"
  config.vm.synced_folder "d:/phpcode", "/phpcode"

通过下载工具下载centos 7 box

官方box文件下载地址:http://cloud.centos.org/centos/7/vagrant/x86_64/images/

百度网盘box文件下载地址

  • CentOS 7: https://pan.baidu.com/s/1kVlAz59

添加并运行box

vagrant box add centos7 CentOS-7.box
vagrant init centos7
vagrant up

基础系统安装

基本系统安装

vagrant ssh
sudo passwd vagrant
sudo yum groupinstall "Development tools" -y
sudo yum install vim gcc kernel-devel kenel-devel-`uname -r`

禁止selinux

sudo setenforce 0 

sudo vi /etc/selinux/config

SELINUX=disabled

停止防火墙

sudo systemctl disable firewalld
sudo systemctl stop firewalld

更新系统

sudo yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm
sudo yum install yum-utils
sudo yum-config-manager --enable remi-php72
sudo yum update
sudo yum install php-gd php-pdo php-opcache php-fpm php-pecl-redis php-pecl-mysql php-pecl-mysql php-mbstring php-intl php-cli php-xml
sudo yum install nginx -y
sudo yum install mariadb mariadb-server -y

修改nginx配置

mkdir /data/log/nginx /data/run/nginx -p
sudo service nginx stop 
sudo vim /etc/nginx/nginx.conf

nginx.conf配置修改如下:

user vagrant;
error_log /data/log/nginx/error.log;
pid /data/run/nginx/nginx.pid;

access_log  /data/log/nginx/access.log  main;

include /data/phpcode/projectname/vagrant/nginx/app.conf;

测试nginx配置

sudo nginx -t

修改nginx service配置:

sudo vim /usr/lib/systemd/system/nginx.service

nginx.service修改内容如下:

[Service]
PIDFile=/data/run/nginx/nginx.pid

重新加载service

sudo systemctl daemon-reload
sudo systemctl start nginx

修改PHP配置

mkdir  /data/run/php-fpm/session /data/run/php-fpm/wsdlcache /data/run/php-fpm/opcache /data/log/php-fpm/ -p
sudo service php-fpm stop
sudo vim /etc/php-fpm.d/www.conf

配置内容

user = vagrant
group = vagrant
php_value[session.save_path]    = /data/run/php-fpm/session
php_value[soap.wsdl_cache_dir]  = /data/run/php-fpm/wsdlcache
php_value[opcache.file_cache]  = /data/run/php-fpm/opcache
php_admin_value[error_log] = /data/log/php-fpm/www-error.log
slowlog = /data/log/php-fpm/www-slow.log
request_slowlog_timeout = 1

重启

sudo service php-fpm stop

配置Mysql

mkdir /data/mysql /data/run/mariadb /data/log/mariadb -p
sudo service mariadb stop
sudo vim /etc/my.cnf

mysqld配置

[mysqld]
datadir=/data/mysql
socket=/usr/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/data/log/mariadb/mariadb.log
pid-file=/data/run/mariadb/mariadb.pid

mysql client 配置

[client]

初始化数据库

sudo /usr/libexec/mariadb-prepare-db-dir mariadb.service

修改systemd配置

sudo vim /usr/lib/systemd/system/mariadb.service

配置内容

User=vagrant
Group=vagrant

重载systemd

sudo systemctl daemon-reload
sudo systemctl start mariadb

修改mysql账号密码

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h localhost.localdomain password 'new-password'

# Alternatively you can run:
'/usr/bin/mysql_secure_installation'

配置composer

下载安装文件

php -r "copy('https://install.phpcomposer.com/installer', 'composer-setup.php');"

安装

php composer-setup.php

删除安装文件

php -r "unlink('composer-setup.php');"

设置全局路径(windows请按参考文档设置)

sudo mv composer.phar /usr/bin/composer

配置使用国内镜像

composer config -g repo.packagist composer https://packagist.phpcomposer.com

percona mysql 5.7 初始化数据库密码

ubuntu 16.04通过apt安装percona mysql 5.7以后,默认数据库密码不知道。

查询官网告诉默认在$HOME/.mysql_secret,实际也找不到

以非安全模式安装,然后初始化

mysqld --initialize --insecure
mysql_secure_installation

Percona安装Tokudb引擎

https://www.percona.com/doc/percona-server/LATEST/tokudb/tokudb_installation.html

添加percona源

参考:https://mirrors.gzqdn.org/help/percona/

安装libjemalloc

sudo apt install libjemalloc-dev

添加libjemalloc到配置

在/etc/mysql/percona-server.conf.d/mysqld_safe.cnf文件中添加

[mysqld_safe]
malloc-lib= /usr/include/jemalloc

关闭Transparent huge pages

查看Transparent huge pages状态

cat /sys/kernel/mm/transparent_hugepage/enabled

关闭Transparent huge pages需要以root身份运行

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

安装包

apt-get install percona-server-tokudb-5.7

激活Tokudb引擎

sudo ps_tokudb_admin --enable -uroot -pPassw0rd

查看状态

mysql> SHOW ENGINES;
mysql> SELECT @@tokudb_version;

mariadb

https://mariadb.com/kb/en/mariadb/enabling-tokudb/