Linux MySql Usage

Install

1
2
3
4
5
6
7
8
9
$ sudo dnf install community-mysql-server
$ sudo systemctl enable mysqld
$ sudo systemctl start mysqld
$ sudo mysql_secure_installation
$ sudo mysql -u root -p

mysql> show databases;
mysql> exit

User Usage

Start Mysql mysqladmin start/restart/shutdown
Connect Mysql $ mysql -u root -p Exit Mysql > exit
change passwd mysqladmin -u $user_name -p $old_pass password $new_pass or in mysql cmd > SET PASSWORD FOR root=PASSWORD("$passwd");
Add new user grant select on ${data}.* to ${user}@${host} identified by "$passwd" 如增加一个用户test密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql, 然后键入以下命令： grant select,insert,update,delete on . to Identified by “123”;
install mysql with yum # yum install -y mysql mysql-server # mysqladmin -u root password 'new-password'

Normal Usage

show databases;
use $my_data；
show tables;
describe $table_name
Create database create database $database_name; GBK : create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; UTF8: CREATE DATABASE test2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Create tables use $database_name; create table $table_name(Name varchar(255) not null, number int not null) default charset=utf8;
Delete database and tables drop database $database; drop table $table_name delete from $table_name truncate table $table_name
Show tables select * from $table_name
Instert data to tables instert into $table_name values("$vaule_a", "$Value_b")
Show mysql encoding > show variables like '%collation_%'; > show variables like 'character%'; > show create database $db_name; > show create table $table_name; > show table status from $tablename like ’%countries%’; > show full columns from $tablename; > show char set;
Change encoding edit /etc/my.cnf and restart mysqld.service

1
2
3
4
5
6
7
8
[mysqld]
character_set_server=utf8

[client]
default-character-set=utf8

[mysqld_safe]
default-character-set=utf8

change database encoding
`alter database $db_name default character set gbk;`
or runing `> set names utf8;` while running mysql

Rename table name alter table t1 rename t2;
Import txt to tables mysql> load data local infile "$path/mysql.txt" into table $my_table;
Create table create table if not exists $table_name ( Nametext NOT NULL,Email Address text NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8;

Select and Edit tables

select usage select all > select * from $table_name; select table or title which have a space 1. use “" to quote the name 2. edit my.cnf and add sql_mode = ANSI_QUOTES below[mysqld], then you can use double quotation marks 3. start mysqld with mysqld_safe –user=mysql –sql-mode=ANSI_QUOTES &`
limit usage : limit $start_line, $offset select the first line > select * from $table_name limit 0,1; select line 11-12 > select * from $table_name limit 10, 2; select the first 10 lines > select * from $table_name limit 10;
like usage
1. % as any zero or many charactors : abc, aabc, aabcdddd > select * from $table_name where $title like '%abc%'
2. _ as any single charactor : aabcd > select * from $table_name where $title like '_abc_'
3. [] will select any string in it : ac, bc > select * from $table_name where $title like '[ab]c'
4. [^] select string which not contain string in it : cc, dc > select * from $table_name where $title like '[^ab]c'
Union Usage
1. union will remove the files which are all the same. select * from $t_name where $Name like "abc" union select * from $t_name where $Email like "abc"
2. union all wont remove the same files select * from $t_name where $Name like "abc" union all select * from $t_name where $Email like "abc"
some useful cmd
1. Select something from two lines select * from $table_name where concat($title, $title) like "%sth%";
change/modify tables
1. if you want to change name, use change alter table $t_name change $odd $new varchar(64) not null;
2. if you only want to change type , use modify alter table $t_name modify $line_name varchar(64) not null;
3. change table engine alter table $t_name type=myisam;
4. if you want to make it default, add default-storage-engine=INNODB under [mysqld] in my.cnf
5. change table engine; alter table $t_name engine=myisam;
Insert/update/delete
1. insert into $t_name(name,address) values('a','b');
2. insert into $t_name values('a','b');
3. update $t_name set Password="123" where Name="abc";
4. delete from $t_name where ...
Add/delete/modify field
1. Add a new field alter table $t_name add $new_field_id type not null default '0'; e.g. alter table eg_table add eg_id integer not null default 372;
2. delete field alter table $t_name drop column $colname;
3. change field name alter table $t_name change $odd_field_name $new_name $old_type;
4. change field type alter table $t_name change $odd_field_name $old_name $new_type;
Create index : http://database.51cto.com/art/200910/156685.htm http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

Normal index 1. Create index CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length 2. modify index ALTER mytable ADD INDEX [indexName] ON (username(length)); 3. delete index DROP INDEX [indexName] ON mytable;
Unique index CREATE UNIQUE INDEX indexName ON mytable(username(length));
combine index ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
when use index, some thing you need remember http://blog.csdn.net/csliuruidongdn/article/details/8783426

Select,Union, and Count

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
use $db_name;
insert into $table_4
select username,count(username)
from
(
select username from $table_1
union all
select username from $table_2
union all
select username from $table_3
) temp_table
group by username
order by count(username) desc,username asc;

Show database size

use information_schema;
Show all tables size select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
Show database you selected select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='$your_db_name';
Show a table size select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='$your_db_name' and table_name='$your_table_name'

Backup and load

Backup : $ mysqldump -u user -p db tab1 tab2 > db.sql
Reload : $ mysql -u user -p db < db.sql

Errors:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

MySQL is trying to tell you that it doesn’t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.

ERROR 1 (HY000): Cant create/write to file ‘/tmp/#sql_3fd4_0.MYI’ (Errcode: 13)

Most time is because you dont have permission to write, make sure you have the right permission. use chmod 1777 /tmp. If still have this problem. make sure you have disabled seLinux setenforce 0.

3 ) ERROR 3 (HY000): Error writing file ‘/tmp/MYf1RZMk’ (Errcode: 28)

This is because you dont have enough space in /tmp, you can mount other path to /tmp, like # mount --bind /home/tmp /tmp

mysql> create index addr_index on address(Name, QQ Number, Email Address); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

Each ENGINE have its limit when create index. MYISAM index length limit is 1000, INNODE length limit is 767, so make sure your lines are now text.

Referfence : [1] http://www.cnblogs.com/xiaochaohuashengmi/archive/2011/10/18/2216279.html [2] diff about engines http://www.cnblogs.com/sopc-mc/archive/2011/11/01/2232212.html

Contents