zqfan's blog

MySQL

2013/03/26 mysql

License: (CC 3.0) BY-NC-SA

Manual

following categories

mysql> ? contents
mysql> ? data types
mysql> ? int

man command

mysql> ? show
mysql> ? create table

What i know?

connect to mysql shell

  • mysql -h hostname -u username -p[password]
  • mysql -h hostname -u username --password=passwd

create database

drop database if exists dbname;
create database dbname;

mysql doesn’t support duplicate database name, you should check if there is a databse already exists, or you can simply drop if exists.

grant privilege

grant all privileges on dbname.* on 'dbadmin'@'%' identified by 'dbadminpasswd';
  • you can also grant parts of privileges to users
  • grant on . means on all
  • % means all, or you can specific a network, like localhost or 192.168.32.0

show

show databases;
use dbname;
show tables;
desc tablename;
show columns from tablename;

create table

create table tablename (
colname1 varchar(255),
colname2 int,
id int not null auto_increment,
primary key (id));

insert

insert into tablename values (name,27,null);

select

select colname1 from table colname2='';

update

update tablename set colname1='' where colname2='';

delete

delete from tablename where colname1='';

Some Concepts

table type

  • 事务安全表：InnoDB、BDB
  • 非事务安全表：MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED

常用的有两个：

  • Myisam：默认存储引擎，当create新表未指定存储类型的时候即为myisam。每个表在磁盘上分为三个文件：.frm存储表定义，.MYD存储数据，.MYI存储索引，数据文件和索引文件可以在不同的目录以分布io，提高速度。
  • InnoDB：具有提交、回滚和崩溃恢复能力的事务安全。缺点是写处理效率略差和占用更多的磁盘空间。

data type

  • 对于myisam，建议使用固定长度的数据列代替可变长度的数据列
  • 对于InnoDB，建议使用VARCHAR类型。理由是InnoDB的主要性能因素是存储总量，而CHAR平均空间要大于VARCHAR。
  • 浮点数float能表示更大的范围但是精度有损失，（意味着写入后读出的值有可能与原值有微小的变化），定点数decimal表示范围小但是没有精度问题。

charset

  • mysql> show character set 查看mysql支持的字符集
  • mysql可以为同一个表的不同字段指定不同的字符集
  • mysql的字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念。CHARACTER定义字符串的存储方式，COLLATION定义字符串的比较方式，mysql支持30+CHARACTER和70+COLLATION
  • 使用mysql> show collation like ‘utf8%’查看相关字符集的校对规则
  • gb2312的字库比gbk小，有些生僻字无法保存
  • 在my.cnf[mysqld]中指定default-character-set=utf8可以设定服务器级别的字符集

SQL Injection

  • logical eval: ?username=admin’ or ‘1=1
  • comment: ?username=admin’/*
  • comment: ?username=admin’%23

how to defense?

  • prepareStatement + Bind-variable
  • mysql_real_escape_string()
  • self-defined check

Rescure

yesterday, a power failure caused raid disk drop and mysql innodb trunction failure, after we rebuild the raid, the mysql is down. i did the following step to restore our mysql data.

  1. add innodb_force_recovery = 4 to [mysqld] group of /etc/mysql/my.cnf
  2. start mysql
  3. mysqldump -uroot -p -A > dump.sql
  4. cp /var/lib/mysql/ib* ./
  5. service mysql stop
  6. comment the innodb_force_recovery
  7. aptitude purge mysql-server-5.5
  8. rm -rf /var/lib/mysql /etc/mysql
  9. aptitude install mysql-server-5.5
  10. config /etc/mysql/my.cnf
  11. mysql -uroot -p < dump.sql
  12. enter mysql, flush privileges; grant all privileges on databasename.* to user@’%’ identified by ‘password’;

and data is restored. but i think this is not the best way.

i tried to just drop the corrupted database but failed, and aptitude purge will not remove the setting files, then even installation success, it cannot restore the data, i don’t know why, so i just remove all the files related to mysql.

Search

    Table of Contents