单台[dell R720]服务器部署多个mysql实例

一、安装mysql准备

1.1 下载mysql软件包

mkdir -p /home/xuekun/mysql

cd /home/xuekun/tools/mysql

wgethttp://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.10.tar.gz

1.2安装mysql软件

yum-y install make gcc-c++ cmake bison bison-devel ncurses-devel

tarxvf mysql-5.6.16.tar.gz

cdmysql-5.6.16

cmake\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DMYSQL_DATADIR=/data\

-DSYSCONFDIR=/etc\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_MEMORY_STORAGE_ENGINE=1\

-DWITH_READLINE=1\

-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock\

-DMYSQL_TCP_PORT=3306\

-DENABLED_LOCAL_INFILE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DEXTRA_CHARSETS=all\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci

make&& make install

1.3创建mysql用户

groupadd mysql

useradd -g mysql -M -s /sbin/nologin mysql

1.4创建mysql数据文件目录

mkdir -p/data/3306/data

mkdir -p/data/3307/data

tree /data/

/data/

|-- 3306

| `-- data

`-- 3307

`-- data

4 directories, 0 files

1.5 授权mysql用户及组访问数据文件目录

chown -Rmysql:mysql /data/3306

chown -Rmysql:mysql /data/3307

1.6 建立3306,3307my.cnf配置文件

vim /data/3306/my.cnf

vim  /data/3307/my.cnf

需要添加的my.cnf内容见附录B：或本文档目录下的my.cnf文件

#授权mysql用户及组访问my.cnf

chown -Rmysql:mysql /data/3306/my.cnf

chown -Rmysql:mysql /data/3307/my.cnf

1.7 建立mysql启动脚本

vim  /data/3306/mysql

vim  /data/3307/mysql

需要添加的mysql 内容见附录C：或本文档目录下的mysql文件

chmod 700/data/3306/mysql

chmod 700/data/3307/mysql

1.8初始化数据库

vim/etc/profile

PATH=/usr/local/mysql/bin:$PATH

exportPATH

#关闭文件，运行下面的命令，让配置立即生效

source/etc/profile

cd/usr/local/mysql

scripts/mysql_install_db--datadir=/data/3306/data

InstallingMySQL system tables...

OK

Filling helptables...

OK

To start mysqldat boot time you have to copy

support-files/mysql.serverto the right place for your system

PLEASE REMEMBERTO SET A PASSWORD FOR THE MySQL root USER !

To do so, startthe server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin-u root password ‘new-password‘

/usr/local/mysql/bin/mysqladmin-u root -h A password ‘new-password‘

Alternativelyyou can run:

/usr/local/mysql/bin/mysql_secure_installation

which will alsogive you the option of removing the test

databases andanonymous user created by default. This is

stronglyrecommended for production servers.

See the manualfor more instructions.

You can startthe MySQL daemon with:

cd/usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

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

cd/usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please reportany problems with the /usr/local/mysql/bin/mysqlbug script!

scripts/mysql_install_db--datadir=/data/3307/data

InstallingMySQL system tables...

OK

Filling helptables...

OK

同上面3306的内容，因此，此处省略。

chown -Rmysql:mysql /data

1.9 启动数据库

启动mysql实例的命令为

/data/3306/mysqlstart

StartingMySQL...

/data/3307/mysqlstart

StartingMySQL...

检查启动情况：

netstat-lnt|grep 330[6-7]

tcp 0 00.0.0.0:3306 0.0.0.0:* LISTEN

tcp 0 00.0.0.0:3307 0.0.0.0:* LISTEN

并加入/etc/rc.local，设置为开机自启动

echo"/data/3306/mysql start" >>/etc/rc.local

echo"/data/3307/mysql start" >>/etc/rc.local

cat/etc/rc.local

#!/bin/sh

#

# This scriptwill be executed *after* all the other init scripts.

# You can putyour own initialization stuff in here if you don‘t

# want to dothe full Sys V style init stuff.

touch/var/lock/subsys/local

/data/3306/mysqlstart

/data/3307/mysql start

提示：如果此步中的数据库启动不了，请稍微等待下，如果还不行请查看错误日志，路径在my.cnf的最下面。

二、配置mysql数据库

2.1 访问测试登陆情况

mysql -uroot -p-S /data/3306/mysql.sock

Enter password:

Welcome to theMySQL monitor. Commands end with ; or \g.

Your MySQLconnection id is 1

Server version:5.1.51-log Source distribution

Type ‘help;‘ or‘\h‘ for help. Type ‘\c‘ to clear the buffer.

mysql>select version();

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

| version() |

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

| 5.1.51-log |

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

1 row in set(0.02 sec)

mysql>system mysql -uroot -p -S /data/3307/mysql.sock #-->system的用法

Enter password:

Welcome to theMySQL monitor. Commands end with ; or \g.

Your MySQLconnection id is 1

Server version:5.1.51-log Source distribution

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

This softwarecomes with ABSOLUTELY NO WARRANTY. This is free software,

and you arewelcome to modify and redistribute it under the GPL v2 license

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current inputstatement.

mysql> showdatabases;

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

| Database |

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

|information_schema |

| mysql |

| test |

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

3 rows in set(0.01 sec)

mysql> quit

Bye

提示：安装完mysql初始登陆管理员root用户无密码。

更改root密码

mysqladmin -uroot password ‘bdkyr14511‘ -S/data/3306/mysql.sock

mysqladmin -uroot password ‘bdkyr14511‘ -S/data/3307/mysql.sock

#测试改密码后的登陆情况

mysql -uroot -p‘bdkyr14511‘ -S /data/3306/mysql.sock

Welcome to theMySQL monitor. Commands end with ; or \g.

Your MySQLconnection id is 3

Server version:5.1.51-log Source distribution

Type ‘help;‘ or‘\h‘ for help. Type ‘\c‘ to clear the buffer.

mysql> system mysql -uroot -p‘bdkyr14511‘ -S /data/3307/mysql.sock

ERROR 1045(28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)

mysql>system mysql -uroot -p‘bdkyr14511‘ -S /data/3307/mysql.sock

Welcome to theMySQL monitor. Commands end with ; or \g.

Your MySQLconnection id is 4

Server version:5.1.51-log Source distribution

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

This softwarecomes with ABSOLUTELY NO WARRANTY. This is free software,

and you arewelcome to modify and redistribute it under the GPL v2 license

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

提示：一般产品环境，禁止将密码写在命令行中，非常危险。

2.2清理系统默认的多余mysql用户

mysql -uroot -p‘bdkyr14511‘ -S /data/3306/mysql.sock

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

Your MySQLconnection id is 4

Server version:5.1.51-log Source distribution

Type ‘help;‘ or‘\h‘ for help. Type ‘\c‘ to clear the buffer.

mysql>select user,host from mysql.user;

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

| user | host |

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

| root |127.0.0.1 |

| | A |

| root | A |

| | localhost |

| root |localhost |

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

5 rows in set(0.00 sec)

mysql> dropuser ‘‘@‘localhost‘;

Query OK, 0rows affected (0.04 sec)

mysql> dropuser ‘‘@‘A‘;

Query OK, 0rows affected (0.01 sec)

mysql> dropuser ‘root‘@‘A‘;

Query OK, 0rows affected (0.00 sec)

mysql>select user,host from mysql.user;

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

| user | host |

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

| root |127.0.0.1 |

| root |localhost |

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

2 rows in set(0.00 sec)

用同样的方法处理3307的用户。

处理后结果：

mysql>select user,host from mysql.user;

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

| user | host |

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

| root |127.0.0.1 |

| root |localhost |

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

2 rows in set(0.00 sec)