1ãä¼é å ³éæ°æ®åºçæ¹æ³
ï¼1ï¼mysqladminæ¹æ³
mysqladmin -uroot -poldboy123 shutdown
ï¼2ï¼èªå¸¦çèæ¬
/etc/init.d/mysqld stop
2ãmysqlç»å½
mysql -uroot -poldboy123 #å¯ç åå²è®°å½éè¦å é¤
cat /root/.mysql_history
mysql -uroot -p -S /data/3306/mysql.sock #å¤å®ä¾ç»å½
3ã设置å¯ç æ¹æ³
mysqladmin -uroot password 'oldboy123'
#ä¿®æ¹å¯ç
mysqladmin -uroot -poldboy123 password '123';
#ç»å½æ°æ®åºåæ´æ¹
update mysql.user set password=password("456") where user='root' and host='localhost';
flush privileges
4ãSQLåç±»
- DQL(Data Query Language),æ°æ®æ£ç´¢è¯å¥ã
- DML(Data Manipulation Language)ã
- TPL(äºå¡å¤çè¯è¨ï¼ã
- DCL(Data Control Language),æ°æ®æ§å¶è¯è¨ã
- DDL(Data Definition Language),æ°æ®å®ä¹è¯è¨ã
- CCL(æéæ§å¶è¯è¨ï¼ã
5ãå建å符éæ°æ®åº
mysql> create database oldboy_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database oldboy_utf8\G
*************************** 1. row ***************************
Database: oldboy_utf8
Create Database: CREATE DATABASE `oldboy_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
6ãææ
select user,host from mysql.user;
create user [email protected] identified by '123';
flush privileges;
help grant
GRANT ALL ON oldboy_uft8.* TO 'test'@'localhost';
show grants for 'test'@'localhost'; #æ¥çç¨æ·æé
mysql> REVOKE INSERT ON oldboy_uft8.* FROM 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `oldboy_uft8`.* TO 'test'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
7ãç´¢å¼å主é®
alter table student change id id int primary key auto_increment;
alter table student add index index_dept(dept(8));
create index inde_name_dept on student(name,dept);
alter table student drop index index_dept;
create unique index index_name on student(name);
8ãå¤ä»½
mysqldump -uoldboy -poldboy123 -B oldboy >/opt/oldboy_bak.sql
grep -E -v "#|\/|^$|--" /opt/oldboy_bak.sql
USE `oldboy`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `inde_name_dept` (`name`,`dept`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'test');
UNLOCK TABLES;
9ã使ç¨explainæ¥çæ§è¡è®¡å
mysql> explain select * from test where name ='test'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)
create index index_name on test(name);
mysql> explain select * from test where name ='test'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name
key: index_name
key_len: 20
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
10ãä¸æä¹±ç é®é¢
#å
æ§è¡å符é设置ï¼åæå
¥å°±ä¸ä¼ä¸æä¹±ç ï¼ä¸´æ¶ç
set names latin1;
#å¨sqlæ件ä¸æå®set names latin1,ç¶åç»å½mysql,æ§è¡
source test.sql
#å¨sqlæ件ä¸æå®set names latin1,ç¶åéè¿mysqlå½ä»¤å¯¼å
¥æ°æ®ã
#éè¿æå®mysqlå½ä»¤çå符éåæ°å®ç°--default-characater-set=latin1
#å¨å®¢æ·ç«¯æå®ï¼æå¡ç«¯åæå®ï¼ç®çå°±æ¯å符éç»ä¸ä¸è´ã
11ãå符é
mysql -uroot -p123 -e 'show character set';
show variables like 'character_set%';
[[email protected] mysql-5.5.32]# vi /etc/sysconfig/i18n
#
#
SYSFONT="latarcyrheb-sun16"
. /etc/sysconfig/i18n
[[email protected] mysql-5.5.32]# echo $LANG
zh_CN.UTF8
mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)
#***
vi /etc/my.cnf
[client]
#password = your_password
default-character-set=utf8
port = 3306
socket = /application/mysql-5.5.32/tmp/mysql.sock
mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)
[mysqld]
character-set-server=utf8 #æ°å¢
port = 3306
socket = /application/mysql-5.5.32/tmp/mysql.sock
#æå¡ç«¯ä¿®æ¹éè¦éå¯
[[email protected] mysql-5.5.32]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
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 |
| character_sets_dir | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
12ãå¦ä½æ´æ¹ç产æ°æ®åºï¼
ä¿®æ¹æ¥éª¤ï¼
对äºå·²æçæ°æ®åºæ³ä¿®æ¹å符éä¸è½ç´æ¥éè¿"alter database character set * "æ âalter table tablenmae character set *â,è¿ä¸¤ä¸ªå½ä»¤é½æ²¡ææ´æ°å·²æè®°å½çå符éï¼èåªæ¯å¯¹æ°å»ºç表æè è®°å½çæã
å·²ç»æçæ°æ®çè°æ´ï¼å¿ é¡»å å°æ°æ®å¯¼åºï¼ç»è¿ä¿®æ¹å符éåéæ°å¯¼å ¥åæå¯å®æã
1ï¼å¯¼åºè¡¨ç»æ
mysqldump -uroot -p --default-character-set=latin1 -d dbname altertable.dql
#âdefault-character-set=gbk表示以GBKå符éè¿è¡è¿æ¥ï¼-dåªå¯¼è¡¨ç»æ
2ï¼ç¼è¾altertable.sqlå°latin1æ¹æGBK
3)ç¡®ä¿æ°æ®åºä¸åæ´æ°ï¼å¯¼åºææçæ°æ®
mysqldubmp -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname>altdata.sql
4)æå¼altdata.sqlå°set names latin1 ä¿®æ¹æset names gbk;
5)建åº
create database dbname default charset gbk;
6)å建表ï¼æ§è¡alttable.sql
mysql -uroot -p dbname<altertable.sql
7)å¯¼å ¥æ°æ®
mysql -uroot -p dbname<altdata.sql