mariadb

1.数据库部署

yum install mariadb-server -ysystemctl start mariadb

2.安全初始化

vim /etc/my.cnfskip-networking=1     ##关闭数据库开启的网络接口；默认情况下，数据库网络接口是打开的，为了数据库的安全需要关闭网络接口systemctl restart mariadb测试：ss -antlupe

[root@localhost yum.repos.d]# mysql_secure_installation/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not foundNOTE: 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 currentpassword for the root user.  If you've just installed MariaDB, andyou 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):   ##第一次修改密码时因不知道当前mysql的密码可直接按回车键OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [Y/n] Y              ##是否要设定数据库超级用户密码New password:                           ##输入要设定的超级用户密码Re-enter new password:                  ##重新输入Password updated successfully!Reloading privilege tables.. ... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y     ##是否删除匿名用户访问权限 ... Success!Normally, root should only be allowed to connect from 'localhost'.  Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] y    ##是否禁止超级用户通过远程登陆 ... Success!By default, MariaDB comes with a database named 'test' that anyone canaccess.  This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y     ##刷新数据库 - Dropping test database... ... Success! - Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y     ##加载数据库中表格的权限 ... Success!Cleaning up...All done!  If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!

3.数据库的基本sql语句操作
（1）登陆

mysql -uroot -p  ##-u表示用户，后面紧接用户名；-p表示密码，后面可紧跟密码，但是容易造成服务器密码泄露，故建议使用mysql -uroot -p命令登陆服务器

（2）数据库及表的建立

CREATE DATABASE westos;              ##新建westos库CREATE TABLE linux (                 ##新建表格linuxusername varchar(15) not null,       ##表格linux的表头为username，字符长度为15，不能为空password varchar(15) not null        ##表格linux的表头为password，字符长度为15，不能为空 );INSERT INTO linux VALUES ('user1','passwd1');  ##向表格linux插入用户为user1，密码为pasword1

（3）查询

SHOW DATABASES；             ##查询数据库USE mysql                    ##进入数据库mysqlSHOW  TABLES；               ##显示数据库mysql中的表格SELECT *  FROM westos；      ##查询user表中的所有内容（*可以用此表中的任何字段来代替）DESC  linux；                ##查看数据库westos表格linux的表头 

(4)更新数据库信息

UPDATE  linux SET password='666' where username='li';      ##更新用户li的密码为666UPDATE linux SET password='456' where ( username='user1' or username='user2' );##更新user1和user2的密码UPDATE linux SET password='666' where username='li' and age='22'; ##更新用户为liqie年纪为22的用户密码为666

DELETE FROM linux where username='li';               ##删除表格Linux中用户li的数据ALTER TABLE linux ADD age varchar(4);                ##在表格linux中添加表头ageALTER TABLE linux ADD class varchar(5) after password;   ##在表格linux的password后面添加class

（5）删除数据库

DELETE FROM linux where username='xu';   ##删除xu的数据从linux表中DROP TABLE linux;                        ##删除linux表DROP DATABASE westos;                    ##删除westos库

（6）数据库的备份

mysqldump -u root -predhat --all-database       ##备份所有表中的左右数据mysqldump -u root -predhat --all-database --no-data ##备份所有表，但不备份数据mysqldump -u root -predhat westos                  ##备份westos库mysqldump -u root -predhat westos  > /mnt/westos.sql    ##备份westos库并把数据保存到westos.sql中mysqldump -uroot -predhat westos linux > /mnt/linux.sql ##备份westos库中的linux表mysqldump -uroot -predhat westos test > /mnt/test.sql   ##备份westos库中的test表mysql -uroot -predhat -e "create database westos;"  ##建立westos库mysql -uroot -predhat westos < /mnt/westos.sql ##把数据导入westos库(westos库必须存在)

（7）用户授权

CREATE USER westos@'localhost' identified by 'westos';  ##添加本地用户CREATE USER lee@'%' identified by 'lee';        ##建立用户lee，此用户可以通过网络登陆SELECT Host,User FROM mysql.user;       ##查看mysql的用户GRANT SELECT ON WESTOS.* TO westos@localhost;           ##授予本地用户在WESTOS库拥有SELECT权限GRANT SELECT ON WESTOS.* TO westos@'%';SHOW GRANTS FOR westos@localhost;                       ##查看本地用户westos的权限SHOW GRANTS FOR westos@'%';REVOKE SELECT ON WESTOS.* FROM westos@localhost;        ##去掉用户本地westos在WESTOS库的SELECT权限drop user lee@'%'                        ##删除用户