====== MySQL安装 ======

===== WSL中MySQL与设置 =====

参见：[[https://docs.microsoft.com/en-us/windows/wsl/tutorials/wsl-database#install-mysql|Get started with databases on Windows Subsystem for Linux]]

====== 常用操作 ======

===== 创建用户 =====

<code sql>
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';

</code>

===== 创建数据库 =====

<code sql>
CREATE DATABASE <database_name>;
-- 设置为utf8格式
CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

</code>

===== 用户授权 =====

<code sql>
-- 授予所有数据库的所有权限
GRANT ALL privileges ON *.* TO '<username>'@'%';
-- 授予单个数据库的所有权限
GRANT ALL privileges ON <database_name>.* TO '<username'@'%';

</code>

====== MySQL常用脚本 ======

===== 实时统计数据库表行数 =====

<code sql>
SHOW TABLE STATUS;

</code>

===== 数据脱敏（录入批量修改手机号码后四位） =====

<code sql>
UPDATE `member`
SET phone = REPLACE(phone, SUBSTRING(phone, -4), LPAD(FLOOR(RAND() * 10000), 4, '0'));
--replace函数用于做字符串替换
--substring函数用于获取字符串/字段部分内容
--lpad函数用于左侧自动补全
--floor函数用于向下取整
--rand函数用于生成随机数（生成结果是在0到1之间的小数）

</code>

===== 查看数据库大小 =====

<code sql>
USE information_schema;
--大小以MB为单位
SELECT TABLE_SCHEMA, SUM(DATA_LENGTH)/1024/1024 FROM TABLES GROUP BY TABLE_SCHEMA;

</code>

===== 生成随机姓名 =====

<code sql>
DELIMITER //
CREATE FUNCTION generateRandomName()
RETURNS VARCHAR(255) CHARSET utf8
BEGIN
    DECLARE xing VARCHAR(2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';
    DECLARE ming VARCHAR(2056) DEFAULT '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';

    DECLARE l_xing INT DEFAULT LENGTH(xing) / 3; # 这里的长度不是字符串的字数,而是此字符串的占的容量大小,一个汉字占3个字节
    DECLARE l_ming INT DEFAULT LENGTH(ming) / 3;

    DECLARE return_str VARCHAR(255) DEFAULT '';

    # 先选出姓
    SET return_str = CONCAT(return_str, SUBSTRING(xing, FLOOR(1 + RAND() * l_xing), 1));

    #再选出名
    SET return_str = CONCAT(return_str, SUBSTRING(ming, FLOOR(1 + RAND() * l_ming), 1));

    IF RAND()>0.400 THEN
    #再选出名
    SET return_str = CONCAT(return_str, SUBSTRING(ming, FLOOR(1 + RAND() * l_ming), 1));
    END IF;

    RETURN return_str;
END;//

DELIMITER ;

</code>

===== 删除重复数据 =====

参考资料：[[https://phoenixnap.com/kb/mysql-remove-duplicate-rows|How to Remove Duplicate Rows in MySQL {3 Ways to Delete}]]

<code sql>
DELETE t1 FROM dates t1
INNER  JOIN dates t2
WHERE
    t1.id < t2.id AND
    t1.day = t2.day AND
    t1.month = t2.month AND
    t1.year = t2.year;

</code>

====== MySQL备份与恢复 ======

[[http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/|参考资料]]

===== 备份 =====

<code bash>
#如果需要create database语句，增加参数--databases
#如果需要drop database语句，增加参数--add-drop-database
mysqldump -u <username> -p[password] <database_name> > dumpfilename.sql

</code>

===== 恢复 =====

<code bash>
mysql -u <username> -p[password] <database_name> < dumpfilename.sql

</code>

===== 备份与恢复示例 =====

假设需要备份与还原的数据库名称为test_db，用户名为test，密码为123456

  * 打开Windows命令行或者Linux/Mac终端
  * 在命令行输入如下命令备份数据库

<code bash>
mysqldump -u test -p 123456 test_db > test_db.bak.sql

</code>

  * 需要恢复数据库的机器上先重建老数据库（如果没有老数据库跳过该步骤）

<code bash>
mysql -u test -p 123456> drop database test_db;
> create database test_db default character set utf8 default collate utf8_general_ci;

</code>

  * 使用mysql命令行恢复数据库

<code bash>
mysql -u test -p 123456 test_db < test_db.bak.sql

</code>

====== 常见问题与解决方案 ======

===== 远程连接数据库提示111 Connection refused. =====

需要修改mysql配置文件，注释掉bind-server=127.0.0.1启用远程连接。Ubuntu下，MySQL配置文件路径''<nowiki>/etc/mysql/mysql.conf.d/mysqld.cnf</nowiki>'' ，MariaDB配置文件路径''<nowiki>/etc/mysql/mariadb.conf.d/50-server.cnf</nowiki>''

===== 本地登录时加127.0.0.1才能登录而不能用localhost =====

直接通过localhost登录或者加-h localhost，提示Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (13)，需要在mysql配置文件加上对客户端的参数配置，例如Ubuntu系统中的路径为/etc/mysql/conf.d/mysql.cnf，增加以下内容

<code>
[client]
host=127.0.0.1

</code>

====== InnoDB Failing to start / MySQL not loading plugins ======

<code>
rm -rf /var/lib/mysql/*; mysql_install_db -u mysql; systemctl start mariadb.service

</code>