1、停止mysql服务2、复制数据库Data文件夹到其它目录，比如从 C:ProgramDataMySQLMySQL Server 5.7Data 复制到 D:MySQLData3、新data目录下除了 文件夹 以及 ibdata1 这个文件，全部删掉4、更改mysql的服务属性，将登陆身份改成本地5、创建软链接或修改my.ini配置文件5.1、将原Data文件夹改名，创建软链接。 mklink /j "C:\ProgramData\MySQL\MySQL Server 5.7\Data" "D:\MySQLData" 5.2、修改C:ProgramDataMySQLMySQL Server 5.7my.ini配置文件中的datadir路径为新Data文件夹6、启动mysql服务，确认运行正常后再删除原Data文件夹参考：https://blog.csdn.net/a123gsfd/article/details/79999838
SQL IN 匹配多个字段的用法：select * from t where (a, b) in ((1, 2), (3, 4)) select t1.a,t2.b from t1 left join t2 on t1.c=t2.d where (t1.a, t2.b) in ((1, 2), (3, 4))
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for hq_date 日期表 -- ---------------------------- DROP TABLE IF EXISTS `hq_date`; CREATE TABLE `hq_date` ( `id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `date` date NOT NULL COMMENT '日期', `day_of_week` tinyint NULL DEFAULT NULL COMMENT '星期几（周日开始，1-7）', `holiday` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '是否为假期', `name_of_holiday` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '假期名称', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `ix_hq_date_date`(`date`) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 5001 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '日期表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Function structure for CalcOverDays 计算超期天数 -- ---------------------------- DROP FUNCTION IF EXISTS `CalcOverDays`; delimiter ;; CREATE FUNCTION `CalcOverDays`(`begindate` date,`enddate` date) RETURNS int(11) BEGIN DECLARE workdays INT DEFAULT 0; -- 工作日天数 DECLARE min_date date DEFAULT begindate; -- 日期表最小日期 DECLARE max_date date DEFAULT enddate; -- 日期表最大日期 DECLARE weekcnt INT DEFAULT 0; -- 两日期的周数差 DECLARE beginweek INT DEFAULT 1; -- 开始日期的星期 DECLARE endweek INT DEFAULT 1; -- 结束日期的星期 IF begindate < enddate THEN SELECT min(date),max(date) into min_date,max_date FROM hq_date; IF begindate < min_date OR enddate > max_date THEN IF begindate < min_date THEN SET beginweek = DAYOFWEEK(begindate); SET endweek = DAYOFWEEK(IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY))); SET weekcnt = timestampdiff(week,begindate,IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY))); IF weekcnt = 0 THEN SET workdays = workdays + endweek - beginweek + 1 - case when (beginweek > 1 and endweek < 7) then 0 when (beginweek = 1 and endweek =7) then 2 else 1 end; ELSE SET workdays = workdays + (weekcnt - 1) * 5 + case when beginweek = 1 then 5 when beginweek = 7 then 0 else 7 - beginweek end + case when endweek = 1 then 0 when endweek = 7 then 5 else endweek - 1 end; END IF; END IF; IF enddate > max_date THEN SET beginweek = DAYOFWEEK(IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY))); SET endweek = DAYOFWEEK(enddate); SET weekcnt = timestampdiff(week,IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY)),enddate); IF weekcnt = 0 THEN SET workdays = workdays + endweek - beginweek + 1 - case when (beginweek > 1 and endweek < 7) then 0 when (beginweek = 1 and endweek =7) then 2 else 1 end; ELSE SET workdays = workdays + (weekcnt - 1) * 5 + case when beginweek = 1 then 5 when beginweek = 7 then 0 else 7 - beginweek end + case when endweek = 1 then 0 when endweek = 7 then 5 else endweek - 1 end; END IF; END IF; END IF; SELECT workdays + count(1) into workdays FROM hq_date WHERE date>=begindate AND date<enddate AND holiday='0'; END IF; RETURN workdays; END ;; delimiter ; -- ---------------------------- -- Function structure for CalcWorkDays 计算工作日天数 -- ---------------------------- DROP FUNCTION IF EXISTS `CalcWorkDays`; delimiter ;; CREATE FUNCTION `CalcWorkDays`(`begindate` date,`enddate` date) RETURNS int(11) BEGIN DECLARE workdays INT DEFAULT 0; -- 工作日天数 DECLARE min_date date DEFAULT begindate; -- 日期表最小日期 DECLARE max_date date DEFAULT enddate; -- 日期表最大日期 DECLARE weekcnt INT DEFAULT 0; -- 两日期的周数差 DECLARE beginweek INT DEFAULT 1; -- 开始日期的星期 DECLARE endweek INT DEFAULT 1; -- 结束日期的星期 IF begindate <= enddate THEN SELECT min(date),max(date) into min_date,max_date FROM hq_date; IF begindate < min_date OR enddate > max_date THEN IF begindate < min_date THEN SET beginweek = DAYOFWEEK(begindate); SET endweek = DAYOFWEEK(IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY))); SET weekcnt = timestampdiff(week,begindate,IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY))); IF weekcnt = 0 THEN SET workdays = workdays + endweek - beginweek + 1 - case when (beginweek > 1 and endweek < 7) then 0 when (beginweek = 1 and endweek =7) then 2 else 1 end; ELSE SET workdays = workdays + (weekcnt - 1) * 5 + case when beginweek = 1 then 5 when beginweek = 7 then 0 else 7 - beginweek end + case when endweek = 1 then 0 when endweek = 7 then 5 else endweek - 1 end; END IF; END IF; IF enddate > max_date THEN SET beginweek = DAYOFWEEK(IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY))); SET endweek = DAYOFWEEK(enddate); SET weekcnt = timestampdiff(week,IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY)),enddate); IF weekcnt = 0 THEN SET workdays = workdays + endweek - beginweek + 1 - case when (beginweek > 1 and endweek < 7) then 0 when (beginweek = 1 and endweek =7) then 2 else 1 end; ELSE SET workdays = workdays + (weekcnt - 1) * 5 + case when beginweek = 1 then 5 when beginweek = 7 then 0 else 7 - beginweek end + case when endweek = 1 then 0 when endweek = 7 then 5 else endweek - 1 end; END IF; END IF; END IF; SELECT workdays + count(1) into workdays FROM hq_date WHERE date>=begindate AND date<=enddate AND holiday='0'; END IF; RETURN workdays; END ;; delimiter ; -- ---------------------------- -- Function structure for GetBeginWorkDate 计算最迟开始日期 -- ---------------------------- DROP FUNCTION IF EXISTS `GetBeginWorkDate`; delimiter ;; CREATE FUNCTION `GetBeginWorkDate`(`deadline` date,`estimate` float) RETURNS date BEGIN DECLARE workdays INT DEFAULT 0; -- 工作日天数 DECLARE dayexsists INT DEFAULT 1; -- 日期表是否存在 DECLARE beginday date DEFAULT deadline; -- 最迟开始工作日期 DECLARE isholiday CHAR DEFAULT '0'; -- 是否为假日 SET workdays = CEILING( estimate / 8 ); WHILE workdays > 0 DO SELECT holiday, count(1) into isholiday, dayexsists FROM hq_date WHERE date = beginday; IF dayexsists = 0 THEN -- 日期表不存在的，周一到周五为工作日，周六周日为双休日 IF DAYOFWEEK(beginday) IN (1,7) = 0 THEN SET workdays = workdays - 1; END IF; ELSE -- 日期表存在的，以日期表的假日为准 IF isholiday = '0' THEN SET workdays = workdays - 1; END IF; END IF; IF workdays > 0 THEN SET beginday = DATE_SUB( beginday, INTERVAL 1 DAY); END IF; END WHILE; RETURN beginday; END ;; delimiter ; SET FOREIGN_KEY_CHECKS = 1;
备份数据库所有非系统库，可选备份docker还是本地的mysql，压缩包可包含路径或不包含路径，自动删除7天前的备份文件首先创建 mysql.sh 备份脚本#!/bin/bash databases=($(docker exec mysql mysql -uroot -pPassword -e "show databases")) basepath='/tmp/mysql/backup/' if [ ! -d "$basepath" ]; then mkdir -p "$basepath" fi for db in ${databases[*]} do if | $db == "information_schema" || $db == "performance_schema" || $db == "mysql" || $db == "sys" ; then continue fi if [ ! -d "$basepath$db" ]; then mkdir -p "$basepath$db" fi # docker模式备份 docker exec mysql mysqldump -uroot -pW-z3DSOd --single-transaction $db > $basepath$db/$db-$(date +%Y%m%d).sql # 正常模式备份 # mysqldump -uroot -pW-z3DSOd --single-transaction $db > $basepath$db/$db-$(date +%Y%m%d).sql # 压缩包内带目录 # tar zPvf $basepath$db/$db.$(date +%Y%m%d).tar.gz $basepath$db/$db-$(date +%Y%m%d).sql # 压缩包内不带目录 tar zcvf $basepath$db/$db.$(date +%Y%m%d).tar.gz -C$basepath$db/ $db-$(date +%Y%m%d).sql # 删除7天前的备份 # find $basepath$db* -mtime +7 -name '*.tar.gz' -exec rm -rf {} \; # 只保留最新的7个备份 find $basepath$db* -name '*.tar.gz' | sort -t"." -k2n | head -n -7 | xargs rm -rf done rm -rf $basepath/*/*.sql 然后是添加执行权限chmod +x /tmp/mysql/mysql.sh 最后是设置定时执行脚本crontab -e 在最后添加一行，每天定时执行0 2 * * * /tmp/mysql/mysql.sh 参考：https://cloud.tencent.com/developer/article/2027981
首先要安装mysql，然后采用bat脚本和定时任务的策略。编写备份脚本，然后在windows添加定时任务，脚本会删除过期的备份文件。@echo off echo 设置MySql数据库的连接信息 set host=127.0.0.1 set port=3306 set user=root set pass=xxxxxx echo 设置要备份的MySql数据库名称 set dbname=testdb echo 获取当天的日期格式，例如：2022122031300 set hour=%time:~0,2% if "%time:~0,1%"==" " set hour=0%time:~1,1% set backup_date=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2% echo 设置备份文件的路径 set backupfile=F:\backData\%dbname%-%backup_date%.sql echo 使用mysqldump对指定的MySql进行备份 echo 注意路径中有空格的要加上双引号 "D:\software\mysql\mysql-8.0.13-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile% echo 删除过期文件，这里是超过30天就删除 rem forfiles /p F:\backData /s /m *.sql /d -30 /c "cmd /c del @file /f" echo 删除过期文件，这里是保留最新的10条记录 for /F "skip=10 eol=: delims=" %F in ('dir /b /o-d F:\backData\*.sql') do @del "%F" 参考：https://blog.csdn.net/xu_hui123/article/details/128395287
CREATE OR REPLACE FUNCTION AGE_IN_IDCARD ( parameter VARCHAR2 ) RETURN NUMBER IS val DATE; BEGIN IF parameter is NULL THEN RETURN -1; ELSE val := CASE WHEN LENGTH(parameter) <= 15 THEN TO_DATE( '19'||NVL( SUBSTR( parameter, 7, 6 ), 'a' ), 'yyyy-MM-dd' ) ELSE TO_DATE( NVL( SUBSTR( parameter, 7, 8 ), 'a' ), 'yyyy-MM-dd' ) END; END IF; RETURN TRUNC( ( TO_CHAR( SYSDATE, 'yyyyMMdd' ) - TO_CHAR( val, 'yyyyMMdd' ) ) / 10000 ); EXCEPTION WHEN OTHERS THEN RETURN - 1; END;
两台数据库服务器，一台是oarcle11g (IP：192.168.1.31)，另一台是oracle19c，需要把数据从11g导入到19c。由于11g的数据库有使用到自动表分区，exp导出报错，只能使用expdp命令了。现在首先要做的是把数据从11g导出到19c的服务器上。第一步：在19c上创建dblink，执行SQL语句创建 link_to_11g，这里的用户名和密码是11g上的用户，确保这个用户拥有exp_full_database，最好是用system用户；CREATE PUBLIC database link link_to_11g CONNECT TO username IDENTIFIED BY "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.31)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))'; 第二步：在19c上创建DIRECTORY，执行SQL语句创建 expdp_dir；CREATE DIRECTORY expdp_dir AS 'D:/databak/'; 第三步：在19c服务器上创建expdp的parfile文件 D:\exp.par，userid为19c的用户，要拥有exp_full_database权限，最好用system用户，network_link和directory是前两步创建的，dumpfile是导出的文件名，logfile是日志文件名，schemas是11g的用户名，示例内容如下：userid=system/"manager" directory=expdp_dir dumpfile=expdp.dump logfile=expdp.log schemas=USER_1,USER_2,USER_3 network_link=link_to_11g 第四步：在19c服务器上打开CMD，执行导出命令：expdp parfile=D:\exp.par 第五步：在19c服务器上打开CMD，执行导入命令：impdp system/"manager" directory=expdp_dir dumpfile=expdp.dump logfile=impdp.log full=yes 参考：https://blog.csdn.net/weixin_31688465/article/details/113398278
--创建用户create user username identified by password;--给用户权限grant all privileges to username;--给用户dba权限grant connect,resource,dba to username;--导入备份文件imp username/password file = C:/dmp/username_20220607.dmp log = C:/dmp/username_20220607.log full = y--导入指定用户到指定用户imp BM/bm123@ORCL fromuser=BM touser=BM2 file = C:/dmp/username_20220607.dmp log = C:/dmp/username_20220607.log ignore=y--导出指定用户exp tc/"tc_19" owner=ZM file=/usr/local/share/xjtc.dmp log = /usr/local/share/xjtc.dmp.log--远程导出exp tc/"tc_19"@172.16.0.3:1521/orcl file=/usr/local/share/xjtc.dmp log = /usr/local/share/xjtc.dmp.log full=y--远程导入imp tc/"erp-ch"@172.16.0.3:1521/orcl file = C:/tc.dmp log = C:/tc.dmp.log full = y statistics=nonelinux安装oracle客户端参考： https://blog.csdn.net/yuki5233/article/details/115724257临时环境变量，输入以下命令起效。export ORACLE_HOME=/usr/lib/oracle/12.2/client64export TNS_ADMIN=$ORACLE_HOME/network/adminexport NLS_LANG='simplified chinese_china'.ZHS16GBKexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport PATH=$ORACLE_HOME/bin:$PATH
select username,machine,osuser,count(1) cnt from v$session where username is not null group by username,machine,osuser ORDER BY count(1) desc;