Mysql multi-instance STARTUP configuration

Multi-instance startup means that I want to start multiple mysqld processes at the same time when I am helping a friend configure environmental sanitation. Next I will introduce the operation configuration method to you.

When the development and production environments are on the same machine or multiple test environments need to be deployed on one machine, multiple mysqld processes are usually required at the same time, recently, this problem was encountered during the test environment.

Or download the tarball installation from mysql installation,

Install mysql

The Code is as follows:	Copy code
Groupadd mysql Useradd-g mysql Cmake. -DCMAKE_INSTALL_PREFIX =/usr/local/mysql5.5.27/-DMYSQL_DATADIR =/data/mysql-connector = 1-DMYSQL_TCP_PORT = 3306-DMYSQL_UNIX_ADDR =/var/run/mysql. sock-DWITH_DEBUG = 0 Make & make install

Then Configure my. cnf, copy a my-medium.cnf from support-files to/etc/my. cnf, and the content of mysqld configuration section is basically like this

My. cnf Configuration

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

By default, mysqld only starts one instance. Since we want to start multiple mysqld instances, we need to use mysqld_multi. It is a perl script. before using it, you need to add some materials to my. cnf.

My. cnf configuration [add mysqld_mutli]

The Code is as follows:

Copy code

# Mysqld_multi reads the short configuration content [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 = 16 M Max_allowed_packet = 1 M Table_open_cache = 64 Sort_buffer_size = 512 K Net_buffer_length = 8 K Read_buffer_size = 256 K Read_rnd_buffer_size = 512 K Myisam_sort_buffer_size = 8 M 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 = 16 M Max_allowed_packet = 1 M Table_open_cache = 64 Sort_buffer_size = 512 K Net_buffer_length = 8 K Read_buffer_size = 256 K Read_rnd_buffer_size = 512 K Myisam_sort_buffer_size = 8 M Log-bin = mysql-bin Binlog_format = mixed Server-id = 1 User = mysql

Create the mysql runtime folder according to the configuration and use the mysql_install_db script to initialize the system library.

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 our configured mysqld1 and mysqld2. At this time, we should be able to see two mysqld processes in the progress table, use the-P parameter to specify the port to access the two databases under/data/mysql1 and/data/mysql2 respectively. The two databases do not affect each other and meet the test requirements. Of course, you can configure more instances.

Additional gains
Some additional problems are encountered during the configuration process.

If ubuntu is used (my current version is 11.10), apparmor will be installed by default. This software is a strange existence, and it is like a dark kernel, I have been silently limiting the access permissions of the software without being seen by the system, so that I do not know its existence. In fact, it is a white list in/etc/apparmor. d/specifies the access permission of/usr/sbin/mysqld to each file. This error is always reported when I want to migrate the mysql DATA folder to another location.
Can't create test file/data/mysql1/littleboy. lower-test

What is puzzling is that mysql has read and write permissions on these folders. In fact, you only need to edit/etc/apparmor. d/usr. sbin. mysqld file. Add the rw permission to the folder Based on the image.

To set remote access permissions for mysql, you only need the following SQL
1

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

 

The most important one is the '%', which indicates that any host can access mysql on the local machine.

Set a password for mysql.

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

The above method is to change the password without logging on to mysql, and there are two ways to change the table. See three methods for setting passwords in MySQL.

After the password is changed, mysqld_multi may not end the mysqld process through the default configuration. At this time, you need to add the user name and password in 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 cause some security risks, because my. cnf is visible. If it is used by many people, you can remove the line of password. When you operate mysqld_multi, add the -- password = 123456 parameter next to it.