分布式锁的三种实现方式

分布式锁三种实现方式:

  1. 基于数据库实现分布式锁;
  2. 基于缓存(Redis等)实现分布式锁;
  3. 基于Zookeeper实现分布式锁;

一, 基于数据库实现分布式锁

1. 悲观锁

利用select … where … for update 排他锁

*其他附加功能与实现一基本一致,这里需要注意的是“where name=lock ”,name字段必须要走索引,否则会锁表。有些情况下,比如表不大,mysql优化器会不走这个索引,导致锁表问题。

2. 乐观锁

所谓乐观锁与前边最大区别在于基于CAS思想,是不具有互斥性,不会产生锁等待而消耗资源,操作过程中认为不存在并发冲突,只有update version失败后才能觉察到。

我们的抢购、秒杀就是用了这种实现以防止超卖。通过增加递增的版本号字段实现乐观锁

1637136916-5676-e3b761944edd6611defde7ee28-0

二, 基于缓存(Redis等)实现分布式锁

1. 使用命令介绍:

(1)SETNX
SETNX key val:当且仅当key不存在时,set一个key为val的字符串,返回1;若key存在,则什么都不做,返回0。
(2)expire
expire key timeout:为key设置一个超时时间,单位为second,超过这个时间锁会自动释放,避免死锁。
(3)delete
delete key:删除key

在使用Redis实现分布式锁的时候,主要就会使用到这三个命令。

2. 实现思想:

(1)获取锁的时候,使用setnx加锁,并使用expire命令为锁添加一个超时时间,超过该时间则自动释放锁,锁的value值为一个随机生成的UUID,通过此在释放锁的时候进行判断。
(2)获取锁的时候还设置一个获取的超时时间,若超过这个时间则放弃获取锁。
(3)释放锁的时候,通过UUID判断是不是该锁,若是该锁,则执行delete进行锁释放。

3. 分布式锁的简单实现代码:

package com.demo.app;

import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.Transaction;
import redis.clients.jedis.exceptions.JedisException;

import java.util.List;
import java.util.UUID;

/**
 * 分布式锁的简单实现代码
 */
class DistributedLock {

    private final JedisPool jedisPool;

    public DistributedLock(JedisPool jedisPool) {
        this.jedisPool = jedisPool;
    }

    /**
     * 加锁
     *
     * @param lockName       锁的key
     * @param acquireTimeout 获取超时时间
     * @param timeout        锁的超时时间
     * @return 锁标识
     */

    public String lockWithTimeout(String lockName, long acquireTimeout, long timeout) {

        Jedis conn = null;
        String retIdentifier = null;
        try {
            // 获取连接
            conn = jedisPool.getResource();
            // 随机生成一个value
            String identifier = UUID.randomUUID().toString();
            // 锁名,即key值
            String lockKey = "lock:" + lockName;
            // 超时时间,上锁后超过此时间则自动释放锁
            int lockExpire = (int) (timeout / 1000);
            // 获取锁的超时时间,超过这个时间则放弃获取锁
            long end = System.currentTimeMillis() + acquireTimeout;

            while (System.currentTimeMillis() < end) {
                if (conn.setnx(lockKey, identifier) == 1) {
                    conn.expire(lockKey, lockExpire);
                    // 返回value值,用于释放锁时间确认
                    retIdentifier = identifier;
                    return retIdentifier;
                }

                // 返回-1代表key没有设置超时时间,为key设置一个超时时间
                if (conn.ttl(lockKey) == -1) {
                    conn.expire(lockKey, lockExpire);
                }
                try {
                    Thread.sleep(10);
                } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                }
            }
        } catch (JedisException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return retIdentifier;
    }

    /**
     * 释放锁
     *
     * @param lockName   锁的key
     * @param identifier 释放锁的标识
     * @return
     */
    public boolean releaseLock(String lockName, String identifier) {
        Jedis conn = null;
        String lockKey = "lock:" + lockName;
        boolean retFlag = false;

        try {
            conn = jedisPool.getResource();
            while (true) {
                // 监视lock,准备开始事务
                conn.watch(lockKey);
                // 通过前面返回的value值判断是不是该锁,若是该锁,则删除,释放锁
                if (identifier.equals(conn.get(lockKey))) {
                    Transaction transaction = conn.multi();
                    transaction.del(lockKey);
                    List<Object> results = transaction.exec();
                    if (results == null) {
                        continue;
                    }
                    retFlag = true;
                }
                conn.unwatch();
                break;
            }
        } catch (JedisException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return retFlag;
    }
}

4. 测试刚才实现的分布式锁

例子中使用50个线程模拟秒杀一个商品,使用–运算符来实现商品减少,从结果有序性就可以看出是否为加锁状态。
模拟秒杀服务,在其中配置了jedis线程池,在初始化的时候传给分布式锁,供其使用。

public class Service {
    private static JedisPool pool = null;
    private DistributedLock lock = new DistributedLock(pool);
    int n = 500;
    
    static {
        JedisPoolConfig config = new JedisPoolConfig();
        // 设置最大连接数
        config.setMaxTotal(200);
        // 设置最大空闲数
        config.setMaxIdle(8);
        // 设置最大等待时间
        config.setMaxWaitMillis(1000 * 100);
        // 在borrow一个jedis实例时,是否需要验证,若为true,则所有jedis实例均是可用的
        config.setTestOnBorrow(true);
        pool = new JedisPool(config, "127.0.0.1", 6379, 3000);

    }

    public void secKill() {
        // 返回锁的value值,供释放锁时候进行判断
        String identifier = lock.lockWithTimeout("resource", 5000, 1000);
        System.out.println(Thread.currentThread().getName() + "获得了锁");
        System.out.println(--n);
        lock.releaseLock("resource", identifier);

    }
}

模拟线程进行秒杀服务;

public class ThreadA extends Thread {
    private Service service;
    public ThreadA(Service service) {
        this.service = service;
    }
    
    @Override
    public void run() {
        service.secKill();
    }
}

public class Test {
    public static void main(String[] args) {
        Service service = new Service();
        for (int i = 0; i < 50; i++) {
            ThreadA threadA = new ThreadA(service);
            threadA.start();
        }
    }
}

结果如下,结果为有序的:

1637136917-5764-ceea4c8cbfa184228a02822f99-1

若注释掉使用锁的部分:

public void secKill() {
    // 返回锁的value值,供释放锁时候进行判断
    //String indentifier = lock.lockWithTimeout("resource", 5000, 1000);
    System.out.println(Thread.currentThread().getName() + "获得了锁");
    System.out.println(--n);
    //lock.releaseLock("resource", indentifier);
}

从结果可以看出,有一些是异步进行的:

1637136917-6356-ceea4c8cbfa184228a02822f99-2

三, 基于Zookeeper实现分布式锁

ZooKeeper是一个为分布式应用提供一致性服务的开源组件,它内部是一个分层的文件系统目录树结构,规定同一个目录下只能有一个唯一文件名。基于ZooKeeper实现分布式锁的步骤如下:

  • (1)创建一个目录mylock;
  • (2)线程A想获取锁就在mylock目录下创建临时顺序节点;
  • (3)获取mylock目录下所有的子节点,然后获取比自己小的兄弟节点,如果不存在,则说明当前线程顺序号最小,获得锁;
  • (4)线程B获取所有节点,判断自己不是最小节点,设置监听比自己次小的节点;
  • (5)线程A处理完,删除自己的节点,线程B监听到变更事件,判断自己是不是最小的节点,如果是则获得锁。

这里推荐一个Apache的开源库Curator,它是一个ZooKeeper客户端,Curator提供的InterProcessMutex是分布式锁的实现,acquire方法用于获取锁,release方法用于释放锁。

实现源码如下:

public class ZkLock implements DistributionLock {

    private String zkAddress = "zk_adress";
    private static final String root = "package root";
    private CuratorFramework zkClient;
    private final String LOCK_PREFIX = "/lock_";
    
    @Bean
    public DistributionLock initZkLock() {
        if (StringUtils.isBlank(root)) {
            throw new RuntimeException("zookeeper 'root' can't be null");
        }
        zkClient = CuratorFrameworkFactory
                .builder()
                .connectString(zkAddress)
                .retryPolicy(new RetryNTimes(2000, 20000))
                .namespace(root)
                .build();
        zkClient.start();
        return this;
    }

    public boolean tryLock(String lockName) {
        lockName = LOCK_PREFIX + lockName;
        boolean locked = true;

        try {
            Stat stat = zkClient.checkExists().forPath(lockName);
            if (stat == null) {
                log.info("tryLock:{}", lockName);
                stat = zkClient.checkExists().forPath(lockName);
                if (stat == null) {
                    zkClient
                            .create()
                            .creatingParentsIfNeeded()
                            .withMode(CreateMode.EPHEMERAL)
                            .forPath(lockName, "1".getBytes());
                } else {
                    log.warn("double-check stat.version:{}", stat.getAversion());
                    locked = false;
                }
            } else {
                log.warn("check stat.version:{}", stat.getAversion());
                locked = false;
            }

        } catch (Exception e) {
            locked = false;
        }
        return locked;
    }

    public void release(String lockName) {
        lockName = LOCK_PREFIX + lockName;
        try {
            zkClient
                    .delete()
                    .guaranteed()
                    .deletingChildrenIfNeeded()
                    .forPath(lockName);
            log.info("release:{}", lockName);
        } catch (Exception e) {
            log.error("删除", e);
        }

    }

    public void setZkAddress(String zkAddress) {
        this.zkAddress = zkAddress;
    }

}

优点:具备高可用、可重入、阻塞锁特性,可解决失效死锁问题。

缺点:因为需要频繁的创建和删除节点,性能上不如Redis方式。

四,对比

数据库分布式锁实现

缺点:

1.db操作性能较差,并且有锁表的风险

2.非阻塞操作失败后,需要轮询,占用cpu资源;

3.长时间不commit或者长时间轮询,可能会占用较多连接资源

Redis(缓存)分布式锁实现

缺点:

1.锁删除失败 过期时间不好控制

2.非阻塞,操作失败后,需要轮询,占用cpu资源;

ZK分布式锁实现

缺点:性能不如redis实现,主要原因是写操作(获取锁释放锁)都需要在Leader上执行,然后同步到follower。

ZooKeeper有较好的性能和可靠性。

从理解的难易程度角度(从低到高)数据库 > 缓存 > Zookeeper

从实现的复杂性角度(从低到高)Zookeeper >= 缓存 > 数据库

从性能角度(从高到低)缓存 > Zookeeper >= 数据库

从可靠性角度(从高到低)Zookeeper > 缓存 > 数据库

MySQL参数 · innodb_additional_mem_pool_size

参数简介

innodb_additional_mem_pool_size 是 InnoDB 用来保存数据字典信息和其他内部数据结构的内存池的大小,单位是 byte,参数默认值为8M。数据库中的表数量越多,参数值应该越大,如果 InnoDB 用完了内存池中的内存,就会从操作系统中分配内存,同时在 error log 中打入报警信息。目前8.0+的版本已经移除了这个参数。

innodb_use_sys_malloc 配置为 ON 时,innodb_additional_mem_pool_size 失效(直接从操作系统分配内存)。

innodb_additional_mem_pool_size 和 innodb_use_sys_malloc 在 MySQL 5.7.4 中移除。

参数合理值预估

./storage/innobase/handler/ha_innodb.cc:
srv_mem_pool_size = (ulint) innobase_additional_mem_pool_size;

./storage/innobase/srv/srv0srv.cc:        mem_init(srv_mem_pool_size);

storage/innobase/mem/mem0dbg.cc: mem_comm_pool = mem_pool_create(size);

从源码中可以看出,innodb_additional_mem_pool_size 的参数值用于指定内存池 mem_comm_pool 的大小;

storage/innobase/mem/mem0mem.cc:
        block = static_cast<mem_block_t*>(
                mem_area_alloc(&len, mem_comm_pool));

函数 mem_area_alloc 从 mem_comm_pool 内存池中分配内存;

storage/innobase/mem/mem0pool.cc:

/* If we are using os allocator just make a simple call
to malloc */
        if (UNIV_LIKELY(srv_use_sys_malloc)) {
        return(malloc(*psize));
}

........

area = UT_LIST_GET_FIRST(pool->free_list[n]);

if (area == NULL) {
        ret = mem_pool_fill_free_list(n, pool);

        if (ret == FALSE) {
                /* Out of memory in memory pool: we try to allocate
                from the operating system with the regular malloc: */

                mem_n_threads_inside--;
                mutex_exit(&(pool->mutex));

                return(ut_malloc(size));
        }

        area = UT_LIST_GET_FIRST(pool->free_list[n]);
}

如果 innodb_use_sys_malloc (上述代码中的srv_use_sys_malloc) 设置为 ON,或者内存池中没有足够的内存可供分配,则直接从操作系统中分配内存。

mem_area_alloc 调用栈如下(use database 触发断点)

#0  mem_area_alloc
#1  0x000000000118048d in mem_heap_create_block_func
#2  0x000000000149a390 in mem_heap_create_func
#3  0x00000000014aa6d5 in dict_load_table
#4  0x0000000001481082 in dict_table_open_on_name
#5  0x000000000109d769 in ha_innobase::open
#6  0x00000000006d5245 in handler::ha_open
#7  0x0000000000b830ae in open_table_from_share
#8  0x000000000091deee in open_table
#9  0x0000000000922eea in open_and_process_table
#10 0x000000000092492f in open_tables
#11 0x0000000000926c21 in open_normal_and_derived_tables
#12 0x0000000000a83834 in mysqld_list_fields
#13 0x00000000009f28e1 in dispatch_command
#14 0x00000000009eeb51 in do_command
#15 0x0000000000982cb6 in do_handle_one_connection
#16 0x000000000098238b in handle_one_connection
#17 0x0000000001877f91 in pfs_spawn_thread
#18 0x0000003d8c007851 in start_thread ()
#19 0x0000003d8bce767d in clone ()

函数 dict_load_table 中会为每张表分配32k的空间 ( mem_heap_create(32000) 实际分配32744字节空间 ),数据字典中每张表所占空间的上限是32k,具体占用空间根据列数和索引数量分配,分配完成后回收32k中未使用的空间

storage/innobase/dict/dict0load.cc: heap = mem_heap_create(32000);

show engine innodb status BUFFER POOL AND MEMORY Dictionary cache

实际使用的数据字典缓存,不会超过每张表32k,实测过程中,每张表不包括索引占4K,每个索引占2k,列数对空间占用影响不大。

测试用表如下,未建索引时,1000张表占用空间4M,增加列占用空间增长不明显,每增加一个索引,占用空间增加2M,可以估测每张表占用空间4k(不含索引),每个索引占用空间2k。

Create Table: CREATE TABLE `1000` (
  `id` int(11) DEFAULT NULL,
  `a` varchar(255) DEFAULT NULL,
 `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

引入和移除该参数的原因

早期操作系统的内存分配器性能和可伸缩性较差,并且当时没有适合多核心CPU的内存分配器。所以,InnoDB 实现了一套自己的内存分配系统,做为内存系统的参数之一,引入了innodb_additional_mem_pool_size

随着多核心CPU的广泛应用和操作系统的成熟,操作系统能够提供性能更高、可伸缩性更好的内存分配器,包括 Hoard、libumem、mtmalloc、ptmalloc、tbbmalloc 和 TCMalloc 等。InnoDB 实现的内存分配器相比操作系统的内存分配器并没有明显优势,所以在之后的版本,会移除 innodb_additional_mem_pool_size 和 innodb_use_sys_malloc 两个参数,统一使用操作系统的内存分配器。

文章转自:https://developer.aliyun.com/article/32384

官方文档:https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html

MySQL的四种事务隔离级别

一、事务的基本要素(ACID)

1、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。

2、一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到。

3、隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。

4、持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。

二、事务的并发问题

1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。

3、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

三、MySQL事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)

 

 

CentOS 7x安装Mysql8.0.x

其实官网有教程的,我这里记录下来,是因为要写一个自动化安装脚本,虽然不经常用到,但是还是写出来比较好。

Mysql8.0仅支持CentOS 7x系统下安装,反正6.5的测试过一次,但是安装成功,启动失败,原因是缺少依赖。

Mysql8.0相信只会安装到64位的机器上。

  • 首先,下载mysql8.0的压缩包:

# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
# wget http://mysql.mirror.kangaroot.net/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz

我测试了最后一个,下载速度最快,所以贴上来好了。

  • 解压缩

tar -xzvf mysql-8.0.18-el7-x86_64.tar.gz
mv mysql-8.0.18-el7-x86_64/* /alidata/server/mysql
mkdir -p /alidata/server/mysql/data
ln -s /alidata/server/mysql-8.0.18 /usr/local/mysql
chmod 777 /alidata/server/mysql
chmod 777 /alidata/server/mysql/data

  • 权限配置

groupadd mysql
useradd -g mysql -s /sbin/nologin mysql
chown -R mysql:mysql /alidata/server/mysql/
chown -R mysql:mysql /alidata/server/mysql/data/
chown -R mysql:mysql /alidata/log/mysql
chmod -R 777 /alidata/server/mysql/support-files
chmod -R 777 /alidata/server/mysql/bin
\cp -f /alidata/server/mysql/support-files/mysql.server /etc/init.d/mysqld
sed -i ‘s#^basedir=$#basedir=/alidata/server/mysql#’ /etc/init.d/mysqld
sed -i ‘s#^datadir=$#datadir=/alidata/server/mysql/data#’ /etc/init.d/mysqld

  • 输出my.cnf文件

cat > /etc/my.cnf <<END
[client]
port=3306
default-character-set=utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
default_authentication_plugin=mysql_native_password
skip-external-locking
log-error=/alidata/log/mysql/error.log
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_log_buffer_size=4M
innodb_log_file_size=256M
interactive_timeout=120
join_buffer_size=2M
key_buffer_size=32M
max_allowed_packet=16M
max_connections=100
max_heap_table_size=64M
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=32M
read_buffer_size=512kb
read_rnd_buffer_size=4M
server_id=1
skip-external-locking=on
sort_buffer_size=256kb
table_open_cache=256
thread_cache_size=16
tmp_table_size=64M
wait_timeout=120

[mysql]
default-character-set=utf8
END

  • 最后,安装并启动

/alidata/server/mysql/bin/mysqld –initialize –user=mysql
chmod 755 /etc/init.d/mysqld
/etc/init.d/mysqld start

*需要注意:

mysql8.0安装成功后,会把初始密码写到log-error对应的文件中,我这里设置的路径是/alidata/log/mysql/error.log,打开这个文件,找到密码,并使用:mysql -uroot -p

登陆终端,把密码修改了,不然的话登陆上去,也操作不了其他。

修改密码的代码:

#alter user ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘bsiidno6gH0′;
#flush privileges

最后,贴出整个shell代码:

#!/bin/bash

yum install -y libaio

ifubuntu=$(cat /proc/version | grep ubuntu)
if14=$(cat /etc/issue | grep 14)

if [ `uname -m` == "x86_64" ];then
machine=x86_64
else
machine=i686
fi
if [ $machine == "x86_64" ];then
  rm -rf mysql-8.0.18-el7-x86_64
  if [ ! -f mysql-8.0.18-el7-x86_64.tar.gz ];then
#   wget http://zy-res.oss-cn-hangzhou.aliyuncs.com/mysql/mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
#   wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
#   wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
#   wget http://mysql.mirror.kangaroot.net/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
    wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
  fi
  tar -xzvf mysql-8.0.18-el7-x86_64.tar.gz
  mv mysql-8.0.18-el7-x86_64/* /alidata/server/mysql
  mkdir -p /alidata/server/mysql/data
  ln -s /alidata/server/mysql-8.0.18 /usr/local/mysql
  chmod 777 /alidata/server/mysql   
  chmod 777 /alidata/server/mysql/data

else
  echo 'unsupport machine i686'
fi

if [ "$ifubuntu" != "" ] && [ "$if14" != "" ];then
   mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
fi

groupadd mysql
useradd -g mysql -s /sbin/nologin mysql
chown -R mysql:mysql /alidata/server/mysql/
chown -R mysql:mysql /alidata/server/mysql/data/
chown -R mysql:mysql /alidata/log/mysql
chmod  -R 777 /alidata/server/mysql/support-files
chmod  -R 777 /alidata/server/mysql/bin
\cp -f /alidata/server/mysql/support-files/mysql.server /etc/init.d/mysqld
sed -i 's#^basedir=$#basedir=/alidata/server/mysql#' /etc/init.d/mysqld
sed -i 's#^datadir=$#datadir=/alidata/server/mysql/data#' /etc/init.d/mysqld
cat > /etc/my.cnf <<END
[client]
port=3306
default-character-set=utf8
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
default_authentication_plugin=mysql_native_password
skip-external-locking
log-error=/alidata/log/mysql/error.log
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin=mysql-bin
binlog_format=mixed
server-id       = 1

innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_log_buffer_size=4M
innodb_log_file_size=256M
interactive_timeout=120
join_buffer_size=2M
key_buffer_size=32M
max_allowed_packet=16M
max_connections=100
max_heap_table_size=64M
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=32M
read_buffer_size=512kb
read_rnd_buffer_size=4M
server_id=1
skip-external-locking=on
sort_buffer_size=256kb
table_open_cache=256
thread_cache_size=16
tmp_table_size=64M
wait_timeout=120

[mysql]
default-character-set=utf8


END

/alidata/server/mysql/bin/mysqld --initialize --user=mysql
chmod 755 /etc/init.d/mysqld
/etc/init.d/mysqld start

#mysql password see @/alidata/log/mysql/error.log
#and must by alter root password.
#
#alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bsiidno6gH0';
#flush privileges
#use mysql mysql
#update user set user.Host='%'where user.User='root';
#export PATH=$PATH:/alidata/server/mysql/bin
#
#/alidata/server/php/bin/php -f ./res/init_mysql.php

官方文档:

https://dev.mysql.com/doc/refman/8.0/en/installing.html

mysql频繁访问、读写配置

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
#TYPE: SYSTEM
#END CONFIG INFO

#
# This is a MySQL example config file for systems with 4GB of memory
# running mostly MySQL using InnoDB only tables and performing complex
# queries with few connections.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the “–help” option.
#
# More detailed information about the individual options can also be
# found in the manual.
#

#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

# *** Application-specific options follow here ***

#
# The MySQL server
#
[mysqld]

# generic configuration options
port = 3306
socket = /tmp/mysql.sock

# back_log is the number of connections the operating system can keep in
# the listen queue, before the MySQL connection manager thread has
# processed them. If you have a very high connection rate and experience
# “connection refused” errors, you might need to increase this value.
# Check your OS documentation for the maximum value of this parameter.
# Attempting to set back_log higher than your operating system limit
# will have no effect.
back_log = 50

# Don’t listen on a TCP/IP port at all. This can be a security
# enhancement, if all processes that need to connect to mysqld run
# on the same host. All interaction with mysqld must be made via Unix
# sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the “enable-named-pipe” option) will render mysqld useless!
#skip-networking

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections = 100

# Maximum amount of errors allowed per host. If this limit is reached,
# the host will be blocked from connecting to the MySQL server until
# “FLUSH HOSTS” has been run or the server was restarted. Invalid
# passwords and other errors during the connect phase result in
# increasing this value. See the “Aborted_connects” status variable for
# global counter.
max_connect_errors = 10

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable “open-files-limit” in
# section [mysqld_safe]
table_open_cache = 2048

# Enable external file level locking. Enabled file locking will have a
# negative impact on performance, so only use it in case you have
# multiple database instances running on the same files (note some
# restrictions still apply!) or if you use other software relying on
# locking MyISAM tables on file level.
#external-locking

# The maximum size of a query packet the server can handle as well as
# maximum query size server can process (Important when working with
# large BLOBs). enlarged dynamically, for each connection.
max_allowed_packet = 16M

# The size of the cache to hold the SQL statements for the binary log
# during a transaction. If you often use big, multi-statement
# transactions you can increase this value to get more performance. All
# statements from transactions are buffered in the binary log cache and
# are being written to the binary log at once after the COMMIT. If the
# transaction is larger than this value, temporary file on disk is used
# instead. This buffer is allocated per connection on first update
# statement in transaction
binlog_cache_size = 1M

# Maximum allowed size for a single HEAP (in memory) table. This option
# is a protection against the accidential creation of a very large HEAP
# table which could otherwise use up all memory resources.
max_heap_table_size = 64M

# Size of the buffer used for doing full table scans.
# Allocated per thread, if a full scan is needed.
read_buffer_size = 2M

# When reading rows in sorted order after a sort, the rows are read
# through this buffer to avoid disk seeks. You can improve ORDER BY
# performance a lot, if set this to a high value.
# Allocated per thread, when needed.
read_rnd_buffer_size = 16M

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead – See the “Sort_merge_passes”
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 8M

# This buffer is used for the optimization of full JOINs (JOINs without
# indexes). Such JOINs are very bad for performance in most cases
# anyway, but setting this variable to a large value reduces the
# performance impact. See the “Select_full_join” status variable for a
# count of full JOINs. Allocated per thread if full join is found
join_buffer_size = 8M

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client’s threads are put in the cache if there aren’t
# more than thread_cache_size threads from before. This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn’t give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size = 8

# This permits the application to give the threads system a hint for the
# desired number of threads that should be run at the same time. This
# value only makes sense on systems that support the thread_concurrency()
# function call (Sun Solaris, for example).
# You should try [number of CPUs]*(2..4) for thread_concurrency
thread_concurrency = 8

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# “Qcache_lowmem_prunes” status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size = 64M

# Only cache result sets that are smaller than this limit. This is to
# protect the query cache of a very large result set overwriting all
# other query results.
query_cache_limit = 2M

# Minimum word length to be indexed by the full text search index.
# You might wish to decrease it if you need to search for shorter words.
# Note that you need to rebuild your FULLTEXT index, after you have
# modified this value.
ft_min_word_len = 4

# If your system supports the memlock() function call, you might want to
# enable this option while running MySQL to keep it locked in memory and
# to avoid potential swapping out in case of high memory pressure. Good
# for performance.
#memlock

# Table type which is used by default when creating new tables, if not
# specified differently during the CREATE TABLE statement.
default-storage-engine = MYISAM

# Thread stack size to use. This amount of memory is always reserved at
# connection time. MySQL itself usually needs no more than 64K of
# memory, while if you use your own stack hungry UDF functions or your
# OS requires more stack for some operations, you might need to set this
# to a higher value.
thread_stack = 192K

# Set the default transaction isolation level. Levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size = 64M

# Enable binary logging. This is required for acting as a MASTER in a
# replication configuration. You also need the binary log if you need
# the ability to do point in time recovery from your latest backup.
log-bin=mysql-bin

# binary logging format – mixed recommended
binlog_format=mixed

# If you’re using replication with chained slaves (A->B->C), you need to
# enable this option on server B. It enables logging of updates done by
# the slave thread into the slave’s binary log.
#log_slave_updates

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Print warnings to the error log file. If you have any problem with
# MySQL you should enable logging of warnings and examine the error log
# for possible explanations.
#log_warnings

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in “long_query_time” or which do not use
# indexes well, if log_short_format is not enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
slow_query_log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use “1” as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2
# *** Replication related settings
# Unique server identification number between 1 and 2^32-1. This value
# is required for both master and slave hosts. It defaults to 1 if
# “master-host” is not set, but will MySQL will not function as a master
# if it is omitted.
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) –
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master’s port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# changes in this file to the variable values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 – 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave – required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master – required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master – required
#master-password = <password>
#
# The port the master is listening on.
# optional – defaults to 3306
#master-port = <port>

# Make the slave read-only. Only users with the SUPER privilege and the
# replication slave thread will be able to modify data on it. You can
# use this to ensure that no applications will accidently modify data on
# the slave instead of the master
#read_only
#*** MyISAM Specific options
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you’re not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size = 32M

# MyISAM uses special tree-like cache to make bulk inserts (that is,
# INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA
# INFILE) faster. This variable limits the size of the cache tree in
# bytes per thread. Setting it to 0 will disable this optimisation. Do
# not set it larger than “key_buffer_size” for optimal performance.
# This buffer is allocated when a bulk insert is detected.
bulk_insert_buffer_size = 64M

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
myisam_sort_buffer_size = 128M

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size = 10G

# If a table has more than one index, MyISAM can use more than one
# thread to repair them by sorting in parallel. This makes sense if you
# have multiple CPUs and plenty of memory.
myisam_repair_threads = 1

# Automatically check and repair not properly closed MyISAM tables.
myisam_recover

# *** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 16M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size = 2G

# InnoDB stores data in one or more data files forming the tablespace.
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well – please refer to the manual for more info
# about this.
innodb_data_file_path = ibdata1:10M:autoextend

# Set this option if you would like the InnoDB tablespace files to be
# stored in another location. By default this is the MySQL datadir.
#innodb_data_home_dir = <directory>

# Number of IO threads to use for async IO operations. This value is
# hardcoded to 8 on Unix, but on Windows disk I/O may benefit from a
# larger number.
innodb_write_io_threads = 8
innodb_read_io_threads = 8

# If you run into InnoDB tablespace corruption, setting this to a nonzero
# value will likely help you to dump your tables. Start from value 1 and
# increase it until you’re able to dump the table successfully.
#innodb_force_recovery=1

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 16

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 1

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size = 8M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size = 256M

# Total number of files in the log group. A value of 2-3 is usually good
# enough.
innodb_log_files_in_group = 3

# Location of the InnoDB log files. Default is the MySQL datadir. You
# may wish to point it to a dedicated hard drive or a RAID1 volume for
# improved performance
#innodb_log_group_home_dir

# Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
# If it is reached, InnoDB will start flushing them out agressively to
# not run out of clean pages at all. This is a soft limit, not
# guaranteed to be held.
innodb_max_dirty_pages_pct = 90

# The flush method InnoDB will use for Log. The tablespace always uses
# doublewrite flush logic. The default value is “fdatasync”, another
# option is “O_DSYNC”.
#innodb_flush_method=O_DSYNC

# How long an InnoDB transaction should wait for a lock to be granted
# before being rolled back. InnoDB automatically detects transaction
# deadlocks in its own lock table and rolls back the transaction. If you
# use the LOCK TABLES command, or other transaction-safe storage engines
# than InnoDB in the same transaction, then a deadlock may arise which
# InnoDB cannot notice. In cases like this the timeout is useful to
# resolve the situation.
innodb_lock_wait_timeout = 120
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192

12310
 
Copyright © 2008-2021 lanxinbase.com Rights Reserved. | 粤ICP备14086738号-3 |