MySQL Tips

ǡ١κ

mysql> create database [DATABASE NAME];

ǡ١κ

mysql> drop database [DATABASE NAME];

桼³̵¡

mysql> GRANT ALL PRIVILEGES ON [DATABASE].* TO [USER]@'%'  IDENTIFIED BY '[PASSWORD]';

mysql> DELETE FROM mysql.user WHERE user='[USER]' and host='[HOST]';
mysql> FLUSH PRIVILEGES;

ѥѹ

mysql> SET PASSWORD FOR [USER]@[HOST]=PASSWORD('[PASSWORD]');

grantޥΰ

Ggrant all privileges on [DATABASE].* to [USER]@[HOST] identified by '[PASSWORD]';

root桼˥ե륢¤Ϳ

mysql> GRANT all privileges ON *.* TO root with grant option;

databaseɽ

mysql> show databases;

桼ۥȰɽ

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,User from user;

ơ֥Ȥɽ

  • ɽ
    select * from [TABLE NAME];

  • 1ԤΤߤɽ
    select * from [TABLE NAME] limit 1;

ơ֥Υ쥳ɷɽ

select count(*) from [TABLE NAME]

ơ֥Ȥõ

truncate table [TABLE NAME];

ơ֥˴

mysql> drop table [TABLE NAME];

ơ֥ޤȤ˴ϲͤʥꥹȥե쥯ȤǥꥹȤɤ߹ǰ롣

  • ꥹ(droptable.sql)
    drop table [TABLE 1];
    drop table [TABLE 2];
    drop table [TABLE 3];
    drop table [TABLE 4];
  • ¹
    mysql -u [USER NAME] -p -h [HOST NAME] [DATABASE NAME] < droptable.sql

ǡ١Υ׺Table

mysqldump -u [USER] --password=[PASSWORD] [DATABASE NAME] [TABLE NAME] [TABLE NAME] ... > [ե̾]

ǡ١Υ׺DB

mysqldump -u [USER] --password=[PASSWORD] [DATABASE NAME] > [ե̾]

ǡ١Υ׺ƤDB

mysqldump -u [USER] --password=[PASSWORD] --all-databases > [ե̾]

ץեΥꥹȥ

mysql -u[USER] -p[PASSWORD] [DATABASE] < פե 

cat פե | mysql -u[USER] -p[PASSWORD] [DATABASE]



(gzip)ե뤫ꥹȥ

zcat פե.gz | mysql -u[USER] -p[PASSWORD] [DATABASE]

ʣΥץեꥹȥ

ʣΥץեޤȤƥꥹȥϡץեݴɥǥ쥯ȥ˰ư˲¹Ԥ롣

cat *.dmp | mysql -u [USER NAME] -p -h [HOST NAME] [DATABASE]

ʸɤγǧ

饻åȴϢΥƥѿǧˤϡmysql饤ޥư饻åȤδĶѿɽ롣

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

饻åȤδĶѿϲɽ

SHOW VARIABLES LIKE 'character\_set\_%';

ͤɽ̤Ȥʤ

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   | 
| character_set_connection | utf8   | 
| character_set_database   | utf8   | 
| character_set_filesystem | binary | 
| character_set_results    | utf8   | 
| character_set_server     | utf8   | 
| character_set_system     | utf8   | 
+--------------------------+--------+
7 rows in set (0.01 sec)

桼ΰɽ

Ѥߥ桼ΰɽˤϲ¹Ԥ롣

mysql> use mysql;
Database changed

mysql> select user,host from user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| mtuser | %         | 
| mysql  | %         | 
| root   | %         | 
|        | localhost | 
| root   | localhost | 
|        | mysql5    | 
| root   | mysql5    | 
+--------+-----------+
7 rows in set (0.00 sec)

ơ֥ΰɽ

DBΥơ֥ɽˤϰʲޥѤ롣

use [DB NAME]
show tables; 

ơ֥Υơǧ

ơ֥ΥơǧˤϰʲޥѤ롣
ѤƤDB󥸥μʤɤɽ롣

use [DB NAME]
show table status;

DBѴ

MyISAMInnoDBѴݤˤϰʲޥѤ롣

alter table [TABLE NAME] ENGINE=INNODB;

DB̤ǧ

SHOW TABLE STATUS [FROM db_name];

SQLե뤫SQL¹Ԥ

SQL򵭽Ҥƥȥե뤫SQL¹ԤˤϰʲԤ

mysql -u root -p [DB NAME] < [SQL File]

ơ֥»å

ơ֥뤬»ƤǽϰʲμˤƳǧԤ
MyISAMInnoDBARCHIVEơ֥ξˤϻѲǽ

  • »å
    check table [TABLE NAME];

ơ֥»

ơ֥뤬»ƤǽϰʲμˤƽԤ
MyISAMơ֥ξˤϻѲǽ

  • »ν
    repair table [TABLE NAME];

夫SQLȯ

  • ñޥμ¹
    mysql -u root --password=[PASSWORD] -e "show databases;"

    echo "show databases;" |mysql -u root --password=[PASSWORD]
  • DBꤷƼ¹
    echo "use [DB NAME]; show table status;" |mysql -u root --password=[PASSWORD]

ǡ١Ĵ٤

  • MBñ̤ɽ
    select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;
  • GBñ̤ɽ
    select table_schema, sum(data_length+index_length) /1024 /1024/1024 as GB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;
    
  • DBΥĴ٤
    select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables where table_schema = '[DATABASE NAME]';

ȥå   Խ ʬ ʎގ̎ ź ʣ ̾ѹ   ǽ   إ   ǽRSS
Last-modified: 2017-05-02 () 22:09:13 (141d)