共计 9651 个字符，预计需要花费 25 分钟才能阅读完成。
修改 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有时候，用 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';本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-04/130153.htm
正文完
星哥玩云-微信公众号
