MySQL简单安全管理和故障排查

---

MySQL安全管理

--启动脚本权限修改
[root@mysql 3306]# find /data -type f -name "mysql"
/data/3307/mysql
/data/3306/mysql
[root@mysql 3306]# find /data -type f -name "mysql" -exec chmod 700 {} \;
[root@mysql 3306]# find /data -type f -name "mysql" -exec chown root.root {} \;
[root@mysql 3306]# find /data -type f -name "mysql" -exec ls -ll {} \;
-rwx------. 1 root root 1146 Nov  5 11:23 /data/3307/mysql
-rwx------. 1 root root 1146 Nov  5 11:23 /data/3306/mysql

--登陆MySQL
[root@mysql 3306]# mysql -S /data/3306/mysql.sock  -u root -p

--修改密码
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *43FA3AFC0D22C2D3BB08ADEE0DCE2974611F603A |
| mysql     | root |                                           |
| 127.0.0.1 | root |                                           |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> UPDATE user SET Password=PASSWORD('123456') where  host ='localhost' and user= 'root'; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

--远程登录命令
[root@mysql 3306]# mysql -u root -p123456 -h ip -P3306 

---

故障排查

--启动命令错误 
[root@mysql 3306]# mysql  start
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/application/mysql-5.5.32/tmp/mysql.sock' 
[root@mysql 3306]# which mysql
/application/mysql/bin/mysql


--磁盘空间不足
[root@mysql67 3307]# cat mysql_3307.err 
151104 10:54:01 mysqld_safe Starting mysqld daemon with databases from /data/3307/data
151104 10:54:01 InnoDB: The InnoDB memory heap is disabled
151104 10:54:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151104 10:54:01 InnoDB: Compressed tables use zlib 1.2.3
151104 10:54:01 InnoDB: Initializing buffer pool, size = 200.0M
151104 10:54:01 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
151104 10:54:01  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
151104 10:54:01  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300151104 10:54:01  InnoDB: Error: Write to file ./ib_logfile0 failed at offset 0 

402653184.
InnoDB: 1048576 bytes should have been written, only 135168 were written.
InnoDB: Operating system error number 2.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 2 means 'No such file or directory'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: Error in creating ./ib_logfile0: probably out of disk space
151104 10:54:01 [ERROR] Plugin 'InnoDB' init function returned error.
151104 10:54:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
/application/mysql-5.5.32/bin/mysqld: Error writing file '/data/3307/data/mysql67-slow.log' (Errcode: 28)
151104 10:54:01 [ERROR] Could not use /data/3307/data/mysql67-slow.log for logging (error 28). Turning logging off 

for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server 

and restart it.
151104 10:54:01 [ERROR] Unknown/unsupported storage engine: innodb
151104 10:54:01 [ERROR] Aborting

151104 10:54:01 [Note] /application/mysql-5.5.32/bin/mysqld: Shutdown complete

151104 10:54:01 mysqld_safe mysqld from pid file /data/3307/mysqld.pid ended

---

常用命令小结

--对比文件命令
[root@mysql 3306]# vimdiff my.cnf ../3307/my.cnf

--vi 替换命令
:g/3306/s//3307/g
:%s/3306/3307/g