mysql学习之旅

1，mysql安装脚本

  1 #!/bin/bash
  2 RSYNC='10.39.2.120::wenhui/database/install'
  3 CMAKE='cmake-2.8.6.tar.gz'
  4 MYSQL='mysql-5.5.24.tar.gz'
  5 MYSQL_DIR='/usr/local/mysql55'
  6 MYSQL_DATADIR='/data1/mysqldata/mysql3306'
  7 
  8 function main(){
  9     user=`whoami`
 10     if [ "$user" != "root" ]; then
 11         echo "please use root to execute this script"
 12         exit 1
 13     fi
 14 
 15     module=$1
 16     module_type=$2
 17     case "$1" in
 18         cmake)
 19             install_cmake
 20             ;;
 21         mysql)
 22             install_mysql $module_type
 23             ;;
 24         *)
 25             echo "USAGE: $0 MODULE[cmake|mysql] MODULE_TYPE[full|libmysql|mysqlclient]"
 26     esac
 27 }
 28 
 29 #install cmake
 30 function install_cmake(){
 31     echo "begin install cmake"; sleep 1
 32     echo "get tar from $RSYNC"
 33     eval "rsync -avzP $RSYNC/$CMAKE ./"
 34     dir=`echo $CMAKE | perl -pe 's/\Q.tar.gz\E$//g'`
 35     eval "test -e $dir && rm -rf $dir"
 36     eval "tar -zxvf $CMAKE"
 37     echo "cd to $dir"
 38     eval "cd $dir"
 39     ./configure
 40     make && make install
 41     cd ..
 42 }
 43 
 44 #install mysql
 45 function install_mysql(){
 46     LD_PATH='/etc/ld.so.conf.d/mysql.conf'
 47     INSTALL_TYPE='full'
 48     if [ "$1" != "" ]; then
 49         INSTALL_TYPE=$1
 50     fi
 51 
 52     echo "begin install mysql $INSTALL_TYPE"; sleep 1
 53     echo "get tar from $RSYNC"
 54     eval "rsync -avzP $RSYNC/$MYSQL ./"
 55     dir=`echo $MYSQL | perl -pe 's/\Q.tar.gz\E$//g'`
 56     eval "test -d $dir && rm -rf $dir"
 57     eval "tar -zxvf $MYSQL"
 58     echo "cd to $dir"
 59     eval "cd $dir"
 60     if [ "$INSTALL_TYPE" == "full" ]; then
 61         eval "useradd mysql -s /sbin/nologin"
 62         eval "cmake     -DCMAKE_INSTALL_PREFIX=$MYSQL_DIR \
 63                         -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
 64                         -DDEFAULT_CHARSET=utf8 \
 65                         -DDEFAULT_COLLATION=utf8_general_ci \
 66                         -DWITH_MYISAM_STORAGE_ENGINE=1 \
 67                         -DWITH_INNOBASE_STORAGE_ENGINE=1 \
 68                         -DWITH_MEMORY_STORAGE_ENGINE=1 \
 69                         -DWITH_READLINE=1 \
 70                         -DENABLED_LOCAL_INFILE=1 \
 71                         -DMYSQL_DATADIR==$MYSQL_DATADIR \
 72                         -DMYSQL_USER=mysql \
 73                         -DMYSQL_TCP_PORT=3306 \
 74                         -DWITH_PARTITION_STORAGE_ENGINE=1 \
 75                         -DEXTRA_CHARSETS=all \
 76                         -DWITH_EXTRA_CHARSETS=all \
 77              "
 78         make && make install
 79 
 80         #copy configure file
 81         SCRIPT_DIR="$MYSQL_DIR/start"
 82         test -d $SCRIPT_DIR || mkdir -p $SCRIPT_DIR
 83         eval "rsync -avzp $RSYNC/my.cnf $SCRIPT_DIR"
 84         eval "rsync -avzp $RSYNC/init.sh $SCRIPT_DIR"
 85 
 86     elif [ "$INSTALL_TYPE" == "libmysql" ]; then
 87         eval "cmake     -DCMAKE_INSTALL_PREFIX=$MYSQL_DIR \
 88                         -DWITHOUT_SERVER=ON
 89              "
 90         make && make install
 91 
 92     elif [ "$INSTALL_TYPE" == "mysqlclient" ]; then
 93         eval "cmake     -DCMAKE_INSTALL_PREFIX=$MYSQL_DIR \
 94              "
 95         make mysqlclient libmysql && make install
 96     fi
 97 
 98     echo "add bin path to /etc/bashrc"
 99     echo "" >> /etc/bashrc
100     echo "#mysql envirment" >> /etc/bashrc
101     echo "export PATH=\$PATH:$MYSQL_DIR/bin" >> /etc/bashrc
102     echo "add ld path to $LD_PATH"
103     echo "$MYSQL_DIR/lib" > $LD_PATH
104     ldconfig
105 }
106 
107 main $1 $2
View Code

主服务：mysql-server mysql mysql-devel

 

[root@yz6254 ~]# sh /usr/local/mysql55/start/init.sh 3306 #创建启动脚本



* [root@yz6254 ~]# head install.sh
..............
MYSQL_DIR='/usr/local/mysql55' # 默认数据库安装路径
MYSQL_DATADIR='/data0/mysqldata/mysql3306' # 默认数据库存放路径
...............
* [root@yz6254 ~]# sh install.sh mysql libmysql # 安装libmysqlclient，用于perl,python连mysql数据库使用


==== 安装细节 ======

# cmake
tar zxvf cmake-2.8.6.tar.gz
cd cmake-2.8.6
./configure
make && make install

# Mysql
tar zxvf mysql-5.5.24.tar.gz
cd mysql-5.5.24
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql55 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR==/data0/mysqldata/mysql3306 \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DWITH_EXTRA_CHARSETS=all \

make && make install



数据库默认存放路径：/var/lib/mysql

常见操作：
mysql>
mysql> select version();

#支持日常计算
mysql> select 4*4;
+-----+
| 4*4 |
+-----+
| 16 |
+-----+
1 row in set (0.00 sec)

mysql> select 4+4;
+-----+
| 4+4 |
+-----+
| 8 |
+-----+
1 row in set (0.00 sec)

mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2015-12-06 |
+--------------+
1 row in set (0.00 sec)

mysql> select version(), current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.1.73 | 2015-12-06 |
+-----------+--------------+
1 row in set (0.00 sec)

mysql> create database yongsan_test;
Query OK, 1 row affected (0.00 sec)
mysql> drop database yongsan_test;
Query OK, 0 rows affected (0.40 sec)


注：数据库名称默认不能更改

 
 1 注：数据库名称默认不能更改
 2 
 3 
 4 数据库默认存放路径：/var/lib/mysql
 5 
 6 
 7 
 8 mysql> 
 9 mysql> create table lc_course(
10     -> id int,
11     -> course_name varchar(100),
12     -> course_length int,
13     -> teacher varchar(50),
14     -> category varchar(100)
15     -> );
16 
17 mysql> show tables;
18 +------------------------+
19 | Tables_in_yongsan_test |
20 +------------------------+
21 | lc_course              |
22 +------------------------+
23 1 row in set (0.00 sec)
24 
25 mysql> alter table lc_course rename course
26     -> ;
27 Query OK, 0 rows affected (0.00 sec)
28 
29 mysql> show tables;
30 +------------------------+
31 | Tables_in_yongsan_test |
32 +------------------------+
33 | course                 |
34 +------------------------+
35 1 row in set (0.00 sec)
36 
37 mysql> alter table course add link varchar(200);
38 Query OK, 0 rows affected (0.02 sec)
39 Records: 0  Duplicates: 0  Warnings: 0
40 
41 
42 
43 mysql> desc course
44     -> ;
45 +---------------+--------------+------+-----+---------+-------+
46 | Field         | Type         | Null | Key | Default | Extra |
47 +---------------+--------------+------+-----+---------+-------+
48 | id            | int(11)      | YES  |     | NULL    |       |
49 | course_name   | varchar(100) | YES  |     | NULL    |       |
50 | course_length | int(11)      | YES  |     | NULL    |       |
51 | teacher       | varchar(50)  | YES  |     | NULL    |       |
52 | category      | varchar(100) | YES  |     | NULL    |       |
53 | link          | varchar(200) | YES  |     | NULL    |       |
54 +---------------+--------------+------+-----+---------+-------+
55 6 rows in set (0.00 sec)
56 
57 mysql> 
58 mysql> alter table course modify teacher varchar(100);
59 Query OK, 0 rows affected (0.03 sec)
60 Records: 0  Duplicates: 0  Warnings: 0
61 
62 mysql> desc course;
63 +---------------+--------------+------+-----+---------+-------+
64 | Field         | Type         | Null | Key | Default | Extra |
65 +---------------+--------------+------+-----+---------+-------+
66 | id            | int(11)      | YES  |     | NULL    |       |
67 | course_name   | varchar(100) | YES  |     | NULL    |       |
68 | course_length | int(11)      | YES  |     | NULL    |       |
69 | teacher       | varchar(100) | YES  |     | NULL    |       |
70 | category      | varchar(100) | YES  |     | NULL    |       |
71 | link          | varchar(200) | YES  |     | NULL    |       |
72 +---------------+--------------+------+-----+---------+-------+
73 6 rows in set (0.00 sec)
74 
75 
76 
77 mysql> alter table course change column teacher lecturn varchar(100);
78 Query OK, 0 rows affected (0.02 sec)
79 Records: 0  Duplicates: 0  Warnings: 0
80 mysql> desc course;
81 +---------------+--------------+------+-----+---------+-------+
82 | Field         | Type         | Null | Key | Default | Extra |
83 +---------------+--------------+------+-----+---------+-------+
84 | id            | int(11)      | YES  |     | NULL    |       |
85 | course_name   | varchar(100) | YES  |     | NULL    |       |
86 | course_length | int(11)      | YES  |     | NULL    |       |
87 | lecturn       | varchar(100) | YES  |     | NULL    |       |
88 | category      | varchar(100) | YES  |     | NULL    |       |
89 | link          | varchar(200) | YES  |     | NULL    |       |
90 +---------------+--------------+------+-----+---------+-------+
91 6 rows in set (0.00 sec)
92 
93 
94 
95 INSERT INTO course VALUES(1,'mysql_linux','20','alex','basic','ln');
96 
97 
98 UPDATE course set course_name='yongsan_123' WHERE id=2
View Code

 

经典介绍：

 

 

mysql> create user yongsan identified by '123qwe';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+--------------------------------+---------+
| host | user |
+--------------------------------+---------+
| % | root |
| % | yongsan |
| 127.0.0.1 | root |
| localhost | |
| localhost | root |
| localhost | zabbix |
| yz3150.hadoop.data.sina.com.cn | |
| yz3150.hadoop.data.sina.com.cn | root |
+--------------------------------+---------+
8 rows in set (0.00 sec)

 

 

 

  1 mysql> rename user yongsan to yongsan_1;
  2 ERROR 2006 (HY000): MySQL server has gone away
  3 No connection. Trying to reconnect...
  4 Connection id:    94
  5 Current database: mysql
  6 
  7 
  8 
  9 mysql> set password = password('123qwe');
 10 Query OK, 0 rows affected (0.00 sec)
 11 
 12 
 13 mysql> set password for yongsan_1 = password('123qwe');
 14 Query OK, 0 rows affected (0.00 sec)
 15 
 16 
 17 
 18 mysql> select * from course;
 19 +------+-------------+---------------+---------+----------+------+
 20 | id   | course_name | course_length | lecturn | category | link |
 21 +------+-------------+---------------+---------+----------+------+
 22 |    1 | mysql_linux |            20 | alex    | basic    | ln   |
 23 |    2 | yongsan_123 |          NULL | NULL    | fly      | NULL |
 24 +------+-------------+---------------+---------+----------+------+
 25 2 rows in set (0.00 sec)
 26 
 27 mysql> 
 28 mysql> 
 29 mysql> 
 30 mysql> delete from course where id='2';
 31 Query OK, 1 row affected (0.00 sec)
 32 
 33 mysql> select * from course;
 34 +------+-------------+---------------+---------+----------+------+
 35 | id   | course_name | course_length | lecturn | category | link |
 36 +------+-------------+---------------+---------+----------+------+
 37 |    1 | mysql_linux |            20 | alex    | basic    | ln   |
 38 +------+-------------+---------------+---------+----------+------+
 39 1 row in set (0.00 sec)
 40 
 41 [yongsan@yz3110 ~]$ mysql -uroot -p yongsan_test < yongsan_test.sql 
 42 Enter password: 
 43 
 44 mysql> select * from course;
 45 +------+-------------+---------------+---------+----------+------+
 46 | id   | course_name | course_length | lecturn | category | link |
 47 +------+-------------+---------------+---------+----------+------+
 48 |    1 | mysql_linux |            20 | alex    | basic    | ln   |
 49 |    2 | yongsan_123 |          NULL | NULL    | fly      | NULL |
 50 +------+-------------+---------------+---------+----------+------+
 51 2 rows in set (0.00 sec)
 52 
 53 
 54 mysql> show character set;
 55 +----------+-----------------------------+---------------------+--------+
 56 | Charset  | Description                 | Default collation   | Maxlen |
 57 +----------+-----------------------------+---------------------+--------+
 58 | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
 59 | dec8     | DEC West European           | dec8_swedish_ci     |      1 |
 60 | cp850    | DOS West European           | cp850_general_ci    |      1 |
 61 | hp8      | HP West European            | hp8_english_ci      |      1 |
 62 | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
 63 | latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
 64 | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
 65 | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
 66 | ascii    | US ASCII                    | ascii_general_ci    |      1 |
 67 | ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
 68 | sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
 69 | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
 70 | tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
 71 | euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
 72 | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
 73 | gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
 74 | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
 75 | cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
 76 | gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
 77 | latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
 78 | armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
 79 | utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
 80 | ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
 81 | cp866    | DOS Russian                 | cp866_general_ci    |      1 |
 82 | keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
 83 | macce    | Mac Central European        | macce_general_ci    |      1 |
 84 | macroman | Mac West European           | macroman_general_ci |      1 |
 85 | cp852    | DOS Central European        | cp852_general_ci    |      1 |
 86 | latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
 87 | cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
 88 | cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
 89 | cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
 90 | binary   | Binary pseudo charset       | binary              |      1 |
 91 | geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
 92 | cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
 93 | eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
 94 +----------+-----------------------------+---------------------+--------+
 95 36 rows in set (0.00 sec)
 96 
 97 查看默认字符编码
 98 mysql> show variables like 'collation%';
 99 +----------------------+-------------------+
100 | Variable_name        | Value             |
101 +----------------------+-------------------+
102 | collation_connection | utf8_general_ci   |
103 | collation_database   | latin1_swedish_ci |
104 | collation_server     | latin1_swedish_ci |
105 +----------------------+-------------------+
106 3 rows in set (0.00 sec)
107 
108 mysql> show variables like 'character_set%';
109 +--------------------------+----------------------------+
110 | Variable_name            | Value                      |
111 +--------------------------+----------------------------+
112 | character_set_client     | utf8                       |
113 | character_set_connection | utf8                       |
114 | character_set_database   | latin1                     |
115 | character_set_filesystem | binary                     |
116 | character_set_results    | utf8                       |
117 | character_set_server     | latin1                     |
118 | character_set_system     | utf8                       |
119 | character_sets_dir       | /usr/share/mysql/charsets/ |
120 +--------------------------+----------------------------+
121 8 rows in set (0.00 sec)
122 
123 mysql> create database lcdb default character set utf8 default collate utf8_general_ci;
124 Query OK, 1 row affected (0.00 sec)
125 指定字符编码
View Code

 

posted @ 2015-12-05 23:49  李永三  阅读(189)  评论(0)  编辑  收藏  举报