Install MySQL from Source Code

Posted on | In | Page View

Install MySQL from Source Code

安裝步驟

基本訊息

設置MySQL目錄

1
2
3
4
5
6
7
8
9
10
[root@hadoop-01 ~]# cd /usr/local
[root@hadoop-01 local]# ls | grep mysql
mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[root@hadoop-01 local]# tar -zxvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[root@hadoop-01 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64 mysql
[root@hadoop-01 local]# vi /etc/profile
## 新增下列兩行在文件中的任一位置，設置MySQL環境變量
export MYSQL_HOME=/usr/local/mysql
export PATH="$MYSQL_HOME/bin:$PATH"
[root@hadoop-01 local]# source /etc/profile

新增Linux的用戶與群組

1
2
3
4
5
6
7
[root@hadoop-01 local]# groupadd -g 101 dba
[root@hadoop-01 local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
[root@hadoop-01 local]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)

複製環境變量配置文件

1
2
3
[root@hadoop-01 local]# cp /etc/skel/.* /usr/local/mysql
cp: omitting directory \`/etc/skel/.\'
cp: omitting directory \`/etc/skel/..\'

建立/etc/my.cnf

MySQL啟動時，會跟據下列順序，讀取第一個存在的配置文件

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. SYSCONFDIR/my.cnf
  4. $MYSQL_HOME/my.cnf
  5. –defaults-extra-file
  6. ~/my.cnf

進行/etc/my.cnf配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
[root@hadoop-01 local]# vi /etc/my.cnf
[client]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
 
[mysqld]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
skip-external-locking
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
query_cache_size= 32M
max_allowed_packet = 16M
myisam_sort_buffer_size=128M
tmp_table_size=32M
table_open_cache = 512
thread_cache_size = 8
wait_timeout = 86400
interactive_timeout = 86400
max_connections = 600
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 32
#isolation level and default engine 
default-storage-engine = INNODB
transaction-isolation = READ-COMMITTED
server-id  = 1
basedir     = /usr/local/mysql
datadir     = /usr/local/mysql/data
pid-file     = /usr/local/mysql/data/hostname.pid
#open performance schema
log-warnings
sysdate-is-now
binlog_format = MIXED
log_bin_trust_function_creators=1
log-error  = /usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
#other logs
#general_log =1
#general_log_file  = /usr/local/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/usr/local/mysql/data/slow_log.err
#for replication slave
#log-slave-updates 
#sync_binlog = 1
#for innodb options 
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:500M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/arch
innodb_log_files_in_group = 2
innodb_log_file_size = 200M
#根據記憶體資源配置
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 50M
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 100
#innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
innodb_locks_unsafe_for_binlog=1
#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on
#case-sensitive file names and separate tablespace
innodb_file_per_table = 1
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

安裝MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
## 修改相關文件的擁有者與權限
[root@hadoop-01 local]# chown mysqladmin:dba /etc/my.cnf 
[root@hadoop-01 local]# chmod 640 /etc/my.cnf
[root@hadoop-01 local]# chown -R mysqladmin:dba /usr/local/mysql
[root@hadoop-01 local]# chmod -R 755 /usr/local/mysql 
[root@hadoop-01 local]# su - mysqladmin
[mysqladmin@hadoop-01 ~]$ mkdir arch  ## 在/etc/my.cnf所設定的Log存放目錄
## 進行安裝
[mysqladmin@hadoop-01 ~]$ scripts/mysql_install_db  --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
-bash: scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory
## 缺少perl
[mysqladmin@hadoop-01 ~]$ sudo yum -y install perl
## 再次進行安裝
[mysqladmin@hadoop-01 ~]$ scripts/mysql_install_db  --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables.../usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
## 此錯誤是因為缺少libaio.so包
[mysqladmin@hadoop-01 ~]$ sudo yum -y install libaio  ## 安裝libaio.so
## 再次進行安裝
[mysqladmin@hadoop-01 ~]$ scripts/mysql_install_db  --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables...2017-09-06 00:11:24 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-09-06 00:11:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK
Filling help tables...2017-09-06 00:11:26 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-09-06 00:11:26 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

配置開機啟動MySQL

1
2
3
4
5
6
7
[mysqladmin@hadoop-01 ~]$ su - ## 切換至root
[root@hadoop-01 ~]# cd /usr/local/mysql
[root@hadoop-01 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql
[root@hadoop-01 mysql]# chmod +x /etc/rc.d/init.d/mysql
[root@hadoop-01 mysql]# chkconfig --del mysql
[root@hadoop-01 mysql]# chkconfig --add mysql
[root@hadoop-01 mysql]# chkconfig --level 345 mysql on

啟動MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@hadoop-01 mysql]# su - mysqladmin
[mysqladmin@hadoop-01 ~]$ bin/mysqld_safe &
[1] 5382
[mysqladmin@hadoop-01 ~]$ 170906 00:30:44 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'.
170906 00:30:44 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
[mysqladmin@hadoop-01 ~]$ ps -ef|grep mysqld  ## 確認MySQL的行程狀態
514        5382   5358  0 00:30 pts/1    00:00:00 /bin/sh bin/mysqld_safe
514        6052   5358  0 00:31 pts/1    00:00:00 grep mysqld
[mysqladmin@hadoop-01 ~]$ netstat -nlp | grep mysql  ## 確認MySQL的網路狀態
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::3306                     :::*                        LISTEN      6025/mysqld    
[mysqladmin@hadoop-01 ~]$ service mysql status  ## 確認MySQL的服務運行狀態
MySQL running (6025)                                       [  OK  ]

若MySQL啟動失敗，可以至$MYSQL_HOME/data/hostname.err下查看日誌。

登入MySQL，進行帳號配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
[mysqladmin@hadoop-01 ~]$ mysql ## 使用空帳號，空密碼登入MySQL
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
mysql> use mysql;   ## MySQL的帳號訊息皆存在此database
Database changed
mysql> update user set password=password('password') where user='root';  ## 設置root帳號的密碼
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> select host, user, password from user;  ## MySQL的帳號訊息皆存在此table
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| hadoop-01 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| ::1       | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| localhost |      |                                           |
| hadoop-01 |      |          |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> delete from user where user='';  ## 刪除空帳號
mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| hadoop-01 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| ::1       | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> flush privileges;
mysql> exit
Bye

使用MySQL root帳號登入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
[mysqladmin@hadoop-01 ~]$ mysql -uroot -h127.0.0.1 -P3306 -ppassword  ## -p後面直接接密碼，不需空格
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; ## 顯示當前用戶有權限訪問的database
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> desc user; ## 顯示table資訊
mysql> show create table user; ## 顯示create database語句
mysql> create database dbname DEFAULT CHARACTER SET utf8;  ## 創建database
Query OK, 1 row affected (0.01 sec)
mysql> grant all on dbname.* TO 'usera'@'%' IDENTIFIED BY 'password'; ## 創建user，設置密碼，允許從所有IP登入，並賦予讀存取db下的所有table權限。可對相同使用者重複設置。
Query OK, 0 rows affected (0.04 sec)
mysql> grant all on dbname.* TO 'usera'@'192.168.16.130' IDENTIFIED BY 'password';  ## 創建user，設置密碼，允許從指定IP登入，並賦予讀存取db下的所有table權限。
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, password from user;
+----------------+-------+-------------------------------------------+
| host           | user  | password                                  |
+----------------+-------+-------------------------------------------+
| localhost      | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| hadoop-01      | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1      | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| ::1            | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| %              | usera | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 192.168.16.130 | usera | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+----------------+-------+-------------------------------------------+
mysql> flush privileges;
mysql> drop user usera@'%';  ## 刪除指定用戶與權限
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, password from user;
+----------------+-------+-------------------------------------------+
| host           | user  | password                                  |
+----------------+-------+-------------------------------------------+
| localhost      | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| hadoop-01      | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1      | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| ::1            | root  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 192.168.16.130 | usera | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+----------------+-------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> delete from user where user='usera';  ## 刪除用戶，但權限會留著
mysql> select host, user, password from user; 
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| hadoop-01 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| ::1       | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)