MySQL

MySQL簡介

MySQL原本是一個開放原始碼的關聯式資料庫管理系統，原開發者為瑞典的MySQL AB公司，該公司於2008年被昇陽微系統（Sun Microsystems）收購。2009年，甲骨文公司（Oracle）收購昇陽微系統公司，MySQL成為Oracle旗下產品。

MySQL在過去由於效能高、成本低、可靠性好，已經成為最流行的開源資料庫，因此被廣泛地應用在Internet上的中小型網站中。隨著MySQL的不斷成熟，它也逐漸用於更多大規模網站和應用，比如維基百科、Google和Facebook等網站。非常流行的開源軟體組合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收購後，Oracle大幅調漲MySQL商業版的售價，且甲骨文公司不再支援另一個自由軟體專案OpenSolaris的發展，因此導致自由軟體社群們對於Oracle是否還會持續支援MySQL社群版（MySQL之中唯一的免費版本）有所隱憂，MySQL的創始人麥克爾·維德紐斯以MySQL為基礎，成立分支計劃MariaDB。而原先一些使用MySQL的開源軟體逐漸轉向MariaDB或其它的資料庫。例如維基百科已於2013年正式宣佈將從MySQL遷移到MariaDB資料庫[6]。

關係型資料庫

關聯式資料庫（英語：Relational database），是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的資料。現實世界中的各種實體以及實體之間的各種聯絡均用關係模型來表示。關係模型是由埃德加·科德於1970年首先提出的，並配合“科德十二定律”。現如今雖然對此模型有一些批評意見，但它還是資料儲存的傳統標準。標準資料查詢語言SQL就是一種基於關聯式資料庫的語言，這種語言執行對關聯式資料庫中資料的檢索和操作。

關係模型由關係資料結構、關係操作集合、關係完整性約束三部分組成。

MySQL特性

MySQL是一種使用廣泛的資料庫，特性如下：

使用C和C++編寫，並使用了多種編譯器進行測試，保證原始碼的可移植性
支援AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種作業系統。
為多種程式語言提供了API。程式語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
支援多執行緒，充分利用CPU資源
優化的SQL查詢演算法，有效地提高查詢速度
既能夠作為一個單獨的應用程式應用在客戶端伺服器網路環境中，也能夠作為一個庫而嵌入到其他的軟體中提供多語言支援，常見的編碼如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作資料表名和資料列名
提供TCP/IP、ODBC和JDBC等多種資料庫連線途徑
提供用於管理、檢查、優化資料庫操作的管理工具
可以處理擁有上千萬條記錄的大型資料庫

MySQL應用

與大型資料庫例如Oracle、DB2、SQL Server等相比，MySQL自有它的不足之處，如規模小、功能有限（MySQL Cluster的功能和效率都相對比較差）等，但是這絲毫也沒有減少它受歡迎的程度。對於一般的個人使用者和中小型企業來說，MySQL提供的功能已經綽綽有餘，而且由於MySQL是開放原始碼軟體，因此可以大大降低總體擁有成本。目前Internet上流行的網站構架方式是LAMP（Linux+Apache+MySQL+PHP），即使用Linux作為作業系統，Apache作為Web伺服器，MySQL作為資料庫，PHP作為伺服器端指令碼直譯器。由於Linux+Apache+MySQL+PHP都是自由或開放原始碼軟體（FLOSS)，因此使用LAMP不用花一分錢就可以建立起一個穩定、免費的網站系統。

MySLQ儲存引擎

MySQL儲存引擎簡介

外掛式儲存引擎是MySQL資料庫最重要的特性之一，使用者可以根據應用的需要選擇如何儲存和索引資料庫，是否使用事務等。mySQL預設支援多種儲存引擎，以適應不同領域的資料庫應用需要。使用者可以通過選擇使用不同的儲存引擎提高應用的效率，提供靈活的儲存，使用者設定可以按照自己的需要定製和使用自己的儲存引擎，以實現最大程度的可定製性。 MySQL常用的儲存引擎為MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事務安全表，其他儲存引擎都是非事務安全表。 MyISAM是MySQL的預設儲存引擎。MyISAM不支援事務、也不支援外來鍵，但其訪問速度快，對事務完整性沒有要求。 InnoDB儲存引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是比起MyISAM儲存引擎，InnoDB寫的處理效率差一些並且會佔用更多的磁碟空間以保留資料和索引。MySQL支援外來鍵儲存引擎只有InnoDB，在建立外來鍵的時候，要求附表必須有對應的索引，子表在建立外來鍵的時候也會自動建立對應的索引。

MySQL儲存引擎特性

主要體現在效能、事務、併發控制、參照完整性、快取、故障恢復，備份及回存等幾個方面目前比較普及的儲存引擎是MyISAM和InnoDB，而MyISAM又是絕大部分Web應用的首選。MyISAM與InnoDB的主要的不同點在於效能和事務控制上。 MyISAM是早期ISAM(Indexed Sequential Access Method）的擴充套件實現，ISAM被設計為適合處理讀頻率遠大於寫頻率的情況，因此ISAM以及後來的MyISAM都沒有考慮對事物的支援，不需要事務記錄，ISAM的查詢效率相當可觀，而且記憶體佔用很少。MyISAM在繼承了ISAM優點的同時，與時俱進的提供了大量實用的新特性和相關工具。例如考慮到併發控制，提供了表級鎖。而且由於MyISAM是每張表使用各自獨立的儲存檔案（MYD資料檔案和MYI索引檔案），使得備份及恢復十分方便（拷貝覆蓋即可），而且還支援線上恢復。所以如果應用不需要事務，不支援外來鍵，處理的只是基本的CRUD（增刪改查）操作，那麼MyISAM是不二選擇。

linux (CentOS7.5_x86_64)下安裝mysql8.0

# 下載mysql 

$ wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz

# 解壓
$ mysql tar -zxvf mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz -C /usr/local

# 修改資料夾名稱 
$ mv mysql-8.0.4-rc-linux-glibc2.12-x86_64/ mysql

新增預設配置檔案
$ vim/etc/my.cnf

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data


# 建立mysql組
$ groupadd mysql

# 建立mysql使用者
$ useradd -g mysql mysql

# 建立mysql資料目錄
$ mkdir $MYSQL_HOME/data

# 初始化mysql 
$ /usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/


# 初始化報錯
bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

# 解決方法
yum install -y libaio

# 初始化報錯
2018-07-08T02:53:24.542370Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc) starting as process 17745 ...
mysqld: Can't create/write to file '/tmp/mysql/data/ibd35qXQ' (Errcode: 13 - Permission denied)
2018-07-08T02:53:24.554816Z 1 [ERROR] [MY-011066] InnoDB: Unable to create temporary file; errno: 13
2018-07-08T02:53:24.554856Z 1 [ERROR] [MY-011066] InnoDB: InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.
2018-07-08T02:53:24.555000Z 0 [ERROR] [MY-010020] Data Dictionary initialization failed.
2018-07-08T02:53:24.555033Z 0 [ERROR] [MY-010119] Aborting
2018-07-08T02:53:24.555919Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete.

# 解決辦法：修改/tmp/mysql的目錄許可權
$ chown -R mysql:mysql /tmp/mysql


# 初始化成功
> 如果無異常情況日誌如下可以看到mysql預設會生成root賬號和密碼root@localhost: /TI(mjVAs1Ta

[root@localhost mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2019-01-29T10:19:34.023997Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 4240
2019-01-29T10:19:39.764895Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /TI(mjVAs1Ta
2019-01-29T10:19:43.041419Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed

# 拷貝mysql啟動檔案到系統初始化目錄
$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld


# 啟動mysql伺服器
$ service mysqld start

複製程式碼

mysql 基本操作


# 使用mysql客戶端連線mysql
$ /usr/local/mysql/bin/mysql -u root -p password

修改mysql的預設初始化密碼
> alter user 'root'@'localhost' identified by 'root';

# 建立使用者 CREATE USER '使用者名稱稱'@'主機名稱' INDENTIFIED BY '使用者密碼'
> create user 'jack'@'localhost' identified by 'jack';

# 授予許可權  grant 許可權 on 資料庫.表 to '使用者名稱'@'登入主機'  [INDENTIFIED BY '使用者密碼'];
> grant replication slave on *.* to 'jack'@'localhost';

# 重新整理
# $ flush privileges; 

# 修改root使用者可以遠端連線
> update mysql.user set host='%' where user='root';

# 檢視mysql所用使用者
> select user,host from mysql.user;

# docker 修改mysql的最大連線數
apt-get update
apt-get install vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=1000

> alter user 'root'@'%' identified with mysql_native_password by 'root';

複製程式碼

mysql 叢集主從複製

準備兩臺安裝好的mysql伺服器 192.168.79.15 (master) 192.168.79.16 (slave)

# 配置主服務新增如下配置
$ vim /etc/my.cnf

# 節點唯一id值
server-id=1

# 開啟二進位制日誌
log-bin=mysql-bin

# 指定日誌格式 有mixed|row|statement 推薦mixed
binlog-format=mixed

# 步進值auto_imcrement。一般有n臺主MySQL就填n(可選配置)
auto_increment_increment=2  

# 起始值。一般填第n臺主MySQL。此時為第一臺主MySQL(可選配置)
auto_increment_offset=1   

# 忽略mysql庫(可選配置)
binlog-ignore=mysql   

# 忽略information_schema庫(可選配置)
binlog-ignore=information_schema   

# 要同步的資料庫，預設所有庫(可選配置)
replicate-do-db=db1


# slave 節點配置

# 節點唯一id值
server-id=2

# 開啟二進位制日誌
log-bin=mysql-bin

# 步進值auto_imcrement。一般有n臺主MySQL就填n(可選配置)
auto_increment_increment=2

# 起始值。一般填第n臺主MySQL。此時為第一臺主MySQL(可選配置)
auto_increment_offset=2

# 要同步的資料庫，預設所有庫(可選配置)
replicate-do-db=db1


# 檢視 master 的狀態 , 尤其是當前的日誌及位置
> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1608 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 在slave節點執行如下命令

注意master_log_file 是對應show master status;中file的值，master_log_pos是對應position的值

> change master to
master_host='192.168.79.15',
master_user='root',
master_password='root',
master_log_file='mysql-bin.000009',
master_log_pos=0;

# 啟動 slave  狀態 ( 開始監聽 msater 的變化 )
> start slave;

# 檢視 slave 的狀態 
> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.79.15
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 863
               Relay_Log_File: node-6-relay-bin.000002
                Relay_Log_Pos: 500
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 863
              Relay_Log_Space: 709
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6291c709-23af-11e9-99fb-000c29071862
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0

# 當Slave_IO_Running: Yes和Slave_SQL_Running: Yes都為yes是說明主從複製正常


#重置 slave 狀態 .
$ reset slave;

#暫停 slave 狀態 ;
$ stop slave;

複製程式碼

linux 安裝mysql8.0.x