sueboy: mysql

顯示具有 mysql 標籤的文章。顯示所有文章

SeckillSolution 秒殺高并發交易

https://github.com/qianshou/SeckillSolution
https://blog.csdn.net/koastal/article/details/78995885
=====
https://blog.csdn.net/qq_38512763/article/details/118830903

mysql virtual colume alert


ALTER TABLE `user` 
  ADD `last name` VARCHAR(50)
   AS (
        case `name`
        when admin then 0
        else CONCAT(`name`, 'user')
         end
      ) 
  ;

sqlmap

docker https://hub.docker.com/r/googlesky/sqlmap

執行指令：


docker run --rm -it -v /tmp/sqlmap:/root/.sqlmap/ googlesky/sqlmap -h

GET


docker run --rm -it -v /tmp/sqlmap:/root/.sqlmap/ googlesky/sqlmap --url='https://test.com/date=2020-04-01' --level=5 --risk=3

POST & header token


docker run --rm -it -v /tmp/sqlmap:/root/.sqlmap/ googlesky/sqlmap --url='http://oo.xx.oo.xx:5000/user/info' --headers='Authorization: bearer eyJhbGcoooooxxxxxoooooxx......' --data='{id: "u123"}' --level=5 --risk=3

ethereum-etl to csv to filebeat to kafka to mysql

filbeat to kafka

https://www.elastic.co/guide/en/beats/filebeat/current/kafka-output.html

sink-connector to mysql

https://docs.confluent.io/current/connect/kafka-connect-jdbc/sink-connector/index.html

mysql和mongoDB

https://github.com/astaxie/build-web-application-with-golang/blob/master/zh/05.6.md

partition by range innodb discard tablespace

https://bobcares.com/blog/database-crash-rescue-how-we-re-built-an-innodb-mysql-database-when-ibdata1-file-was-corrupted/

https://www.percona.com/blog/2014/10/14/recover-orphaned-innodb-partition-tablespaces-in-mysql/

ooo

apt-get install mysql-utilities

查table statement
mysqlfrm --diagnostic xxxxoooo.frm > t.sql

remove t.sql not sql statme

mysql -u root -pxxxooo database < t.sql

percona stand my.cnf

https://tools.percona.com/wizard/

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name server generated for at 2017-04-26 15:24:40

[mysql]

# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G

# LOGGING #
log_bin = /var/log/mysql/mysql.log
log-error = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log

install mariadb finish give root all connect and grant

create user 'root'@'%' identified by 'password';

grant ALL PRIVILEGES on *.* to 'root'@'%' with grant option;

flush privileges;

if error have root@localhost

delete from mysql.user where user='root';
flush privileges;
create user 'root'@'%' identified by 'password';
grant ALL PRIVILEGES on *.* to 'root'@'%' with grant option;

select user, host from user;
show grants for 'root'@'%';

FLUSH PRIVILEGES;

=====restore root privileges=====

http://stackoverflow.com/questions/1709078/how-can-i-restore-the-mysql-root-user-s-full-privileges

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;

create user 'root'@'localhost' identified by 'password';

create user 'root'@'%' identified by 'password';

GRANT ALL ON *.* TO 'root'@'localhost';

GRANT ALL ON *.* TO 'root'@'%';

optimizing mysql for high concurrency on amazon

http://tech.noredink.com/post/133347435578/optimizing-mysql-for-high-concurrency-on-amazon

Looking for concurrency bottlenecks

performance schema

List the total maximum wait by event inside of InnoDB

SELECT EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC LIMIT 30;

List current and last wait events on InnoDB.

-- "what the current, or last completed, wait for each session was, and for exactly how long they waited" SELECT NAME, IF(PPS.THREAD_ID = 0, 'Internal Thread', CONCAT(IPS.USER, '@', IPS.HOST)) USER, DB, COMMAND, STATE, TIME, EVENT_NAME LAST_WAIT, IF(TIMER_WAIT IS NULL , 'Still Waiting', TIMER_WAIT/1000000000) LAST_WAIT_MS FROM performance_schema.events_waits_current JOIN performance_schema.threads PPS USING (THREAD_ID) LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST IPS ON IPS.ID = PPS.THREAD_ID;

Disabling the query cache
Increasing InnoDB thread concurrency
Increasing InnoDB open tables

database performance aws vs bare metal

https://medium.com/devoops-and-universe/database-performance-aws-vs-bare-metal-452f64481b07

mariadb Performance Schema

https://mariadb.com/kb/en/mariadb/performance-schema-overview/

MySQLTuner

https://github.com/major/MySQLTuner-perl

mysql heidisql

http://www.heidisql.com/

mysql 備份

http://codego.net/10343891/

1.逻辑备份（mysqldump的，由脚本）锁定数据库。这将破坏客户端端端操作。我的方法备份关键任务数据库InnoDB的OpenSolaris上，并采取DATADIR和日志目录的日常ZFS快照。这些快照，然后复制到离线服务器。因为InnoDB的是事务性和快照是atomicity，也没有必要关闭在创建快照之前的服务器（回收来自它就像复苏的表单突然停电：InnoDB支持它）。

[轉]mysql 備份還原

https://read01.com/PxLz.html

[轉]Mysql 集成随机唯一id mysql unique number generation

http://justcode.ikeepstudying.com/2015/05/mysql-%E9%9B%86%E6%88%90%E9%9A%8F%E6%9C%BA%E5%94%AF%E4%B8%80id-mysql-unique-number-generation/

SELECT FLOOR(10000 + RAND() * 89999) AS random_number
FROM table1
WHERE "random_number" NOT IN (SELECT unique_id FROM table2)
LIMIT 1

function get_unique_username($chance=10,$show_sql=FALSE)
{
global $conn;

// connect db
if(!$conn) connect();

$output = FALSE;
$rand = rand(1,$chance);

$sql = 'SELECT FLOOR('.($rand==5?'10000 + RAND() * 89999':'100000 + RAND() * 899999').') AS random_number FROM gm_users WHERE "random_number" NOT IN (SELECT unique_id FROM gm_ids) LIMIT 1';
if($show_sql) echo $sql;

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);

if($row['random_number'])
{
$output = $row['random_number'];
// insert this random_number to table gm_ids
insert('gm_ids', array('unique_id'=>$output));
}

return $output;
}

=====
http://www.debugease.com/mysql/278572.html

无法实现！变通的办法，还是直接使用 auto_increment ，然后前面加几个随机数。如果一定要做到，则可以创建一个 8 位数字的表 1,2,3,...,99999999

然后每次任意出其中一个 select * from x order by rand() limit 1取出该数据后删除

=====
http://www.yunii.com/71257.htm

4.使用mysql的 UUID()函數。前面的自增字段（auto_increment）只能生成”表內”的唯一值，且需要搭配使其爲”唯一的主鍵或唯一索引”，它的值是逐步增長的。這裏的UUID産生的是字符串類型值，固定長度爲:36個字符。UUID生成的是在時間、空間上都獨一無二的值，是“隨機+規則”組合而成。

可以看到，多次調用UUID()函數得到的值不相同，它由五部分組成，並且有連字符（-）隔開，一共36個字符。其中：

前3組值是時間戳換算過來的，解決“時間上唯一”；

第4組值是暫時性保持時間戳的唯一性，重啓mysql才會變動；

第5組是mac值轉過來的，有助于解決“空間上的唯一”，同一個機器多實例的一般相同。如果mac值獲取不到，則是一個隨機值。

這些已經可以保證得到的值在時間和空間上的唯一。當然你也可以去掉連字符: select replace(uuid(),'-','')。

訂閱：文章 (Atom)

EExcel 丞燕快速查詢2