修改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 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

  1. 开行失败,通过查看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

 

注意:如果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 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

在上述[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_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';

 

网站地图xml地图