修改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.
Pleasemakesure you have this command available andinyour 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. Pleasemakesure 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 fileor directorychmod: cannot access ‘/var/log/mariadb/mariadb.log’: No such fileor directorytouch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such fileor directorychown: cannot access ‘/var/log/mariadb/mariadb.log’: No such fileor 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 fileor directory/usr/local/mysql/bin/mysqld_safe: line 166: /var/log/mariadb/mariadb.log: No such fileor directorytouch: cannot touch ‘/var/log/mariadb/mariadb.log’: No such fileor directorychown: cannot access ‘/var/log/mariadb/mariadb.log’: No such fileor directorychmod: cannot access ‘/var/log/mariadb/mariadb.log’: No such fileor 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 fileor 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 ended
mysqld_multi logfile 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 fileor 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 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=3306socket=/sales3306/mysql/run/mysql.sock
datadir=/sales3306/mysql/data
server-id=1003306log-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=3307socket=/sales3307/mysql/run/mysql.sock
datadir=/sales3307/mysql/data
server-id=1003307log-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
注意:如果mysqld不是在/usr/local/mysql下,则在每个实例下面还需要添加basedir变量。
在折腾过程中还是蛮多坎坷的,结论就是很个性化的定制,譬如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 3306Reporting 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.00sec)
mysql>flush privileges;
Query OK,0 rows affected (0.00 sec)
-----------------------------------------
最后验证的时候,发现直接将password添加到[mysqld_multi]中,并不能关闭实例
# grep "password" -B 5multi.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=123456stop
[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 onthe command line interface can be insecure.
ERROR1045 (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 idis 8Server version:5.6.28-logMySQL Community Server (GPL)
Copyright (c)2000, 2015, Oracle and/or its affiliates. Allrights reserved.
Oracleis a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarksoftheir respective
owners.
Type'help;' or '\h' for help. Type '\c' to clear the currentinput 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@localhostSSL:Not in use
Currentpager: stdout
Using outfile:''Using delimiter: ;
Server version:5.6.28-logMySQL Community Server (GPL)
Protocol version:10Connection: 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 17sec
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.00sec)
mysql> select user,host frommysql.users;
ERROR1142 (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 idis 9Server version:5.6.28-logMySQL Community Server (GPL)
Copyright (c)2000, 2015, Oracle and/or its affiliates. Allrights reserved.
Oracleis a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarksoftheir respective
owners.
Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.
mysql>
所以,作为mysql安全加固的一部分,这些账号都可以删除的。
总结:如果multiadmin要从本地登录,必须授予localhost的登录权限,%并不包含localhost。
grant all privileges on *.* to 'multiadmin'@'localhost' identified by '123456';