标签归档:mysql

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

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

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以前版本解决办法

参考

解决nginx+php+yii2下time_wait连接数过多问题

查看连接数

netstat -n |  wc -l
netstat -n | grep -i time_wait | wc -l

发现9000和3306的time_out状态的连接数过多, 网上大部分解决方案是修改sysctl.conf回收重用ipv4连接, 但是这在公司的阿里云机器上好像行不通,回导致无法访问。

tcp复用解决方案

net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=30

解决nginx time_wait连接数过多问题

修改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;
}

解决mysql time_out连接数过多

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

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

使用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

mysql生成固定位数随机字母以及数字

随机数字

CREATE DEFINER=`root`@`%` FUNCTION `rand_int`(counts INTEGER) RETURNS varchar(20) CHARSET utf8
BEGIN
       DECLARE sTemp VARCHAR(20);
    DECLARE sTempCounts INTEGER;
       SET sTemp = CONCAT( ROUND(ROUND(RAND(),counts)*(POW(10,counts))),'');

    IF(CHAR_LENGTH(sTemp)<counts) THEN

      SET sTempCounts = counts - CHAR_LENGTH(sTemp);
      SET sTemp = CONCAT(sTemp, RIGHT(CONCAT(POW(10,sTempCounts),''),sTempCounts));
    END IF;
      RETURN sTemp;
END

随机字母:

CREATE DEFINER=`root`@`%` FUNCTION `rand_str`(  
    f_num INT UNSIGNED
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
      DECLARE i INT UNSIGNED DEFAULT 0;   
      DECLARE v_result VARCHAR(200) DEFAULT '';   
      DECLARE v_dict VARCHAR(200) DEFAULT '';  
      SET v_dict = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';  
      SET v_dict = LPAD(v_dict,200,v_dict);  
      WHILE i < f_num   
      DO   
        SET v_result = CONCAT(v_result,SUBSTR(v_dict,CEIL(RAND()*200),1));  
        SET i = i + 1;   
      END WHILE;    
      RETURN v_result;   
END

mysql切换主库记录

原来公司采用的是异地Mysql机房。三台机器是A(主)->B(从,主)->C(从)模式。

今天因为测试磁盘性能不小心将B磁盘数据弄没了。(直接将这篇文章的sdb1换成sda2然后整个磁盘数据没有了)

于是要将C的主库从B切换到A上。

在C上找到最后同步的binlog

show slave status

在C上找到binlog的最后同步点

sudo mysqlbinlog --base64-output=decode-rows -v --set-charset=utf8  /home/mysql/mysql-bin.000015 --start-datetime="2016-12-19 11:38:00" --stop-datetime="2016-12-19 11:39:00"

在A上找到对应的同步点

mysqlbinlog  --base64-output=decode-rows -v --set-charset=utf8 /data/mysql/mysql-bin.001232 --start-datetime="2016-12-19 11:38:00" --stop-datetime="2016-12-19 11:38:50"

开始同步

change master to master_host='x.x.x.x', master_user='slave', master_password='********', master_log_file='mysql-bin.001232', master_log_pos=648129189;

同步发现没有权限,增加同步权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'x.x.x.x' IDENTIFIED BY 'password';

解决同步时出现Cannot execute the current event group in the parallel mode错误

set global slave_parallel_workers=0;

php mysql实现非连续不重复ID

非连续ID有很多用处.最大的一个用处就是作为订单id,用户id之类的不想让别人遍历(直接通过请求里面传id值来爬你的数据),以及不想让别人看出来你有多少数据量(如果是连续id,应用有多少订单,一目了然)的id.

mysql的实现方法

添加一个触发器,每次插入的时候通过取当前最大的id然后加一个随机数得到新的id.

   CREATE TRIGGER `rand_increase` BEFORE INSERT ON `users` FOR EACH ROW SET NEW.id=(SELECT max(id) FROM users)+FLOOR(RAND()*500)

php的实现方法

生成一个ID,到数据库里面查看ID是否存在,如果存在,再生成一个.

class model
{
    public function generateRandomId($length=15) {
        $random = "";
        for ($i = 0; $i < $length; $i++) {
            $random .= mt_rand(0, 9);
        }
        return $random;
    }
    public function beforeInsert() {
        do{
            $inserId=$this->generateRandomId(10);
        }while(!$this->selectById($inserId));
        $this->insertId=$inserId;
    }
}

理论上,在超高并发条件下,这两种方法都不是完美的解决办法,但是实际上只要你的id不是太短(比如15位以上?),不完美发生的概率非常非常非常非常非常非常非常非常小.

来源:php mysql实现非连续不重复ID