用户工具

站点工具


linux:mysql

docker

优先考虑docker运行mysql或mariadb,utf8mb4.cnf

docker run --name mysql -p 3306:3306 -v /root/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=qweasdzxc123 -d mysql:5.7.16
docker run --name mysql57 -p 3306:3306 -v //c/Users/admin/utf8mb4.cnf:/etc/mysql/conf.d/utf8mb4.cnf -e MYSQL_ROOT_PASSWORD= -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql:5.7

mysqld_safe

mysqld_safe --defaults-file=./my.cnf --initialize,初始化data目录
mysqld_safe --defaults-file=./my.cnf &,后台运行服务
mysql -S ./mysql.sock -p,使用sock文件连接(-P3321指定端口的方式报错)
alter user 'root'@'localhost' identified by 'ZKR#mysql8'; 必须修改初始密码
# vi ./my.cnf
[client]
socket =/usr/local/mysql/mysql.sock
port=3321
[mysql]
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error = /usr/local/mysql/data/error.log
port=3321
user=mysql
tmpdir = /tmp
pid-file=/usr/local/mysql/mysqld.pid
#skip-grant-tables
skip-name-resolve
socket=/usr/local/mysql/mysqld.sock
character-set-server=utf8
lower_case_table_names=1
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

时区设置

select now();   //查看时间是否正常
select @@time_zone;  //默认是SYSTEM,东8区是+08:00
set global time_zone = '+8:00';
flush privileges;

mariadb

mariadb是mysql的免费开源分支,mysql已被oracle收购

yum -y install mariadb mariadb-server
systemctl status mariadb   #查询状态
systemctl start mariadb    #启动运行
systemctl enable mariadb   #开机自启
mysql_secure_installation  #设置root密码,删除测试用户和库

mysql community server

mysql社区版,参考版本:5.7.21,

rpm -Uvh mysql57-repo.rpm
yum install mysql-community-server,

更改密码

/var/log/mysqld.log,使用临时密码登陆(先复制密码文本到本机)
#日志内容如下:
[Note] A temporary password is generated for root@localhost: e_zx!pYZl9S#
set password=password('admin@mxchain.net'); #初次登陆必须修改密码

分配权限

GRANT ALL PRIVILEGES ON ifast.* TO 'ifast'@'%' IDENTIFIED BY 'ifast123';

转换编码

5.5.3+应总是使用utf8mb4,官方建议使用varchar替代char

utf8mb64:mysql5.5.3+,connector 5.1.13+,https://segmentfault.com/a/1190000000616820,
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name CHANGE column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

数据库配置

[client] 
default-character-set = utf8mb4
[mysql] 
default-character-set = utf8mb4
[mysqld] 
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
max_allowed_packet=256M
max_connections=1500
lower_case_table_names=1
[mysqldump]
quick
default-character-set=utf8mb4
max_allowed_packet=256M

连接数据库

mysqldump

jdbc.url=jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8&autoReconnect=true

导出数据

show variables like '%secure%';   # --secure-file-priv 导致不能导出
select * into outfile './ifast.csv' from sys_log;
mysqldump -t -T /tmp/ --fields-terminated-by=',' --fields-enclosed-by=\" -h127.0.0.1 -uroot -p ifast sys_log;
mysqldump -uroot -p ifast > ifast.sql
mysqldump my_app --where="1 limit 1000" > my_backup.sql,单库导出
mysqldump --databases my_app1 my_app2 --where="1 limit 1000" > my_backup.sql,多库导出,每张表前1千条数据
mysqldump --all-databases --where="1 limit 1000" > my_backup.sql,全部导出
select table_schema,sum(table_rows) total_rows,sum(data_length)/1024/1024 total_size_M,sum(index_length)/1024/1024 total_index_M from information_schema.tables group by table_schema;
select table_name,table_rows,data_length/1024/1024 data_size_M,index_length/1024/1024 index_size_M from information_schema.tables where table_schema='test' order by table_rows desc; 

定时备份

# crontab -e,
8 1,3,5,7-19,21,23 * * 1-5 sh /soft/backup/shells/backup_dbs.sh 2>&1 >> /var/log/backup.log
//事件调度
SELECT @@event_scheduler;
SET GLOBAL event_scheduler = ON;  //show processlist
//每秒插入一条记录,[IF NOT EXISTS]
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND 
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
//修改事件:ALTER EVENT e_test DISABLE; 或ENABLE
//删除事件:DROP EVENT [IF EXISTS] event_name
//每月1日执行存储过程
CREATE PROCEDURE `delLastMonth`()
BEGIN
DECLARE lastmonth int;
SET lastmonth = DATE_FORMAT(date_sub(curdate(),interval 1 MONTH),'%Y%m');
DELETE FROM wysytask.user_sign WHERE `month`=lastmonth;
DELETE FROM wysytask.user_resign WHERE `month`=lastmonth;
END
CREATE EVENT `delLastMonth` 
ON SCHEDULE EVERY 1 MONTH 
STARTS '2019-08-01 00:00:05' 
ON COMPLETION PRESERVE 
DO CALL delLastMonth

High Performance MySQL

ftp://203.157.240.9/pub/docs/High.Performance.MySQL.3rd.Edition.pdf
max[_user]_connections,最大连接数,show variables like '%_connections',单次查询控制在0.5秒以内,sql+app+io+render<3秒

启动报错

Attempted to open a previously opened tablespace,计算机管理-事件查看器-Windows日志MySQL,修改my.ini,innodb_force_recovery=1

CLIENT_PLUGIN_AUTH is required,新版的mysql driver不支持mysql 5.1版本

左连接

//限制右表使用on,限制左表使用where,重复数据使用group by或district
select * from t1 left join t2 
on t1.id=t2.t_id and t2.type=1
where t1.status=1

binlog

mysqlbinlog --no-defaults mysql-bin.000001 | less
show binary logs;
purge binary logs to 'binlog.000058'; # 删除此文件之前的日志,如果磁盘不足,可直接删文件

my.cnf

[mysql@grpznkfsapp038 mysql]$ cat /etc/my.cnf
[client]
user                            = root
password                        = "Mysql#123"
port                            = 3306
socket                          = /data/db/mysql/3306/mysql.sock
default-character-set           = utf8mb4
#prompt                          = \\u@\\h:\\d \\r:\\m:\\s >
#no-auto-rehash

[mysql]
port                            = 3306
socket                          = /data/db/mysql/3306/mysql.sock
default-character-set           = utf8mb4
prompt                          = \\u@\\h:\\d \\r:\\m:\\s >
no-auto-rehash

[mysqld]
default_storage_engine          = InnoDB
character_set_server            = utf8mb4
binlog_format                   = ROW
transaction_isolation           = READ-COMMITTED
max-allowed-packet              = 512M
max-connections                 = 3000
max-connect-errors              = 1000000

datadir                         = /data/db/mysql/3306/data
log_bin                         = /data/db/mysql/3306/binlogs/mysql-bin
relay-log                       = /data/db/mysql/3306/binlogs/relay-log
relay-log-index                 = /data/db/mysql/3306/binlogs/relay-log.index


innodb_buffer_pool_size         = 8G
innodb_log_file_size            = 1G
innodb_log_files_in_group       = 5
innodb_io_capacity              = 200
server-id                       = 207
port                            = 3306
innodb_data_file_path           = ibdata1:500M;ibdata2:500M:autoextend
innodb_temp_data_file_path      = ibtmp1:500M;ibtmp2:500M:autoextend:max:5120M
innodb_flush_method             = O_DIRECT
gtid_mode                       = ON
enforce_gtid_consistency        = ON
binlog_checksum                 = NONE
log_slave_updates               = 1
relay_log_info_repository       = TABLE
master_info_repository          = TABLE
lower_case_table_names          = 1
explicit_defaults_for_timestamp = 1
socket                          = /data/db/mysql/3306/mysql.sock
pid-file                        = /data/db/mysql/3306/mysql.pid
log_error                       = /data/db/mysql/3306/logs/error.log
slow_query_log_file             = /data/db/mysql/3306/logs/slow.log
tmpdir                          = /data/db/mysql/3306/tmp

default-time-zone               = '+8:00'
log_timestamps                  = SYSTEM
report_host                     = 'hostname'
binlog_expire_logs_seconds      = 604800

default_authentication_plugin   = mysql_native_password
slow-query-log                  = 1
long_query_time                 = 1

slave_parallel_type            = LOGICAL_CLOCK
slave_parallel_workers         = 4
#read_only                      = 1
#super_read_only                = 1

log_bin_trust_function_creators=1
sort_buffer_size = 1048576
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
linux/mysql.txt · 最后更改: 2022/06/02 09:13 由 admin