溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一：實驗背景；
在馬哥教育學習到MYSQL這章時，留作業練習的實驗
二：實驗準備；
1、先檢查虛擬機上是否存在mariadb： rpm -qa mariadb*，若有的話，則卸載；
2、通過yum info mariadb查找到mariadb的官網，進去下載mariadb的最新版本mariadb-10.2.9-linux-x86_64.tar.gz；
三：實驗步驟；
1、創建mysql用戶；
[root@localhost ~]# getent passwd mysql #查一下該用戶是否存在，不存在的話，就執行下面的代碼創建 [root@localhost ~]# useradd -d /app/mysqldb -r -m -s /sbin/nologin mysql #創建mysql賬戶并指明家目錄（可以自定義），為了安全起見將shell設為nologin
2、將下載的最新安裝包拷進linux虛擬機，并解壓縮；
[root@localhost ~]# rz #用rz將windows下下載好的安裝包拷進linux [root@localhost ~]# tar xvf mariadb-10.2.8-linux-x86_64.tar.gz -C /usr/local/ #解壓縮(因為是已經編譯好的二進制文件，所以目錄已經被制定好，必須放在/usr/local，故加上-C解壓縮到指定目錄） [root@localhost ~]# cd /usr/local/ #進到這個目錄下面 [root@localhost /usr/local]# ln -s mariadb-10.2.8-linux-x86_64/ mysql #創建軟鏈接（因為名字必須是mysql）
3、創建mysql的配置文件；
[root@localhost /usr/local]# cd /usr/local/mysql/ #進入到剛創建的軟鏈接目錄下 [root@localhost /usr/local/mysql]# mkdir /etc/mysql/ # 因為編譯時默認配置文件放在/etc/mysql下，所以創建一個mysql的目錄 [root@localhost /usr/local/mysql]# ls support-files/ #配置文件已經被準備好，放在了/usr/local/mysql/support-files目錄下 binary-configure mysqld_multi.server magic mysql-log-rotate my-huge.cnf mysql.server my-innodb-heavy-4G.cnf policy my-large.cnf wsrep.cnf my-medium.cnf wsrep_notify my-small.cnf [root@localhost /usr/local/mysql]# cp support-files/my-huge.cnf /etc/mysql/my.cnf #有好幾個，選擇其中一個復制到特定目錄下/etc/mysql/并改名為特定的配置文件名my.cnf [root@localhost /usr/local/mysql]# vim /etc/mysql/my.cnf #編輯配置文件 [mysqld] #在[mysqld]這一項下面加上下面三行，第一行是必須要加的，后面兩行是為了方便管理、性能優化的，可有可無 datadir = /app/mysqldb #指明數據庫存放路徑 innodb_file_per_table = on #用的是innodb引擎，并且使每個表單獨一個文件，方便以后復制單表時方便 skip_name_resolve = on #為了加速訪問速度，忽略反向解析
4、創建系統數據庫；
#數據庫文件已經被準備好的，放置在/usr/local/mysql/scripts/mysql_install_db里 [root@localhost /usr/local/mysql]# scripts/mysql_install_db --user=mysql --datadir=/app/mysqldb #執行上條命令時，只能在/usr/local/mysql下執行，而不能進入到scripts下執行，會報錯的 [root@localhost /usr/local/mysql]# ls /app/mysqldb/ # 驗證一下數據庫是否生成 aria_log.00000001 ib_logfile0 multi-master.info mysql-bin.000003 aria_log_control ib_logfile1 mysql mysql-bin.000004 ib_buffer_pool ibtmp1 mysql-bin.000001 mysql-bin.index ibdata1 localhost.localdomain.pid mysql-bin.000002 performance_schema [root@localhost /usr/local/mysql]#
5、嘗試啟動mysql服務；
[root@localhost /usr/local/mysql]# cp support-files/mysql.server /etc/init.d/mysqld # 將服務腳本放在特定的目錄下 [root@localhost /usr/local/mysql]# chkconfig --list mysqld # 查看一下mysqld服務是否生成，沒生成的話就用下面的命令加上 [root@localhost /usr/local/mysql]# chkconfig --add mysqld [root@localhost /usr/local/mysql]# service mysqld start # 我的這個報錯是因為，我為了做實驗演示，特意將原有的mysql用戶刪除重建，導致/var/log/mariadb目錄沒有了屬主屬組 Restarting mysqld (via systemctl): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details. [FAILED] [root@localhost /usr/local]# chown -R mysql:mysql /var/log/mariadb/ #將/var/log/mariadb/及其子文件的屬主屬組都換成mysql
6、創建日志文件；
我的原本/var/log/mariadb/目錄文件已經存在(才會出現第5中的問題)所以不用創建，沒有這個日志文件的話，在第5步啟動服務時會報錯說沒有找到日志文件/var/log/mariadb，我們只需要創建出來就好了；
[root@localhost /usr/local/mysql]# mkdir /var/log/mariadb/ #先創建出日志文件目錄 [root@localhost /usr/local/mysql]# chown mysql /var/log/mariadb/ #這一步是原先沒有/var/log/mariadb/這個文件，所以創建后需要將其所屬人換成mysql，這樣以后mysql就有權限在其下創建或刪除子文件 [root@localhost /usr/local/mysql]# service mysqld start #再重啟服務 Starting mysqld (via systemctl): [ OK ] [root@localhost /usr/local/mysql]#
7、添加PATH變量；
#因為mysql被放在/usr/local/mysql/bin下，所以可以將其路徑寫到配置文件中，使用時就不用寫絕對路徑了 [root@localhost /usr/local/mysql]# vim /etc/profile.d/mysql.sh PATH=/usr/local/mysql/bin:$PATH [root@localhost /usr/local/mysql]# . /etc/profile.d/mysql.sh #使其生效 [root@localhost /usr/local/mysql]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@localhost /usr/local/mysql]#
8、初始化，跑安全腳本，設置口令密碼；
[root@localhost /usr/local/mysql]# mysql_secure_installation #跑安全腳本 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, 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): #在這里提示輸入口令密碼，因為我們之前并沒有設，所以直接Enter回車 OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y #問是否給root設密碼，跑安全腳本就是為了安全，所以我們當然要設置密碼嘍！ 輸入“y”后回車 New password: #輸入口令密碼 Re-enter new password: #再次輸入(要輸兩邊確認) Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB 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] y #是否移除匿名用戶，為了安全，我們輸入“y”，將匿名用戶移除，不讓其登錄 ... Success! 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 #是否禁用遠程登錄，不禁止，故輸入“n”，回車 ... skipping. By default, MariaDB 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 #是否移除test數據庫文件，“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 # 是否重新加載，即是否立即使以上的配置生效，當然要啊，所以“y” ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!
四：實驗測試；
[root@localhost /usr/local/mysql]# mysql -uroot -p #因為我們設置過口令了，所以就要加上密碼登錄，可以“-p密碼”，也可以直接 -p 后回車再輸入密碼，后者比較安全 Enter password: #輸入密碼 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 24 Server version: 10.2.9-MariaDB-log MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; #顯示數據庫文件信息，而且從“[(none)]”看出現在我們并沒有在哪個特定的數據庫中 +--------------------+ | Database | +--------------------+ | #mysql50#.mozilla | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.01 sec) MariaDB [(none)]> use mysql #設置指定數據庫為當前數據庫（注意：此處是個mysql的內部命令，后面不用加分號，加分號的都是SQL語句） Database changed
MariaDB [mysql]> show tables; #顯示當前數據庫中的所有表信息 +---------------------------+ | Tables_in_mysql | +---------------------------+ | column_stats | | columns_priv | | db | | event | | func | | general_log | | gtid_slave_pos | | help_category | | help_keyword | | help_relation | | help_topic | | host | | index_stats | | innodb_index_stats | | innodb_table_stats | | plugin | | proc | | procs_priv | | proxies_priv | | roles_mapping | | servers | | slow_log | | table_stats | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 30 rows in set (0.01 sec)
MariaDB [mysql]> desc user; #查看user表的表結構，名字、類型、值、....（內容只顯示了前面的一部分）
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.01 sec)
MariaDB [mysql]>select host,user,password from user; #從user表中查找host/user/password信息（如下圖所示）
+-----------------------+------+-------------------------------------------+
| host | user | password |
+-----------------------+------+-------------------------------------------+
| localhost | root | *367DDCBB0F311F04DDA795BC971B23954EF636DD |
| localhost.localdomain | root | *367DDCBB0F311F04DDA795BC971B23954EF636DD |
| 127.0.0.1 | root | *367DDCBB0F311F04DDA795BC971B23954EF636DD |
| ::1 | root | *367DDCBB0F311F04DDA795BC971B23954EF636DD |
+-----------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)MariaDB [mysql]> select user(); #查看當前用戶名 +----------------+ | user() | #注意，mysql中的用戶名和平常所說的用戶名不太一樣， +----------------+ mysql中的用戶名是由“用戶名@本機（或者ip地址）”共同組合而成的全稱 | root@localhost | +----------------+ 1 row in set (0.00 sec) # 注意，對于SQL語句，后面都要加上分號，否則回車后不執行，一直到你輸入分號為止（如下所示） MariaDB [(none)]> show databases -> -> ; +--------------------+ | Database | +--------------------+ | #mysql50#.mozilla | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec MariaDB [mysql]> quit #quit退出 Bye [root@localhost /usr/local/mysql]#
五：實驗總結；
這個實驗其實步驟很簡單，主要是將代碼敲對就OK了，在試驗中，有的同學在配置文件my.conf中的那三行代碼打錯了，導致了一些問題出現，根據步驟一步一不做，然后出錯的時候就按照步驟去排查。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
