实验步骤
安装和初始化mariadb数据库服务
[root@server ~]# yum install -y mariadb mariadb-server.x86_64
[root@server ~]# systemctl start mariadb.service
[root@server ~]# systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@server ~]# firewall-cmd –add-port=3306/tcp –permanent
success
[root@server ~]# firewall-cmd –reload
success
初始化数据库
设置root管理员在数据库中的密码值（注意，该密码并非root管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键）。
设置root管理员在数据库中的专有密码。
随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
删除默认的测试数据库，取消测试数据库的一系列访问权限。
刷新授权列表，让初始化的设定立即生效。
[root@server ~]# 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):
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
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
… 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] y
… 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] 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
… 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@server ~]# mysql -u root -p
Enter password:
数据库的一些基础命令(！注：任何命令都需要以分号结尾)
MariaDB [(none)]> set password =password=(‘123’); #修改当前用户的密码
MariaDB [(none)]> create user test@localhost identified by ‘123’; #创建一个名为test的用户并给其设置密码
MariaDB [(none)]> grant select,update on mysql.user to test@localhost; #赋予查询和修改权限给test用户
MariaDB [(none)]> revoke select,update on mysql.user from test@localhost; #移除test用户的查询和修改权限
MariaDB [(none)]> show databases; #查看所有数据库
MariaDB [(none)]> create database test; #创建名为test的数据库
MariaDB [(none)]> use test; #切换数据库
MariaDB [test]> show tables; #查看test数据库中的所有表
MariaDB [test]> create table student(id varchar(20),name varchar(20)); #创建一张名为student的表并给表添加两个表项
MariaDB [test]> desc student; #查看student表的表项
MariaDB [test]> insert into student values(“1002″,”xiaoming”); 给表插入数据
MariaDB [test]> select * from student; #查看表中的数据
MariaDB [test]> select id from student where name=”xiaoming”; #有条件的对表进行查询
MariaDB [test]> alter table student modify column name varchar(10); #修改表项的长度
MariaDB [test]> update student set name=’xiaozhang’ where id=’1001′; #修改表中id为‘1001’的name值
MariaDB [test]> delete from student where id=’1001′; #删除表中1001的数据
MariaDB [test]> drop table student; #删除表
MariaDB [mysql]> drop database test; #删除test数据库
MariaDB [mysql]> show variables like ‘%char%’; #查看数据库字符集
MariaDB [mysql]> show engines; #查看数据库存储引擎
MariaDB [mysql]> show variables like “%storage_engine%”; #查看数据库默认的存储引擎
数据库的备份和恢复
mysqldump命令用于备份数据库数据，格式为”mysqldump [参数] [数据库名称]”其中参数与mysql命令大致相同，-u 参数用于定义登录数据库的账户名称，-p参数代表密码提示符
[root@server ~]# mysqldump -u root -p test > /root/root.dump #备份root用户中test数据库并命名为test.dump
导入数据库
MariaDB [(none)]> create database test; #先创建一个空的数据库
[root@server ~]# mysql -u root -p test < /root/root.dump #再将其导入
MySQL数据库字符集设置(有两种方法)
1、编辑/etc/my.cnf配置为文件
[root@server ~]# vim /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8
[root@server ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
character-set-server=utf8
[root@server ~]# vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
default-character-set=utf8
MariaDB [(none)]> show variables like “%char%”; #查看结果
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
2、直接使用命令修改
MariaDB [(none)]> set character_set_client=utf8;
MariaDB [(none)]> set character_set_results=utf8;
MariaDB [(none)]> set character_set_connection=utf8;
MariaDB [(none)]> show variables like “%char%”; #查看结果
mysql密码破解
[root@server ~]# systemctl stop mariadb.service #关闭mysql服务
[root@server ~]# /usr/bin/mysqld_safe –user=mysql –skip-grant-table & #使mysql服务跳过权限方式启动
[1] 18580
[root@server ~]# 190324 19:55:24 mysqld_safe Logging to ‘/var/log/mariadb/mariadb.log’.
190324 19:55:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@server ~]# mysql #直接敲mysql进入数据库
MariaDB [(none)]> use mysql #mysql用户和密码认证信息都存放在mysql库中的user表
MariaDB [mysql]> update user set password=password(‘123′) where user=’root’; #只需要修改mysql相应的密码表项数据即可
MariaDB [mysql]>flush privileges; #刷新权限缓存
[root@server ~]# systemctl restart mariadb.service
索引：
mysql索引可以用来快速的寻找某些特定值得记录，所有mysql索引都以B+树的形式保存
类型：
普通索引(normal)：使用最广泛的索引
唯一索引(unique)：不允许重复的索引，允许有空值
全文索引(full text)：只能用于MyISAM表，主要是用于大量的内容检索
主键索引(primary key)：又称为特殊的唯一索引，不允许有空值
组合索引：可提高mysql效率
MariaDB [test]> alter table student add primary key (id); #创建主键索引
MariaDB [test]> alter table student add unique(id); #创建唯一索引
MariaDB [test]> alter table student add index index_name(id); #创建普通索引，index_name 为索引名，下同
MariaDB [test]> alter table student add fulltext(id); #创建全文索引
MariaDB [test]> alter table student add index index_name(id,name); #创建组合索引
删除索引
MariaDB [test]> alter table student drop primary key; #后加索引类型
MariaDB [test]> alter table student drop index_name; #或加索引名
查看索引
MariaDB [test]> show keys from student; #查看主键索引
MariaDB [test]> show index from student; #查看所有索引
mysql慢查询
慢查询主要用于跟踪异常的sql语句，慢查询日志则是用来记录在mysql中响应时间超过阀值的语句，具体指运行时间超过long _query_time 值得语句，会被记录到慢查询日志当中
mysql默认不开启慢查询日志功能
MariaDB [test]> show variables like “%slow%”; #查看慢查询日志是否开启
MariaDB [test]> show variables like “%long_query%”; #查看超时时间，默认为10s
慢查询的参数：
1、log_slow_queries：慢查询日志功能
2、long_query_time：慢查询超时时间，默认为十秒，mysql5.5以上可以设置微秒
3、slow_query_log：慢查询日志
4、slow_query_log_file：慢查询日志文件
5、slow_launch_time：thread create时间，单位为秒，如果create时间超过超过这个值，该变量的值就会加1
6、
log-queries-not-using-indexes：记录未添加索引的sql语句
开启mysql慢查询日志的方法有两种：
修改配置文件
[root@server ~]# vim /etc/my.cnf
log-slow-queries = /data/mysql/localhost.log
long_query_time = 0.01
long-queries-not-using-indexes
[root@server ~]# systemctl restart mariadb.service
在数据库中执行命令
MariaDB [(none)]> set global slow_query_log=on;
MariaDB [(none)]> show variables like “%slow%”;
