mysql安裝與配置 · Linux筆記

# 二進制安裝MySQL-5.6.40 ## 0.準備工作-下載mysql ``` https://downloads.mysql.com/archives/community/ mkdir /root/tools -p cd /root/tools wget https://downloads.mysql.com/archives/get/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz ``` ## 1.添加用戶 ``` useradd -s /sbin/nologin -M mysql ``` ## 2.解壓 mysql 二進制包 ``` cd /root/tools tar xf mysql-5.6.40-*-x86_64.tar.gz ```  ## 3.把MySQL 移動到 /apps/ ``` mkdir -p /apps/ mv /root/tools/mysql-5.6.40-*-x86_64 /apps/mysql-5.6.40 ``` ## 4.創建軟連接 ``` ln -s /apps/mysql-5.6.40/ /apps/mysql ``` ## 5.讓MySQL用戶管理 /apps/mysql ``` chown -R mysql.mysql /apps/mysql/data ``` ## 6.安裝這個軟件初始化數據庫 > #1.軟件安裝在哪里 > #2.數據存放在哪里 > #3.MySQL使用的用戶誰？ ``` /apps/mysql/scripts/mysql_install_db --basedir=/apps/mysql --datadir=/apps/mysql/data --user=mysql ``` ``` #####To start mysqld at boot time you have to copy #####support-files/mysql.server to the right place for your system #####mysql啟動腳本默認放在support-files/mysql.server ##### #####記得給MySQL設置個密碼 #####PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! #####To do so, start the server, then issue the following commands: ##### ##### /apps/mysql/bin/mysqladmin -u root password 'new-password' ##### /apps/mysql/bin/mysqladmin -u root -h web01 password 'new-password' ``` ## 7.復制啟動腳本授權 ``` cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld chmod +x /etc/init.d/mysqld ``` ## 8.修改啟動腳本和 mysql命令中的路徑 ``` sed -i 's#/usr/local/mysql#/apps/mysql#g' /apps/mysql/bin/mysqld_safe /etc/init.d/mysqld ``` ## 9.復制默認的配置文件 ``` \cp /apps/mysql/support-files/my-default.cnf /etc/my.cnf /etc/init.d/mysqld start ``` ``` ###故障 ##1./tmp權限 ##2.主機名解析 hosts解析 #ping 主機名 ##3.一步一步執行 ## ##/apps/mysql/bin/mysql ##Welcome to the MySQL monitor. Commands end with ; or \g. ##Your MySQL connection id is 1 ##Server version: 5.5.49 MySQL Community Server (GPL) ## ##Copyright (c) 2000, 2016, 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> ``` ## 10.PATH路徑 ``` echo 'export PATH=/apps/mysql/bin:$PATH' >>/etc/profile source /etc/profile which mysql ``` ## 11.加入開機自啟動 ``` chkconfig --add mysqld chkconfig mysqld on chkconfig | grep mysql ``` ## 12.給MySQL root用戶設置密碼 ``` /apps/mysql/bin/mysqladmin -u root password 'oldboy123' ``` ## 13.重新登錄MySQL數據庫 ``` mysql -uroot -poldboy123 ``` ## 14.數據庫基礎框架 - #1.數據庫 test mysql - #2.表格 ``` #mysql SQL語句 #查看系統中所有數據庫 #show databases; #查看系統中所有的用戶 #使用某一個數據庫 ``` ``` mysql> #查看當前都有啥 mysql> show databases; //******** +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.07 sec) ``` ## 初級查看系列-開始 ``` ##使用某一個數據庫 ###相當于進入 mysql 數據庫中 cd mysql ; cd test #use mysql ``` ``` ##我想查看當前在哪？ pwd 當前正在使用哪個數據庫 select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) ``` ``` ##我是誰？查看當前用戶 select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) ``` ``` ###當前系統都有什么用戶？他們可以在哪里登錄？ ***** select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | web01 | | root | web01 | +------+-----------+ 6 rows in set (0.02 sec) ####初級查看系列-結束 ###show databases; ###select user,host from mysql.user; ``` ## 初級添加刪除系列 ``` #創建數據庫 create database wordpress; #刪除數據庫 drop database wordpress; #添加用戶 grant all on wordpress.* to 'wordpress'@'172.16.1.0/255.255.255.0' identified by '123456'; grant all on wordpress.* to 'wordpress'@'172.16.1.0/255.255.255.0' identified by '123456'; 授權所有的權限, wordpress數據庫所有的權限給 wordpress用戶可以在172.16.1.0/255.255.255.0 網段登錄數據庫這個用戶的密碼123456； #更新系統的權限表 flush privileges; ###進行測試 mysql -uwordpress -p123456 mysql -uwordpress -p -h 172.16.1.8 #刪除用戶 drop user wordpress@'172.16.1.8'; ``` > ###1.查看都有什么數據庫 > ###2.查看都有什么用戶 > ###3.添加用戶 ## help sql語句。 ``` #跳過授權表（不用密碼登錄） #/etc/init.d/mysqld restart --skip-grant-table #mysql 命令行 #-u 指定用戶 #-p 指定密碼（不要有空格） #-h 連接到某一臺服務器 #更改密碼 mysqladmin -uroot -poldboy123 password '新的密碼' ```