CentOS7.X原始碼安裝MySQL-5.7.18

qq2233466866發表於2018-06-11
CentOS原始碼MySql

mysql資料庫安裝及配置

  1. 安裝前的準備

    yum install 
vim 
git 
gcc 
gcc-c++ 
wget 
make 
cmake 
automake 
autoconf 
libaio 
libtool 
net-tools 
bison-devel 
libaio-devel 
ncurses-devel 
perl-Data-Dumper 
-y

  2. 下載boost_1_59_0

    cd /usr/local
wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

  3. 下載mysql-5.7.18

    cd /root
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18.tar.gz
tar -zxvf mysql-5.7.18.tar.gz
cd mysql-5.7.18

  4. 執行cmake

    cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DWITH_SYSTEMD=1 
-DDOWNLOAD_BOOST=1 
-DWITH_BOOST=/usr/local 
-DDEFAULT_CHARSET=utf8mb4 

make
make install

  5. 新增mysql使用者

    useradd -s /sbin/nologin -M mysql

  6. 建立資料檔案目錄、所屬組、使用者改為mysql

    mkdir /data/
mkdir /data/mysql/
chown -R mysql:mysql /etc/my.cnf
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /var/*/mysql*
chown -R mysql:mysql /usr/local/mysql/

  7. 修改SELinux

    vim /etc/sysconfig/selinux

SELINUX=permissive

ESC
:wq

setenforce 0

  8. my.cnf詳細配置

    vim /etc/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
datadir=/data/mysql
character-set-server=utf8mb4
init_connect=`SET NAMES utf8mb4`
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

symbolic-links=0
log-error=/var/log/mysqld.log
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
secure_file_priv=/var/lib/mysql-files

server-id=1
binlog-format=Row
log-bin=/data/mysql/mysql-bin
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ESC
:wq

  9. 初始化資料庫配置

    # --initialize-insecure引數為不安全的初始化方案，建議使用--initialize引數
# 以下程式碼執行完之後,會在/data/mysql/mysql-error.log儲存初始密碼

su mysql
su mysql !!!
su mysql !!!

/usr/local/mysql/bin/mysqld 
--initialize 
--user=mysql 
--datadir=/data/mysql 
--basedir=/usr/local/mysql 

# 檢視初始密碼
cat /var/lib/mysql/mysql.log|grep password

# ssh連線mysql,如果對ssh不瞭解,不要這樣做
/usr/local/mysql/bin/mysql_ssl_rsa_setup 
--initialize 
--user=mysql 

vim /etc/my.cnf

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

[mysqld]
port = 3306
slow_query_log = 1
long_query_time = 1
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
log_error = /data/mysql/mysql-error.log
slow_query_log_file = /data/mysql/mysql-slow.log
default_storage_engine = InnoDB

[mysqld_safe]
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysqld.pid

ESC
:wq

  10. 新增mysqld.service

    exit
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/

  11. mysqld.service詳細配置

    vim /usr/lib/systemd/system/mysqld.service

# 將下面的配置覆蓋整個檔案內容
[Unit]
Description=開機啟動MySQL.
After=default.target network.target syslog.target

[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/mysql/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/mysql/mysql.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

[Install]
WantedBy=multi-user.target

ESC
:wq

  12. 啟用mysqld.service

    systemctl enable mysqld
systemctl start  mysqld
systemctl daemon-reload

  13. 測試配置是否正確，沒有任何輸出則表示一切正常

    /usr/local/mysql/bin/mysqld_pre_systemd

  14. 資料檔案所屬使用者修改為mysql（如果‘初始化資料庫配置’時完全按文件進行，可不執行本步驟，不確定則執行）

    chown -R mysql:mysql /data/mysql/

  15. 啟動mysql

    systemctl enable mysqld
systemctl start  mysqld

  16. 新增mysql到mysql使用者環境變數

    su mysql
vim /home/mysql/.bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin

ESC
:wq

source /home/mysql/.bash_profile

  17. 嘗試連線mysql

    # mysql預設使用者為root
mysql -u root -p
password:**************
#修改root[DB使用者]初始密碼
SET PASSWORD=PASSWORD(`**************`);
show databases;
use mysql;
show tables;
select * from user G;

  18. 測試啟動、停止、重啟mysql

    #啟用並啟動
systemctl enable mysqld
systemctl start mysqld
#禁用並關閉
systemctl disable mysqld
systemctl stop mysqld
#啟用並重新啟動
systemctl enable mysqld
systemctl restart mysqld

  19. 設定外部訪問密碼

    # 資料庫名-萬用字元及格式:
    *[允許操作所有資料庫]
    %pay%[允許操作名字包含`pay`的資料庫]
    mydatabase[僅允許操作`mydatabase`資料庫]
# 表名-萬用字元及格式:
    *[允許操作所有表]
    %pay%[允許操作名字包含`pay`的表]
    mytable[僅允許操作`mytable`表]
# ip地址-萬用字元及格式:
    %/0.0.0.0[允許任意IP訪問]
    xxx.xxx.xxx.xxx[僅限公網訪問]
    192.168.x.xxx[僅限區域網訪問]
    localhost/127.0.0.1/::1[僅限本機訪問]

# 資料庫授權格式
GRANT 許可權名 ON 資料庫名.資料表名 TO `資料使用者`@`訪問IP` IDENTIFIED BY `使用者密碼` WITH GRANT OPTION;

# 為任意資料庫表 任意操作 分配root使用者,僅限本地訪問
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY `**************` WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO `root`@`127.0.0.1` IDENTIFIED BY `**************` WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO `root`@`::1` IDENTIFIED BY `**************` WITH GRANT OPTION;

# 為任意資料庫表 任意操作 分配mysql使用者,任意IP訪問(安裝除錯階段、本地開發使用,用後即刪)
GRANT ALL PRIVILEGES ON *.* TO `mysql`@`%` IDENTIFIED BY `**************` WITH GRANT OPTION;

# 為任意資料庫表 增刪改查 分配單獨使用者,為不同的業務分配專屬使用者
GRANT INSERT ON *.* TO `insert1`@`127.0.0.1` IDENTIFIED BY `**************` WITH GRANT OPTION;
GRANT DELETE ON *.* TO `delete1`@`127.0.0.1` IDENTIFIED BY `**************` WITH GRANT OPTION;
GRANT UPDATE ON *.* TO `update1`@`127.0.0.1` IDENTIFIED BY `**************` WITH GRANT OPTION;
GRANT SELECT ON *.* TO `select1`@`127.0.0.1` IDENTIFIED BY `**************` WITH GRANT OPTION;

# 重新整理許可權
FLUSH PRIVILEGES;
  20. 領支付寶紅包支援作者

    掃碼領支付寶紅包


相關文章