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

# ll -h /sales3306/mysql/data/ibdata1 
-rw-rw----. 1 mysql mysql 12M Mar 31 21:09 /sales3306/mysql/data/ibdata1

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!

innodb_data_file_path=ibdata1:12M;ibdata2:20M:autoextend

/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.

export PATH=$PATH:/usr/local/mysql/bin/

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

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

# 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_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

[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

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)

# 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

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)'

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 |
+------------+-----------+-------------------------------------------+

# 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'

[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>

grant all privileges on *.* to 'multiadmin'@'localhost' identified by '123456';