mysql多实例管理_MySQL多实例管理

继续上篇：

配置mysql多实例数据库开机自启动

[root@MYSQL ~]#

[root@MYSQL ~]# echo "#mysql multi instances"

>>/etc/rc.local

[root@MYSQL ~]# echo "/data/3306/mysql start"

>>/etc/rc.local

[root@MYSQL ~]# echo "/data/3307/mysql start"

>>/etc/rc.local

[root@MYSQL ~]#

[root@MYSQL ~]# tail -3 /etc/rc.local

#mysql multi instances

/data/3306/mysql start

/data/3307/mysql start

[root@MYSQL ~]#

登入测试

[root@MYSQL ~]#

[root@MYSQL ~]# mysql -S /data/3306/mysql.sock

Welcome to the MySQL monitor.  Commands end with ;

or \g.

Your MySQL connection id is 1

Server version: 5.5.32-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>

mysql> show databases;

####这里结尾要分号！！！！

+--------------------+

| Database

|

+--------------------+

| information_schema |

| mysql

|

| performance_schema |

| test

|

+--------------------+

4 rows in set (0.00 sec)

mysql>

mysql>

mysql> select user();

+----------------+

| user()

|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.01 sec)

mysql>

退出：

mysql> quit

Bye

[root@MYSQL ~]#

为管理员设置密码：3306

[root@MYSQL ~]#

[root@MYSQL ~]# mysqladmin -u root -S /data/3306/mysql.sock

password '123456'

[root@MYSQL ~]#

[root@MYSQL ~]#  mysql -uroot -p -S

/data/3306/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ;

or \g.

Your MySQL connection id is 7

Server version: 5.5.32-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>

mysql> quit

Bye

[root@MYSQL ~]#

为管理员设置密码：3307

[root@MYSQL ~]#

[root@MYSQL ~]# mysqladmin -u root -S /data/3307/mysql.sock

password '123456'

[root@MYSQL ~]#  mysql -uroot -p -S

/data/3307/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ;

or \g.

Your MySQL connection id is 5

Server version: 5.5.32 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>

mysql> quit

Bye

[root@MYSQL ~]#

关闭脚本密码不对的时候如下：

[root@MYSQL ~]#

[root@MYSQL ~]# /data/3306/mysql stop

Stoping MySQL...

/application/mysql/bin/mysqladmin: connect to server at 'localhost'

failed

error: 'Access denied for user 'root'@'localhost' (using password:

YES)'

[root@MYSQL ~]#

关闭mysql多实例：

[root@MYSQL ~]#

[root@MYSQL ~]# /data/3306/mysql stop

Stoping MySQL...

[root@MYSQL ~]# /data/3307/mysql stop

Stoping MySQL...

[root@MYSQL ~]#

增加一个实例3308：

[root@MYSQL ~]# mkdir -p /data/3308/data

[root@MYSQL ~]#

[root@MYSQL ~]# \cp /data/3306/my.cnf /data/3308

[root@MYSQL ~]# \cp /data/3306/mysql /data/3308

[root@MYSQL ~]# sed -i 's/3306/3308/g' /data/3308/my.cnf

[root@MYSQL ~]# sed -i 's/server-id=1/server-id=8/g'

/data/3308/my.cnf

[root@MYSQL ~]# sed -i 's/3306/3308/g' /data/3308/mysql

[root@MYSQL ~]# chown -R mysql:mysql /data/3308

[root@MYSQL ~]# chown 700 /data/3308/mysql

[root@MYSQL ~]# cd /application/mysql/scripts

[root@MYSQL scripts]# ./mysql_install_db --datadir=/data/3308/data

--basedir=/application/mysql --user=mysql

WARNING: The host 'MYSQL' could not be looked up with

resolveip.

This probably means that your libc libraries are not 100 %

compatible

with this binary MySQL version. The MySQL daemon, mysqld, should

work

normally with the exception that host name resolving will not

work.

This means that you should use IP addresses instead of

hostnames

when specifying MySQL privileges !

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time 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 do so, start the server, then issue the following

commands:

/application/mysql/bin/mysqladmin -u root password

'new-password'

/application/mysql/bin/mysqladmin -u root -h MYSQL password

'new-password'

Alternatively you can run:

/application/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.

This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /application/mysql ; /application/mysql/bin/mysqld_safe

&

You can test the MySQL daemon with mysql-test-run.pl

cd /application/mysql/mysql-test ; perl

mysql-test-run.pl

Please report any problems with the

/application/mysql/scripts/mysqlbug script!

[root@MYSQL scripts]#

[root@MYSQL scripts]#

[root@MYSQL scripts]#

[root@MYSQL scripts]# chown -R mysql:mysql /data/3308

[root@MYSQL scripts]# egrep "server-id|log-bin"

/data/3308/my

my.cnf  mysql

[root@MYSQL scripts]# egrep "server-id|log-bin"

/data/3308/my.cnf

log-bin = /data/3308/mysql-bin

server-id = 1

[root@MYSQL scripts]# /data/3308/mysql start

Starting MySQL...

[root@MYSQL scripts]# sleep 5

[root@MYSQL scripts]# netstat -lnt |grep 3308

tcp

0

0

0.0.0.0:3308

0.0.0.0:*

LISTEN

[root@MYSQL scripts]#

登入3308：

[root@MYSQL scripts]#  mysql -S

/data/3308/mysql.sock

Welcome to the MySQL monitor.  Commands end with ;

or \g.

Your MySQL connection id is 1

Server version: 5.5.32-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>

mysql> quit

Bye

[root@MYSQL scripts]#

设置账号密码：

[root@MYSQL scripts]# mysqladmin -u root -S /data/3308/mysql.sock

password '123456'

[root@MYSQL scripts]#  mysql -S

/data/3308/mysql.sock

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

(using password: NO)

[root@MYSQL scripts]#

[root@MYSQL scripts]# mysql -uroot -p -S

/data/3308/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ;

or \g.

Your MySQL connection id is 4

Server version: 5.5.32-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>

mysql> quit

Bye

[root@MYSQL scripts]#

开机自启动：

[root@MYSQL scripts]# echo "/data/3308/mysql start"

>>/etc/rc.local

[root@MYSQL scripts]# tail -3 /etc/rc.local

/data/3306/mysql start

/data/3307/mysql start

/data/3308/mysql start

[root@MYSQL scripts]#

安装管理部分完！！！！