MariaDB生存指南-----基础入门

增加新用户

格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"

如，增加一个用户user1密码为password1，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。
首先用以root用户连入mysql，然后键入以下命令：
grant select,insert,update,delete on *.* to 'user1'@'localhost' Identified by "password1";

如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
grant all privileges on *.* to 'alex'@'%' identified by "alex";

刷新权限
FLUSH PRIVILEGES;

查询用户权限

show grants for alex@'%';

字符集问题

查看默认编码格式：
show variables like "%char%";
设置编码格式：
SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_server='utf8';
查看数据库test的编码格式：
show create database test;
设置数据库test的编码格式：
ALTER DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
查看数据表testTable的编码格式:
show create table testTable;
设置数据表testTable的编码格式：
ALTER TABLE `testTable` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 　

禁止用户远程登陆

use mysql;
update user set host = "localhost" where user = "root" and host = "%";
flush privileges;

 

版本升级

https://www.rootusers.com/how-to-upgrade-mariadb-version-10-1-to-10-3/

数据库启动命令汇总

1、使用mysqld启动、关闭MySQL服务
mysqld是MySQL的守护进程，我们可以用mysqld来启动、关闭MySQL服务。

2、使用mysqld_safe启动、关闭MySQL服务
mysqld_safe是一个脚本，一个非常安全的启动、关闭MySQL服务的脚本。它实际上也是调用mysqld来启动、关闭MySQL服务。

3、使用service 启动、关闭MySQL服务
service mysql stop

4、使用/etc/init.d/mysql启动、关闭MySQL服务
/etc/init.d/mysql也是一个脚本，它调用mysqld_safe脚本来启动MySQL服务。

资源限制

对单个账户可以设置的资源限制有
1. 每小时查询次数
2. 每小时更新次数
3. 每小时连接次数
4. 同时在线的连接个数
对应：
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
举例：

grant all privileges on *.* to 'name'@'host' identified by "password" WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;

当账户的限制非0时则会给资源使用计数。
Server运行时给每个账户的使用资源计数，如果达到了连接次数限制则下一个连接将会被拒绝。
同样地若达到了查询、修改等次数限制则会产生一个error信息。
每个账户各自进行资源计数而不是针对客户端。可以全局重置当前的每小时使用的资源计数，也可以针对指定的账户重置计数：
1. 将所有账户计数器清零使用FLUSH USER_RESOURCES语句。重新加载权限表语句也会清零计数器（FLUSH PRIVILEGES或mysqladmin reload命令）
2. 给特定账户清零计数器使用GRANT USAGE语句指定一个与原来一样的限制次数
计数器清零对于max_user_connections无效，系统重启会将所有的计数器清零。

 

参数说明

1、innodb buffer pool

MyISAM是使用操作系统的文件系统缓存来缓存那些经常被查询的数据。然而InnoDB不依赖操作系统的缓存，在InnoDB Buffer Pool处理缓存。

在一个独立的MySQL服务器中，推荐设置innodb-buffer-pool-size为服务器总可用内存的70%-80%。并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

也可以通过如下计算：

show global variables like 'innodb_buffer_pool_size';
show global status like 'Innodb_buffer_pool_pages_data';
show global status like 'Innodb_buffer_pool_pages_total';
show global status like 'Innodb_page_size';

 计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
当结果 < 95% 则减少 innodb_buffer_pool_size，

建议设置大小为： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)

2、tmp_table_size/max_heap_table_size

临时表是指sql执行时生成临时数据表，如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认show variables like "tmpdir";

max_heap_table_size 是MEMORY内存引擎的表大小 ， 因为临时表也是属于内存表所以也会受此参数的限制 所以如果要增加 tmp_table_size 的大小 也需要同时增加 max_heap_table_size 的大小
可以通过 Created_tmp_disk_tables 和 Created_tmp_tables 状态来分析是否需要增加 tmp_table_size

查看状态
show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_tables';

Created_tmp_disk_tables : 磁盘临时表的数量
Created_tmp_tables      : 内存临时表的数量

一般的比例关系是:
Created_tmp_disk_tables/Created_tmp_tables<5%

默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞

3、wsrep_slave_threads

Galera是基于同步复制的，数据传输到各个节点是通过一个类似队列形式，当从节点处理不过来时，就会发生堵塞，这也就是Galera集群的限流。很显然从节点可以开启多个线程来提高处理速度，这就涉及参数wsrep_slave_threads.
wsrep_slave_threads： 本地的执行队列的线程数量，一般为CPU线程数的1-1.5倍

4、max_allowed_packet

最大值是1G(1073741824)，如果设置超过1G，查看最终生效结果也只有1G。

当日志中出现了诸如：“Package for query is too large （xxxxxxxx > 4194304). You can change this value on the server by setting the max_allowed_package variable”的提示的时候，错误的本身就清楚的提示了对应的方式。

原因是 mysql 会限制 server 接受的数据包大小。有时候大的插入和更新超过了 max_allowed_packet 参数限制，会导致写入或者更新失败。于是会出现上面的异常。

5、key_buffer_size

key_buffer_size

作者：安卓软件程序开发入门学习
链接：https://www.imooc.com/article/47132
来源：慕课网

key_buffer_size

作者：安卓软件程序开发入门学习
链接：https://www.imooc.com/article/47132
来源：慕课网

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

 

 

 

wsrep-流量控制

节点从集群接收但尚未应用和提交的事务将保留在接收队列中。
由于不同节点之间执行事务的速度不一样，慢节点的接收队列会越积越长。当接收队列达到一定大小时，节点触发流控，作用就是协调各个节点，保证所有节点执行事务的速度大于队列增长速度。

流控的实现原理很简单：整个Galera集群中，同时只有一个节点可以广播消息，每个节点都会获得广播消息的机会（获得机会后也可以不广播）。当慢节点的接收队列超过一定长度后，它会广播一个FC_PAUSE消息，所有节点收到消息后都会暂缓广播消息，直到该慢节点的接收队列长度减小到一定长度后再恢复复制。

流控相关参数如下：
gcs.fc_limit：接收队列中积压事务的数量超过该值时，流控被触发，缺省值为16。对于Master-Slave模式（只在一个节点写）的Galera集群，可以配置一个较大的值，防止主从复制延迟。对启动多写的Galera集群，较小的值比较合适，因为较大的接收队列长度意味着更多冲突。
gcs.fc_factor：当接收队列长度开始小于 gcs.fc_factor * gcs.fc_limit 时恢复复制，缺省值为1。
gcs.fc_master_slave：Galera集群是否为Master-Slave模式，缺省为no。
查询大小：
show global variables like "wsrep_provider_options"\G