你的位置:首页 > 数据库

[数据库]修改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/profile

2. 启动失败,通过查看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)