搜公众号
推荐 原创 视频 Java开发 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库
Lambda在线 > 阿冲吧 > 2019年2月7日 MariaDB数据库

2019年2月7日 MariaDB数据库

阿冲吧 2019-02-07
举报


1.查看数据库

 MariaDB [(none)]> show databases;

2.建立数据库

MariaDB [(none)]> show databases;

MariaDB [(none)]> create database zhangsanfeng;数据库名

Query OK, 1 row affected (0.00 sec)

3.删除数据库

MariaDB [(none)]> drop database zhangsanfeng;数据库名

Query OK, 0 row affected (0.00 sec)

4.创建表

MariaDB [(none)]>use school(数据库名)

MariaDB [school]> create table users (user_name char(20) not null,user_passwd char(30) default '' ,primary key(user_name));

Query OK, 0 rows affected (0.00 sec)

5.修改表(加入数据)

MariaDB[school]>insert into users(user_name,user_passwd)values(‘zhangsan’,password(‘666666’));

Query OK, 1 row affected, 1 warning (0.00 sec)

6.查询表

MariaDB [school]> select * from users;

MariaDB [school]> select * from users where user_name='zhangsan';(查询某一特定数据)

7.查询表的基本结构

MariaDB [school]> describe users;

8.查询表的详细结构

MariaDB [school]> show create table users \G

9.更改表中的数据

MariaDB [school]> update users set user_passwd=password('') where user_name='zhangsan';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

   9.1更改表的数据类型

MariaDB [school]>alter table users modify 属性名(字段名)新的数据类型;

   9.2更改表的字段名

MariaDB [school]>alter table users change 旧属性名(旧字段名) 新属性名(新字段名) 新数据类型;

   9.3更改表名

MariaDB [school]>alter table users(旧表名)rename chong(新表名);

   9.4删除外键表名

MariaDB [school]>alter table users(表名)drop foreign key 外键表名;

10.删除表中的数据

MariaDB [school]> delete from users where user_name='zhangsan';

Query OK, 1 row affected (0.50 sec)

11.删除表

MariaDB [school]> drop table users;

Query OK, 0 rows affected (0.00 sec)

12.查询当前用户

MariaDB [(none)]> select user();

13.查询当前服务器版本

MariaDB [(none)]> select version();

14.查询当前日期

MariaDB [(none)]> select now();

15.查询数据库存储引擎

MariaDB [(none)]> show engines\G;

MariaDB [(none)]> show variables like storage_engine;

16. 修改数据库引擎

MariaDB [(none)]>alter table users engine=MyISAM(新的存储引擎)

在my.cnf文件中

[mysqld]

default-storage-engine=INNODB    修改为需要的存储引擎

数据库存储引擎即表的类型,决定表在计算机中的存储方式。

数据库存储引擎

2019年2月7日 MariaDB数据库
2019年2月7日 MariaDB数据库

17.查询当前所有连接进程信息

MariaDB [(none)]> show full processlist;

18.删除已满的数据库日志信息

在my.cnf中的[mysqld]段下面加入:expire-logs-days=7(设置自动清除7天钱的logs),重启mariadbl;

MariaDB [(none)]>purge binary logs to 'mysql-bin.000003'; #删除bin-log(删除mysql-bin.000003之前的而没有包含mysql-bin.000003)

如果是mysql主从环境的,删除日志,语句格式如下: 

PURGE {MASTER | BINARY} LOGS TO ‘log_name’

PURGE {MASTER | BINARY} LOGS BEFORE ‘date

19.为MYSQL添加一个用户

MariaDB [(none)]>  grant select,insert,update,delete on book.* to test2@localhost identified by 'abc'; #增加test2用户,密码为abc。并只能在localhost这台主机上登录,并且只能访问book这个库中的表,具有查询,插入,更新,删除权限;

语法:mysql> GRANT <权限> ON <库>.<表> TO '用户'@'主机名' identified by '密码';

20. 备份一个数据库

MariaDB [(none)]>mysqldump -u root -p dbname(备份的数据库名)表名 表名 >dbname.sql(备份的数据库文件)

21. 备份多个数据库

MariaDB [(none)]>mysqldump -u root -p --databases dbname1 dbname2 >dbname1.sql(备份的数据文件)

22. 备份全部数据库

MariaDB [(none)]>mysqldump -u root -p --all-databases >all.sql(备份的数据文件)

23. 增量备份

MariaDB [along]>show master status; 查询当前使用的二进制日志

MariaDB [along]>show binlog events in bin-log.000014;显示起始结束编号位置

MariaDB [along]>mysqlbinlog --start-position=314  --stop-position=637 bin-log.000014 > /backup/mysql-along-backup-add-`date +%F-%T`.sql()(备份的文件)

2019年2月7日 MariaDB数据库

 注意:起始的编号一定要比插入命令的编号往前和往后推一个编号,()

24. 继续插入数据,在没备份的情况下删除数据库,模拟误操作()

① 继续日常的操作()

MariaDB [along]> insert into home values(3,'wangjianlin');()

② 误删除along数据库,上一天还没来得及备份()

MariaDB [along]> drop database along;()

这个时候稳住,不要慌,下面开始恢复()

数据恢复()

① 由于最后我们没有备份就删除了数据库,所以我们首先需要保护最后的二进制日志,查看删除操作之前的position编号值()

MariaDB [(none)]> show binlog events in 'bin-log.000014';()

2019年2月7日 MariaDB数据库

备份数据

cd /var/lib/mysql/(https://www.jianshu.com/p/7052b06bd364)

mysqlbinlog --start-position=706 --stop-position=837 bin-log.000014 > /backup/mysql-along-backup-add-`date +%F-%T`.sql(https://www.jianshu.com/p/7052b06bd364)

导入之前的所有备份(https://www.jianshu.com/p/7052b06bd364)

① 查看我们的备份目录(https://www.jianshu.com/p/7052b06bd364)

 

2019年2月7日 MariaDB数据库

② 按顺序导入所有备份()

完全备份 ---> 增量备份()

mysql -uroot -p < mysql-along-backup-2017-11-16-16\:45\:22.sql()

mysql -uroot -p < mysql-along-backup-add-2017-11-16-17\:15\:25.sql()

mysql -uroot -p < mysql-along-backup-add-2017-11-16-17\:27\:50.sql()

查看数据库及数据,恢复完成()

25. 数据还原(还原一个数据库)

MariaDB [(none)]>mysqldump -u root -p dbname <dbname.sql

26. 还原所有的数据库

MariaDB [(none)]>mysqldump -u root -p <all.sql

27. 数据库迁移(相同版本的mariadb数据库迁移)

MariaDB [(none)]>mysqldump -h host1 -u root --password=123456 --all-databases |mysql -h host2 -u root --password=123456 (将host1上root用户的所有数据库迁移到host2上root用户里)

28. 不同版本的mariadb数据库迁移

命令同上,需要注意的是高版本可以兼容低版本,但高版本很难迁移到低版本。

29. 优化数据库性能

MariaDB [(none)]>show status like value; 查询数据库性能

30. 分析查询语句

MariaDB [school]>explain select * from student\G;

MariaDB [school]>describe select * from student\G;

31. 利用索引进行查询

MariaDB [school]>describe select * from student where name=zhangsan\G;

MariaDB [school]>create index index_name  on student(name); 

 MariaDB [school]>describe select * from student where name=zhangsan\G;

32.Mysql复制的基本原理过程如下

(1)Slave上面的IO线程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

(2)Master接收到来自Slave的IO线程的请求后,通过负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息,返回给Slave端的IO线程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息在Master端binary log文件的名称以及在Binary log中的位置;

(3)Slave的IO线程收到信息后,将接收到的日志内容依次写入到Slave端的RelayLog文件(mysql-relay-lin.xxxxx)的最末端,并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”

(4)Slave的SQL线程检测到Relay Log中新增加了内容后,会马上解析该Log文件中的内容成为在Master端真实执行时候的那些可执行的查询或操作语句,并在自身执行那些查询或操作语句,这样,实际上就是在master端和Slave端执行了同样的查询或操作语句,所以两端的数据是完全一样的。

33.mysql主从复制的优点

<1> 如果主服务器出现问题, 可以快速切换到从服务器提供的服务;

<2> 可以在从服务器上执行查询操作, 降低主服务器的访问压力;

<3> 可以在从服务器上执行备份, 以避免备份期间影响主服务器的服务。

新安装MYSQL后怎样提升MYSQL的安全级别

A.修改mysql默认端口

D.root特权账号的处理(建议给root账号设置强密码,并指定只允许本地登录)

E.开启二进制查询日志和慢查询日志

F.mysql安装目录及数据存储目录权限控制:给mysql安装目录读取权限,给mysql日志和数据所在目录读取和写入权限

G.删除无用mysql账号和删除无用的数据库(安装好的mysql默认会有个test库,可将其删除)

35.mysql服务查询慢怎么优化

方法一:

 mysql>show variables like 'long%'; 注:这个long_query_time是用来定义慢于多少秒的才算“慢查询”

mysql> set long_query_time=1; 注: 我设置了1, 也就是执行时间超过1秒的都算慢查询。

mysql> set global slow_query_log='ON' 注:打开日志记录 

 /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。 

long_query_time=1 

slow_query_log_file=/tmp/slow.log 

方法二:mysqldumpslow命令 

复制代码代码如下:

/path/mysqldumpslow -s c -t 10 /tmp/slow-log 

这会输出记录次数最多的10条SQL语句,其中: 

-s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙; 

-t, 是top n的意思,即为返回前面多少条的数据; 

-g, 后边可以写一个正则匹配模式,大小写不敏感的; 

比如 

/path/mysqldumpslow -s r -t 10 /tmp/slow-log 

得到返回记录集最多的10个查询。 

/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log 

得到按照时间排序的前10条里面含有左连接的查询语句。 

36、什么是关系型数据库?什么是非关系型数据库?

  关系型数据库概念:可以理解为一第二维表,每个关系都具有一个关系名,就是通常说的表名,是指爱用了关系模型来组织的数据库

  非关系型数据库:关系型数据库暴露了很多难以克服的问题,而非关系型的数据库则由于其本身的特点得到了非常迅速的发展。NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战,尤其是大数据应用难题。

37.Redis、Memcached和MongoDB优点和局限性?

   Memcached

   Memcached的优点:

   Memcached可以利用多核优势,单实例吞吐量极高,可以达到几十万QPS(取决于key、value的字节大小以及服务   器硬件性能,日常环境中QPS高峰大约在4-6w左右)。适用于最大程度扛量。

   支持直接配置为session handle。

   Memcached的局限性:

   只支持简单的key/value数据结构,不像Redis可以支持丰富的数据类型。  

   无法进行持久化,数据不能备份,只能用于缓存使用,且重启后数据全部丢失。

   无法进行数据同步,不能将MC中的数据迁移到其他MC实例中。

   Memcached内存分配采用Slab Allocation机制管理内存,value大小分布差异较大时会造成内存利用率降低,并引    发低利用率时依然出现踢出等问题。需要用户注重value设计。

 

  Redis

  Redis的优点:

   支持多种数据结构,如 string(字符串)、 list(双向链表)、dict(hash表)、set(集合)、zset(排序set)、hyperloglog(基数估算)

   支持持久化操作,可以进行aof及rdb数据持久化到磁盘,从而进行数据备份或数据恢复等操作,较好的防止数据丢失的手段。

   支持通过Replication进行数据复制,通过master-slave机制,可以实时进行数据的同步复制,支持多级复制和增量复制,master-slave机制是Redis进行HA的重要手段。

   单线程请求,所有命令串行执行,并发情况下不需要考虑数据一致性问题。

   支持pub/sub消息订阅机制,可以用来进行消息订阅与通知。

   支持简单的事务需求,但业界使用场景很少,并不成熟。

Redis的局限性:

   Redis只能使用单线程,性能受限于CPU性能,故单实例CPU最高才可能达到5-6wQPS每秒(取决于数据结构,数据大小以及服务器硬件性能,日常环境中QPS高峰大约在1-2w左右)。

   支持简单的事务需求,但业界使用场景很少,并不成熟,既是优点也是缺点。

   Redis在string类型上会消耗较多内存,可以使用dict(hash表)压缩存储以降低内存耗用。

 Mogodb

   mogodb是一种文档性的数据库。先解释一下文档的数据库,即可以存放xml、json、bson类型系那个的数据。这些数据具备自述性(self-describing),呈现分层的树状数据结构。redis可以用hash存放简单关系型数据。

   mogodb存放json格式数据。

   适合场景:事件记录、内容管理或者博客平台,比如评论系统。

38.Redis两种保存快照的方式是什么?他们有什么区别?

   快照模式和AOF模式

   快照模式:将数据保存在内存中,再保存到磁盘中,性能高,但是可能会有少量数据丢失

   AOF模式:性能差,一致性要求高,可以选用这种方法,一般生产环境两种都开

39.什么是主健?什么是外健?什么是索引?索引的优缺点是什么?

   主健:主关键字(主键,primary key)是被挑选出来,作表的行的惟一标识的候选关键字。一个表只有一个主关    键字。主关键字又可以称为主键。 主键可以由一个字段,也可以由多个字段组成,分别成为单字段主键或多字段主键。 

   外健:表的外键就是 这个字段 是关联着别的表,且是别的表的主键。

   索引:相当于书中的目录 

   优点:加快查询表记录的速度

   缺点:会减慢对表记录的写(insert upadate delete)的速度

   索引会占用物理磁盘空间

   db.frm 保存表结构

   db.MYD 保存表里数据

   db.MYI 保存索引信息文件

40.主健、外健的作用是什么?并说明主健特点?

   

主健作用:1)保证实体的完整性; 

             2)加快数据库的操作速度 

             3)在表中添加新记录时,ACCESS会自动检查新记录的主键值,不允许该值与其他记录的主键值重复。 

             4) ACCESS自动按主键值的顺序显示表中的记录。如果没有定义主键,则按输入记录的顺序显示表中的记录。 

       特点 :

             1) 一个表中只能有一个主键。如果在其他字段上建立主键,则原来的主键就会取消。在ACCESS中,虽然主键不是必需的,但最好为每个表都设置一个主键。 

             2)主键的值不可重复,也不可为空(NULL)。

   外健作用: 使两张表形成关联,外键只能引用外表中的列的值! 

 

41.什么是存储引擎?         

   MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。

   通过选择不同的技术,  你能够获得额外的速度或者功能,从而改善你的应用的整体功能。

   

42.指出MySQL引擎中Innodb与MyIsam的区别,生产环境中怎么选用存储引擎?        

   InnoDB: 支持行锁,支持事务,支持外健,批量插入速度慢,内存使用高,空间使用高,数据可压缩,独享表空间

   MyISAM:支持表锁,不支持事务,不支持外健,批量插入速度快,内存使用低,空间使用低,数据不可压缩,共享表空间

   在一般的既有读又有写的业务中,建议选用Innodb引擎。

   只有读没有写的业务就用MyIsam引擎

 

43.什么是幻读?什么是脏读?什么是不可重复读?

   幻读:是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改这种修改涉及到表中的全部数据行。同时第二个事务也修改这个表中的数据

         这种修改是向表中插入一行新数据。那么以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。

   脏读:脏读就是指当一个事务正在访问数据并且对数据进行了修改而这种修改还没有提交到数据库中,这时另外一个事务也访问这个数据,然后使用了这个数据,实际这个数据并没有提交还不能正常使用

   不可重复读:是指在一个事务内多次读同一数据。在这个事务还没有结束时另外一个事务也访问该同一数据。那么在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的因此称为是不可重复读。 

 

44.什么是事务?什么是回滚?

   事务:你的一次sql操作从开始执行到正确执行结速的这个过程称为事务

   事务回滚:恢复到未操作一切动作前的状态

 

45.事务隔离的四种级别是哪四种?分别代表什么意思?

    未提交读(read uncommitted):会出现脏读、不可重复读和幻读。

    提交读(read committed):会出现不可重复读和幻读。

    重复读(repeatable read):会出现幻读。

    串行化(serializable):隔离级别最高,不允许出现脏读、不可重复读和幻读。

 

46.事务有几大特性?分别代表什么意思?

    事务具有四个特征:原子性( Atomicity )、一致性( Consistency )、隔离性( Isolation )和持续性( Durability )。这四个特性简称为 ACID 特性。

    原子性: 事务是数据库的逻辑工作单位,事务中包含的各操作要么都做,要么都不做 

    一致性: 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时,就说数据库处于一致性状态。如果数据库系统 运行中发生故障,有些事务尚未完成就被迫中断,这些未完成事务对数据库所做的修改有一部分已写入物理数据库,这时数据库就处于一种不正确的状态,或者说是 不一致的状态。 

    隔离性:一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰。 

    持续性:也称永久性,指一个事务一旦提交,它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响

 

47.数据库默认的三个库是什么库?分别代表什么意思?

    information_schema: 主要存储系统中的一些数据库对像信息,如用户信息,列信息,权限信息,字符集信息和分区信息等

    performance_schema: 主要存储数据库服务器性能参数

    mysql:主要存储系统的用户权限信息

    test:该数据库为MySQL数据库管理系统自动创建的测试数据库,任何用户都可以使用

 

48.MySQL锁粒度是什么?MySQL有几种锁级别?说一下他的特性?

    就是通常我们所说的锁级别。

    MySQL有三种锁的级别:页级、表级、行级。

    MySQL这3种锁的特性可大致归纳如下:

    表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

    行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

 

49.说一下数据库的几种备份方式和包启的意思?

    冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;

    温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;

    热备(hot backup):备份的同时,业务不受影响。

 

50.简述如何搭建主从数据库以及工作模式?

搭建:1、主数据库修改my.cnf配置文件,打开log-bin功能,设置server-id,重启服务

     2、在主服务器上授权服户可以从 从服务器 上连接自已,且有拷贝数据的权限

     3、从服务器上修改my.cnf文件,设置server-id,重启服务

     4、在从服务器上使用授权用户测试是否能连接登陆主数据库服务器

     5、从服务器上建立同步账户信息

 

工作模式:从服务器的IO线程连接主数据库的IO线程,并从主服务器获取二进制日志,保存为本地的中继日志,

 接着通过SQL线程执行中继日志里面的SQL语句,从而使主从库保持一致

    

51.主从数据库不能同步怎么办?

     1、通过跳过错误来继续执行同步,适用于对数据一致性不是那么高的情况下

        set global sql_slave_skip_counter =1;

     2、重新做主从,完全同步

        该方法适用于主从库数据相差较大,或者要求数据完全统一的情况

        热备方式修复主从库

        操作过程

        1)先关闭从数据库

        2)记录主库的log_file文件名和位置点

        3)导出主库的数据库,拷贝到从库机器上

        4)从库删除以前的老库,导进从主库拷贝过来的新库

        5)changes主库的Log_file和位置点

        6)开启从库

 

52.怎么清理数据库碎片?

    查看哪个表空间最占用空间

    对大表进行move

   

53.MariaDB集群的功能和优势是什么?

    功能:

    同步复制

    真正的multi-master,即所有节点可以同时读写数据库  

    自动的节点成员控制,失效节点自动被清除

    新节点加入数据自动复制

    真正的并行复制,行级

    用户可以直接连接集群,使用感受上与MySQL完全一致

    优势:

    因为是多主,所以不存在Slave lag(延迟)

    不存在丢失交易的情况

    同时具有读和写的扩展能力

    更小的客户端延迟

    节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的

 

54.数据库有哪几种日志,分别有什么作用?

    二进制日志:该日志文件会以二进制形式记录数据库的各种操作,但是却不记录查询操作

   错误日志:该日志文件会记录MySQL服务器启动、关闭和运行时出错等信息

   慢查询日志:记录执行时间超过指定时间的各种操作,通过工具分析慢查询日志可以定们MySQL服务器性能瓶颈所在。

   通用查询日志:该日志记录MySQL服务器的启动和关闭信息、客户端的连接信息、更新数据记录 SQL语句和查询数据记录SQL语句

 

55、MySQL出现sql锁是怎么回事,如何优化?

    使用show full processlist查看锁表的命令,再确定是sql语句的效率问题,还是没建索引,或是数据库引擎的问题。

    

56、如何优化MySQL?

   1、对查询频繁的表加主键或索引

   2、对重要数据的mysql做主主或主从的高可用与读写分离

   3、对数据量大的表或库,进行分表或分库,减轻总表大小。

4、优化my.cnf在内存参数。提高内存的使用率。

       

版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《2019年2月7日 MariaDB数据库》的版权归原作者「阿冲吧」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注阿冲吧微信公众号

阿冲吧微信公众号:SCTGKE

阿冲吧

手机扫描上方二维码即可关注阿冲吧微信公众号

阿冲吧最新文章

精品公众号随机推荐

举报