Mysql Multi-instance startup configuration detailed

When the development and production environment on the same machine, or need to deploy more than one set of test environment on a machine, often need to start multiple mysqld processes, and recently to help test the environment encountered this problem.

or from the installation of MySQL start, download tarball installation,

installing MySQL

The code is as follows	Copy Code
Groupadd MySQL useradd-g MySQL MySQL CMake. -dcmake_install_prefix=/usr/local/mysql5.5.27/-dmysql_datadir=/data/mysql-dwith_innobase_storage_engine=1- Dmysql_tcp_port=3306-dmysql_unix_addr=/var/run/mysql/mysql.sock-dwith_debug=0 Make && make install

Then configure MY.CNF, from the support-files inside copy a my-medium.cnf to/ETC/MY.CNF, inside mysqld configuration section of the content is basically like this

MY.CNF Configuration

The code is as follows	Copy Code
[Mysqld] Port = 3306 Socket =/var/run/mysql/mysql.sock Skip-external-locking Key_buffer_size = 16M Max_allowed_packet = 1M Table_open_cache = 64 Sort_buffer_size = 512K Net_buffer_length = 8K Read_buffer_size = 256K Read_rnd_buffer_size = 512K Myisam_sort_buffer_size = 8M

The default mysqld only starts one instance, and since our goal is to start multiple mysqld instances, we need to use Mysqld_multi. It is a Perl script that needs to be added to the my.cnf before it is used.

MY.CNF configuration [Add Mysqld_mutli]

The code is as follows

Copy Code

# Mysqld_multi will read the short content of this configuration [Mysqld_multi] Mysqld =/usr/local/mysql/bin/mysqld Mysqladmin =/usr/local/mysql/bin/mysqladmin # The first mysqld instance [Mysqld1] Port = 3306 Socket =/var/run/mysql/mysql1.sock DataDir =/DATA/MYSQL1 General-log-file =/var/log/mysql/error.log Skip-external-locking Key_buffer_size = 16M Max_allowed_packet = 1M Table_open_cache = 64 Sort_buffer_size = 512K Net_buffer_length = 8K Read_buffer_size = 256K Read_rnd_buffer_size = 512K Myisam_sort_buffer_size = 8M Log-bin=mysql-bin Binlog_format=mixed Server-id = 1 user = MySQL # Second MYSQLD instance [Mysqld2] Port = 3307 Socket =/var/run/mysql/mysql2.sock DataDir =/data/mysql2 General-log-file =/var/log/mysql/error.log Skip-external-locking Key_buffer_size = 16M Max_allowed_packet = 1M Table_open_cache = 64 Sort_buffer_size = 512K Net_buffer_length = 8K Read_buffer_size = 256K Read_rnd_buffer_size = 512K Myisam_sort_buffer_size = 8M Log-bin=mysql-bin Binlog_format=mixed Server-id = 1 user = MySQL

Then create the MySQL runtime folder by configuration and initialize the system library with mysql_install_db script

The code is as follows	Copy Code
Mkdir-p/data/mysql1/data/mysql2/var/log/mysql/var/run/mysql Chgrp mysql/data/mysql*/var/log/mysql/var/run/mysql Chown mysql/data/mysql*/var/log/mysql/var/run/mysql mysql_install_db--DATADIR=/DATA/MYSQL1--user=mysql mysql_install_db--DATADIR=/DATA/MYSQL2--user=mysql Mysqld_multi Start 1-2

The last command has actually started with our configured Mysqld1 and mysqld2, where we should see two mysqld processes in the process table, and try specifying the ports with the-p parameter to access the two libraries below/DATA/MYSQL1 and/DATA/MYSQL2 respectively, The two do not affect each other, just can meet the requirements of testing. Of course, it is also possible to configure more instances.

Extra Harvest
There are some additional problems with this configuration and note the memo.

If you are using Ubuntu (my current version is still 11.10), the default is to install AppArmor, the software is a strange existence, it is like a secret lover, has been silently restricting access to the software, and then not be seen by the system, so that I do not know its existence. In fact it is a whitelist, in/etc/apparmor.d/specified/usr/sbin/mysqld access to each file, when I want to move MySQL Data folder to another location, always reported this error
Can ' t create test file/data/mysql1/littleboy.lower-test

And the puzzling is that MySQL has read and write access to these folders, in fact, only need to edit the/etc/apparmor.d/usr.sbin.mysqld file, dots for the folder plus RW permissions on it

Set remote access permissions for MySQL, just the following SQL
1

The code is as follows	Copy Code
INSERT mysql.user (' Host ', ' user ', ' Password ') VALUES ('% ', ' root ', Password (' 123456 '));

One of the most critical is the '% ', which means that MySQL can be accessed by any host.

Set the password for MySQL.

The code is as follows	Copy Code
Mysqladmin-u root-h 127.0.0.1 Password 123456

The above is to change the password without login MySQL, there are two ways to change the table. See MySQL Set password three ways

After the password has been modified, Mysqld_multi may not be able to end the MYSQLD process by default configuration, which requires the user name and password to be added to the configuration

The code is as follows	Copy Code
[Mysqld_multi] Mysqld =/usr/local/mysql/bin/mysqld Mysqladmin =/usr/local/mysql/bin/mysqladmin user = root Password = 123456

Of course this may have some security implications, because MY.CNF is visible. If many people use, can be password this line to remove, each operation Mysqld_multi, in the back with--password=123456 parameters on the line