修改ibdata1大小的驗證
ibdata是共享表空間，在MySQL初始化的時候就生成了。
但很多童鞋會看到網上各種大神的調優建議，在MySQL已經初始化的情況下，修改配置文件中innodb_data_file_path=ibdata1:12M:autoextend。導致MySQL啟動的時候報錯。
下面來模擬一下：
原來的ibdata1大小為12M
# ll -h /sales3306/mysql/data/ibdata1 -rw-rw----. 1 mysql mysql 12M Mar 31 21:09 /sales3306/mysql/data/ibdata1
修改配置文件中的innodb_data_file_path參數，調整其大小
innodb_data_file_path=ibdata1:20M:autoextend
重啟數據庫服務
啟動數據庫的過程中沒有報錯，但就是沒有起來，查看日志信息
[ERROR] InnoDB: auto-extending data file /sales3306/mysql/data/ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 1280 pages, max 0 (relevant if non-zero) pages!
報錯信息其實很明顯，ibdata1實際大小和配置文件中指定的大小不一致。
遇到這種問題，如何修復呢？
其實，只需將該參數設置為等於或者小於其實際大小。
關於等於，其實不難理解，畢竟要吻合，那小於又為什麼可以呢？關鍵在於該參數後面的autoextend選項，所以實際值比初始值大很正常。當然小於的情況只適用於帶有autoextend選項的表空間，MySQL可指定多個表空間，但只有最後一個才能指定該選項。
獲取其實際大小，有兩種方式，
一是通過ls -l查看其具體大小，可直接寫12582912（12M）
二是通過錯誤日志的報錯信息，譬如上面很容易算出其實際大小為768*16/1024=12M
當然，如果默認的共享表空間體積太大了，可新增一個表空間
innodb_data_file_path=ibdata1:12M;ibdata2:20M:autoextend
注意：共享表空間，即便把數據清理掉後，也不會回收空間，只能遷移數據，重新初始化。
mysqld_multi的使用
1. 在執行mysqld_multi時報以下錯誤：
/usr/local/mysql/bin/mysqld_multi --defaults-file=/root/multi.cnf report WARNING: my_print_defaults command not found. Please make sure you have this command available and in your path. The command is available from the latest MySQL distribution. ABORT: Can't find command 'my_print_defaults'. This command is available from the latest MySQL distribution. Please make sure you have the command in your PATH.
解決方法：
在/etc/profile中添加如下內容：
export PATH=$PATH:/usr/local/mysql/bin/
並使其生效 source /etc/profile
2. 啟動失敗，通過查看mysqld_multi的錯誤日志/usr/local/mysql/multi.log
有如下報錯信息：
Starting MySQL servers 160116 20:25:22 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. touch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such file or directory chmod: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directory touch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such file or directory chown: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directory 160116 20:25:22 mysqld_safe Logging to '/sales3307/mysql/log/.err'. 160116 20:25:22 mysqld_safe Starting mysqld daemon with databases from /sales3306/mysql/data /usr/local/mysql/bin/mysqld_safe: line 129: /var/log/mariadb/mariadb.log: No such file or directory /usr/local/mysql/bin/mysqld_safe: line 166: /var/log/mariadb/mariadb.log: No such file or directory touch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such file or directory chown: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directory chmod: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directory 160116 20:25:22 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended /usr/local/mysql/bin/mysqld_safe: line 129: /var/log/mariadb/mariadb.log: No such file or directory 160116 20:25:22 mysqld_safe Starting mysqld daemon with databases from /sales3307/mysql/data 160116 20:25:23 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended mysqld_multi log file version 2.16; run: Sat Jan 16 20:25:24 2016
。。。怎麼會出現mariadb的信息，這個跟我的操作系統有關，CentOS 7，默認的數據庫是Mariadb，而不是MysQL。
失敗原因：
沒有指定錯誤日志
指定錯誤日志後，log-error=/sales3307/mysql/log/error.log
重新啟動，又報如下錯誤：
2016-01-16 20:41:09 18683 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mariadb/mariadb.pid' (Errcode: 2 - No such file or directory) 2016-01-16 20:41:09 18683 [ERROR] Can't start server: can't create PID file: No such file or directory 160116 20:41:10 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
失敗原因：沒有指定pid文件
指定pid文件的路徑
pid-file=/sales3307/mysql/run/mysqld.pid
重新啟動，終於啟動成功
# mysqld_multi --defaults-file=/root/multi.cnf report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running
最後貼一下配置文件：
mysqld的部分沒有貼，這塊配置是公用的
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin #用來做關閉mysql使用 user = root log=/usr/local/mysql/multi.log ##password='' [mysqld3306] port=3306 socket=/sales3306/mysql/run/mysql.sock datadir=/sales3306/mysql/data server-id=1003306 log-bin=/sales3306/mysql/log/mysql-bin tmpdir=/sales3306/mysql/tmp/ innodb_log_group_home_dir = /sales3306/mysql/data innodb_buffer_pool_size=200M log-error=/sales3306/mysql/log/error.log pid-file=/sales3306/mysql/run/mysqld.pid [mysqld3307] port=3307 socket=/sales3307/mysql/run/mysql.sock datadir=/sales3307/mysql/data server-id=1003307 log-bin=/sales3307/mysql/log/mysql-bin tmpdir=/sales3307/mysql/tmp/ innodb_log_group_home_dir = /sales3307/mysql/data innodb_buffer_pool_size=100M log-error=/sales3307/mysql/log/error.log pid-file=/sales3307/mysql/run/mysqld.pid
在折騰過程中還是蠻多坎坷的，結論就是很個性化的定制，譬如socket，log-error，pid-file都要指定各自的路徑。不然啟動過程中，系統會按默認的來，多實例都按默認的來，會起沖突的。但整個排錯過程還是蠻簡單的，不是看mysqld_multi的錯誤日志，就是實例本身的日志。
下面演示一下，mysqld_multi的用法：
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf stop
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report Reporting MySQL servers MySQL server from group: mysqld3306 is not running MySQL server from group: mysqld3307 is not running
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report 3306 Reporting MySQL servers MySQL server from group: mysqld3306 is not running
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf start 3306
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is not running
在上述[mysqld_multi]的配置中，有個user和password，這個是用來執行mysqladmin關閉程序的。
有時候，用root權限太大，且密碼以明文的方式暴露存在安全風險。
雖然mysqld_multi支持--password選項，但如果兩個實例的密碼不一樣，又如何同時關閉實例呢？
可為兩個實例創建同名賬戶，只賦予shutdown權限，這樣可解決上述的困擾。
mysql> grant shutdown on *.* to 'multiadmin'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
-----------------------------------------
最後驗證的時候，發現直接將password添加到[mysqld_multi]中，並不能關閉實例
# grep "password" -B 5 multi.cnf [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin #用來做關閉mysql使用 user =multiadmin password=123456
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf start
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf stop
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf --password=123456 stop
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report Reporting MySQL servers MySQL server from group: mysqld3306 is not running MySQL server from group: mysqld3307 is not running
直接使用stop並不能關閉實例，但是在客戶端指定password卻又可以，用mysqladmin關閉又沒問題。
直接使用stop關閉，multi日志報如下信息：
Warning: Using a password on the command line interface can be insecure. ^G/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'multiadmin'@'localhost' (using password: YES)'
當然，[mysqld_multi]中除了用mysqld_safe啟動mysql實例外，也可直接通過mysqld，這時[mysqld3306]，[mysqld3307]中需指定user=mysql。
------------------------------------------------
在驗證的過程中，發現給multiadmin如下授權
GRANT ALL PRIVILEGES ON *.* TO 'multiadmin'@'%' IDENTIFIED BY PASSWORD
%並不包括localhost
驗證如下：
mysql> select user,host,password from mysql.user; +------------+-----------+-------------------------------------------+ | user | host | password | +------------+-----------+-------------------------------------------+ | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | spark01 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | spark01 | | | multiadmin | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------------+-----------+-------------------------------------------+
在本機用multiadmin登錄
# mysql --socket=/sales3307/mysql/run/mysql.sock -umultiadmin -p123456 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'multiadmin'@'localhost' (using password: YES)
但是不輸入密碼卻又能登錄，只是沒有任何權限
[root@spark01 ~]# mysql --socket=/sales3307/mysql/run/mysql.sock -umultiadmin Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.6.28, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 8 Current database: Current user: multiadmin@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.28-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /sales3307/mysql/run/mysql.sock Uptime: 32 min 17 sec Threads: 1 Questions: 22 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 67 Queries per second avg: 0.011 -------------- mysql> select user(); +----------------------+ | user() | +----------------------+ | multiadmin@localhost | +----------------------+ 1 row in set (0.00 sec) mysql> select user,host from mysql.users; ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'users'
這個其實與上面權限表中的user為空，host為localhost有關。
只要是從本地登錄，不管你指定任何權限表之外的用戶，都可以登錄，譬如hello用戶在權限表中並不存在，卻依然可以登錄，就是拜user為空，host為localhost所賜。
[root@spark01 ~]# mysql --socket=/sales3307/mysql/run/mysql.sock -uhello Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
所以，作為mysql安全加固的一部分，這些賬號都可以刪除的。
總結：如果multiadmin要從本地登錄，必須授予localhost的登錄權限，%並不包含localhost。
grant all privileges on *.* to 'multiadmin'@'localhost' identified by '123456';
