三分钟搭建大数据sql开发平台

Read more   2019/12/31 posted in  JAVA 三分钟系列 商业智能 SQL

yearning docker部署注意事项

第一次启动时,yearning是无法访问的,因为第一次mysql需要初始化yearning的数据,可能yearning先于mysql启动了,导致读取数据出现异常

解决方法

  • 先启动mysql,通过查看日志,确认mysql初始化完成后在启动yearning

docker-compose.yml

version: '2'

services:
  db:
    image: mysql:5.7
    volumes:
      - ./docker/etc/mysql/:/etc/mysql/conf.d/
      - ./db_data/:/var/lib/mysql/
      - ./init-sql/:/docker-entrypoint-initdb.d/
    restart: always
    ports:
      - "3406:3306"
    environment:
      MYSQL_ROOT_PASSWORD: yearning
      MYSQL_DATABASE: Yearning
      MYSQL_USER: yearning
      MYSQL_PASSWORD: yearning
  yearning:
    image: registry.cn-hangzhou.aliyuncs.com/cookie/yearning:latest
    depends_on:
      - db
    ports:
      - "9180:8000"
    environment:
      HOST: localhost
      MYSQL_PASSWORD: yearning
      MYSQL_USER: root
      MYSQL_ADDR: db
  inception:
    image: hhyo/inception:latest
    depends_on:
      - db
    ports:
      - "6669:6669"
    volumes:
      - ./inc.cnf:/etc/inc.cnf

# 默认账号:admin,默认密码:Yearning_admin
# 感谢 eacdy 张功震 贡献

注意的点

  1. 必须先给用户赋予ddl权限,用户才可以提交ddl的工单
2018/5/20 posted in  SQL

inception安装

docker 安装, docker hub地址

https://hub.docker.com/r/hhyo/inception

github仓库地址

https://github.com/hhyo/inception

inception 配置文件

[inception]
general_log=1
general_log_file=inception.log
port=6669
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_remote_system_password=root
inception_remote_system_user=wzf1
inception_remote_backup_port=3306
inception_remote_backup_host=127.0.0.1
inception_support_charset=utf8,utf8mb4
inception_enable_nullable=0
inception_check_primary_key=1
inception_check_column_comment=1
inception_check_table_comment=1
inception_osc_on=OFF
inception_osc_bin_dir=/usr/bin
inception_osc_min_table_size=1
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1

中文文档:https://inception-document.readthedocs.io/zh_CN/latest/

2018/5/20 posted in  SQL

perocona

perocona toolkit
重复锁查询
杀慢查询

pt-query-digest 分析慢查询日志

image-20180610100427468

2018/5/20 posted in  SQL

把所有字段转成utf8mb4

use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "supply_chain2" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql  
FROM `TABLES` where table_schema like "supply_chain2" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "supply_chain2" and data_type in ('varchar');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "supply_chain2"
-- and data_type in ('text','tinytext','mediumtext','longtext');
2018/5/20 posted in  SQL

死锁

查看当前的数据库隔离级别

​ select @@global.tx_isolation

http://www.ywnds.com/?p=4949

​ 两个事务开始修改多个表时,如果访问表的顺序不同,会出现互相等待对方释放锁,然后才能继续处理的情况。MySQL会立刻发现这种情况并且终止较小的事务,允许其他的事务执行。

2018/5/20 posted in  SQL

mysql配置主从复制小记

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@`%` IDENTIFIED BY 'repl';
flush privileges;
# for master-slave architecture
log_bin=mysql-bin
server_id=10
SHOW MASTER STATUS\G
log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
CHANGE MASTER TO MASTER_HOST='mysql',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
SHOW SLAVE STATUS\G
START SLAVE;
replicate_wild_do_table = test.%
2018/5/20 posted in  SQL

使用xtrabackup进行mysql备份恢复

docker run --name=cpx_prod20180430023756 -v $PWD:/root/tools -e MYSQL_ROOT_PASSWORD=root -d registry.cn-hangzhou.aliyuncs.com/xiayu/mysql-5.6-xtrabackup:latest
innobackupex --apply-log /root/tools/mysql --user=root --password=root

innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /root/tools/mysql
mysqld_safe --defaults-file=/root/tools/mysql/backup-my.cnf --user=mysql --datadir=/root/tools/mysql/ &
innobackupex --defaults-file=/root/tools/mysql/backup-my.cnf --apply-log /root/tools/mysql
mysqld_safe --defaults-file=/etc/mysql/my.cnf --user=root --datadir=/var/lib/mysql &
2018/5/20 posted in  SQL

mysql 常用命令

set names utf8
source

select语句 into outfile '外部文件路径' fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
load data infile '外部文件路径' into table 表名 fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

只导出表结构
mysqldump -u 用户名 -p -d --add-drop-table 数据库名 > 导出的文件名

mysqldump  -uroot -p --databases test mysql #空格分隔
mysqldump  -uroot -p -all-databases
mysql -uroot -proot test < test.sql

查看锁等待

SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM
    information_schema.innodb_lock_waits w
        INNER JOIN
    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
        INNER JOIN
    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
2017/3/10 posted in  SQL

三分钟实现程序分库分表

2014/7/6 posted in  PHP SQL