mariadb常规操作（死锁、mysqldumper备份还原、创建用户并赋权或者删除权限、连接数）

1 死锁：

第一种：
1.查询是否锁表

show OPEN TABLES where In_use > 0

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist
#或者
select * from information_schema.`PROCESSLIST`;

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 线程ID

例子：

查出死锁进程：SHOW PROCESSLIST
杀掉进程          KILL 420821;

其它关于查看死锁的命令：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

2 mariadb的数据备份

2.1 备份数据库

2.3.1.1 脚本备份，保留5天的备份文件，压缩文件

vim back.sh添加如下内容：
mysqldump -h 192.168.180.6 -uroot -p'q1w2E#R$' --single-transaction --databases paas | gzip > /home/backdata/dump_paas_$(date +%Y%m%d).sql.gz
mysqldump -h 192.168.180.6 -uroot -p'q1w2E#R$' --single-transaction --databases hrbm | gzip > /home/backdata/dump_hrbm_$(date +%Y%m%d).sql.gz
rm -rf /home/backdata/dump_*_$(date -d "5 days ago" +%Y%m%d).sql.gz

2.2 设置定时任务任务

2.3.2.1 查看crond情况
查看crontab服务状态：

service crond status

手动启动crontab服务：

service crond start

2.3.2.2 添加crond

crontab -e

添加如下内容：

12 18 * * * /home/mdm/backup.sh

2.3.2.3 列出crontab文件
为了列出crontab文件，可以用：

crontab -l

2.3.2.4 重启crontab服务

service crond restart

2.3.2.5 删除crontab文件
要删除crontab文件，可以用：

  crontab -r

2.3 全库备份还原

1、通过命令导出全部数据

  mysqldump -u root -p --all-databases > /data/db.dump

2、mysql的数据文件都放在/var/lib/mysql/* 中，所有的db信息，以及账号密码信息

  rm -rf /var/lib/mysql/*    #清空mariadb所有数据

3、导入数据库的db文件，在mysql命令行中输入这个命令

source /opt/db.dump

4、第二种方式导入数据，通过mysql命令

 mysql -h ip地址 -uroot -p  数据库名 <  /opt/db.dump  

3 mariadb常用DCL语句

3.1 创建用户并赋予数据库权限

grant all privileges on *.*  to  '%'@'%' 
其中：
*.*   第一个*代表的是数据库，第二个*代表的是表,这里是开放所有的数据库和表，你也可以设置特定的库和表
'%'@'%'  第一个%表示的是用户名，这里代表所有的用户，第二个'%'代表的是主机名或者ip地址，这里也是代表所有主机

还是要记得，修改完权限设置之后，要记得刷新
flush privileges;

create user paas@'%' identified by 'XXX';
--赋予paas用户root用户权限
grant all privileges on *.* to paas@'%' with grant option;
--赋予projreader用户对数据库前缀 dev2_proj_的库的所有表的查询权限
grant select on `dev2_proj_%`.* to  projreader@'%';

create user pfizer@'%' identified by 'XXX';
grant all privileges on dev2_pfizer.* to pfizer@'%';
grant all privileges on dev2_pfizer_activiti.* to pfizer@'%';
grant all privileges on dev2_paas.* to pfizer@'%';
grant all privileges on dev2_paas_system.* to pfizer@'%';
grant all privileges on dev2_quartz.* to pfizer@'%';

grant create,select,insert on dev2_paas.* to pfizer_query@'%' identified by "密码"

查看MySQL用户权限：

show grants for 你的用户;

3.2 修改用户密码

方法一：

 SET PASSWORD FOR 'root'=PASSWORD('newfwk1234');

方法二：

ALTER USER 'user1'@'%' IDENTIFIED BY 'user1paaswordnew';

方法三：

mysql>update mysql.user set password=password('新密码') where User="test" and Host="localhost";
mysql>flush privileges;

3.3 删除权限

REVOKE 语句与 GRANT 对应

• 注：REVOKE 收回权限时只做精确匹配，若找不到记录则报错。而 GRANT 授予权限时可以使用模糊匹配。

1、回收user1对test数据库的all权限

REVOKE ALL PRIVILEGES ON `数据库名`.* FROM '用户名'@'%';

2、查看user1的权限

SHOW GRANTS for 用户名;

SHOW GRANTS for 用户名;

4 mariadb查看连接数（连接总数、活跃数、最大并发数）

当前状态一般查表：information_schema.GLOBAL_STATUS
全局设置一般查表：information_schema.GLOBAL_VARIABLES

查询数据库当前设置的最大连接数

mysql> show variables like '%max_connection%';
或者：
mysql> select * from information_schema.GLOBAL_VARIABLES w where w.VARIABLE_NAME like  '%max_connections%'
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections |       |
| max_connections       | 2512  |
+-----------------------+-------+
2 rows in set (0.00 sec)

#这个设置会马上生效，但是当mysql重启时这个设置会失效，更好的办法是修改mysql的ini配置文件my.ini
mysql> set global max_connections=1000;        重新设置最大连接数

在/etc/my.cnf里面设置数据库的最大连接数

[mysqld]
max_connections = 1000

查看当前连接数、最大并发数

mysql> show global status like 'Threads%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threads_cached          | 29    |
| Threads_connected       | 232    |
| Threads_created         | 16024    |
| Threads_running         | 1     |
+-------------------------+-------+
7 rows in set (0.00 sec)

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected ：这个数值指的是当前打开的连接数，跟show processlist;去掉system user用户的结果相同
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running ：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，相对应的线程也是sleep状态。

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。

Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size的值：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 128   |
+-------------------+-------+
1 row in set (0.00 sec)

查询所有用户的当前连接

命令：show processlist; 

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
show processlist命令只列出前100条，如果想全列出请使用show full processlist;

mysql> show processlist;

status说明

命令：show status;

mysql>show status like '%变量名%';

变量名如下：

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。 
Connections 试图连接MySQL服务器的次数。 
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。 
Delayed_writes 用INSERT DELAYED写入的行数。 
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands 执行FLUSH命令的次数。 
Handler_delete 请求从一张表中删除行的次数。 
Handler_read_first 请求读入表中第一行的次数。 
Handler_read_key 请求数字基于键读行。 
Handler_read_next 请求读入基于一个键的一行的次数。 
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。 
Handler_update 请求更新表中一行的次数。 
Handler_write 请求向表中插入一行的次数。 
Key_blocks_used 用于关键字缓存的块的数量。 
Key_read_requests 请求从缓存读入一个键值的次数。 
Key_reads 从磁盘物理读入一个键值的次数。 
Key_write_requests 请求将一个关键字块写入缓存次数。 
Key_writes 将一个键值块物理写入磁盘的次数。 
Max_used_connections 同时使用的连接的最大数目。 
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。 
Open_tables 打开表的数量。 
Open_files 打开文件的数量。 
Open_streams 打开流的数量(主要用于日志记载） 
Opened_tables 已经打开的表的数量。 
Questions 发往服务器的查询的数量。 
Slow_queries 要花超过long_query_time时间的查询数量。 
Threads_connected 当前打开的连接的数量。 
Threads_running 不在睡眠的线程数量。 
Uptime 服务器工作了多长时间，单位秒。