Shulou(Shulou.com)06/01 Report--

1. Multi-instance application scenarios:

1. The choice of cash-strapped companies

When the company does not have too much business visits and is reluctant to spend money, but at the same time wants the database services of different businesses to be independent, and requires technologies such as master-slave synchronization to provide backup or read-write separation services, it is best to use multiple instances.

2. Concurrent access is not a very large business.

When the business visit volume of the company is not too large, the server resources are basically idle, which is very suitable for multi-instance applications. If the SQL statement is well optimized, multi-instance is a technique worth using. Even if the concurrency is very large, as long as the reasonable allocation of system resources, there will be no big problem.

Second, the principle of mysql multi-instance.

Mysql multi-instance, to put it simply, is to open multiple different mysql service ports (such as 3306 and 3307) on a server and run multiple mysql service processes. These service processes provide their own services by listening to different service ports through different socket.

These mysql instances share a set of mysql installers, using different my.cnf configuration files, launchers, and data files. When providing services, mysql multiple instances are logically independent, and each instance obtains the relevant hardware resources of the server according to the set value of the configuration file.

III. Steps for multi-instance deployment

Here the compilation and installation of the database will not be described, if it is not clear, please refer to other documents.

The port used in the experiment: 33064pm 33065

1. Create a data directory

Create a data file storage directory and modify the user group of the folder

Mkdir-p / home/mysqldata/33064

Mkdir-p / home/mysqldata/33065

Chown mysql.mysql 33064/33065/

2. Initialize the database:

/ usr/bin/mysql_install_db\

-- datadir=/home/mysqldata/33064/\

-- user=mysql

/ usr/bin/mysql_install_db\

-- datadir=/home/mysqldata/33065/\

-- user=mysql

3. Configuration / etc/my.cnf configuration file

Each instance has its own msyqld module, which contains the initialization parameters corresponding to the instance, such as [mysqld33064], [mysqld33065]

[root@model /] # cat / etc/my.cnf

[mysqld_multi]

Mysqld=/usr/bin/mysqld_safe

Mysqladmin=/usr/bin/mysqladmin

User=mysql

Password=mysql

[mysqld33064]

Datadir=/home/mysqldata/33064

Port=33064

Socket=/home/mysqldata/33064/mysql33064.sock

User=mysql

Symbolic-links=0

Default-storage-engine=INNODB

Log-bin = mysql-bin

Binlog_format = mixed

Expire_logs_days = 7

# # utf8

# default-character-set = utf8

# character_set_connection = utf8

# character_set_results = utf8

Character-set-server = utf8

Collation-server = utf8_general_ci

Init_connect = 'SET collation_connection = utf8_general_ci'

Init_connect = 'SET NAMES utf8'

Server-id = 1

[mysqld33065]

Datadir=/home/mysqldata/33065

Port=33065

Socket=/home/mysqldata/33065/mysql33065.sock

User=mysql

Symbolic-links=0

Default-storage-engine=INNODB

Log-bin = mysql-bin

Binlog_format = mixed

Expire_logs_days = 7

# # utf8

# default-character-set = utf8

# character_set_connection = utf8

# character_set_results = utf8

Character-set-server = utf8

Collation-server = utf8_general_ci

Init_connect = 'SET collation_connection = utf8_general_ci'

Init_connect = 'SET NAMES utf8'

Server-id = 1

[mysqld_safe]

# log=/var/log/mysqldquery.log

# # error log

Log-error=/var/log/mysqld.log

# pid-file=/var/run/mysqld/mysqld.pid

4. Start and stop multiple instances

Start with the mysqld_multi command instead of the mysqld command, which starts the corresponding database instance by passing different port parameters.

/ usr/bin/mysqld_multi-- defaults-file=/etc/my.cnf start 33064

/ usr/bin/mysqld_multi-- defaults-file=/etc/my.cnf start 33065

/ usr/bin/mysqld_multi-- defaults-file=/etc/my.cnf stop 33064

/ usr/bin/mysqld_multi-- defaults-file=/etc/my.cnf stop 33065

Check the database process. If two database processes indicate that the database is installed normally, otherwise it fails. You need to check the log log to deal with the specific reason.

[root@model mysql] # ps-ef | grep mysql

Root 3990 10 11:18 pts/0 00:00:00 / bin/sh / usr/bin/mysqld_safe-- datadir=/home/mysqldata/33065-- port=33065-- socket=/home/mysqldata/33065/mysql33065.sock-- user=mysql-- symbolic-links=0-- default-storage-engine=INNODB-- log-bin=mysql-bin-- binlog_format=mixed-- expire_logs_days=7-- character-set-server=utf8-- collation-server=utf8_general_ci-- init_connect=SET collation_connection = utf8_general_ci-- init_connect=SET NAMES utf8-- server-id=1

Mysql 4132 3990 0 11:18 pts/0 00:00:01 / usr/libexec/mysqld-basedir=/usr-- datadir=/home/mysqldata/33065-- user=mysql-- symbolic-links=0-- default-storage-engine=INNODB-- log-bin=mysql-bin-- binlog_format=mixed-- expire_logs_days=7-- character-set-server=utf8-- collation-server=utf8_general_ci-- init_connect=SET collation_connection = utf8_general_ci-- init_connect=SET NAMES utf8-- server-id=1-- log-error=/var/log / mysqld.log-pid-file=/home/mysqldata/33065/model.pid-socket=/home/mysqldata/33065/mysql33065.sock-port=33065

Root 4155 10 11:18 pts/0 00:00:00 / bin/sh / usr/bin/mysqld_safe-- datadir=/home/mysqldata/33064-- port=33064-- socket=/home/mysqldata/33064/mysql33064.sock-- user=mysql-- symbolic-links=0-- default-storage-engine=INNODB-- log-bin=mysql-bin-- binlog_format=mixed-- expire_logs_days=7-- character-set-server=utf8-- collation-server=utf8_general_ci-- init_connect=SET collation_connection = utf8_general_ci-- init_connect=SET NAMES utf8-- server-id=1

Mysql 4297 4155 0 11:18 pts/0 00:00:01 / usr/libexec/mysqld-basedir=/usr-- datadir=/home/mysqldata/33064-- user=mysql-- symbolic-links=0-- default-storage-engine=INNODB-- log-bin=mysql-bin-- binlog_format=mixed-- expire_logs_days=7-- character-set-server=utf8-- collation-server=utf8_general_ci-- init_connect=SET collation_connection = utf8_general_ci-- init_connect=SET NAMES utf8-- server-id=1-- log-error=/var/log / mysqld.log-pid-file=/home/mysqldata/33064/model.pid-socket=/home/mysqldata/33064/mysql33064.sock-port=33064

5. Multi-instance connection login

Mysql-S / home/mysqldata/33064/mysql33064.sock

Mysql-S / home/mysqldata/33065/mysql33065.sock

6. Delegate authority

The newly installed database root user does not have localhost login rights, so you need to assign permissions before you can log in with a user name and password.

Mysql-S / home/mysqldata/33064/mysql33064.sock

Mysql > grant allon *. * to 'root'@'%' identified by' root'

Mysql > grant allon *. * to 'root'@'localhost' identified by' root'

Mysql > flushprivileges

7. Log in with a user name and password

Mysql-uroot-proot-S / home/mysqldata/33064/mysql33064.sock

8. Modify password for multiple instances

Change the root password of instance 33064 and use the mysqladmin command. As follows:

Mysqladmin-uroot-p password root-S / home/mysqldata/33064/mysql33064.sock

At this point, our mysql multi-instance deployment is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.