mariadb数据库服务

什么是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  "\nThe backup successful!"