MySQL

最流行的开源关系型数据库,常用的MySQL版本为社区版或Percona版.

另一篇记录:MySQL性能优化

安装

Ubuntu

# 安装MySQL8
wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
sudo apt update 
sudo apt-get install mysql-server
sudo systemctl start mysql
# 默认密码为空
sudo mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

# mysql-workbench
sudo snap install mysql-workbench-community
sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service
# 用snap安装的连接失败问题参考 https://blockdev.io/mysql-workbench-ubuntu-20-04-and-app-armor/

# 安装5.6
sudo apt install mysql-server


# 设置root密码, Percona
/usr/bin/mysqladmin -u root password 'new-password'
# 配置文件
/etc/mysql/my.cnf

/etc/default/mysql

CentOS安装

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum localinstall mysql80-community-release-el7-3.noarch.rpm
yum repolist all | grep mysql
yum install yum-utils
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
yum install mysql-community-server
systemctl start mysqld.service
systemctl status mysqld.service
# 临时密码
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
# Mysql 5
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpass';  

warning: /var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-libs-compat-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# CentOS7 install mysql client
sudo yum install mysql

配置

修改root密码

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'new-pass';
flush privileges;

授权登录

错误消息: ERROR 1045 (28000): Access denied for user 'root'@'x.x.x.x' (using password: YES)

# 允许远程登录等
sudo mysql_secure_installation
select user,authentication_string,plugin,host from mysql.user;
alter user 'root'@'%' identified with mysql_native_password by 'your_root_password';

MySQL8有变化,不允许用GRANT隐式创建用户. 否则报错: ERROR 1410 (42000): You are not allowed to create a user with GRANT

CREATE USER 'xulz'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'xulz'@'%' WITH GRANT OPTION;
flush privileges;

MySQL5用法:

GRANT ALL PRIVILEGES ON dbname.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';

通用语法:

# 要求至少8位,含大小写数字和符号
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

FLUSH PRIVILEGES;
  • grant与on之间是各种权限,例如:insert,select,update等
  • on之后是数据库名和表名,第一个_表示所有的数据库,第二个_表示所有的表
  • @后可以跟域名或IP地址(%代表所有地址),identified by后面的是登录用的密码,可以省略,即缺省密码或者叫空密码

MySQL 8.0 caching_sha2_password Auth failed

# 可以修改默认配置
[mysqld]
default_authentication_plugin=caching_sha2_password

# 或使用原来的认证方式
CREATE USER 'nativeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

常用命令

CREATE USER 'xulz'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'xulz'@'%' WITH GRANT OPTION;
select current_user();
-- 授予权限需要以 root@localhost 登录
-- 创建数据库并授权
CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;


-- 创建相同结构
CREATE TABLE new_table LIKE old_table;     
-- 同时复制数据
INSERT INTO new_table SELECT * FROM old_table; 
-- 显示所有索引
SHOW INDEX FROM yourtable;
# 测试连接或权限问题
mysql -u<username> -p<password> -h<ip> -D<database> -e "select * from <表名>"

字符集

初始安装后默认字符集使用latin1,对于中文字符需求,一定要修改配置文件:

vi /etc/my.cnf

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

# 需要重启服务生效
systemctl restart mysqld
-- 字符集
show variables like "%char%";

CREATE DATABASE IF NOT EXISTS `gitlabhq_production` DEFAULT CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`;
show variables like "character_set_database";
show variables like "collation_database";

-- 更新排序字符集
-- 不要使用已过时的 _general_ci
ALTER DATABASE yourDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
-- mb4 支持4字节字符
-- ai : accent-insensitive
-- ci : case-insensitive sorting and comparison

信息查询

-- 显示基础信息
mysql > status

-- 查询各数据库大小
SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables GROUP BY table_schema;

-- 查询每个表的行数,注:对innodb这个是估算值
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'your_db';


-- 查询每个表大小
SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES WHERE table_schema = "your_db";

-- 所有用户信息
select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;

-- 查询所有表名
select table_name from information_schema.tables where table_schema='test';

聚合

  • RowNumber
  • 分析函数、窗口函数(window function)
  • GROUP_CONTACT

备份/导出数据

# 备份数据
mysqldump -d -uroot -p mydb >mydb_schema.sql
--no-create-info, -t   # 只包含数据
--skip-triggers
--no-data, -d   

# 备份多个数据库
--databases db1 db2 > db12.sql
    
# 备份全部数据库
mysqldump -u xulz  -p --all-databases > all_db.sql

其他选项:

| gzip > database_name.sql.gz
$(date +%Y%m%d)

执行脚本:

#!/bin/bash

USER="xulz"
PASSWORD=""
#OUTPUT="/Users/xulz/DBs"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
echo "Dumping database: $db"
mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
# gzip $OUTPUT/`date +%Y%m%d`.$db.sql
fi
done

恢复/导入数据

# 导入数据
mysql -h host -u username -p password --default_character_set utf8 database < file.sql


mysqld database_name < file.sql
# Load-data高速导入数据
load data local infile /root/out.txt into table table1 fields terminated by ',’ (field1,field2)

# 恢复全部数据库
mysql -u xulz -p
mysql> source all_db.sql

# 或者
mysql -u xulz -p < all_db.sql

重置丢失的root密码

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
# 增加
[mysqld] 
skip-grant-tables 

# 重启服务
sudo systemctl restart mysql.service 
mysql -u root
flush privileges;
use mysql;
# Mysql 8
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpass';  

# Mysql 5
update user set password=PASSWORD("newpass") where User='root';
flush privileges;
# 去掉选项
# [mysqld] 
# skip-grant-tables 

sudo systemctl restart mysql.service

mysql-python的使用

# Ubuntu下执行 pip3 install mysqlclient遇到错误 `OSError: mysql_config not found`
# mysql-python 需要的mysql_config 包含在 libmysqlclient-dev 或 libmysqld-dev
sudo apt-get install libmysqlclient-dev 


# Mac: mysql_config: command not found
brew install mysql-client

性能

慢查询

-- 配置
show variables like '%slow_query%';
SET GLOBAL log_output = 'FILE,TABLE';
select * from mysql.slow_log;
-- 或者
mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
mysql> SET SESSION long_query_time = 1;  -- 配置文件: long_query_time = 1
mysql> SET SESSION min_examined_row_limit = 100;

工具

  • 自带工具: mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log
  • 第三方:pt-query-digest

其他命令

select @@global.max_connections;
show variables like max_connections;
SELECT * FROM   information_schema.STATISTICS WHERE  TABLE_SCHEMA = DATABASE()

常见问题

Error Code: 1273. Unknown collation: ‘utf8mb4_0900_ai_ci’

原因:通常发生在把8.0的sql导入到低版本(如5.6)时 解决方法:

  1. 升级MySQL Server到8.0
  2. 或者把sql里的 utf8mb4_0900_ai_ci 改为 utf8_unicode_ci

集群及高可用

Percona XtraDB Cluster

推荐至少3节点,每个节点包含全部数据的复制.主要作为读操作的扩展方案,写操作不会成倍scale.实现基于单实例并加入了写复制(同步).

限制:复制只支持InnoDB存储引擎, 系统表复制只支持DDL.写吞吐量由最差的节点决定.不支持Lock查询

用Connector/J实现负载均衡

Failover协议是“Multi-Host”链接模式中最基础的协议,“load balancing”、“replication”、“farbic”协议都基于Failover协议。

中间件

实现透明访问分布式数据库集群中的各个分库分表

一些工具

资源