Recent Comments
Tag Archives: mysql
xxx command denied to user xxx mysql权限管理
感谢原作者 https://www.cnblogs.com/smallrookie/p/7552097.html 今天遇到一个mysql 权限的问题，即标题所述 xxx command denied to user xxx，一般mysql 这种报错，基本都属于当前用户没有进行该操作的权限，需要 root 用户授权才能解决，从网上找了一些资料，感觉这篇写得不错，分享一下： 原文地址：http://www.rainsts.net/article.asp?id=988 可以用 CREATE USER 或 GRANT 创建用户，后者还同时分配相关权限。而 REVOKE 则用于删除用户权限，DROP USER 删除账户。 $ mysql -u root -p password: mysql> create database test; # 创建数据库 Query OK, 1 row … Continue reading
mysql TPS和QPS查询sql
mysql TPS和qps查询sql TPS查询 select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME =’COM_COMMIT’; select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME =’COM_ROLLBACK’; select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME =’UPTIME’; select (@num_com+@num_roll)/@uptime; QPS查询 select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where … Continue reading
MySQL调优之innodb_buffer_pool_size大小设置
MySQL调优之innodb_buffer_pool_size大小设置 转载自 https://blog.csdn.net/sunny05296/article/details/78916775 MySQL调优之innodb_buffer_pool_size大小设置 相关查看命令 sql> show global variables like ‘innodb_buffer_pool_size’; sql> show global status like ‘Innodb_buffer_pool_pages_data’; sql> show global status like ‘Innodb_page_size’; 或 sql> use mysql; sql> select @@innodb_buffer_pool_size; …. MariaDB [(none)]> show global variables like ‘innodb_buffer_pool_size’; +————————-+———–+ | Variable_name … Continue reading
Mysql cannot allocate memory for the buffer pool 解决方法
今天打开网站提示数据库连接失败，吓我一跳，赶紧查日志，果然数据库服务挂了，具体日志如下 190605 8:35:19 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 10509 … 190605 8:35:19 InnoDB: The InnoDB memory heap is disabled 190605 8:35:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins 190605 8:35:19 InnoDB: Compressed tables use zlib 1.2.7 190605 … Continue reading
MySQL高可用–HAProxy+Keepalived+Mycat+MHA集群
转载自https://blog.csdn.net/weixin_43800781/article/details/86690840,并修改了原文上面的一些细节错误,感谢原作者! 1\集群概述 需解决的关键点有： MySQL主从同步及半同步复制 SSH免密登录 MHA集群的搭建 Mycat分片服务 HAProxy负载均衡 Keepalived的高可用 软件版本,除了mysql需要自己下载,其余yum源都有,其实Mysql用mariadb也可以 mysql-5.7.XX java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz haproxy.x86_64 2\准备跳板机 # cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.51 mysql51 192.168.1.52 mysql52 192.168.1.53 mysql53 192.168.1.54 mysql54 192.168.1.55 mysql55 192.168.1.56 mhamanager … Continue reading
MyCat error
copy from https://www.jianshu.com/p/f15d64fcb2f3 启动MyCAT之前，需要先检查一些配置： java的版本需要是1.7或以上； Mysql的配置文件需要加一行lower_case_table_names = 1在[mysqld]栏目中，这个设置为Mysql大小写不敏感，否则可能会发生表找不到的问题； 在示例的2个数据hostM1和hostS1上，新建3个数据库db1,db2,db3，如不新建，可能提示找不到数据库ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0（这个提示不够友好，是在运行很长一段时间后才提示）； ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:1 这个有可能是Mycat和MySQL部署在同一台机器上，而在schema.xml是使用了IP的，但是账号只能使用localhost登陆，所以会出现本地的Mycat无法连接MySQL 添加MYCAT_HOME环境变量指向解压的mycat目录，主要是为了一些bin目录下的脚本的使用。
Msql扩展查询操作练习
–创建测试数据 create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values(’01’ , N’赵雷’ , ‘1990-01-01′ , N’男’) insert into Student values(’02’ , N’钱电’ , ‘1990-12-21′ , N’男’) insert into Student values(’03’ , N’孙风’ , ‘1990-05-20′ , N’男’) insert into … Continue reading
案例：Mysql基本查询练习
+++++++++++++++++++++++++++++++++++++++++++++++++++ 表基本操作练习题 1 复制user表的所有记录到teadb库的teacher表里 只复制user表的表结构给新teadb库的tea2表 mysql> create table teacher select * from teadb; Query OK, 41 rows affected (0.32 sec) Records: 41 Duplicates: 0 Warnings: 0 mysql> select * from teacher; +—-+———————+——+——+——–+———-+——-+——-+—————————————————————–+—————————+—————-+———-+ | id | name | sex | age | s_year | password | uid | gid | comment | homedir | shell | pay | +—-+———————+——+——+——–+———-+——-+——-+—————————————————————–+—————————+—————-+———-+ | 1 | root | boy | 21 | 1990 | x | 0 | 0 | root | /root | /sbin/nologin | 30000.00 | | 2 | bin | boy | 21 | 1990 | x | 1 | 1 | bin | /bin | /sbin/nologin | 5000.00 | | 4 | adm | boy | 21 | 1990 | x | 3 | 4 | adm | /var/adm | /sbin/nologin | 5000.00 | | 5 | lp | boy | 21 | 1990 | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | 5000.00 | | 6 | sync | boy | 21 | 1990 | x | 5 | 0 | sync | /sbin | /sbin/nologin | 5000.00 | | 7 | shutdown | boy | 21 | 1990 | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | 5000.00 | | 8 | halt | boy | 21 | 1990 | x | 7 | 0 | halt | /sbin | /sbin/halt | 5000.00 | | 9 | mail | boy | 21 | 1990 | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | 5000.00 | | 10 | operator | girl | 21 | 1990 | x | 11 | 0 | operator | /root | /sbin/nologin | 10000.00 | | 11 | games | girl | 21 | 1990 | x | 12 | 100 | games | /root | /sbin/nologin | 10000.00 | | 12 | ftp | girl | 21 | 1990 | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | 10000.00 | | 13 | nobody | girl | 21 | 1990 | x | 99 | 99 | Nobody | / | /sbin/nologin | 10000.00 | | 14 | systemd-network | girl | 21 | 1990 | x | 192 | 192 | systemd Network Management | /root | /sbin/nologin | 10000.00 | | 16 | polkitd | girl | 21 | 1990 | x | 999 | 998 | User for polkitd | / | /sbin/nologin | 10000.00 | | 17 | libstoragemgmt | girl | 21 | 1990 | x | 998 | 996 | daemon account for libstoragemgmt | /var/run/lsm | /sbin/nologin | 10000.00 | | 18 | rpc | girl | 21 | 1990 | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin | 10000.00 | | 19 | colord | girl | 21 | 1990 | x | 997 | 995 | User for colord | /var/lib/colord | /sbin/nologin | 10000.00 | | 20 | saslauth | girl | 21 | 1990 | x | 996 | 76 | Saslauthd user | /run/saslauthd | /sbin/nologin | 10000.00 | | 21 | abrt | girl | 21 | 1990 | x | 173 | 173 | | /root | /sbin/nologin | 10000.00 | | 22 | rtkit | girl | 21 | 1990 | x | 172 | 172 | RealtimeKit | /root | /sbin/nologin | 10000.00 | | 23 | radvd | girl | 21 | 1990 | x | 75 | 75 | radvd user | / | /sbin/nologin | 10000.00 | | 24 | chrony | girl | 21 | 1990 | x | 995 | 993 | | /var/lib/chrony | /sbin/nologin | 10000.00 | | 25 | tss | girl | 21 | 1990 | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null | /sbin/nologin | 10000.00 | | 26 | usbmuxd | girl | 21 | 1990 | x | 113 | 113 | usbmuxd user | /root | /sbin/nologin | 10000.00 | | 27 | geoclue | girl | 21 | 1990 | x | 994 | 991 | User for geoclue | /var/lib/geoclue | /sbin/nologin | 10000.00 | | 28 | qemu | girl | 21 | 1990 | x | 107 | 107 | qemu user | /root | /sbin/nologin | 10000.00 | | 29 | rpcuser | girl | 21 | 1990 | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin | 10000.00 | | 30 | nfsnobody | girl | 21 | 1990 | x | 65534 | 65534 | Anonymous NFS User | /var/lib/nfs | /sbin/nologin | 10000.00 | … Continue reading
Day05.Mysql备份与恢复
物理备份 rm -rf /var/lib/mysql/* systemctl stop mysqld rm -rf /var/lib/mysql 把刚才的备份文件拷贝到的当前库下 scp 192.168.4.50:/root/mysql.bak /var/lib/mysql/ 重启正常 重新初始化数据库 1、停数据库服务 systemctl stop mysqld 2、删除库 rm -rf /var/lib/mysql/* 3、重新启动，mysql数据库会自己检测 [root@51 mysql]# grep password /var/log/mysqld.log | tail 2018-09-13T02:33:34.691299Z 0 [Note] Shutting down plugin ‘sha256_password’ 2018-09-13T02:33:34.691305Z … Continue reading
Day04.Mysql查询
复制表 1、把整张表，包括数据，全部复制 mysql> create table tea8 select * from tea7; Query OK, 8 rows affected (0.32 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> select * from tea8; +—-+—–+——–+———+————-+ | id | age | name | sex | hobby | … Continue reading