常用mysql库

percona：https://www.percona.com/software/mysql-database/percona-server
mariadb：https://downloads.mariadb.org/
Oracle：https://dev.mysql.com/downloads/

常用SQL

# 条件批量更新
UPDATE categories SET
    display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)
# 查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)
# 删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId  having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)
# 查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)
# 删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

CRUD

CREATE DATABASE IF NOT EXISTS db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

备份与恢复

//备份
mysqldump -u{user} -p{Password} -B {db1} {db2} > sql_bak.sql
 -d, --no-data 只备份表结构
 -t, --no-create-info 只备份数据,不备份create table
 -n,--no-create-db 不备份create database，可被-A或-B覆盖
//恢复
mysql -u{user} -p{Password} -p < sql_bak.sql

docker start

chown 1001 /www/data/docker/mysql/data
docker pull percona/percona-server:5.7
docker run --name mysql -p 4306:3306 -v /www/data/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=Mydb2018 -d percona/percona-server:5.7

mysql

CREATE user zc IDENTIFIED BY ‘Zc2018mysql’; grant all privileges on . to ‘zc’@’%’ identified by ‘Zc2018mysql’; v5.7修改密码 update user set authentication_string=password(‘Mydb2018’) where user=’zc’; grant all privileges on . to ‘qizai’@’%’ identified by ‘Mydb2018’; 2

创建数据库

create database test default charset utf8mb4 collate utf8mb4_general_ci;

设置不验证密码复杂度

set global validate_password_policy=0;

授权

grant all privileges on test.* to ‘data’@’%’ identified by ‘mysql#password’;

创建用户

CREATE user local_rsync IDENTIFIED BY ‘Local#rsync2018’;

授权

GRANT CREATE,INSERT,SELECT,UPDATE ON intbee TO ‘local_rsync’@’10.0.1.%’ IDENTIFIED BY ‘Local#rsync2018’; FLUSH PRIVILEGES;

直接修改授权

select user,host,grant_priv from mysql.user; 查看授权 update mysql.user set grant_priv=’Y’ where user=’zc’; 更新授权

FLUSH PRIVILEGES;

查看授权

SHOW GRANTS FOR ‘root’@’10.0.1.%’;

mysql8.0

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'%' IDENTIFIED BY 'Mydb2019';
FLUSH PRIVILEGES;

关键配置参数

expire_logs_days=15        #binlog保留时间15天,修改后需要通过flush logs;才能删除旧文件
max_binlog_size=1G      #binlog大小

锁问题定位

查看数据库版本
select version();
查看表状态
show open tables from intbee;
分析行锁定命令
show status like '%lock%';
show status like 'innodb_row_lock%';
show variables like '%timeout%';
查看事务锁等待状态情况（优先使用，遇到表无法插入，由于之前的读线程事务没有提交，导致无法插入）
select * from information_schema.innodb_trx;
select from information_schema.innodb_locks;#正在锁的事务
select from information_schema.innodb_lock_waits;#等待锁的事务
查看锁日志详细（包含最近的死锁日志）
show engine innodb status \G;
查看表状态
show table status like 'table_name'\G;
show table status from db_name like 'table_name%'\G;
查询是否锁表
show OPEN TABLES where In_use > 0; 
数据库引擎
show variables like '%engine%';
事务隔离级别
select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
查看gap锁开启状态
show variables like 'innodb_locks_unsafe_for_binlog';
查看自增锁模式
show variables like 'innodb_autoinc_lock_mode';
分析sql日志
mysqlbinlog --start-datetime='2018-04-23 00:00:00' --stop-datetime='2018-04-24 00:00:00' /www/log/mysql/bin-file.00009* |grep 'table_name' -B 6 -A 6 >>table_name_bin.sql
参数解析
Innodb_row_lock_current_waits：当前等待锁的数量
Innodb_row_lock_time：系统启动到现在锁定的总时间长度(重要)
Innodb_row_lock_time_avg：每次等待所花平均时间(重要)
Innodb_row_lock_time_max：系统启动到现在 等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动到现在 总共等待的次数(重要)