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
  1. 注意: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: '?'.
  2. 如果出现报错: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);

 

posted @ 2018-04-30 11:42  我的五年  阅读(25)  评论(0)    收藏  举报  来源