MySQL备忘录

一、插入数据乱码：Incorrect string value: '\xE6\x9B\xB9\xE5\x86\xAC...' for column 'realname' at r 1、alter database team_club character set utf8; 2、alter table activity convert to character set utf8 3、alter table tableName character set utf8; 4、alter database databaseName character set utf8; 二、忘记root密码 # vi /etc/my.cnf 在[mysqld]的段中加上一句：skip-grant-tables # /etc/init.d/mysqld restart # /usr/bin/mysql mysql> USE mysql ; mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 三、备份数据库 mysqldump -p regression_testing > re_2010_11_12.sql 四、取消导入数据时候的主外键检查 SET FOREIGN_KEY_CHECKS = 0; 五、取消导入数据时候的主外键检查 SET FOREIGN_KEY_CHECKS = 0; 六、创建数据库 CREATE DATABASE IF NOT EXISTS ctnefacade default charset utf8 COLLATE utf8_general_cs; CREATE DATA 七、导入数据 mysql -u username -p -h localhost --default-character-set=utf8 database-name < import.sql 八、.授权 grant all privileges on *.* to `root`@`dev.shtianxin.com` identified by '/;p0.lo9' with grant option; 九、修改字段类型 ALTER TABLE project MODIFY COLUMN manager_assortment VARCHAR(10); 十、修改mysql控制台乱码问题 show variables like 'character_set%'; // 控制从命令行sql语句输入编码 set global character_set_client='utf8'; // 控制结果集编码 set global character_set_results='utf8'; 十一、修改字段类型 ALTER TABLE member MODIFY summary TEXT; 十二 、安装、自启动 yum -y install mysql-server /etc/rc.d/init.d/mysqld start chkconfig mysqld on chkconfig --list mysqld 如果2--5为on的状态就OK #mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off 十三、远程访问 开放防火墙的端口号 mysql增加权限：mysql库中的user表新增一条记录host为"% "，user为"root " 十四、重要目录 数据库目录：/var/lib/mysql/ 配置文件：/usr/share /mysql（mysql.server命令及配置文件） 相关命令：/usr/bin（mysqladmin mysqldump等命令） 启动脚本：/etc/rc.d/init.d/（启动脚本文件mysql的目录） 十五、卸载 1、rpm -ivh mysql-server-5.1.... --nodeps --force(直接强制安装) 2、yum remove mysql(rhel卸载) 3、CentOS自带卸载异常，rpm -e --nodeps mysql-libs-5.1..... 4、server/client都需要安装 5、卸载自带mysql，yum remove mysql-libs-5.1.... 6、查找以前是否装有mysql命令：rpm -qa|grep -i mysql 十六、联合唯一索引（执行时，可删除重复记录，剩一条）： alter ignore table zqw_score_call_back add unique index(userid,adid,package_name); 十七、查询个人排名 select * FROM  (      SELECT A.*,@rank:=@rank+1 as client_ip      FROM      (       SELECT openid as openid,gameid as gameid,subscribe as subscribe,avg(score) as score,create_time as time FROM wx_game_ranking                where gameid = #{gameid} and subscribe = #{subscribe}                GROUP BY openid        ORDER BY score  DESC,  create_time asc      ) A ,(SELECT @rank:=0) B  ) M  where m.openid = #{openid} ORDER BY score desc 十八、级联删除 delete ww from wx_game_winning as ww,wx_game_ranking as rr where ww.ranking_id=rr.id and rr.gameid = #{gameid} 十九、更改编码 set @@character_set_database='utf8'; 二十、自动备份 1：赋予脚步可执行权限 chmod +x mysql_backup.sh 2：添加定时执行计划 vi /etc/crontab 添加：00 5 * * * root /backup/mysql_backup.sh 每日5点钟备份一次数据库 二十一、添加唯一索引 alter table bcd_qrcodeitem add unique index(seqno);