修改ibdata1大小的验证以及如何使用mysqld_multi管理多实例

修改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 reportWARNING: my_print_defaults command not found.Please make sure you have this command available andin your path. The command is available from the latestMySQL distribution.ABORT: Can"t find command "my_print_defaults".This command is available from the latest MySQLdistribution. Please make sure you have the commandin your PATH.解决方法：在/etc/profile中添加如下内容：export PATH=$PATH:/usr/local/mysql/bin/并使其生效 source /etc/profile2. 启动失败，通过查看mysqld_multi的错误日志/usr/local/mysql/multi.log 有如下报错信息：Starting MySQL servers160116 20:25:22 mysqld_safe Logging to "/var/log/mariadb/mariadb.log".touch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such file or directorychmod: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directorytouch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such file or directorychown: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directory160116 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 directorytouch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such file or directorychown: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directorychmod: cannot access ‘/var/log/mariadb/mariadb.log’: No such file or directory160116 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 directory160116 20:25:22 mysqld_safe Starting mysqld daemon with databases from /sales3307/mysql/data160116 20:25:23 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid endedmysqld_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 directory160116 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 reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running最后贴一下配置文件：mysqld的部分没有贴，这块配置是公用的[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin#用来做关闭mysql使用user = rootlog=/usr/local/mysql/multi.log##password=""[mysqld3306]port=3306socket=/sales3306/mysql/run/mysql.sockdatadir=/sales3306/mysql/dataserver-id=1003306log-bin=/sales3306/mysql/log/mysql-bintmpdir=/sales3306/mysql/tmp/innodb_log_group_home_dir = /sales3306/mysql/datainnodb_buffer_pool_size=200Mlog-error=/sales3306/mysql/log/error.logpid-file=/sales3306/mysql/run/mysqld.pid[mysqld3307]port=3307socket=/sales3307/mysql/run/mysql.sockdatadir=/sales3307/mysql/dataserver-id=1003307log-bin=/sales3307/mysql/log/mysql-bintmpdir=/sales3307/mysql/tmp/innodb_log_group_home_dir = /sales3307/mysql/datainnodb_buffer_pool_size=100Mlog-error=/sales3307/mysql/log/error.logpid-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 reportReporting MySQL serversMySQL server from group: mysqld3306 is not runningMySQL server from group: mysqld3307 is not running
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf report 3306Reporting MySQL serversMySQL 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 reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL 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_safemysqladmin = /usr/local/mysql/bin/mysqladmin#用来做关闭mysql使用user =multiadminpassword=123456 [root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf start
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL 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 reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL 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 reportReporting MySQL serversMySQL server from group: mysqld3306 is not runningMySQL 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" failederror: "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 -p123456Warning: 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 -umultiadminWelcome to the MySQL monitor.Commands end with ; or g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql> s--------------mysqlVer 14.14 Distrib 5.6.28, for linux-glibc2.5 （x86_64） usingEditLine wrapperConnection id:8Current database:Current user:multiadmin@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:""Using delimiter:;Server version:5.6.28-log MySQL Community Server （GPL）Protocol version:10Connection:Localhost via UNIX socketServer characterset:latin1Db characterset:latin1Client characterset:utf8Conn.characterset:utf8UNIX socket:/sales3307/mysql/run/mysql.sockUptime:32 min 17 secThreads: 1Questions: 22Slow queries: 0Opens: 74Flush tables: 1Open tables: 67Queries 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 -uhelloWelcome to the MySQL monitor.Commands end with ; or g.Your MySQL connection id is 9Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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";本文永久更新链接地址