linux下的mysql 的安裝

學生資訊：
t1：學号 姓名 性别 年齡 家庭住址 籍貫...
t2：學号 專業 選課 教師 ...

關系型資料庫（RDBMS）：oracle mysql db2 sql server sybase...
優點：容易了解、友善使用、資料一緻、讀寫實時
資料一緻：事務
、ATM，輸入密碼
、連接配接資料庫，驗證密碼
、驗證成功後，擷取使用者資訊
、轉賬 
、你的賬戶扣除；同時在對方的賬戶裡增加
、取卡走人

要麼全部成功；要麼全部失敗；
在我們資料庫中一個事務是由很多條sql語句組成，要麼全部執行成功，要麼全部失敗；這樣才能保證資料的一緻性

應用場景：、對資料的一緻性要求比較高（銀行的交易系統）、對資料的實時性要求較高

非關系型資料：【nosql】 Mongdb redis memcahe ..

https://db-engines.com/en/ranking


Mysql資料庫的安裝：
、通過二進制包的方式安裝
rpm：xxx.el6.x86_64.rpm
glibc：Linux所有版本通用的一個二進制的軟體包。也是C的運作庫，幾乎所有的linux系統最底層的api接口，都會依賴glibc。

優點：友善安裝
缺點：不能自定義

、源碼包安裝  xxx.tar.gz 
三步曲 （配置編譯安裝）

優點：靈活，可以自定義
缺點：比較麻煩

、建構自己的rpm包

優點：根據需求定址自己的rpm包，後續友善安裝
缺點：前期的建構時間長，而且麻煩


安裝：
、使用rpm包安裝
> redhat 自己的mysqlrpm包
mysql.x86_64                            -el6                         
mysql-bench.x86_64                      -el6                         
mysql-connector-java.noarch             :-el6                       
mysql-connector-odbc.x86_64             r1144-el6                      
mysql-devel.i686                        -el6                         
mysql-devel.x86_64                      -el6                         
mysql-libs.i686                         -el6                         
mysql-server.x86_64                     -el6                          
mysql-test.x86_64                       -el6                          
php-mysql.x86_64                        -el6                         


# rpm -ql mysql-server
/etc/logrotate.d/mysqld  日志輪轉檔案
/etc/rc.d/init.d/mysqld  啟動腳本
/var/lib/mysql       mysql的預設資料根目錄
/var/log/mysqld.log  日志檔案
/var/run/mysqld      mysql程序目錄

# rpm -ql mysql   
/usr/bin/mysql 用戶端指令
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog  
/usr/bin/mysqlcheck
/usr/bin/mysqldump
/usr/bin/mysqlimport
/usr/bin/mysqlshow 


# /etc/rc.d/init.d/mysqld start
Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
OK   
以上個ok說明資料庫初始化完畢
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h node1.uplook.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

後續配置：
# /usr/bin/mysql_secure_installation   安全配置




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n
 ... skipping.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n  生産環境中一般是不允許遠端通路
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


# netstat -nltp|grep 
tcp               :                :*                   LISTEN      /mysqld

# mysql -p123
mysql> show databases  檢視目前資料庫
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
 rows in set ( sec)

information_schema資料庫：對象資料資訊庫，一般儲存的是其他資料庫的相關資訊；
裡面隻有一個隻讀的表，實際上是一個視圖，不是基本表，無法看到任何的相關檔案
mysql資料庫：核心的庫
test庫：測試庫

後續密碼設定：
、直接使用用戶端工具設定密碼
# mysqladmin -u root password ''  設定新密碼
# mysqladmin -u root password 'newpass' -p123  重新設定密碼
# mysqladmin -u root password 'mysql' -p
Enter password: 

、使用sql語句直接更新表資訊、
mysql> update user set password=password() where user='root' and host='localhost';
Query OK,  row affected ( sec)
Rows matched:   Changed:   Warnings: 

mysql> flush privileges;  重新整理授權表
Query OK,  rows affected ( sec)

或者
mysql> set password for 'root'@'localhost'=password('');  不需要重新整理授權表
Query OK,  rows affected ( sec)

、不知道原來的密碼
> 修改配置檔案
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
如果以上配置檔案有沖突，那麼以最後讀取到的為主
vim /etc/my.cnf
[mysqld]
skip-grant-tables  跳過授權表
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

> 直接以背景安全程序啟動mysql
# /usr/bin/mysqld_safe --skip-grant-tables &



、安裝mysql AB 官方的rpm包

MySQL-server--el6.x86_64.rpm
MySQL-client--el6.x86_64.rpm      MySQL-shared--el6.x86_64.rpm
MySQL-devel--el6.x86_64.rpm       MySQL-shared-compat--el6.x86_64.rpm
MySQL-embedded--el6.x86_64.rpm    MySQL-test--el6.x86_64.rpm

安裝前：
# rpm -e mysql-server mysql mysql-libs --nodeps
# rm -rf /var/lib/mysql/*
# cd /var/lib/mysql/

# rpm -ivh MySQL-server-5.6.19-1.el6.x86_64.rpm

# cat ~/.mysql_secret 
# The random password set for the root user at Thu May 11 11:30:37 2017 (local time): FZVTUxxeTCJx3wSC  初始密碼


# mysql_secure_installation
...

錯誤提示：找不到/var/lib/mysql/mysql.sock
解決：
# service mysql start  3306監聽


注意：pid檔案和sock檔案是在服務啟動後産生的；

錯誤：
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> 
mysql> 
mysql> 
mysql> set password for 'root'@'localhost'=password('111');
Query OK, 0 rows affected (0.00 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
performance_schema：資料庫是mysql 5.5版本以後有的，收集作業系統性能相關的資訊

mysql用戶端工具：
-u:指定使用者
-S:指定sock檔案
-h：指定主機（ip|主機名）
-e：外部執行sql
-P：指定端口
-p：指定密碼


mysql -p  (預設是root使用者從localhost登入)
mysql -u xxx -P xxx -p 

說明：
-p參數後面跟的密碼不能有空格；如果有空格就意味者通路的是參數後面的資料庫


# mysql -p 123
Enter password: 
ERROR 1049 (42000): Unknown database '123'

# mysql -uroot -e 'show databases;' -p
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+


作業：
1、安裝glibc的mysql；一台主機同時啟動多個執行個體

2、預習源碼安裝mysql （參照mysql的官方文檔）