Mysql数据库常用操作和Mysql大数据高效迁移方案
1、数据迁移:
1.数据量较少时可使用mysqldump和mysql命令导出和导入
# 导出指定数据库系统
mysqldump -u _username -p _dbname > _sqlfileFilename.sql
# 导入指定数据库系统
mysql -u _username -p _dbname < "./_sqlfileFilename.sql"
# 导出所有数据库系统
mysqldump -u _username -p --all-databases > _all.sql
# 导入
mysql -u _username -p < "./_all.sql"
# 以"_"开头の部分根据实际情况填写
# 参考:https://www.cnblogs.com/SZxiaochun/p/8359456.html
- 注意:win导出导入建议都用cmd,否则会出现如下报错:ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '?'.
- 如果出现报错:ERROR at line 140: Unknown command '\''. 可以添加字符集参数解决:mysql -uroot -pPASSWORD test < data2.sql --default-character-set=utf8
2.数据量较大时可使用select data into outfile file.txt、load data infile file.txt into table的命令高效迁移(百万数据只需数分钟)
参考:https://cloud.tencent.com/developer/article/1804242
在源数据库中导出数据文件
select * from dc_mp_fans into outfile '/data/fans.txt';
复制数据文件到目标服务器
zip fans.zip /data/fans.txtscp fans.zip root@ip:/data/
在目标数据库导入文件
unzip /data/fans.zip
load data infile '/data/fans.txt' into table wxa_fans(id,appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@dummy,@dummy,@dummy,language,country,province,city,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);
按照这么几个步骤操作,几分钟内就完成了一个百万级数据表的跨服务器迁移工作。
注意项
mysql安全项设置
在mysql执行load data infile和into outfile命令都需要在mysql开启了secure_file_priv选项, 可以通过show global variables like '%secure%';查看mysql是否开启了此选项,默认值Null标识不允许执行导入导出命令。通过vim /etc/my.cnf修改mysql配置项,将secure_file_priv的值设置为空:
[mysqld] secure_file_priv=''
则可通过命令导入导出数据文件。
3.数据恢复
除了上述通过sql文件恢复的方式,mysql8同样也可以用data文件恢复数据。具体操作就是将原数据库的data目录下的所有内容复制到新数据库data目录下,然后登录数据库依次对每个表执行如下sql语句:
ALTER TABLE tablename IMPORT TABLESPACE;
经典常用SQL查询语句和常见问题
2、常用sql:
------------------------
// mysql改root密码
mysqladmin -u用户名 -p旧密码 password 新密码
# ---more-MySQL修改root密码の4种方法:
# https://blog.csdn.net/th_num/article/details/71402801
# 查看sql_mode
SHOW VARIABLES LIKE 'sql_mode';
#------------------------
以下位转载类容 原文作者:qmdweb
原文: 经典SQL查询语句大全 https://blog.csdn.net/qmdweb/article/details/83054739
#------------------------
1、说明:创建数据库系统
CREATE DATABASE database-name
2、说明:删除数据库系统
drop database dbname
3、说明:备份sql server
--- 创建 备份数据の device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明:创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有の表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、说明:删除新表
drop table tabname
truncate table tableName //清空表并释放空间(新记录のid从1开始)
6、说明:增加1个列
Alter table tabname add column col type
# 注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变の是增加varchar类型の长度。
7、说明:添加主键: Alter table tabname add primary key(col)
说明:删除主键: Alter table tabname drop primary key(col)
8、说明:创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
注:索引是不可更改の,想更改必须删除重新建。
9、说明:创建视图:create view viewname as select statement
删除视图:drop view viewname
10、说明:几个简单の基本のsql语句
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
替换:UPDATE table set colname = replace( colname,"/static","") WHERE 范围;
查找:select * from table1 where field1 like ’%value1%’ ---likeの语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count(fidld) as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
11、说明:几个高级查询运算词
A: UNION 运算符
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出1个结果表。当 ALL 随 UNION一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表の每一行不是来自 TABLE1 就是来自 TABLE2。
B: EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中の行并消除所有重复行而派生出1个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
C: INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有の行并消除所有重复行而派生出1个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词の几个查询结果行必须是一致の。
12、说明:使用外连接
A、left (outer) join:
左外连接(左连接):结果集几包括连接表の匹配行,也包括左连接表の所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right (outer) join:
右外连接(右连接):结果集既包括连接表の匹配连接行,也包括右连接表の所有行。
C:full/cross (outer) join:
全外连接:不仅包括符号连接表の匹配行,还包括两个连接表中の所有记录。
12、分组:Group by:
一张表,一旦分组 完成后,查询后只能得到组相关の信息。
组相关の信息:(统计信息) count,sum,max,min,avg 分组の标准)
在SQLServer中分组时:不能以text,ntext,image类型の字段作为分组依据
在selecte统计函数中の字段,不能和普通の字段放在一起;
13、查询N天前
# eg.查询30天前
SELECT * FROM resource WHERE upload_at < DATE_SUB(now(),INTERVAL 30 DAY);