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

I. Preface

II. Overview

III. Environmental preparation

Fourth, install MySQL 5.5.35

5. Create a new configuration file that supports multiple instances (I configure four instances here)

6. Initialize multi-instance database

7. Provide management script mysqld_multi.server

VIII. Overall backup to facilitate subsequent migration

IX. Manage multiple instances of MySQL

10. Log in to multiple instances of MySQL

11. Other management configurations

XII. Summary

Note: test environment CentOS 6.4x86x64, software version MySQL 5.5.35, software download address: http://dev.mysql.com/downloads/mysql/5.1.html#downloads.

1. Application scenario

The reason why the data pseudo-distributed architecture is adopted, and there may not be so many users at the beginning of the project, so we should first set up a set of physical database servers, but deploy multiple instances to facilitate subsequent migration.

In order to avoid the defect that mysql does not support SMP architecture, the method of multi-instance binding processor is used to assign different databases to different instances to provide data services.

One physical database server supports the data service of multiple databases. In order to improve the recovery efficiency of mysql replication slaves, multi-instance deployment is adopted.

The mysql database server architecture for dual-master replication wants more hot backup of some important business data from remote computer rooms, while mysql replication does not support multi-master replication mode and does not provide services to users. In order to effectively control costs, we will consider deploying a physical server with excellent performance in remote computer rooms, or even adding disk enclosures, for this reason, multi-instances will also be deployed.

Traditional game industry MMO/MMORPG, and Web Game, each server corresponds to a database, and may have to do a lot of data query and data correction work, in order to reduce the probability of maintenance errors, may also adopt the way of multi-instance deployment, allocate the database according to the concept of zone.

The above introduction of application scenarios mainly refers to this article: http://www.zhdba.com/mysqlops/2011/07/30/multi-mysqld/, our application here is mainly based on the previous three scenarios. Let's talk about the problems that we should pay attention to.

two。 Background / requirements, points for attention

(1)。 Background and demand

Store all installation files, configuration files, and data directories in the / data/mysql directory to facilitate rapid migration, overall backup and rapid replication in the future.

Run four MySQL instances on one server, bound on ports 3306, 3307, 3308, and 3309

Binlog logs are enabled for all four instances, and the data directories are stored in / data/mysql/data, / data/mysql/data2, / data/mysql/data3, / data/mysql/data4, respectively.

All four instances use InnoDB as the default storage engine and UTF-8 as the character encoding.

All four instances use the same performance optimization configuration parameters.

(2)。 Matters needing attention

When compiling and installing, point the database configuration file my.cnf and the data directory to the / data/mysql directory

Four different instances are managed by mysqld_multi, and the same profile sharing performance is used to optimize configuration parameters

In the same configuration file, use [mysqld1], [mysqld2], [mysqld3], [mysqld4] tags to realize the differentiated configuration of different instances

III. Environmental preparation

1. Install the yum source

one

two

[root@node1 src] # wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm

[root@node1 src] # rpm-ivh epel-release-6-8.noarch.rpm

two。 Synchronization time

one

two

three

[root@node1 src] # yum install-y ntp

[root@node1 src] # ntpdate 202.120.2.101

[root@node1 src] # hwclock-w

3. Install the mysql5.5 dependency package

one

[root@node1 ~] # yum install-y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

4. Install cmake

one

[root@node1 ~] # yum install-y cmake

Fourth, install MySQL 5.5.35

1. Create installation directory and data storage directory

one

two

[root@node1 ~] # mkdir / data/mysql

[root@node1 ~] # mkdir / data/mysql/data

two。 Create mysql users and groups

one

two

three

[root@node1 ~] # useradd mysql

[root@node1 ~] # id mysql

Uid=500 (mysql) gid=500 (mysql) group = 500 (mysql)

3. Authorized installation directory and data directory

one

two

[root@node1] # chown-R mysql.mysql / data/mysql/

[root@node1] # chown-R mysql.mysql / data/mysql/data

4. Install mysql

one

two

three

four

five

[root@node1 ~] # cd src/

[root@node1 src] # tar xf mysql-5.5.35.tar.gz

[root@node1 src] # cd mysql-5.5.35

[root@node1 mysql-5.5.35] # cmake-DCMAKE_INSTALL_PREFIX=/data/mysql-DSYSCONFDIR=/data/mysql/etc-DMYSQL_DATADIR=/data/mysql/data-DMYSQL_TCP_PORT=3306-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock-DMYSQL_USER=mysql-DEXTRA_CHARSETS=all-DWITH_READLINE=1-DWITH_SSL=system-DWITH_EMBEDDED_SERVER=1-DENABLED_LOCAL_INFILE=1-DWITH_INNOBASE_STORAGE_ENGINE=1

[root@node1 mysql-5.5.35] # make & & make install

Well, at this point, our mysql installation is complete, and let's provide the multi-instance configuration file for mysql.

5. Create a new configuration file that supports multiple instances (I configure four instances here)

1. Delete the default data directory

one

two

[root@node1 ~] # cd / data/mysql/

[root@node1 mysql] # rm-rf data

two。 Create a directory required for a multi-instance configuration

one

two

[root@node1 mysql] # mkdir etc tmp run log binlogs data data2 data3 data4

[root@node1 mysql] # chown-R mysql.mysql tmp run log binlogs data data2 data3 data4

3. Provide configuration fil

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

fifty-nine

sixty

sixty-one

sixty-two

sixty-three

sixty-four

sixty-five

sixty-six

sixty-seven

sixty-eight

sixty-nine

seventy

seventy-one

seventy-two

seventy-three

seventy-four

seventy-five

seventy-six

seventy-seven

seventy-eight

seventy-nine

eighty

eighty-one

eighty-two

eighty-three

eighty-four

eighty-five

eighty-six

eighty-seven

eighty-eight

eighty-nine

ninety

ninety-one

ninety-two

ninety-three

ninety-four

ninety-five

ninety-six

ninety-seven

ninety-eight

ninety-nine

one hundred

one hundred and one

one hundred and two

one hundred and three

one hundred and four

one hundred and five

one hundred and six

one hundred and seven

one hundred and eight

one hundred and nine

one hundred and ten

one hundred and eleven

one hundred and twelve

one hundred and thirteen

one hundred and fourteen

one hundred and fifteen

one hundred and sixteen

one hundred and seventeen

one hundred and eighteen

one hundred and nineteen

one hundred and twenty

one hundred and twenty one

one hundred and twenty two

one hundred and twenty three

one hundred and twenty four

one hundred and twenty five

one hundred and twenty six

one hundred and twenty seven

one hundred and twenty eight

one hundred and twenty nine

one hundred and thirty

one hundred and thirty one

one hundred and thirty two

one hundred and thirty three

one hundred and thirty four

one hundred and thirty five

one hundred and thirty six

one hundred and thirty seven

one hundred and thirty eight

one hundred and thirty nine

one hundred and forty

one hundred and forty one

one hundred and forty two

one hundred and forty three

one hundred and forty four

one hundred and forty five

one hundred and forty six

one hundred and forty seven

one hundred and forty eight

one hundred and forty nine

one hundred and fifty

one hundred and fifty one

one hundred and fifty two

one hundred and fifty three

one hundred and fifty four

one hundred and fifty five

one hundred and fifty six

one hundred and fifty seven

one hundred and fifty eight

one hundred and fifty nine

one hundred and sixty

one hundred and sixty one

one hundred and sixty two

one hundred and sixty three

one hundred and sixty four

one hundred and sixty five

one hundred and sixty six

one hundred and sixty seven

one hundred and sixty eight

one hundred and sixty nine

one hundred and seventy

one hundred and seventy one

one hundred and seventy two

one hundred and seventy three

one hundred and seventy four

one hundred and seventy five

[root@node1 ~] # cd src/

[root@node1 src] # cd mysql-5.5.35

[root@node1 mysql-5.5.35] # cp support-files/my-small.cnf / data/mysql/etc/my.cnf

[root@node1 ~] # cd / data/mysql/etc/

[root@node1 etc] # vim my.cnf

# This server may run 4 + separate instances. So we use mysqld_multi to manage their services.

[client]

Default-character-set = utf8

[mysqld_multi]

Mysqld = / data/mysql/bin/mysqld_safe

Mysqladmin = / data/mysql/bin/mysqladmin

Log = / data/mysql/log/mysqld_multi.log

User = root

# password =

# This is the general purpose database.

# The locations are default.

# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.

[mysqld1]

Socket = / data/mysql/run/mysqld.sock

Port = 3306

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

Datadir = / data/mysql/data

Lc-messages-dir = / data/mysql/share/english

# These support master-master replication

# auto-increment-increment = 4

# auto-increment-offset = 1 # Since it is master 1

Log-bin = / data/mysql/binlogs/bin-log-mysqld1

Log-bin-index = / data/mysql/binlogs/bin-log-mysqld1.index

# binlog-do-db = # Leave this blank if you want to control it on slave

Max_binlog_size = 1024m

# This is exlusively for mysqld2

# It is on 3307 with data directory / data/mysqld/data2

[mysqld2]

Socket = / data/mysql/run/mysqld.sock2

Port = 3307

Pid-file = / data/mysql/run/mysqld.pid2

Datadir = / data/mysql/data2

Lc-messages-dir = / data/mysql/share/english

# Disable DNS lookups

# skip-name-resolve

# These support master-slave replication

Log-bin = / data/mysql/binlogs/bin-log-mysqld2

Log-bin-index = / data/mysql/binlogs/bin-log-mysqld2.index

# binlog-do-db = # Leave this blank if you want to control it on slave

Max_binlog_size = 1024m

# Relay log settings

# relay-log = / data/mysql/log/relay-log-mysqld2

# relay-log-index = / data/mysql/log/relay-log-mysqld2.index

# relay-log-space-limit = 4G

# Slow query log settings

# log-slow-queries = / data/mysql/log/slow-log-mysqld2

# long_query_time = 2

# log-queries-not-using-indexes

# This is exlusively for mysqld3

# It is on 3308 with data directory / data/mysqld/data3

[mysqld3]

Socket = / data/mysql/run/mysqld.sock3

Port = 3308

Pid-file = / data/mysql/run/mysqld.pid3

Datadir = / data/mysql/data3

Lc-messages-dir = / data/mysql/share/english

# Disable DNS lookups

# skip-name-resolve

# These support master-slave replication

Log-bin = / data/mysql/binlogs/bin-log-mysqld3

Log-bin-index = / data/mysql/binlogs/bin-log-mysqld3.index

# binlog-do-db = # Leave this blank if you want to control it on slave

Max_binlog_size = 1024m

# This is exlusively for mysqld4

# It is on 3309 with data directory / data/mysqld/data4

[mysqld4]

Socket = / data/mysql/run/mysqld.sock4

Port = 3309

Pid-file = / data/mysql/run/mysqld.pid4

Datadir = / data/mysql/data4

Lc-messages-dir = / data/mysql/share/english

# Disable DNS lookups

# skip-name-resolve

# These support master-slave replication

Log-bin = / data/mysql/binlogs/bin-log-mysqld4

Log-bin-index = / data/mysql/binlogs/bin-log-mysqld4.index

# binlog-do-db = # Leave this blank if you want to control it on slave

Max_binlog_size = 1024m

# The rest of the my.cnf is shared

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

Basedir = / data/mysql

Tmpdir = / data/mysql/tmp

Socket = / data/mysql/run/mysqld.sock

Port = 3306

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

Datadir = / data/mysql/data

Lc-messages-dir = / data/mysql/share/english

Skip-external-locking

Key_buffer_size = 16K

Max_allowed_packet = 1m

Table_open_cache = 4

Sort_buffer_size = 64K

Read_buffer_size = 256K

Read_rnd_buffer_size = 256K

Net_buffer_length = 2K

Thread_stack = 128K

# Increase the max connections

Max_connections = 2

# The expiration time for logs, including binlogs

Expire_logs_days = 14

# Set the character as utf8

Character-set-server = utf8

Collation-server = utf8_unicode_ci

# This is usually only needed when setting up chained replication

# log-slave-updates

# Enable this to make replication more resilient against server crashes and restarts

# but can cause higher I/O on the server

# sync_binlog = 1

# The server id, should be unique in same network

Server-id = 1

# Set this to force MySQL to use a particular engine/table-type for new tables

# This setting can still be overridden by specifying the engine explicitly

# in the CREATE TABLE statement

Default-storage-engine = INNODB

# Enable Per Table Data for InnoDB to shrink ibdata1

Innodb_file_per_table = 1

# Uncomment the following if you are using InnoDB tables

# innodb_data_home_dir = / data/mysql/data

# innodb_data_file_path = ibdata1:10M:autoextend

# innodb_log_group_home_dir = / data/mysql/data

# You can set.. _ buffer_pool_size up to 50-80% of RAM

# but beware of setting memory usage too high

Innodb_buffer_pool_size = 16m

Innodb_additional_mem_pool_size = 2m

# Set.. _ log_file_size to 25% of buffer pool size

Innodb_log_file_size = 5m

Innodb_log_buffer_size = 8m

Innodb_flush_log_at_trx_commit = 1

Innodb_lock_wait_timeout = 50

[mysqldump]

Quick

Max_allowed_packet = 16m

[mysql]

No-auto-rehash

[myisamchk]

Key_buffer_size = 8m

Sort_buffer_size = 8m

[mysqlhotcopy]

Interactive-timeout

[mysql.server]

User = mysql

[mysqld_safe]

Log-error = / data/mysql/log/mysqld.log

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

Open-files-limit = 8192

Note: MySQL comes with several different configuration files, which are placed in the / data/mysql/support-files directory, which are my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf. By their names, we can directly understand that they are configured for different servers. The configuration files in this article are from my-small.cnf, because I set them on a virtual machine. In a production environment, we can optimize the server by referring to some parameter configurations in my-huge.cnf or my-innodb-heavy-4G.cnf

4. Modify my.cnf read and write permissions

one

two

[root@node1 etc] # chown-R root.root / data/mysql/etc

[root@node1 etc] # chmod 600 / data/mysql/etc/my.cnf

All right, now that our configuration file is set up, let's initialize the database.

6. Initialize multi-instance database

1. Change to the installation directory of mysql

one

[root@node1 ~] # cd / data/mysql/

two。 Initialize instance [mysqld1]

one

[root@node1 mysql] # scripts/mysql_install_db-basedir=/data/mysql-datadir=/data/mysql/data-user=mysql

3. Initialize instance [mysqld2]

one

[root@node1 mysql] # scripts/mysql_install_db-basedir=/data/mysql-datadir=/data/mysql/data2-user=mysql

4. Initialize instance [mysqld3]

one

[root@node1 mysql] # scripts/mysql_install_db-basedir=/data/mysql-datadir=/data/mysql/data3-user=mysql

5. Initialize instance [mysqld4]

one

[root@node1 mysql] # scripts/mysql_install_db-basedir=/data/mysql-datadir=/data/mysql/data4-user=mysql

All right, now that we're done with initialization, let's provide a multi-instance management script.

7. Provide management script mysqld_multi.server

1. Create an administrative script directory

one

[root@node1 mysql] # mkdir / data/mysql/init.d

two。 Provide management scripts

one

[root@node1 mysql] # cp support-files/mysqld_multi.server init.d/

3. Simply modify the script

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

[root@node1 mysql] # cd init.d/

[root@node1 init.d] # vim mysqld_multi.server

#! / bin/sh

#

# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.

# This script assumes that my.cnf file exists either in / etc/my.cnf or

# / root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the

# mysqld_multi documentation for detailed instructions.

#

# This script can be used as / etc/init.d/mysql.server

#

# Comments to support chkconfig on RedHat Linux

# chkconfig: 2345 64 36

# description: A very fast and reliable SQL database engine.

#

# Version 1.0

#

Basedir=/data/mysql

Bindir=/data/mysql/bin

Conf=/data/mysql/etc/my.cnf

Export PATH=$PATH:$bindir

If test-x $bindir/mysqld_multi

Then

Mysqld_multi= "$bindir/mysqld_multi"

Else

Echo "Can't execute $bindir/mysqld_multi from dir $basedir"

Exit

Fi

Case "$1" in

'start')

"$mysqld_multi"-defaults-extra-file=$conf start $2

'stop')

"$mysqld_multi"-defaults-extra-file=$conf stop $2

'report')

"$mysqld_multi"-defaults-extra-file=$conf report $2

'restart')

"$mysqld_multi"-defaults-extra-file=$conf stop $2

"$mysqld_multi"-defaults-extra-file=$conf start $2

*)

Echo "Usage: $0 {start | stop | report | restart}" > & 2

Esac

All right, at this point, all our configurations are complete, let's pack up and back up.

VIII. Overall backup to facilitate subsequent migration

one

two

three

four

five

six

seven

[root@node1 ~] # cd / data/

[root@node1 data] # tar czvf mysql-5.5.350-full.tar.gz / data/mysql/

[root@node1 data] # ll-h

Total consumption 128m

Drwx-. 2 root root 16K August 17 18:42 lost+found

Drwxr-xr-x 22 mysql mysql 4.0K January 6 22:08 mysql

-rw-r--r-- 1 root root 128m January 7 00:25 mysql-5.5.350-full.tar.gz

Note: after the backup is completed, take the mysql-5.5.350-full.tar.gz directly to another server, and you can enable it directly after decompression. Hey hey, it's convenient.

IX. Manage multiple instances of MySQL

1. Start four mysql instances at the same time

(1)。 Method 1:

one

[root@node1] # / data/mysql/init.d/mysqld_multi.server start 1, 2, 5, 3, 4.

Or method 2:

one

[root@node1] # / data/mysql/init.d/mysqld_multi.server start 3306 3307 3308 3309

(2)。 Check out the launched instance

one

two

three

four

five

[root@node1 ~] # netstat-ntulp | grep mysqld

Tcp 0 0 0.0.0.0 3307 0.0.0.015 * LISTEN 31416/mysqld

Tcp 0 0 0.0.0.0 3308 0.0.0.015 * LISTEN 31414/mysqld

Tcp 0 0 0.0.0.0 3309 0.0.0.015 * LISTEN 31420/mysqld

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 31413/mysqld

two。 Shut down four mysql instances at the same time

(1)。 Method 1:

one

[root@node1] # / data/mysql/init.d/mysqld_multi.server stop 1, 2, 5, 3, 4.

Or method 2:

one

[root@node1] # / data/mysql/init.d/mysqld_multi.server stop 3306 3307 3308 3309

3. Start or shut down the mysql instance separately

(1)。 Start an instance

one

two

three

[root@node1 ~] # / data/mysql/init.d/mysqld_multi.server start 1

[root@node1 ~] # netstat-ntulp | grep mysqld

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 32221/mysqld

(2)。 Close an instance

one

[root@node1 ~] # / data/mysql/init.d/mysqld_multi.server stop 1

Note that starting or closing two or three instances is the same as above and will not be demonstrated here.

10. Log in to multiple instances of MySQL

Note, we start four instances at the same time. Let's demonstrate how to log in to each of these four instances. To demonstrate the difference between the four instances, we create mydb1, mydb2, mydb3, and mydb4 in each of the four instances.

1. Log in to [mysqld1]

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

[root@node1] # / data/mysql/bin/mysql-uroot-h227.0.0.1-P3306-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.35-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > create database mydb1

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mydb1 |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

two。 Log in to [mysqld2]

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

[root@node1] # / data/mysql/bin/mysql-uroot-h227.0.0.1-P3307-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.35-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > create database mydb2

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mydb2 |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

3. Log in to [mysqld3]

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

[root@node1] # / data/mysql/bin/mysql-uroot-h227.0.0.1-P3308-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.35-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.01sec)

Mysql > create database mydb3

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mydb3 |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

4. Log in to [mysqld4]

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

[root@node1] # / data/mysql/bin/mysql-uroot-h227.0.0.1-P3309-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.35-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.01sec)

Mysql > create database mydb4

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mydb4 |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

All right, this is the end of our demonstration of MySQL multi-instance login. Let's set the root password for mysql.

11. Other management configurations

1. Create a password for the root user of mysql

one

two

three

four

[root@node1] # / data/mysql/bin/mysqladmin-uroot-h227.0.0.1-P3306 password '123456'

[root@node1] # / data/mysql/bin/mysqladmin-uroot-h227.0.0.1-P3307 password '123456'

[root@node1] # / data/mysql/bin/mysqladmin-uroot-h227.0.0.1-P3308 password '123456'

[root@node1] # / data/mysql/bin/mysqladmin-uroot-h227.0.0.1-P3309 password '123456'

two。 Delete an empty password account for an anonymous connection

Note: log in to instances [mysqld1], [mysqld2], [mysqld3] and [mysqld4], respectively, and execute the following command:

one

two

three

four

five

six

Mysql > use mysql; # Select system database mysql

Mysql > select Host,User,Password from user; # View all users

Mysql > delete from user where password= ""; # Delete an account without a password

Mysql > flush privileges; # refresh permissions

Mysql > select Host,User,Password from user; # confirm whether all users with empty passwords have been deleted

Mysql > exit

XII. Summary

1. Using source code to compile and install MySQL may take more time at the first time, but it is very worthwhile, because we can organize the location of all MySQL-related files by ourselves; and after source code compilation and installation, MySQL can be copied directly to other servers to run, which greatly facilitates our future migration, backup and configuration of the new server.

two。 In this paper, only four examples [mysqld1], [mysqld2], [mysqld3] and [mysqld4] are used to illustrate. In fact, we can implement [mysqld5], [mysqld6] in this way. Wait for more instances, provided that your server hardware is well configured, but generally there are no more than 6 instances on our side.

3. In the case of multiple instances running on a single machine, do not log in to the server using mysql-hlocalhost or directly ignore the-h parameter. This should be regarded as a bug of MySQL, that is, if you use localhost or ignore the-h parameter instead of specifying 127.0.0.1, even if the selected port is 3307, you will still log in to 3306, so you should try your best to avoid this confusion. Uniformly use 127.0.0.1 to bind the port or use socket to log in. If you do not specify the-h227.0.0.1 option in mysql5.5, you cannot log in.

Finally, I hope you can get something from it.

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.