1.登录MySQL
mysql -uroot -p123456 db_1
参数:u用户名 p密码 数据库名
2. 导入数据库
drop database if exists game_s1;
create database game_s1;
use game_s1;
select database();
source /home/sql/game_s1_bak.sql;
注:倒入game_s1_bak.sql 内容
3.格式化输出
select * from t_account_info where char_name = 'z'\G;
4. 查找某段时间充值记录
select* from t_charge_info where charge_time between '2013-10-01 00:00:00' and '2013-10-01 07:19:45';
DROP TABLE IF EXISTS `t_charge_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_charge_info` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '交易编号',
`platform_gamesvr_id` char(30) NOT NULL COMMENT '平台号+服号可以唯一确定一个游戏账户',
`charge_money` int(11) NOT NULL DEFAULT '0' COMMENT '平台货币',
`charge_gold` int(11) NOT NULL DEFAULT '0' COMMENT '游戏货币',
`level` int(11) NOT NULL DEFAULT '0' COMMENT '玩家等级',
`user_name` varchar(30) NOT NULL COMMENT '平台账号',
`order_id` varchar(60) NOT NULL COMMENT '平台充值编号',
`charge_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',
PRIMARY KEY (`id`),
UNIQUE KEY `char_name_uniq` (`order_id`),
KEY `charge_account` (`platform_gamesvr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='平台充值信息';
//导出记录
/********************charge.sql *******************
use game_s1;
select platform_gamesvr_id,charge_money,charge_time from t_charge_info where charge_time <'2013-09-04';
******************************************************/
mysql -uroot -p123456 -h 127.0.0.1 -D game_s1 <charge.sql> charge.txt
mysql -uroot -p123456 -h 127.0.0.1 game_s1 -e "select platform_gamesvr_id,charge_money,charge_time from t_charge_info where charge_time <'2013-09-04';" > charge.txt
###############log####################
create database game_s1_log
CREATE TABLE `ACTION_LOG` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ACCOUNT` varchar(256) NOT NULL DEFAULT '',
`PLAYERID` int(10) unsigned NOT NULL,
`PLAYERNAME` varchar(33) NOT NULL DEFAULT '',
`PLAYERLEVEL` int(10) unsigned NOT NULL,
`TYPE` int(10) unsigned NOT NULL,
`TIME` int(10) unsigned NOT NULL,
`INFO` mediumtext NOT NULL,
PRIMARY KEY (`ID`),
KEY `TIME` (`TIME`),
KEY `TYPE_TIME` (`TYPE`,`TIME`),
KEY `PLAYERID_TYP_TIME` (`PLAYERID`,`TYPE`,`TIME`),
KEY `PLAYERNAME_TYPE_TIME` (`PLAYERNAME`,`TYPE`,`TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=628648 DEFAULT CHARSET=utf8
mysql -uroot -p123456 -h 127.0.0.1 game_s1_log -e "select * from ACTION_LOG where PLAYERNAME='228test' and type=1;" > log.txt
5.将数据库导入到文件中
mysqldump --skip-opt --quick --default-character-set=utf8 --add-drop-table --create-options -e -u root -p123456 --databases game_s1 > /backup/game_s1.sql 2>>/backup/mysqldump.log
rsync -i -avz -H --progress -e "ssh -p 10022" root@ip:/backup/game_s1.sql.gz /home/
gunzip -d game_s1.sql.gz
6.将name,uid导入到文件中
select uid,char_name INTO OUTFILE '/tmp/trunk20150826.xls' FIELDS TERMINATED BY ',' from t_account_info
7.将文件数据导入数据库
SELECT * INTO OUTFILE 'C:\\log1.txt' FIELDS TERMINATED BY ',' FROM log.log1
LOAD DATA INFILE 'C:\\log1.txt' INTO TABLE aa.log2 FIELDS TERMINATED BY ',';
FIELDS TERMINATED BY ',' 字段间分割符
OPTIONALLY ENCLOSED BY '"' 将字段包围 对数值型无效
LINES TERMINATED BY '\n' 换行符
8.按等级查人数
select level,count(*) from t_character_info where char_flag=0 group by level order by level;
9. 如果字段原来无默认值,直接执行如下语句,添加默认值:
alter table 表名 add default(1) for 字段名
--如果原来有默认值,现在要更改默认值,则需要先把原来的默认值drop掉,再添加新的默认值
alter table 表名 drop constraint 默认值约束的名称
--如果不知道默认值约束的名称,用如下语句查询得到:
select [name]
from sysobjects t
where id = (select cdefault from syscolumns where id = object_id(N'表名')
and name='字段名')
10. 查询mysql当前用户使用状态
show processlist;
11.恢复用户数据
insert INTO consume (id,score) select id,sum(SUBSTRING(INFO,LOCATE('amount":"', INFO)+9,5)/1) c from db_1.ACTION_LOG
where TIME >= unix_timestamp("2015-12-17 1:00:00") and TIME <= unix_timestamp(CURRENT_TIMESTAMP()) group by id order by c desc;