Modify validation of ibdata1 size and how to manage multiple instances using Mysqld_multi

Modify validation of ibdata1 size

Ibdata is a shared table space that is generated when MySQL is initialized.

But many children's shoes will see the tuning recommendations of various gods on the web, and in the case of MySQL has been initialized, modify the config file in Innodb_data_file_path=ibdata1:12m:autoextend. Cause MySQL to start when the error.

Here's a mock-up:

The original ibdata1 size is 12M

# ll-h/sales3306/mysql/data/1:/sales3306/mysql/data/ibdata1

Modify the Innodb_data_file_path parameter in the config file to resize it

Innodb_data_file_path=ibdata1:20m:autoextend

Restarting the database service

The database is started without an error, but it is not up, view the log information

file 768 inch file  the 0 if Non-zero) pages!

The error message is actually obvious, ibdata1 the actual size and the size specified in the configuration file is inconsistent.

How do I fix this problem?

In fact, just set the parameter to be equal to or less than its actual size.

About Equals, in fact is not difficult to understand, after all, to coincide, that is less than and why can it? The key is the Autoextend option that follows the parameter, so the actual value is larger than the initial value is normal. Of course, less than the case only applies to tablespace with autoextend option, MySQL can specify more than one table space, but only the last one can specify this option.

To get its actual size, there are two ways to

One is to view its specific size through ls-l, can be directly written 12582912 (12M)

The second is the error message through the wrong log, for example, it is easy to calculate the actual size of 768*16/1024=12m

Of course, if the default shared tablespace size is too large, you can add a table space

Innodb_data_file_path=ibdata1:12m;ibdata2:20m:autoextend

Note: Shared tablespace, even after the data has been cleaned out, will not reclaim space, can only migrate data, reinitialization.

Use of Mysqld_multi

1. In the execution Mysqld_multi times the following error:

/usr/local/mysql/bin/mysqld_multi--defaults-file=/root/ make sure Command available and in your path. The command is available from the Latestmysql distribution. Abort:can 'my_print_defaults '.    make sure your command in your PATH.

Workaround:

In/etc/profile, add the following:

Export path= $PATH:/usr/local/mysql/bin/

and make it effective source/etc/profile

2. Failed to start by viewing the Mysqld_multi error log/usr/local/mysql/multi.log

Have the following error message:

Starting MySQL Servers160116  -: -: AMysqld_safe Logging to'/var/log/mariadb/mariadb.log'.Touch: CannotTouch'/var/log/mariadb/mariadb.log ': No suchfileor directorychmod: Cannot access '/var/log/mariadb/mariadb.log ': No suchfileor directoryTouch: CannotTouch'/var/log/mariadb/mariadb.log ': No suchfileor directoryChown: Cannot access '/var/log/mariadb/mariadb.log ': No suchfileor directory160116  -: -: AMysqld_safe Logging to'/sales3307/mysql/log/.err'.160116  -: -: AMysqld_safe starting mysqld daemon with databases from/sales3306/mysql/Data/usr/local/mysql/bin/mysqld_safe:line129:/var/log/mariadb/mariadb.log:no Suchfileor directory/usr/local/mysql/bin/mysqld_safe:line166:/var/log/mariadb/mariadb.log:no Suchfileor directoryTouch: CannotTouch'/var/log/mariadb/mariadb.log ': No suchfileor directoryChown: Cannot access '/var/log/mariadb/mariadb.log ': No suchfileor directorychmod: Cannot access '/var/log/mariadb/mariadb.log ': No suchfileor directory160116  -: -: AMysqld_safe mysqld from PIDfile/var/run/mariadb/Mariadb.pid ended/usr/local/mysql/bin/mysqld_safe:line129:/var/log/mariadb/mariadb.log:no Suchfileor directory160116  -: -: AMysqld_safe starting mysqld daemon with databases from/sales3307/mysql/Data160116  -: -: atMysqld_safe mysqld from PIDfile/var/run/mariadb/mariadb.pid endedmysqld_multi LogfileVersion2.16; Run:sat Jan -  -: -: -  .

。。。 How can there be mariadb information, this is related to my operating system, CentOS 7, the default database is mariadb, not MySQL.

Reason for failure:

No error log specified

After specifying the error log, Log-error=/sales3307/mysql/log/error.log

Reboot, and report the following error:

 .- on- -  -: A: the 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) .- on- -  -: A: the 18683[ERROR] Can't start Server:can'T create PIDfile: No Suchfileor directory160116  -: A:TenMysqld_safe mysqld from PIDfile/var/run/mariadb/mariadb.pid Ended

Failure reason: No PID file specified

Specify the path to the PID file

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

Reboot, finally start success

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

MYSQLD parts are not affixed, this configuration is common

[Mysqld_multi]mysqld=/usr/local/mysql/bin/Mysqld_safemysqladmin=/usr/local/mysql/bin/mysqladmin# used to do shutdown MySQL using user=Rootlog=/usr/local/mysql/multi.log# #password="'[Mysqld3306]port=3306Socket=/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/datainnodb_buffer_pool_size=200Mlog-error=/sales3306/mysql/log/Error.logpid-file=/sales3306/mysql/run/Mysqld.pid[mysqld3307]port=3307Socket=/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/datainnodb_buffer_pool_size=100Mlog-error=/sales3307/mysql/log/Error.logpid-file=/sales3307/mysql/run/mysqld.pid

In the process of tossing is still a lot of bumpy, the conclusion is very personalized customization, such as Socket,log-error,pid-file to specify their own path. Otherwise, during the startup process, the system will be the default, and multiple instances will be the default, conflicting. But the entire troubleshooting process is quite simple, not to see the Mysqld_multi error log, is the log of the instance itself.

Here is a demonstration of the use of Mysqld_multi:

[Email protected] ~]# Mysqld_multi--defaults-file=/root/multi.cnf Stop
[[Email protected]~]# Mysqld_multi--defaults-file=/root/MULTI.CNF reportreporting MySQL serversmysql server from group:mysqld3306 are not runningmysql servers from group:mysqld33 was not running
[[Email protected]~]# Mysqld_multi--defaults-file=/ROOT/MULTI.CNF Report3306Reporting MySQL serversmysql server from group:mysqld3306 are not running
[[Email protected]~]# Mysqld_multi--defaults-file=/ROOT/MULTI.CNF start3306

[[Email protected]~]# Mysqld_multi--defaults-file=/root/multi.cnf reportreporting MySQL serversmysql server from group:mysqld3306 are Runningmysql server from GROUP:MYSQLD33 was not running

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

Sometimes, the root privilege is too large and the password is exposed in plaintext to a security risk.

Although Mysqld_multi supports the--password option, how do I close an instance if the password for two instances is different?

You can create an account with the same name for two instances, giving only shutdown permissions, which resolves the above problems.

Mysql> '  multiadmin' @'localhost '  123456'0 rows affected (0.00  sec) MySQL>  0 rows affected (0.00 sec)

Modify validation of ibdata1 size and how to manage multiple instances using Mysqld_multi