Modify the ibdata1 size verification and how to use mysqld_multi to manage multiple instances and mysqldmulti

Modify the ibdata1 size verification and how to use mysqld_multi to manage multiple instances and mysqldmulti

Verification of modifying the ibdata1 size

Ibdata is a shared tablespace that is generated during MySQL initialization.

However, many children's shoes will see the tuning suggestions from various online experts. when MySQL has been initialized, modify innodb_data_file_path = ibdata1: 12 M: autoextend in the configuration file. An error is reported when MySQL is started.

The following is a simulation:

The original ibdata1 size is 12 Mb.

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

Modify the innodb_data_file_path parameter in the configuration file and resize it.

innodb_data_file_path=ibdata1:20M:autoextend

Restart Database Service

No error is reported when the database is started, but it is not found. view the log information.

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

The error message is obvious. The actual size of ibdata1 is different from that specified in the configuration file.

How can we fix this problem?

In fact, you only need to set this parameter to equal to or smaller than its actual size.

It is not difficult to understand the equality. After all, we need to make a consistent decision. Why is it less than that? The key lies in the autoextend option next to this parameter, so the actual value is much larger than the initial value. Of course, the smaller value is only applicable to tablespaces with the autoextend option. MySQL can specify multiple tablespaces, but only the last one can specify this option.

Obtains the actual size in two ways,

First, you can use ls-l to view the specific size. You can directly write 12582912 (12 M)

Second, the error message in the error log. For example, it is easy to calculate the actual size as 768*16/1024 = 12 M.

Of course, if the size of the default shared tablespace is too large, you can add a new tablespace.

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

Note: even after the data is cleared, the shared tablespace does not recycle the space. You can only migrate and reinitialize the data.

 

Use of mysqld_multi

1. the following error is reported when mysqld_multi is executed:

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

Solution:

Add the following content to/etc/profile:

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

And make it take effect source/etc/profile

2. Failed to start. view the error log/usr/local/mysql/multi. log of mysqld_multi.

The following error message is displayed:

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

... How can mariadb information appear? This is related to my operating system. For CentOS 7, the default database is Mariadb, not MysQL.

Cause of failure:

No error log specified

After the error log is specified, log-error =/sales3307/mysql/log/error. log

Restart and the following error is reported:

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

Cause of failure: No pid file specified

Path of the pid File

Pid-file =/sales3307/mysql/run/mysqld. pid

Restart and start successfully

# mysqld_multi --defaults-file=/root/multi.cnf reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running

Finally, paste the configuration file:

The mysqld part is not pasted. This configuration is public.

[Mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladmin # used to disable mysql using user = rootlog =/usr/local /mysql/multi. log # password = ''[mysqld3306] port = 3306 socket =/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/login 200Mlog-error =/sales3306/mysql/log/error. logpid-file =/sales3306/mysql/run/mysqld. pid [mysqld3307] port = 3307 socket =/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/login 100Mlog-error =/sales3307/mysql/log/error. logpid-file =/sales3307/mysql/run/mysqld. pid

 

During the tossing process, there were still a lot of ups and downs. The conclusion was that the customization was very personalized, such as socket, log-error, and pid-file. Otherwise, the system will use the default value during the startup process, and multiple instances will use the default value, causing a conflict. However, the troubleshooting process is quite simple, not the error log of mysqld_multi, or the log of the Instance itself.

 

The usage of mysqld_multi is as follows:

[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

In the above [mysqld_multi] configuration, there is a user and password, which is used to execute mysqladmin to close the program.

Sometimes, the root permission is too large, and the password is exposed in plaintext mode, there is a security risk.

Although mysqld_multi supports the -- password option, if the passwords of the two instances are different, how can I disable the instance at the same time?

You can create accounts with the same name for two instances and only grant the shutdown permission to these instances.

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)

-----------------------------------------

During the final verification, we found that the password was directly added to [mysqld_multi] And the instance cannot be closed.

# Grep "password"-B 5 multi. cnf [mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladmin # used to disable mysql using user = multiadminpassword = 123456

 

[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf start
[root@spark01 ~]# mysqld_multi --defaults-file=/root/multi.cnf reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL 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 reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL 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 reportReporting MySQL serversMySQL server from group: mysqld3306 is not runningMySQL server from group: mysqld3307 is not running

If you use stop directly, the instance cannot be closed, but you can specify the password on the client. It is okay to use mysqladmin to close the instance.

Use stop directly to disable it. The multi log reports the following information:

Warning: Using a password on the command line interface can be insecure.^G/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failederror: 'Access denied for user 'multiadmin'@'localhost' (using password: YES)'

 

Of course, in [mysqld_multi], apart from using mysqld_safe to start mysql, you can also directly use mysqld. In this case, [mysqld3306] and [mysqld3307], you must specify user = mysql.

------------------------------------------------

During verification, the following authorization is found for multiadmin:

Grant all privileges on *. * TO 'multiadmin' @ '%' IDENTIFIED BY PASSWORD

% Does not include localhost

The verification is as follows:

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

Log on to the machine with multiadmin

# mysql --socket=/sales3307/mysql/run/mysql.sock -umultiadmin -p123456Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'multiadmin'@'localhost' (using password: YES)

However, you can log on again without entering the password, but you do not have any permissions.

[root@spark01 ~]# mysql --socket=/sales3307/mysql/run/mysql.sock -umultiadminWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 wrapperConnection id:        8Current database:    Current user:        multiadmin@localhostSSL:            Not in useCurrent pager:        stdoutUsing outfile:        ''Using delimiter:    ;Server version:        5.6.28-log MySQL Community Server (GPL)Protocol version:    10Connection:        Localhost via UNIX socketServer characterset:    latin1Db     characterset:    latin1Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:        /sales3307/mysql/run/mysql.sockUptime:            32 min 17 secThreads: 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'

This is actually related to the fact that the user in the preceding permission table is empty and the host is localhost.

You can log on as long as you log on from a local machine, no matter whether you specify any user other than the permission table, for example, the hello user does not exist in the permission table, but can still log on, that is, the user is empty, the host is from localhost.

[root@spark01 ~]# mysql --socket=/sales3307/mysql/run/mysql.sock -uhelloWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Therefore, these accounts can be deleted as part of mysql security reinforcement.

Summary: If multiadmin wants to log on from a local machine, you must grant the logon permission to localhost. % does not contain localhost.

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