当前位置:Gxlcms > mysql > mysql运维之---知识积累_MySQL

mysql运维之---知识积累_MySQL

时间:2021-07-01 10:21:17 帮助过:5人阅读

一、mysql sql处理业务类

1.1、通过生日计算周岁

select date_format(from_days(to_days(now())-to_days('1788-11-26')),'%Y')+0;

1.2、mysqladmin命令修改密码,-h指定数据库服务器的ip

# /usr/mysql/bin/mysqladmin -h 192.168.0.% -uyangsq -p password
Enter password:
/usr/mysql/bin/mysqladmin: connect to server at '192.168.0.%' failed
error: 'Unknown MySQL server host '192.168.0.%' (2)'
Check that mysqld is running on 192.168.0.% and that the port is 3306.
You can check this by doing 'telnet 192.168.0.% 3306'
# /usr/mysql/bin/mysqladmin -h 192.168.0.3 -uyangsq -p password

Enter password:
New password:
Confirm new password:

1.3、关闭多个服务器,必须连接到指定的端口号

# /usr/mysql/bin/mysqladmin --port 3306 shutdown

1.4、在表的某个字段后边添加新的字段名

alter table stuscore add column classid int not null after course;

1.5、多表关联删除多表
delete t1,t2 from class t1 inner join stuscore t2 on t1.classid=t2.classid and t1.classid in(1,2);

1.6、服务器维护许多提供操作相关信息的状态变量。用FLUSH STATUS语句可以将许多状态变量重设为0。

show status like '%thread%';

Threads_connected:当前打开的连接的数量。

Threads_created:创建用来处理连接的线程数。如果Threads_created较大,你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
Threads_running:激活的(非睡眠状态)线程数。

1.7、usage权限,可以创建账户而不授予任何权限,只能够登录,除了内置的test库,对其他库没有任何权限,show databases只能列出information_schema/test。
usage权限不能被回收,也即REVOKE用户并不能删除用户。
mysql> show grants for root@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1 |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
mysql> revoke all privileges,grant option from root@'127.0.0.1';
mysql> show grants for root@'127.0.0.1';
+------------------------------------------+
| Grants for root@127.0.0.1 |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' |
+------------------------------------------+

1.8、shell中-n不为空返回真,使用-n判空,必须考虑把变量用""包含

人气教程排行