MySQL-Programs 程序

mysql, mysqldump 等命令行工具

Chapter 4 MySQL Programs
https://dev.mysql.com/doc/refman/5.7/en/programs.html

---

mysql命令

4.5.1 mysql — The MySQL Command-Line Client
https://dev.mysql.com/doc/refman/5.7/en/mysql.html

mysql 客户端命令：
mysql [-h host] [-P port] -u {user} -p{password} {database}
-h, --host=name 要连接的远程主机名或ip地址，默认是 localhost
-P, --port=# 大写P表示数据库端口号，默认是 3306
-u, --user=name 用户名
-p, --password[=name] 小写p表示密码，如果不提供-p密码项，会在交互命令行中提示输入
-D, --database=name 指定登录后的数据库名，例如 -D user
-N, --skip-column-names 输出结果不打印列名
-e, --execute=name 在 MySQL 外部执行 SQL

mysql -hlocalhost -P3307 -uroot -p

注意
1、-h, -P, -u 参数前可以有空格也可以没有空格，但是如果 -p 后带有用户密码，那么 -p 与密码之间必须没有空格，否则让你重新输入密码
2、命令最后如果加数据库名，则登录后直接切换为命令中的数据库，否则登录后的当前数据库为 none, 如果最后加 db 名没有自动切换，用 -D 显示指定下。

例1、不显示指定密码
mysql -h mysql.myhost.com -u root -p 回车后输入密码

例2、-p后直接跟密码登录
mysql -h mysql.myhost.com -u root -p123456 -D database

例3、mysql命令的选项和值是可以挨在一起的
mysql -h127.0.0.1 -P3306 -uroot -p123456 -Ddatabase

例4、如果mysql密码中有特殊字符，可以将密码用单引号引起来
mysql -hlocalhost -P5535 -uroot -p'O6~4myloveXdodjf)S(+4*yjQ&8dfdsf' db

例5、MySQL安装完成后会自动提供一个不带密码的root用户，使用root账号登录mysql：
mysql -u root

---

-hlocalhost连接报错

现象：
mysql -hlocalhost -P8306 -uroot -pxxx 连接报错

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

但改为 mysql -h127.0.0.1 -P8306 -uroot -pxxx 可成功连接

原因：
首先要知道 mysql -hlocalhost 和 -h127.0.0.1 的区别
mysql -h 127.0.0.1 的时候，使用 TCP/IP 连接
mysql -h localhost 的时候，不使用 TCP/IP 连接，而使用Unix Domain Socket
所以，使用 -hlocalhost 连接报错的原因就是找不到 socket 文件，可能原因是 docker 中的 mysql socket 文件和宿主机上不同，且文件没有映射。

解决：
使用 -h127.0.0.1 连接就行

---

mysql -e 命令行执行SQL

mysql -hlocalhost -P5535 -uroot -p123 -e 'show tables;' 不登录 MySQL 直接在外部用命令行执行 SQL, 常用于 shell 脚本中操作数据库。
加 -N 参数可以去掉列头，方便 shell 中直接获取 SQL 结果。

mysql -e "show tables;"
+--------------------+
| Tables_in_commerce |
+--------------------+
| corder             |
| customer           |
| product            |
+--------------------+

mysql -N -e "show tables;"
+----------+
|   corder |
| customer |
|  product |
+----------+

---

mysqldump 数据库(表)导入导出

4.5.4 mysqldump — A Database Backup Program
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

远程数据库（表）导出到本地数据库（表）文件

默认不带参数的导出，导出文本内容大概如下：创建数据库判断语句-删除表-创建表-锁表-禁用索引-插入数据-启用索引-解锁表

有3种方式来调用 mysqldump：
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] —database DB1 [DB2 DB3…]
shell> mysqldump [options] –all–database
如果没有指定任何表或使用了 —database 或 –all–database 选项，则转储整个数据库。

常用选项：
-S, --socket=name 指定连接使用的sock文件

--set-gtid-purged=OFF 选项，导出的脚本中没有 SET @@GLOBAL.GTID_PURGED

--opt 该选项是速记；等同于指定 --add-drop-tables--add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset 它可以给出很快的转储操作并产生一个可以很快装入 MySQL 服务器的转储文件。该选项默认开启，但可以用 --skip-opt 禁用。要想只禁用确信用-opt启用的选项，使用–skip形式；例如 --skip-add-drop-tables 或 --skip-quick
--compress，-C 压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。
--lock-tables，-l 开始转储前锁定所有表。

-t 不重新建表

--no-create-info, -t 不增加 create table 建表和 drop table 语句。当目的数据库中已经创建好表结构时，用此选项。

-c insert语句带列名

--complete-insert, -c insert 语句中增加具体的列名。这样对目的表结构不同原表，情况下更有用。

导出数据库/表

导出的其实是一个 .sql 脚本，直接记事本就可以打开，里面有建库建表语句， insert 数据语句
1、导出数据库

-- 将192.168.1.1主机上的mydb数据库导出到本地的mydb.bak文件中
mysqldump -h192.168.1.1 -P3306 -uroot -p123456 --databases mydb > mydb.bak;
mysqldump -h192.168.1.1 -P3306 -uroot -p123456 mydb > mydb.bak;

-- 将本地mysql服务器上的mydb1数据库导出到本地的mydb1.bak文件中
mysqldump  -uroot -p123456 --databases mydb1 > mydb1.bak;

2、导出数据表

-- 将192.168.1.1主机上的mydb数据库的tb1数据表导出到本地的tb1.bak文件中
mysqldump -h192.168.1.1 -P3306 -uroot -p123456 -t -c --set-gtid-purged=OFF mydb tb1 > tb1.bak;

-- 将本地主机上的mydb1数据库的tb2数据表导出到本地的tb2.bak文件中
mysqldump  -uroot -p123456 mydb1 tb2 > tb2.bak;

导出为多条insert语句

默认 mysqldump 会将多条插入语句导出成一条多个 value 的 insert 语句
在mysqldump时加上参数 --skip-extended-insert 即可导出为多条 insert 语句
--extended-insert, -e 表示使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用 --skip-extended-insert 取消选项。

mysqldump -h localhost -u user -ppsswd --skip-extended-insert blog comment > blog.comment.sql

导入数据库/表

（1）导入数据库
在本地数据库中创建与导出数据库mydb 同名 的数据库：

create database mydb;

注意：如果数据库中已有同名表，表结构和会被覆盖，可以直接打开导出的备份sql，删除 create table 语句。

然后退出数据库，再使用以下的 命令导入数据库文件 mydb.bak 到本地数据库 mydb 中：

mysql -uroot -p123456 -h192.168.1.1 -P3306 mydb < mydb.bak
mysql -uroot -p123456 mydb < /root/data/mydb.bak;

或者进入mysql中，使用sourc指令完成数据库导入，如下：

mysql> source  /root/data/mydb.bak;

/root/data/mydb.bak 是远程数据库mydb导出文件的本地存放位置

（2）导入数据表
在本地数据库中创建相对应导出的数据库mydb同名的数据库：
mysql> create database mydb;
切换到 mydb 数据库
use mydb;

然后在mysql中使用source指令来完成数据表的导入，如下：
mysql> source /root/data/tb1.bak;
/root/data/tb1.bak是远程数据表tb1导出文件的本地存放位置

MySQL数据库（表）的导入导出（备份和还原）
https://blog.csdn.net/Deutschester/article/details/6866842

导入报错 @@GLOBAL.GTID_EXECUTED

导入报错：
ERROR 1840 (HY000) at line 34: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决：
方法1、删掉 sql 文件中的 SET @@GLOBAL.GTID_PURGED 命令

SET @@GLOBAL.GTID_PURGED='195b2bea-02dd-43a9-8fbd-31c01219b9ce:1-46755421,
34e4afb2-bbd1-48d4-881c-42d2a47e6a3b:1-6270,
b778d073-fe32-4e6a-8f3b-36c22eff7433:1-761812,
f9721209-ae78-4c95-aca6-6e2405ad0137:1-25429';

方法2、目标数据库上执行 reset master 清空本地的 gtid

方法3，mysqldump 导出时加 --set-gtid-purged=OFF 选项，导出的脚本中没有 SET @@GLOBAL.GTID_PURGED

windows上导出文件无法导入

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:
shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.4, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the –result-file option, which creates the output in ASCII format:
shell> mysqldump [options] --result-file=dump.sql

---

mysqladmin

---

存储过程

查询存储过程

show procedure status like '%xx%';
或者
select * from information_schema.Routines where ROUTINE_NAME=’CountProc1’;

查看存储过程的代码

show create procedure proc_name;

计算多表count之和

t_pic_record_{0-99} 是 100 个分表，统计所有分表中 groupid=in_group_id 的数据个数之和。

DROP PROCEDURE IF EXISTS count_t_pic_record;
delimiter $
create procedure count_t_pic_record(in_group_id varchar(64))
begin
    declare i int;
    set i=0;
    set sum=0;
    while i < 99 do
        set cnt=0;
        select count(*) into cnt from concat('t_pic_record_',i) where groupid=in_group_id;
        sum = sum + cnt;
    end while;
    select sum;
end $

while循环批量插入数据

创建一个存储过程，while 循环插入 100 万 person 数据，同时给每个人打 3 个标签插入 person_tag 关联表

DROP PROCEDURE IF EXISTS batch_insert_person;
delimiter $
create procedure batch_insert_person()
begin
    declare num int;
    set num=1;
    while num < 1000000 do
        -- 随机姓名 可根据需要增加/减少样本
        set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
        set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
        -- length(@surname)/3 是因为中文字符占用3个长度
        set @FULL_NAME = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
        set @id_no = CONCAT('1',CEILING(RAND()*9000000000+1000000000));
        set @rand_conf = convert(rand() * 100, decimal(10,2));
        set @arch_id = CEILING(RAND()*9000000000+1000000000);
        set @person_no = substring(MD5(RAND()),1,15);
        set @arch_cover = substring(MD5(RAND()),1,24);
        set @rand_tag_id = floor(rand() * 27);
        insert into person(archive_cover, archive_id, confidence, id_card, id_photo_address, last_modified_date, manual_process, person_number, real_info_modified_date, real_name)
            values(@arch_cover, @arch_id, @rand_conf, @id_no,'http://image.masikkk.com/idcard', now(),0, @person_no, now(),@FULL_NAME);
        insert into person_tag(person_id, tag_id) values
            (LAST_INSERT_ID(), @rand_tag_id), (LAST_INSERT_ID(), @rand_tag_id+1), (last_insert_id(), @rand_tag_id-1);
        set num=num+1;
    end while;
end $
call batch_insert_realinfos();

---

执行sql脚本

Mysql命令行执行.sql脚本有两种方式

mysql < xx.sql(未登录mysql)

1、在未连接数据库的情况下
mysql -h localhost -uroot -ppwd database_name < prod.sql

例如执行一个批量sql脚本并统计执行时间

time mysql -h localhost -uroot -ppwd database_name < prod.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real    6m13.689s
user    0m30.067s
sys    0m11.806s

source xx.sql(已登录mysql)

2、在已经连接数据库的情况下，使用 source 命令 加 脚本全路径
source /tmp/prod.sql
其中 /tmp/prod.sql 是登录 mysql 的机器上的 sql 脚本

---

UDF自定义函数

6.2 Adding a Loadable Function
https://dev.mysql.com/doc/extending-mysql/5.7/en/adding-loadable-function.html

User Defined Function(UDF) 允许用户自定义函数并以插件 plugin 的形式添加到 MySQL 中。

1、c/c++编写自定义函数
MySQL UDF 函数只支持用 c/c++ 编写。完成后编译为 udf_example.so 动态链接库文件。

2、将 udf_example.so 动态链接文件拷贝到 MySQL 的 plugin 目录下
plugin 目录可以通过变量 plugin_dir 查看，

mysql> show variables  like '%plugin_dir%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| plugin_dir    | /usr/local/opt/mysql@5.7/lib/plugin/ |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

3、安装函数
根据函数的返回类型，在 MySQL 命令行中执行下面的语句来安装函数，以安装返回 INTEGER 类型的 udf_example 函数为例：

DROP FUNCTION IF EXISTS udf_example;
CREATE FUNCTION metaphon RETURNS INTEGER SONAME 'udf_example.so';

4、删除函数

DROP FUNCTION udf_example;

---