MySQL DBA system Learning (4) Multiple instances of MySQL multi start stop

Multiple instance configuration installation for MySQL

First, under what circumstances we would consider deploying multiple instances on a physical server, which is roughly the following:

1, the use of data pseudo-distributed architecture, and the initial start of the project does not necessarily have that many users, to this end a group of physical database server, but the deployment of multiple instances to facilitate follow-up migration;

2, to circumvent the pitfalls that MySQL does not support for SMP architectures, the use of multiple instance binding processors (NUMA processors must be supported, but now most processors are supported!). ), the different databases are allocated to different instances to provide data services;

3, a physical database server to support the data services of multiple databases, in order to improve the recovery efficiency of MySQL replication from the machine, the use of multiple instances of deployment;

MySQL database server architecture for dual-master replication, want to part of the important business of the data more than a remote computer room of the hot backup, and MySQL replication does not support the replication model, and not to provide users with services, in order to effectively control the cost, will consider a remote room to deploy a very good performance of the physical server, Even in the way of disk cabinets, multiple instances will be deployed for this purpose;

4, the traditional game industry MMO/MMORPG, as well as the Web Game, each suit is a database, and may have to do a lot of data query and data revision work, in order to reduce maintenance and error probability, also may adopt the way of multiple instance deployment, according to the concept of the region to allocate a database;

Second, the introduction of multiple instances of MySQL

Mysqld_multi is designed to manage multiple Mysqld service processes

Third, configure multiple instance mysqld and initialization

1, create the data file directory for the instance

[Root@test4 ~]# cd/mysql/
[Root@test4 mysql]# ls
Data
[Root@test4 mysql]# mkdir data2/
[Root@test4 mysql]# mkdir data3/

2, initializing multiple instances of MySQL
[Root@test4 mysql]# mysql_install_db--datadir=/mysql/data2/--user=mysqlinstalling

MySQL system tables ...
Ok

[Root@test4 mysql]# mysql_install_db--datadir=/mysql/data3/--user=mysqlinstalling MySQL

System tables ...
Okfilling Help Tables ...
Ok
To start mysqld at boot time and you have to copy
Support-files/mysql.server to the ' right place ' for your system
Please REMEMBER to SET A PASSWORD for the MySQL root USER!
To did so, start the server, then issue the following commands:
/usr/local/mysql/bin/mysqladmin-u root password ' new-password '
/usr/local/mysql/bin/mysqladmin-u root-h test4.wolf.org password ' new-password '
Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation
Which would also give you the option of removing the test
Databases and anonymous user created by default. This is
Strongly recommended for production servers.
The manual for more instructions.
Can start the MySQL daemon with:
Cd/usr/local/mysql; /usr/local/mysql/bin/mysqld_safe &
Can test the MySQL daemon with mysql-test-run.pl
Cd/usr/local/mysql/mysql-test; Perl mysql-test-run.pl
Please have a problems with The/usr/local/mysql/bin/mysqlbug script!

3, configure mysqld multiple instance my.cnf files

(1) The configuration my.cnf file detailed explanation

[Root@test4 ~]# cat/etc/my.cnf [Mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safe mysqladmin =/usr/local/mysql/  Bin/mysqladmin user =test password =test [client] #password = your_password Port = 3306 Socket = /tmp/mysql.sock [mysqld3306] Port = 3306 socket =/tmp/mysql3306.sock skip-external-locking Key_buffe  R_size = 16M Max_allowed_packet = 1M Table_open_cache = 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 datadir =/mysql/da Ta server-id = 1 [mysqld3307] Port = 3307 socket =/tmp/mysql3307.sock skip-external-locking key _buffer_size = 16M Max_allowed_packet = 1M Table_open_cache = sort_buffer_size = 512K Net_buffer_length = 8K Read_buffe R_size = 256K Read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-bin=mysql-bin binlog_format=mixed datadir =/my Sql/data2 Server-id = 1 [mysqld3308] Port = 3308 socket =/tmp/mysql3308.sock skip-external-locking key_buffer_size = 16M m Ax_allowed_packet = 1M Table_open_cache = sort_buffer_size = 512K Net_buffer_length = 8K read_buffer_size = 256K Read_r        Nd_buffer_size = 512K myisam_sort_buffer_size = 8M log-bin=mysql-bin binlog_format=mixed datadir =/mysql/data3 Server-id = 1 [mysqldump] Quick max_allowed_packet = 16M [MySQL] no-auto-rehash [myisamchk] key_buffer_size = 20M Sort_buffer _size = 20M Read_buffer = 2M Write_buffer = 2M [Mysqlhotcopy] Interactive-timeout

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/