MySQL日常管理

2017年12月19日

#

MySQL日常管理

###1.查看当前sock文件在哪里,可以使用下面的命令

执行 mysql --help | grep socket 命令
--protocol=name     The protocol to use for connection (tcp, socket, pipe,
-S, --socket=name   The socket file to use for connection.
                    The buffer size for TCP/IP and socket communication.
socket                            /tmp/mysql.sock

###2.新建MySQL用户命令

grant all on t1.t to 'test'@'116.247.80.22' identified by 'passwd';
grant all on *.* to  monitor@'10.104.203.62' identified by '123456789';

###3.强制杀掉mysqld进程命令

ps aux|grep mysqld | grep -v grep | awk '{print $2}' | xargs kill -9 

###4.强制某个MySQL用户使用ssl连接数据库

4.1 生成秘钥命令:

/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/cert/conf/mysql/ --uid=mysql --suffix=test

4.2 建立需要使用ssl key连接的账号

grant all on t1.t to 'fang'@'%' identified by '123456789' REQUIRE ssl;
CREATE USER 'some_user'@'%' IDENTIFIED BY '<autogenerated password here>';
GRANT ALL PRIVILEGES ON dbname.* TO 'some_user'@'%' REQUIRE SSL;

###5.查看my.cnf文件路径
mysql –help | grep my.cnf

###6.tcpdump使用
tcpdump -i eth0 -s 0 -l -w – dst port 3306

###7.MySQL权限管理
CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;
GRANT SELECT ON db2.invoice TO ‘jeffrey’@’localhost’;
ALTER USER ‘jeffrey’@’localhost’ WITH MAX_QUERIES_PER_HOUR 90;

###8.Centos7修改系统最大文件描述符
8.1设置系统最大描述符
cat /proc/sys/fs/file-max
sysctl fs.file-max
echo 655300 > /proc/sys/fs/file-max
sysctl fs.file-max
sysctl -w fs.file-max=655350
echo fs.file-max=524288 >> /etc/sysctl.conf
sysctl -p

 8.2设置用户级别的限制
 计算 fdmax = 物理内存大小(m为单位) / 4 * 256  假设内存为8G,fdmax=524288 
 执行命令: echo fs.file-max=524288 >> /etc/sysctl.conf
 执行命令: sysctl -p  
 执行命令: echo  * soft nofile  524286 >> /etc/security/limits.conf 
 执行命令: echo  * hard nofile 524287 >> /etc/security/limits.conf 

发表评论

电子邮件地址不会被公开。 必填项已用*标注