MYSQL

设计目标 存取（入库和分析）高效 节省存储空间 评估单台设备基于采集评率的每年存储成本 http://mysql.rjweb.org/doc.php/datawarehouse

InnoDB MySQL 5.1 shipping the older version of InnoDB, If you’re using MySQL 5.1, please ensure that you’re using the InnoDB plugin. It’s much better than the older version of InnoDB. It now scales well to 24 CPU cores, and arguably up to 32 or even more cores depending on the scenario

为什么要使用OIDC 只使用基本OAUTH2认证是不安全因为无法保证与用户的访问令牌是真实的。使用 OIDC可以做到这一点 https://security.stackexchange.com/questions/37818/why-use-openid-connect-instead-of-plain-oauth2/260519#260519 hasRole a role is just an authority with a special ROLE_ prefix. So in Spring security 3 @PreAuthorize(“hasRole(‘ROLE_XYZ’)“) is the same as @PreAuthorize(“hasAuthority(‘ROLE_XYZ’)“) and in Spring security 4 @PreAuthorize(“hasRole(‘XYZ’)”) is the same as @PreAuthorize(“hasAuthority(‘ROLE_XYZ’)“). http://localhost:8080/oauth/token curl -u eagleeye:thisissecret -i -H ‘Accept:application/json’ -d “grant_type=password&scope=webclient&username=will&password=pass” -H “Content-Type: application/x-www-form-urlencoded” -X POST http://localhost:8080/oauth/token access protected resource

cp data file cp /var/lib/mysql /data -Rf chown -R mysql:mysql /data/mysql AppArmor /etc/apparmor.d/local/usr.sbin.mysqld /data/mysql r, /data/mysql/** rwk, sudo systemctl reload apparmor sudo as myql sudo -s -u mysql mysql 时间类型支持微秒 MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) Mysql DATETIME(6) DATETIME[(fsp)] The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part.

mysqladmin status MySQL is multithreaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors.

innodb-memcached-multiple-get-range-query native partitioning in-place APIs

install curl -O http://download.redis.io/redis-stable.tar.gz tar xzvf redis-stable.tar.gz cd redis-stable make make test sudo make install config sudo mkdir /etc/redis sudo cp redis-stable/redis.conf /etc/redis sudo adduser --system --group --no-create-home redis sudo mkdir /var/lib/redis sudo chown redis:redis /var/lib/redis sudo chmod 770 /var/lib/redis /etc/redis/redis.conf supervised systemd dir /var/lib/redis # bind localhost start redis-server /etc/redis/redis.conf shutdown redis-cli shutdown redis-cli 127.0.0.1:6379> shutdown run in docker docker run --name redis -network host -v /var/lib/redis:/data /etc/redis/redis.

[mysqld] server-id = 2 relay-log-index = slave-relay-bin.index relay-log = slave-relay-bin mysql>CHANGE MASTER TO MASTER_HOST = 'db2',MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = 'xyzzy'; Connecting the Master mysql> START SLAVE;

Server and Operating System Kernel – vm.swappiness Disables swapping completely while 1 causes the kernel to perform the minimum amount of swapping # Set the swappiness value as root echo 1 > /proc/sys/vm/swappiness # Alternatively, using sysctl sysctl -w vm.swappiness=1 # Verify the change cat /proc/sys/vm/swappiness 1 # Alternatively, using sysctl sysctl vm.swappiness vm.swappiness = 1 Filesystems – XFS/ext4/ZFS FILE SIZE mount option EXT4 16TB noatime,data=writeback,barrier=0,nobh,errors=remount-ro XFS 8EiB defaults,nobarrier Disk Subsystem – I/O scheduler Most modern Linux distributions come with noop or deadline I/O schedulers by default, both providing better performance than the cfq and anticipatory ones

mysql 8 测试环境快速搭建(WSL/root远程访问) sudo apt install -y mysql-server mysql --version sudo mysql mysql>CREATE USER 'root'@'%' IDENTIFIED BY '123'; mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; mysql>flush PRIVILEGES mysql.conf.d/mysqld.cnf :32:bind-address = 0.0.0.0 sudo service mysql restart Create the root user (yes, a new user because what exists is ‘root@localhost’ which is local access only) root用户本地登录 本地登录使用系统认证(auth_socket) sudo mysql root用户远程登录 mysql -u root -p'123' -h 192.168.168.128 mysql> select user,host,plugin from mysql.

mysql install ubuntu 16.04 install mysql 5.7 at default sudo apt-get update sudo apt-get install mysql-server Enable root remote connection mysql -u root -p mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION; mysql>FLUSH PRIVILEGES; SHOW current setting mysql> SHOW VARIABLES WHERE Variable_name LIKE 'innodb%'; MySQL 5.7 has significantly better default values. the following variables are set by default: [mysqld] innodb_buffer_pool_instances=8 innodb_flush_method=O_DIRECT setting of mysql 5.

whether MySQL Server supports partitioning mysql -u root -p123456 -e "SHOW PLUGINS;" |grep partition

show the last queries mysql>SET GLOBAL log_output = 'FILE'; mysql>SET GLOBAL general_log = 'ON'; mysql>SHOW VARIABLES WHERE Variable_name LIKE 'general_log_file' Disable ONLY_FULL_GROUP_BY SHOW VARIABLES WHERE Variable_name LIKE 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | [mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" mysql> SHOW CREATE TABLE <tablename>;

[mysqld] log-bin = master-bin log-bin-index = master-bin.index server-id = 1 Grant the user to retrieve the binary log from the master mysql>CREATE USER repl_user; GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'xyzzy';

查看表状态 show table status FROM redis_db like 'point_value'; +-------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | point_value | InnoDB | 10 | Dynamic | 316755485 | 143 | 45420118016 | 0 | 0 | 3145728 | NULL | 2022-01-30 18:55:44 | 2022-02-06 18:17:56 | NULL | utf8_general_ci | NULL | | | +-------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.

key_buffer_size the size of the index buffers held in memory, which affects the speed of index reads recommend: 25% or more of the available server memory A good way to determine whether to adjust the value is to compare the key_read_requests value, which is the total value of requests to read an index, and the key_reads values, the total number of requests that had to be read from disk.

select myql 5.7 wget wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb dpkg -i mysql-apt-config_0.8.12-1_all.deb turns off the GPG check sources.list.d/mysql.list deb [trusted=yes] http://repo.mysql.com/apt/ubuntu/ bionic mysql-5.7 install mysql and create admin user sudo apt update apt-cache policy mysql-server | grep 5.7 sudo apt install mysql-client=5.7.37-1ubuntu18.04 mysql-community-server=5.7.37-1ubuntu18.04 mysql -u root -p GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION