什麼是mariadb?
MariaDB資料庫管理系統是MySQL的一個分支，主要由開源社群在
維護，採用GPL授權許可 MariaDB的目的是完全相容MySQL，包括API和
命令列，使之能輕鬆成為MySQL的代替品。在儲存引擎方面，使用XtraDB
（英語：XtraDB）來代替MySQL的InnoDB。 MariaDB由MySQL的
創始人Michael Widenius（英語：Michael Widenius）主導開發，他早
前曾以10億美元的價格，將自己建立的公司MySQL AB賣給了SUN，此後，
隨著SUN被甲骨文收購，MySQL的所有權也落入Oracle的手中。MariaDB
名稱來自Michael Widenius的女兒Maria的名字。
1.Mariadb安裝
1-1安裝mariadb和mariadb-client元件:
# yum groupinstall -y mariadb mariadb-client
1-2啟動mariadb服務:
# systemctl start mariadb ; systemctl enable mariadb
[root@server1 ~]# ss -antple|grep mysql
LISTEN 0 50 *:3306 *:*
users:(("mysqld",2622,13)) uid:27 ino:36479 sk:ffff8800235a0000 <->
1-4編輯/etc/my.cnf檔案,在[mysqld]中加入以下引數:
skip-networking=1
1-5# systemctl restart mariadb
# ss -antlp |grep mysql 此時只允許通過套接字檔案進行本地連線,阻斷
所有來自網路的tcp/ip連線。
2.使用mysql_secure_installation工具進行資料庫安全設定,根據提示完成
操作:
# mysql_secure_installation
[root@server1 ~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
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):
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]
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]
... 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]
... Success!
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]
- 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]
... 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@server1 ~]#
3.登入資料庫:
# mysql -u root -p Enter password: redhat MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> quit
4.資料庫基本操作SQL
show databases; 顯示資料庫 use mysql; 進入資料庫 show tables; 顯示資料庫中的表 desc user; 檢視user表的資料結構 flush privileges; 重新整理資料庫資訊 select host.user,password from user; 查詢user表中的host，user，password欄位 create database westos; 建立westos資料庫 use westos; create table linux( 建立表，username，password欄位 username varchar(15) not null, password varchar(15) not null ); select * from mysql.user; 查詢mysql庫下的user表中的所以 alter table linux add age varchar(4); 新增age欄位到linux表中 alter table linux drop age; alter table linux add age varchar(4) after username; show tables; desc linux; insert into linux values (`user1`,`passwd1`); 在linux表中插入值為username = user1，password = password1 update linux set password=password(`passwd2`) where username=user1; 更新linux表中user1 的密碼為password2 delete from linux where username=user1; 刪除linux表中user1的所以內容
5.mysql 密碼恢復
1)#systemctl stop mariadb
2)#mysql_safe --skip-grant &
3)#mysql
update mysql.user set password=password(`westos`) where user=`root`; 更新mysql.user 表中條件為root使用者的密碼為加密westos 4)killall -9 mysqld_safe ps -aux | grep mysql kill -9 **** 5)systemctl start mariadb
6.使用者和訪問許可權
建立使用者CREATE USER wxh@localhost identified by `westos`; CREATE USER lee@`%` identified by `redhat`;使用者授權GRANT INSERT,UPDATE,DELETE,SELECT on mariadb.* to wxh@localhost; GRANT SELECT on mariadb.* lee@`%`;過載授權表FLUSH PRIVILEGES;檢視使用者授權SHOW GRANTS FOR wxh@localhost;撤銷使用者許可權REVOKE DELETE,UPDATE,INSERT on mariadb.* from wxh@localhost;刪除使用者DROP USER wxh@localhost;
7.備份與恢復
備份# mysqldump -uroot -predhat westos > westos.dump # mysqldump -uroot -predhat --all-databases > backup.dump # mysqldump -uroot -predhat --no-data westos > westos.dump ##只備份框架，不備份資料。恢復# mysqladmin -uroot -predhat create db2 或 mysql -uroot -predhat -e `CREATE DATABASE db2;` # mysql -uroot -predhat db2 < westos.dump
8.網頁管理資料庫
1)裝軟體http,php,php-mysql (phpMyAdmin-3.4.0-all-languages) 2)cd /var/www/html 3)下載解壓php壓縮包, 4)mv phpMyAdmin-3.4.0-all-languages/ myadmin 5)cd myadmin/ 6)cp config.sample.inc.php config.inc.php vim config.inc.php $cfg[`blowfish_secret`] = ``; ==>$cfg[`blowfish_secret`] = `steven`; 7)systemctl restart httpd 8)172.252.254.X/myadmin
資料庫備份指令碼
#!/bin/bash HELLO=$1.`date +%Y-%m-%d`.sql read -p "please input your user name :" NAME read -s -p "please input the user password :" PASSWORD mkdir /mydata &>/dev/null touch /mydata/$HELLO mysqldump -u$NAME -p$PASSWORD $1 >/mydata/$HELLO echo -e " The backup successful!"
本文轉自willis_sun 51CTO部落格，原文連結：http://blog.51cto.com/willis/1850896，如需轉載請自行聯絡原作者